[MySQL性能优化系列]LIMIT语句优化(转)

    选择打赏方式

1. 背景

假设有如下SQL语句:

SELECT * FROM table1 LIMIT offset, rows

这是一条典型的LIMIT语句,常见的使用场景是,某些查询返回的内容特别多,而客户端处理能力有限,希望每次只取一部分结果进行处理。

上述SQL语句的实现机制是:

  1. 从“table”表中读取offset+rows行记录

  2. 抛弃前面的offset行记录,返回后面的rows行记录作为最终结果。

这种实现机制存在一个弊端:虽然只需要返回rows行记录,但却必须先访问offset行不会用到的记录。对一张数据量很大的表进行查询时,offset值可能非常大,此时limit语句的效率就非常低了。

2. 简单查询的LIMIT优化

假设表message表中有10万行记录,每次取1000条。
优化前:

SELECT message.* FROM message LIMIT 0,1000SELECT message.* FROM message LIMIT 1000,1000SELECT message.* FROM message LIMIT 2000,1000……SELECT message.* FROM message LIMIT 998000,1000SELECT message.* FROM message LIMIT 999000,1000

优化后(利用自增主键,避免offset的使用):

SELECT message.* FROM message WHERE uid>0 LIMIT 1000SELECT message.* FROM message WHERE uid>1000 LIMIT 1000SELECT message.* FROM message WHERE uid>2000 LIMIT 1000……SELECT message.* FROM message WHERE uid>998000 LIMIT 1000SELECT message.* FROM message WHERE uid>999000 LIMIT 1000

在笔者的机器上,优化前,SQL语句从前往后越来越慢(最后一条语句执行了150毫秒),而优化后,每条语句的耗时都是微妙级的。

3. 复杂查询的LIMIT优化

实际工程中遇到的查询,通常要复杂些,比如,多表查询、条件查询。这种情况下,查询结果通常不是按照自增主键的顺序逐一排列的。

例如,对于下述SQL语句,就不能像第二节那样优化了:

SELECT timerec FROM message WHERE evttype = 1 AND nodename = 'node1'LIMIT 0,1000……SELECT timerec FROM message WHERE evttype = 1 AND nodename = 'node1'LIMIT 999000,1000……

优化方案:建立临时表(含自增主键)存储数十万行的查询结果,之后用第二节的方法分多次访问临时表、获取数据。

  1. 创建临时表

CREATE TEMPORARY TABLE tmp_timerec( `uid` bigint(20) NOT NULL AUTO_INCREMENT, `timerec` datetime NOT NULL,
 PRIMARY KEY (`uid`))
  1. 插入查询结果到临时表

INSERT INTO tmp_timerecSELECT null,timerec FROM messageWHERE evttype = 1 AND nodename = ‘node1’
  1. 分多次查询临时表

SELECT timerec FROM tmp_timerec where uid > 0 LIMIT 1000……SELECT timerec FROM tmp_timerec where uid > 999000 LIMIT 1000

转自http://www.cnblogs.com/beynol/p/mysql-optimization-limit.html

版权声明:若无特殊注明,本文皆为《 楚天之怒 》原创,转载请保留文章出处。
本文链接:[MySQL性能优化系列]LIMIT语句优化(转) https://www.chutianzhinu.com/post-60.html
正文到此结束

热门推荐

发表吐槽

你肿么看?

你还可以输入 250 / 250 个字

嘻嘻 大笑 可怜 吃惊 害羞 调皮 鄙视 示爱 大哭 开心 偷笑 嘘 奸笑 委屈 抱抱 愤怒 思考 日了狗 胜利 不高兴 阴险 乖 酷 滑稽

评论信息框

吃奶的力气提交吐槽中...

已有1条吐槽

橙子柠檬Blog

2018-09-04 00:35 广西梧州市电信
这排版语句都混合一起了,看着费劲 face
 Windows 10 x64   Google Chrome 68.0.3440.106