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

2023-09-01 17:51:07 57

排序问题

最近看了极客时间上《MySQL实战45讲》，纠正了一直以来对InnoDB二级索引的一个理解不到位，正好把相关内容总结下。

PS：本文的所有测试基于MySQL8.0.13。

先把问题抛出来，下面的SQL所创建的表，有两个查询语句，哪个索引是非必须的？

CREATETABLE`geek`(
`a`int(11)NOTNULL,
`b`int(11)NOTNULL,
`c`int(11)NOTNULL,
`d`int(11)NOTNULL,
PRIMARYKEY(`a`,`b`),
KEY`c`(`c`),
KEY`ca`(`c`,`a`),
KEY`cb`(`c`,`b`)
)ENGINE=InnoDB;

select*fromgeekwherec=Norderbyalimit1;
select*fromgeekwherec=Norderbyblimit1;

作者给的答案是索引c和ca的数据模型是一样的，因此ca是多余的。为啥？？

我们知道，二级索引里存放的不是行的位置，而是主键的值，也知道索引是有序的。

如果c与ca的数据模型一样，那么就要求二级索引的叶子节点不仅是按索引列排序、而且还按关联的主键值进行排序。

我以前的理解是二级索引只按索引列进行排序，主键值是不排序的。

问了专栏作者，得到的答复是：索引c就是按照cab这样排序，（二级索引)）有保证主键算进去、还是有序的。（PS：非原话，前后问了三次得到）。

本着先问是不是，再问为什么的思路，进行一番探究。

是不是？

如果能直接看InnoDB的数据文件，那就可以直接看出是不是遵循了这样的排序规则。可惜那是二进制文件，又没有顺手的工具可以方便查看，放弃。

后来找到了MySQL的handler语句，它支持MyISAM/InnoDB两种引擎的表。handler语句提供了直接访问表存储引擎的接口。

下面的语法表示读取指定表指定索引的第一条/前一条/下一条/最后一条记录。

handlertable_name/table_name_aliasreadindex_namefirst/pre/next/last;

就用handler语句来验证下，先建一个简单的表，插入几条数据：

createtablet_simple(
idintprimarykey,
vint,
keyk_v(v)
)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;

insertintot_simplevalues(1,5);
insertintot_simplevalues(10,5);
insertintot_simplevalues(4,5);

上面的插入语句，二级索引列的值都是一样的，主键不是按顺序的，这样就可以看遍历时是不是按主键顺序存放的。

mysql>handlert_simpleopenasts;
QueryOK,0rowsaffected(0.00sec)

mysql>handlertsreadk_vnext;
+----+------+
|id|v|
+----+------+
|1|5|
+----+------+
1rowinset(0.00sec)

mysql>handlertsreadk_vnext;
+----+------+
|id|v|
+----+------+
|4|5|
+----+------+
1rowinset(0.00sec)

mysql>handlertsreadk_vnext;
+----+------+
|id|v|
+----+------+
|10|5|
+----+------+
1rowinset(0.00sec)

从结果可以看到，遍历的二级索引，值相等时，按主键的顺序遍历，基本可以确定二级索引不仅按索引列排序，还按主键值排序了。

为什么？

之前一直没看到说MySQL有这样的机制，问了前公司和先公司的DBA都没了解过这个。

最后DBA同事找到了索引扩展,IndexExtensions，里面有这么段描述做了说明：

InnoDBautomaticallyextendseachsecondaryindexbyappendingtheprimarykeycolumnstoit.Considerthistabledefinition:

CREATETABLEt1(
i1INTNOTNULLDEFAULT0,
i2INTNOTNULLDEFAULT0,
dDATEDEFAULTNULL,
PRIMARYKEY(i1,i2),
INDEXk_d(d)
)ENGINE=InnoDB;

InnoDB自动扩展每个二级索引，把主键值追加到索引列后面，把扩展后的组合列作为该索引的索引列。对于上面t_simple表的k_v索引，扩展后是(v,id)列。

优化器会根据扩展后的二级索引的主键列来决定如何和是否使用那个索引。优化器可以用扩展的二级索引来进行ref,range,index_merge等类型的索引访问、松散的索引扫描、连接和排序优化，以及min()/max()优化。

可以用showvariableslike'%optimizer_switch%';查看索引扩展是否开启；用SEToptimizer_switch='use_index_extensions=on/off';进行开启或关闭，这个只影响当前会话。

经测试，哪怕关闭了当前会话的索引扩展，用handler访问时仍然有按主键排序的效果。

总结

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

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

热门推荐

随机推荐