索引失效问题

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

5. 索引调优

5.1 长字段的调优

新增一个字段，使用函数计算 hash 值（CRC32 或者 FNV64），存储起来，并将该字段添加索引，查询时使用 first_name_hash = CRC32('chenkaixin12121') 查询即可

缺点：like 无法使用该方式进行优化
解决方案：添加前缀索引

索引选择性 = 不重复的索引值/数据表的总记录数

# 完整列的选择性
select count(distinct first_name)/count(*) from employees;
# 计算前缀索引的长度
select count(distinct left(first_name, 8))/count(*) from employees;
# 添加索引
alter table employees add key (first_name(8))

注意：无法做 order by, group by；无法使用覆盖索引

5.2 单列索引和组合索引

SQL 存在多个条件，多个单列索引，会使用索引合并
如果出现索引合并，往往说明索引不够合理
如果 SQL 暂时没有性能问题，暂时可以不管
组合索引要注意索引列顺序

5.3 覆盖索引

对应索引 X，SELECT 的字段只需从索引就能获得，而无需到表数据里获取，这样的索引就叫覆盖索引

覆盖索引能提交SQL的性能
SELECT 尽量只返回想要的字段（使用覆盖索引，减少网络传输的开销）

5.4 重复索引

索引是有开销的，增删改的时候，索引的维护开销，索引越多，开销越大，条件允许的情况下，尽量少创建索引

explain select * from salaries where from_date = '1986-06-26' order by emp_no;
索引 index(from_date): type=ref extra=null，使用了索引
索引 index(from_date) 某种意义上来说就相当于 index(from_date, emp_no)

修改索引 index(from_date,to_date) 再次执行
explain select * from salaries where from_date = '1986-06-26' order by emp_no;
索引 index(from_date,to_date): type=ref extra=Using filesort 说明 order by 子句无法使用索引
索引 index(from_date, to_date) 某种意义上来说就相当于index(from_date, to_date, emp_no)，不符合最左前缀原则，所以order by子句无法使用索引

5.5 冗余索引（针对 B-Tree 和 B+Tree）

如果已经存在索引 index(A,B)，又创建了 index(A)，那么 index(A) 就是 index(A,B) 的冗余索引

5.6 未使用的索引

某个索引根本未曾使用，累赘，删除