mysql（五）索引的三种级别

对于一个查询而言，一个三星索引，可能是其最好的索引。如果查询使用三星索引，一次查询通常只需要进行一次磁盘随机读以及一次窄索引片的扫描，因此其相应时间通常比使用一个普通索引的响应时间少几个数量级。

所谓的"三星索引"，一般是在数据库优化中提及，它是指一个理想的、优秀的索引应该具备以下三个条件：

• 索引的选择性：当一个索引能够将查询的结果集缩小到表中很小的一部分时，我们会给这个索引评定一颗星。也就是说，索引的字段是高度选择性的，同样的值很少，这可以使得数据库通过索引就能过滤掉大量的数据。
• 排序和分组性能：如果索引的列的排序顺序能够匹配查询语句中的 ORDER BY 或者 GROUP BY 的顺序，这样的索引会被评定为二颗星。当索引的排序顺序和查询语句中的排序或分组顺序一致时，查询语句可以直接利用索引完成排序或分组，而不需要再做额外的排序操作。
• 覆盖索引：如果索引包含了查询语句中所有的列，那么这个索引就是一个覆盖索引，我们会给这个索引评定为三颗星。当查询语句能够只通过访问索引就获取所有需要的信息，而无需再访问实际的数据表，那么查询的效率会得到极大的提升。

第一星：索引的选择性

索引的选择性是指索引字段中不重复值的数量与表记录总数的比例。索引的选择性越高，重复的值越少，那么索引的效率就越高，因为数据库管理系统在查找时可以更快地定位到具体的记录。

例如，假设我们有一个用户表，包含了1,000,000条用户记录，表的字段包括 user_id，user_name，gender 等。其中，

user_id 是主键，每一条记录的 user_id 都是独一无二的，因此它的选择性为 1（1,000,000个不同的值/1,000,000条记录）。
gender 字段只有两个可能的值：‘Male’ 或 ‘Female’，因此它的选择性为 0.000002（2个不同的值/1,000,000条记录）。
当执行查询时，如果我们根据 user_id 来查询，如 SELECT * FROM users WHERE user_id = 123456，由于 user_id 的选择性为 1，数据库可以快速定位到具体的一条记录，不需要遍历其他记录。

反之，如果我们根据 gender 来查询，如 SELECT * FROM users WHERE gender = ‘Male’，由于 gender 的选择性非常低，数据库可能需要遍历表中大部分的记录才能找出所有性别为 ‘Male’ 的用户，效率明显下降。

第二星：排序和分组性能

如果查询中的 ORDER BY 或 GROUP BY 子句的列与索引的列顺序相匹配，那么MySQL可以直接利用索引进行排序和分组，而无需进行额外的排序步骤。需要注意的是：想要排序使用索引，必须where查询的时候用到索引，才有可能使用到索引排序，而不是说根据order by、group by的字段选择索引。

不用索引排序，mysql中的排序有单路、双路排序，还有文件排序。

第三星：覆盖索引

覆盖索引是指索引已经包含了查询所需的所有列的数据，无需再回表到原来的数据表中进行查找。这种索引可以大大提高查询效率，特别是在数据表非常大的情况下。覆盖索引是MySQL三星索引的第三星。

如果一个查询可以通过使用覆盖索引而避免访问数据表，我们通常说这个查询是一个"覆盖索引查询"。在执行覆盖索引查询时，MySQL只需要读取索引，而不需要读取实际的表数据。由于索引通常比数据表要小，这种方式可以大大减少磁盘I/O，提高查询效率。

这三颗星哪颗重要？

第三颗星。因为将一个列排除在索引之外可能会导致很多磁盘随机读（回表操作）。第一和第二颗星重要性差不多，可以理解为第三颗星比重是50%，第一颗星为27%，第二颗星为23%，所以在大部分的情况下，会先考虑第一颗星，但会根据业务情况调整这两颗星的优先度