书接上文，继续使用上次创建的 student 表，表里已经被我们装填了大量数据，接下来做分页查询的索引优化。
分页查询优化
普通的分页查询语句：
SELECT * FROM student LIMIT 10000,10;
这条语句是从 student 表中取出 10001 行开始的10条数据，不过看起来只查询了十条，但实际上是将 10010 条记录读取，只显示后面十条数据，抛弃前面的 10000 条记录，所以执行效率很低。
- 根据自增且连续的主键排序的分页查询
上一条语句就是这样的例子，表通过主键 id 进行排序，看一下执行计划：
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------- ---------- ------ ------------- ------ ------- ------ ------ -------- --------
1 SIMPLE student (NULL) ALL (NULL) (NULL) (NULL) (NULL) 97664 100.00 (NULL)
type 类型 ALL，全表扫描，查询效率很低。对于这种情况，可以改写一下，id 连续且自增，直接按照 id 进行筛选，查询 10000 后五条的数据：
EXPLAIN SELECT * FROM student WHERE id > 10000 LIMIT 10;
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------- ---------- ------ ------------- ------- ------- ------ ------ -------- -------------
1 SIMPLE student (NULL) range PRIMARY PRIMARY 4 (NULL) 48832 100.00 Using where
显然，使用了索引，扫描的行数少了很多，执行效率也提高了很多。但这样的改写对于数据有很高的要求，比如数据不能物理删除，物理删除导致主键非连续，从而使得结果不一致，使用逻辑删除的情况可以采用上面的优化方法。
这种方式必须满足这两个条件：
- 主键连续且自增
- 结果集按照主键排序
- 根据非主键字段排序的分页查询
SELECT * FROM student ORDER BY NAME LIMIT 10000, 10;
结果集
id name age school start_time
------ -------- ------ ------------ ---------------------
19002 老18999 18999 老大小学 2024-03-29 16:03:21
22 老19 19 老大小学 2024-03-29 16:01:23
193 老190 190 老大小学 2024-03-29 16:01:24
1903 老1900 1900 老大小学 2024-03-29 16:01:34
19003 老19000 19000 老大小学 2024-03-29 16:03:21
19004 老19001 19001 老大小学 2024-03-29 16:03:21
19005 老19002 19002 老大小学 2024-03-29 16:03:21
19006 老19003 19003 老大小学 2024-03-29 16:03:21
19007 老19004 19004 老大小学 2024-03-29 16:03:21
19008 老19005 19005 老大小学 2024-03-29 16:03:21
查询计划
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------- ---------- ------ ------------- ------ ------- ------ ------ -------- ----------------
1 SIMPLE student (NULL) ALL (NULL) (NULL) (NULL) (NULL) 97664 100.00 Using filesort
Usiing filesort，ALL 全表扫描，使用文件排序，并没有使用 name 字段的索引，扫描整个索引并查找到没索引的行的成本可能比全表扫描更高，所以优化器放弃使用索引。优化的关键是让排序时返回的字段尽可能少，可以用下面的优化：
SELECT id FROM student ORDER BY NAME LIMIT 10000, 10) t WHERE student.id = t.id;
查询结果：
id name age school start_time id
------ -------- ------ ------------ ------------------- --------
19002 老18999 18999 老大小学 2024-03-29 16:03:21 19002
22 老19 19 老大小学 2024-03-29 16:01:23 22
193 老190 190 老大小学 2024-03-29 16:01:24 193
1903 老1900 1900 老大小学 2024-03-29 16:01:34 1903
19003 老19000 19000 老大小学 2024-03-29 16:03:21 19003
19004 老19001 19001 老大小学 2024-03-29 16:03:21 19004
19005 老19002 19002 老大小学 2024-03-29 16:03:21 19005
19006 老19003 19003 老大小学 2024-03-29 16:03:21 19006
19007 老19004 19004 老大小学 2024-03-29 16:03:21 19007
19008 老19005 19005 老大小学 2024-03-29 16:03:21 19008
执行计划
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ---------- ---------- ------ ------------- ------------------- ------- ------ ------ -------- -------------
1 PRIMARY <derived2> (NULL) ALL (NULL) (NULL) (NULL) (NULL) 10010 100.00 (NULL)
1 PRIMARY student (NULL) eq_ref PRIMARY PRIMARY 4 t.id 1 100.00 (NULL)
2 DERIVED student (NULL) index (NULL) idx_name_age_school 140 (NULL) 10010 100.00 Using index
可以看到查询结果是一致的，执行计划中 extra 列也从 Using filesort 变成 Using index，可以对比查询时间，显然看到查询效率变高了。
Join 关联查询优化
创建表
CREATE TABLE `temp1` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`first` INT(11) DEFAULT NULL,
`second` INT(11) DEFAULT NULL,
PRIMARY KEY(`id`),
KEY `idx_first` (`first`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
DROP PROCEDURE IF EXISTS insert_temp1;
DELIMITER ;;
CREATE PROCEDURE insert_temp1()
BEGIN
DECLARE i INT;
SET i=1;
WHILE(i<=100)DO
INSERT INTO temp1(`first`,`second`) VALUES(i, i);
SET i=i+1;
END WHILE;
END;;
DELIMITER ;
CALL insert_temp1();
先来看一个查询语句
SELECT * FROM student INNER JOIN temp1 ON student.id=temp1.id;
执行计划
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------- ---------- ------ ------------- ------- ------- ----------------------- ------ -------- --------
1 SIMPLE temp1 (NULL) ALL PRIMARY (NULL) (NULL) (NULL) 100 100.00 (NULL)
1 SIMPLE student (NULL) eq_ref PRIMARY PRIMARY 4 multiplefather.temp1.id 1 100.00 (NULL)
MySQL 表关联常见有两种算法
- Nested-Loop Join 算法
- Block Nested-Loop Join 算法
Nested-Loop Join 算法
嵌套循环连接算法（NLJ）一次一次循环地从第一张表（驱动表）中读取行，这行数据取出关联字段，再根据关联字段在另一张表（被驱动表）里取到满足条件的数据，然后取出两张表的结果合集。
从执行计划中可以看出，驱动表是 temp1。id 相同，按顺序执行，先执行的是驱动表，优化器一般优先选择小标做驱动表，而不是按照 Inner Join 使用时两张表的顺序。
当使用 Left Join 时，左表是驱动表，右表是被驱动表，当使用 Right Join 时，右表是驱动表，左表是被驱动表，当使用 Join 时，MySQL 会选择小表作为驱动表，大表作为被驱动表。
使用了 NLJ 算法，一般 Join 语句中，如果执行计划 Extra 中未出现 Using Join Buffer 表示使用的 Join 算法是 NLJ。
综合这些信息，再看上面语句的执行流程：
- 从小表 temp1 中取出一条记录
- 从该语句中找到关联字段，再去 student 表查询
- 取出 student 表中满足条件的行，跟 temp1 表中的结果进行合并，返回并重复执行这三步。
整个过程中，会读取驱动表 temp1 的所有数据，遍历出关联字段 id 的值，再根据 id 的值索引扫描 student 表中的对应行，因为 temp1 表中有 100 条数据，扫描 100 次 student 表的索引，1次扫描可以理解为最终只扫描 student 表一行完整数据，也就是总共 student 表也扫描了 100 行。因此整个过程扫描了 200 行。但如果被驱动表关联字段没有索引，使用 NLJ 算法性能较低。下面对比两种算法。
Block Nested-Loop Join（BNL） 算法
基于块的嵌套循环连接算法会先把驱动表的数据都读取到 join_buffer 中，然后扫描被驱动表，把被驱动表的每一行数据取出跟 join_buffer 中的数据做对比。
执行下面的查询计划
EXPLAIN SELECT * FROM temp1 INNER JOIN student ON temp1.second=student.age;
执行计划
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
------ ----------- ------- ---------- ------ ------------- ------ ------- ------ ------ -------- --------------------------------------------
1 SIMPLE temp1 (NULL) ALL (NULL) (NULL) (NULL) (NULL) 100 100.00 (NULL)
1 SIMPLE student (NULL) ALL (NULL) (NULL) (NULL) (NULL) 97664 10.00 Using where; Using join buffer (hash join)
没想到吧，不是 Using join buffer(Block Nested Loop)，是因为 MySQL 在 8.0.20 版本以后，就已经把 BNL 移除了，使用 hash join 代替。我用的 MySQL 版本是 8.0.28，复现不了 BNL，但思路还是要总结一下的。
BNL 算法语句执行流程是：
- 把 temp1 表所有数据都放入到 join_buffer 中
- 把表 student 中的每一行取出来，跟 join_buffer 中的数据做对比
- 返回满足 join 条件的数据
整个过程对表 temp1 和 student 表都做了一次全表扫描，因此扫描的总行数为 100000（表student 的数据总量）+ 100（表 temp1 的数据总量）=100100，join_buffer 中的数据都是无序的，因此对表 student 中的每行数据都要做 100 次判断，所以内存中判断总数是 100000*100=1000 万次。如果 temp1 表的数据很大，在 join_buffer 中放不下，就会将数据分段，分段放进 join_buffer，再进行判断。
对比 BNL 和 NLJ 两种算法：
如果使用 NLJ 算法，扫描的行数是 100 * 100000=1000 万次，但这个是磁盘扫描。而 BNL 算法，是在内存中进行判断，相比磁盘扫描，肯定快得多。
如果被驱动表的关联字段，没有索引，那就选择 BNL 算法，有索引就选择 NLJ 算法。
而 hash join，基本思想是根据驱动表在内存中建立一个 hash table，然后用大表来探测这个 hash table。这样这需要遍历一遍内表，就可以完成 join 操作，输出匹配的记录。NLJ 的复杂度是驱动表记录数*被驱动表的记录数，hash join 只需要遍历一次内表就可以完成查询，效率有所提升。
in 和 exists 优化
主要原则就是小表驱动大表，把前面的表称为 A 表，后面的表称为 B 表。当 B 表的数据小于 A 表的数据时，in 优于 exists。
select * from A where id in (select id from B);
A 表的数据小于 B 表时，exists 优于 in。
select * from A where exists (select 1 from B where B.id=A.id);
总结
这篇文章主要对分页查询、join 连接查询以及 in 和 exists 的索引查询优化，也比较浅显，还要更努力的学习，希望各位都能从中有所收获。
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
