《高性能MySQL》第七章 MySQL 执行计划优化

通过 EXPLAIN 对 SQL 语句的执行计划进行分析，以此来判断一条 SQL 的执行效率，并作出对应的优化调整。

EXPLAIN 能够分析 select update insert replace delete 这些SQL。

一、执行计划分析

SQL 如何使用索引
1. 并不是某一列上存在索引，sql 在执行时就必定会使用该索引。
联接查询的执行顺序
查询扫描的数据行数

二、执行计划中的内容

以下面的查询SQL 为例，解释执行计划中输出内容的含义

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

id: 1
- ID 列中的数据为一组数字，表示执行 SELECT 语句的顺序
- ID 值相同时，执行顺序由上至下
- ID 值越大优先级越高，执行越靠前
select_type: SIMPLE
- SIMPLE：不包含子查询或是 UNION 操作的查询
- PRIMARY：查询中如果包含任何子查询，那么最外层的查询则被标记为 PRIMARY
- SUBQUERY：SELECT 列表中的子查询
- DEPENDENT SUBQUERY：依赖外部结果的子查询
- UNION：Union操作的第二个或是之后的查询的值为 union
- DEPENDENT UNION：当 UNION 作为子查询时，第二或是第二个后的查询的 select_type 值
- UNION RESULT：UNION 产生的结果集
- DERIVED：出现在 FROM 子句中的子查询
table: product_comment
- 输出数据行所在的表的名称
- <unionM,N>由ID 为 M，N 查询union 产生的结果集
- / 由 ID 为N 的查询结果的结果集
partition: NULL
- 对于分区表，显示查询的分区ID
- 对于非分区表，显示为 NULL
type: ALL
- 以下类型的性能从高到低依次排列
- system：这是 const 联接类型的一个特例，当查询的表只有一行时使用
- const：表中有且只有一个匹配的行时使用，如对主键或是唯一索引的查询，这是效率最高的联接方式
- eq_ref：唯一索引或是主键索引查找，对于每个索引键，表中只有一条记录与之匹配
- ref：非唯一索引查找，返回匹配某个单独值的所有行
- ref_or_null：类似于 ref 类型的查询，但是附加了对 NULL 值列的查询
- index_merge：该联接类型表示使用了索引合并优化方法
- range：索引范围扫描，常见于 between、>、< 这样的查询条件
- index：FULL index Scan 全索引扫描，同ALL 的区别是，遍历的是索引树
- ALL：FULL Table Scan 全表扫描，这是效率最差的联接方式
possible_keys: NULL
- 指出 MySQL 能使用哪些索引来优化查询，基于查询所使用到的列和过滤条件来判断的
- 查询列所涉及到的列上的索引都会被列出，但不一定会被使用
key: NULL
- 查询优化器优化查询实际所使用的索引
- 如果没有可用的索引，则显示为 NULL
- 如果查询使用了覆盖索引，则该索引仅出现在 Key 列中
key_len: NULL
- 表示索引字段的最大可能长度
- ken_len的长度由字段定义计算而来，并非数据的实际长度
ref: NULL
- 表示那些列或常量被用于查找索引列上的值
rows: 9400
- 表示 MySQL 通过索引统计信息，估算的所需读取的行数
- Rows 值的大小是个统计抽样结果，并不十分准确
filtered: 1.00
- 表示返回结果的行数占需读取行数的百分比
- Filtered 列的值越大越好，越大表示过滤的比例越高，最大100%
- Filtered 列的值依赖统计信息，并不十分准确
Extra: Using where
- Distinct：优化 distinct 操作，在找到第一匹配的元素后即停止找同样值的动作
- Not exists：使用 not exists 来优化操作
- Using filesort：使用额外操作进行排序，通常会出现在 order by 或 group by 查询中
- Using index：使用了覆盖索引进行查询
- Using teporary：MySQL 需要使用临时表来处理查询，常见于排序、子查询和分组查询
- Using where：需要在 MySQL 服务器层使用 WHERE 条件来过滤数据
- select tables optimized away：直接通过索引来获取数据，不用访问表

通过上面的查询语句，以及索引的设计规范，应该在 where 语句查询的部分设置索引，这里使用联合索引。

-- 查询表中，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 = 0.8 ，audit_rate = 0.2，则联合索引应该设计为：

CREATE INDEX idx_productID_auditStatus
ON product_comment(product_id,audit_status);

执行计划的限制

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

一、执行计划分析

二、执行计划中的内容

执行计划的限制

《高性能MySQL》目录