发表于 | 分类于 | 0 | 阅读次数 325

在线上查询异常日志的时候发现一个错误 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 子句里用不到的字段
  • 表的记录非常少
  • 有大量重复数据，选择性低，因为索引的选择性越高，查询效率越好，因为可以在查找时过滤更多行
  • 频繁更新的字段，如果创建索引要考虑其索引维护开销