【HBZ分享】Mysql索引设计的原则， 失效场景，唯一与普通索引区别，聚簇与非聚簇索引大综合

索引失效场景

1. 最左匹配原则： 如果是联合索引，一定要遵从最左匹配原则，理论上可以将顺序打乱，但实际最好是按顺序写，养成良好的sql书写习惯。
2. 设计联合索引时，一定要把最常用的字段往左边放，避免查询where出现跳过中间字段，直接写后面的，这样索引会不生效，如果一定要跳过，则建议把后面字段单独建立个索引。
3. 如果存在or连接，or的两边字段一定要单独创建各自的索引，不要使用联合索引。
4. 使用like时，尽可能的左边是精确查询，因为这样依旧可以走索引，如果左边也是模糊查询，则索引不会生效
5. 重复率极高的字段要注意索引失效，因为mysql优化器会判定走索引和走全表扫描区别不大，最终会选择走全表
6. 字符串不加【引号】会导致索引失效，注意书写规范，如果是varchar类型的即使是像888这种数字也要加上引号’888’
7. 索引字段参与函数，运算符操作了，会导致索引失效
8. IS NULL不走索引，IS NOT NULL会走索引，设计字段时，如果没有必须要求是NULL，或者sql中不会出现IS NULL条件，则最好默认值给个字符串，不要让值有NULL的场景

索引设计与创建原则

1. 高频词查询 且 数据量大的表，要建立索引。
2. 经常需要排序，分组 和 联合操作的字段需要创建联合索引
3. 短索引可以提升访问IO效率，对于BLOB, TEXT或很长的varchar列使用前缀索引，因为索引越短，效率越高。(前缀索引就是根据某个字段的内容前n个字符创建索引，比如前10个字符创建索引，那么他在查询时就只会比对前10个前缀字符，使得索引列体积更小，性能更高。)
4. 删除无用索引，同个列上创建索引越多，维护索引成本就越高，优化器在选择判断上也会扫描所以包含该字段的索引，会导致性能下降，而且索引多了也浪费磁盘资源，索引就相当于一个字段
5. 根据业务需求设计好联合索引，尽可能让查询的字段都包含在联合索引范围内，防止回表查询。
6. 尽量选择区分度高的列作为索引，区分度越高越好，比如唯一索引，避免某个列有大量重复的数据。
7. 索引列不要参与计算，或 带函数的查询，因为索引不生效
8. 尽量扩展利用现有的索引，不是新增索引，比如可以给联合索引里在追加个字段来取代单独创建个索引。
9. 避免索引字段使用NULL， 含空值的列很难优化的，可以给个默认值，比如0或者空字符串这些。
10. order by时，也要遵循最左原则，并且多个字段要都升序或者都降序，如果A字段升序但B字段降序就会出现一部分查索引，一部分走全表吗，从extra字段看就是即有using index, 又有using filesort
11. 如果不可避免filesort，大量数量排序时，可以增大排序缓冲区大小，即配置sort_buffer_size(默认256K)

也就是：
如果排序数据大小 < sort_buffer_size, 则为内存排序。
如果排序数据大小 > sort_buffer_size, 则为磁盘排序。

唯一索引 与 普通索引

1. 两者特点:
   • 普通索引: 当查询到【最后一个】不是目标值的情况下，会结束查询
   • 唯一索引: 当查询到【第一个】目标值，就会立即结束查询
2. 两者在查询性能上大多是旗鼓相当，效率相同的。只有当目标数据在当前数据页最后一个的时候，唯一索引不会跨页，但普通索引会多一个页查询是否是最后一个目标值，仅多一个页而已，这点性能忽略不计。
3. 唯一索引在插入和更新时，会需要更多的计算，会扫描看修改或添加内容的索引字段是否唯一，所以增改时唯一索引的性能不如普通索引

聚簇索引 与 非聚簇索引

1. 聚簇索引在性能上优于非聚簇索引， 因为聚簇索引的key和数据都在一起，避免了回表查询。
2. 这样检索数据可以最大程度减少磁盘IO，但如果是经常更新表中数据，则聚簇索引的维护成本就相对较高了，因为数据也在这里，需要更新。

覆盖索引 与 非覆盖索引

1. 覆盖可以直接从索引中获取数据，无需回表查询，所以性能很高。
2. 非覆盖索引就是需要的数据不在索引中，需要带着id去主键索引去捞，这个就叫回表查询，性能慢一些。
   注意：覆盖索引与联合索引没有必然联系，普通索引也可能是覆盖索引

必要情况要开启慢查询对慢sql进行监控告警