2020-11-15

Mysql执行计划

words: 3.5k    views:    time: 14min

存储服务

mysql sql

---

执行计划是数据库根据SQL语句和相关表的统计信息作出的一个查询方案，这个方案是由查询优化器自动分析产生的，比如一条SQL语句如果用来从一个 10万条记录的表中查1条记录，那优化器会选择索引查找方式，如果该表只有5000条记录，那优化器就会改变方案，采用全表扫描方式。

因此，要产生一个正确的执行计划有两点很重要：

1. SQL语句是否清晰地告诉查询优化器它想干什么？
2. 查询优化器得到的数据库统计信息是否是最新的、正确的？

Mysql执行计划

mysql> explain select * from servers;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | servers | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL     | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.03 sec)

重点关注6个参数：

1. possible_keys：可能用到的索引

MySQL优化器认为这条SQL可能会用到的索引，但只是可能，不一定会用；

比如，表建了两个索引：idx_price、idx_name，而SQL的WHERE条件是price，那么possible_keys就会显示idx_price；

2. key：实际使用的索引

MySQL执行时实际用到的索引，这里有几个情况：

key和possible_keys一致：  说明优化器选对了索引，正常执行；    
key为NULL：              说明没有用到任何索引，直接走全表扫描（效率极低，优先优化）；    
key和possible_keys不一致：说明优化器选错了索引，可能需要干预；

3. key_len：索引的长度

MySQL实际使用的索引长度（字节），这个参数能判断索引有没有被充分利用；

比如有联合索引idx_price_name（price, name），但key_len只显示了price的长度，说明只用到了联合索引的前半部分price，name部分没用到，可能需要调整索引或SQL；

另外，key_len越短，说明索引使用越高效（前提是能满足查询需求），因为索引越短，索引体积就越小，数据库处理索引的流程(读、存、比)就越高效；

4. rows：预估扫描的行数

优化器预估的，执行目标SQL需要扫描的数据行数，行数越少，执行效率越高；

5. type：数据扫描类型

MySQL查询数据时使用的扫描方式，它直接决定了SQL的执行效率，也是我们优化时的首要关注对象，按执行效率从低到高，说明下最常见的6种类型

ALL：全表扫描（最坏情况，必须避免）
MySQL会扫描整张表的所有数据，找到符合条件的记录；
比如没有索引的表，执行 SELECT * FROM user WHERE age = 20，就会走ALL；
特点：效率极低，数据量越大，执行越慢。只要type是ALL，优先检查是否能加索引、是否走对了索引；

index：全索引扫描（和ALL差不多，尽量避免）
扫描整个索引表，好处是不需要对数据进行排序（因为索引本身是有序的），但本质还是全量扫描，效率依然很低；
比如索引idx_name，执行 SELECT name FROM user，MySQL会扫描整个idx_name索引（不用回表查数据），这时type就是index；

range：索引范围扫描（及格线，尽量争取）
优化的及格线，从这一级别开始，索引的作用会越来越明显，效率会大幅提升；
特点：只扫描索引的某个范围，不用全量扫描，效率中等，日常开发中最常见、也最容易实现的优化目标；

ref：非唯一索引扫描（推荐，争取达到）
使用了非唯一索引，或者唯一索引的非唯一性前缀，返回的数据可能是多条（索引列的值不唯一）；
不需要扫描全索引（因为索引有序），即使有重复值，也只在小范围内扫描，效率比range高；
比如非唯一索引idx_age，执行 SELECT * FROM user WHERE age = 20，这时type就是ref（因为age可能有多个值）；

eq_ref：唯一性索引扫描（优秀，尽量争取）
使用了主键，或唯一索引时产生的扫描方式，通常出现在多表联查中；
每次查询只会返回一条匹配的数据（由于主键/唯一索引的值唯一），效率很高；
比如主键id，执行 SELECT * FROM user u JOIN order o ON u.id = o.user_id，这时u.id是主键，type就是eq_ref；

const：常量级扫描（最优，理想状态）
效率最高的扫描方式，适用于结果只有一条的主键/唯一索引查询，且查询条件是常量值比较；
和eq_ref对比，const是和常量值比较，比如 SELECT * FROM user WHERE id = 1，而eq_ref通常用于多表联查；

6. Extra：额外执行信息（辅助判断效率）

Extra信息方便进行性能分析，它反映了MySQL在执行过程中的细节，说明下最常见的几种取值

Using filesort：严重低效，必须避免
MySQL无法利用索引完成排序，只能在内存（Sort Buffer）甚至磁盘中进行手动排序；
常见场景如：ORDER BY 或 GROUP BY 的字段没有索引，或者联合索引顺序不匹配；
优化方法：让GROUP BY、ORDER BY的字段和索引字段一致，确保排序字段满足最左匹配原则，利用B+树天然的有序性；

Using temporary：严重低效，必须避免
MySQL发现由于数据过于复杂，不得不建立一张临时表来中转数据（通常是为了去重或分组）；临时表会占用内存/磁盘资源，数据量越大，效率越低；
常见场景如：在非索引列上进行DISTINCT、GROUP BY，或者关联查询时 ORDER BY驱动表以外的列；
优化方法：给GROUP BY、ORDER BY的字段加索引，避免创建临时表，或者优化 SQL 逻辑减少中间结果集；

Using index：高效，推荐
查询所需的所有数据，都能在索引中找到，不需要回表查询原表数据，也就是常说的覆盖索引；避免了回表操作（回表是低效操作），执行效率很高；
比如索引idx_price_name(price, name)，执行SELECT name FROM user WHERE price = 20时不需要回表，索引中能直接获取name；

Using index condition：进阶优化（补漏必备）
索引下推(ICP)是MySQL 5.6+的黑科技，就是在索引内部提前过滤掉不符合条件的记录，从而减少回表次数；
虽然不如Using index快，但它代表你的联合索引正在被高效利用，是一个性能良好的信号；

Using where：最常见的状态
MySQL服务器（Server层）在存储引擎检索行后，还需要进行一次筛选；就是存储引擎给的数据并不完全符合条件，Server层得再做一次筛选；

Using index for group-by：分组优化巅峰
类似于Using index，MySQL发现只需利用索引树，就能完成GROUP BY或 DISTINCT，连排序都不用了，极度高效；
意味着MySQL连索引树的所有叶子节点都不用扫，直接通过索引的索引（松散索引扫描）就能拿到结果。

7. id：执行select子句或者操作表的顺序，id的值越大，代表优先级越高，越先执行，如果id相同，表示为一组；

8. select_type：查询的类型，用于区分各种复杂的查询，比如：普通查询、联合查询、子查询等；

SIMPLE：表示最简单的select查询语句，也就是在查询中不包含子查询或者union交并差集等操作；

PRIMARY：当查询语句中包含任何复杂的子部分，最外层查询则被标记为PRIMARY；

SUBQUERY：当select或where列表中包含了子查询，该子查询被标记为SUBQUERY；

DERIVED：表示包含在from子句中的子查询的select，在from列表中包含的子查询会被标记为derived；

UNION：如果union后边又出现的select语句，则会被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived；

UNION RESULT：表示从union的临时表中读取数据，table列<union1,4>则表示取第一个和第四个select的结果进行union操作；

9. table：查询的表名，并不一定是真实存在的表，有别名显示别名，也可能为临时表；

10. partitions：查询时匹配到的分区信息，对于非分区表值为NULL，当查询的是分区表时，partitions显示分区表命中的分区情况；

11. filtered：存储引擎返回的数据中，经过Server层筛选后，真正满足所有WHERE条件的记录所占的百分比，能反映索引的过滤性好不好；

sql优化示例

• 深度分页问题

offset越大，分页查询越慢，主要是Innodb存储层不解析limit和offset关键字，它只管将数据检索出来交给server层，server层进行数数筛选最后响应给客户端。比如下面这个查询，其实存储层一共查出了6000010条数据，然后server层筛选只留下了最后10条；

select * from user order by id limit 6000000, 10;

如果通过非主键索引进行limit，还会有一个回表过程，虽然user_name建了索引，但优化器认为与其回表6000010次，还不如直接扫描全表

select * from user order by user_name limit 6000000, 10

这个问题没什么好的办法，可以尝试滚动查询，记住上次返回的id最大值，然后下次从这个id继续

SELECT * FROM user u1
INNER JOIN (
    SELECT id FROM user WHERE user_name >= {lastName} and id > {lastId} ORDER BY user_name, id LIMIT 10
) u2 ON u1.id = u2.id
ORDER BY u1.user_name, u1.id

• 关联更新、删除

UPDATE operation o
SET    status = 'applying'
WHERE  o.id IN (SELECT id
                FROM (SELECT o.id, o.status
                      FROM operation o
                      WHERE o.group = 123 AND o.status NOT IN ( 'done' )
                      ORDER BY o.parent, o.id LIMIT  1) t);

+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
| id | select_type        | table | type  | possible_keys | key     | key_len | ref   | rows | Extra                                               |
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+
| 1  | PRIMARY            | o     | index |               | PRIMARY | 8       |       | 24   | Using where; Using temporary                        |
| 2  | DEPENDENT SUBQUERY |       |       |               |         |         |       |      | Impossible WHERE noticed after reading const tables |
| 3  | DERIVED            | o     | ref   | idx_2,idx_5   | idx_5   | 8       | const | 1    | Using where; Using filesort                         |
+----+--------------------+-------+-------+---------------+---------+---------+-------+------+-----------------------------------------------------+

可以使用JOIN重写，子查询模式从DEPENDENT SUBQUERY变为DERIVED

UPDATE operation o
       JOIN  (SELECT o.id, o.status
              FROM operation o
              WHERE o.group = 123 AND o.status NOT IN ( 'done' )
              ORDER BY o.parent, o.id LIMIT  1) t
         ON o.id = t.id
SET status = 'applying'

+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key   | key_len | ref   | rows | Extra                                               |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+
| 1  | PRIMARY     |       |      |               |       |         |       |      | Impossible WHERE noticed after reading const tables |
| 2  | DERIVED     | o     | ref  | idx_2,idx_5   | idx_5 | 8       | const | 1    | Using where; Using filesort                         |
+----+-------------+-------+------+---------------+-------+---------+-------+------+-----------------------------------------------------+

• 混合排序

SELECT * FROM my_order o
INNER JOIN my_appraise a ON a.orderid = o.id
ORDER  BY a.is_reply ASC, a.appraise_time DESC
LIMIT  0, 20

+----+-------------+-------+--------+---------------+---------+---------+------------+---------+----------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows    | Extra          |
+----+-------------+-------+--------+---------------+---------+---------+------------+---------+----------------+
|  1 | SIMPLE      | a     | ALL    | idx_orderid   | NULL    | NULL    | NULL       | 1967647 | Using filesort |
|  1 | SIMPLE      | o     | eq_ref | PRIMARY       | PRIMARY | 122     | a.orderid  |       1 | NULL           |
+----+-------------+-------+--------+---------------+---------+---------+------------+---------+----------------+

如果is_reply只有0和1两种状态，可以使用下面的方法重写

SELECT * FROM (
    (SELECT * FROM my_order o
     INNER JOIN my_appraise a ON a.orderid = o.id AND is_reply = 0
     ORDER  BY appraise_time DESC LIMIT 0, 20)
    UNION ALL
    (SELECT * FROM my_order o
     INNER JOIN my_appraise a ON a.orderid = o.id AND is_reply = 1
     ORDER  BY appraise_time DESC LIMIT 0, 20)) t
ORDER BY is_reply ASC, appraisetime DESC LIMIT 20;

• EXISTS语句

SELECT * FROM my_neighbor n
LEFT JOIN my_neighbor_apply sra ON n.id = sra.neighbor_id AND sra.user_id = 'xxx'
WHERE n.topic_status < 4
  AND EXISTS(SELECT 1 FROM message_info m WHERE  n.id = m.neighbor_id AND m.inuser = 'xxx')
  AND n.topic_type <> 5

+----+--------------------+-------+------+---------------+------------------+---------+-------+---------+------------------------------------+
| id | select_type        | table | type | possible_keys | key              | key_len | ref   | rows    | Extra                              |
+----+--------------------+-------+------+---------------+------------------+---------+-------+---------+------------------------------------+
|  1 | PRIMARY            | n     | ALL  |               | NULL             | NULL    | NULL  | 1086041 | Using where                        |
|  1 | PRIMARY            | sra   | ref  |               | idx_user_id      | 123     | const |       1 | Using where                        |
|  2 | DEPENDENT SUBQUERY | m     | ref  |               | idx_message_info | 122     | const |       1 | Using index condition; Using where |
+----+--------------------+-------+------+---------------+------------------+---------+-------+---------+------------------------------------+

将exists改为join，能够避免嵌套子查询

SELECT * FROM   my_neighbor n
INNER JOIN message_info m ON n.id = m.neighbor_id AND m.inuser = 'xxx'
LEFT JOIN my_neighbor_apply sra ON n.id = sra.neighbor_id AND sra.user_id = 'xxx'
WHERE n.topic_status < 4 AND n.topic_type <> 5

+----+-------------+-------+--------+---------------+------------------+---------+-----------+------+-----------------------+
| id | select_type | table | type   | possible_keys | key              | key_len | ref       | rows | Extra                 |
+----+-------------+-------+--------+---------------+------------------+---------+-----------+------+-----------------------+
|  1 | SIMPLE      | m     | ref    |               | idx_message_info | 122     | const     |    1 | Using index condition |
|  1 | SIMPLE      | n     | eq_ref |               | PRIMARY          | 122     | ighbor_id |    1 | Using where           |
|  1 | SIMPLE      | sra   | ref    |               | idx_user_id      | 123     | const     |    1 | Using where           |
+----+-------------+-------+--------+---------------+------------------+---------+-----------+------+-----------------------+

• 提前过滤

SELECT * FROM my_order o
LEFT JOIN my_userinfo u ON o.uid = u.uid
LEFT JOIN my_productinfo p ON o.pid = p.pid
WHERE ( o.display = 0 ) AND ( o.ostaus = 1 )
ORDER BY o.selltime DESC LIMIT 0, 15

+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref             | rows   | Extra                                              |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+
|  1 | SIMPLE      | o     | ALL    | NULL          | NULL    | NULL    | NULL            | 909119 | Using where; Using temporary; Using filesort       |
|  1 | SIMPLE      | u     | eq_ref | PRIMARY       | PRIMARY | 4       | o.uid           |      1 | NULL                                               |
|  1 | SIMPLE      | p     | ALL    | PRIMARY       | NULL    | NULL    | NULL            |      6 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+--------+----------------------------------------------------+

先做一系列的左连接，然后排序取前15条记录，从执行计划也可以看出，参与排序的记录数为90万

SELECT * FROM (
    SELECT * FROM my_order o
    WHERE o.display = 0 AND o.ostaus = 1
    ORDER BY o.selltime DESC LIMIT 0, 15
) o
LEFT JOIN my_userinfo u ON o.uid = u.uid
LEFT JOIN my_productinfo p ON o.pid = p.pid
ORDER BY o.selltime DESC limit 0, 15

+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref   | rows   | Extra                                              |
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL  |     15 | Using temporary; Using filesort                    |
|  1 | PRIMARY     | u          | eq_ref | PRIMARY       | PRIMARY | 4       | o.uid |      1 | NULL                                               |
|  1 | PRIMARY     | p          | ALL    | PRIMARY       | NULL    | NULL    | NULL  |      6 | Using where; Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | o          | index  | NULL          | idx_1   | 5       | NULL  | 909112 | Using where                                        |
+----+-------------+------------+--------+---------------+---------+---------+-------+--------+----------------------------------------------------+

虽然估算的行扫描仍然为90万，但是利用了索引以及LIMIT子句后，实际执行时间会降低很多

sql技巧

• case when 统计

根据左边各个市的人口统计每个省的人口

SELECT CASE pref_name
      WHEN '长沙' THEN '湖南' 
      WHEN '衡阳' THEN '湖南'
      WHEN '海口' THEN '海南' 
      WHEN '三亚' THEN '海南'
    ELSE '其他' END AS district,
    SUM(population) 
FROM PopTbl GROUP BY district;

• case when 更新

工资1万以上的员工，降薪10%，工资低于1万的员工，加薪20%

UPDATE Salaries
SET salary = CASE WHEN salary >= 10000 THEN salary * 0.9
WHEN salary < 10000 THEN salary * 1.2
ELSE salary END;

• 自连接 删除重复行

DELETE FROM Products P1
 WHERE id < ( SELECT MAX(P2.id) 
                   FROM Products P2 
                  WHERE P1.name = P2.name 
                    AND P1.price = P2.price ); 

• EXISTS 代替 DISTINCT

如何找出有销售记录的商品，使用DISTINCT可以如下

SELECT DISTINCT I.item_no
FROM Items I INNER JOIN SalesHistory SH
ON I. item_no = SH. item_no;

为了排除重复数据，DISTINCT会对结果进行排序，用EXISTS代替可以避免排序

SELECT item_no FROM Items I
WHERE EXISTS 
        (SELECT *
           FROM SalesHistory SH
          WHERE I.item_no = SH.item_no);

参考：

1. https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#jointype_index_merge

Mysql基本原理 Git 代码管理

存储服务

mysql sql