如何高效使用索引——10 个索引优化最佳指南
什么是索引?
索引是一种排好序的数据结构,一般存在于磁盘文件中,它记录了原数据的单个列或多个列,通过索引查询,程序不需要查询所有记录,只需要先按照索引查到具体的数据,然后在根据索引记录的指针位置,找到对应的原始数据记录。
举个例子来说,索引就好比是我们书本的目录,我们通过目录能够快速定位到我们想看的指定章节的页数,如果我们不使用索引,最大可能就是从头往后,一页一页的去找。
在关系型数据库中,常见的索引类型包括 B 树索引、哈希索引和全文索引等。B 树索引是最常用的索引类型,它适用于大多数查询场景。哈希索引适用于等值查询,但对于范围查询和排序操作不太有效。全文索引则用于对文本数据进行搜索。
在 MySQL 中有两种数据访问方式:顺序访问和索引访问。
顺序访问
顺序访问又叫全表扫描,也就是你要查数据时,它是从表的第一行一直按照你的条件进行匹配,直到最后一行,如果数据量比较少的情况下,这是没有问题,但是如果数据量很多,这种查询方式就有点够呛了,前端请求一个数据,直接把网站给干崩了。
索引访问
索引访问顾名思义查询数据是在索引数据结构上执行的,索引数据首先是排好序的,其次它没有保存完整的数据列(聚集索引除外,它的完整记录是放在叶子节点中的)。
这种访问方式前提是你已经建好索引,并且你检索的数据列存在索引表中,只有这样你才可以使用索引查询。如果创建了索引之后,当我们对数据进行更新操作时,需要同时更新和维护索引数据。
索引的优点和缺点
优点
提高查询性能:索引可以大大减少数据库需要扫描的数据行数,从而提高查询的速度。
支持排序和分组操作:索引可以帮助数据库快速对数据进行排序和分组,提高这些操作的性能。
强制数据唯一性:可以创建唯一索引来确保数据的唯一性,防止重复数据的插入。
缺点
占用存储空间:索引需要占用额外的存储空间来存储索引数据。
降低数据插入、更新和删除的性能:当对数据进行插入、更新或删除操作时,数据库需要同时维护索引,这可能会导致这些操作的性能下降。
可能导致查询优化器选择错误的执行计划:如果索引使用不当,查询优化器可能会选择错误的执行计划,导致性能下降。
索引失效
索引失效,是指表中有字段创建了索引,但是由于 SQL 语句书写不当导致索引失效的情况。通常有以下几种索引失效的场景:
查询条件包含 or,可能导致索引失效。
like 通配符可能导致索引失效。
联合索引,查询时的条件列不是联合索引中的第一个列,索引失效。
在索引列上使用 MySQL 的内置函数,索引失效。
对索引列运算(如,+、-、*、/),索引失效。
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 及之后版本引入了索引合并策略,实际上也是反映了表上索引建的很糟糕,通常有以下几种原因:
当出现对多个索引做相交操作时(通常由多个 AND 条件),通常意味着需要一个包含所有相关列的多列索引,而不是多个独立的单列索引。
当需要对多个索引做联合操作时(通常有多个 OR 条件),通常需要耗费大量的 CPU 和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高,需要合并扫描返回的大量数据的时候。
所以复合索引作为多个独立单列索引的优化方法出现。复合索引由多个列组成,这些列的组合共同决定了索引的结构和作用范围。使用复合索引有以下几个注意事项:
当多个列经常一起用于查询条件时,可以创建复合索引。复合索引可以提高这些查询的性能。
复合索引的列顺序很重要。应该将最具选择性的列放在最前面,然后依次是其他列。例如,如果经常根据用户的姓名和年龄进行查询,那么可以在用户表的姓名和年龄列上创建复合索引,并且将姓名列放在前面。
如果在执行计划 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+树遍历叶节点,不回表,效率会比全表扫描时高,查询优化器选择效率高的方案。
覆盖索引的利弊
好处:
避免回表(Innodb 表进行索引的二次查询)
Innodb 是以聚集索引的顺序来存储的,对于 Innodb 来说,二级索引在叶子节点中所保存的是行的主键信息,如果是用二级索引查询数据,在查找到相应的键值后,还需通过主键进行二次查询才能获取我们真实所需要的数据。在覆盖索引中,二级索引的键值中可以获取所要的数据,避免了对主键的二次查询 ,减少了 IO 操作,提升了查询效率。
可以把随机 IO 变成顺序 IO 加快查询效率
由于覆盖索引是按键值的顺序存储的,对于 IO 密集型的范围查找来说,对比随机从磁盘读取每一行的数据 I0 要少的多,因此利用覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查找的 顺序 IO。
由于覆盖索引可以减少树的搜索次数,显著提升查询性能,所以使用覆盖索引是一个常用的性能优化手段。
弊端:
索引字段的维护总是有代价的。因此,在建立几余索引来支持覆盖索引时就需要权衡考虑了。这是业务 DBA,或者称为业务数据架构师的工作。
不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列,而哈希索引、空间索引和全文索引等都不存储索引列的值,所以 MySQL 只能使用 B-Tree 所以来做覆盖索引,另外不同的存储引擎实现覆盖索引的方式也不同,而且不是所有的引擎都支持覆盖索引。
7. 避免在索引列上进行函数操作
当在索引列上进行函数操作时,数据库无法使用索引来加速查询。例如,如果在用户表的姓名列上创建了索引,并且查询中使用了UPPER(name)
函数,那么数据库无法使用索引来加速查询。可以将函数操作移到查询的条件中,而不是在索引列上进行函数操作。例如,可以将查询条件改为name = UPPER('John')
,而不是UPPER(name) = 'JOHN'
。
8. 聚簇索引
在具有聚簇索引的表中,数据行实际上是按照索引键的顺序存储的。这意味着索引的叶子节点包含了实际的数据行。例如,在一个存储学生信息的表中,如果按照学生的学号建立聚簇索引,那么数据行将按照学号的顺序在磁盘上存储。在 InnoDB 存储引擎中,若表有主键,则主键自动作为聚簇索引;若没有主键,会选择第一个唯一索引作为聚簇索引;若没有唯一索引,会隐式定义一个包含所有列的隐藏列作为聚簇索引。在 InnoDB 存储引擎中,若表有主键,则主键自动作为聚簇索引;若没有主键,会选择第一个唯一索引作为聚簇索引;若没有唯一索引,会隐式定义一个包含所有列的隐藏列作为聚簇索引
优点
快速范围查询:由于数据是按照索引键的顺序存储的,对于范围查询(例如查找学号在一定范围内的学生)非常高效。数据库可以快速定位到范围的起始位置,然后顺序读取数据,而不需要进行大量的随机磁盘访问。
高效的连接操作:如果多个表通过具有聚簇索引的列进行连接操作,数据库可以更有效地合并数据,因为数据已经按照连接列的顺序存储。
减少磁盘 I/O:因为数据的存储与索引的结构紧密结合,对于某些查询,可以减少磁盘 I/O 操作,提高查询性能。
缺点
插入和更新成本高:当插入或更新数据行时,数据库可能需要移动大量的数据以保持数据的有序性。这会导致较高的插入和更新成本,特别是在频繁进行插入和更新操作的情况下。
索引占用空间大:聚簇索引通常需要占用较多的存储空间,因为它包含了实际的数据行。这可能会对存储资源造成压力。
不适合频繁更改索引键值的场景:如果索引键的值经常改变,数据库需要不断地调整数据的存储顺序,这会导致性能下降。
不同的数据库管理系统对于聚簇索引的创建方式略有不同。一般来说,可以在创建表时指定一个列作为聚簇索引键。在选择聚簇索引键时,需要考虑以下因素:
列的唯一性:如果选择的列具有较高的唯一性,那么聚簇索引的效果会更好,因为可以减少数据的重复存储和冲突。
查询模式:根据常见的查询模式选择聚簇索引键。如果经常进行范围查询或连接操作,可以选择与这些查询相关的列作为聚簇索引键。
数据的稳定性:选择一个相对稳定的列作为聚簇索引键,避免频繁更改索引键值。
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
评论