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)
如何删除重复数据
业务场景：删除评论表中对同一订单同一商品的重复评论，只保留最早的一条。
- 查看是否存在对于同一订单同一商品的重复评论。
SELECT order_id,product_id,COUNT(*) FROM product_comment GROUP BY order_id,product_id HAVING COUNT(*)>1;
- 备份product_comment表。
CREATE TABLE bak_product_comment_161022 LIKE product_comment;
INSERT INTO bak_product_comment_161022 SELECT * FROM product_comment;
- 删除同一订单的重复评论。
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
参考
- 高性能可扩展MySQL数据库设计及架构优化 电商项目，sqlercn，https://coding.imooc.com/class/79.html
关于我：
linxinzhe，全栈工程师，目前供职于某500强通信企业。人工智能，区块链爱好者。
GitHub:https://github.com/linxinzhe
欢迎留言讨论，也欢迎关注我~
我也会关注你的哦！
网友评论