解释mysql中什么是索引?它的作用是什么?

发布网友 发布时间：2022-04-24 06:18

我来回答

共4个回答

懂视网 时间：2022-04-29 20:25

从上面这张示例图也可以看到，这棵B+树最下面的叶子节点存储了所有的元素，并且是按顺序存储的，而非叶子节点仅存储索引列的值。

3.2 图解 BTree 索引

在 InnoDB 中，基于 BTree 的索引模型的最为常用的，下面以一个实际的例子来图解 InnoDB 中 BTree 索引的结构。

CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(36) DEFAULT NULL, `age` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`) USING BTREE, INDEX `nameIndex`(`name`) USING BTREE
) ENGINE = InnoDB;-- 插入数据insert into user1(id,name,age) values (1,'one',21),(2,'two',22),(3,'three',23),(4,'four',24),(5,'five',25);复制代码

在这张表中只有两个字段：主键 id 和 name 字段，同时建立了一个以 name 字段为索引列的 BTree 索引。

以主键 id 字段的为索引的索引，又叫主键索引，它的索引树结构是：索引树的非叶子阶段存放的都是主键 id 的值，叶子节点存放的值是该主键 id 对应的整个数据行，如下图所示：

也正因为主键索引的叶子节点存储的是该主键 id 对应的整个数据行，主键索引又被称为聚簇索引。

而以 name 字段为列的索引树，非叶子节点存放的同样是索引列的值，而其叶子阶段存放的值是主键 id 的值，如下图所示。

3.3 索引的执行流程

首先来看下面这句 SQL，查询 user 表中 id=1 的数据行。

select * from user where id=1;复制代码

这句 SQL 的执行流程很简单，存储引擎会走主键 id 的索引树，当找到 id=1 时，就会把索引树上 id=1 的数据行返回（由于主键值是唯一的，所以找到命中目标就会停止搜索，直接返回结果集）。

3.3.1 回表

接下来再看使用普通索引进行查询的情况，它的情况与主键索引略有不同。

select * from user where name='one';复制代码

上面这句 SQL 查询语句的流程是这样的：首先存储引擎会搜索普通索引 name 列的索引树，当命中 name 等于 one 的记录后，存储引擎需要经过一个非常重要的步骤：回表。

由于普通索引的索引树子节点存放的是主键值，当查询语句需要查询除主键 id 及索引列之外的其他字段时，需要根据主键 id 的值再回到主键索引树中进行查询，得到主键 id 对应的整个数据行，然后从中获取客户端需要的字段后，才将这一行加入结果集。

随后存储引擎会继续搜索索引树，直到遇到第一个不满足 name='one' 的记录才会停止搜索，最后将所有命中的记录返回客户端。

我们把根据从普通索引查询到的主键 id 值，再在主键索引中查询整个数据行的过程称之为回表。

当数据量十分庞大时，回表是一个十分耗时的过程，所以我们应该尽量避免回表发生，这就引出了下一个问题：使用覆盖索引避免回表。

3.3.2 覆盖索引

不知道你有没有注意到，在上一个回表的问题中有这样一句描述：“当查询语句需要查询除主键 id 及索引列之外的其他字段时...”，在这种场景下需要通过回表来获取其他的查询字段。也就是说，如果查询语句需要查询的字段仅有主键 id 和索引列的字段时，是不是就不需要回表了？

下面来分析一波这个过程，首先建立一个联合索引。

alter table user add index name_age ('name','age');复制代码

那么这棵索引树的结构图应该是下面这样：

联合索引索引树的子节点顺序是按照声明索引时的字段来排序的，类似于 order by name, age ，而它索引对应的值与普通索引一样是主键值。

select name,age from user where name='one';复制代码

上面这条 SQL 是查询所有 name='one' 记录的 name 和 age 字段，理想的执行计划应该是搜索刚刚建立的联合索引。

与普通索引一样，存储引擎会搜索联合索引，由于联合索引的顺序是先按照 name 再按照 age 进行排序的，所以当找到第一个 name 不是 one 的索引时，才会停止搜索。

而由于 SQL 语句查询的只是 name 和 age 字段，恰好存储引擎命中查询条件时得到的数据正是 name, age 和 id 字段，已经包含了客户端需要的字段了，所以就不需要再回表了。

我们把只需要在一棵索引树上就可以得到查询语句所需要的所有字段的索引成为覆盖索引，覆盖索引无须进行回表操作，速度会更快一些，所以我们在进行 SQL 优化时可以考虑使用覆盖索引来优化。

4. 最左前缀原则

上面所举的例子都是使用索引的情况，事实上在项目中复杂的查询语句中，也可能存在不使用索引的情况。首先我们要知道，MySQL 在执行 SQL 语句的时候一张表只会选择一棵索引树进行搜索，所以一般在建立索引时需要尽可能覆盖所有的查询条件，建立联合索引。

而对于联合索引，MySQL 会遵循最左前缀原则：查询条件与联合索引的最左列或最左连续多列一致，那么就可以使用该索引。

为了详细说明最左前缀原则，同时说明最左前缀原则的一些特殊情况。

5. 索引失效场景

即便我们根据最左前缀的原则创建了联合索引，还是会有一些特殊的场景会导致索引失效，下面举例说明。

假设有一张 table 表，它有一个联合索引，索引列为 a,b,c 这三个字段，这三个字段的长度均为10。

CREATE TABLE `demo` ( `a` varchar(1) DEFAULT NULL, `b` varchar(1) DEFAULT NULL, `c` varchar(1) DEFAULT NULL, INDEX `abc_index`(`a`, `b`, `c`) USING BTREE
) ENGINE = InnoDB;复制代码

5.1 全字段匹配

第一种情况是查询条件与索引字段全部一致，并且用的是等值查询，如：

select * from demo where a='1' and b='1' and c='1';select * from demo where c='1' and a='1' and b='1';复制代码

输出上述两条 SQL 的执行计划来看它们使用索引的情况。

mysql> explain select * from demo where a='1' and b='1' and c='1';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+| 1 | SIMPLE | demo | NULL | ref | abc_index | abc_index | 18 | const,const,const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

mysql> explain select * from demo where c='1' and a='1' and b='1';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+| 1 | SIMPLE | demo | NULL | ref | abc_index | abc_index | 18 | const,const,const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)复制代码

第一条 SQL 很显然能够用到联合索引。

从执行计划中可以看到，第二条 SQL 与第一条 SQL 使用的索引以及索引长度是一致的，都是使用 abc_index 索引，索引长度为 18 个字节。

按理说查询条件与索引的顺序不一致，应该不会用到索引，但是由于 MySQL 有优化器存在，它会把第二条 SQL 优化成第一条 SQL 的样子，所以第二条 SQL 也使用到了联合索引 abc_index。

综上所述，全字段匹配且为等值查询的情况下，查询条件的顺序不一致也能使用到联合索引。

5.2 部分字段匹配

第二种情况是查询条件与索引字段部分保持一致，这里就需要遵循最左前缀的原则，如：

select * from demo where a='1' and b='1';select * from demo where a='1' and c='1';复制代码

上述的两条查询语句分别对应三个索引字段只用到两个字段的情况，它们的执行计划是：

mysql> explain select * from demo where a='1' and b='1';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+| 1 | SIMPLE | demo | NULL | ref | abc_index | abc_index | 12 | const,const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------------+------+----------+-------------+1 row in set, 1 warning (0.00 sec)

mysql> explain select * from demo where a='1' and c='1';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra   |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+| 1 | SIMPLE | demo | NULL | ref | abc_index | abc_index | 6 | const | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+--------------------------+1 row in set, 1 warning (0.00 sec)复制代码

从它们的执行计划可以看到，这两条查询语句都使用到了 abc_index 索引，不同的是，它们使用到索引的长度分别是：12、6 字节。

在这里需要额外提一下索引长度的计算方式，对于本例中声明为 varchar(1) 类型的 a 字段，它的索引长度= 1 * (3) + 1 + 2 = 6。

所以这两条查询语句使用索引的情况是：

1. 使用联合索引，索引长度为 12 字节，使用到的索引字段是 a,b 字段；
2. 使用联合索引，索引长度为 6 字节，使用到的索引字段是 a 字段；

由此可见：最左前缀原则要求，查询条件必须是从索引最左列开始的连续几列。

5.3 范围查询

第三种情况是查询条件用的是范围查询（<,>,!=,<=,>=,between,like）时，如：

select * from demo where a='1' and b!='1' and c='1';复制代码

这两条查询语句的执行计划是：

mysql> EXPLAIN select * from demo where a='1' and b!='1' and c='1';
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra   |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+| 1 | SIMPLE | demo | NULL | range | abc_index | abc_index | 12 | NULL | 2 | 10.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+1 row in set, 1 warning (0.00 sec)复制代码

从执行计划可以看到，第一条 SQL 使用了联合索引，且索引长度为 12 字节，即用到了 a,b 两个字段；第二条 SQL 也使用了联合索引，索引长度为 6 字节，仅使用了联合索引中的 a 字段。

综上所述，在全字段匹配且为范围查询的情况下，也能使用联合索引，但只能使用到联合索引中第一个出现范围查询条件的字段。

需要注意的是：

5.4 查询条件为函数或表达式

第四种情况是查询条件中带有函数或特殊表达式的，比如：

select * from demo where id + 1 = 2;select * from demo where concat(a, '1') = '11';复制代码

可能由于数据的原因（空表），我输出的执行计划是使用了联合索引的，但是事实上，在查询条件中，等式不等式左侧的字段包含表达式或函数时，该字段是不会用到索引的。

至于原因，是因为使用函数或表达式的情况下，索引字段本身的值已不具备有序性。

5.5 其他索引失效的场景

6. 索引下推

上文中已经罗列了联合索引的实际结构、最左前缀原则以及索引失效的场景，这里再说一下索引下推这个重要的优化规则。

select * from demo where a > '1' and b='1';

mysql> explain select * from demo where a > '1' and b='1';
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra   |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+| 1 | SIMPLE | demo | NULL | range | abc_index | abc_index | 6 | NULL | 1 | 10.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+1 row in set, 1 warning (0.00 sec)复制代码

上面这条查询语句，从它的执行计划也可以看出，它使用的索引长度为 6 个字节，只用到了第一个字段。

所以 MySQL 在查询过程中，只会对第一个字段 a 进行 a > '1' 的条件判断，当满足条件后，存储引擎并不会进行 b=1 的判断， 而是通过回表拿到整个数据行之后再进行判断。

这好像很蠢，就算索引只用到了第一个字段，但明明索引树中就有 b 字段的数据，为什么不直接进行判断呢？

听上去好像是个 bug，其实在未使用索引下推之前整个查询逻辑是：由存储引擎检索索引树，就算索引树中存在 b 字段的值，但由于这条查询语句的执行计划使用了联合索引但没有用到 b 字段，所以也无法进行 b 字段的条件判断，当存储引擎拿到满足条件（a>'1'）的数据后，再由 MySQL 服务器进行条件判断。

在 MySQL5.6 版本中对这样的情况进行优化，引入索引下推技术：在搜索索引树的过程中，就算没能用到联合索引的其他字段，也能优先对查询条件中包含且索引也包含的字段进行判断，减少回表次数，提高查询效率。

在使用索引下推优化之后，b 字段作为联合索引列，又存在于查询条件中，同时又没有在搜索索引树时被使用到，MySQL 服务器会把查询条件中关于 b 字段的部分也传给存储引擎，存储引擎会在搜索索引树命中数据之后再进行 b 字段查询条件的判断，满足的才会加入结果集。

Ps: 执行计划中 Extra 字段的值包含 Using index condition 就代表使用到了索引下推。

7. 温故知新

1. 索引分类？聚簇索引结构？非聚簇索引结构？
2. 常用的实现索引的数据模型？
3. B+树索引的执行流程？
4. 什么是回表？如何优化？
5. 什么是覆盖索引？
6. 什么是最左前缀原则？
7. 索引在哪些情况下可能会失效？
8. 什么是索引下推？

更多相关免费学习推荐：mysql教程(视频)

热心网友 时间：2022-04-29 17:33

热心网友 时间：2022-04-29 18:51

热心网友 时间：2022-04-29 20:26

在满足语句需求的情况下，尽量少的访问资源是数据库设计的重要原则，这和执行的 SQL 有直接的关系，索引问题又是 SQL 问题中出现频率最高的，常见的索引问题包括：无索引（失效）、隐式转换。1. SQL 执行流程看一个问题，在下面这个表 T 中，如果我要执行 需要执行几次树的搜索操作，会扫描多少行？

这分别是 ID 字段索引树、k 字段索引树。

这条 SQL 语句的执行流程：

1. 在 k 索引树上找到 k=3，获得 ID=3002. 回表到 ID 索引树查找 ID=300 的记录，对应 R33. 在 k 索引树找到下一个值 k=5，ID=5004. 再回到 ID 索引树找到对应 ID=500 的 R4

5. 在 k 索引树去下一个值 k=6，不符合条件，循环结束

这个过程读取了 k 索引树的三条记录，回表了两次。因为查询结果所需要的数据只在主键索引上有，所以必须得回表。所以，我们该如何通过优化索引，来避免回表呢？2. 常见索引优化2.1 覆盖索引覆盖索引，换言之就是索引要覆盖我们的查询请求，无需回表。

如果执行的语句是 ，这样的话因为 ID 的值在 k 索引树上，就不需要回表了。

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

但是，维护索引是有代价的，所以在建立冗余索引来支持覆盖索引时要权衡利弊。

2.2 最左前缀原则

B+ 树的数据项是复合的数据结构，比如 的时候，B+ 树是按照从左到右的顺序来建立搜索树的，当 这样的数据来检索的时候，B+ 树会优先比较 name 来确定下一步的检索方向，如果 name 相同再依次比较 sex 和 age，最后得到检索的数据。

可以清楚的看到，A1 使用 tl 索引，A2 进行了全表扫描，虽然 A2 的两个条件都在 tl 索引中出现，但是没有使用到 name 列，不符合最左前缀原则，无法使用索引。所以在建立联合索引的时候，如何安排索引内的字段排序是关键。评估标准是索引的复用能力，因为支持最左前缀，所以当建立（a，b）这个联合索引之后，就不需要给 a 单独建立索引。原则上，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的。上面这个例子中，如果查询条件里只有 b，就是没法利用（a，b）这个联合索引的，这时候就不得不维护另一个索引，也就是说要同时维护（a，b）、（b）两个索引。这样的话，就需要考虑空间占用了，比如，name 和 age 的联合索引，name 字段比 age 字段占用空间大，所以创建（name，age）联合索引和（age）索引占用空间是要小于（age，name）、(name）索引的。

2.3 索引下推

以人员表的联合索引（name, age）为例。如果现在有一个需求：检索出表中“名字第一个字是张，而且年龄是26岁的所有男性”。那么，SQL 语句是这么写的

通过最左前缀索引规则，会找到 ID1，然后需要判断其他条件是否满足在 MySQL 5.6 之前，只能从 ID1 开始一个个回表。到主键索引上找出数据行，再对比字段值。而 MySQL 5.6 引入的索引下推优化(index condition pushdown)，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。这样，减少了回表次数和之后再次过滤的工作量，明显提高检索速度。

2.4 隐式类型转化

隐式类型转化主要原因是，表结构中指定的数据类型与传入的数据类型不同，导致索引无法使用。所以有两种方案：

修改表结构，修改字段数据类型。

修改应用，将应用中传入的字符类型改为与表结构相同类型。

3. 为什么会选错索引3.1 优化器选择索引是优化器的工作，其目的是找到一个最优的执行方案，用最小的代价去执行语句。在数据库中，扫描行数是影响执行代价的因素之一。扫描的行数越少，意味着访问磁盘数据的次数越少，消耗的 CPU 资源越少。当然，扫描行数并不是唯一的判断标准，优化器还会结合是否使用临时表、是否排序等因素进行综合判断。

3.2 扫描行数

MySQL 在真正开始执行语句之前，并不能精确的知道满足这个条件的记录有多少条，只能通过索引的区分度来判断。显然，一个索引上不同的值越多，索引的区分度就越好，而一个索引上不同值的个数我们称为“基数”，也就是说，这个基数越大，索引的区分度越好。

MySQL 使用采样统计方法来估算基数：采样统计的时候，InnoDB 默认会选择 N 个数据页，统计这些页面上的不同值，得到一个平均值，然后乘以这个索引的页面数，就得到了这个索引的基数。而数据表是会持续更新的，索引统计信息也不会固定不变。所以，当变更的数据行数超过 1/M 的时候，会自动触发重新做一次索引统计。

在 MySQL 中，有两种存储索引统计的方式，可以通过设置参数 innodb_stats_persistent 的值来选择：

on 表示统计信息会持久化存储。默认 N = 20，M = 10。

off 表示统计信息只存储在内存中。默认 N = 8，M = 16。

由于是采样统计，所以不管 N 是 20 还是 8，这个基数都很容易不准确。所以，冤有头债有主，MySQL 选错索引，还得归咎到没能准确地判断出扫描行数。

可以用 来重新统计索引信息，进行修正。

3.3 索引选择异常和处理1. 采用 force index 强行选择一个索引。2. 可以考虑修改语句，引导 MySQL 使用我们期望的索引。3. 有些场景下，可以新建一个更合适的索引，来提供给优化器做选择，或删掉误用的索引。