索引失效
- where 条件中包含 or 时，可能会导致索引失效
- 尽量避免使用 or 语句，可以根据情况使用 union 或 in 等来代替，这两个语句的执行效率也比 or 高
- 不同版本 MySQL，关于 or 走索引情况不一致，需要具体分析
- MySQL 5.7 版本中，当 or 两边都是单列索引，索引生效
- where 语句中索引列使用了负向查询，可能会导致索引失效
- 负向查询包括：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE 等
- 其中负向查询并不绝对会索引失效，这要看优化器判断，全表扫描或者走索引哪个成本低了
- 索引字段可以为 null，使用 is null 或 is not null，可能会导致索引失效
- null 的列使索引/索引统计/值比较更加复杂，对 MySQL 来说更难优化
- null 在 MySQL 内部需要特殊处理，增加数据库处理记录的复杂性，同等条件下，表中有较多空字段的时候，数据库的处理性能会降低很多
- null 值需要更多的存储空间，无论是表中还是索引中每行中的 null 的列都需要额外的空间来标识
- 对 null 的处理，只能采用 is null 或 is not null，而不能使用 =、in、<、<>、!=、not in 这些符号操作
- 在索引列上使用内置函数，一定会导致索引失效
- 建议尽量在应用程序中进行计算和转换
- 隐式类型转换导致的索引失效
- 索引列 user_id 为 varchar 类型，不会命中索引，因为 MySQL 做了隐式类型转换，调用函数将 user_id 做了转换
- select * from `user` where user_id= 12;
- select * from `user` where CAST(user_id AS signed int) = 12;
- 隐式字符编码转换导致的索引失效
- 当两个表之间做关联查询时，如果两个表中关联的字段字符编码不一致的话，MySQL 可能会调用 CONVERT 函数，将不同的字符编码进行隐式转换从而达到统一；作用到关联的字段时，就会导致索引失效
- Demo : select l.operator from `trade_log` l, `trade_detail` d where d.tradeid = l.tradeid and d.id = 4;
- 其中 d.tradeid 字符编码为 utf8，而 l.tradeid 的字符编码是 utf8mb4
- 因为 utf8mb4 是 utf8 的超集，所以 MySQL 在做转换时会用 CONVERT 将 utf8 转成 utf8mb4；简单来看，就是 CONVERT 作用到了 d.tradeid 上，因此索引失效
- Suggest :
- 将关联的字段的字符编码统一
- 若无法统一字符编码时，手动将 CONVERT 函数作用于关联式 = 的右侧，起到字符编码统一的目的；这里是强制将 utf8mb4 转为 utf8，注意：从超集向子集转换是有数据截断的风险的
- select l.operator from `trade_log` l, `trade_detail` d where d.tradeid = CONVERT(l.tradeid USING utf8) and d.id = 4;
- 对索引列进行运算，一定会导致索引失效
- 运算符如：+、-、*、/ 等
- Demo : select * from `user` where age - 1 = 10;
- Suggest : 优化，将运算放在值上，或者在应用程序中算好
- select * from `user` where age = 10 - 1;
- like 通配符可能会导致索引失效
- 以 % 开头，索引失效
- Suggest :
- 去掉开头的 %
- select * from `user` where `name` like '李%';
- 利用覆盖索引来名中索引
- select name from `user` where `name` like '%李%';
- 联合索引中，违背最左匹配原则，一定会导致索引失效
- MySQL 优化器的最终选择，不走索引
- 即使完全符合索引生效的场景，考虑到实际数据量等原因，最终是否使用索引还要看 MySQL 优化器的判断；也可以在 SQL 中强制表明走哪个索引
优化索引的建议：
- 禁止在更新十分频繁，区分度不高的属性上建立索引
- 更新会变更 B+树，更新频繁的字段建立索引会大大降低数据库性能
- "性别"这种区分度不大的属性，建立索引是没有意义的，不能有效过滤数据，性能和全表扫描类似
- 建立联合索引，必须把区分度高的字段放在前面
