在MySQL中,我们可以通过EXPLAIN命令获取MySQL如何执行SELECT语句的信息,包括在SELECT语句执行过程中表如何连接和连接的顺序。
下面分别对EXPLAIN命令结果的每一列进行说明:
EXPLAIN SELECT * FROM customer WHERE active=0;
EXPLAIN SELECT store_id FROM customer;
EXPLAIN SELECT * FROM customer WHERE customer_id>=10 AND customer_id<=20;
注意这种情况下比较的字段是需要加索引的,如果没有索引,则MySQL会进行全表扫描,如下面这种情况,create_date字段没有加索引:
EXPLAIN SELECT * FROM customer WHERE create_date>='2006-02-13' ;
store_id
字段存在普通索引(非唯一索引)
EXPLAIN SELECT * FROM customer WHERE store_id=10;
ref类型还经常会出现在join操作中:
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;
EXPLAIN SELECT * FROM film film INNER JOIN film_text film_text ON film.film_id = film_text.film_id;
EXPLAIN SELECT * FROM customer WHERE customer_id =10;
根据唯一索引unique index进行的查询:
EXPLAIN SELECT * FROM customer WHERE email ='MARY.SMITH@sakilacustomer.org';
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;
在5.6版本之前:
优化器首先使用复合索引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》PDF文字版(全)PDF 下载 http://www.linuxidc.com/Linux/2016-05/130922.htm