Mysql复合索引最左匹配原则以及索引失效条件

复合索引最左匹配原则

复合索引又叫联合索引。两个或更多个列上的索引被称作复合索引。

对于复合索引：例如索引是key index （a,b,c）。可以支持a | a,b| a,c| a,b,c 组合进行查找，但不支持 b| c| b,c进行查找。就算是你弄乱了顺序如 c,b,a，mysql也会自动帮你改为a,b,c，然后用到索引 。这就是mysql最左匹配原则，查询条件里面要有复合索引最左边的那个字段才会用到索引。当最左侧字段是常量引用时，索引就十分有效。
如我的表格设计是这样子：

KEY `waybill_key` (`waybill_code`,`waybill_id`),

这里给waybill_code和waybill_id添加了复合索引
执行语句：

select * from waybill where waybill_code = '2132131' 

用到了索引（explain查询type为ref），但是执行语句：

select * from waybill where waybill_id = '2132131' 

并没有用到索引（explain查询type为all）

所以说创建复合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；仅对后面的任意列执行搜索时，复合索引则没有用处。

用EXPLAIN 来查看语句是否用到了索引

可以用 EXPLAIN 来查看索引是否生效，如：

EXPLAIN select * from waybill where waybill_code = '%2132131%' 

查询结果：

这里的type为ref说明已经用到了索引，这里附上这些type的说明：

类型	说明
null	MYSQL不用访问表或者索引就直接能到结果
system	表只有一行，MyISAM引擎
const	常量连接，表最多只有一行匹配，通用用于主键或者唯一索引比较时
eq_ref	每次与之前的表合并行都只在该表读取一行，这是除了system，const之外最好的一种，特点是使用=，而且索引的所有部分都参与join且索引是主键或非空唯一键的索引
ref	如果每次只匹配少数行，那就是比较好的一种，使用=或<=>，可以是左覆盖索引或非主键或非唯一键
fulltext	全文搜索
ref_or_null	与ref类似，但包括NULL
index_merge	表示出现了索引合并优化(包括交集，并集以及交集之间的并集)，但不包括跨表和全文索引。这个比较复杂，目前的理解是合并单表的范围索引扫描（如果成本估算比普通的range要更优的话）
unique_subquery	在in子查询中，就是value in (select…)把形如select unique_key_column的子查询替换。PS：所以不一定in子句中使用子查询就是低效的
index_subquery	同上，但把形如”select non_unique_key_column“的子查询替换
range	常数值的范围
index	索引树扫描。a.当查询是索引覆盖的，即所有数据均可从索引树获取的时候（Extra中有Using Index）；b.以索引顺序从索引中查找数据行的全表扫描（无 Using Index）；c.如果Extra中Using Index与Using Where同时出现的话，则是利用索引查找键值的意思；d.如单独出现，则是用读索引来代替读行，但不用于查找
all	全表扫描(full table scan)

从上往下性能越来越慢，即：

null > system > const > eq_ref > ref > range > index  > all 

all 全表扫描的时候最慢

mysql explain type 说明出自：mysql explain type的区别和性能优化

索引失效的条件

1、不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描

2、存储引擎不能使用索引范围条件右边的列

3、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *

4、mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描

5、is null,is not null也无法使用索引

6、like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描的操作。

问题：解决like‘%字符串%’时索引不被使用的方法？

字符串不加单引号索引失效

SELECT * from staffs where name='2000';  
-- 因为mysql会在底层对其进行隐式的类型转换
 
SELECT * from staffs where name=2000;  
--- 未使用索引

一般性建议

1、对于单键索引，尽量选择针对当前query过滤性更好的索引

2、在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

3、在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引

4、尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

出自：联合索引生效和失效的条件看这里