MySQL进阶之（八）索引优化与查询优化

EijoaHoaiQ:D

已于 2024-05-21 11:21:16 修改

阅读量673

点赞数 23

分类专栏： MySQL进阶文章标签：索引优化查询优化索引失效覆盖索引

于 2024-05-16 22:46:16 首次发布

本文链接：https://blog.csdn.net/qq_50994235/article/details/138509905

版权

MySQL进阶专栏收录该内容

9 篇文章 2 订阅

订阅专栏

八、索引优化与查询优化

8.1 索引失效案例
8.2 查询优化
8.3 普通索引 vs 唯一索引
- 8.3.1 查询性能
- 8.3.2 更新性能

数据库调优都有哪些维度：

索引失效、没有充分利用到索引 —— 建立索引
关联查询太多 join（设计缺陷或不得已的需求）—— SQL 优化
服务器调优及各个参数设置（缓冲、线程数等）—— 调整 my.cnf
数据过多 —— 分库分表

虽然 SQL 查询优化的技术有很多，但是大方向上完全可以分为物理查询优化和逻辑查询优化两大块：

物理查询优化是通过索引和表连接方式等基数来进行优化。
逻辑查询就是就是通过 SQL 等价变换提升查询效率，也就是写法执行效率更高。

前言

提前为下文做数据准备，两张表：学员表(student) 插入 50w 条数据，班级表(class) 插入 1w 条数据。

CREATE TABLE `class` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`className` VARCHAR(30) DEFAULT NULL,
	`address` VARCHAR(40) DEFAULT NULL,
	`monitor` INT NULL ,
	PRIMARY KEY (`id`)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

CREATE TABLE `student` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`stuno` INT NOT NULL ,
	`name` VARCHAR(20) DEFAULT NULL,
	`age` INT(3) DEFAULT NULL,
	`classId` INT(11) DEFAULT NULL,
	PRIMARY KEY (`id`)
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

8.1 索引失效案例

MySQL 中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响：

使用索引可以快速地定位表中的某条记录，从而提高数据库的查询速度，提高数据库性能。
如果查询时没有使用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。

大多数情况下都默认采用 B+Tree 来构建索引，空间列类型的索引使用 R-Tree，memory 引擎还支持 hash 索引。

用不用索引，最终都是优化器说了算，优化器基于 cost（CostBaseOptimizer），它不是基于规则（Rule-BaseOptimizer），也不是基于语义，怎么样开销小就怎么来。另外，SQL 语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

8.1.1 全值匹配

全值匹配表示不使用索引查询。

# 2. 索引失效案例
## 2.1 全值匹配我最爱
SHOW INDEX FROM student;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30; -- 0.32s
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classId = 4; -- 0.29s
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classId = 4 AND name = 'abcd'; -- 0.28s

在这里插入图片描述

CREATE INDEX idx_age ON student(age); -- int 类型占用4字节
CREATE INDEX idx_age_classid ON student(age, classId);
CREATE INDEX idx_age_classid_name ON student(age, classId, name);
SHOW INDEX FROM student;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30; -- 0.08s 5
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classId = 4; -- 0.07s 10
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classId = 4 AND name = 'abcd'; -- 0.05s 73

在这里插入图片描述

由此可以看出，添加索引后极大地提高了查询效率。

8.1.2 最左前缀法则

MySQL 在建立联合索引时会遵从最左前缀原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，并不跳过索引中的列。

已知有如下索引：
在这里插入图片描述

例子一：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND name = 'abcd';

在这里插入图片描述

例子二：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classId = 4 AND name = 'abcd';

在这里插入图片描述
例子三：

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

在这里插入图片描述
总结：MySQL 可以为多个字段创建索引，一个索引可以包含 16 个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法使用。如果查询条件中没有使用这些字段中的第一个字段时，联合索引不会被使用。

8.1.3 运算符、函数、类型转换（自动或手动）导致索引失效

01、使用函数导致索引失效

```每次作用完函数后再进行后续的操作，但不确定函数会返回什么（破坏索引的有序性），所以需要遍历全部数据，也就放弃了使用索引。``

未添加索引时：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE 'abc%'; -- ALL
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(name, 3) = 'abc'; -- ALL

给 name 字段添加索引后：

CREATE INDEX idx_name ON student(name);

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE 'abc%'; -- range
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(name, 3) = 'abc'; -- ALL

在这里插入图片描述

02、使用运算符导致索引失效

使用运算符时，这些运算符在逻辑上需要遍历更多的数据行来确定结果，而索引优化的是数据检索的效率，所以在某些情况下，优化器可能会选择不使用索引。

CREATE INDEX idx_sno ON student(stuno);
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno + 1 = 900001; -- ALL相当于做了运算，不走索引
EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno = 900000; -- ref

在这里插入图片描述

03、类型转换导致索引失效

查询条件中的数据类型与索引列的数据类型不一致时，MySQL 会进行隐式类型转换（将索引列的类型转换成查询条件中的数据类型），使得它与索引列上的原始值不匹配，从而导致索引失效。

# 索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = 123; -- ALL 将name列转换为int类型，无法与索引上的字符串匹配
# 索引生效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name = '123'; -- ref

在这里插入图片描述

官方文档：12.2 Type Conversion in Expression Evaluation。

当操作符与不同类型的操作数一起使用时，会发生类型转换以使操作数兼容。某些转换是隐式发生的。例如，MySQL 会根据需要自动将字符串转换为数字，反之亦然。

以下规则描述了比较操作的转换方式：

两个参数至少有一个是 NULL 时，比较的结果也是 NULL，特殊的情况是使用 <=> 对两个 NULL 做比较时会返回1，这两种情况都不需要做类型转换。
两个参数都是字符串，会按照字符串来比较，不做类型转换。
两个参数都是整数，按照整数来比较，不做类型转换。
十六进制的值和非数字做比较时，会被当做二进制串。
有一个参数是 timestamp 或 datetime，并且另外一个参数是常量，常量会被转换为 timestamp 。
有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较。
所有其他情况下，两个参数都会被转换为浮点数再进行比较。

8.1.4 范围条件右边的列索引失效

联合索引会形成多个键值的 B+Tree，首先会根据第一个字段排序后，再根据第二个字段排序…从左至右依次根据前一个字段排序后再进行下一个字段的排序。

对某字段进行范围查找时，该字段可以进行二分查找定位到匹配值，然后将所有符合条件的数据取出来，此字段可以使用到索引；其后面字段的值是根据此字段排序后再进行排序的，所以其后面字段是无序的，无法在无序的 B+Tree 里使用二分查找来查询，所以使用不到索引。

首先将多余的索引删除，只保留主键对应的那个索引。

CALL proc_drop_index('study01', 'student');

CREATE INDEX idx_age_classId_name ON student(age, classId, name);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND classId > 20 AND name = 'abc' ; -- range 10

在这里插入图片描述

上面查询结果中，age、classId 字段使用到了索引，name 字段未使用到索引。因为查询结果中的 key_len = 10，使用到了 age(int 4 个字节 + 非空 1 个字节) = 5 个字节，classId(int 4 个字节 + 非空 1 个字节) = 5 个字节，5 + 5 = 10 个字节。

因为 classId 是一个范围查询，在它的右侧出现的条件将不会使用索引，分析如下：

首先 classId 字段在 B+Tree 上是有序的，所以可以用二分查找法定位到 20，然后将所有大于 20 的数据取出来，classId 可以用到索引。
name 有序的前提是 classId 是确定的值，那么现在 classId 的值是取大于 20 的，可能有 10 个大于 20 的 classId，也可能有一百个 classId。
大于 20 的 classId 那部分的 B+Tree 里，name 字段是无序的，所以 name 不能在无序的 B+Tree 里用二分查找来查询，name 用不到索引。

优化

像这种 sql 中出现范围查询时，比如：(<)、(<=)、(>)、(>=)、 between 等，应该将范围查询条件放置语句最后，并将该字段放在联合索引的最后：

# 将范围查询条件放到最后
CREATE INDEX idx_age_name_classId ON student(age, name, classId);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND name = 'abc' AND classId > 20;

在这里插入图片描述

小贴士：
在应用开发中范围查询，例如金额查询、日期查询往往都是范围查询，务必将查询条件放置在 where 语句的最后。

8.1.5 不等于(!=或<>)索引失效

如果查询条件是等于，可以利用索引的有序性精确查找。但是如果查询条件是不等于，就需要遍历全部数据后进行判断了，这样就无法使用索引了。

# 不等于(!=或<>)索引失效
CREATE INDEX idx_name ON student(name);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name <> 'abc';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name != 'abc' ;

在这里插入图片描述

8.1.6 is null、is not null 索引失效问题

is null 一定会走索引，跟数据量没有关系，其与等值符号一个逻辑。null 值索引字段通常是放在 B+Tree 最左边。
is not null 跟返回的数据量有关，返回数据量少则走索引，大于总数据的某个百分比不走索引（is not null 的数据占全表数据的比值比较大时，使用索引就没有意义了，相当于查出全部数据后再进行一次回表操作，这样还不如一开始直接不使用索引）。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL; -- ref 5
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL; -- ALL

在这里插入图片描述

8.1.7 like 以通配符 % 开头索引失效

如果使用前通配符 % 查询，可能会导致索引失效，这是因为大多数索引默认是按照从左到右的顺序进行匹配的。如果通配符 % 出现在开头，索引就无法按照从左到右的顺序进行匹配，只能遍历全部数据。
如果使用后通配符 % 查询，则可以使用索引，匹配以某值开头的数据。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE '%abc';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name LIKE 'abc%';

在这里插入图片描述

但是还有一些特殊情况，比如：查询的字段刚好使用了覆盖索引时，前 % 查询也可以使用到索引：

EXPLAIN SELECT SQL_NO_CACHE id, name FROM student WHERE name LIKE '%abc';

在这里插入图片描述

like 查询以 % 开头使用了索引的原因就是使用了覆盖索引。
使用覆盖索引无需回表便可以查询到所需字段，因为 B+Tree 非聚簇索引中叶子节点并没有存储行记录的完整信息，所以其大小要远小于聚簇索引，因此可以减少大量的 I/O 操作，减少耗时，所以会使用到索引。

8.1.8 OR 前后存在非索引的列，索引失效

在 where 子句中，如果在 or 前的条件列进行了索引，而在 or 后的条件列没有进行索引，则索引失效。也就是说，or 前后的两个条件中的列都是索引列时，查询中才使用索引。

or 前后都是索引列时，会分别对两个字段进行扫描，然后将两个结果集合并，这样就避免了全表扫描，提升查询效率。

EXPLAIN SELECT  SQL_NO_CACHE * FROM student WHERE age = 30 OR classId = 100; -- 假设age有索引，classId无索引，这时就索引失效

在这里插入图片描述

# 给classId创建索引
CREATE INDEX idx_classid ON student(classId);
EXPLAIN SELECT  SQL_NO_CACHE * FROM student WHERE age = 30 OR classId = 100; -- index_merge 5,5

在这里插入图片描述

8.1.9 小结

针对查询中是否能使用索引，给出一些建议：

对于单列索引，尽量选择针对当前 query 过滤性更好的索引。
在选择组合索引的时候，当前 query 中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
在选择组合索引的时候，尽量选择能够包含当前 query 中的 where 子句中更多字段的索引。
在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。

8.2 查询优化

仍然先提前准备数据，新建两张表：type、books，并插入数据。

# 关联查询优化
##  数据准备
CREATE TABLE IF NOT EXISTS type(
	id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	card INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS books(
	bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	card INT(10) UNSIGNED NOT NULL,
	PRIMARY KEY (bookid)
);

8.2.1 关联查询优化

01、外连接

⭐ 没有索引，左连接查询：

# 没有任何索引的情况下
EXPLAIN SELECT SQL_NO_CACHE * FROM type LEFT JOIN books ON type.card = books.card;

在这里插入图片描述
⭐ 给被驱动表添加索引后，左连接查询：

# 给被驱动表添加索引
CREATE INDEX idx_x_card ON books(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM type LEFT JOIN books ON type.card = books.card;

在这里插入图片描述
可以看到被驱动表的 type 变成了 ref，rows 也比较明显。这是由左连接特性决定的。left join 条件用于确定如何从右表搜索行，而左表是包含所有，所以右边应该作为关键点，一定需要建立索引。

⭐ 给驱动表添加索引后，左连接查询：

# 给驱动表添加索引
CREATE INDEX idx_y_card ON type(card);
EXPLAIN SELECT SQL_NO_CACHE * FROM type LEFT JOIN books ON type.card = books.card;

在这里插入图片描述

结论：左连接查询时，给驱动表添加索引，无法避免全表扫描；给被驱动表添加索引，可以避免全表扫描。

02、内连接

先将两表的索引删除，再进行下面的验证。

DROP INDEX idx_x_card ON books;
DROP INDEX idx_y_card ON type;

⭐ 没有索引，内连接查询：

# 没有索引，内连接查询
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN books ON type.card = books.card;

在这里插入图片描述

⭐ 添加索引后，内连接查询：

CREATE INDEX idx_x_card ON books(card);
CREATE INDEX idx_y_card ON type(card);

# 结论：对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN books ON type.card = books.card;

在这里插入图片描述

⭐ 删除上面的任意一个索引，内连接查询：

# 结论：对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表
DROP INDEX idx_x_card ON books;-- 这里为了说明结论，故将上面作为被驱动表books的中的索引删除，则type表就被当作被驱动表
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN books ON type.card = books.card;

在这里插入图片描述

⭐ 再向 type 表中新增 20 条数据后，内连接查询：

# 此时books表中有20条数据，type表中有40条数据
# 保证两个表都有索引
CREATE INDEX idx_x_card ON books(card);
# 结论：对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表（小表驱动大表）。
EXPLAIN SELECT SQL_NO_CACHE * FROM type INNER JOIN books ON type.card = books.card;

在这里插入图片描述

结论：

对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的。
对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在的表会被作为被驱动表。
对于内连接来说，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表（小表驱动大表）。

03、join 语句原理

新建两表 f1、f2，并随便插入几条数据：

CREATE TABLE a(f1 INT, f2 INT, INDEX(f1)) ENGINE=INNODB;
CREATE TABLE b(f1 INT, f2 INT) ENGINE=INNODB;
INSERT INTO a VALUES(1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
INSERT INTO b VALUES(3,3),(4,4),(5,5),(6,6),(7,7),(8,8);

join 方式连接多个表，本质就是各个表之间数据的循环匹配。MySQL 5.5 之前，只支持一种表间关联方式，就是嵌套循环，如果关联表的数据量很大，则 join 关联的执行时间会非常长。在 MySQL 5.5 以后的版本中，MySQL 通过引入 BNLJ 算法来优化嵌套查询。

有下面一条 SQL 语句：

EXPLAIN SELECT * FROM A JOIN B ON A.id = B.aid;

⭐ 简单嵌套循环连接（Simple Nested-Loop Join）

简单嵌套循环连接算法很简单，就是从表 A 中取出一条数据 1，遍历表 B，将匹配到的数据放到 result…依次类推，驱动表 A 中的每一条记录与被驱动表 B 的记录进行判断：
在这里插入图片描述
这个语句的执行流程是这样的：

从表 A 中取出一行数据 R；
从数据行 R 中，取出 a 字段到表 B 里去查找；
取出表 B 中满足条件的行，跟 R 组成一行，作为结果集的一部分；
重复步骤 1 到 3，直到表 A 的末尾循环结束。

这种方式的效率是非常低的，假如表 A 中有 100 条数据，表 B 中有 1000 条数据，则会遍历 100 * 1000 = 10 万次，开销如下：

开销统计	SNLJ
外表扫描次数	1
内表扫描次数	A
读取记录数	A + B * A
JOIN比较次数	B * A
回表读取记录次数	0

MySQL 肯定不会这么粗暴地取进行表地连接，所以就出现了后面的两种对 Nested-Loop Join 优化算法。

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

Index Nested-Loop Join 优化的思路主要是为了减少内层表数据的匹配次数，所以要求被驱动表上必须有索引才行。通过外层表匹配条件直接与内层表索引进行匹配，避免和内层表的每条记录进行比较，这样就极大地减少了对内层表的匹配次数。

先遍历表 A，然后根据从表 A 中取出的每行数据中的 a 值，再去表 B 中查找满足条件的记录。这个形式类似于我们代码中的嵌套查询，并且可以使用上被驱动表的索引。

在这里插入图片描述
在这个流程里，执行步骤是这样的：

对驱动表 A 做了全表扫描；
对于每行 R，根据字段 a 去 B 表中查找，走的是树搜索过程。因为构造的数据都是一一对应的，所以每次的搜索过程都只扫描一行；

开销如下：

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

给被驱动表加索引效率是非常高的，但是如果所以不是主键索引，还要进行一次回表查询。所以，被驱动表的索引是主键索引时，效率会更高。

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

如果存在索引，那么会使用 index 的方式进行 join，如果 join 的列没有索引，被驱动表要扫描的次数就太多了。每次访问被驱动表，其表中的记录都会被加载到内存中，然后再从驱动表中取出一条与其匹配，匹配结束后清除内存…这样周而复始，大大地增加了 I/O 的次数。为了减少被驱动表的 I/O 次数，就出现了 Block Nested-Loop Join 的方式。

不再是逐条获取驱动表的数据，而是一块一块地获取，引入了 join buffer 缓冲区，将驱动表 join 相关地部分数据列缓存到 join buffer 中，然后全表扫描被驱动表。被驱动表地每一条记录一次性和 join buffer 中所有驱动表记录进行匹配（内存中操作），将简单嵌套循环中地多次比较合并成一次，降低了被驱动表的访问频率。

在这里插入图片描述

注意：
这里缓存的不只是关联表的列，select 后面的列也会缓存起来。
在一个有 N 个 join 关联的 SQL 中会分配 N-1 个 join buffer。所有查询的时候尽量减少不必要的字段，可以让 join buffer 中可以存放更多的列。

开销如下：

开销统计	SNLJ	INLJ	BNLJ
外表扫描次数	1	1	1
内表扫描次数	A	0	A * used_cloumn_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

04、小结

保证被驱动表的 join 字段已经创建了索引。
需要 join 的字段，数据类型保持绝对一致。
left join 时，选择小表作为驱动表，大表作为被驱动表（小表驱动大表，另外，查询字段少的表很可能被当作驱动表，因为查询字段少，在 join buffer 中一次性可以加载更多的数据，扫描的次数更少）。减少外层循环的次数。
inner join 时，MySQL 会自动将小结果集的表选为驱动表。
能够直接多表关联的尽量直接关联，不用子查询。如果有子查询，建议将子查询 SQL 拆开结合程序多次查询，或者使用 join 来代替子查询。
衍生表创建不了索引。

8.2.2 子查询优化

MySQL 从 4.1 版本开始支持子查询，使用子查询可以进行 select 语句的嵌套查询，即：以一个 select 查询的结果作为另一个 select 语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作。

子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子查询的执行效率不高。原因如下：

执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的 CPU 和 IO 资源，产生大量的慢查询。
子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。
对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

在 MySQL 中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。

下面举了两个例子来说明。

⭐ 举例一

查询学生表中是班长的学生信息：

CREATE INDEX idx_monitor ON class(monitor);
# 使用子查询
EXPLAIN SELECT
	* 
FROM
	student stu1 
WHERE
	stu1.stuno IN (
	SELECT
	stu1.stuno IN ( SELECT monitor FROM class c WHERE monitor IS NOT NULL ));

在这里插入图片描述

# 使用多表查询
EXPLAIN SELECT stu1.* FROM student stu1 JOIN class c ON stu1.stuno = c.monitor where c.monitor IS NOT NULL;

在这里插入图片描述

⭐ 举例二

查询所有不为班长的同学：

# 查询不为班长的学生信息
# 子查询
EXPLAIN SELECT SQL_NO_CACHE a.* FROM student a  WHERE a.stuno NOT IN (SELECT b.monitor FROM class b WHERE b.monitor IS NOT NULL);

在这里插入图片描述

# 连接查询
EXPLAIN SELECT SQL_NO_CACHE a.* FROM student a LEFT OUTER JOIN class b ON a.stuno = b.monitor WHERE b.monitor IS NULL;

在这里插入图片描述

结论：尽量不要使用 not in 或者 not exists，使用 left join xxx on xxx where xx is null 替代。

8.2.3 排序优化（order by）

在 MySQL 中，支持两种排序方式，分别是 FileSort 和 Index 排序：

Index 排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
FileSort 排序则一般在内存中进行排序，占用 CPU 较多。如果待排序结果比较大，会产生临时文件 I/O 到磁盘进行排序的情况，效率较低。

优化建议：

SQL 中，可以在 where 子句和 order by 子句中使用索引，目的是在 where 子句中避免全表扫描，在 order by 子句中避免使用 FileSort 排序。当然，某些情况下全表扫描，或者 FileSort 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
尽量使用 Index 完成 order by 排序。如果 where 和 order by 后面是相同的列就是用单列索引列；如果不同就使用联合索引。
无法使用 Index 时，需要对 FileSort 方式进行调优。

下面举例之前，先删除 student 和 class 表中的非主键索引：

CALL proc_drop_index('study01', 'student');
CALL proc_drop_index('study01', 'class');

01、没有索引

# 过程一：没有索引的情况
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age, classId;
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age, classId LIMIT 10;

在这里插入图片描述

02、order by 时不 limit，索引失效

给 student 表创建联合索引：

# 过程二：order by时不limit，索引失效
# 创建索引
CREATE INDEX idx_age_classid_name ON student(age, classId, name);

# 在查询时，需要在查询完二级索引后执行回表操作，当数据量过大，回表次数过多，比较耗时。所以，干脆一次性将数据加载完，在内存中排序。
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age, classId; -- ALL  Using filesort

在这里插入图片描述

# 使用了覆盖索引，查询的字段刚好是联合索引中的字段，不用回表，直接比较
EXPLAIN SELECT SQL_NO_CACHE age, classId FROM student ORDER BY age, classId; -- index  Using index

在这里插入图片描述

# 增加limit过滤条件，排好序后取limit条数据，比全部回表操作快
EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age, classId LIMIT 10; -- index

在这里插入图片描述

03、order by 时顺序错误，索引失效

order by 时也需遵循最左前缀原则，否则索引失效。

继续给 student 表创建索引：

# 过程三：order by 时顺序错误，索引失效
CREATE INDEX idx_age_classid_stuno ON student(age, classId, stuno);

EXPLAIN SELECT * FROM student ORDER BY classId LIMIT 10; -- ALL
EXPLAIN SELECT * FROM student ORDER BY classId, name LIMIT 10; -- ALL
EXPLAIN SELECT * FROM student ORDER BY age, classId, stuno LIMIT 10; -- index
EXPLAIN SELECT * FROM student ORDER BY age, classId LIMIT 10; -- index
EXPLAIN SELECT * FROM student ORDER BY age LIMIT 10; -- index

此查询中 order by 后的第一个字段必须是 age 才可以使用索引。

04、order by 时规则不一致，索引失效（顺序错，不索引；方向反，不索引）

order by 时，其后面的字段需遵循最左前缀原则，且排序条件需一致，否则索引失效。

EXPLAIN SELECT * FROM student ORDER BY age DESC, classId ASC LIMIT 10; -- ALL
EXPLAIN SELECT * FROM student ORDER BY classId DESC, name DESC LIMIT 10; -- ALL
EXPLAIN SELECT * FROM student ORDER BY age ASC, classId DESC LIMIT 10; -- ALL
EXPLAIN SELECT * FROM student ORDER BY age DESC, classId DESC LIMIT 10; -- index Backward index scan
EXPLAIN SELECT * FROM student ORDER BY age ASC, classId ASC LIMIT 10; -- index

05、无过滤，不索引

如果 where 中使用了最左前缀字段与常量等值比较，则 order by 可以使用索引，否则索引失效。

# 过程五：无过滤，不索引
EXPLAIN SELECT * FROM student WHERE age = 45 ORDER BY classId; -- ref 5
EXPLAIN SELECT * FROM student WHERE age = 45 ORDER BY classId, name; -- ref 5
EXPLAIN SELECT * FROM student WHERE classId = 45 ORDER BY age; -- ALL
EXPLAIN SELECT * FROM student WHERE classId = 45 ORDER BY age LIMIT 10; -- index 73

06、尽量避免使用 FileSort 方式排序

先清除 student 表上的索引，只留主键：

CALL proc_drop_index('study01', 'student');

场景：查询年龄为 30 岁的，且学生编号小于 101000 的学生，按用户名称排序。

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno < 101000 ORDER BY name; -- ALL Using filesort

在这里插入图片描述
优化方案如下：

⭐ 方案一

# 方案一: 为了去掉filesort我们可以创建下面索引
CREATE INDEX idx_age_name ON student(age, name);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno < 101000 ORDER BY name; -- ref

在这里插入图片描述

⭐ 方案二

# 方案二: 尽量让where的过滤条件和排序使用上索引
DROP INDEX idx_age_name ON student;
CREATE INDEX idx_age_stuno_name ON student (age, stuno, name);
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 30 AND stuno <101000 ORDER BY name ; -- range Using filesort

在这里插入图片描述
由此可见，使用了 FileSort + index 运行速度更快了。

结论：

两个索引同时存在，MySQL 自动选择最优的方案。但是，随着数据量的变化，选择的索引也会随之变化的。
当【范围条件】和【group by 或 order by】的字段出现二选一时，优先观察条件字段的过滤数量。如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之则相反。

07、filesort 算法：双路排序和单路排序

排序的字段如果不在索引列上，则 filesort 会有两种算法：双路排序和单路排序。

⭐ 双路排序（慢）

MySQL 4.1 之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据。取出排序字段进行排序，排序完成后再次回表查询所需要的其他字段。

对磁盘进行两次扫描，I/O 耗时较大，查询性能比较低。所以，在 MySQL 4.1 之后，出现了单路排序。

⭐ 单路排序（快）

从磁盘一次性读取需要的所有列，按照 order by 列在 buffer 对它们进行排序，避免了二次读取数据，并把随机 I/O 变成了顺序 I/O。但是它会用到更多的空间，因为它会把数据都加载到内存中。

⭐ 结论

单路总体而言要好于多路，但是也有问题：它要占用很多空间，因为它是把所有字段都取出来，所以有可能取出的数据总大小超出了 sort_buffer 的容量，导致每次只能取 sort_buffer 容量大小的数据进行排序，排序完再取 sort_buffer 容量大小…从而多次 I/O。

优化策略：

尝试提高 sort_buffer_size。
尝试提高 max_length_for_sort_data。

可以提高这个参数的大小，但是也不能设置的太高。如果设置的太高，数据总容量超出 sort_buffer_size 的概率就增大，会造成高的磁盘 I/O 活动和低的处理器使用率。
Order by 时 select * 是一个大忌。最好只 Query 需要的字段。

8.2.4 分组优化（group by）

group by 使用索引的原则几乎与 order by 一致，group by 即使没有过滤条件用到索引，也可以直接使用索引。
group by 先排序再分组，遵循索引键的最左前缀原则。
当无法使用索引列时，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置。
where 效率要高于 having，查询条件能写在 where 就不要写在 having 中。
减少使用 order by、group by、distinct，这些语句比较耗费 CPU。
包含了 order by、group by、distinct 这些查询的语句，where 条件过滤出来的结果集尽量保持在 1000 行以内，否则查询会非常慢。

8.2.5 分页查询优化

一般分页查询时，通过创建覆盖所有能够比较好的提高性能。比如 limit 2000000，10 这个语句，需要 MySQL 排序前 2000000 条记录，仅仅返回 2000000 - 2000010 之间的记录，其他记录丢弃，查询排序的代价非常大。

EXPLAIN SELECT * FROM student LIMIT 2000000, 10; -- ALL

在这里插入图片描述
有两个优化思路：

⭐ 优化思路一

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

EXPLAIN SELECT * FROM student t, (SELECT id FROM student ORDER BY id LIMIT 2000000, 10) a WHERE t.id = a.id;

在这里插入图片描述

⭐ 优化思路二

对于主键自增的表，可以把 limit 查询转换成某个位置的查询。

EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10;

在这里插入图片描述

8.2.6 优先考虑覆盖索引

一个索引包含了满足查询结果的数据叫做覆盖索引。简单来说就是，索引列 + 主键包含 select 到 from 之间查询的列。

01、举例一

# 4. 覆盖索引（一个索引包含了满足查询结果的数据就叫做覆盖索引）
DROP INDEX idx_age_stuno ON student;
CREATE INDEX idx_age_name ON student (age, name);

## 举例一
EXPLAIN SELECT * FROM student WHERE age <> 20; -- ALL
EXPLAIN SELECT age, name FROM student WHERE age <> 20; -- index idx_age_name
EXPLAIN SELECT id, age, name, classid FROM student WHERE age <> 20; -- ALL

在这里插入图片描述

02、举例二

## 举例二
EXPLAIN SELECT * FROM student WHERE name LIKE '%abc'; -- ALL
EXPLAIN SELECT id, age, name FROM student WHERE name LIKE '%abc'; -- index idx_age_name

在这里插入图片描述

03、覆盖索引的利弊

好处：

避免 InnoDB 表进行索引的二次查询（回表）

对于 InnoDB 来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据，在查询到相应的键值后，还需要通过主键进行二次查询才能获取行数据。在覆盖索引中，二级索引的键值中就可以获取到所需要的数据，避免了对主键的二次查询，减少了 I/O 操作，提升了查询效率。
可以把随机 I/O 变成顺序 I/O，加快查询效率

由于覆盖索引是按键值的顺序存储的，对于 I/O 密集型的范围查找来说，对比随机从磁盘读取每一行的数据 I/O 要少得多，所以利用覆盖索引在访问时也可以把磁盘的随机读取 I/O 转变成索引查找的顺序 I/O。

弊端： 索引字段的维护是有代价的。所以，建立冗余索引来支持覆盖所有时就需要权衡考虑了。

总之，覆盖索引可以减少树的搜索次数，显著提升查询性能，但是也许权衡利弊，谨慎使用。

8.2.7 给字符串添加索引

MySQL 是支持前缀索引的。默认地，如果创建索引的语句不指定前缀长度，那么索引就会包含整个字符串。

比如下面两个 SQL：

mysql> alter table teacher add index index1(email);

mysql> alter table teacher add index index2(email(6));

两种不同的定义在数据结构和存储上是有区别的：

在这里插入图片描述

⭐ 情况一

如果使用的是 index1（email 整个字符串的索引结构），执行顺序是这样的：

从 index1 索引树找到满足索引值是 ‘zhangssxyz@xxx.com’ 的这条记录，取得 ID2 的值；
到主键上查到主键值是 ID2 的行，判断 email 的值是正确的，将这行记录加入结果集；
取 index1 索引树上刚刚查到的位置的下一条记录，发现已经不满足 email = ‘zhangssxyz@xxx.com’ 的条件了，循环结束。

这个过程中，只需要回主键索引取一次数据，所以系统认为只扫描了一行。

⭐ 情况二

如果使用的是 index2（email(6) 索引结构），执行顺序是这样的：

从 index2 索引树找到满足索引值是 ‘zhangs’ 的记录，找到的第一个是 ID1；
到主键上查到主键值是 ID1 的行，判断出 email 的值不是 ‘zhangssxyz@xxx.com’，这行记录丢弃；
取 index2 上刚刚查到的位置的下一条记录，发现仍然是 ‘zhangs’，取出 ID2，再到 ID 索引上取整行然后判断，这次值对了，将这行记录加入结果集；
重复上一步，直到在 idxe2 上取到的值不是 ‘zhangs’ 时，循环结束。

也就是说使用前缀索引，定义好长度，就可以做到既节省空间，又不用额外增加太多的查询成本，且区分度越高越好。因为区分度越高，意味着重复的键值越少。

小贴士：使用前缀索引就不能使用覆盖所有对查询性能的优化了，在使用时需谨慎选择。

8.2.8 索引下推

索引下推（Index Condition Pushdown，简称：ICP）是 MySQL 5.6 中的新特性，是一种在存储引擎层使用索引过滤数据的优化方式。

如果没有 ICP，存储引擎会遍历索引以定位基表中的行，并将它们返回给 MySQL 服务器，由服务器评估 where 后面的条件是否保留行。
启用 ICP 后，如果部分 where 条件可以仅使用索引中的列进行筛选，则 MySQL 服务器会把这部分 where 条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行。

好处：ICP 可以减少存储引擎必须访问基表的次数和 MySQL 服务器必须访问存储引擎的次数。

但是，ICP 的加速效果取决于在存储引擎内通过 ICP 筛选掉的数据的比例。

01、使用 ICP 前后的扫描过程

⭐ 在不使用 ICP 索引扫描的过程：

步骤一：storage 层根据 B+Tree 索引快速定位到二级索引记录后，根据二级索引记录的主键值进行回表操作，将完整的用户记录返回给 server 层。

步骤二：server 层对返回的数据，判断后面的 where 条件是否成立，如果成立就将其发送给客户端；否则跳过该记录，进行步骤一，直至满足索引条件的记录都扫描过为止。

在这里插入图片描述

⭐ 使用 ICP 扫描的过程

步骤一：storage 层首先将 index key 条件满足的索引记录区间确定，不着急回表。然后在索引上使用 index filter 进行过滤。将满足的 index filter 条件的索引记录才去回表取出整行记录返回 server 层。不满足 index filter 条件的索引记录丢弃，不回表，也不会返回 server 层。

步骤二：server 层对返回的数据，使用 table filter 条件做最后的过滤，直至将返回的所有记录都扫描过为止。

在这里插入图片描述

使用前后的成本差别：
使用 ICP 前，存储层多返回了需要被 index filter 过滤掉的整行记录。
使用 ICP 后，直接就去掉了不满足 index filter 条件的记录，省去了回表和传递到 server 层的成本。

ICP 的加速效果取决于在存储引擎内通过 ICP 筛选掉的数据的比例。

02、ICP 的使用条件

只能用于二级索引。
explain 显示的执行计划中 type 值（join 类型）为 range、ref、eq_ref 或 ref_or_null。
并非全部 where 条件都可以使用 ICP 筛选，如果 where 条件的字段不在索引列中，还是要读取整表的记录到 server 端做 where 过滤的。
ICP 可以用于 MyISAM 和 InnoDB 存储引擎。
MySQL 5.6 版本不支持分区表的 ICP 功能，5.7 版本的开始支持。
当 SQL 使用覆盖索引时，不支持 ICP 优化方法。

03、ICP 的开启/关闭

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

# 关闭索引下推
set optimizer_switch = 'index_condition_pushdown=off';

# 打开索引下推
set optimizer_switch = 'index_condition_pushdown=on';

8.2.9 其他查询优化

01、exsits 和 in 的区分

索引是个前提，选择是否使用索引还是要看表的大小，一般情况下遵循：小表驱动大表。

例如下面这两条语句：

SELECT * FROM A WHERE cc IN (SELECT cc FROM B);

SELECT * FROM A EXISTS (SELECT cc FROM B WHERE B.cc = A.cc);

IN 语句相当于先执行子查询后将结果缓存起来，然后将外表作为外层循环，缓存结果作为内层循环进行遍历。也就是说，子查询的结果集越大，循环次数就越多。

for i in A
	for j in B
		if i.cc == j.cc then...

EXISTS 语句相当于将外表作为外层循环，子查询作为内层循环，条件匹配到项后就返回结果进行下一次循环。也就是说，循环次数取决于外表的大小。

for i in B
	for j in A
		if i.cc == j.cc then...

依据 “小表驱动大表”，如果外表大于内表，就使用 IN；如果外表小于内表，就使用 EXISTS。

02、count(*) 和 count(具体字段) 的效率

在 MySQL 中统计数据表的行数，可以使用三种方式： select count(*) 、 select count(1) 和
select count(具体字段) ，使用这三者之间的查询效率是怎样的？

count(*) 和 count(1) 都是对所有结果进行 count，包含 NULL，其本质上并没有什么区别。如果有 where 子句，则是对所有符合筛选条件的数据进行统计；如果没有 where 子句，则是对数据表的数据行数进行统计。

如果采用 count(具体字段) 来统计数据行，不包含对 NULL 的统计，需要进行字段的非 NULL 判断，所以效率会低一些。另外，要尽量采用二级索引来统计（优化器会选择占用空间更小的二级索引来进行统计）。

03、关于 select (*)

在表查询中，尽量不要使用 * 作为查询的字段列表，推荐使用 select <字段列表> 查询。有两个原因：

MySQL 在解析的过程中，会通过查询数据字典将 * 按序转换成所有列明，这会大大的耗费资源和时间。
无法使用覆盖索引。

04、limit 1 对优化的影响

针对的是会扫描全表的 SQL 语句，如果可以确定只有一条，那么建议加上 limit 1，当找到一条记过的时候就不会继续扫描了，这样会加快查询速度。

如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上 limit 1 了。

8.3 普通索引 vs 唯一索引

在使用 MySQL 的过程中，随着表数据的增多，为了加快查询效率，我们会在表中建立不同类型的索引。

对于一个普通的二级索引，目的就是为了加速查询，所以我们可能会为表中的某个字段或者某些字段，建立一个普通的二级索引。

而对于唯一索引来说，由于其唯一键约束的特性，有时我们会更多地赋予其业务含义。比如具有唯一性的身份证号。

8.3.1 查询性能

MySQL 磁盘与内存交互是通过加载数据页的，一个数据页的默认大小是 16 KB，一个 int 类型的索引占 4 字节，加上头信息 6 字节，一共是 10 字节，也就是说一个页上能存放 16 * 1024 / 10 = 1638 条数。

对于主键索引，叶子节点存放的是一行真正的数据，而对于二级索引来说，在叶子节点存储的是索引字段以及对应的主键 id。

有如下 SQL：

select id from A where m = 100;

对于唯一索引来说，由于其唯一性，所以在查找到第一条记录之后，就结束查找。

对于二级索引来说，找到第一条记录后会继续向后找，判断是否还有符合条件的记录。

所以，两者的区别就在于是否继续查找下一条。一个页可以存放 1638 条记录，对于普通二级索引来说，判断下一个记录的操作对性能的消耗是非常小的。

所以，普通二级索引和唯一索引的查询性能基本是相当的。

8.3.2 更新性能

对于MySQL来说，更新一条语句的逻辑是首先读到要更新的记录，如果这个记录没有在内存里，就先加载到内存。然后执行更新的语句，之后再把变更的数据刷新到磁盘中。

但是，对于 MySQL 来说，把数据从磁盘读到内存涉及到随机 I/O，成本是非常高的。如果每次更新数据都要这么来一次的话，高性能这个指标恐怕很难保证。

所以，设计 MySQL 的大叔引入了一个叫做 change buffer 的东西。

change buffer 是一种可以持久化的缓存数据。当我们要更新数据时，如果要更新的数据不存在于内存，此时并不需要把数据从磁盘加载到内存，而是将更新操作记录在 change buffer 中，更新操作就算完成了。当下次要读取这些数据时，会把读到的数据和 change buffer 进行合并，或者叫 merge。

通过 change buffer，更新操作就不需要去读磁盘了，全程都是内存操作，性能自然可以得到极大的提升。

但是！change buffer 只对普通二级索引有效，对于唯一索引是无效的（因为在更新一条记录时，需要检查索引的唯一性约束，也就是要把数据从磁盘加载到内存进行判断，已经执行磁盘 I/O 了，就没必要再使用 change buffer 了）。

所以，普通索引的更新性能要好于唯一索引的。