SpiritTree2.png

前言:在 MySQL 学习总结(一)文中末尾提到了有关 SQL 优化的一些认识,可以看到 SQL 优化与索引的使用密切相关。所以为了提升 SQL 的性能,了解索引以及如何正确地使用索引也就成了我们的必修课。

文中所提到的内容均为本人学习过程中自己的理解,如有不当之处欢迎指出和补充🙏。

1. 什么是索引?

🌰 举个栗子:我们有一本书,就拿《高性能 MySQL》为例。我们要查看这本书中与 “索引” 相关的内容,通常情况下有两种方式选择:1. 从第一页开始往后翻,直到翻到 “索引” 相关内容处开始阅读;2. 查看书中的目录,找到 “创建高性能索引” 目录,该目录指向的页码数为 141, 直接将书翻至 141 页开始阅读。

我们都知道,第一种方式的看书效率非常低。而第二中方式就能很快地找到对应的章节进行阅读。

索引 的作用就如同 目录 的功能一样,能够快速地从大量的数据中找出目标内容。

2. MySQL 中的索引原理

MySQL 支持很多类型的索引,其中 B+Tree 是谈得最多也是用得最多的索引类型。这里先推荐一篇文章:MySQL InnoDB 索引原理 ,文中对 InnoDB 存储引擎做了个较为全面的分析,能够帮助理解 InnoDB 中索引的工作原理。

文中有提到一个很重要的概念 —— 页(Page)。作为 MySQL 的基本存储结构,我们先要对它有个基本的认识。

InnoDB数据页结构.jpg

  • 生成的数据页可以组成一个双向链表,图中标示为 File HeaderFile Tailer 分别指向前一个数据页和后一个数据页。
  • 每个数据页中的存放的数据可以组成一个单项链表:InnoDB数据页.png

执行查询 SQL 时 MySQL中的操作就如上面栗子中的读书是一样的:

  • 每个数据页都会为存储在里面的数据生成一个目录,而在通过主键查找某条记录时,就可以先从目录中快速找到对应的 ,然后再从槽中找到指定的记录。
  • 在以不是索引的列上进行查询时,由于没有目录,所以就只能从最开始的记录依次往后查找,直到找到指定的记录。

当然除了 B+Tree 类型的索引,MySQL 还支持 哈希索引(Memory 存储引擎的默认索引类型)、全文索引(MyISAM 存储引擎支持)、空间数据索引(也称为 R-Ttree,用于存储地理数据,MyISAM 支持)以及 第三方存储引擎使用的索引类型。

3. 高效地使用索引

使用索引有如下优点:

  1. 大大降低了服务器需要扫描的数据量。
  2. 将随机 I/O 变为了顺序 I/O。
  3. 基于第二条,帮助服务器避免了在排序上的性能消耗。

当然,也不是在任何情况下使用索引都是好的:

  1. 由于使用索引后,插入、更新以及删除时都会有一定的性能消耗,所以对于小表以及特大型表,使用索引均不是最佳的解决方案。
  2. 随着表中索引的增多,维护索引所带来的的开销也会变大,所以过分的建立索引不是最佳的解决方案。这一点中在 《阿里巴巴 Java 开发手册 —— 泰山版》中也有指出:image.png

由此可见,正确地创建和使用索引是实现高性能查询的基础。接下来一起学习如何高效地使用索引。

3.1 单列索引

不是说在列上创建了索引,在执行查询 SQL 的时候就可以高枕无忧了。在使用不当的情况下,MySQL 无法使用已有的索引,而直接走全表扫描。如以下情形:

  • 在索引列上执行运算操作。
  • 在索引列上使用函数。
  • 在索引列上使用 != 、<> 同样也会导致 MySQL 无法使用索引。
  • 再重复值较多的列上创建索引,可能会导致索引失效。
  • 在索引列上使用 like 时,也会导致索引失效。

对照以上情形,养成良好的 SQL 编写习惯也是至关重要的。

3.2 多列索引

多列索引也叫联合索引,在使用联合索引时一定要着重注意索引中列的最左匹配原则,MySQL 在默写情况下可能会对 SQL 中索引列的顺序进行优化,但大多数情况下不会,所以要注意了。

🌰 举个栗子:

create table t (
   id int,
   c1 int,
   c2 int,
   c3 int,
   primary key (id),
   index `c1_c2_c3` (c1, c2, c3)
);

我们创建了一个名为 (c1_c2_c3) 的联合索引,现在执行如下语句:

select c1, c2, c3 from t where c1 = '1' and c3 = '3'; // 可以使用索引

select c1, c2, c3 from t where c3 = '3' and c1 = '1'; // 通过 MySQL 自身的优化,也可以使用索引

select c1, c2, c3 from t where c2 = '2';              // 不满足最左匹配原则,不使用索引

select c1, c2, c3 from t where c1 = '1';	      // 满足最左匹配原则,使用索引

在创建 (c1_c2_c3) 索引的同时,还创建了 (c1)(c1_c2) 这两个索引,所以在使用过程中注意删除冗余索引,避免带来多余的性能消耗。

3.3 前缀索引

有时候需要在内容很长的列上创建索引,一般的创建方式将会导致索引变大且非常慢。这时候就需要用到前缀索引了。

在创建前缀索引前,需要先了解一个 索引选择性 的概念。借用 《高性能 MySQL》 中的一段话:

索引性能是指,不重复的索引值(也称为基数,cardinality)和数据表的记录总数(#T)的比值,范围从 1/#T 到 1 之间。索引的选择性越高查询效率越高,因为选择性高的索引可以让 MySQL 在查找时过滤掉更多的行。唯一索引的选择性是 1,这是最好的索引选择性,性能是最好的。

所以在创建前缀索引时,要选择足够长的前缀以保证较高的 索引选择性,但是又不能太长以免带来额外的开销。通常情况下,前缀索引的长度计算方式如下:

// 获取索引选择性(cardinality)的值
select count(distinct column)/count(*) as cardinality from table;

// 改变 length 的长度,用查询出的 sel 与 cardinality 值进行比较
select count(distinct left(column, length))/count(*) as sel from table;

如果 sel 的值随着 length 的增加变化甚微时,就可以确定前缀索引的长度了。但如果此时 sel 的值与 cardinality 的值相差较大时,就要考虑是否应该在该列上创建索引了。

除了前缀索引外,有时候 后缀索引 也是不错的选择。

3.4 聚簇索引

聚簇索引并不是一种单独的索引类型,而是一种数据存储的方式。

在《高性能 MySQL》中对聚簇索引做了如下说明:聚簇索引的数据存放在索引的叶子页中,由于无法同时把数据行存放在两个不同的地方,所以一个表中只能有一个聚簇索引。

聚簇索引非聚簇索引以及唯一索引之间的区别:

  • 聚簇索引的叶子节点就是数据节点,而非聚簇索引的叶子节点仍然是索引节点,只不过有指向对应数据块的指针。
  • 聚簇索引默认是主键,如果表中没有主键,InnoDB 会选择一个唯一的非空索引代替,如果没有这样的索引, InnoDB 会隐式定义一个主键来作为聚簇索引。
  • 聚簇索引不一定是唯一索引,聚簇索引的索引值不要求是唯一的,唯一聚簇索引才有这样的要求。在一个聚簇索引的列上可以插入多个相同的值,只是这些相同的值在硬盘中的顺序与聚簇索引的顺序一致。

聚簇索引的优点:

  • 可以把相关数据储存在一起,这样只需要读取少量的数据页就能获取所有相关的数据。
  • 聚簇索引将索引和数据保存在同一个 BTree 中,所以从聚簇索引中获取数据通常效率更高。
  • 使用覆盖索引扫描的查询可以直接使用页节点中的索引值。

当然聚簇索引也有缺点:

  • 插入数据的速度严重依赖插入顺序。
  • 更新聚簇索引列的代价很高。
  • 在数据储存不连续的情况下,聚簇索引可能会导致全表扫描的速度变慢。

3.5 覆盖索引

如果一个索引中包含(或者说覆盖)所有需要查询的字段的值,我们就成之为 “覆盖索引”。

由于在 InnoDB 中索引中存储的并不是数据行,而是存储的主键信息。所以在一般情况下我们查询表中的数据时,会先通过查询索引获取主键的值,再通过获取到的主键值进行回表操作。也就是说查询数据的时候执行了两次索引查找,而不是一次。而覆盖索引的作用就是避免进行回表操作。

🌰 举个栗子:

订单表 order 中有一个多列索引 (items_id, user_id),如果此时我们只需要查询 items_id 和 user_id 这两列的数据,就可以使用该索引作为覆盖索引。如:

 select items_id, user_id from table;

使用 explain 查看该语句的执行计划会发现如下信息:

......
type: index
......
Extra: Using index

其中 type 的值为 index 表示使用了索引,而 Extra 的值为 Using index 即表示使用了 覆盖索引,避免了使用索引后所进行的回表操作。

3.6 其他索引优化

除了上面提到的 冗余索引 外,可能在我们的数据表中遗留下一些永远也用不到或者使用得非常少的索引,像这样的索引完全就是个累赘,建议删除。

参考

MySQL InnoDB 索引原理

通俗易懂 索引、单列索引、复合索引、主键、唯一索引、聚簇索引、非聚簇索引、唯一聚簇索引 的区别与联系

书籍 《高性能 MySQL》


关于作者:NekoChips
本文地址:https://chenyangjie.com.cn/articles/2020/05/07/1588857758825.html
版权声明:本篇所有文章仅用于学习和技术交流,本作品采用 BY-NC-SA 4.0 许可协议,如需转载请注明出处!
许可协议:知识共享许可协议