文章目录
- 索引失效
- 失效情况总结
- 特别问题：
- 1. or条件索引情况
- in和exists的效率比较
索引失效
先看下索引失效的情况
失效情况总结
- like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
- or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效。
- 组合索引，不是使用第一列索引，索引失效。
- 数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
- 在索引列上使用 IS NULL 或 IS NOT NULL操作。索引是不索引空值的，所以这样的操作不能使用索引，可以用其他的办法处理，例如：数字类型，判断大于0，字符串类型设置一个默认值，判断是否等于默认值即可。
- 在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。
- 对索引字段进行计算操作。
- 在索引字段上使用函数。
- 当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。
特别问题：
1. or条件索引情况
通常来说，只要or条件中的每一列都加上了索引，就会走索引。
除[上述总结的失效情况](# 失效情况总结)外，以下情况，or条件索引也会失效：
- 键值较少的列(重复数据较多的列)
假如索引列TYPE有5个键值，如果有1万条数据，那么 WHERE TYPE = 1将访问表中的2000个数据块。
再加上访问索引块，一共要访问大于200个的数据块。
如果全表扫描，假设10条数据一个数据块，那么只需访问1000个数据块，既然全表扫描访问的数据块少一些，肯定就不会利用索引了。
- 当or条件中多列都有索引时，其中type包含index_merge，都会用到索引，如果有一个列没索引，用or的话，则会全表扫描
in和exists的效率比较
如何选择
1）子表数据量比外表数据量少，使用in。
2）子表数据量比外表数据量大，使用exists。
3）子表与外表数据量大小差不多，用in与exists的效率相差不大。
原因分析
1）in语句：使用hash将外表与内表连接。select * from A where id in (select id from B) 会用到A表的id索引
以上查询使用了in语句,in()只执行一次,它查出B表中的所有id字段并缓存起来.之后,检查A表的id是否与B表中的id相等,如果相等则将A表的记录加入结果集中,直到遍历完A表的所有记录.如:A表有10000条记录,B表有1000000条记录,那么最多有可能遍历100001000000次,效率很差.
再如:A表有10000条记录,B表有100条记录,那么最多有可能遍历10000100次,遍历次数大大减少,效率大大提升.
2）exists是对外表做loop循环，每次loop循环再对子表进行访问。select a.* from A a where exists(select 1 from B b where a.id=b.id) 会用B表的id索引
以上查询使用了exists语句,exists()会执行A.length次,它并不缓存exists()结果集,因为exists()结果集的内容并不重要,重要的是结果集中是否有记录,如果有则返回true,没有则返回false.当B表比A表数据大时适合使用exists(),因为它没有那么遍历操作,只需要再执行一次查询就行.
如:A表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断A表中的id是否与B表中的id相等.
如:A表有10000条记录,B表有100000000条记录,那么exists()还是执行10000次,因为它只执行A.length次,可见B表数据越多,越适合exists()发挥效果.
再如:A表有10000条记录,B表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000*100次,因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快.
3）not in 和 not exists
使用not in 会内外表都全表扫描；使用not exists还能用上内表（子表）的索引。所以，一定建议使用Not exists
t in 会内外表都全表扫描；使用not exists还能用上内表（子表）的索引。所以，一定建议使用Not exists
