Written by honors

分页查询优化

分页语句

limit ,
  • offset: 返回结果第一行的偏移量（想要跳过多少行）
  • size: 指定返回多少条

示例

查询employees中第一页的数据，每页的数据量为10条数据

SELECT * FROM employees LIMIT 0,10

file

想要查询其它页，只需要将offset设置为对应size的倍数即可，但是LIMIT有个问题，就是在offset非常大的时候，查询会非常慢

示例

查询employees中第30001页的数据，每页的数据量为10条数据

SELECT * FROM employees LIMIT 300000,10

file

如果查询的offset设置的更大，那么这个时间就会更长。

EXPLAIN 排查

EXPLAIN SELECT * FROM employees LIMIT 300000,10

file
可以看到，查询的type为All,扫描的行数是298981，这里设置的offset越大，rows就会越大，针对offset非常大的情况，就需要做一些优化

方案1：覆盖索引

覆盖所以可以大幅度减少查询的时间，修改sql为

SELECT emp_no FROM employees LIMIT 300000,10

file

EXPLAIN 排查

EXPLAIN SELECT emp_no FROM employees LIMIT 300000,10

file
type变为了index,发生了全索引扫描，全索引扫描相比全表扫描还是要快的

如果我们确实需要返回全部的字段，可以使用覆盖索引+join或覆盖索引+子查询的方式

方案2：覆盖索引+join

示例

SELECT
    * 
FROM
    employees a
    INNER JOIN ( SELECT emp_no FROM employees LIMIT 300000, 10 ) b ON a.emp_no = b.emp_no
# 因为On的字段都是emp_no，所以也可以简写成using(emp_no),结果是一样的

SELECT
    * 
FROM
    employees a
    INNER JOIN ( SELECT emp_no FROM employees LIMIT 300000, 10 ) b USING(emp_no)

file

方案3：覆盖索引+子查询

示例


    SELECT
    * 
FROM
    employees 
    WHERE emp_no >=( SELECT emp_no FROM employees LIMIT 300000, 1) LIMIT 10

file

方案4：范围查询+limit语句

如果我们能拿到查询条件上一页的主键的最大值，那么sql就会很简单
示例
从一开始的sql中，我们可以查到，第一页最大id为10010，那么查询第二页的sql就变成了

SELECT
    * 
FROM
    employees 
WHERE
    emp_no > 10010 
    LIMIT 10

如果要查询第三页，只需要将emp_no修改即可，依此类推，这种方案的好处是，不管查询多少页，每次扫描的行数都是10，但是使用的前提是，需要拿到上一页的主键最大值，否则这种方案就没法实施。

方案5：起始主键+结束主键

如果我们能拿到查询条件的起始主键值和结束主键值，那么sql就可以变成

SELECT
    * 
FROM
    employees 
WHERE
    emp_no BETWEEN 20000 and 20010

这种情况下，limit语句都不需要，直接变成了范围查询

方案6：禁止传入过大的页码

示例
在百度搜索任意字符
file

然后点击下一页，查看浏览器的地址
file

然后点击第三页，查看浏览器的地址
file

可以发现，地址中多了pn,可以判断出，pn就是页码，于是，我们将pn改为990，即查询第100页的数据
file
回到了第一页，然后我们输入750
file
成功了，而且可以看到百度最大支持到76页。对于一般来说，很少有人会搜索到700多条还没有找到自己想要的数据，76的结果是完全够用的。

发表回复