MySQL（高级性能篇）10章——索引优化与查询优化

数据库调优维度

索引：解决索引失效或利用不充分问题，合理建立索引
SQL：对包含大量 JOIN 操作的 SQL 语句进行调整和改进
服务器：调整 my.cnf 中的参数（如缓冲、线程数）
数据量：数据过多时采用分库分表策略

查询优化分类

物理优化：借助索引和表连接方式优化，重点掌握索引使用
逻辑优化：通过 SQL 等价变换，换查询写法提升效率

一、索引失效案例

索引对性能的重要性：在 MySQL 中，合理设计索引可提高性能。索引能高效访问数据、快速定位记录、加快查询，若查询不用索引，数据量大时全量扫描会使查询变慢
索引类型：大多数情况下采用 B + 树构建索引，空间列类型的索引使用 R - 树，MEMORY 表还支持 hash 索引
索引使用决策：查询是否使用索引由优化器决定。MySQL 优化器是基于成本（Cost - Base Optimizer）的，而非基于规则（Rule - Based Optimizer）或语义，会选择开销小的执行方式。此外，SQL 语句是否使用索引还与数据库版本、数据量、数据选择度有关

（1）全值匹配

系统中经常出现的SQL语句如下：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';

建立索引前执行：（关注执行时间）

SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';

分别为student表创建三个索引：

CREATE INDEX idx_age ON student(age);#索引一
CREATE INDEX idx_age_classid ON student(age,classId);#索引二
CREATE INDEX idx_age_classid_name ON student(age,classId,NAME);#索引三

全值匹配：全值匹配是指查询语句的条件列与联合索引列从左到右一一对应、完全匹配，覆盖所有索引列且顺序一致
结论：
1. 索引选择原则：当存在多个索引时，查询优化器通常会基于成本估算来选择最合适的索引，而不是单纯看与查询字段的匹配度。不过一般来说，和查询条件匹配度越高、能更精准筛选数据的索引，被选中的可能性越大，因为这样通常可以减少数据扫描量，提高查询效率
2. 其他索引状态：除被选中的索引外，其他索引并非失效。只是在当前查询中未被优化器选用，但在其他查询场景下仍可能被使用
补充：SQL语句中SQL_NO_CACHE的使用保证不存在查询缓存，使各语句的比较不受“是否缓存”的影响

（2）最佳左前缀法则

最左前缀原则：在使用联合索引进行查询时，查询条件需要从联合索引的最左边的列开始依次使用，并且中间不能跳过列，这样才能有效利用该联合索引来加速查询。如果查询条件不满足最左前缀要求，可能导致索引部分失效或完全失效
示例：
结论：MySQL 可以为多个字段创建联合索引，不同存储引擎对一个索引最多包含的字段数有不同限制，如 InnoDB 默认一个索引最多含 16 个字段且部分情况可调整该限制。使用多列索引时，过滤条件需按索引建立顺序依次满足，（1）若跳过某个字段，其后的字段无法利用该索引；（2）若查询条件未使用索引中的第一个字段，多列（联合）索引将不会被使用

（3）主键插入顺序

InnoDB 存储引擎表未显式创建索引时，数据存于聚簇索引叶子节点，记录存于数据页，数据页和记录按主键值升序排序。插入记录时，主键值依次增大，插满一页换页插入；主键值忽大忽小则处理较麻烦
假设某个数据页存储的记录已经满了，它存储的主键值在1~100之间：此时如果再插入一条主键值为9的记录，那它插入的位置就如下图：
数据页满处理问题：InnoDB 表中，若数据页已满，插入记录需进行页面分裂和记录移位，会造成性能损耗
主键值插入建议：为避免上述损耗，建议插入记录的主键值依次递增，可让主键具有 AUTO_INCREMENT 属性
自增主键优势：创建表时给主键添加 AUTO_INCREMENT 约束，存储引擎会自动填入自增主键值，这种主键占用空间小，支持顺序写入，能减少页分裂

（4）计算、函数、类型转换(自动或手动)导致索引失效

（5）类型转换导致索引失效

举例：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;

理解：

（6）范围条件右边的列索引失效

原因：
这说明：范围条件右边的列不能使用，比如：（<）（<=）（>）（>=）和 between 等
例如：金额查询，日期查询往往都是范围查询。应将查询条件放置where语句最后。也就是说，在创建联合索引时，要把涉及范围的字段放在最后一个

（7）不等于(!= 或者<>)索引失效

（8）is null可以使用索引，is not null无法使用索引

可以把is null理解成等于，is not null理解成不等于
总结：

（9）like以通配符%开头索引失效

原理：
在使用LIKE关键字进行查询的查询语句中，如果匹配字符串的第一个字符为'%'，索引就不会起作用。只有'%'不在第一个位置，索引才会起作用

（10）OR 前后存在非索引的列，索引失效

原理：
index_merge就是对age和name分别进行了扫描，然后将这两个结果集合并了，这样做的好处就是避免了全表扫描

（11）数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4（5.5.3版本以上支持），兼容性更好，统一字符集可以避免由于字符集转换产生的乱码
不同的字符集进行比较前需要进行转换，造成索引失效

（12）练习及一般性建议

练习：假设index(a,b,c)
建议：

二、关联查询优化

（1）采用外连接

（2）采用内连接

（3）join语句原理

join连接多表本质是数据循环匹配，MySQL 5.5版本前仅支持嵌套循环（Nested Loop Join）表间关联方式，在关联表数据量大时执行时间长，5.5以后版本引入BNLJ算法优化嵌套执行

3.3.1驱动表和被驱动表

驱动表就是主表，被驱动表就是从表，非驱动表
查询优化器会依据查询语句对连接表进行优化，从而决定先查询的表。先被查询的表即为驱动表，另一个表则是被驱动表，而通过使用 explain 关键字能够查看查询中驱动表和被驱动表的具体情况

3.3.2 Simple Nested-Loop Join (简单嵌套循环连接)

算法相当简单，从表A中取出一条数据1，遍历B表，将匹配到的数据放到result。以此类推，驱动表A中的每一条记录与驱动表B的记录进行判断
可以看到这种方式效率是非常低的，以上述表A数据100条，表B数据1000条计算，则A*B=10万次
开销统计 SNLJ
外表扫描次数 1
内表扫描次数 A
读取记录数 A+B*A
JOIN比较次数 B*A
回表读取记录数 0（因为这个没有涉及到索引）
当然mysql肯定不会这么简单粗暴地去进行表的连接，所以就出现了后面的两种对Nested-Loop Join优化的算法

开销统计	SNLJ
外表扫描次数	1
内表扫描次数	A
读取记录数	A+B*A
JOIN比较次数	B*A
回表读取记录数	0（因为这个没有涉及到索引）

3.3.3 Index Nested-Loop Join （索引嵌套循环连接）

Index Nested-Loop Join 的优化核心在于降低内层表数据匹配次数。它要求被驱动表具备索引，借助外层表的匹配条件与内层表索引直接匹配，规避与内层表每条记录逐一比较，显著减少对内层表的匹配操作，提升查询效率
驱动表中每条记录通过被驱动表的索引进行访问，因为索引查询的成本是比较固定的，故MySQL优化器都倾向于使用记录数少的表作为驱动表（小表驱动大表）
开销统计 SNLJ INLJ
外表扫描次数 1 1
内表扫描次数 A 0
读取记录数 A+B*A A+B(match)
JOIN比较次数 B*A A * Index(Height)
回表读取记录次数 0 B(match)(if possible)
如果被驱动表加索引，效率是非常高的。但如果索引不是主键索引，所以还得进行一次回表查询。相比，被驱动表的索引是主键索引，效率会更高

开销统计	SNLJ	INLJ
外表扫描次数	1	1
内表扫描次数	A	0
读取记录数	A+B*A	A+B(match)
JOIN比较次数	B*A	A * Index(Height)
回表读取记录次数	0	B(match)(if possible)

3.3.4 Block Nested-Loop Join（块嵌套循环连接）

理解：
注意：

如图所示：

开销统计	SNLJ	INLJ	BNLJ
外表扫描次数	1	1	1
内表扫描次数	A	0	A * used_column_size / join_buffer_size +1
读取记录数	A+B*A	A+B(match)	A + B * (A * used_column_size / join_buffer_size)
JOIN比较次数	B*A	A*Index(Height)	B*A
回表读取记录次数	0	B(match)(if possible)	0

参数设置：block_nested_loop
1. 通过show variables like '%optimizer_switch%'，查看block_nested_loop状态，默认是开启的
2. 如图所示
参数设置：join_buffer_size
1. 驱动表能不能一次加载完，要看join buffer能不能存储所有的数据，默认情况下join_buffer_size=256K
2. join_buffer_size的最大值在32位操作系统可以申请4G，而在64位操作系统下可以申请大于4G的join_buffer空间（64位Windows除外，其最大值会被截断为4GB并发出警告）

（4）小结

整体效率比较：INLJ > BNLJ > SNLJ

永远用小结果集驱动大结果集（其本质就是减少外层循环的数据数量）（小的度量单位指的是表行数*每行大小）

select t1.b,t2.* from t1 straight_join t2 on (t1.b=t2.b) where t2.id<=100; # 推荐
select t1.b,t2.* from t2 straight_join t1 on (t1.b=t2.b) where t2.id<=100; # 不推荐

为被驱动表的匹配条件增加索引（减少内层表的循环匹配次数）
增加join buffer size的大小（一次缓存的数据越多，那么内层表的扫表次数就越少）
减少驱动表不必要的查询字段（字段越少，join buffer所缓存的数据就越多）

（5）Hash Join

连接算法版本更替：从 MySQL 8.0.18 版本开始加入 Hash Join，自 8.0.20 版本起将废弃 Block Nested - Loop Join（BNLJ），且默认使用 Hash Join
Nested Loop 适用场景：当被连接的数据子集较小时，Nested Loop 是比较好的连接选择
Hash Join 适用场景：Hash Join 常用于大数据集连接

三、子查询优化

MySQL 子查询概述：从 4.1 版本起，MySQL 支持子查询，即一个 SELECT 语句的查询结果可作为另一个 SELECT 语句的条件，实现 SELECT 语句的嵌套查询。子查询能一次性完成逻辑上需多个步骤的 SQL 操作，有助于通过一条 SQL 语句实现复杂查询
子查询执行效率问题：
1. 临时表开销：执行子查询时，MySQL 需为内层查询结果创建临时表，外层查询再从临时表中获取记录，查询结束后还要撤销临时表。此过程会消耗大量 CPU 和 I/O 资源，易产生慢查询
2. 缺乏索引：子查询结果存储的临时表，无论是内存临时表还是磁盘临时表，通常都没有索引，这会影响查询性能。并且，返回结果集越大，对查询性能的影响越显著
连接查询代替子查询：在 MySQL 中，可使用连接（JOIN）查询替代子查询。连接查询无需创建临时表，速度通常比子查询快。若查询中合理使用索引，性能会更优
特定情况的优化建议：尽量避免使用 NOT IN 或者 NOT EXISTS，因为它们在处理大量数据时效率较低。可以使用 LEFT JOIN ... ON ... WHERE ... IS NULL 来替代，以提高查询性能。不过需要注意，要确保在关联字段上有合适的索引，这样才能充分发挥 LEFT JOIN 的优势

五、排序优化

（1）排序优化

Index排序：在 MySQL 中，若使用索引进行排序（Index 排序），索引本身能保证数据的有序性，无需额外排序操作，效率较高
FileSort排序：FileSort 排序可能先尝试在内存中完成排序，此过程会占用较多 CPU 资源。若待排序结果集较大，内存无法容纳时，会产生临时文件并进行磁盘 I/O 操作来完成排序，整体效率较低
索引使用优化建议：
1. 通用原则：在SQL的 WHERE 子句和 ORDER BY 子句中合理使用索引。在 WHERE 子句中使用索引可避免全表扫描，在 ORDER BY 子句中使用索引可避免使用 FileSort 排序，以此提高查询效率。虽然某些特殊情况下，全表扫描或 FileSort 排序不一定比使用索引慢，但总体上仍应尽量避免这些低效操作
2. 索引选择：若 WHERE 和 ORDER BY 后面使用的是相同列，可使用单列索引。 - 若WHERE 和 ORDER BY 后面使用的列不同，可考虑使用联合索引
3. 特殊情况处理：当无法使用索引完成排序时，需要对 FileSort 排序方式进行调优

（2）索引失效情况

索引失效情况有哪些：
1. 情况一：
2. 情况二：
3. 情况三：
4. 情况四：

小结：

INDEX a_b_c(a,b,c)order by 能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c
- ORDER BY a DESC,b DESC,c DESC#如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b = const ORDER BY c
- WHERE a = const ORDER BY b,c
- WHERE a = const AND b > const ORDER BY b,c#不能使用索引进行排序
- ORDER BY a ASC,b DESC,c DESC /* 排序不一致 */
- WHERE g = const ORDER BY b,c /*丢失a索引*/
- WHERE a = const ORDER BY c /*丢失b索引*/
- WHERE a = const ORDER BY a,d /*d不是索引的一部分*/
- WHERE a in (...) ORDER BY b,c /*对于排序来说，多个相等条件也是范围查询*/

（3）案例实战

优先使用 Index 排序，避免 FileSort 排序：
执行案例分析：
结论：

（4）filesort算法：双路排序和单路排序

FileSort 的两种算法：
单路排序存在的问题：
综合结论：
优化策略一：尝试提高 sort_buffer_size
1. sort_buffer_size
2. 如图：
优化策略二：尝试提高max_length_for_sort_data
1. max_length_for_sort_data
2. 如图：

六、GROUP BY优化

（1）GROUP BY与索引

索引使用原则：GROUP BY 使用索引的原则和 ORDER BY 基本相同，即便没有过滤条件，也能直接使用索引
排序分组规则：GROUP BY 先对数据排序再分组，需遵循索引的最佳左前缀法则
无索引列处理：若无法使用索引列，可增大 max_length_for_sort_data 和 sort_buffer_size 参数（看上面）

（2）WHERE 与 HAVING

（3）减少高CPU消耗操作

七、优化分页查询

（1）分页查询与覆盖索引

（2）优化思路一：索引排序分页 + 主键关联

（3）优化思路二：主键自增表的位置查询

八、优先考虑覆盖索引

（1）什么是覆盖索引

8.1.1覆盖索引定义

8.1.2示例分析

（2）覆盖索引的利弊

九、如何给字符串添加索引

（1）前缀索引

案例：
MySQL是支持前缀索引的。默认地，如果你创建索引的语句不指定前缀长度，那么索引就会包含整个字符串
前缀索引：前缀索引通常用于字符串类型的列，但不局限于此，一些数据类型较长、数据重复度低的列也可使用前缀索引，所以不能简单说就是给字符串类型列创建的索引
```
alter table teacher add index index1(email);alter table teacher add index index2(email(6));
```
上面两种不同的定义在数据结构和存储上有什么区别呢？下图就是这两个索引的示意图
使用index1（email 全字符串索引）的执行顺序：
1. 从 index1 索引树找到索引值为 'zhangssxyz@xxx.com' 的记录，获取其 ID2
2. 到主键索引查询主键值为 ID2 的行，确认 email 值无误后将该行加入结果集
3. 因下一条记录不满足条件，循环结束。此过程仅回主键索引取一次数据，系统认为只扫描了一行
使用index2（email (6) 前缀索引）的执行顺序：
1. 从 index2 索引树找出索引值为 'zhangs' 的记录，首个记录 ID 为 ID1
2. 到主键索引查询主键值为 ID1 的行，发现 email 值不符，丢弃该行
3. 继续取 index2 中下一条索引值为 'zhangs' 的记录，获取 ID2，再到主键索引取整行判断，值正确则加入结果集
4. 重复此步骤，直至 index2 上索引值不再是 'zhangs' 时结束循环

（2）前缀索引对覆盖索引的影响

使用前缀索引就用不上覆盖索引对查询性能的优化了，原因如下

十、索引下推

（1）使用前后的扫描过程

定义：Index Condition Pushdown（ICP）是 MySQL 5.6 新特性，是存储引擎层用索引过滤数据的优化方式
无 ICP 情况：
启用 ICP 情况：
好处：减少存储引擎访问基表次数和服务器访问存储引擎次数
效果影响因素：加速效果取决于存储引擎内通过 ICP 筛选掉数据的比例

（2）ICP的开启/关闭

默认情况下启用索引条件下推。可以通过设置系统变量optimizer_switch控制：index_condition_pushdown

#打开索引下推
SET optimizer_switch ='index_condition_pushdown=on' ;#关闭索引下推
SET optimizer_switch= "index_condition_pushdown=off' ;

当使用索引条件下推时，EXPLAIN语句输出结果中 Extra 列内容显示为 Using index condition

（4）ICP的使用条件

表访问类型与 ICP：当表访问类型是 range（范围查询）、ref（根据索引查找匹配值）、eq_ref（使用唯一索引查找一行记录）和 ref_or_null（查找匹配值或为空值）时，数据库可以使用 ICP 优化。就像不同的开门方式下（不同访问类型），都能用 ICP 这把 “钥匙” 来提高效率
支持的表类型：ICP 能在 InnoDB 和 MyISAM 表中使用，而且对于分区的 InnoDB 和 MyISAM 表也适用
InnoDB 表的 ICP 应用：在 InnoDB 表中，ICP 只用于二级索引。ICP 的目的是减少回表的次数
覆盖索引与 ICP：ICP 的主要目的是减少回表次数。而覆盖索引能让查询所需数据直接从索引获取，无需回表。既然都不回表了，使用 ICP 也无法进一步减少 I/O，因此 SQL 使用覆盖索引时不支持 ICP
相关子查询与 ICP：相关子查询的条件不能使用 ICP。相关子查询就像每次做一件事都要先看另一件事的结果，情况比较复杂，ICP 没办法处理这种关联紧密的查询条件
在不使用ICP索引扫描的过程：
1. storage层：只将满足index key条件的索引记录对应的整行记录取出，返回给server层
2. server 层：对返回的数据，使用后面的where条件过滤，直至返回最后一行
在使用ICP索引扫描的过程：
1. storage层：首先将index key条件满足的索引记录区间确定，然后在索引上使用index filter进行过滤。将满足的index filter条件的索引记录才去回表取出整行记录返回server层。不满足index filter条件的索引记录丢弃，不回表、也不会返回server层
2. server 层：对返回的数据，使用table filter条件做最后的过滤
使用前后的成本差别：
1. 未使用 ICP 时，存储层会返回需被 index filter 过滤的整行记录
2. 启用 ICP 后，可直接剔除不满足 index filter 条件的记录，省去回表及传递到 server 层的成本
3. ICP 的加速效果取决于存储引擎内 ICP 筛选掉数据的比例

十一、普通索引 vs 唯一索引

从性能的角度考虑，你选择唯一索引还是普通索引呢？选择的依据是什么呢？

create table test(
id int primary key,
k int not null,
name varchar(16),
index (k)
)engine=InnoDB;

表中R1~R5的(ID,k)值分别为(100,1)、(200,2)、(300,3)、(500,5)和(600,6)

（1）查询过程

假设，执行查询的语句是 select id from test where k=5
1. 对于普通索引来说，查找到满足条件的第一个记录(500,5)后，需要查找下一个记录，直到碰到第一个不满足k=5条件的记录
2. 对于唯一索引来说，由于索引定义了唯一性，查找到第一个满足条件的记录后，就会停止继续检索
那么，这个不同带来的性能差距会有多少呢？答案是，微乎其微

（2）更新过程

change buffer介绍：更新数据页时，若数据页在内存，直接更新；若不在，InnoDB 会把更新操作缓存在 change buffer 里，不用从磁盘读入数据页。下次查询访问该数据页时，把页读入内存，执行 change buffer 里和它有关的操作，保证数据逻辑正确
merge 过程：将 change buffer 里的操作应用到原数据页得出最新结果叫 merge。访问数据页、后台线程定期、数据库正常关闭时，都会触发 merge
使用 change buffer 的好处：把更新操作先记在 change buffer，能减少读磁盘，提升语句执行速度，还避免占用内存，提高内存利用率
索引与 change buffer：只有普通索引能使用 change buffer，唯一索引更新不能用
插入新纪录（400,4）的处理流程：

（3）change buffer的使用场景

索引选择原则：普通索引和唯一索引查询能力差不多，主要看对更新性能的影响，建议优先选普通索引
change buffer使用建议：
1. 数据量大的表，普通索引配合 change buffer 对更新优化明显
2. 若更新后需要马上查询该记录，应关闭 change buffer；其他情况，change buffer 能提升更新性能
业务无法保证数据唯一的处理：
1. 业务正确性优先，若业务不能保证数据唯一或要求数据库约束，必须创建唯一索引。此时若遇大量插入慢、内存命中率低问题，可将此作为排查思路
2. 归档库场景，如线上数据保留半年，历史数据存归档库，因归档数据无唯一键冲突，为提高归档效率，可把唯一索引改成普通索引

十二、其它查询优化策略

（1）EXISTS 和 IN 的区分

（2）COUNT(*)与COUNT(具体字段)效率

问题背景：在 MySQL 中统计数据表行数有 SELECT COUNT(*)、SELECT COUNT(1) 和 SELECT COUNT(具体字段) 三种方式，比较它们的查询效率需保证统计结果相同，若统计某个字段的非空数据行数则另当别论
效率分析：
1. COUNT(*) 与 COUNT(1)：（1）本质无区别，执行效率可视为相等，虽执行时间可能略有差异（2）有 WHERE 子句时，统计符合筛选条件的数据行；无 WHERE 子句时，统计全量数据行数
2. 不同存储引擎下的差异：（1）MyISAM 引擎：统计行数复杂度为 O (1)。因为每张 MyISAM 数据表的 meta 信息中存储了 row_count 值，表级锁保证数据一致性（2）InnoDB 引擎：支持事务，采用行级锁和 MVCC 机制，无法像 MyISAM 一样维护 row_count 变量，需扫描全表，复杂度为 O (n)，通过循环 + 计数完成统计
3. COUNT(具体字段) 在 InnoDB 引擎中的情况：（1）尽量采用二级索引统计数据行数。主键使用聚簇索引，包含信息多，比二级索引大（2）COUNT(*) 和 COUNT(1) 只需统计行数，系统会自动选用占用空间更小的二级索引；若有多个二级索引，使用 key_len 小的；没有二级索引时，采用主键索引统计

（3）关于SELECT(*)

表查询时，建议明确指定字段列表，即使用 SELECT <字段列表> 进行查询，而非使用 * 作为查询字段列表，原因如下：

解析成本高：MySQL 解析查询时，会通过查询数据字典把 * 按顺序转换为所有列名，这会大量消耗资源和时间
无法利用覆盖索引：使用 * 无法使用覆盖索引，可能降低查询性能

（4）LIMIT 1 对优化的影响

全表扫描场景：对于会扫描全量数据的 SQL 语句，若能确定结果集只有一条，添加 LIMIT 1 可加快查询速度。因为当找到一条结果后，查询会停止，不再继续扫描剩余数据
唯一索引场景：若数据表已针对查询字段建立唯一索引，查询会通过索引进行，不会全量扫描数据，此时无需添加 LIMIT 1

（5）多使用COMMIT

使用 COMMIT 建议：程序中应尽可能多使用 COMMIT，这样既能提升程序性能，又能因 COMMIT 释放资源而减少需求
COMMIT 释放的资源：
1. 回滚段上用于恢复数据的信息
2. 程序语句获得的锁
3. redo / undo log buffer 中的空间
4. 管理上述 3 种资源的内部开销