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

数据库  /  管理员 发布于 6年前   245

排序问题

最近看了极客时间上 《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 的数据模型是一样的，因此 ca 是多余的。为啥？？

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

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

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

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

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

是不是？

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

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

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

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

就用 handler 语句来验证下，先建一个简单的表，插入几条数据：

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;

InnoDB 自动扩展每个二级索引，把主键值追加到索引列后面，把扩展后的组合列作为该索引的索引列。对于上面 t_simple 表的 k_v 索引，扩展后是 (v, id)列。

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

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

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

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对的支持。