服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|

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

2019-06-11 11:04来源:coderbee笔记所属分类:Mysql

这篇文章主要给大家介绍了关于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 的数据模型是一样的,因此 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 访问时仍然有按主键排序的效果。

总结

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

延伸 · 阅读

精彩推荐
  • MysqlMysql如何适当的添加索引介绍

    Mysql如何适当的添加索引介绍

    今天小编就为大家分享一篇关于Mysql如何适当的添加索引介绍,小编觉得内容挺不错的,现在分享给大家,具有很好的参考价值,需要的朋友一起跟随小编来看看吧...

    DreamBoyMrsLin 267 2019-06-03
  • Mysqlmysql语句如何插入含单引号或反斜杠的值详解

    mysql语句如何插入含单引号或反斜杠的值详解

    这篇文章主要给大家介绍了关于mysql语句如何插入含单引号或反斜杠的值的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧...

    nemo 466 2019-06-05
  • Mysql详解Mysql数据库date, datetime类型设置0000-00-00默认值(default)报错问题

    详解Mysql数据库date, datetime类型设置0000-00-00默认值(default)报错问

    这篇文章主要介绍了详解Mysql数据库date, datetime类型设置0000-00-00默认值(default)报错问题,小编觉得挺不错的,现在分享给大家,也给大家做个参考。一起跟随小编过来看看吧...

    chentging 257 2019-06-10
  • MysqlMySQL SHOW PROCESSLIST协助故障诊断全过程

    MySQL SHOW PROCESSLIST协助故障诊断全过程

    这篇文章主要给大家介绍了关于MySQL SHOW PROCESSLIST协助故障诊断的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧...

    彭东稳 361 2019-06-05
  • MysqlMySQL中int最大值深入讲解

    MySQL中int最大值深入讲解

    这篇文章主要给大家介绍了关于MySQL中int最大值的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧...

    haoyq 471 2019-06-06
  • MysqlMySQL数据库列的增删改实现方法

    MySQL数据库列的增删改实现方法

    这篇文章主要介绍了MySQL数据库列的增删改实现方法,结合实例形式分析了mysql数据库针对列的增加、修改、删除等相关操作sql命令及使用技巧,需要的朋友可以参考下...

    小菜鸟有大梦想 182 2019-06-02
  • MysqlMySQL慢日志实践小结

    MySQL慢日志实践小结

    这篇文章主要介绍了MySQL慢日志实践小结,小编觉得挺不错的,现在分享给大家,也给大家做个参考。一起跟随小编过来看看吧...

    极客者 145 2019-06-10
  • MysqlMysql主键和唯一键的区别点总结

    Mysql主键和唯一键的区别点总结

    在本篇文章中小编给大家分享了关于Mysql主键和唯一键的区别,有兴趣的朋友们学习下吧。...

    laozhang 272 2019-06-07