mysql 演示数据库：http://downloads.mysql.com/docs/sakila-db.zip
匹配全值
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.98\G;
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;
如果列名是索引，那么使用column_name is null 就会使用索引
explain select * from payment where rental_id is null\G;
实现索引匹配部分精确而其他部分进行范围匹配
explain select inventory_id from rental where rental_date='2006-02-14 15:16:03' and customer_id>=300 and customer_id<=400\G;
Index Condition PushDown(ICP)
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<=400\G;
5.5版本的复合索引
5.6版本的ICP
