【MySQL索引与优化篇】索引的分类与设计原则

索引的分类与设计原则

1. 索引的分类

• 从 功能逻辑 上说，索引主要有 4 种，分别是普通索引、唯一索引、主键索引、全文索引
• 按照 物理实现方式，索引可以分为 2 种，分别是聚簇索引和非聚族索引
• 按照 作用字段个数 进行划分，分成单列索引和联合索引

2. MySQL8.0索引新特性

2.1 支持降序索引

MySQL在8.0版本之前创建的仍然是升序索引，使用时进行反向扫描，这大大降低了数据库的效率。在某些场景下，降序索引意义重大。例如，如果一个查询，需要对多个列进行排序，且顺序要求不一致，那么使用降序索引将会避免数据库使用额外的文件排序操作，从而提高性能

2.2 隐藏索引

从MySQL 8x开始支持 隐藏索引 (invisible indexes)，只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引(即使使用force index (强制使用索引)，优化器也不会使用该索引)，确认将索引设置为隐藏索引后系统不受任何响应，就可以彻底删除索引。这种通过先将索引设置为隐藏索引，再删除索引的方式就是软删除；通过这种方式可以验证删除索引后的性能，确认删除后无影响再考虑实际删除该索引。

注意：当索引被隐藏时，它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏，那么可以将其删除，因为索引的存在会影响插入、更新和删除的性能。

3. 索引的设计原则

3.1 适合索引的9个设计原则

1. 频繁作为 WHERE 查询条件的字段

2. 经常GROUP BY 和 ORDER BY的列

3. DISTINCT 字段需要创建索引

4. 多表JOIN 连接操作时，创建索引注意事项

   • 连接表的数量尽量不要超过 3 张，因为每增加一张表就相当于增加了一次嵌套的循环，数量级增长会非常快，严重影响查询的效率
   • 对 WHERE 条件创建索引，因为 WHERE才是对数据条件的过滤
   • 对用于连接的字段创建索引

5. 使用列的类型小的创建索引

6. 对于长字符串，使用字符串前缀创建索引

   • 使用索引列前缀的方式 无法支持使用索引排序，只能使用文件排序
   • 【强制】在varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度

   说明: 索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会 高达 99%以上，可以使用 count(distinct left(列名,索引长度))/count()的区分度来确定

7. 区分度高(散列性高)的列适合作为索引

   • 可以使用公式 select count(distinct a)/count(*) from t1 计算区分度，越接近1越好，一般超过33%就算是比较高效的索引了

8. 使用最频繁的列放到联合索引的左侧

9. 在多个字段都要创建索引的情况下，联合索引优于单值索引

3.2 限制索引的数目

在实际工作中，我们也需要注意平衡，索引的数目不是越多越好。我们需要限制每张表上的索引数量，建议单张表索引数量 不超过6个 。原因：

• 每个索引都需要占用 磁盘空间，索引越多，需要的磁盘空间就越大
• 索引会影响INSERT、DELETE、UPDATE等语句的性能，因为表中的数据更改的同时，索引也会进行调整和更新，会造成负担
• 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的 索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，会增加MySQL优化器生成执行计划时间，降低查询性能

3.3 不适合使用索引的情况

1. 在where中使用不到的字段，不要设置索引
2. 数据量小的表最好不要使用索引
3. 有大量重复数据的列上不要建立索引，即尽可能在区分度高的列上建索引
4. 避免对经常更新的表创建过多的索引
5. 不建议用无序的值作为索引
6. 删除不再使用或者很少使用的索引
7. 不要定义冗余或重复的索引