怎么提高MySQL Limit查询性能的方法

64次阅读

共计 2226 个字符，预计需要花费 6 分钟才能阅读完成。

自动写代码机器人，免费开通

这篇文章给大家分享的是有关怎么提高 MySQL Limit 查询性能的方法的内容。丸趣 TV 小编觉得挺实用的，因此分享给大家做个参考，一起跟随丸趣 TV 小编过来看看吧。

在 MySQL 数据库操作中，我们在做一些查询的时候总希望能避免数据库引擎做全表扫描，因为全表扫描时间长，而且其中大部分扫描对客户端而言是没有意义的。其实我们可以使用 Limit 关键字来避免全表扫描的情况，从而提高效率。

有个几千万条记录的表 on MySQL 5.0.x，现在要读出其中几十万万条左右的记录。常用方法，依次循环：

select * from mytable where index_col = xxx limit offset, limit;

经验：如果没有 blob/text 字段，单行记录比较小，可以把 limit 设大点，会加快速度。

问题：头几万条读取很快，但是速度呈线性下降，同时 mysql server cpu 99%，速度不可接受。

调用 explain select * from mytable where index_col = xxx limit offset, limit; 显示 type = ALL

在 MySQL optimization 的文档写到 All 的解释

A full table scan is done for each combination of rows from the previous tables. This is normally not good if the table is the first table not marked const, and usually very bad in all other cases. Normally, you can avoid ALL by adding indexes that allow row retrieval from the table based on constant values or column values from earlier tables.

看样子对于 all, mysql 就使用比较笨的方法，那就改用 range 方式？因为 id 是递增的，也很好修改 sql。

select * from mytable where id   offset and id   offset + limit and index_col = xxx

explain 显示 type = range，结果速度非常理想，返回结果快了几十倍。

Limit 语法：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。

如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)。

为了与 PostgreSQL 兼容，MySQL 也支持句法：LIMIT # OFFSET #。

mysql  SELECT * FROM table LIMIT 5,10; // 检索记录行 6 -15
// 为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1
mysql  SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last
// 如果只给定一个参数，它表示返回最大的记录行数目，换句话说，LIMIT n  等价于  LIMIT 0,n
mysql  SELECT * FROM table LIMIT 5; // 检索前 5 个记录行

MySQL 的 limit 给分页带来了极大的方便，但数据量一大的时候，limit 的性能就急剧下降。同样是取 10 条数据，下面两句就不是一个数量级别的。

select * from table limit 10000,10
select * from table limit 0,10

文中不是直接使用 limit，而是首先获取到 offset 的 id 然后直接使用 limit size 来获取数据。根据他的数据，明显要好于直接使用 limit。

这里我具体使用数据分两种情况进行测试。

1、offset 比较小的时候：

select * from table limit 10,10 
// 多次运行，时间保持在 0.0004-0.0005 之间
Select * From table Where vid >=(Select vid From table Order By vid limit 10,1) limit 10 
// 多次运行，时间保持在 0.0005-0.0006 之间，主要是 0.0006

结论：偏移 offset 较小的时候，直接使用 limit 较优。这个显然是子查询的原因。

2、offset 大的时候：

select * from table limit 10000,10 
// 多次运行，时间保持在 0.0187 左右
Select * From table Where vid >=(Select vid From table Order By vid limit 10000,1) limit 10
// 多次运行，时间保持在 0.0061 左右，只有前者的 1 /3。可以预计 offset 越大，后者越优。

感谢各位的阅读！关于“怎么提高 MySQL Limit 查询性能的方法”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！

向 AI 问一下细节

正文完