共计 1785 个字符,预计需要花费 5 分钟才能阅读完成。
前言
分页的 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 查询的思路:
- 尽可能使用索引覆盖扫描,而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列(延迟关联)
- 将 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 语句进行优化,我们还可以在应用程序层面对分页进行一些优化设计。
- 将具体的页数换成“下一页”按钮,假设每页显示 20 条记录,那么每次查询时都是用 LIMIT 返回 21 条记录并只显示 20 条,如果第 21 条存在,那么就显示“下一页”按钮。
- 先获取并缓存较多的数据(例如 1000 条),然后每次分页都从缓存中获取。这样做可以让应用程序根据结果集的大小采取不同策略,如果结果集少于 1000,就可以在页面上显示所有的分页连接;如果结果集大于 1000,则可以在页面上设计一个额外的“找到的结果多于 1000 条”之类的按钮。
正文完