MySQL中通过EXPLAIN如何分析SQL的执行计划详解
前言
在MySQL中,我们可以通过EXPLAIN命令获取MySQL如何执行SELECT语句的信息,包括在SELECT语句执行过程中表如何连接和连接的顺序。
下面分别对EXPLAIN命令结果的每一列进行说明:
.select_type:表示SELECT的类型,常见的取值有:
类型 | 说明 |
---|---|
SIMPLE | 简单表,不使用表连接或子查询 |
PRIMARY | 主查询,即外层的查询 |
UNION | UNION中的第二个或者后面的查询语句 |
SUBQUERY | 子查询中的第一个 |
.table:输出结果集的表(表别名)
.type:表示MySQL在表中找到所需行的方式,或者叫访问类型。常见访问类型如下,从上到下,性能由差到最好:
ALL | 全表扫描 |
---|---|
index | 索引全扫描 |
range | 索引范围扫描 |
ref | 非唯一索引扫描 |
eq_ref | 唯一索引扫描 |
const,system | 单表最多有一个匹配行 |
NULL | 不用扫描表或索引 |
1、type=ALL,全表扫描,MySQL遍历全表来找到匹配行
一般是没有where条件或者where条件没有使用索引的查询语句
EXPLAIN SELECT * FROM customer WHERE active=0;
一般是查询的字段都有索引的查询语句
EXPLAIN SELECT store_id FROM customer;
3、type=range,索引范围扫描,常用于<、<=、>、>=、between等操作
EXPLAIN SELECT * FROM customer WHERE customer_id>=10 AND customer_id<=20;
EXPLAIN SELECT * FROM customer WHERE create_date>='2006-02-13' ;
store_id
字段存在普通索引(非唯一索引)
EXPLAIN SELECT * FROM customer WHERE store_id=10;
customer、payment表关联查询,关联字段customer.customer_id
(主键),payment.customer_id
(非唯一索引)。表关联查询时必定会有一张表进行全表扫描,此表一定是几张表中记录行数最少的表,然后再通过非唯一索引寻找其他关联表中的匹配行,以此达到表关联时扫描行数最少。
因为customer、payment两表中customer表的记录行数最少,所以customer表进行全表扫描,payment表通过非唯一索引寻找匹配行。
EXPLAIN SELECT * FROM customer customer INNER JOIN payment payment ON customer.customer_id = payment.customer_id;
eq_ref一般出现在多表连接时使用primary key或者unique index作为关联条件。
film、film_text表关联查询和上一条所说的基本一致,只不过关联条件由非唯一索引变成了主键。
EXPLAIN SELECT * FROM film film INNER JOIN film_text film_text ON film.film_id = film_text.film_id;
const/system出现在根据主键primary key或者 唯一索引 unique index 进行的查询
根据主键primary key进行的查询:
EXPLAIN SELECT * FROM customer WHERE customer_id =10;
EXPLAIN SELECT * FROM customer WHERE email ='MARY.SMITH@sakilacustomer.org';
.possible_keys: 表示查询可能使用的索引
.key: 实际使用的索引
.key_len: 使用索引字段的长度
.ref: 使用哪个列或常数与key一起从表中选择行。
.rows: 扫描行的数量
.filtered: 存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例(百分比)
.Extra: 执行情况的说明和描述,包含不适合在其他列中显示但是对执行计划非常重要的额外信息
最主要的有一下三种:
Using Index | 表示索引覆盖,不会回表查询 |
---|---|
Using Where | 表示进行了回表查询 |
Using Index Condition | 表示进行了ICP优化 |
Using Flesort | 表示MySQL需额外排序操作, 不能通过索引顺序达到排序效果 |
什么是ICP?
MySQL5.6引入了Index Condition Pushdown(ICP)的特性,进一步优化了查询。Pushdown表示操作下放,某些情况下的条件过滤操作下放到存储引擎。
EXPLAIN SELECT * FROM rental WHERE rental_date='2005-05-25' AND customer_id>=300 AND customer_id<=400;
优化器首先使用复合索引idx_rental_date过滤出符合条件rental_date='2005-05-25'
的记录,然后根据复合索引idx_rental_date回表获取记录,最终根据条件customer_id>=300 AND customer_id<=400
过滤出最后的查询结果(在服务层完成)。
在5.6版本之后:
MySQL使用了ICP来进一步优化查询,在检索的时候,把条件customer_id>=300 AND customer_id<=400
也推到存储引擎层完成过滤,这样能够降低不必要的IO访问。Extra为Using index condition
就表示使用了ICP优化。
参考
《深入浅出MySQL》
总结
更多相关文章
- MySQL系列多表连接查询92及99语法示例详解教程
- Android(安卓)- Manifest 文件 详解
- Android的Handler机制详解3_Looper.looper()不会卡死主线程
- Selector、shape详解(一)
- android2.2资源文件详解4--menu文件夹下的菜单定义
- Android发送短信方法实例详解
- Android(安卓)读取资源文件实例详解
- 详解Android中的屏幕方向
- Android学习笔记(10)————Android的Listview详解1(ArrayAdapte