开发者

MySQL之主键索引排序失效问题

目录
  • 主键索引排序失效
    • 现在初始化几行android数据
    • 查一下所有记录
    • 查看一下执行计划
  • 总结

    主键索引排序失效

    环境:mysql8

    有一张用户信息表user_info,建表DDL如下:

    CREATE TABLE `user_info` (
      `id` int(11) NOT NULL COMMENT '用户编号',
      `age` int(11) NOT NULL COMMENT '用户年龄',
     js 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);

    查一下所有记录

    MySQL之主键索引排序失效问题

    接着往这个表里插入一行数据(6,1),猜测一下这行数据最终会插入在什么位置?

    可能大部分人都会认为插入在(5,3)(7,8)之间,因为id=6的话,5<6<7。

    但是再次查看结果,发现并符合预期,而是插入在了(1,1)(5,3)之间:

    MySQL之主键索引排序失效问题

    看起来效果就像是age变为了主键,根据age默认排序了,或者说主键索引排序失效。

    这是因为在MySQL底层实现中,对于像use开发者_Hadoopr_info这种特殊的表,有特殊的处理方式。这张user_infomiEPkr的特殊点在于,只有两列,一列是主键,另一列也有索引。

    这时候非主键http://www.devze.com的age这一列就是一个覆盖索引,因为age的索引可以查到所有字段。

    MySQL内部会认为访问数据的时候,覆盖索引的效率比主键索引高,所以维护默认的排序会优先根据覆盖索引列来进行。

    查看一下执行计划

    MySQL之主键索引排序失效问题

    • type=index,代表只遍历了索引树;
    • key=idx_ageandroid,代表真正用到了索引;
    • Extra=Using index,代表覆盖索引生效,在索引树中就可以查到所需数据,避免了回表扫描表数据文件。

    总结

    以上为个人经验,希望能给大家一个参考,也希望大家多多支持我们。

    0

    上一篇:

    下一篇:

    精彩评论

    暂无评论...
    验证码 换一张
    取 消

    最新数据库

    数据库排行榜