Skip to content

02-mysql.md

Latest commit

 

History

History
405 lines (298 loc) · 8.09 KB

02-mysql.md

File metadata and controls

405 lines (298 loc) · 8.09 KB

3.2 MySQL 关系数据库

📍 导航：返回目录 | 上一节：Redis | 下一节：Kafka

存储引擎

InnoDB vs MyISAM

特性 InnoDB MyISAM
事务 ✅ 支持 ❌ 不支持
外键 ✅ 支持 ❌ 不支持
行锁 ✅ 支持 ❌ 表锁
崩溃恢复 ✅ 支持 ❌ 不支持
MVCC ✅ 支持 ❌ 不支持
全文索引 ✅ 支持（5.6+） ✅ 支持

推荐：生产环境统一使用 InnoDB

索引原理

B+ 树索引

特点：

  • 非叶子节点不存数据，只存索引
  • 叶子节点包含所有数据，且形成有序链表
  • 单节点存储更多索引，树高更低

查询效率：3-4 层 B+ 树可存储千万级数据

聚簇索引 vs 非聚簇索引

聚簇索引（主键索引）：

  • 叶子节点存储完整行数据
  • 一张表只有一个聚簇索引

非聚簇索引（二级索引）：

  • 叶子节点存储主键值
  • 需要回表查询完整数据

索引类型

-- 主键索引
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 普通索引
CREATE INDEX idx_name ON users(name);

-- 联合索引
CREATE INDEX idx_name_age ON users(name, age);

-- 全文索引
CREATE FULLTEXT INDEX idx_content ON articles(content);

索引优化原则

最左前缀匹配

-- 索引：idx_name_age_city (name, age, city)

-- ✅ 使用索引
SELECT * FROM users WHERE name = 'Alice';
SELECT * FROM users WHERE name = 'Alice' AND age = 30;
SELECT * FROM users WHERE name = 'Alice' AND age = 30 AND city = 'Beijing';

-- ❌ 不使用索引
SELECT * FROM users WHERE age = 30;
SELECT * FROM users WHERE city = 'Beijing';

索引失效场景

-- ❌ 函数计算
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- ✅ 改写
SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- ❌ 类型转换
SELECT * FROM users WHERE id = '123';  -- id是INT
-- ✅ 正确
SELECT * FROM users WHERE id = 123;

-- ❌ 前导模糊查询
SELECT * FROM users WHERE name LIKE '%Alice%';
-- ✅ 可以使用索引
SELECT * FROM users WHERE name LIKE 'Alice%';

-- ❌ OR 连接非索引字段
SELECT * FROM users WHERE id = 1 OR nickname = 'Bob';
-- ✅ 改用 UNION
SELECT * FROM users WHERE id = 1
UNION
SELECT * FROM users WHERE nickname = 'Bob';

事务与锁

事务隔离级别

隔离级别 脏读 不可重复读 幻读
READ UNCOMMITTED ✅ ✅ ✅
READ COMMITTED ❌ ✅ ✅
REPEATABLE READ（默认） ❌ ❌ ✅*
SERIALIZABLE ❌ ❌ ❌

*InnoDB 通过 MVCC 和 Next-Key Lock 解决幻读

查看和设置：

-- 查看当前隔离级别
SELECT @@transaction_isolation;

-- 设置隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

锁类型

行锁：

  • Record Lock：锁定单行
  • Gap Lock：锁定间隙
  • Next-Key Lock：Record Lock + Gap Lock

表锁：

  • 意向锁（IS/IX）：表级别锁，与行锁兼容

示例：

-- 共享锁（S锁）
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE;

-- 排他锁（X锁）
SELECT * FROM users WHERE id = 1 FOR UPDATE;

死锁

检测：

SHOW ENGINE INNODB STATUS;

避免死锁：

  1. 按相同顺序访问表和行
  2. 尽量缩短事务时间
  3. 使用较低的隔离级别
  4. 为表添加合理索引

查询优化

EXPLAIN 分析

EXPLAIN SELECT * FROM users WHERE name = 'Alice';

关键字段：

  • type：访问类型（const > eq_ref > ref > range > index > ALL）
  • possible_keys：可能使用的索引
  • key：实际使用的索引
  • rows：扫描行数
  • Extra：额外信息（Using index, Using filesort 等）

优化技巧

**1. 避免 SELECT ***：

-- ❌ 不推荐
SELECT * FROM users;

-- ✅ 推荐
SELECT id, name, email FROM users;

2. 使用 LIMIT：

-- 分页查询
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

-- 优化大偏移量（延迟关联）
SELECT u.* FROM users u
INNER JOIN (
    SELECT id FROM users ORDER BY id LIMIT 10 OFFSET 100000
) AS t ON u.id = t.id;

3. IN vs EXISTS：

-- IN：外表小，内表大
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE city = 'Beijing');

-- EXISTS：外表大，内表小
SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.city = 'Beijing');

4. 批量插入：

-- ❌ 逐条插入
INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (name) VALUES ('Bob');

-- ✅ 批量插入
INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie');

5. 避免JOIN过多表：

-- 建议单次JOIN不超过3个表
-- 可以分多次查询，在应用层组装数据

主从复制

原理

主库 → Binlog → 从库 Relay Log → 从库数据

配置主库：

# my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW

配置从库：

[mysqld]
server-id = 2
relay-log = mysql-relay

建立主从关系：

-- 从库执行
CHANGE MASTER TO
    MASTER_HOST='master_ip',
    MASTER_USER='repl',
    MASTER_PASSWORD='password',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=154;

START SLAVE;
SHOW SLAVE STATUS\G

主从延迟

原因：

  • 主库并发写入，从库单线程应用
  • 大事务
  • 网络延迟

解决方案：

  • 并行复制（MySQL 5.7+）
  • 读写分离时强制读主库
  • 使用半同步复制

分库分表

垂直拆分

按业务拆分：

  • 用户库
  • 订单库
  • 商品库

水平拆分

分片策略：

1. 范围分片：

user_0: user_id 0-9999
user_1: user_id 10000-19999
user_2: user_id 20000-29999

2. Hash 分片：

shard_id = user_id % 1024

3. 一致性Hash：解决扩容问题

分库分表中间件

  • ShardingSphere：Java生态
  • Vitess：Go语言，YouTube开源
  • MyCAT：基于Cobar

实战示例（Go）

数据库连接池

import (
    "database/sql"
    _ "github.com/go-sql-driver/mysql"
    "time"
)

func InitDB() (*sql.DB, error) {
    dsn := "user:password@tcp(127.0.0.1:3306)/dbname?parseTime=true"
    db, err := sql.Open("mysql", dsn)
    if err != nil {
        return nil, err
    }
    
    // 连接池配置
    db.SetMaxOpenConns(25)                 // 最大连接数
    db.SetMaxIdleConns(10)                 // 最大空闲连接
    db.SetConnMaxLifetime(5 * time.Minute) // 连接最大生命周期
    
    return db, db.Ping()
}

事务处理

func TransferMoney(db *sql.DB, fromID, toID int64, amount float64) error {
    tx, err := db.Begin()
    if err != nil {
        return err
    }
    defer tx.Rollback() // 如果未提交，回滚
    
    // 扣款
    _, err = tx.Exec("UPDATE accounts SET balance = balance - ? WHERE id = ?", amount, fromID)
    if err != nil {
        return err
    }
    
    // 加款
    _, err = tx.Exec("UPDATE accounts SET balance = balance + ? WHERE id = ?", amount, toID)
    if err != nil {
        return err
    }
    
    return tx.Commit()
}

本章小结

MySQL 是最流行的关系数据库，掌握索引、事务、查询优化、主从复制是后台开发基本功。

关键要点：

  • ✅ InnoDB 支持事务和行锁，是首选引擎
  • ✅ B+ 树索引设计决定查询性能
  • ✅ 理解事务隔离级别和锁机制
  • ✅ 分库分表是应对大数据量的必然选择

扩展阅读

  • 《高性能MySQL》
  • 《MySQL技术内幕：InnoDB存储引擎》
  • MySQL官方文档

💡 思考题：

  1. 为什么 InnoDB 使用 B+ 树而不是 B 树？
  2. 什么是 MVCC？如何实现的？
  3. 如何设计分库分表方案？

⏮️ 上一节：Redis | ⏭️ 下一节：Kafka