你好，我是朱晓峰。这节课，我想和你聊一聊怎么对查询语句进行调优。

你肯定遇到过这样的情况：你写的SQL语句执行起来特别慢，要等好久才出结果，或者是干脆就“死”在那里，一点反应也没有。一旦遇到这种问题，你就要考虑进行优化了。

如果你开发过数据库应用，肯定会有这样的体会：让应用运行起来不难，但是要运行得又快又好，就没那么不容易了。这很考验我们的内功。

而要想提高应用的运行效率，你就必须掌握优化查询的方法。今天，我就给你讲一下MySQL的查询分析语句和2种优化查询的方法。

查询分析语句

虽然MySQL的查询分析语句并不能直接优化查询，但是却可以帮助你了解SQL语句的执行计划，有助于你分析查询效率低下的原因，进而有针对性地进行优化。查询分析语句的语法结构是：

{ EXPLAIN | DESCRIBE | DESC }查询语句;

下面我借助一个小例子，给你详细地讲解一下，怎么使用查询分析语句，来分析一个查询的执行计划。

假设有一个销售流水表（demo.trans），里面有400万条数据，如下所示：

现在，我要查询一下商品编号是1的商品，在2020年6月18日上午9点到12点之间的销售明细。代码如下所示：

mysql> SELECT itemnumber,quantity,price,transdate
-> FROM demo.trans
-> WHERE itemnumber=1
-> AND transdate>'2020-06-18 09:00:00'
-> AND transdate<'2020-06-18 12:00:00';
+------------+----------+-------+---------------------+
| itemnumber | quantity | price | transdate |
+------------+----------+-------+---------------------+
| 1 | 0.276 | 70.00 | 2020-06-18 11:04:00 |
| 1 | 1.404 | 70.00 | 2020-06-18 11:10:57 |
| 1 | 0.554 | 70.00 | 2020-06-18 11:18:12 |
| 1 | 0.431 | 70.00 | 2020-06-18 11:27:39 |
| 1 | 0.446 | 70.00 | 2020-06-18 11:42:08 |
| 1 | 0.510 | 70.00 | 2020-06-18 11:56:43 |
+------------+----------+-------+---------------------+
6 rows in set (6.54 sec)

结果显示，有6条记录符合条件。这个简单的查询一共花去了6.54秒，这个速度显然太慢了。

为了找到查询运行慢的原因，咱们来分析一下它，看看为什么会用这么多时间，有没有办法优化。现在，我们用下面的语句分析一下这个查询的具体细节：

mysql> EXPLAIN SELECT itemnumber,quantity,price,transdate -- 分析查询执行情况
-> FROM demo.trans
-> WHERE itemnumber=1                  -- 通过商品编号筛选
-> AND transdate>'2020-06-18 09:00:00' -- 通过交易时间筛选
-> AND transdate<'2020-06-18 12:00:00';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key |key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | trans | NULL | ALL | NULL | NULL | NULL | NULL | 4157166 | 1.11 | Using where |      -- 没有索引，扫描4157166条记录
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

这个结果集中包含了很多个字段，理解这些字段的意思，是我们优化查询的关键。

字段比较多，我先说说简单的字段，你只要知道概念就可以了。另外一类比较复杂的字段，一会儿我再具体讲。

1. id：是一个查询序列号。
2. table：表示与查询结果相关的表的名称。
3. partition：表示查询访问的分区。
4. key：表示优化器最终决定使用的索引是什么。
5. key_len：表示优化器选择的索引字段按字节计算的长度。如果没有使用索引，这个值就是空。
6. ref：表示哪个字段或者常量被用来与索引字段比对，以读取表中的记录。如果这个值是“func”，就表示用函数的值与索引字段进行比对。
7. rows：表示为了得到查询结果，必须扫描多少行记录。
8. filtered：表示查询筛选出的记录占全部表记录数的百分比。
9. possible_key：表示MySQL可以通过哪些索引找到查询的结果记录。如果这里的值是空，就说明没有合适的索引可用。你可以通过查看WHERE条件语句中使用的字段，来决定是否可以通过创建索引提高查询的效率
10. Extra：表示MySQL执行查询中的附加信息。你可以点击这个链接查询详细信息。
11. type：表示表是如何连接的。至于具体的内容，你可以参考下查询分析语句输出内容说明。

除了刚刚这些字段，还有1个比较重要，那就是select_type。它表示查询的类型，主要有4种取值。

• SIMPLE：表示简单查询，不包含子查询和联合查询。
• PRIMARY：表示是最外层的查询。
• UNION：表示联合查询中的第二个或者之后的查询。
• DEPENDENTUNION：表示联合查询中的第二个或者之后的查询，而且这个查询受外查询的影响。

关于这个DEPENDENTUNION取值，不是很好理解，我举个小例子。

假设我们有下面的查询语句：

mysql> SELECT *
-> FROM demo.goodsmaster a
-> WHERE itemnumber in
-> (
-> SELECTb.itemnumber
-> FROM demo.goodsmaster b
-> WHERE b.goodsname = '书'
-> UNION
-> SELECTc.itemnumber
-> FROM demo.goodsmaster c
-> WHERE c.goodsname = '笔'
-> );
+------------+---------+-----------+---------------+------+------------+----------------+
| itemnumber | barcode | goodsname | specification | unit | salesprice | avgimportprice |
+------------+---------+-----------+---------------+------+------------+----------------+
| 1 | 0001 | 书 | 16开 | 本 | 89.00 | 31.00 |
| 2 | 0002 | 笔 | NULL | 包 | 5.00 | 2.87 |
+------------+---------+-----------+---------------+------+------------+----------------+
2 rows in set (0.00 sec)

对这个语句的执行进行分析，得到如下的结果：

mysql> EXPLAIN SELECT *
-> FROM demo.goodsmaster a
-> WHERE a.itemnumber in
-> (
-> SELECTb.itemnumber
-> FROM demo.goodsmaster b
-> WHERE b.goodsname = '书'
-> UNION
-> SELECTc.itemnumber
-> FROM demo.goodsmaster c
-> WHERE c.goodsname = '笔'
-> );
+----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
| 1 | PRIMARY | a | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using where |
| 2 | DEPENDENT SUBQUERY | b | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 50.00 | Using where |
| 3 | DEPENDENT UNION | c | NULL | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | 50.00 | Using where |
| NULL | UNION RESULT | <union2,3> | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | Using temporary |
+----+--------------------+------------+------------+--------+---------------+---------+---------+------+------+----------+-----------------+
4 rows in set, 1 warning (0.00 sec)

MySQL在执行的时候，会把这个语句进行优化，重新写成下面的语句：

SELECT *
FROM demo.goodsmaster a
WHERE EXISTS
(
SELECT b.id
FROM demo.goodsmaster b
WHERE b.goodsname = '书' ANDa.itemnumber=b.itemnumber
UNION
SELECT c.id
FROM demo.goodsmaster c
WHERE c.goodsname = '笔' AND a.itemnumber=c.itemnumber
);

在这里，子查询中的联合查询是：

SELECT c.id
FROM demo.goodsmaster c
WHERE c.goodsname = '笔' AND a.itemnumber=c.itemnumber

这个查询就用到了与外部查询相关的条件a.itemnumber=c.itemnumber，因此，查询类别就变成了“UNION DEPENDENT”。

好了，现在，我们已经知道了查询分析语句的结果集中各个字段的含义。现在来分析一下刚刚的查询语句。

我们发现，这个查询是一个简单查询，涉及的表是demo.trans，没有分区，连接类型是扫描全表，没有索引，一共要扫描的记录数是4157166。因此，查询速度慢的主要原因是没有索引，导致必须要对全表进行扫描才能完成查询。所以，针对这个问题，可以通过创建索引的办法，来提高查询的速度。

下面，我们用条件语句中的筛选字段itemnumber和transdate分别创建索引：

mysql> CREATE INDEX itemnumber_trans ON demo.trans(itemnumber);
Query OK, 0 rows affected (59.86 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE INDEX transdate_trans ON demo.trans(transdate);
Query OK, 0 rows affected (56.75 sec)
Records: 0  Duplicates: 0  Warnings: 0

然后我们再次运行刚才的查询，看看优化有没有起作用：

mysql> SELECT itemnumber,quantity,price,transdate
    -> FROM demo.trans
    -> WHERE itemnumber=1
    -> AND transdate>'2020-06-18 09:00:00'
    -> AND transdate<'2020-06-18 12:00:00';
+------------+----------+-------+---------------------+
| itemnumber | quantity | price | transdate           |
+------------+----------+-------+---------------------+
|          1 | 0.276    | 70.00 | 2020-06-18 11:04:00 |
|          1 | 1.404    | 70.00 | 2020-06-18 11:10:57 |
|          1 | 0.554    | 70.00 | 2020-06-18 11:18:12 |
|          1 | 0.431    | 70.00 | 2020-06-18 11:27:39 |
|          1 | 0.446    | 70.00 | 2020-06-18 11:42:08 |
|          1 | 0.510    | 70.00 | 2020-06-18 11:56:43 |
+------------+----------+-------+---------------------+
6 rows in set (0.09 sec)

结果显示，查询只运行了0.09秒，跟之前的6.54秒相比，快了很多。这说明我们的优化措施起了作用。下面我们再次运行查询分析语句，查看一下现在的查询执行计划。

mysql> EXPLAIN SELECT itemnumber,quantity,price,transdate
    -> FROM demo.trans
    -> WHERE itemnumber=1                 -- 按商品编号筛选
    -> AND transdate>'2020-06-18 09:00:00'-- 按照交易时间筛选
    -> AND transdate<'2020-06-18 12:00:00';
+----+-------------+-------+------------+-------+----------------------------------+-----------------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys                    | key             | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+-------+------------+-------+----------------------------------+-----------------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | trans | NULL       | range | itemnumber_trans,transdate_trans | transdate_trans | 6       | NULL |  552 |     1.92 | Using index condition; Using where | -- 使用了索引
+----+-------------+-------+------------+-------+----------------------------------+-----------------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.01 sec)

结果显示，这一次的查询执行计划有了改变。系统发现，有2个索引itemnumber_trans和transdate_trans可以使用，并且最终选择了使用交易时间字段创建的索引transdate_trans来执行查询。扫描的方式也不再是全表扫描，而是改为了区域扫描，实际的扫描记录数也减少到了552个。这样一来，查询更加精准，查询的速度自然也就大幅提高了。

至此，我给你介绍了查询分析语句，并且演示了如何通过使用查询分析语句对慢查询的执行计划进行分析，并且利用分析的结果对慢查询进行优化。接下来，我再给你介绍2种优化查询的方法。

2种查询优化的方法

优化查询最有效的方法就是创建索引。关于如何创建索引，我已经在第11讲中介绍过了，这里就不多说了。下面我来讲讲怎么在包含关键字“LIKE”和“OR”的条件语句中，利用索引提高查询效率。

使用关键字“LIKE”

“LIKE”经常被用在查询的限定条件中，通过通配符“%”来筛选符合条件的记录。比如，

1. WHERE字段 LIKE ‘%aa’，表示筛选出所有以字段以“aa”结尾的记录；
2. WHERE字段 LIKE ‘aa%’，表示筛选出所有以“aa”开始的记录；
3. WHERE字段 LIKE ‘%aa%’，表示所有字段中包含“aa”的记录。

这里你要注意的是，通配符在前面的筛选条件是不能用索引的。也就是说，WHERE字段LIKE‘%aa’和WHERE字段 LIKE ‘%aa%’都不能使用索引，但是通配符在后面的筛选条件，就可以使用索引。

下面，我举个小例子，通过查询分析语句来验证一下索引的使用情况。

假设我用商品流水表的字段商品条码“barcode”创建了一个索引：

mysql> CREATE INDEX trans_barcode ON demo.trans(barcode);
Query OK, 0 rows affected (1 min 20.78 sec)
Records: 0 Duplicates: 0 Warnings: 0

我们来看看如果把通配符放在前面，能不能用到索引：

mysql> EXPLAIN SELECT * FROM demo.trans
-> WHERE barcode LIKE '%182505';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key |key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | trans | NULL | ALL | NULL | NULL | NULL | NULL | 4144028 | 11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

查询分析的结果显示，type的值是“ALL”，key是空，表示需要进行全表扫描，没有索引可用，rows的值是4144028，基本上要检索全部记录，效率非常低。

再看看通配符在后面的情况：

mysql> EXPLAIN SELECT * FROM demo.trans
-> WHERE barcode LIKE '6953150%';
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type |possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | trans | NULL | range | trans_barcode | trans_barcode | 803 | NULL | 563 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+---------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

type的值是“range”，意思是使用索引检索一个给定范围的记录。rows的值是563，也就是只需要扫描563条记录就行了，这样效率就高多了。

使用关键字“OR”

关键字“OR”表示“或”的关系，“WHERE表达式1 OR 表达式2”，就表示表达式1或者表达式2中只要有一个成立，整个WHERE条件就是成立的。

需要注意的是，只有当条件语句中只有关键字“OR”，并且“OR”前后的表达式中的字段都建有索引的时候，查询才能用到索引。

同样，为了方便你理解，我还是举个小例子，通过查询分析语句来实际验证一下。

我刚才已经用字段条码给商品流水表创建了一个索引，现在我再用商品编号“itemnumber”创建一个索引：

mysql> CREATE INDEX trans_itemnumber ON demo.trans(itemnumber);
Query OK, 0 rows affected (20.24 sec)
Records: 0 Duplicates: 0 Warnings: 0

我们先看一下关键字“OR”前后的表达式中的字段都创建了索引的情况：

mysql> EXPLAIN SELECT * FROM demo.trans
-> WHERE barcode LIKE '6953150%'
-> OR itemnumber = 1;
+----+-------------+-------+------------+-------------+--------------------------------+--------------------------------+---------+------+-------+----------+---------------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------------+--------------------------------+--------------------------------+---------+------+-------+----------+---------------------------------------------------------------+
| 1 | SIMPLE | trans | NULL | index_merge | trans_barcode,trans_itemnumber | trans_barcode,trans_itemnumber | 803,5 | NULL | 81099 | 100.00 | Using sort_union(trans_barcode,trans_itemnumber); Using where |
+----+-------------+-------+------------+-------------+--------------------------------+--------------------------------+---------+------+-------+----------+---------------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

查询分析结果显示，有2个索引可以使用，分别是“trans_barcode”和“trans_itemnumber”。key=index_merge，就说明优化器选择了合并索引的方式。因此，这个关键字“OR”前后的表达式中的字段都创建了索引的查询，是可以用到索引的。

在下面的例子中，表达式goodsname LIKE '%海鲜菇%'中的字段goodsname没有创建索引，我们来验证一下查询是否能够利用索引：

mysql> EXPLAIN SELECT * FROM demo.trans
-> WHERE barcode LIKE '6953150%'
-> OR goodsname LIKE '%海鲜菇%';
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key |key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | trans | NULL | ALL | trans_barcode | NULL | NULL | NULL | 4144028 | 20.99 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

查询分析的结果显示，type=ALL，是全表扫描，不能用索引。

总结

今天，我们学习了优化查询的方法，包括用来分析查询执行情况的查询分析语句，语法结构是：

{ EXPLAIN | DESCRIBE | DESC }查询语句;

同时，我们还学习了在使用关键字“LIKE”和“OR”的情况下，用索引来提高查询效率的方法。

特别需要提醒你注意的是，在使用“LIKE”关键字的条件语句中，通配符“%”在前面的筛选条件不能使用索引，通配符“%”在后面的筛选条件可以使用索引。在使用“OR”关键字的条件语句中，只有关键字“OR”前后的表达式中的字段都创建了索引，条件语句才能使用索引。

关于优化查询，还有一个值得关注的点，就是子查询。这是MySQL的一项重要的功能，可以帮助我们通过一个SQL语句实现比较复杂的查询。但是，子查询的执行效率不高。因为MySQL会用临时表把子查询的结果保存起来，然后再使用临时表的内容完成查询。这样一来，查询就多了一个创建临时表的过程，执行效率没有连接查询高。针对这种情况，建议你把子查询转换成连接查询，这样可以进一步提高查询的效率。

思考题

假设现在有一个这样的查询，请你把这个包含子查询的查询改成2个表的连接查询。

mysql> EXPLAIN SELECT * FROM demo.trans
-> WHERE itemnumber IN
-> (
-> SELECTitemnumber FROM demo.goodsmaster
-> WHERE goodsname LIKE '%书%'
-> );
+----+-------------+-------------+------------+------+------------------+------------------+---------+-----------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+------------------+------------------+---------+-----------------------------+------+----------+-------------+
| 1 | SIMPLE | goodsmaster | NULL | ALL | PRIMARY | NULL | NULL | NULL | 2 | 50.00 | Using where |
| 1 | SIMPLE | trans | NULL | ref | trans_itemnumber | trans_itemnumber | 5 | demo.goodsmaster.itemnumber | 889 | 100.00 | NULL |
+----+-------------+-------------+------------+------+------------------+------------------+---------+-----------------------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

欢迎在留言区写下你的思考和答案，我们一起交流讨论。如果你觉得今天的内容对你有所帮助，也欢迎你把它分享给你的朋友或同事，我们下节课见。