如何高效使用索引——10个索引优化最佳指南

什么是索引？

索引是一种排好序的数据结构，一般存在于磁盘文件中，它记录了原数据的单个列或多个列，通过索引查询，程序不需要查询所有记录，只需要先按照索引查到具体的数据，然后在根据索引记录的指针位置，找到对应的原始数据记录。

举个例子来说，索引就好比是我们书本的目录，我们通过目录能够快速定位到我们想看的指定章节的页数，如果我们不使用索引，最大可能就是从头往后，一页一页的去找。

在关系型数据库中，常见的索引类型包括 B 树索引、哈希索引和全文索引等。B 树索引是最常用的索引类型，它适用于大多数查询场景。哈希索引适用于等值查询，但对于范围查询和排序操作不太有效。全文索引则用于对文本数据进行搜索。

在 MySQL 中有两种数据访问方式：顺序访问和索引访问。

顺序访问

顺序访问又叫全表扫描，也就是你要查数据时，它是从表的第一行一直按照你的条件进行匹配，直到最后一行，如果数据量比较少的情况下，这是没有问题，但是如果数据量很多，这种查询方式就有点够呛了，前端请求一个数据，直接把网站给干崩了。

索引访问

索引访问顾名思义查询数据是在索引数据结构上执行的，索引数据首先是排好序的，其次它没有保存完整的数据列(聚集索引除外，它的完整记录是放在叶子节点中的)。

这种访问方式前提是你已经建好索引，并且你检索的数据列存在索引表中，只有这样你才可以使用索引查询。如果创建了索引之后，当我们对数据进行更新操作时，需要同时更新和维护索引数据。

索引的优点和缺点

优点

• 提高查询性能：索引可以大大减少数据库需要扫描的数据行数，从而提高查询的速度。
• 支持排序和分组操作：索引可以帮助数据库快速对数据进行排序和分组，提高这些操作的性能。
• 强制数据唯一性：可以创建唯一索引来确保数据的唯一性，防止重复数据的插入。

缺点

• 占用存储空间：索引需要占用额外的存储空间来存储索引数据。
• 降低数据插入、更新和删除的性能：当对数据进行插入、更新或删除操作时，数据库需要同时维护索引，这可能会导致这些操作的性能下降。
• 可能导致查询优化器选择错误的执行计划：如果索引使用不当，查询优化器可能会选择错误的执行计划，导致性能下降。

索引失效

索引失效，是指表中有字段创建了索引，但是由于 SQL 语句书写不当导致索引失效的情况。通常有以下几种索引失效的场景：

1. 查询条件包含 or，可能导致索引失效。
2. like 通配符可能导致索引失效。
3. 联合索引，查询时的条件列不是联合索引中的第一个列，索引失效。
4. 在索引列上使用 MySQL 的内置函数，索引失效。
5. 对索引列运算(如,+、-、*、/)，索引失效。
6. mysql 估计使用全表扫描要比使用索引快，则不使用索引。

例如，在 employees 表的 age 列上创建索引：

正确使用 age 列的索引：

age 列索引失效：

很容易看出 where 中的表达式其实等价于 age=28，但是 MySQL 无法自动解析这个表达式，这完全是用户行为。

（我们知道 MySQL 先在索引上按值进行查找，然后返回索引值对应的数据行，一旦对索引列进行运算，则将无法正确的找到对应的数据行，从而改为全表逐行扫描查询对比）

10 个索引优化最佳指南

1.选择合适的索引列

• 选择经常用于查询条件的列作为索引列。例如，如果经常根据用户的姓名进行查询，那么可以在用户表的姓名列上创建索引。

• 将具有高选择性的列作为索引列。 选择性是指，不重复的索引值(也称为基数)和表数据的记录总数T的比值，范围从1/T到1之间。选择性越高，索引的效果越好。例如，如果一个列只有两个可能的值，那么创建索引的效果可能不太好。唯一索引的选择性为1，这是最好的索引选择性，性能也是最好的。

• 避免在频繁更新的列上创建索引。 因为这会降低数据更新的性能。

• 对于 BLOB、TEXT 或很大的 VARCHAR 类型的列，作为查询条件时（原则上是要避免这样的操作，但有时总是情非得已），该列必须使用前缀索引，这样来提高查询性能。因为 MySQL 是不允许索引这些列的完整长度的。

2. 创建复合索引

在 SQL 优化时，有人会采取在多个列上建立独立的单列索引，大部分情况下并不能提高MySQL的查询性能。这是一种错误的做法。MySQL5.0 及之后版本引入了索引合并策略，实际上也是反映了表上索引建的很糟糕，通常有以下几种原因：

1. 当出现对多个索引做相交操作时（通常由多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
2. 当需要对多个索引做联合操作时（通常有多个OR条件），通常需要耗费大量的CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。

所以复合索引作为多个独立单列索引的优化方法出现。复合索引由多个列组成，这些列的组合共同决定了索引的结构和作用范围。使用复合索引有以下几个注意事项：

1. 当多个列经常一起用于查询条件时，可以创建复合索引。复合索引可以提高这些查询的性能。
2. 复合索引的列顺序很重要。应该将最具选择性的列放在最前面，然后依次是其他列。例如，如果经常根据用户的姓名和年龄进行查询，那么可以在用户表的姓名和年龄列上创建复合索引，并且将姓名列放在前面。
3. 如果在执行计划EXPLAIN中看到索引合并，应该仔细检查查询和表的结构，看是不是已经是最优的。也可以通过参数optimizer_switch来关闭索引合并功能，也可以使用IGNORE INDEX提示让优化器忽略掉某些索引。

创建复合索引的示例如下：

在员工表的员工 id 和员工姓名列上创建复合索引 idx\_employee\_id\_name

根据employee\_id进行查询

从执行计划中的type可以看出，索引是有效的。但如果根据employee_name进行查询，则索引将会失效(全表扫描)，如下：

3. 避免过多的索引

过多的索引会占用大量的存储空间，并且会降低数据插入、更新和删除的性能。

只创建必要的索引，根据实际的查询需求进行选择。

4. 选择合适的索引列顺序

正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要（只用于B-Tree索引，哈希或者其他索引存储数据并不是顺序存储）。对于如何选择索引的列顺序有一个经验法则：将选择性最高的索引放在索引的最前列。在某些场景这个经验时非常有用，但是通常不如避免随机IO和排序那么重要，考虑问题需要更全面。另外，具有随机或不可预测值的列不适合作为索引的前列。比如，一个包含随机生成的 UUID 的列，由于其选择性低且难以预测，不适合作为索引的首列。

5. 定期维护索引

随着数据的不断插入、更新和删除，索引可能会变得碎片化，影响查询性能。可以定期使用数据库的索引维护工具（如重建索引、重新组织索引等）来优化索引。

监控索引的使用情况，根据实际的查询需求调整索引。如果某些索引很少被使用，可以考虑删除它们。

6. 使用覆盖索引

覆盖索引能够在不访问实际表数据的情况下，仅通过索引就满足查询的需求。当数据库可以使用覆盖索引来满足查询时，不需要访问表中的数据行，从而提高查询性能。覆盖索引包含了查询所需的所有列的数据。当数据库执行查询时，如果可以使用覆盖索引，那么就不需要从表中读取数据行，从而大大减少了磁盘 I/O 操作，提高查询性能。

例如，有一个员工表包含员工编号、姓名、年龄、部门等列。如果经常执行查询只需要员工编号和姓名列，那么可以在员工编号和姓名列上创建一个覆盖索引。当执行这样的查询时，数据库可以直接从索引中获取所需的数据，而无需访问表中的数据行。

没覆盖索引情况下，“不等于”索引失效

没覆盖索引的情况下，使用“不等于”会导致索引失效。因为如果使用索引，则需要依次遍历非聚簇索引B+树里所有叶节点，时间复杂度O(n)，找到记录后还要回表，加在一起效率不如全表扫描，所以查询优化器就选择全表扫描了。

覆盖索引情况下，“不等于”索引生效

覆盖索引，查的两个字段被联合索引给覆盖了，性能更高。虽然还是需要依次遍历非聚簇索引B+树里所有叶节点，时间复杂度O(n)，但是不需要回表了，整体效率比不用索引更高，查询优化器就又使用索引了。

没覆盖索引的情况下，左模糊查询导致索引失效

覆盖索引情况下，左模糊查询索引生效

主要原因也是因为走非聚簇索引B+树遍历叶节点，不回表，效率会比全表扫描时高，查询优化器选择效率高的方案。

覆盖索引的利弊

好处：

1. 避免回表（Innodb 表进行索引的二次查询）

Innodb是以聚集索引的顺序来存储的，对于 Innodb 来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据，在查找到相应的键值后，还需通过主键进行二次查询才能获取我们真实所需要的数据。在覆盖索引中，二级索引的键值中可以获取所要的数据，避免了对主键的二次查询 ，减少了IO操作，提升了查询效率。

2. 可以把随机 IO 变成顺序 IO 加快查询效率

由于覆盖索引是按键值的顺序存储的，对于 IO 密集型的范围查找来说，对比随机从磁盘读取每一行的数据 I0 要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查找的 顺序 IO。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

弊端:

索引字段的维护总是有代价的。因此，在建立几余索引来支持覆盖索引时就需要权衡考虑了。这是业务DBA，或者称为业务数据架构师的工作。

不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree所以来做覆盖索引，另外不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引。

7. 避免在索引列上进行函数操作

当在索引列上进行函数操作时，数据库无法使用索引来加速查询。例如，如果在用户表的姓名列上创建了索引，并且查询中使用了UPPER(name)函数，那么数据库无法使用索引来加速查询。可以将函数操作移到查询的条件中，而不是在索引列上进行函数操作。例如，可以将查询条件改为name = UPPER('John')，而不是UPPER(name) = 'JOHN'。

8. 聚簇索引

在具有聚簇索引的表中，数据行实际上是按照索引键的顺序存储的。这意味着索引的叶子节点包含了实际的数据行。例如，在一个存储学生信息的表中，如果按照学生的学号建立聚簇索引，那么数据行将按照学号的顺序在磁盘上存储。在 InnoDB 存储引擎中，若表有主键，则主键自动作为聚簇索引；若没有主键，会选择第一个唯一索引作为聚簇索引；若没有唯一索引，会隐式定义一个包含所有列的隐藏列作为聚簇索引。在 InnoDB 存储引擎中，若表有主键，则主键自动作为聚簇索引；若没有主键，会选择第一个唯一索引作为聚簇索引；若没有唯一索引，会隐式定义一个包含所有列的隐藏列作为聚簇索引

优点

1. 快速范围查询：由于数据是按照索引键的顺序存储的，对于范围查询（例如查找学号在一定范围内的学生）非常高效。数据库可以快速定位到范围的起始位置，然后顺序读取数据，而不需要进行大量的随机磁盘访问。
2. 高效的连接操作：如果多个表通过具有聚簇索引的列进行连接操作，数据库可以更有效地合并数据，因为数据已经按照连接列的顺序存储。
3. 减少磁盘 I/O：因为数据的存储与索引的结构紧密结合，对于某些查询，可以减少磁盘 I/O 操作，提高查询性能。

缺点

1. 插入和更新成本高：当插入或更新数据行时，数据库可能需要移动大量的数据以保持数据的有序性。这会导致较高的插入和更新成本，特别是在频繁进行插入和更新操作的情况下。
2. 索引占用空间大：聚簇索引通常需要占用较多的存储空间，因为它包含了实际的数据行。这可能会对存储资源造成压力。
3. 不适合频繁更改索引键值的场景：如果索引键的值经常改变，数据库需要不断地调整数据的存储顺序，这会导致性能下降。

不同的数据库管理系统对于聚簇索引的创建方式略有不同。一般来说，可以在创建表时指定一个列作为聚簇索引键。在选择聚簇索引键时，需要考虑以下因素：

1. 列的唯一性：如果选择的列具有较高的唯一性，那么聚簇索引的效果会更好，因为可以减少数据的重复存储和冲突。

2. 查询模式：根据常见的查询模式选择聚簇索引键。如果经常进行范围查询或连接操作，可以选择与这些查询相关的列作为聚簇索引键。

3. 数据的稳定性：选择一个相对稳定的列作为聚簇索引键，避免频繁更改索引键值。

9. 冗余，重复索引

重复索引，是指在相同列上按照相同的顺序创建的相同类型的索引。应该避免这样的创建重复索引，发现以后也应该立即移除。

上面的例子是创建一个主键，先加上唯一限制(unique(id))，然后再加上索引(index(id))以供查询使用。然而唯一限制和主键限制都是通过索引使用，因此，上面的写法实际上在相同的列上创建了三个重复的索引。通常并没有理由要这样做，除非是在同一列上创建不同类型的索引来满足不同的查询需求。

冗余索引和重复索引有一些不同，比如：如果创建了索引（A,B），再创建（A）那就是冗余索引，因为A就是前一个索引的前缀索引。索引（A,B）完全就可以当做A来使用。但是如果创建了索引B那就不是冗余索引了。因为B不是索引(A,B）的最左前缀索引。另外，其他不同类型的索引，例如哈希，全文索引也不会是B-Tree的冗余索引。

10. 使用索引提示

当查询优化器选择了错误的执行计划时，可以使用索引提示来强制数据库使用特定的索引。另外，索引提示应该谨慎使用，只有在确定查询优化器选择了错误的执行计划时才使用。过多的索引提示可能会导致数据库无法选择最优的执行计划。

先在账户表的姓名列上创建索引

强制数据库使用特定的索引

总结

通过上面的讲解，是为了说明如何高效使用索引并给出了对应的10个索引优化的最佳实践。索引是提高数据库查询性能的重要工具，但如果不正确地使用索引，可能会导致性能下降。遵循上面列出的10个指南，在一定程度上可以高效地使用索引，提高数据库的性能，当然，索引优化还有其他优秀的方案，需要在实际数据库开发过程中不断的去学习和实践，应该根据具体的查询需求和数据特点选择合适的索引优化方法，以达到最佳的性能效果。

Chat2DB 文档：https://docs.chat2db.ai/zh-CN/docs/start-guide/getting-started

Chat2DB 官网：https://chat2db.ai/zh-CN

Chat2DB GitHub：https://github.com/codePhiliaX/Chat2DB