mysql的索引使用注意事项以避免索引失效
在mysql中,建立好了索引,但是在使用时需要保证建立的索引在sql的查询中要使用到。所以在使用索引过程中需要注意一些问题,避免索引失效。
1:全值匹配原则和最佳左前缀原则
在案列表staffs表中,给name,age,和pos三个列建立了一个组合索引
执行查询,看一下最长见的3中情况,能够使用到索引
着3中情况都能够正常使用到索引。但值得注意的是where后面的条件的顺序和建立索引的顺序是一样的,才能够使用索引。现在来看看其他情况。
这里由于索引是name->age->pos的顺序建立的(图一),但是这个sql只使用到了age和pos,导致了建立的索引没有使用上,进而索引失效
从上面两个图看出,如果表有复合索引,但是使用时没有使用索引的第一个字段,则这个索引将不能够被使用。
查询从索引的最左最前开始,且不能够跳过索引中的列(第一列就像火车头,单独车头能用,单独车厢不能用,有车头,且车厢和车厢之间没有连接,后面的车厢不能用)如图。
2:不能够在索引列上做有关操作,如计算,(自动或强制)类型转换,使用函数,这都会导致该列的索引失效 如图。
由上面可以看出,两个sql的结果都是一样的,但是过程却大不相同,进而对查询的性能也会有影响,这是非常需要注意的(索引列上少计算)。
3:范围以后全失效。如图
由于age使用了一个范围,通过key_len看出后面列没有使用到索引。
4:少用 select * from ....... 这在任何情况下都需要注意 。
少用select * 要做到数据使用多少就取多少。
5:> < != 会导致索引失效产生全表扫描。 列
第二个 sql 的where 中使用到了 != 导致了全表扫描,且没有用到任何索引。上面的那个sql也使用了 != 但效果却大不相同,是因为select 后面是查询的是特定的列,如果这些列有一部分有索引,那么就能够使用到索引。
6 :列上使用 is null , is not null 也无法使用索引。
从图中可以看出 is null 和is not null 都没有使用到索引,但是is not null ,的结果和is null 的结果不一样。理论上能够使用到索引,但是实际没有使用到。
7:like 关键字 如果以通配符开头 会导致索引失效,如 like '%abcd '
通过测试可以看到,统配符在左边或者两边都有时,都会导致了索引失效。那么如果需求就是在左边或者两边都有like,使用覆盖索引就能够解决这个问题
这里的select 后面可以是建立索引时的那些字段中的一个或多个,但是包含了索引外的字段是,也是无法使用到索引的。
8:字符串不加单引号也会导致索引失效(这个过程会 包含类型转换)如图
9 : or 链接是索引会失效
最后上一张图。