MySQL 是怎样运行的：单表访问方法、Join及基于成本的优化

0. 索引的使用

0.1 server 层和存储引擎层

server层和存储引擎层的交互是以记录为单位的

0.2 扫描区间

1. SELECT * FROM single_table WHERE key_part1 < 'b' AND key_part2 = 'a';
   形成的扫描区间为 [$-\infty$，‘b’)

2. SELECT * FROM single_table WHERE key_part1 <= 'b' AND key_part2 = 'a';
   形成的扫描区间为 (($-\infty$，$-\infty$), (‘b’, ‘a’))

对 1 而言，每个 key_part1 = 某个小于'b'的值来说的记录都是按照 key_part2 = 'a' 排序的，但从整个区间来看，可能会形成很多间断的区间，这种情况是无法利用 key_part2 来形成扫描空间的。

对 2 而言，端点 key_part1 = 'b' AND key_part2 = 'a' 处可以利用 key_part2 来减少扫描空间。

1. 单表访问方法

访问方法：执行查询的方式

只需于选取代价最小的那种访问方式去执行单表查询语句即可。

1.1 const

主键值或者唯一的二级索引定位一条记录（与常数等值比较时才有效）

需要注意的是，使用唯一二级索引查询列为 NULL 值时不会使用 const 访问方法来执行，使用 ref 访问方式。

key IS NULL

1.2 ref

1. 普通二级索引与常数进行等值比较。

2. 二级索引允许存储 NULL 值时，执行包含 key IS NULL

3. 二级索引中包含多个列时，只有最左边连续的列与常数进行等值比较，就可以使用 ref 访问方法

1.3 ref_or_null

二级索引等于某个常数，会有列值为 NULL 的记录，即
key1 = 'abc' OR key1 IS NULL
值为 NULL 的记录会被放在索引最左边。

1.4 range

使用索引查询时，扫描区间为若干个单点扫描区间（IN）或者范围扫描区间。

注意：不包含仅一个单点扫描区间，并且这里的扫描区间不能为 $(-\infty ,+\infty )$

1.5 index

扫描全部二级索引记录的访问方法（不用执行回表），即需要查询的数据及搜索条件均在索引列中

特例：人为规定；通过全表扫描执行查询，添加了 ORDER BY 主键

1.6 all

全表扫描，直接扫描全部的聚簇索引记录

1.7 索引合并

一般情况下只会为单个索引生成扫描空间，但也可能为多个索引生成扫描空间。

这里的合并是，先根据二级索引得到二级索引记录，先不进行回表，等合并后，再进行回表操作。

（1）Intersection 索引合并

要求从每个索引中获取到的二级索引记录都是按照主键值排序的。

带来的好处是：两个有序数组求交/并集或去重比较容易；按主键排序后进行回表操作容易形成顺序 IO。

这对二级索引来说，相当于要求等值查询，而非范围查询，并且需要给定所有的索引列。

例如WHERE key1 = 'a' AND id > 9000，对聚簇索引，本身就是按照主键顺序排序的，可以是范围查询，因为二级索引是包含索引列和主键的，在索引列值相同的情况下，是按主键排序的，只需扫描二级索引即可，并不需要扫描聚簇索引。

（2）Union 索引合并

要求从每个索引中获取到的二级索引记录都是按照主键值排序的。

例如WHERE key1 = 'a' OR id > 9000，此时需要扫描聚簇索引

可以组合 Intersection 与 Union 索引合并

（3）Sort-Union 索引合并

针对单独根据搜索条件从某个二级索引中获取记录比较少的情况。

很多情况下无法保证二级索引记录按照主键排序，例如范围扫描，此时要进行额外的排序工作。先获取二级索引记录，按照主键值进行排序后再进行合并。

2. 连接

涉及单表的条件

内外连接的根本区别为：在驱动表中不符合 ON 子句中的连接条件时，是否会加入到最后的结果集中。

（1）每获得一条驱动表（外循环中的表）记录，就立即到被驱动表（内循环中的表）中寻找匹配的记录。

对驱动表而言，会使用只涉及驱动表的条件来进行单表访问。

WHERE 子句中的过滤条件，不论是内连接还是外连接，不符合的记录都不会被加入到最后的结果集。

ON 子句中的过滤条件，专门针对外连接，驱动表中的记录都会被加入到最后的结果集。

对外连接而言，必须使用 ON 子句来指出连接条件。

（2）左（外）连接，LEFT (OUTER) JOIN，左侧的为驱动表；

右（外）连接，RIGHT (OUTER) JOIN，右侧的为驱动表；

内连接，推荐书写为 INNER JOIN

2.1 连接算法

基于块的嵌套循环连接，涉及到 Join Buffer。先把驱动表结果集放在Join Buffer 中，每一条被驱动表记录可以一次性匹配多条驱动表结果集中的记录；需要注意的是： Join Buffer 中不会存放驱动表记录的所有列，只有查询列表中列和过滤条件中的列。

2.2 Multi-Range Read 优化

根据二级索引，将满足搜索条件的主键存放到 read_rnd_buffer（read_rnd_buffer_size）
将 read_rnd_buffer中的主键递增排序
按照 read_rnd_buffer中顺序进行回表

2.3 Batched Key Access（BKA）

一次传入多个值到被驱动表中；

使用 BKA 优化，每多一个 join，就多一个 join_buffer

2.4 BNL 转 BKA

BNL 想转 BKA，就需要对被驱动表建立索引；

有时候被驱动表很大，但实际用到的数据比较少，这种情况下建立索引也比较耗时和浪费；可以把需要的数据导入一个临时表中，对这个临时表建立索引，再做 Join；

2.5 内存表和临时表的区别

（1）内存表：使用 Memory 引擎，重启后表结构还在，数据会被清空；

（2）临时表：可以使用各种引擎；create temporary table...（外部临时表）

一个临时表只能被创建它的 session 访问，对其他线程不可见，在这个 session 结束时会自动删除临时表；

临时表可以与普通表重名，session 内有同名的临时表和普通表的时候，show create 语句，以及增删改查语句访问的是临时表；

show tables 命令不显示临时表；

（3）MySQL 在内存中维护了 table_def_key

普通表：由 库名+表名 组成；
临时表：库名 + 表名 + server_id + thread_id；

普通表和临时表的命名规则不同，因此可以共存；

（4）在实现上，每个线程都维护了自己的临时表链表

每次操作时，先遍历这个链表，检查是否有这个名字的临时表，若有就操作临时表，否则去查找普通表；

（5）临时表与主备库复制

如果当前的 binlog_format=row，那么跟临时表有关的语句，就不会记录到 binlog 里。也就是说，只在 binlog_format=statment/mixed 的时候，binlog 中才会记录临时表的操作。

注意：备库的同步线程是一直执行的，不会自动删除临时表，需要在主库显示执行DROP TEMPORARY TABLE

（6）主库不同线程创建的相同名字的临时表，在从库中可能会分到同一线程中执行，会不会造成冲突？

不会，在记录 binlog 时，也会把主库线程 id 记录下来；

2.6 内部临时表

（1）UNION

先创建内存临时表，会按照查询列建立主键
依次执行两个查询

注意：如果使用 UNION ALL 就没有去重功能，也就不需要使用内存临时表

（2）Group by

建立内存临时表，主键为分组需要的列
向内存临时表插入数据或更新数据（如果没有就插入，否则更新）
对内存临时表根据分组列排序（Order by null 可取消此步）

有两个优化手段：

1. 分组列为 InnoDB 的索引列时，就不需要建立内存临时表，也不需要排序（不断累计结果，第一个不满足条件时就放入结果集）
2. SQL_BIG_RESULT 提示；表明数据量很大，直接使用磁盘临时表；可能会选择先进行排序，再扫描一遍获得结果（转换为 1）；

（3）generated column 机制，用来实现列数据的关联更新；例如：

alter table t1 add column z int generated always as(id % 100), add index(z);

2.7 Memory 引擎

适合使用的场景：内存临时表，其他场景还是使用 InnoDB；

（1）数据和索引分开存放，数据是按照插入顺序存放（类似 MyISAM）；全部扫描是扫描的数据部分；

（2）主键 id 默认是 hash 索引；（不支持范围查询）也可以为其创建 B+ 树索引 using btree

（3）即使使用 VARCHAR 也会视为 CHAR，保证每行长度相同；不支持 Blob 和 Text 字段；

（4）删除空出来的位置可以方便的被复用；

（5）主从复制问题

在 M-S 架构中，如果备库重启会丢失数据，主库如果更新这个表会导致主从复制停止

在双 M 架构中，可能会导致主库删除内存表（MySQL 在数据库重启后，会在 binlog 中记录 delete from mermory_tbl）

3. 基于成本的优化

MySQL 的执行成本：IO 成本和 CPU 成本。

读取一个页面花费成本为 1.0；
读取及检测一条记录是否符合搜索条件的成本默认为 0.2；

3.1 优化步骤

（1）找出可能使用的索引

索引列与常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=(<>)、LIKE 进行前缀匹配时

注意：类似 key1 != 50 这样的搜索条件，这里即使索引列有 NULL，其实是查询的结果中是不包含列值为 NULL 的记录的，即扫描区间为 （NULL， 50）和（50，$+\infty$）。

（2）计算全表扫描的代价（通过查看表的一系列统计信息）

SHOW TABLE STATUS LIKE '表名';

ROW：表中的记录数，对 MyISAM 表来说，该值是准确的。对 InnoDB 来说，是估计值。
Data_length；表占用的字节数。对 MyISAM 表来说，该值是数据文件的大小；对 InnoDB 来说，是聚簇索引占用的存储空间大小。

（3）计算使用不同索引执行查询的代价

MySQL 会先分析使用唯一二级索引的成本，再分析使用普通索引的成本。

对使用二级索引 + 回表的方式，成本主要为：

1. 扫描区间的数量：认为一个扫描区间的 IO 成本与读取一个页面相同。

2. 需要回表的记录数：会在 B+ 数中找到区间左右端点记录，统计它们之间的数量。（即使对单点扫描区间也会这样做）
   当相隔不超过 10 个页面时，会根据每页中 Page Header 的 PAGE_N_RECS进行累加。相距太远，也会沿着最左记录向右读 10 个页面，计算平均数量，再乘相隔页面数。

3. 每次回表操作都相当于访问一个页面。根据上述需要回表的记录数。

4. 回表操作完成后，再检测其他搜索条件。根据上述需要回表的记录数。

5. 是否有可能使用索引合并

（4）注意事项，单点扫描区间过多时

在计算二级索引成本的第 2 步时，会少量访问 B+ 数中的数据，这种计算方式称为 index dive。

单点扫描区间大于等于eq_range_index_dive_limit时，不会使用 index dive 的计算方式，使用索引统计数据进行估算。

SHOW INDEX FROM 索引名;主要使用 Cardinality 信息。

单个值的重复次数 Rows / Cardinality ，单个值的重复次数 * 单点扫描区间数量 = 需要回表的记录数

（5）对 ref 单表访问方式，计算因回表带来的 IO 成本时有上限限制，下面两个取最小值：

1. 最多不能超过相当于访问全表记录数的 1/10 （此处的全表记录数是一个估计值）；
2. 最多不能超过全表扫描的 IO 成本的 3 倍（聚簇索引占用页面的 3 倍）；

使用 ref 访问方法时，是按照主键排序的，因此能够产生较多的顺序 IO。（即使与 range 访问方法需要扫描的记录数相同， ref 仍更具优势）

3.2 Join 成本

单次查询驱动表的成本；查询得到的记录条数称为扇出（fanout）；

扇出值 × 查询被驱动表的成本

对内连接来说，可以选择左侧或右侧是驱动表；

对被驱动表而言，确定好驱动表后连接条件可以确定，例如s1.key1 = 常数，但在优化过程中是无法得知具体的常数值，那么如何进行估算？这里也是采用统计数据中的单个值的重复次数。

多表 Join，需要考虑连接顺序，采用了一些提前结束某种连接顺序评估等启发式规则。

4. InnoDB 统计数据

innodb_stats_persistent永久性/非永久性地存储统计数据，默认为ON；

InnoDB 默认以表为单位来收集和存储统计数据，在创建表时可以指定该表统计数据的存储方式。

… Engine=InnoDB, STATS_PERSISTENT = (1 | 0)； 存储到 磁盘 | 内存，默认与innodb_stats_persistent相同
… Engine=InnoDB, STATS_SAMPLE_PAGES = 数量；n_rows统计时的采样数量，默认与innodb_stats_persistent_sample_pages相同

4.1 基于磁盘的永久性统计数据

实际上是把统计数据存储到两个表中：innodb_table_stats 和 innodb_index_stats；

（1）innodb_table_stats ；每条记录对应一个表的统计数据；

主键为（database_name，table_name）

1. n_rows 项的收集
   从聚簇索引中选取几个叶子节点页面，计算平均包含的记录数量。
2. clustered_index_size 和 sum_of_other_index_sizes 的收集
   从数据字典中找出索引对应根页面位置。
   从根页面读取对应的 Segment Header，找到对应段信息存储的位置。
   获取叶子节点段和非叶子节点段的 INODE Entry。
   统计段中索引零散页面，及 FREE、NOT_FULL、FULL 这 3 个链表的 List Length 字段占用区的数量。（这里简单的都计算上了，实际上可能某些区中有些页面仍是空闲的，即包括了分配给了段但尚未使用的页面）

（2）innodb_index_stats ；每条记录对应一个索引的统计数据；

该表的主键为（database_name，table_name，index_name，stat_name）

stat_name 是统计项的名称。

n_leaf_pages：该索引叶子节点占用的页面数
size：该索引占用页面数
n_diff_pfxNN：对应索引列不重复的值。例如 01表示的是 key_part1 这一列不重复的值， 02 表示 key_part1、 key_part2 两列组合不重复的数。

在计算某些索引列中包含多少个不重复值时，对叶子节点采样的数量为 sample_size；（对多列索引，需要采样的数量为 innodb_stats_persistent_sample_pages × 索引包含列的个数，最大为该索引所有的叶子页面数量）

（3）更新统计数据

1. 开启innodb_stats_auto_recalc；默认开启；
   对每个表维护一个变量，记录对该表增删改的记录条数，发生变动的记录超过表大小的 10%，重新计算一次统计数据（异步发生）。
   在创建/修改表时，可以更改该属性 STATS_AUTO_RECALC = ( 1 | 0 )
2. 手动调用 ANALYZE TABLE 表名；会立即重新计算统计数据，是同步进行的。

4.2 innodb_stats_method 的使用

（1）计算某个索引列不重复值的数量时，如何对待 NULL 值？可以通过 innodb_stats_method 系统变量进行选择

nulls_equal：认为所有 NULL 值都相同；
nulls_unequal：认为所有 NULL 值都不相同；
nulls_ignored：直接把 NULL 忽略掉；

注意的是，对基于内存的非永久统计数据来说，nulls_unequal 和 nulls_ignored 效果一样；对基于磁盘的方式硬编码为 nulls_equal 方式（对 MySQL 8.0 来说也是这样）；

最好不要在索引列存放 NULL 值。

5. SQL 注意事项

5.1 相关问题

（1）IN 子句中参数顺序问题

IN 子句生成的范围区间会进行排序和去重。

（2）COUNT 是如何执行的

COUNT(expr) 统计满足要求的行记录中的 expr 不为 NULL 的行的数量

COUNT(*)、COUNT(常数)、COUNT(主键列) 因为只关心是否有记录，会选择最小的索引执行查询；

COUNT(非主键列)会选择对应的索引列执行查询。

1. 用缓存保存计数，可以使用一个 Redis 服务保存行的总数，这个表每插入 \ 删除一条记录，对应计数就 +1 \ -1；
   但缓存可能丢失更新，且逻辑上计数是不精确的；
   Redis 和 MySQL 组成的系统不支持分布式事务，无法拿到精确一致的视图
2. 在数据库中保存计数；可以采用事务的方式进行计数，可以保证逻辑上的精确；

（3）LIMIT

当前在第pageNo页，每页显示pageSize个数据

LIMIT (pageNo - 1) * pageSize, pageSize;

MySQL是在 server 层准备向客户端发送记录的时候才会去处理 LIMIT 子句中的内容（这里的发送是缓存到本地的网络缓冲区，缓冲区大小由 net_buffer_length 控制，默认是16KB大小。等缓冲区满了才真正发送网络包到客户端。）

这里 key1 是二级索引
SELECT * FROM t ORDER BY key1 LIMIT 5000, 1; 可能会选择 ALL + filesort 的方式执行查询，之所以不采用遍历二级索引的方式
因为是在 server 层判断 LIMIT 子句，也就是说扫描二级索引的记录后，先进行回表，再发送到 server 层，然后才能判断，因此每扫描一个记录都要进行一次回表操作。
通过下述子查询的方式避免了回表操作
SELECT * FROM t, (SELECT id FROM t ORDER BY key1 LIMIT 5000, 1) AS d WHERE t.id = d.id;

（4）多表连接

如果想实现多表连接，写成如下形式：
WHERE t1.id = t2.id = t3.id;
上述这些写的含义是：(t1.id = t2.id) = t3.id ，布尔表达式的结果为 0 或 1，也就说 t3.id = 0 | 1 才会被加入结果集中，而非与 t1.id 相等；
正确的方式为：
WHERE t1.id = t2.id AND t1.id = t3.id;

（5）Sort 排序

对 InnoDB 引擎，max_length_for_sort_data 行数据所占字节数，如果单行超过这个值，就会使用 排序列 + 主键id 进行排序，排序完成后需要回表找到所有需要的数据；否则，使用全字段排序；
如果使用了临时表（即 Memory 引擎），会使用 排序列 + 位置pos 进行排序，这里的数据在内存中，回表不需要 IO 操作；（tmp_table_size这个配置限制了内存临时表的大小，超过这个大小就会使用磁盘临时表，即采用 InnoDB 引擎）

排序时用的内存 sort_buffer 在排序完成后就会释放掉；

5.6 之后在有 LIMIT 并且不超过 sort_buffer_size 时引入了优先队列排序；

（6）Order by 和 LIMIT 组合使用时，需要注意

可能排序列有多个重复值，这些重复值的顺序是不确定的；那么很有可能带有 LIMIT 和不带 LIMIT 的顺序是不确定的；

如果想要顺序是确定的，那么需要加入额外的唯一排序列（如主键）

5.2 SELECT 相关问题

（1） INSERT ... SELECT... 的对象是同一个表，就会使用临时表来避免循环插入（一边遍历，一边插入，那么遍历的数据可能是刚刚插入的）；

在可重复读下事务可以看到自己插入的数据

（2）INSERT ... SELECT... 在可重复读下，会对 SELECT 对象加锁；

（3）insert 语句如果出现唯一键冲突，会在冲突的唯一值上加共享的 next-key lock(S锁)。因此，碰到由于唯一键约束导致报错后，要尽快提交或回滚事务，避免加锁时间过长。（如果冲突的是主键索引，就加记录锁）

（4）insert into … on duplicate key update 插入一行数据，如果遇到唯一性约束问题，就执行后面的更新操作；如果有多列违反唯一性，以第一个冲突列为准；

5.3 SQL 异常排查

（1）查询长时间不返回时，可以使用 show processlist 看下语句状态

（2）查询 sys.schema_table_lock_waits 可以找到阻塞的事务 id；

（3）查询单行数据慢的情况

1. 被锁住
2. 在此行记录中可能有大量的 undo log 存在