MySQL数据库SQL语句优化原理专题（三）

落叶说Mysql数据库运维 2020-11-02

471

需求

做过开发的同学，对分页肯定不会陌生，因为很多前台页面展示，为了更好的展示数据，就会用到分页，所以如何写一个高性能的分页SQL语句，是每一个开发人员需要掌握的技能。

分页SQL

这里给大家写一个分页SQL语句。

mysql> show create table sbtest1\G;
*************************** 1. row ***************************
       Table: sbtest1
Create 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=1000000
1 row in set (0.00 sec)

这里准备了一张500W记录的测试表

mysql> select count(*) from sbtest1;
+----------+
| count(*) |
+----------+
|  5000000 |
+----------+
1 row in set (36.23 sec)

测试分页SQL语句

select a.* from sbtest1 a where k>=10 order by k limit 1000000,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-20183514546-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     | <derived2> | 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-20183514546-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语句如下所示

select a.* from sbtest1 a inner join (select id from sbtest1 where k>=10 order by k limit 1000000,10) b using (id);

如果order by后面需要加上pad列进行排序呢，变成如下所示

select a.* from sbtest1 a inner join (select id from sbtest1 where k>=10 order by k,pad limit 1000000,10) b using (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，大家看看怎么优化。

select a.id from sbtest1 a where k>=10 order by k desc,pad asc limit 1000000,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 服务

mysql

文章转载自落叶说Mysql数据库运维，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL数据库SQL语句优化原理专题（三）

需求

分页SQL

进阶

评论