当前位置: 主页>网站教程>数据库> 我所了解的MySQL之二：索引

分享文章到：

我所了解的MySQL之二：索引

发布时间：11/01 来源：未知浏览：关键词：

mysql教程栏目今天介绍相干索引见识。

mysql教程栏目今天介绍相干索引见识。

图片来源: Data Structure Visualizations

从上面这张示例图也可以看到，这棵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 对应的整个数据行，如下图所示：

结合索引索引树的子节点次序是按照声明索引时的字段来排序的，相似于 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 是该字段声明时的长度。
第二个数字 3 是该字段字符类型的长度：utf8=3, gbk=2, latin1=1。
第三个数字 1 是该字段的默许类型，若默许允许 NULL，第三个数字是 1，由于 NULL 需要一个字节的额外空间；若默许不允许 NULL，这里应当是0。
第四个数字 2 是 varchar 类型的变长字段需要附加的字节。

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

运用结合索引，索引长度为 12 字节，运用到的索引字段是 a,b 字段；
运用结合索引，索引长度为 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 字段。

综上所述，在全字段匹配且为范畴查询的状况下，也能运用结合索引，但只能运用到结合索引中首先个涌现范畴查询前提的字段。

需要注意的是：

like 必需请求是左含糊匹配才干用到索引，由于字符类型字段的索引树也是有序的。
between 并纷歧定是范畴查询，它相当于运用 in 多值精准匹配，所以 between 并不会由于是范畴查询就让结合索引背面的索引列失效。

5.4 查询前提为函数或表达式

第四种状况是查询前提中带有函数或特别表达式的，比方：

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

可能因为数据的缘由（空表），我导出的施行规划是运用了结合索引的，但是事实上，在查询前提中，等式不等式左侧的字段包括表达式或函数时，该字段是不会用到索引的。

至于缘由，是由于运用函数或表达式的状况下，索引字段自身的值已不具备有序性。

5.5 其他索引失效的场景

查询影响行数大于全表的25%
查询前提运用 <>(!=), not in, is not null
in 查询前提中值数据类型纷歧致，MySQL 会将所有值转化为与索引列一致的数据类型，从而没法运用索引

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 就代表运用到了索引下推。