limit 优化查询

一时失言乱红尘 2022-05-19 22:58 165阅读 0赞

## [\[MySQL性能优化系列\]LIMIT语句优化][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,1000
    SELECT message.* FROM message LIMIT 1000,1000
    SELECT message.* FROM message LIMIT 2000,1000
    ……
    SELECT message.* FROM message LIMIT 998000,1000
    SELECT message.* FROM message LIMIT 999000,1000

优化后（利用自增主键，避免offset的使用）：

SELECT message.* FROM message WHERE uid>0 LIMIT 1000
    SELECT message.* FROM message WHERE uid>1000 LIMIT 1000
    SELECT message.* FROM message WHERE uid>2000 LIMIT 1000
    ……
    SELECT message.* FROM message WHERE uid>998000 LIMIT 1000
    SELECT 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_timerec
    SELECT null,timerec FROM message
    WHERE 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

最后,附上MySQL性能优化系列的全部链接:

*  [\[MySQL性能优化系列\]巧用索引][MySQL] [http://www.cnblogs.com/beynol/p/mysql-optimization-index.html][MySQL]
 *  [\[MySQL性能优化系列\]LIMIT语句优化][MySQL_LIMIT 1] [http://www.cnblogs.com/beynol/p/mysql-optimization-limit.html][MySQL_LIMIT 1]
 *  [\[MySQL性能优化系列\]提高缓存命中率][MySQL 1] [http://www.cnblogs.com/beynol/p/mysql-optimization-cache.html][MySQL 1]

[MySQL_LIMIT]: https://www.cnblogs.com/beynol/p/mysql-optimization-limit.html
[MySQL]: http://www.cnblogs.com/beynol/p/mysql-optimization-index.html
[MySQL_LIMIT 1]: http://www.cnblogs.com/beynol/p/mysql-optimization-limit.html
[MySQL 1]: http://www.cnblogs.com/beynol/p/mysql-optimization-cache.html