MySQL数据库 InnoDB引擎索引原理与设计索引调优简述

MySQL的InnoDB引擎比较常用，了解它的索引原理，才能在设计索引的时候得心应手，轻松应对数据库表的优化。

1、聚集索引

也叫聚簇索引。

1.1、必然会有聚集索引

聚集索引 != 主键索引；

任何表都必然会有聚集索引，而主键索引并非必然存在。

1.2、聚集索引创建方式

实践中，通常都会指定主键，所以它们通常是同一回事。

1.3、数据结构

所有索引都是 B+Tree 方式存储，高度维持在3~4层， 只有叶子节点存储数据（这里已然是存了每行的所有列的数据）；

聚集索引的叶子节点数据， 逻辑上按照索引列正序排序，物理上不连续 ；

每个节点的大小等于内存中的一页的大小（页是计算机管理存储器的逻辑块，许多OS中，页的大小通常为4K），使得每次获取一个节点时只需要一次IO；

一个节点内的数据不一定填满整个节点，如果需要插入数据时，可快速插入而不必分拆节点。

叶子节点的数据即是表数据的实际存储位置 ，当不使用任何索引查询时，直接按原正序遍历数据。

2、辅助索引

也叫二级索引。

2.1、数据结构

所有索引都是 B+Tree 方式存储，高度维持在3~4层，只有叶子节点存储数据，但 叶子节点只存了聚集索引的列（如果聚集索引是主键，那么就是存了主键id的值，否则就是隐含列row_id；后面为省事直接叫id，实际意义以此描述为准）；

可以将辅助索引理解为一个小表，数据列含有索引列及id。

3、设计索引3.1、索引覆盖与回表

索引覆盖：

查询的 列范围是本次查询使用的索引包含的列及id 时，即为索引覆盖，表示本次查询的列的数据可以直接从索引中获取，不需要回表查询；

查询语句前加上 explain 分析时，extra 列的值是 using index 时就表示本次查询属于索引覆盖。

回表：

相反，查询的列范围超出本次查询使用的索引包含的列及id时， 其他列的数据无法在索引中确定，必须要回表获取 ——这种行为就是回表；

查询语句前加上 explain 分析时，extra 列的值是 using index condition，表示本次查询使用了索引的结果作为条件再回表获取数据。

回表时， 通过辅助索引存储的id，去聚集索引直接定位获取对应的数据 ，再取出对应的列。

小结：

如果能够 避免回表，有助于提升查询的速度 。

3.2、前缀索引

对于使用 Redundant 或者 Compact 格式的 InnoDB 表，索引键前缀长度限制为767字节。如果 TEXT 或 VARCHAR 列的前缀索引超过191个字符，则可能会达到此限制（假定为 utf8mb4 字符集，每个字符最多4个字节）。

设计表时， 尽量给出合理的长度 ，避免字段太长导致索引长度过长，进而影响索引的性能。

因为索引虽然加快查询速度，但是过多的索引也会成为数据库的负担，毕竟索引也需要磁盘存储起来的。

3.3、复合索引

与之相对的是单列索引，即是只在一个列上加索引的情况；

单列索引无法满足需求时，可通过多个字段创建复合索引，也叫组合索引，也有些人习惯性的叫联合索引等，叫法不重要，反正就那意思。

复合索引遵循 最左匹配原则mssql数据库，就是说查询时，右边或中间的字段可以少，左边的不可以少，否则索引失效。

设计复合索引时需要注意：

3.4、跳跃索引

因为复合索引遵循最左匹配原则，否则索引失效，但在 MySQL8.0 之后对某些特殊情况做了优化，即：如果左边的列缺失，但是 左边列的唯一值少而右面列的唯一值多 时，数据库直接跳过左边的列进行索引扫描，而不是直接遍历表。

跳跃索引是优化的结果，并不是必然存在（不符合条件就不存在），也不是存在过以后就一直存在（刚开始符合条件但后面数据的变化导致不符合条件，以后也不会存在），所以最好还是不要依赖数据库的优化，同时要认清MySQL数据库的版本。

4、总结

本文讲述了聚集索引与辅助索引的概念及其数据结构，基于 B+Tree 数据结构进一步说明了索引覆盖与回表是如何发生的，以及各种索引设计时需要注意的地方。

（编辑：拼字网 - 核心网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!