MySQL之主键索引排序失效问题
目录
主键索引排序失效
现在初始化几行数据
查一下所有记录
查看一下执行计划
总结
主键索引排序失效环境：MySQL8
有一张用户信息表user_info，建表DDL如下：
CREATE TABLE `user_info` (
`id` int(11) NOT NULL COMMENT '用户编号',
`age` int(11) NOT NULL COMMENT '用户年龄',
PRIMARY KEY (`id`),
KEY `idx_age` (`age`) USING BTREE COMMENT '年龄索引'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
只有两列，第一列用户编号id做主键，第二列用户年龄age有一个普通索引idx_age。
INSERT INTO `user_info` (`id`,`age`) VALUES(1,1),(5,3),(7,8),(11,12);
查一下所有记录
接着往这个表里插入一行数据(6,1)，猜测一下这行数据最终会插入在什么位置？
可能大部分人都会认为插入在(5,3)和(7,8)之间，因为id=6的话，5<6<7。
但是再次查看结果，发现并符合预期，而是插入在了(1,1)和(5,3)之间：
看起来效果就像是age变为了主键，根据age默认排序了，或者说主键索引排序失效。
这是因为在MySQL底层实现中，对于像user_info这种特殊的表，有特殊的处理方式。这张user_info表的特殊点在于，只有两列，一列是主键，另一列也有索引。
这时候非主键的age这一列就是一个覆盖索引，因为age的索引可以查到所有字段。
MySQL内部会认为访问数据的时候，覆盖索引的效率比主键索引高，所以维护默认的排序会优先根据覆盖索引列来进行。
查看一下执行计划
type=index，代表只遍历了索引树；
key=idx_age，代表真正用到了索引；
Extra=Using index，代表覆盖索引生效，在索引树中就可以查到所需数据，避免了回表扫描表数据文件。
以上为个人经验，希望能给大家一个参考，也希望大家多多支持软件开发网。
相关文章
Nyako
2021-06-03
Xena
2020-07-03
Elizabeth
2023-07-20
Ianthe
2023-07-20
Valora
2023-07-20
Phemia
2023-07-20
Tia
2023-07-20
Summer
2023-07-20
Fredrica
2023-07-20
Hester
2023-07-20
Ianthe
2023-07-20
Jacinda
2023-07-20
Winola
2023-07-20
Emily
2023-07-20
Gella
2023-07-20
Maha
2023-07-21
Edie
2023-07-21
Bonnie
2023-07-21
Ilona
2023-07-22