关于使用 Mysql 索引的一些建议-CSDN博客

本文链接：https://blog.csdn.net/qq_66450373/article/details/141563808

最近也是在准备秋招实习，准备面试，刚好复习到 Mysql 索引这一方面的东西，就总结一下我所看到的吧！

（1）适合创建索引的字段

主键字段：主键通常是表中唯一标识记录的字段，PK 就不多说了，必须要有的
频繁用于查询条件的字段：如果一个字段经常出现在 WHERE 子句中作为查询条件，考虑为该字段创建索引。
频繁用于排序的字段：如果一个字段经常出现在 ORDER BY 子句中，考虑为该字段创建索引。
频繁用于分组的字段：如果一个字段经常出现在 GROUP BY 子句中，考虑为该字段创建索引。
外键字段：外键用于维护表之间的参照完整性。如果某个字段作为外键关联到其他表，可以考虑为该字段创建索引。
唯一性要求高的字段：对于那些要求唯一性的字段，如电子邮件地址、社保号码等，可以创建唯一索引以确保数据唯一性，同时提升查询速度。
组合索引：当多个字段组合在一起用于查询时，可以考虑创建组合索引。组合索引是对多个字段的联合索引，能够有效提升复杂查询的性能。
覆盖索引：覆盖索引是指查询所需的所有字段都在索引中，这样 MySQL 在查询时可以直接从索引中获取数据，而不需要访问数据表。覆盖索引通常包含多个字段。
适合全文索引的字段：对于需要进行全文检索的字段，如文章内容、产品描述等，可以使用 MySQL 的全文索引功能。（但一般用 es）

（2）不适合创建索引的字段

大字段通常不适合创建索引：很大或很长的字段（如大文本、Blob 字段）通常不适合作为索引，因为会占用大量的存储空间，并且查询性能提升不明显。
被频繁更新的字段：虽然索引能带来查询上的效率，但是维护索引的成本也是不小的。如果一个字段不被经常查询，反而被经常修改，那么就更不应该在这种字段上建立索引了。
高度重复的字段：当字段的值高度重复时，索引的选择性很低，索引效率较差，无法显著提高查询速度。
仅用于记录或审计目的的字段：就是传说中的三件套（创建时间，更新时间，逻辑删除字段）
很少被使用的字段：如果一个字段很少出现在查询条件中，创建索引的价值较低，可能会导致不必要的存储和维护开销。
极端稀疏或含有大量空值的字段：当一个字段的非空值非常少，大多数记录为空值，索引的选择性非常低，查询性能提升不明显。
计算字段或函数结果：如果查询中经常使用某个字段的计算值或函数结果，而不是字段的原始值，索引的作用会非常有限，因为 MySQL 需要对每个记录计算结果，再决定是否符合条件。
非确定性或随机值字段：一些字段的值是非确定性的或者具有随机性，这类字段的索引不仅没有明显的查询加速作用，还会导致索引的维护成本上升。

（3）索引并不是越多越好

建议单张表索引不超过 5 个！索引可以提高效率同样可以降低效率。
索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况会降低查询效率。因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能。

（4）尽可能考虑建立联合索引而不是单列索引

提升查询性能：在多条件查询中，联合索引可以同时涵盖多个字段，MySQL可以通过一次索引查找获取结果，而不需要对多个单列索引进行组合，减少查询时间。
节省存储空间：联合索引可以代替多个单列索引，减少了索引数量，从而节省存储空间和索引维护的开销。如果查询所需的字段都在联合索引中，MySQL可以直接通过索引返回查询结果，而不需要访问数据表，这样可以减少 I/O 操作。
更好的选择性：单列索引在某些情况下选择性较差（如性别字段），但是将其与其他选择性更高的字段组合成联合索引，可以大幅提升索引的整体选择性，从而提高查询效率。

（5）注意避免冗余索引
冗余索引是指多个索引之间存在功能重叠的现象，也就是说，一个索引能够满足的查询，另一个索引也可以满足。在MySQL中，冗余索引会带来额外的存储成本和维护开销，但却不会带来额外的性能提升。因此，避免冗余索引是数据库优化中的一个重要原则。

比如：能够命中索引 (a, b) 的查询通常也能命中索引 (a)，这意味着如果已经存在索引 (a, b)，通常就没有必要再单独创建索引 (a)。相反，如果发现有需要包含多个字段的查询，可以考虑扩展已有的索引，而不是创建新的单列索引。

（6）前缀代替普通
前缀索引仅限于字符串类型，较普通索引会占用更小的空间，所以可以考虑使用前缀索引带替普通索引。

（7）删除那些长期不使用的索引
删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗。
MySQL 5.7 可以通过查询 sys 库的 schema_unused_indexes 视图来查询哪些索引从未被使用。

（8）最后也是最重要的，知道如何去分析一个语句是否走索引

我们可以使用 EXPLAIN 命令来分析 SQL 的 执行计划 ，这样就知道语句是否命中索引了。执行计划是指一条 SQL 语句在经过 MySQL 查询优化器的优化会后，具体的执行方式。

EXPLAIN 并不会真的去执行相关的语句，而是通过 查询优化器 对语句进行分析，找出最优的查询方案，并显示对应的信息。

mysql> EXPLAIN SELECT `score`,`name` FROM `t_student` ORDER BY `score` DESC;
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra          |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
|  1 | SIMPLE      | t_student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 997572 |   100.00 | Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+--------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

每个字段含义：图源