索引失效问题

发表于 分类于 阅读次数： 本文字数： 577 阅读时长 ≈ 2 分钟

在线上查询异常日志的时候发现一个错误 IOException: Broken pipe，根据日志初步判断是由于慢 sql 所导致连接超时，以下是排查过程

image.png

排查过程

  1. 先看下这条 sql，没有发现问题，使用 explain 分析 sql，发现没有使用到索引

image.png

  1. 查看分析结果的 Extra 列，看到 Range checked for each record (index map: 0x10)，查询后发现原因是由于 pi 这张表所使用排序规则是 utf8mb4_general_ci，而 ps 表使用的是 utf8mb4_bin
  2. 修改排序规则，可以看到已经使用到了索引

索引失效问题总结

  • 使用索引列进行连接，字符集或排序规则不一致
  • 隐式类型转换
  • 使用了左模糊
  • 对索引列进行计算或使用内置函数
  • 组合索引违背最左匹配原则
  • 索引列使用了负向查询（!=，<>，!<，!>，not，not in，not like，not exists），可能会导致索引失效
  • where 语句中包含 or 时，可能会导致索引失效

B-Tree 索引

特性
  • 完全匹配：index(name) => where name = ‘chenkaixin12121’
  • 范围匹配：index(age) => where age > 18
  • 前缀匹配：index(name) => where name like ‘chen%’
限制
1
2
3
4
5
6
7
8
index(name,age,sex)

# 查询条件不包括最左列，无法使用索引
where age = 18 and sex = 1;
# 跳过了索引中的列，则无法完全使用索引，只能使用 name 索引
where name = 'chenkaixin12121' and sex = 1;
# 查询中有某个列的范围（模糊）查询，则其右边所有列都无法使用索引，只能用到 name,age 索引
where name = 'chenkaixin12121' and age > 18 and sex = 1;

创建索引的原则

建议创建索引的场景
  • select 语句，频繁作为 where 条件的字段
  • update/delete 语句的 where 条件
  • distinct 所使用的字段
  • 字段的值有唯一性约束
  • 对于多表查询，联接字段应创建索引，且类型务必保持一致
  • 避免隐藏类型转换
不建议创建索引的场景
  • where 子句里用不到的字段
  • 表的记录非常少
  • 有大量重复数据，选择性低，因为索引的选择性越高，查询效率越好，因为可以在查找时过滤更多行
  • 频繁更新的字段，如果创建索引要考虑其索引维护开销