MySQL执行计划（explain）分析

MySQL执行计划（explain）分析

• EXPLAIN支持对SELECT、UPDATE、INSERT、REPLACE、DELETE分析

• 执行计划能知道：

  • SQL如何使用索引
  • 联接查询的执行顺序
  • 查询扫描的数据行数

• ID列：

  • 表示执行SELECT语句的顺序
  • ID相同时，执行顺序由上至下
  • ID越大优先级越高，越优先被执行

• SELECT_TYPE列：

  • SIMPLE：不包含子查询或是UNION操作的查询
  • PRIMARY：查询中包含任何子查询，那么最外层的查询则被标记为PRIMARY
  • SUBQUERY：SELECT列表中的子查询
  • DEPENDENT SUBQUERY：依赖外部结果的子查询
  • UNION：UNION操作的第二个或是之后的查询的值为UNION
  • DEPENDENT UNION：当UNION作为子查询时，第二或是第二个后的查询的SELECT_TYPE值
  • UNION RESULT：UNION产生的结果集
  • DERIVED：出现在FROM子句中的子查询
  • 用途：查看查询方法

• TABLE列：

  • 输出数据行所在的表的名称
  • <unionM,N>由ID为M,N查询union产生的结果集
  • <derivedN>或<subqueryN>由ID为N的查询产生的结果
  • 用途：查看数据来源

• PARTITIONS列

  • 对于分区表，显示查询的分区ID
  • 对于非分区表，显示为NULL
  • 用途：用于检查出低效率的跨分区扫描

• TYPE列

  • system：这是const联接类型的一个特例，当查询的表只有一行时使用
  • const：表中有且只有一个匹配的行时使用，如对主键或是唯一索引的查询，效率最高的联接方式
  • eq_ref： 唯一索引或主键查找，对于每个索引键，表中只有一条记录与之匹配
  • ref：非唯一索引查找，返回匹配某个单独值的所有行。
  • ref_or_null：类似于ref类型的查询，但是附加了对NULL值列的查询
  • index_merge：该联接类型表示使用了索引合并优化方法。
  • range：索引范围扫描，常见于between、>、<这样的查询条件
  • index：全索引撒秒，同ALL的区别是，遍历的是索引数
  • ALL：全表扫描，效率最差的连接方式

• EXTRA列

  • distinct：优化distinct操作，在找到第一匹配的元祖后即停止找同样值的动作
  • not exists：使用Not Exists来优化查询
  • using filesort：使用额外操作进行排序，通常会出现在order by或group by查询中
  • using index：使用了覆盖索引进行查询
  • using temporary：MySQL需要使用临时表来处理查询，常见于排序，子查询，和分组查询
  • using where：需要在MySQL服务器层使用WHERE条件来过滤数据
  • select tables optimized away：直接通过索引来获取数据，不用访问表（效率最高）

• POSSIBLE_KEYS列

  • 指出MySQL能使用哪些索引来优化查询
  • 查询列所涉及到的列上的索引都会被列出，但不一定会被使用

• KEY列

  • 查询优化器优化查询实际所使用的索引
  • 如果没有可用的索引，则显示为NULL
  • 如查询使用了覆盖索引，则该索引仅出现在Key列中

• KEY_LEN列

  • 表示索引字段的最大可能长度
  • 长度由字段定义计算而来，并非数据的实际长度

• REF列

  • 表示哪些列或常量被用于查找索引列上的值

• ROWS列

  • 表示MySQL通过索引统计信息，估算的所需读取的行数
  • ROWS值的大小是个统计抽样结果，并不十分准确

• FILTERED列

  • 表示返回结果的行数占需读取行数的百分比
  • FILTERED列的值越大越好
  • 依赖于统计信息

执行计划的限制

• 无法展示存储过程，触发器，UDF对查询的影响
• 无法使用EXPLAIN对存储过程进行分析
• 早期版本的MySQL只支持对SELECT语句进行分析

常见业务优化处理

优化评论分页查询

例子

SELECT customer_id,title,content FROM `product_comment`
WHERE audit_status=1 AND product_id=199726
LIMIT 0,5

这里的索引有audit_status和product_id，可以建立联合索引。但是哪个放左边就要计算区分度。

计算方法

SELECT COUNT(DISTINCT audit_status)/COUNT(*) AS audit_rate,
COUNT(DISTINCT product_id)/COUNT(*) AS product_rate
FROM product_comment;

区分度越高越好，放左边。结论product_rate>audit_rate

建立联合索引

CREATE INDEX idx_productID_auditStatus ON product_comment(product_id,audit_status)

如何删除重复数据

业务场景：删除评论表中对同一订单同一商品的重复评论，只保留最早的一条。

1. 查看是否存在对于同一订单同一商品的重复评论。

SELECT order_id,product_id,COUNT(*) FROM product_comment GROUP BY order_id,product_id HAVING COUNT(*)>1;

2. 备份product_comment表。

CREATE TABLE bak_product_comment_161022 LIKE product_comment;

INSERT INTO bak_product_comment_161022 SELECT * FROM product_comment;

3. 删除同一订单的重复评论。

DELETE a
FROM product_comment a
JOIN(
    SELECT order_id,product_id,MIN(comment_id) AS comment_id
    FROM product_comment
    GROUP BY order_id,product_id
    HAVING COUNT(*)>=2
) b ON a.order_id=b.order_id AND a.product_id=b.product_id
AND a.comment_id>b.comment_id

如何进行分区间数据统计示例

业务场景：统计消费总金额大于1000元的，800到1000元的，500到800元的，以及500元以下的人数。

SELECT COUNT(CASE WHEN IFNULL(total_money,0) >=1000 THEN a.customer_id END) AS '>1000'
      ,COUNT(CASE WHEN IFNULL(total_money,0) >=800 AND IFNULL(total_money,0) <1000 THEN a.customer_id END) AS '800~1000'
      ,COUNT(CASE WHEN IFNULL(total_money,0) >=500 AND IFNULL(total_money,0) <800 THEN a.customer_id END) AS '500~800'
      ,COUNT(CASE WHEN IFNULL(total_money,0) <500  THEN a.customer_id END) AS '<500'
FROM mc_userdb.`customer_login` a
LEFT JOIN
( SELECT customer_id,SUM(order_money) AS total_money
  FROM mc_orderdb.`order_master` GROUP BY customer_id) b
ON a.`customer_id`=b.`customer_id`

捕获有问题的SQL-慢查日志

• 启动MySQL慢查日志

set global show_query_log_file = /sql_log/show_log.log

set global log_queries_not_using_indexes = on; -- 未使用索引的SQL记录日志

set global long_query_time=0.001; -- 抓取执行超过多少时间的SQL（秒）

set global low_query_log=on; -- 启动

• 如何分析慢查日志，使用mysqldumpslow工具，例如：mysqldumpslow slow-mysql.log

参考

1. 高性能可扩展MySQL数据库设计及架构优化 电商项目，sqlercn，https://coding.imooc.com/class/79.html

关于我：

linxinzhe，全栈工程师，目前供职于某500强通信企业。人工智能，区块链爱好者。

GitHub:https://github.com/linxinzhe

欢迎留言讨论，也欢迎关注我~
我也会关注你的哦！