每天一个知识点-设计索引原则和索引失效

每日一题

设计索引参考的几个原则

索引的设计往往关乎于查询的速度，磁盘的占用，在面试中也是必问的一个问题，下面介绍设计索引过程中通常会考虑的几个点，但不是必须遵守的原则。

1. 最适合的索引列是出现在 WHERE 子句中，或连接子句中的列。
2. 建议使用唯一的索引，索引值的分布，基数越大，索引的效果越好。 例如有出生日期和性别这两列，使用出生日期很容易区分各行，使用性别列，大约每次都会扫面一半的行
3. 如果是字符串类需要建立索引，尽可能使用较短的字符串，或者使用字符串的前缀作为索引列没这样可以大大减少索引的存储时间，在检索时，buffer_pool中也可以容纳更多的键值，同时页减少了比对大小的计算量
4. 联合索引在创建时一定要考虑索引列的顺序，不一定非要考虑基数大的放在前面，也要考虑索引的使用频率，联合索引可以利用给最左的列来匹配行，同时也要考虑到索引覆盖的场景，能节省回表操作提高查询效率
5. 索引不是越多越好。额外的索引会额外的存储空间，降低写操作的性能，也会减缓表的修改速度，在生成查询计划时，要考虑哥哥索引也要花费时间。

索引失效的常见常见

1. 条件字段函数操作或者运算操作

   例如： where a+1 = 2 或者 where month(modified)=7;

2. 条件字段发生了隐式类型转换 或者发生了编码转换

   例如: where id = ‘123’

3. 联合索引不满足最左前缀

   但是在最新发布的mysql 版本中 如果不满足最左前缀 但是 第一索引类 基数小 比如说 性别字段，优化器会自东优化 类似于 性别 in （男，女）

4. 错误 like 使用
   例如 ： name like ‘%波儿’ 不满足最左前缀

5. 使用OR操作

6. 两列做比较

   例如 ： where r1 > r2 因为不知到r2的具体值

7. 不连续范围的比较

   一些不连续的范围的比较：in、not in、between…and、!= ，这些在查询返回多个字段时，会导致索引失效

8. is not null
   is null 不会导致索引失效。相反，is not null会导致索引失效。

9. 索引扫描结果超过全表的30%优化器会认为全表扫描更快

10. 索引数量过多，选择索引时间过长，会导致不能使用正确的索引