MySQL InnoDB 二级索引的排序

栏目: 数据库 · Mysql · 发布时间: 5年前

内容简介:最近看了极客时间上《MySQL实战45讲》,纠正了一直以来对 InnoDB 二级索引的一个理解不到位,正好把相关内容总结下。PS:本文的所有测试基于 MySQL 8.0.13 。先把问题抛出来,下面的 SQL 所创建的表,有两个查询语句,哪个索引是非必须的?作者给的答案是索引 c 和 ca 的数据模型是一样的,因此 ca 是多余的。为啥??

排序问题

最近看了极客时间上《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;
This table defines the primary key on columns (i1, i2). It also defines a secondary index k_d on column (d), but internally InnoDB extends this index and treats it as columns (d, i1, i2).

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 访问时仍然有按主键排序的效果。

欢迎关注我的微信公众号: coderbee-note ,可以更及时回复你的讨论。

MySQL InnoDB 二级索引的排序

以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

计算机组成(第 6 版)

计算机组成(第 6 版)

Andrew S. Tanenbaum、Todd Austin / 刘卫东、宋佳兴 / 机械工业出版社 / 2014-8-19 / CNY 99.00

本书采用结构化方法来介绍计算机系统,书的内容完全建立在“计算机是由层次结构组成的,每层完成规定的功能”这一概念之上。作者对本版进行了彻底的更新,以反映当今最重要的计算机技术以及计算机组成和体系结构方面的最新进展。书中详细讨论了数字逻辑层、微体系结构层、指令系统层、操作系统层和汇编语言层,并涵盖了并行体系结构的内容,而且每一章结尾都配有丰富的习题。本书适合作为计算机专业本科生计算机组成与结构课程的教......一起来看看 《计算机组成(第 6 版)》 这本书的介绍吧!

URL 编码/解码
URL 编码/解码

URL 编码/解码

MD5 加密
MD5 加密

MD5 加密工具

HEX HSV 转换工具
HEX HSV 转换工具

HEX HSV 互换工具