在线上查询异常日志的时候发现一个错误
IOException: Broken pipe
,根据日志初步判断是由于慢 sql 所导致连接超时,以下是排查过程
1. 排查过程
- 先看下这条 sql,没有发现问题,使用 explain 分析 sql,没有使用到索引
- 查看分析结果的 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 时,可能会导致索引失效
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.