前提:建立了一个employee表,同时建立了一个组合索引lastName,gender 。
1.最常说的like匹配
例1 explain select * from employee where lastName like '%lucy';
例2 explain select * from employee where lastName like 'lucy%'
结论:例1索引失效 ,例2索引生效(匹配列前缀 'lucy%')
2. 强制类型转换会让索引失效
例如:explain select * from employee where gender =1 or lastName = 'lucy'
例如:explain select * from employee where gender =1 and lastName = 'lucy'
结论:当mysql优化器自动进行强制类型转换时,索引失效。
3.最左匹配原则
例1 explain select * from employee where lastName = 'lucy' and gender =1;
例2 explain select * from employee where gender =1 and lastName = 'lucy';
例3 explain select * from employee where lastName = 'lucy';
例4 explain select * from employee where gender =1;
结论:当建立一个组合索引时,根据例1和例2 得出 where条件后面的排放顺序不会影响索引的命中;根据例3 例4 得出如果组合索引中有部分字段缺失,按照建索引的顺序从左到右,依次匹配如果前一个列不匹配,则后一个列索引失效。
4. where条件中使用or即使有索引也会失效
explain select * from employee where lastName = 'lucy' or gender =1;
以上内容转自网络
更多讯息欢迎添加小优:DKA-2018