Red Dragonfly

MySQL 千万级 limit 优化方案

 
  • 2021年 10月16日

MySQL 的分页查询通常通过limit来实现。

MySQL 的 limit 基本用法很简单。limit 接收 1 或 2 个整数型参数，如果是 2个 参数，第一个是指定第一个返回记录行的偏移量，第二个是返回记录行的最大数目。初始记录行的偏移量是0。

为了与PostgreSQL兼容，limit也支持 limit # offset #。

方案

1. 取出每次最大的 ID, 然后 where 这个 ID

-- 正序
select * from table_name where id > 最大id limit 10， 10；

-- 倒序未优化写法：
select id from t order by id desc limit 8000000,10;

-- 倒序优化写法：
select * from table_name where id <= (
    (select max(id) from t) - 8000000) order by id desc limit 10;

这种方法适用于：除了主键ID等离散型字段外，也适用连续型字段 datetime 等

最大 ID 由前端分页 pageNum 和 pageIndex 计算出来。

2. IN获取id

select * from table_name where id in (
    select id from table_name where ( user = xxx )) limit 10, 10;

3. join方式 + 覆盖索引（推荐）

select * from table_name inner join (
    select id from table_name where (user = xxx) limit 10000,10) b using (id)

如果对于有where 条件，又想走索引用limit的，必须设计一个索引，将where 放第一位，limit用到的主键放第2位，而且只能select 主键！

select id from table_name where pid = 1 limit 100000,10;
-- 创建索引：
alter table table_name add index idx_pid_id(pid, id)

-- 传统limit，文件扫描 [SQL]
SELECT * FROM tableName ORDER BY id LIMIT 500000,2; 
受影响的行: 0 时间: 5.371s 
-- 子查询方式，索引扫描 [SQL] 
SELECT * FROM tableName WHERE id >= (
  SELECT id FROM tableName ORDER BY id LIMIT 500000 , 1) LIMIT 2; 
受影响的行: 0 时间: 0.274s 
-- JOIN分页方式 [SQL] 
SELECT * FROM tableName AS t1 JOIN (
  SELECT id FROM tableName ORDER BY id desc LIMIT 500000, 1) AS t2 
  WHERE t1.id <= t2.id 
  ORDER BY t1.id 
  desc LIMIT 2; 
受影响的行: 0 时间: 0.278s

just so so .

参考

EOF

搜索