乐趣区

MySQL分页优化实验与总结

jiezi

前言

分页的 sql 优化是日常开发中经常遇到的问题，笔者在此做一个经验总结，并附上相应的实验过程。

实验准备

若不想亲自实验的，可以直接跳过这一节。但还是建议大家做一下实验，眼见为实。

1. 安装测试数据库

本次实验使用的数据是 mysql 官方提供的 employee 数据库，mysql 官方提供了一些测试数据库，可以在这里找到 https://dev.mysql.com/doc/ind…。

2. 修改测试数据库

安装好 employee 数据库后，笔者出于测试修改了一下 salaries 表的结构，方便测试，修改操作如下：

// 修改原表的主键为 id
CREATE TABLE `test_salaries` (`id` int(11) NOT NULL AUTO_INCREMENT,
    `emp_no` int(11) NOT NULL,
    `salary` int(11) NOT NULL,
    `from_date` date NOT NULL,
    `to_date` date NOT NULL,
    PRIMARY KEY (`id`),
    CONSTRAINT `test_salaries_ibfk_1` FOREIGN KEY (`emp_no`) REFERENCES `employees` (`emp_no`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
        
// 导入原表数据
INSERT INTO test_salaries (id,emp_no,salary,from_date,to_date) SELECT NULL,emp_no,salary,from_date,to_date FROM salaries;

3. 完成测试环境

至此，实验的准备工作完成。可先查看一下 test_salaries 表中有多少数据（以下测试基于该表）

SELECT count(*)FROM test_salaries;

优化分页 SQL 查询

优化分页 SQL 查询的思路：

  1. 尽可能使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列（延迟关联）
  2. 将 limit 查询转换为已知位置的查询，让 mysql 通过范围扫描获得对应的结果（范围扫描）

延迟关联

原始 sql 查询语句：

SELECT * FROM test_salaries WHERE salary <= 94000 LIMIT 2677500,10;

原始 sql 查询语句执行效果：

只查询 id 的 sql 语句：

SELECT id FROM test_salaries WHERE salary <= 94000 LIMIT 2677500,10;

只查询 id 的 sql 语句执行效果：

优化后的 sql 语句：

SELECT * FROM test_salaries INNER JOIN (SELECT id FROM test_salaries WHERE salary <= 94000 LIMIT 2677500,10) AS lim USING(id);

优化后的 sql 语句执行效果：

并且我们可以注意到，这条语句的执行时间与上一条只查询 id 的语句的执行时间非常接近。

范围扫描

原始 sql 查询语句：

SELECT * FROM test_salaries limit 2844030,10;

原始 sql 查询语句执行效果：

只查询 id 的 sql 语句：

SELECT id FROM test_salaries ORDER BY id limit 2844030,1;

只查询 id 的 sql 语句执行效果：

优化后的 sql 语句：

SELECT * FROM test_salaries WHERE id>=(SELECT id FROM test_salaries ORDER BY id limit 2844030,1) limit 0,10;

优化后的 sql 语句执行效果：

同样的，我们可以发现后两句 sql 的执行时间比较接近。

应用程序层面的分页优化设计

除了对 sql 语句进行优化，我们还可以在应用程序层面对分页进行一些优化设计。

  1. 将具体的页数换成“下一页”按钮，假设每页显示 20 条记录，那么每次查询时都是用 LIMIT 返回 21 条记录并只显示 20 条，如果第 21 条存在，那么就显示“下一页”按钮。
  2. 先获取并缓存较多的数据（例如 1000 条），然后每次分页都从缓存中获取。这样做可以让应用程序根据结果集的大小采取不同策略，如果结果集少于 1000，就可以在页面上显示所有的分页连接；如果结果集大于 1000，则可以在页面上设计一个额外的“找到的结果多于 1000 条”之类的按钮。
退出移动版