MySQL InnoDB 二级索引的排序示例详解

排序问题

最近看了极客时间上《MySQL实战45讲》，纠正了一直以来对 InnoDB 二级索引的一个理解不到位，正好把相关内容总结下。

PS：本文的所有测试基于 MySQL 8.0.13 。

先把问题抛出来，下面的 SQL 所创建的表，有两个查询语句，哪个索引是非必须的？

CREATE TABLE `geek` ( `a` int(11) NOT NULL, `b` int(11) NOT NULL, `c` int(11) NOT NULL, `d` int(11) NOT NULL, PRIMARY KEY (`a`,`b`), KEY `c` (`c`), KEY `ca` (`c`,`a`), KEY `cb` (`c`,`b`)) ENGINE=InnoDB;select * from geek where c=N order by a limit 1;select * from geek where c=N order by b limit 1;

我们知道，二级索引里存放的不是行的位置，而是主键的值，也知道索引是有序的。

如果 c 与 ca 的数据模型一样，那么就要求二级索引的叶子节点不仅是按索引列排序、而且还按关联的主键值进行排序。

我以前的理解是二级索引只按索引列进行排序，主键值是不排序的。

问了专栏作者，得到的答复是：索引 c 就是按照 cab 这样排序，（二级索引)）有保证主键算进去、还是有序的。（PS：非原话，前后问了三次得到）。

本着先问是不是，再问为什么的思路，进行一番探究。

是不是？

如果能直接看 InnoDB 的数据文件，那就可以直接看出是不是遵循了这样的排序规则。可惜那是二进制文件，又没有顺手的工具可以方便查看，放弃。

后来找到了 MySQL 的 handler 语句，它支持 MyISAM/InnoDB 两种引擎的表。handler 语句提供了直接访问表存储引擎的接口。

下面的语法表示读取指定表指定索引的第一条/前一条/下一条/最后一条记录。

handler table_name/table_name_alias read index_name first/pre/next/last;

create table t_simple ( id int primary key, v int, key k_v (v)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;insert into t_simple values (1, 5);insert into t_simple values (10, 5);insert into t_simple values (4, 5);

mysql> handler t_simple open as ts;Query OK, 0 rows affected (0.00 sec)mysql> handler ts read k_v next;+----+------+| id | v |+----+------+| 1 | 5 |+----+------+1 row in set (0.00 sec)mysql> handler ts read k_v next;+----+------+| id | v |+----+------+| 4 | 5 |+----+------+1 row in set (0.00 sec)mysql> handler ts read k_v next;+----+------+| id | v |+----+------+| 10 | 5 |+----+------+1 row in set (0.00 sec)

为什么？

之前一直没看到说 MySQL 有这样的机制，问了前公司和先公司的 DBA 都没了解过这个。

最后 DBA 同事找到了索引扩展, Index Extensions ，里面有这么段描述做了说明：

InnoDB automatically extends each secondary index by appending the primary key columns to it. Consider this table definition:

CREATE TABLE t1 ( i1 INT NOT NULL DEFAULT 0, i2 INT NOT NULL DEFAULT 0, d DATE DEFAULT NULL, PRIMARY KEY (i1, i2), INDEX k_d (d)) ENGINE = InnoDB;

优化器会根据扩展后的二级索引的主键列来决定如何和是否使用那个索引。优化器可以用扩展的二级索引来进行 ref,range,index_merge 等类型的索引访问、松散的索引扫描、连接和排序优化，以及 min()/max() 优化。

可以用 show variables like '%optimizer_switch%'; 查看索引扩展是否开启；用 SET optimizer_switch = 'use_index_extensions=on/off'; 进行开启或关闭，这个只影响当前会话。

经测试，哪怕关闭了当前会话的索引扩展，用 handler 访问时仍然有按主键排序的效果。

总结

更多相关文章

随机推荐