插件式架构

MySQL的插件式架构.xmind
MySQL的插件式架构

索引问题

索引的出现是为了减少单一维度查询时，搜索数据的成本。

索引的基础架构

索引的分类

不同的存储引擎支持不同的索引数据结构。

MySQL 支持的索引类型至少包括：BTree索引、Hash索引、full-text全文检索、R-Tree索引。

Innodb 支持的索引数据结构只有 B+树。

B+树索引

b+tree-index-image.png

B 树扩充了二叉平衡树，让每个节点能够存储的数据大大提升。

B+ 树从 B 树演变而来，B 树每个节点都存储数据，但高度高，只有查找离根节点近的数据的速度是快的；B+树所有数据都存储在叶子节点，所以查询到特定的数据必须走完查询路径，也因此 B+树的查找速度稳定，遍历全部数据和范围查找的算法稳定（不用上溯下钻）。两种数据结构，各有所长。

B+树的每个节点可以被认为是一个磁盘块（block）-可以认为 MySQL 的磁盘块等同于 OS 的数据页，大小通常为 4k/8k/16k。磁盘块通常是双层的，第一层表示存储的数据项（data entry），第二层表示指向子节点的指针（pointer）。但 B+树本身只有叶子节点真实数据，非叶子节点存储的数据指引了指针的搜索方向（作为分界符）。

三层的 B+树能够存储的上百万条数据。也就是说，第三层是叶子节点，且叶子节点的数量为百万级。

假设数据总量为 M：

因为分界的关系，所以如果一个磁盘块能够拥有的数据项数量为 n，则可以拥有的指针数量为 n + 1。

则树的高度 height = log(n+1为底)M。

而数据的块大小又是固定的，也就意味着数据项的大小，决定了 n 的大小。所以 int 为 4 字节，bigint 为 4 字节，产生的 key_length 不一样，最终导致的树的形态也就不一样-注意，这就是B+树的数据只放在叶子节点的原因，非叶子节点存有最大限度的小数据（只有索引数据），它的 n 值最大，树的高度越低。反之，如果一个非叶子节点只能存储一个数据，则树退化为线性表。

总结：小数据 + 非叶子节点只存放小索引的设计 = B+树的高度。转换成 B 树则树的高度会变高很多，增加了磁盘 I/O。

一个数据结构可视化地址。

最左匹配原则

当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

不不能跳过索引中的列列，否则只能⽤用到索引前⾯面的部分。

但高版本的 MySQL 开始支持跳跃索引（待补充）。

如果查询中有某个列列的范围查询，则其右边所有的列列都⽆无法⽤用到索引优化。

索引列不能参与计算，另当like通配符在最左如:like’%dd’，或者使用负向匹配 not in,!=,<>等运算符都不不会使⽤用索引。

字段加函数则⽆无法使⽤用索引。隐式转换⽆无法使⽤用索引（这其实也是相当于对索引列加函数进行转化）， 同样的问题也存在于 join 查询。

查询优化器对索引的选择

多个索引同时存在，也每次只能使用一个索引。有重叠的索引，如 status、status + time 可能导致任一索引不被使用，有时候单一索引反而更简单。因为添加索引的字段一定要有很好的区分度【cardinality】，区分度不够的时候回表的开销不如 all（full table scan）。

数据量小(比如小于2000 时)的时候 type 可能会是 all，即不走索引直接全表扫描，原理是类似 pg 和 oracle 的 cost-based optimizer。

哪些情况要建索引

  1. 主键自动建主键索引
  2. 频繁作为查询条件的字段应该创建索引
  3. 查询中与其他表关联的字段，外键关系建立索引
  4. 在高并发下倾向建立组合索引
  5. 查询中的排序字段，排序字段若通过索引去访问将大大提高排序速度
  6. 查询中统计或者分组的数据
  7. Index Selectivity = count(distinct column = cardinality)/count(*)。在遇到慢查询的时候，应该考虑建立新索引或者更新存量索引的结构，将查询的关键列包含进去。一个常见的问题是，一个单据既有状态，又有时间，时间的区分度是更高的，但常见的最佳实践是在状态上加索引，因为状态上的索引带来的潜在查询结果更小。 server 层通过 executor 调用 engine 的读接口次数会少很多。

哪些情况不适合建索引

  1. 频繁更新的字段

  2. where条件用不到的字段不创建索引

  3. 表记录太少

  4. 经常增删改的表

  5. 数据重复太多的字段，为它建索引意义不大（假如一个表有10万，有一个字段只有T和F两种值，每个值的分布概率大约只有50%，那么对这个字段的建索引一般不会提高查询效率，索引的选择性是指索引列的不同值数据与表中索引记录的比，，如果，一个表中有2000条记录，表中索引列的不同值记录有1980个，这个索引的选择性为1980/2000=0.99，如果索引项越接近1，这个索引效率越高）。

与 order by 的关系

1、如果你只需要结果集中的某几行，那么建议使用 limit（limit 最好不要配 offset，配 id，要注意 id 滚动的问题）。这样的话可以避免抓取全部结果集，然后再丢弃那些你不要的行。

2、对于 order by 查询，带或者不带 limit 可能返回行的顺序是不一样的。

3、如果 limit row_count 与 order by 一起使用，那么在找到第一个 row_count 就停止排序，直接返回（类似 ES 的提前返回）。limit 的本质是找到足够多的数据的时候才停止，如果只是想限制查询足够多的数据，id < begin + limit 的性能表现会好得多。

4、如果 order by 列有相同的值，那么 MySQL 可以自由地以任何顺序返回这些行。换言之，只要 order by 列的值不重复，就可以保证返回的顺序。

5、可以在order by子句中包含附加列(组合 order by)，以使顺序具有确定性。

6、ORDER BY的索引优化。如果一个SQL语句形如：
SELECT [column1],[column2],…. FROM [TABLE] ORDER BY [sort];
在[sort]这个栏位上建立索引就可以实现利用索引进行order by 优化。相反地，如果 order by 没有命中索引，就会导致 file sort或者错误的索引选择，mysql 5.7 也不例外。但很多时候，没有命中索引，也不一定就会慢，命中索引或多或少都会导致回表，有可能不回表的速度更快 - 这取决于 query optimizer 怎么看待这个查询计划。考虑多方诉求的话，可以打破常规，考虑把 id 加进索引里。所以 order by 的列不是查询优化器选择的索引是最尴尬的。

7、WHERE + ORDER BY的索引优化，形如：
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] = [value] ORDER BY [sort];
建立一个联合索引(columnX,sort)来实现order by 优化。

注意：如果columnX对应多个值，如下面语句就无法利用索引来实现order by的优化
SELECT [column1],[column2],…. FROM [TABLE] WHERE [columnX] IN ([value1],[value2],…) ORDER BY[sort];

8、WHERE+ 多个字段ORDER BY
SELECT * FROM [table] WHERE uid=1 ORDER x,y LIMIT 0,10;
建立索引(uid,x,y)实现order by的优化,比建立(x,y,uid)索引效果要好得多。

MySQL Order By 不能使用索引来优化排序的情况

  • 对不同的索引键做 ORDER BY ：(key1,key2分别建立索引)
    SELECT * FROM t1 ORDER BY key1, key2;

  • 在非连续的索引键部分上做 ORDER BY：(key_part1,key_part2建立联合索引;key2建立索引)
    SELECT * FROM t1 WHERE key2=constant ORDER BY key_part2;

  • 同时使用了 ASC 和 DESC：(key_part1,key_part2建立联合索引)
    SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

  • 用于搜索记录的索引键和做 ORDER BY 的不是同一个：(key1,key2分别建立索引)
    SELECT * FROM t1 WHERE key2=constant ORDER BY key1;

  • 如果在WHERE和ORDER BY的栏位上应用表达式(函数)时，则无法利用索引来实现 order by 的优化
    SELECT * FROM t1 ORDER BY YEAR(logindate) LIMIT 0,10;

特别提示:
1>mysql一次查询只能使用一个索引。如果要对多个字段使用索引，建立复合索引。
2>在ORDER BY操作中，MySQL只有在排序条件不是一个查询条件表达式的情况下才使用索引。

9、如果不指定 ORDER BY，不能指望 mysql 默认返回任何默认顺序。但一旦指定了 order by，MySQL 的 order by 的默认值是 asc。

10、 MySQL 8.0 开始支持索引在磁盘上排序 。

11、Databases can read indexes in both directions. 但，如果走索引的列和 order by 的列正好相反，那么查询会非常非常慢。举例，假设 t 的数据量非常大，select * from t where gmt_create < '2019-08-11 22:00:00' order by id，如果 MySQL 的查询优化器决定使用 id作为索引（MySQL 上每次只有一个索引会生效），那么查询会先从主索引的树的左边往右扫（扫描顺序由 order by 的顺序决定），如果当前时间和 2019-08-11 22:00:00 之间的数据量非常大，会导致非常大的 filtered，查询会异常地慢（这种情况有点类似 index jumping-注意看这个例子的 6.3）。

12、order-by 语句可能会误导查询优化器，选择错误的索引，形成错误的查询计划。这是一个无数的 RD 和 DBA 工作中会遇到的已知 bug。

与 Group By 的关系

must appear in the GROUP BY clause or be used in an aggregate function

所有的 group by 里的列必须被select。select 中除了聚合函数，必须放在 group by 里。

group by 的实质是先排序后分组。在 group by 的列没有索引时，考虑使用 order by null（作用是强制对查询结果禁用排序），有时候可以消除 file sort（因为不需要排序了）。

常见的分组逻辑：
where 先搜出结果集，group by 对结果进行分组（整张表不一定会分到一组，如果分组键里有唯一索引每一行都是一组，则每一行都是一组，所以有个常见的逻辑谬误，如 max 问题），然后对分组进行 having 过滤。

索引失效_复合索引（避免）

  1. 应该尽量全值匹配
  2. 复合最佳左前缀法则（第一个索引不能掉，中间不能断开）
  3. 不在索引列上做任何操作（计算、函数、类型转换）会导致索引失效而转向全表扫描
  4. 储存引擎不能使用索引中范围条件右边的列
  5. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *（特别是生成 orm 映射的时候，尽量把所有的列写入select 段中）。
  6. mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
  7. is null，is not null也可能会无法使用索引
  8. like 以通配符开头
  9. 字符串不加单引号（引发了隐式转化）
  10. 少用or（在大多数情况下用 in 代替，in 也不好，有时候会导致全表扫描）

null

参考《MySQL中IS NULL、IS NOT NULL、!=不能用索引？胡扯！》、《MySQL中NULL对索引的影响》。

对MySQL来说，null是一个特殊的值，Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values。比如：不能使用=,<,>这样的运算符，对null做算术运算的结果都是null，count时不会包括null行等，null比空字符串需要更多的存储空间等。

blob 与 text

这两种数据类型不能配置非 null 缺省值（即缺省值可以为 null），所以不适合配 not null 约束。

破除偏见

首先，null 列会存在于 MySQL 的索引里。一般传言认为：null 值必然会全表扫描，是不准确的；null 值不会存储在索引里，也是不准确的。

is-null使用索引.png

NULL 与 B+ 树的存储

一条记录的主键值不允许存储 NULL 值。设置为 NOT NULL 的列也不允许存储 NULL 值。

对于索引列值为NULL的二级索引记录来说，它们被放在B+树的最左边。

We define the SQL null to be the smallest possible value of a field.

在通过二级索引idx_key1对应的B+树快速定位到叶子节点中符合条件的最左边的那条记录后，就可以顺着每条记录都有的 next_record 属性沿着由记录组成的单向链表去获取记录了，直到某条记录的key1列不为 NULL。

是否使用索引的决策依据到底是什么

MySQL 的执行计划是由查询优化器产出的。查询优化器在这个场景里最重要的参考因子是成本（cost-based optimizing 的优化策略是数据库领域最常见的优化策略）。

  • 读取二级索引记录的成本。
  • 将二级索引记录执行回表操作，也就是到聚簇索引中找到完整的用户记录的操作所付出的成本。

换言之，回表意味着 IO 被放大了（简单来说，读至少乘以一个系数 2）。如果回表比简单地全表扫描聚簇索引成本还要高，那么查询优化器就会选择不走索引。

比方说对于下边这个查询：

1
SELECT * FROM s1 WHERE key1 IS NULL;

复制代码优化器会分析出此查询只需要查找key1值为NULL的记录，然后访问一下二级索引idx_key1，看一下值为NULL的记录有多少（如果符合条件的二级索引记录数量较少，那么统计结果是精确的，如果太多的话，会采用一定的手段计算一个模糊的值）- 这种在查询真正执行前优化器就率先访问索引来计算需要扫描的索引记录数量的方式称之为 index dive。当然，对于某些查询，比方说WHERE子句中有IN条件，并且IN条件中包含许多参数的话，比方说这样：

1
SELECT * FROM s1 WHERE key1 IN ('a', 'b', 'c', ... , 'zzzzzzz');

复制代码这样的话需要统计的key1值所在的区间就太多了，这样就不能采用index dive的方式去真正的访问二级索引idx_key1，而是需要采用之前在背地里产生的一些统计数据去估算匹配的二级索引记录有多少条（很显然根据统计数据去估算记录条数比index dive的方式精确性差了很多）。
反正不论采用index dive还是依据统计数据估算，最终要得到一个需要扫描的二级索引记录条数，如果这个条数占整个记录条数的比例特别大，那么就趋向于使用全表扫描执行查询，否则趋向于使用这个索引执行查询。

理解了这个也就好理解为什么在WHERE子句中出现IS NULL、IS NOT NULL、!=这些条件仍然可以使用索引，本质上都是优化器去计算一下对应的二级索引数量占所有记录数量的比值而已。

MySQL 官方文档的介绍

参考《8.2.1.13 IS NULL Optimization》。

  1. 对 NOT NULL 的列使用 IS NULL 查询，表达式会被 optimized away，但如果查询的表是由于 outer join 产生的 null 值，则不会发生 optimization。

其他优化建议

  1. 批量insert语句最好采用bulk insert的方法，如insert into table(xxx) values (xxx),(xxx),每个批次以执行时间小于100ms为原则。
  2. 禁止使用Select *，*用所需字段代替。
  3. 禁止使用子查询.
  4. 避免使用Or（ in 也不那么好），用Union代替.
  5. 不要使用大偏移量的分页。
  6. 为较长的字符串使用前缀索引。如果alter table table_name add key (long_string(25));，可以起到类似 git 的 commit 前缀的作用。

explain 思维导图

MySQL-Explain.png
MySQL-Explain.xmind

事务与隔离级别

ACID 特性详解

事务是数据库管理系统执行过程中的一个逻辑单位，由一系列操作组成。ACID 是事务的四个基本特性：

  • 原子性（Atomicity）：事务是一个不可分割的工作单位，事务中的操作要么全部成功，要么全部失败。通过 Undo Log 实现，当事务执行失败时，利用 Undo Log 回滚到事务开始前的状态。

  • 一致性（Consistency）：事务执行前后，数据库的完整性约束没有被破坏，数据库从一个一致性状态变换到另一个一致性状态。

  • 隔离性（Isolation）：一个事务的执行不应受其他并发事务的干扰，事务之间是隔离的。通过锁机制和 MVCC 实现。

  • 持久性（Durability）：事务一旦提交，对数据库的修改是永久性的，即使系统发生故障也不会丢失。通过 Redo Log 实现。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 开启事务
START TRANSACTION;
-- 或
BEGIN;

-- 执行一系列操作
UPDATE account SET balance = balance - 100 WHERE user_id = 1;
UPDATE account SET balance = balance + 100 WHERE user_id = 2;

-- 提交事务
COMMIT;

-- 或回滚事务
ROLLBACK;

四种隔离级别

SQL 标准定义了四种隔离级别，MySQL InnoDB 默认使用可重复读（REPEATABLE READ）：

  1. 读未提交（READ UNCOMMITTED）：允许读取未提交的数据，可能导致脏读、不可重复读、幻读。

  2. 读已提交（READ COMMITTED，RC）：只能读取已提交的数据，解决了脏读问题，但可能出现不可重复读和幻读。

  3. 可重复读（REPEATABLE READ，RR）：保证同一事务中多次读取同一数据的结果一致，解决了脏读和不可重复读，但可能出现幻读。MySQL InnoDB 通过 MVCC 和 Next-Key Lock 解决了幻读问题。

  4. 串行化（SERIALIZABLE）：最高的隔离级别，强制事务串行执行，通过锁机制实现，解决了所有并发问题，但并发性能最差。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 查看当前隔离级别
SELECT @@GLOBAL.tx_isolation, @@SESSION.tx_isolation;

-- MySQL 8.0 语法
SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation;

-- 设置全局隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 在事务开始时设置隔离级别
START TRANSACTION ISOLATION LEVEL READ COMMITTED;

MVCC 机制原理

MVCC（Multi-Version Concurrency Control，多版本并发控制）通过保存数据的历史版本，实现读写操作互不阻塞，提高并发性能。

ReadView

ReadView 是 MVCC 中用于判断数据可见性的快照视图，包含以下关键信息：

  • m_ids：生成 ReadView 时，当前系统中活跃（未提交）的事务 ID 列表。
  • min_trx_id：m_ids 中最小的事务 ID。
  • max_trx_id：生成 ReadView 时，系统应该分配给下一个事务的 ID（即当前最大事务 ID + 1）。
  • creator_trx_id：生成该 ReadView 的事务 ID。

可见性判断规则：

  1. 被访问版本的 trx_id < min_trx_id：说明该版本在 ReadView 生成前已提交，可见。
  2. 被访问版本的 trx_id ≥ max_trx_id：说明该版本在 ReadView 生成后才开始，不可见。
  3. min_trx_id ≤ 被访问版本的 trx_id < max_trx_id：
    • 如果 trx_id 在 m_ids 中：说明该版本由活跃事务生成，不可见。
    • 如果 trx_id 不在 m_ids 中：说明该版本已提交，可见。
  4. 被访问版本的 trx_id == creator_trx_id：说明是自己修改的，可见。

Undo Log 版本链

每次对记录进行修改时，Undo Log 会记录修改前的版本，通过回滚指针（roll_pointer）将这些版本串联成一个链表。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 演示 MVCC 效果
-- 事务 A
START TRANSACTION;
SELECT * FROM user WHERE id = 1;  -- 读取到版本 V1

-- 事务 B（在事务 A 未提交前执行）
START TRANSACTION;
UPDATE user SET name = 'new_name' WHERE id = 1;  -- 生成版本 V2
COMMIT;

-- 事务 A 继续执行
SELECT * FROM user WHERE id = 1;  -- 仍然读取到版本 V1（可重复读）
COMMIT;

-- RC 隔离级别下，事务 A 第二次查询会读取到版本 V2

幻读问题与 Next-Key Lock

幻读是指在同一事务中，前后两次查询的结果集不一致，出现了之前不存在的记录（或者之前存在的记录消失了）。

MySQL InnoDB 在 RR 隔离级别下，通过 Next-Key Lock 解决幻读问题：

  • Record Lock：锁住索引记录本身。
  • Gap Lock：锁住索引记录之间的间隙，防止插入新记录。
  • Next-Key Lock：Record Lock + Gap Lock，锁住索引记录及其前面的间隙。
1
2
3
4
5
6
7
8
9
10
-- 演示幻读场景
-- 事务 A
START TRANSACTION;
SELECT * FROM user WHERE age > 20 FOR UPDATE;  -- 锁定 age > 20 的所有记录和间隙

-- 事务 B（尝试插入）
INSERT INTO user (name, age) VALUES ('new_user', 25);  -- 被阻塞，直到事务 A 提交或回滚

-- 事务 A
COMMIT;

锁机制

全局锁、表级锁、行级锁

全局锁

全局锁锁定整个数据库实例，所有表的读和写都被阻塞。主要用于全库逻辑备份。

1
2
3
4
5
-- 加全局读锁
FLUSH TABLES WITH READ LOCK;

-- 释放全局锁
UNLOCK TABLES;

表级锁

表级锁锁定整张表，分为表锁和 MDL（Metadata Lock）。

1
2
3
4
5
6
7
8
9
-- 表锁
LOCK TABLES user READ;  -- 读锁，其他会话可读不可写
LOCK TABLES user WRITE; -- 写锁，其他会话不可读不可写

-- 释放锁
UNLOCK TABLES;

-- MDL（自动加锁，无需显式操作）
-- 读操作会加读锁，写操作会加写锁

行级锁

行级锁只锁定被访问的行，其他行不受影响。InnoDB 支持行级锁，通过索引实现。

1
2
3
4
-- 显式加锁
SELECT * FROM user WHERE id = 1 FOR UPDATE;  -- 行级排他锁（X锁）
SELECT * FROM user WHERE id = 1 LOCK IN SHARE MODE;  -- 行级共享锁（S锁）-- MySQL 8.0 语法
SELECT * FROM user WHERE id = 1 FOR SHARE;

共享锁与排他锁

  • 共享锁（Shared Lock，S锁）：允许其他事务也加共享锁，但不允许加排他锁。用于读操作。
  • 排他锁（Exclusive Lock，X锁）：不允许其他事务加任何锁。用于写操作。

兼容性矩阵：

锁类型 S锁 X锁
S锁 兼容 不兼容
X锁 不兼容 不兼容

意向锁

意向锁是表级锁，用于表明事务打算在表的某个行上加什么类型的锁。分为意向共享锁（IS）和意向排他锁（IX）。

  • 意向共享锁（IS）：事务打算在某些行上加共享锁。
  • 意向排他锁（IX）：事务打算在某些行上加排他锁。

意向锁是 InnoDB 自动加的，无需显式操作，用于提高加锁效率（避免全表扫描检查行锁）。

间隙锁与临键锁

  • 间隙锁（Gap Lock）：锁定索引记录之间的间隙，防止插入幻影记录。
  • 临键锁（Next-Key Lock）：锁定索引记录及其前面的间隙，是 Record Lock 和 Gap Lock 的组合。
1
2
3
4
5
6
-- 假设表中有索引 age，记录为 10, 20, 30
-- 间隙锁示例
SELECT * FROM user WHERE age = 15 FOR UPDATE;  -- 锁定 (10, 20) 间隙

-- 临键锁示例
SELECT * FROM user WHERE age = 20 FOR UPDATE;  -- 锁定 (10, 20] 临键锁

死锁检测与处理

死锁是指两个或多个事务互相持有对方需要的锁，形成循环等待。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 查看死锁日志
SHOW ENGINE INNODB STATUS;

-- 死锁示例
-- 事务 A
START TRANSACTION;
UPDATE user SET name = 'A' WHERE id = 1;  -- 持有 id=1 的行锁
UPDATE user SET name = 'A' WHERE id = 2;  -- 等待 id=2 的行锁

-- 事务 B（在事务 A 未释放锁前执行）
START TRANSACTION;
UPDATE user SET name = 'B' WHERE id = 2;  -- 持有 id=2 的行锁
UPDATE user SET name = 'B' WHERE id = 1;  -- 等待 id=1 的行锁，形成死锁

-- InnoDB 会检测到死锁，自动回滚其中一个事务

死锁处理策略：

  1. 死锁检测：InnoDB 自动检测死锁，回滚代价较小的事务。
  2. 死锁预防：
    • 按固定顺序访问表和行。
    • 尽量缩短事务持有锁的时间。
    • 使用较低的隔离级别。
    • 为长时间运行的事务添加合理的超时时间。
1
2
-- 设置锁等待超时时间（单位：秒）
SET SESSION innodb_lock_wait_timeout = 50;

InnoDB 存储引擎架构

Buffer Pool 工作原理

Buffer Pool 是 InnoDB 的内存缓冲区，用于缓存数据页和索引页，减少磁盘 I/O。

  • 数据页缓存：缓存表的数据页（16KB）。
  • 索引页缓存：缓存索引页。
  • Free List：空闲页链表。
  • Flush List：脏页链表（已修改但未刷盘的页）。
  • LRU List：最近最少使用链表，管理页的淘汰策略。
1
2
3
4
5
6
7
-- 查看 Buffer Pool 配置
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';

-- 查看 Buffer Pool 状态
SHOW ENGINE INNODB STATUS\G
-- 查看 Buffer pool hit rate

Change Buffer

Change Buffer 是 Buffer Pool 中的一部分，用于缓存对辅助索引（非主键索引）的修改操作（INSERT、UPDATE、DELETE）。

当辅助索引页不在 Buffer Pool 中时，修改操作不会立即加载索引页，而是将修改记录在 Change Buffer 中，等到索引页被访问时再合并。

1
2
-- 查看 Change Buffer 配置
SHOW VARIABLES LIKE 'innodb_change_buffer_max_size';

Redo Log 与 WAL 机制

Redo Log 是重做日志，记录数据页的物理修改，用于实现事务的持久性和崩溃恢复。

**WAL（Write-Ahead Logging）**机制：先写日志，再写数据页。

  1. 事务执行时，修改先写入 Redo Log Buffer。
  2. 事务提交时，Redo Log Buffer 刷盘（根据 innodb_flush_log_at_trx_commit 设置）。
  3. 数据页异步刷盘到磁盘。
1
2
3
4
5
6
-- 查看 Redo Log 配置
SHOW VARIABLES LIKE 'innodb_log_file_size';
SHOW VARIABLES LIKE 'innodb_log_files_in_group';
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';-- 值为 0：每秒将 Redo Log Buffer 写入 OS 缓存并刷盘
-- 值为 1（默认）：每次事务提交将 Redo Log Buffer 写入 OS 缓存并刷盘
-- 值为 2：每次事务提交将 Redo Log Buffer 写入 OS 缓存，每秒刷盘

Undo Log

Undo Log 是回滚日志，记录数据修改前的值，用于事务回滚和 MVCC。

  • 事务回滚：事务执行失败时，利用 Undo Log 回滚到事务开始前的状态。
  • MVCC：通过 Undo Log 构建数据的历史版本链。
1
2
3
4
-- 查看 Undo Log 配置
SHOW VARIABLES LIKE 'innodb_undo_directory';
SHOW VARIABLES LIKE 'innodb_undo_tablespaces';
SHOW VARIABLES LIKE 'innodb_undo_log_truncate';

Doublewrite Buffer

Doublewrite Buffer 是位于系统表空间的额外存储区域，用于解决部分写失效问题。

当刷新数据页到磁盘时，如果发生故障（如断电），可能导致数据页只写入了一部分（部分写失效）。Doublewrite Buffer 机制：

  1. 先将数据页写入 Doublewrite Buffer（顺序写）。
  2. 再将数据页写入实际数据文件位置（随机写）。
  3. 如果实际写入失败，可以从 Doublewrite Buffer 恢复。
1
2
-- 查看 Doublewrite Buffer 配置
SHOW VARIABLES LIKE 'innodb_doublewrite';

刷脏页机制

脏页是指在 Buffer Pool 中被修改但未刷盘的数据页。刷脏页的触发条件：

  1. Redo Log 空间不足：当 Redo Log 可用空间小于一定阈值时，触发刷脏页。
  2. Buffer Pool 空间不足：当需要加载新页但 Buffer Pool 无空闲页时，触发 LRU 淘汰脏页。
  3. MySQL 正常关闭：关闭时将所有脏页刷盘。
  4. 后台线程定期刷盘：innodb_io_capacity 控制刷脏页的速率。
1
2
3
4
-- 查看刷脏页相关配置
SHOW VARIABLES LIKE 'innodb_io_capacity';
SHOW VARIABLES LIKE 'innodb_max_dirty_pages_pct';
SHOW VARIABLES LIKE 'innodb_adaptive_flushing';

查询优化实战

慢查询日志分析

慢查询日志记录执行时间超过指定阈值的 SQL 语句。

1
2
3
4
5
6
7
8
9
10
11
-- 开启慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- 设置阈值为 1 秒
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- 记录未使用索引的查询

-- 查看慢查询日志配置
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

-- 慢查询日志位置
SHOW VARIABLES LIKE 'slow_query_log_file';

慢查询日志分析工具：

  • mysqldumpslow：MySQL 自带的简单分析工具。
  • pt-query-digest：Percona Toolkit 提供的强大分析工具。
1
2
3
4
5
# 使用 mysqldumpslow 分析慢查询
mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log

# 使用 pt-query-digest 分析
pt-query-digest /var/log/mysql/mysql-slow.log

EXPLAIN 执行计划详解

EXPLAIN 用于分析 SQL 语句的执行计划，帮助优化查询。

1
2
3
4
EXPLAIN SELECT * FROM user WHERE id = 1;

-- MySQL 8.0 支持更详细的信息
EXPLAIN ANALYZE SELECT * FROM user WHERE id = 1;

EXPLAIN 输出字段详解：

  • id：SELECT 查询的序列号，标识查询的执行顺序。
  • select_type：查询类型（SIMPLE、PRIMARY、SUBQUERY、DERIVED、UNION 等）。
  • table：访问的表名。
  • partitions：匹配的分区。
  • type：访问类型（性能从好到差）：
    • system：表只有一行记录（系统表）。
    • const：通过主键或唯一索引查找，最多返回一行。
    • eq_ref：通过唯一索引扫描，对于每个索引键，表中只有一条记录匹配。
    • ref：通过非唯一索引扫描，返回匹配某个单独值的所有行。
    • range：索引范围扫描。
    • index：索引全扫描。
    • ALL：全表扫描。
  • possible_keys：可能使用的索引。
  • key：实际使用的索引。
  • key_len：使用的索引长度。
  • ref：索引查找所用的列或常量。
  • rows：预估需要扫描的行数。
  • filtered：被表条件过滤后的行百分比。
  • Extra：额外信息：
    • Using index：使用了覆盖索引，无需回表。
    • Using where：使用了 WHERE 过滤。
    • Using temporary：使用了临时表。
    • Using filesort：使用了文件排序（无法使用索引排序）。
1
2
-- 示例：分析执行计划
EXPLAIN SELECT id, name FROM user WHERE age > 20 ORDER BY create_time;

常见慢查询场景与优化方案

1. 全表扫描

场景：没有合适的索引或索引失效。

1
2
3
4
5
6
7
8
9
-- 优化前
SELECT * FROM user WHERE name LIKE '%test%';

-- 优化方案
-- 1. 使用前缀索引
ALTER TABLE user ADD KEY idx_name_prefix (name(10));
-- 2. 使用全文索引（MySQL 5.6+）
ALTER TABLE user ADD FULLTEXT INDEX idx_name_fulltext (name);
SELECT * FROM user WHERE MATCH(name) AGAINST('test');

2. 索引失效

场景：对索引列使用函数、计算、类型转换等。

1
2
3
4
5
-- 优化前
SELECT * FROM user WHERE YEAR(create_time) = 2020;

-- 优化方案
SELECT * FROM user WHERE create_time >= '2020-01-01' AND create_time < '2021-01-01';

3. 深分页问题

场景：使用大偏移量的 LIMIT。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 优化前（性能差）
SELECT * FROM user ORDER BY id LIMIT 1000000, 10;

-- 优化方案 1：使用子查询优化
SELECT * FROM user WHERE id > (
    SELECT id FROM user ORDER BY id LIMIT 1000000, 1
) LIMIT 10;

-- 优化方案 2：使用 JOIN 优化
SELECT u.* FROM user u
INNER JOIN (SELECT id FROM user ORDER BY id LIMIT 1000000, 10) tmp
ON u.id = tmp.id;

-- 优化方案 3：记录上次查询的最大 ID（适用于连续分页）
SELECT * FROM user WHERE id > 1000000 ORDER BY id LIMIT 10;

4. OR 条件优化

场景：使用 OR 连接多个条件。

1
2
3
4
5
6
7
-- 优化前（可能导致索引失效）
SELECT * FROM user WHERE status = 1 OR age > 20;

-- 优化方案：使用 UNION
SELECT * FROM user WHERE status = 1
UNION
SELECT * FROM user WHERE age > 20;

5. IN 条件优化

场景：IN 条件包含大量值。

1
2
3
4
5
6
7
-- 优化前
SELECT * FROM user WHERE id IN (1, 2, 3, ..., 10000);

-- 优化方案：使用临时表或 JOIN
CREATE TEMPORARY TABLE temp_ids (id INT PRIMARY KEY);
INSERT INTO temp_ids VALUES (1), (2), (3), ..., (10000);
SELECT u.* FROM user u INNER JOIN temp_ids t ON u.id = t.id;

分页查询优化（深分页问题）

深分页是指使用大偏移量的 LIMIT，性能会随着偏移量的增加而急剧下降。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 问题 SQL
SELECT * FROM user ORDER BY id LIMIT 1000000, 10;

-- 优化方案 1：延迟关联（推荐）
SELECT u.* FROM user u
INNER JOIN (SELECT id FROM user ORDER BY id LIMIT 1000000, 10) tmp
ON u.id = tmp.id;

-- 优化方案 2：使用游标分页（适用于有序主键）
SELECT * FROM user WHERE id > last_seen_id ORDER BY id LIMIT 10;

-- 优化方案 3：使用覆盖索引
SELECT id FROM user ORDER BY id LIMIT 1000000, 10;
-- 然后根据 ID 查询完整数据
SELECT * FROM user WHERE id IN (...);

MySQL 高可用架构

主从复制原理

MySQL 主从复制基于 binlog 实现，主库将数据变更记录到 binlog，从库读取 binlog 并重放。

复制流程：

  1. 主库执行 SQL，记录数据变更到 binlog。
  2. 从库的 I/O 线程连接主库，请求 binlog。
  3. 主库的 dump 线程读取 binlog 发送给从库。
  4. 从库的 I/O 线程接收 binlog 并写入 relay log。
  5. 从库的 SQL 线程读取 relay log 并重放。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- 主库配置
-- my.cnf
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
binlog-do-db = your_database

-- 创建复制用户
CREATE USER 'repl'@'%' IDENTIFIED BY 'password';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
FLUSH PRIVILEGES;

-- 查看主库状态
SHOW MASTER STATUS;

-- 从库配置
-- my.cnf
[mysqld]
server-id = 2
relay-log = mysql-relay-bin

-- 配置主从复制
CHANGE MASTER TO
  MASTER_HOST='master_host',
  MASTER_USER='repl',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154;

-- 启动复制
START SLAVE;

-- 查看从库状态
SHOW SLAVE STATUS\G

binlog 格式：

  • Statement：记录 SQL 语句，节省空间，但在某些场景下可能不一致（如使用 NOW()、UUID() 等函数）。
  • Row：记录每一行的变更，数据一致性好，但占用空间较大。
  • Mixed：混合模式，默认使用 Statement，在不确定的场景使用 Row。
1
2
-- 设置 binlog 格式
SET GLOBAL binlog_format = 'ROW';

半同步复制

半同步复制是指主库在收到至少一个从库确认后，才提交事务，提高数据安全性。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 主库安装插件
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

-- 从库安装插件
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

-- 主库配置
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000;  -- 超时时间（毫秒）

-- 从库配置
SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- 重启从库 I/O 线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

-- 查看半同步复制状态
SHOW STATUS LIKE 'Rpl_semi_sync%';

MGR（MySQL Group Replication）

MySQL Group Replication 是基于 Paxos 协议的多主复制方案，提供自动故障转移和数据一致性保证。

MGR 特性：

  • 多主模式：所有节点都可以写入。
  • 单主模式：只有一个主节点，其他节点只读。
  • 自动故障转移：主节点故障时自动选举新主。
  • 强一致性：基于 Paxos 协议保证数据一致性。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 安装 MGR 插件
INSTALL PLUGIN group_replication SONAME 'group_replication.so';

-- 配置 MGR
SET GLOBAL group_replication_bootstrap_group=OFF;
SET GLOBAL group_replication_start_on_boot=ON;
SET GLOBAL group_replication_local_address= "node1:33061";
SET GLOBAL group_replication_group_seeds= "node1:33061,node2:33061,node3:33061";

-- 启动 MGR（第一个节点）
SET GLOBAL group_replication_bootstrap_group=ON;
START GROUP_REPLICATION;
SET GLOBAL group_replication_bootstrap_group=OFF;

-- 其他节点启动 MGR
START GROUP_REPLICATION;

-- 查看 MGR 状态
SELECT * FROM performance_schema.replication_group_members;

读写分离与中间件

读写分离是指将读操作分发到从库，写操作在主库执行，提高系统并发能力。

常见中间件：

  • MySQL Router：MySQL 官方路由器。
  • ProxySQL：高性能的 MySQL 代理。
  • MyCat：开源的数据库中间件。
  • ShardingSphere：Apache 开源的分布式数据库中间件。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- ProxySQL 配置示例
-- 插入主机组
INSERT INTO mysql_replication_hostgroups (writer_hostgroup, reader_hostgroup, comment)
VALUES (10, 20, 'test');

-- 插入后端服务器
INSERT INTO mysql_servers (hostgroup_id, hostname, port)
VALUES (10, 'master_host', 3306), (20, 'slave_host1', 3306), (20, 'slave_host2', 3306);

-- 配置查询规则
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '^SELECT.*FOR UPDATE$', 10, 1),
       (2, 1, '^SELECT', 20, 1);

-- 加载配置
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;