Mysql对于Innodb关于ORDER BY使用索引的性能问题

最新推荐文章于 2023-04-10 00:33:12 发布

Mr.Zhang

最新推荐文章于 2023-04-10 00:33:12 发布

阅读量786

点赞数 1

分类专栏： Mysql 文章标签： mysql sql

本文链接：https://blog.csdn.net/qq_34199383/article/details/106909143

版权

Mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

如何提升 ORDER BY 查询速度

通常ORDER BY（对某些列进行排序）语句的性能方面对新人来说很容易被忽略。当然尽管对ORDER BY 的列加索引也不一定能够对性能有所提升！

关于ORDER BY是对我们查询出来的记录按照某种规则进行排序（也就是排序算法）。
如果查询出来的结果集过大的话，将无法再内存中直接进行排序，可能要借助磁盘空间来暂时存储中间结果（凡是跟磁盘挂钩的，速度肯定不会快到哪里去），最后才返回给客户端显示。

通常情况下如果遇到数据量大的时候使用ORDER BY我们可以考虑给ORDER BY的这些列建立一个索引。（但这可能起不到作用，主要看语法，后面说。）

CREATE TABLE player_score (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
type int(11) NOT NULL DEFAULT ‘0’,
score int(11) unsigned NOT NULL DEFAULT ‘0’,
rank int(11) unsigned NOT NULL DEFAULT ‘0’,
other int(11) unsigned NOT NULL DEFAULT ‘0’,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

例：我们有一个sql语句如下

SELECT * FROM player_score order by type, score LIMIT 10;

对于这条SQL查询出来的结果集来说，Innodb是先对type类型的值进行排序，如果type值相同再对score进行排序的。
这个步骤有没觉得似曾相识？

是的！他跟我们的索引逻辑是一样的

在我们对type跟score建立索引后（顺序一定要对！！！先type后score，跟语句中一样！！！）,并命名为：idx_type_score，这棵B+树是按照type大小排序，相同则按照score排序，完全符合ORDER BY的需求，对建立了索引的字段进行ORDER BY相当于不需要再次进行各种排序了，也不用考虑排序过程中数据量是否过大，是否会在硬盘中暂存，可以直接从索引中取出对应的值，再进行回表操作就可以取出完整数据了。

PS ：回表其实就是自己建立的非聚簇索引中获取对应的主键值，再回到Innodb为我们自动建立的聚簇索引中通过主键来获取完整的记录。
这种操作主要是为了节省空间，不需要每建立一个索引就保存一套完整的数据。

从上面内容引出如下

我们将SQL语句改成这样：

SELECT * FROM player_score order by type, score;

可以看出我们相比前面的SQL少了LIMIT 10

二级索引中获取的每一个主键都需要回调聚簇索引中查询一遍获取完整数据，所以LIMIT很大的时候其实使用索引反而变成了一种累赘！

因为没有了限制，那输出的数据会随着表中数据量的变化而变慢（因为要回表的数据越来越多了），直到最后由查询优化器去决定使用全表查询还是索引查询了。

对于这种情况，使用索引可能并不能提升速度！

进一步改写SQL语句

从上面的SQL语句可以看出，对于数据量大的情况下上面语句建立索引其实并不能起到预期的效果。
我们可以结合自身的需求，是否能将上面的SQL语句改写成如下形式：

SELECT type, score, rank FROM player_score order by type, score;

我们只需要查询type、score、rank三个字段，那在SELECT的时候只查询出需要的这几个字段（当然其他情况其实也是不太推荐使用 * 把所有数据查询出来的），对于这三个字段，我们可以选择使用覆盖索引，将type、score、rank建立为一个索引 idx_type_score_rank。
idx_type_score_rank索引的目录项中一个目录页的数据包含以下几个参数

目录页
type
score
rank
id

这是我们再次使用ORDER BY来对type, score进行排序时可以使用idx_type_score_rank索引来进行查找数据（这是联合索引的特性，不了解可以去看看），而且从上面的目录页中可以看出，这棵二级索引中包含了我们想要查找的所有数据，所以就不需要再次通过获取ID后回表来查询聚簇索引数据。