匹配全值

explain select * from rental where rental_date='2005-05-25 17:22:10' and inventory_id=373 and customer_id=343

匹配值的范围查询

explain select * from rental where customer_id >= 373 and customer_id<400

仅仅使用索引中的最左列进行查询，比如：在col1+col2+col3字段上的联合索引中，能够使用的索引情况可以有：col1 (col2+col3) ,col1+col2+col3。不能被使用的索引：col2 (col2+col3) 等情况。以payment表为例

alter table payment add index idx_payment_date (payment_date,amount,last_update);

explain select * from payment where payment_date='2006-02-14 15:16:03' and last_update='2006-02-15 22:12:32'G;

explain select * from payment where amount=3.98 and last_update='2006-02-15 22:12:32'G;

explain select * from payment where amount=3.98 and payment_date='2006-02-14 15:16:03'G;

explain select * from payment where last_update='2006-02-15 22:12:32' and payment_date='2006-02-14 15:16:03'G;

当查询的的列都在索引的字段中时，查询的效率更高

explain select last_update from payment where amount=3.98G;

explain select last_update from payment where payment_date='2006-02-14 15:16:03'G;

仅仅使用索引中的第一列，并且只包含索引第一列的开头一部分进行查找

explain select title from film_text where title like 'AFRICAN%'G;

explain select description from film_text where description like 'AFRICAN%'G;

explain select * from payment where rental_id is nullG;

explain select inventory_id from rental where rental_date='2006-02-14 15:16:03' and customer_id>=300 and customer_id<=400G;

mysql 5.6引入了Index Condition PushDown(ICP) 的特性，进一步优化了查询，某些情况下的条件过滤操作下放到存储引擎

explain select * from rental where rental_date='2006-02-14 15:16:03' and customer_id>=300 and customer_id<=400G;