mysql 排序后下一条记录_什么是MySQL InnoDB 二级索引的排序？｜附实例详解

概述

今天主要给大家介绍了关于MySQL InnoDB 二级索引的排序的相关资料，通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值。

每个InnoDB表具有一个特殊的索引称为聚簇索引(也叫聚集索引，聚类索引，簇集索引)。如果表上定义有主键，该主键索引就是聚簇索引。如果未定义主键，MySQL取第一个唯一索引(unique)而且只含非空列(NOT NULL)作为主键，InnoDB使用它作为聚簇索引。如果没有这样的列，InnoDB就自己产生一个这样的ID值，它有六个字节，而且是隐藏的，使其作为聚簇索引。

表中的聚簇索引(clustered index )就是一级索引，除此之外，表上的其他非聚簇索引都是二级索引，又叫辅助索引(secondary indexes)。

排序问题

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

先把问题抛出来，下面的 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 这样排序，(二级索引))有保证主键算进去、还是有序的。

是不是？

如果能直接看 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)

从结果可以看到，遍历的二级索引，值相等时，按主键的顺序遍历，基本可以确定二级索引不仅按索引列排序，还按主键值排序了。