mysql limit工作原理及order by效率分析

MySQL的limit m n工作原理就是先读取前面m+n条记录,然后抛弃前m条,读后面n条想要的,所以m越大,偏移量越大,性能就越差。

推荐分页查询方法:

1、尽量给出查询的大致范围

SELECT c1,c2,cn... FROM table WHERE id>=20000 LIMIT 10;

2、子查询法

SELECT c1,c2,cn... FROM table WHERE id>=
(
    SELECT id FROM table LIMIT 20000,1
)
LIMIT 10;

3、高性能MySQL一书中提到的只读索引方法

SELECT c1,c2,cn... FROM member ORDER BY last_active LIMIT 50,5

优化后SQL:

SELECT c1, c2, cn .. .
  FROM member
 INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5)
 USING (member_id)

分别在于,优化前的SQL需要更多I/O浪费,因为先读索引,再读数据,然后抛弃无需的行。而优化后的SQL(子查询那条)只读索引(Cover index)就可以了,然后通过member_id读取需要的列。

order by和limit

如果你order by和limit一起使用,那么mysql在排序结果中找到最初的row_count行之后就会完成这条语句,而不是对整个结果集进行排序。如果使用了索引排序,它就非常快地完成。如果整个filesort必须都做完的话,那么在找到最初的row_count行之前,匹配该查询的所有行都将被select,并且做sort操作。如果这些行找到了,mysql将不会对剩余的结果集进行排序。

Order by和Limit混合使用引起的问题

如果在order by语句中返回的结果集有很多行,那么非排序的列的返回结果是不确定的,即随机的,所以如果配合limit的话每次返回的结果集的顺序是不固定的,比如下面这个例子

mysql> SELECT * FROM ratings ORDER BY category;

+----+----------+--------+

| id | category | rating |

+----+----------+--------+

| 1 | 1 | 4.5 |

| 5 | 1 | 3.2 |

| 3 | 2 | 3.7 |

| 4 | 2 | 3.5 |

| 6 | 2 | 3.5 |

| 2 | 3 | 5.0 |

| 7 | 3 | 2.7 |

+----+----------+--------+

使用了limit以后,可发现id列和rating列和之前的结果集顺序有出入:

mysql> SELECT * FROM ratings ORDER BY category LIMIT 5;

+----+----------+--------+

| id | category | rating |

+----+----------+--------+

| 1 | 1 | 4.5 |

| 5 | 1 | 3.2 |

| 4 | 2 | 3.5 |

| 3 | 2 | 3.7 |

| 6 | 2 | 3.5 |

+----+----------+--------+

如果你有必要保证每次有相同的结果集,则需要order by你需要的那几列了:

mysql> SELECT * FROM ratings ORDER BY category, id;

+----+----------+--------+

| id | category | rating |

+----+----------+--------+

| 1 | 1 | 4.5 |

| 5 | 1 | 3.2 |

| 3 | 2 | 3.7 |

| 4 | 2 | 3.5 |

| 6 | 2 | 3.5 |

| 2 | 3 | 5.0 |

| 7 | 3 | 2.7 |

+----+----------+--------+

mysql> SELECT * FROM ratings ORDER BY category, id LIMIT 5;

+----+----------+--------+

| id | category | rating |

+----+----------+--------+

| 1 | 1 | 4.5 |

| 5 | 1 | 3.2 |

| 3 | 2 | 3.7 |

| 4 | 2 | 3.5 |

| 6 | 2 | 3.5 |

+----+----------+--------+

Order by和limit一起使用的优化原理

从MySQL5.6.2版本以后,优化器将更加智能地处理下面形式的查询了

SELECT ... FROM single_table ... ORDER BY non_index_column [DESC] LIMIT [M,]N;

这种在很大的结果集中只返回很少的行数的查询类型在web应用中非常常见,比如

SELECT col1, ... FROM t1 ... ORDER BY name LIMIT 10;

SELECT col1, ... FROM t1 ... ORDER BY RAND() LIMIT 15;

排序缓存有一个参数是sort_buffer_size,如果这个参数大小足够上面范例中的N行的排序结果集(如果M也被定义,那就是M+N行的结果集大小),那么服务器将会避免一个文件排序操作,使得排序完全在内存中完成。

(1)内存排序+limit原理

1 扫描表,在内存中插入那些被选择排序的列的数据到一个排好序的队列中,比如order by col1,col2,则插入col1和col2列的数据。如果队列满了,则挤出排序在末尾的数据。

2 返回队列中的前N行记录,如果M也被定义,则调到第M行开始返回后续的N行记录。

(2)文件排序+limit原理

1扫描表,重复步骤2和3,直到表的结尾

2选中这些行数直到排序缓存被填满

3在排序缓存中写入第一个N行(如果M被定义,则M+N行)到一个排序文件中。

(3)两者比较

在内存中排序和使用文件排序相比,扫描表的代价几乎是一样的,不同的是其他的开销:

内存排序的方法在插入数据到一个有序队列中会牵扯到更多的cpu资源,而文件排序会消耗更多的磁盘IO,优化器在考虑两者的平衡性上会主要考虑N的值大小

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏小强的进阶之路

BAT大厂都会问的MySQL底层数据结构

左边子节点的数据小于父节点数据,右边子节点的数据大于父节点数据。如果col2是索引,查找索引为89的行元素,那么只需要查找两次,就可以获取到行元素所在的磁盘指针...

12730
来自专栏雪山飞猪

大数据架构的简单概括

Doug Cutting启动了一个赫赫有名的项目Hadoop,主要包括Hadoop分布式文件系统HDFS和大数据计算引擎MapReduce,分别实现了GFS和M...

19330
来自专栏大数据成神之路

从0建设离线数据仓库

技术升级快于我们的想象,今天的故事在明天来看就是一种常识。对于数仓而言,又何尝不是?互联网的发展,导致大数据的人才缺口。互联网公司雨后春笋,传统行业机巧转身。短...

32770
来自专栏好好学java的技术栈

基于 SpringBoot 的 Restful 风格实现增删改查功能(附源码)

在去年的时候,在各种渠道中略微的了解了SpringBoot,在开发web项目的时候是如何的方便、快捷。但是当时并没有认真的去学习下,毕竟感觉自己在Struts和...

17220
来自专栏云原生实验室

在 Kubernetes 中实现零宕机部署应用

如果你使用像 Gmail 这样的在线服务或者大型社交媒介和电子商务平台,你可能从来都没有遇到过哪个页面会提示你“请等待我们的应用更新完成”。

12910
来自专栏雪山飞猪

Node.js能解决什么问题?

对于PHP、JAVA、Python等服务端语言中,为每个客户端连接创建一个新的线程,而每个线程需要大约2M的内存,理论上,具有8GB内存的服务器可以同时连接的最...

10330
来自专栏Java建设者

一文详解微服务架构

本文将介绍微服务架构和相关的组件,介绍他们是什么以及为什么要使用微服务架构和这些组件。本文侧重于简明地表达微服务架构的全局图景,因此不会涉及具体如何使用...

16040
来自专栏一块自留地

Mysql索引原理及各种tree的比较

我们都知道mysql的索引使用B树来实现的,那么为什么会考虑B树,不考虑其他数据结构呢?

8540
来自专栏开发架构二三事

mysql主从复制中的1032错误

可以看到是一条更新语句,UPDATE infosys_login. business_login_user_auth_info where XXX ... @1...

24820
来自专栏用户画像

Hive

Hive是Hadoop生态系统中比不可少的一个工具,它提供了一种SQL(结构化查询语言)方言,可以查询存储在Hadoop分布式文件系统(HDFS)中的数据或其他...

12010

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励