需求
做过开发的同学，对分页肯定不会陌生，因为很多前台页面展示，为了更好的展示数据，就会用到分页，所以如何写一个高性能的分页SQL语句，是每一个开发人员需要掌握的技能。
分页SQL
这里给大家写一个分页SQL语句。
mysql> show create table sbtest1\G;*************************** 1. row *************************** Table: sbtest1Create Table: CREATE TABLE `sbtest1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `k` int(10) unsigned NOT NULL DEFAULT '0', `c` char(120) NOT NULL DEFAULT '', `pad` char(60) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `k_1` (`k`), KEY `idx_sbtest1_k_pad` (`k`,`pad`)) ENGINE=InnoDB AUTO_INCREMENT=5000001 DEFAULT CHARSET=utf8 MAX_ROWS=10000001 row in set (0.00 sec)
这里准备了一张500W记录的测试表
mysql> select count(*) from sbtest1;+----------+| count(*) |+----------+| 5000000 |+----------+1 row in set (36.23 sec)
测试分页SQL语句
selecta.*fromsbtest1awherek>=10orderbyklimit1000000,10;
DBA看一条SQL语句是否有性能问题，首先会看看SQL语句的执行计划，这里我们也一起先看看执行计划
mysql> explain select a.* from sbtest1 a where k>=10 order by k limit 1000000,10;+----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-----------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-----------------------------+| 1 | SIMPLE| a| NULL | ALL | k_1,idx_sbtest1_k_pad | NULL | NULL | NULL | 4804854 | 50.00 | Using where; Using filesort |+----+-------------+-------+------------+------+-----------------------+------+---------+------+---------+----------+-----------------------------+1 row in set, 1 warning (0.36 sec)
从执行计划上可以看到，possible_keys有k_1,idx_sbtest1_k_pad 两个，可是SQL语句真正执行的时候，并没有使用到索引，从key为NULL就可以知道，不走索引，性能基本会有问题，怎么办。
有的朋友会说，让SQL走索引啊，可是MySQL数据库的优化器，为什么不让SQL走索引呢。原来优化器会在索引存在的情况下，通过符合RANGE范围的条数和总数的比例来选择是使用索引还是进行全表遍历，当需要读取的数据超过一个临界值时，优化器会放弃从索引中读取而改为进行全表扫描。
有一定技术储备的朋友就会开始支招，强制走索引，那么来看看强制走索引的效果。
mysql> explain select a.* from sbtest1 a FORCE INDEX(k_1) where k>=10 order by k limit 1000000,10;+----+-------------+-------+------------+-------+---------------+------+---------+------+---------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+-------+---------------+------+---------+------+---------+----------+-----------------------+| 1 | SIMPLE| a| NULL | range | k_1 | k_1 | 4 | NULL | 2402427 | 100.00 | Using index condition |+----+-------------+-------+------------+-------+---------------+------+---------+------+---------+----------+-----------------------+1 row in set, 1 warning (0.35 sec)
看执行计划，已经走了索引(k_1)，那真实的执行时间来一起看看。
mysql> select a.* from sbtest1 a FORCE INDEX(k_1) where k>=10 order by k limit 1000000,10;+---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+| id| k | c | pad |+---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+| 625795 | 2491354 | 97204650565-29444916083-97115589676-40957844519-94449833558-65804998462-60532777657-14143069112-49633654712-45111409020 | 62512103246-48607606334-30155359433-33452555729-53895828100 || 699397 | 2491354 | 32339675223-04668542998-80231026645-48395677535-02765586559-55831506063-42378875097-54580937047-37599981559-17618207647 | 29107597440-02396388801-60803416516-3514546-47217028215 || 710717 | 2491354 | 90067622196-22944775363-10477154003-65718440755-24111522288-05149673185-81902474191-32337446442-26659698531-19766723590 | 86804024266-77223258212-31659403967-01772860244-99013209389 || 743592 | 2491354 | 93848584940-19743659878-81447583142-56888444937-65254387827-69794611882-41783362491-17920606195-80837332876-61005171709 | 18905422789-64525703285-24594762685-66972415320-18032736842 || 857338 | 2491354 | 65447608668-53055131627-49484988592-56750024449-62428854104-09419771096-35386440425-85674387129-76043542898-38871555063 | 00988589677-01502672421-94556913009-28600249646-25815466940 || 865647 | 2491354 | 40627346200-31156486805-14979452789-43576795710-31471455633-50576965996-46658943363-33235956457-29071759844-01216446475 | 77768402076-66310138953-89244891269-40818622962-29528856272 || 867735 | 2491354 | 60189133283-00412480472-85765718780-99930014238-27634346893-05196495869-38494141485-78725336333-97665769760-66018168229 | 51330855469-21834479110-70983834788-18568325279-18729638102 || 911459 | 2491354 | 45484218970-22406621124-16181607460-87980700413-18848748487-38186811906-83866179899-48052521037-07022164880-32639610985 | 66781406596-73096831064-93546863231-94196105489-63929163328 || 936676 | 2491354 | 94497954531-98116538211-42837637393-90318550621-31404186605-85978752098-54751837452-40263829756-21584452095-09911571729 | 57946842290-70811751667-96407003836-80311371855-21528783577 || 1117598 | 2491354 | 01895171691-22358407775-28637603773-13518829168-90455880119-28672508798-65059848567-86474578422-79051749776-25498873626 | 19312889757-23713866145-22328278494-54535848675-44626652413 |+---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+10 rows in set (1 min 36.66 sec)
看到实际执行时间(1 min 36.66 sec)，是不是不敢相信，走索引了，怎么还会要1分36秒啊，简直不能接受。我来用一副图，来展示一下SQL语句执行过程，你就会明白，为什么执行时间长了。
从上图，可以很明显的看出，性能瓶颈在哪里了吧，是回表查询操作耗时，因为要回表查询大约500W次，所以怎么减少回表操作，就是优化的重点。
那可不可以只查询要返回的10条记录的ID，最后只做10次回表操作呢，答案，当然是可以的，优化之后的SQL如下所示。
mysql> explain select a.* from sbtest1 a inner join (select id from sbtest1 where k>=10 order by k limit 1000000,10) b using (id); +----+-------------+------------+------------+--------+-----------------------+---------+---------+------+---------+----------+--------------------------+| id | select_type | table| partitions | type | possible_keys | key| key_len | ref | rows | filtered | Extra|+----+-------------+------------+------------+--------+-----------------------+---------+---------+------+---------+----------+--------------------------+| 1 | PRIMARY| | NULL | ALL | NULL | NULL | NULL | NULL | 1000010 | 100.00 | NULL || 1 | PRIMARY| a| NULL | eq_ref | PRIMARY| PRIMARY | 4 | b.id | 1 | 100.00 | NULL || 2 | DERIVED| sbtest1 | NULL | range | k_1,idx_sbtest1_k_pad | k_1| 4 | NULL | 2402427 | 100.00 | Using where; Using index |+----+-------------+------------+------------+--------+-----------------------+---------+---------+------+---------+----------+--------------------------+3 rows in set, 1 warning (0.02 sec)
看执行计划，效果非常好，而且用的是覆盖索引，怎么看出是用了覆盖索引，因为Extra列同时出现了Using where和Using index。
来看看实际执行时间
mysql> select a.* from sbtest1 a inner join (select id from sbtest1 where k>=10 order by k limit 1000000,10) b using (id); +---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+| id| k | c | pad |+---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+| 625795 | 2491354 | 97204650565-29444916083-97115589676-40957844519-94449833558-65804998462-60532777657-14143069112-49633654712-45111409020 | 62512103246-48607606334-30155359433-33452555729-53895828100 || 699397 | 2491354 | 32339675223-04668542998-80231026645-48395677535-02765586559-55831506063-42378875097-54580937047-37599981559-17618207647 | 29107597440-02396388801-60803416516-3514546-47217028215 || 710717 | 2491354 | 90067622196-22944775363-10477154003-65718440755-24111522288-05149673185-81902474191-32337446442-26659698531-19766723590 | 86804024266-77223258212-31659403967-01772860244-99013209389 || 743592 | 2491354 | 93848584940-19743659878-81447583142-56888444937-65254387827-69794611882-41783362491-17920606195-80837332876-61005171709 | 18905422789-64525703285-24594762685-66972415320-18032736842 || 857338 | 2491354 | 65447608668-53055131627-49484988592-56750024449-62428854104-09419771096-35386440425-85674387129-76043542898-38871555063 | 00988589677-01502672421-94556913009-28600249646-25815466940 || 865647 | 2491354 | 40627346200-31156486805-14979452789-43576795710-31471455633-50576965996-46658943363-33235956457-29071759844-01216446475 | 77768402076-66310138953-89244891269-40818622962-29528856272 || 867735 | 2491354 | 60189133283-00412480472-85765718780-99930014238-27634346893-05196495869-38494141485-78725336333-97665769760-66018168229 | 51330855469-21834479110-70983834788-18568325279-18729638102 || 911459 | 2491354 | 45484218970-22406621124-16181607460-87980700413-18848748487-38186811906-83866179899-48052521037-07022164880-32639610985 | 66781406596-73096831064-93546863231-94196105489-63929163328 || 936676 | 2491354 | 94497954531-98116538211-42837637393-90318550621-31404186605-85978752098-54751837452-40263829756-21584452095-09911571729 | 57946842290-70811751667-96407003836-80311371855-21528783577 || 1117598 | 2491354 | 01895171691-22358407775-28637603773-13518829168-90455880119-28672508798-65059848567-86474578422-79051749776-25498873626 | 19312889757-23713866145-22328278494-54535848675-44626652413 |+---------+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+10 rows in set (0.31 sec)
效果也是非常棒的，310毫秒就查询出了结果。
进阶
在这里优化之后的SQL语句如下所示
selecta.*fromsbtest1ainnerjoin(selectidfromsbtest1wherek>=10orderbyklimit1000000,10)busing(id);
如果order by后面需要加上pad列进行排序呢，变成如下所示
selecta.*fromsbtest1ainnerjoin(selectidfromsbtest1wherek>=10orderbyk,padlimit1000000,10)busing(id);
上述分页优化核心思想就是覆盖索引，很显然加pad列之后，就不能用覆盖索引解决问题了，因为不满足使用覆盖索引的条件。
mysql> explain select id from sbtest1 where k>=10 order by k,pad limit 1000000,10;+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+| 1 | SIMPLE| sbtest1 | NULL | ALL | k_1 | NULL | NULL | NULL | 4804854 | 50.00 | Using where; Using filesort |+----+-------------+---------+------------+------+---------------+------+---------+------+---------+----------+-----------------------------+1 row in set, 1 warning (0.01 sec)
这个时候要如何优化呢，需要在k和pad列上创建复合索引，就可以解决问题。
给大家留一个SQL，大家看看怎么优化。
selecta.idfromsbtest1awherek>=10orderbykdesc,padasclimit1000000,10;
最后介绍一下在GtiChat平台开启的一个新Chat，
Chat名称：二进制包方式安装 MySQL 8.0.21 和 Docker 方式搭建 MySQL 8.0.21 服务
本 Chat 你将会获取以下知识：
1. 怎么从 MySQL 官网获取最新的 MySQL 数据库安装包，以及获取历史归档版本的 MySQL 数据库安装包
2. 提供标准化的 MySQL 8.0.21 参数配置(稍作微调，即可上生产)，并讲解核心参数
3. MySQL 8.0.21 数据多实例创建和多实例启停维护
4. MySQL 8.0.21 安全配置
5. Docker 方式搭建 MySQL 8.0.21 服务
