sql优化学习

执行计划

通过执行计划，我们可以分析一条sql语句的性能，是我们进行sql优化的关键。我们看看执行计划包含了哪些信息。

id

每个sql都有一个id，如果使用子查询，那么id就不同。如果使用union查询，会产生一个id=NULL的临时表，主要对UNION表结果进行去重合并。

select_type

• SIMPLE

简单的查询，不涉及union或子查询

• PRIMARY

最外层的查询，比如子查询，最外层的就是PRIMARY

• SUBQUERY

如果sql包括子查询，子查询就是SUBQUERY。

select id from orders where (select id from orders) or id = 1

如果是简单的子查询，mysql会自动转换为连接查询。

select id from orders where (select id from orders)

• DEPENDENT SUBQUERY

子查询依赖了父条件

select id from orders o where id in (select id from orders and o.id = 1) or o.id=2

• UNION

union查询，左边是PRIMARY，其余为UNION

• UNION RESULT

union会创建临时表，用来对结果去重合并，那么这个表类型就是UNION RESULT

• DEPENDENT UNION

union查询依赖外部查询

select id from orders where id in (select id from orders where id=1 union select id from orders where id=2)

我们又个疑惑，这个查询并没有依赖外部查询，但是确实结果有DEPENDENT UNION，原因还是mysql的优化

• DERIVED

物化子查询

select * from (select *,count(*) from orders group by id) AS xxx where xxx.id=1

• MARTERIALIZED

物化子查询后，进行关联查询

select * from order where id in (select order_id from order_item)

partitions

和分区表有关

type

• system

表只有一条数据，且统计数据精确，比如MyISAM等。如果是InnoDB则变成了const

• const

使用主键或唯一索引等值查询，如果是非唯一索引，那么就不是const。也就是const的结果一定唯一

select * from orders where id=1

• eq_ref

如果被驱动表通过主键或唯一索引关联查询

select  o.* from orders o inner join orders o2 on o.id=o2.id

• ref

通过普通索引查询，可能返回多列

select * from order where creater=1 -- creater是普通索引

• fulltext

略

• ref_or_null

等值匹配为null

select * from orders where order_no=xxx and order_no is null; -- order_no必须可以空

• index_merge

索引合并

• unique_subquery

• index_subquery

• range

范围查询，between、>、<、in

• index

全表扫描但走覆盖索引

select id from orders where order_no like '%xx'

• all

全表扫描

possible_keys

可能用到的索引

key

实际用到的索引。如果走全表但走覆盖索引时，key是有值的，但possible_keys无值

key_len

实际使用到的索引长度。根据这个字段可以判断组合索引是否全部使用，如果全部使用，则key_len为组合字段的长度和。

可变字段+2（eg: varchar）

可null字段+1

如果是字符类型，比如utf8，varchar(50) null的长度为50*3+2+1

如果是int类型，比如int null的长度为4+1

ref

=const，表示等值匹配

=列名，比如关联查询为eq_ref，则为被驱动表的列名

rows

预估需要读取的记录数

filtered

在rows中，有多少百分比的记录满足条件

extra

额外信息。

• impossible where

where永远为false

select * from orders where id is null

• using join buffer

• not exists

在连接查询中，条件is null不可能满足

select o.* from orders o left join order_item oi on o.id = oi.order_id where o.id is null

• using intersect/using union/using sort_union

索引合并

• zero limit
• using filesort

排序操作没用到索引，会在磁盘或内存中进行排序，非常耗性能

• using temporary

使用了临时表，常见的如distinct/group by/union

select * from orders group by creater -- creater是索引

这个sql同时用到了using filesort/using temporary，因为group by操作会先进行order by，再group by