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

Snipaste_20211108_140346.png

1. 排查过程

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

Snipaste_20211108_142044.png

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

2. 索引失效问题总结

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

3. B-Tree 索引

3.1 特性
完全匹配:index(name) => where name = 'chenkaixin12121'
范围匹配:index(age) => where age > 18
前缀匹配:index(name) => where name like 'chen%'
3.2 限制
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;

4. 创建索引的原则

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

Q.E.D.


盛年不重来,一日难再晨。