概述
在 MySQL 查询语句遇到性能问题时,常常会猜测是其索引设置或者 SQL 写法问题导致。通常地使用 explain 查看 SQL 执行计划,从执行计划中分析出当前要优化 SQL 的性能消耗关键点。
解读执行计划
使用 explain 关键字查看 SQL 的执行计划,查询结果包含的信息在各列的指标中展示出来,这些列包括:id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, extra。先逐个简单地介绍执行计划结果中各字段的含义。
- id
id 列值为数值时,代表执行顺序,值相同则执行顺序从上到下,值不同则执行顺序从大到小。
- select_type
查询类型,一般地有:SIMPLE, PRIMARY, SUBQUERY等,用来标识 SQL 中出现的简单查询(不包含子查询)、外层主查询、子查询的,不一一列出。
-
table:表名或表别名
-
partitions:涉及分区表查询时,用来标识分区
-
type
标识表的连接类型,性能由高到低依次为:system, const, eq_ref, ref, fulltext, ref_or_null, index_merge, unique_subquery, index_subquery, range, index, ALL。
常见的有const, eq_ref, ref, ref_or_null, range, index, ALL,其中:
const:查询匹配到一条记录,通常是命中主键索引或者唯一索引,只需要读一次数据,性能极佳。
SELECT * FROM tbl_name WHERE primary_key = 1;
SELECT * FROM tbl_name WHERE primary_key_part1 = 1 AND primary_key_part2 = 2;
eq_ref:典型的两表关联的时候命中主键索引或者唯一索引,即为 eq_ref 类型,性能也非常不错。
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
ref:where 或表关联条件中的字段命中了普通索引(非主键和唯一索引,按索引最左匹配原则),即为 ref 类型,这种连接类型性能也不错。
SELECT * FROM ref_table WHERE key_column=expr;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
ref_or_null:和 ref 类型类似,但需要对 NULL 值进行查找匹配,如
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;
range:范围查找,如使用了 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, LIKE, or IN() 作为条件匹配方式
SELECT * FROM tbl_name
WHERE key_column = 10;
SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name
WHERE key_column IN (10,20,30);
SELECT * FROM tbl_name
WHERE key_part1 = 10 AND key_part2 IN (10,20,30);
index:索引树扫描,性能最差和 ALL 类型一样,但索引树大小通常小于全表数据大小,性能稍稍优于 ALL 类型。
ALL:没有命中到索引,全表扫描,性能最差。
-
possible_keys:即可能使用到的索引
-
key:实际使用到的索引
-
key_len:实际使用到的索引长度
-
ref:和索引进行比较匹配的列
-
rows:预估的扫描行数
-
filtered:通过条件筛选出来的行数占比
-
extra:附带一些查询中的一些额外信息,需要注意的是,做性能优化时,关注 extra 信息中的 using filesort 和 using temporary,因为在排序和产生临时表时将无法使用索引,此时可能需要对索引结构进行修改或改善查询SQL。
优化策略
建立合适的索引
给 SQL 中的表建立合适的索引,一般地,索引建立原则遵循:
- 在识别度较大的列上建立索引
- 出现在条件中或关联字段中或 group by、order by 字句中的列可以适当建立索引
- 注意索引最左前缀匹配原则
- 从实际情况出发建立索引,并非所有列都适合建索引
关注索引的命中
- 建好索引后,重新查看执行计划,看当前查询是否命中索引,一般地,索引命中的条件
优化 SQL 的写法
- 调整 SQL 写法,使之更好地命中索引。
表设计上的优化
考虑业务存储设计上是否有优化的空间或者分库分表等手段。
性能测试
MySQL执行的总时长可以分为:duration / fetch,其中 duration 表示 SQL 实际执行的时长,fetch 表示获取查询结果集传输的时间消耗。fetch 的时间消耗可能每次不同,duration 的时间消耗则是 SQL 优化过程中不断使其尽量减少的一个指标。
参考文档:https://dev.mysql.com/doc/refman/5.6/en/explain-output.html