文章目录

  • 1. 索引失效的场景
  • 1.1 隐式类型转换问题
  • 1.2 联合索引违反最左前缀匹配原则
  • 1.3 对索引列运算
  • 1.4 is null 判断
  • 1.5 like 使用不当
  • 1.6 对索引列使用函数
  • 1.7 in 使用不当
  • 1.8 or 使用不当
  • 1.9 查询优化不走索引
  • 2. 没必要建立索引的场景


1. 索引失效的场景

1.1 隐式类型转换问题

如果表字段定义的类型为字符串char，但是在搜索时指定的 where条件传入的参数却是数字类型INT，那么会存在 隐式类型转换的问题，从而造成不走索引的慢查询

1.2 联合索引违反最左前缀匹配原则

联合索引违反了最左前缀匹配原则：带头大哥不能死，中间兄弟不能断。例如建立了联合索引 (a, b, c)，以下使用都会有问题

  1. 违反最左前缀匹配原则
    where b = 1 and c = 1
  2. 联合索引 b 的范围查询会使范围条件字段之后的索引失效，导致之后 c 字段没有使用到索引，也就是只用到了索引 a 和 b，联合索引部分失效
    where a = 1 and b > 1 order by c

1.3 对索引列运算

对索引列的运算会导致索引失效，应该避免

运算符

含义

+

加

-

减

*

乘

/

除以

!=、<>

不等于

%

取余

1.4 is null 判断

is null| is not null | 都会导致索引失效

1.5 like 使用不当

like 查询 ‘name%’ 的百分号加在右边才走索引，可以使用覆盖索引来避免 ‘%name%’ 查询索引失效

1.6 对索引列使用函数

例如select * from template t where ROUND(t.logicdb_id) = 1，不会走索引列，这种情况下应该建立基于函数的索引，如 ROUND(t.logicdb_id)

1.7 in 使用不当

in 查询肯定会走索引，但是当 in 的取值范围较大而表数据较少时会导致索引失效，走全表扫描，该情况与下文 1.9 节类似。如果使用了 not in，则不走索引

1.8 or 使用不当

条件中有or，即使其中有部分字段带索引也不会使用，要想使用or又想让索引生效，只能将 or 条件中的每个列都加上索引

1.9 查询优化不走索引

对于数据量比较小的表，如果 MySQL 查询优化时认为全表扫描更快时也不会走索引

2. 没必要建立索引的场景

  1. 字段的唯一性差，比如性别，只有男、女两种，没有必要建索引
  2. 频繁更新的字段不用，因为索引频繁更新有性能损耗
  3. where 中不用的字段没必要建索引
  4. B+ 树索引在搜索可为 NULL的列时索引性能不好，通常在建表时会将字段置为 NOT NULL ，并指定 DEFAULT默认值
Mysql难以优化引用可空列查询，它会使索引、索引统计和值更加复杂。可空列需要更多的存储空间，还需要mysql内部进行特殊处理。
  可空列被索引后，每条记录都需要一个额外的字节，还能导致 MyIsam 中固定大小的索引变成可变大小的索引。
                                                                                     ——《高性能mysql》