MySQL InnoDB 二级索引的排序示例详解_Mysql_脚本之家
脚本之家
服务器常用软件
手机版
投稿中心
关注微信
快捷导航
软件下载
android
MAC
驱动下载
字体下载
DLL
源码下载
PHP
ASP.NET
ASP
JSP
软件编程
C#
JAVA
C 语言
Delphi
Android
网络编程
PHP
ASP.NET
ASP
JavaScript
在线工具
CSS格式化
JS格式化
Html转化为Js
数据库
MYSQL
MSSQL
oracle
DB2
MARIADB
CMS
PHPCMS
DEDECMS
帝国CMS
WordPress
常用工具
PHP开发工具
python
Photoshop
必备软件
网站首页
网页制作
网络编程
脚本专栏
脚本下载
数据库
服务器
电子书籍
操作系统
网站运营
平面设计
其它
媒体动画
电脑基础
硬件教程
网络安全
MsSql
Mysql
mariadb
oracle
DB2
mssql2008
mssql2005
SQLite
PostgreSQL
MongoDB
Redis
Access
数据库文摘
数据库其它
您的位置：首页 → 数据库 → Mysql → MySQL InnoDB二级索引排序
MySQL InnoDB 二级索引的排序示例详解
更新时间：2019年01月09日 10:23:15 作者：coderbee笔记
这篇文章主要给大家介绍了关于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 访问时仍然有按主键排序的效果。
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对脚本之家的支持。
您可能感兴趣的文章:Mysql InnoDB中B树索引使用注意事项Mysql InnoDB引擎的索引与存储结构详解一看就懂的MySQL的聚簇索引及聚簇索引是如何长高的MySQL学习教程之聚簇索引mysql聚集索引、辅助索引、覆盖索引、联合索引的使用Mysql InnoDB聚簇索引二级索引联合索引特点详解
mysql
innodb
二级索引
相关文章
Mysql带返回值与不带返回值的2种存储过程写法这篇文章主要介绍了Mysql带返回值与不带返回值的2种存储过程写法,需要的朋友可以参考下 2017-10-10
mysql处理海量数据时的一些优化查询速度方法最近一段时间由于工作需要，开始关注针对Mysql数据库的select查询语句的相关优化方法,需要的朋友可以参考下 2017-04-04
详细谈谈MYSQL中的COLLATE是什么mysql COLLATE是校对集的意思,可以理解为,排序规则等,下面这篇文章详细的给大家介绍了关于MYSQL中COLLATE是什么的相关资料,需要的朋友可以参考下 2021-06-06
解决Mysql多行子查询的使用及空值问题所谓多行子查询，是指执行查询语句获得的结果集中返回了多行数据的子查询，今天通过本文给大家分享Mysql多行子查询的使用及空值问题，感兴趣的朋友一起看看吧 2022-01-01
mysql数据库 主从复制的配置方法本文主要介绍 mysql数据库 主从负责的配置方法,在做数据库开发的时候有时候会遇到,这里做出详细流程,大家可以参考下 2016-07-07
RHEL6.5编译安装MySQL5.6.26教程这篇文章主要介绍了RHEL6.5编译安装MySQL5.6.26教程的相关资料,需要的朋友可以参考下 2015-10-10
MySQL数据库设计概念及多表查询和事物操作数据库设计就是根据业务系统具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型,本文给大家介绍MySQL数据库设计概念及多表查询和事物操作，感兴趣的朋友一起看看吧 2022-05-05
MySQL修改默认字符集编码的方法这篇文章主要介绍了MySQL修改默认字符集编码的方法的相关资料,非常不错，具有参考借鉴价值，需要的朋友可以参考下 2016-09-09
详细分析mysql MDL元数据锁这篇文章主要介绍了mysql MDL元数据锁的相关资料，文中讲解非常细致，代码帮助大家更好的理解和学习，感兴趣的朋友可以了解下 2020-08-08
MySQL之浅谈DDL和DML大家好，本篇文章主要讲的是MySQL之浅谈DDL和DML，感兴趣的同学赶快来看一看吧，对你有帮助的话记得收藏一下，方便下次浏览 2021-12-12
最新评论
大家感兴趣的内容
1mysql安装图解 mysql图文安装教程(详细说明)2Can''''t connect to MySQL serv3windows下MySQL5.6版本安装及配置过程附有截图和4Mysql字符串截取函数SUBSTRING的用法说明5MySQL提示:The server quit withou6MySQL创建用户与授权方法7mysql之TIMESTAMP（时间戳）用法详解8MySQL——修改root密码的4种方法(以windows为9MySQL日期数据类型、时间类型使用总结10Mysql查看版本号的几种方式
最近更新的内容
MySQL中InnoDB的Memcached插件的使用教程MySQL创建横向直方图的解决方案MySQL复制之GTID复制的具体使用Mysql按特定符号分割成多行和多列的示例浅析MySQL的注入安全问题linux安装mysql 8.0.x的完整步骤浅谈MySQL中的自增主键用完了怎么办MySQL高级进阶sql语句总结大全使用mysql事件调度器定时删除binlog有关mysql优化的一些东东整理
常用在线小工具
微信
投稿
脚本任务
在线工具
关注微信公众号
关于我们 -
广告合作 -
联系我们 -
免责声明 -
网站地图 -
投诉建议 -
在线投稿
CopyRight 2006-2021 JB51.Net Inc All Rights Reserved. 脚本之家 版权所有