MySQL分页优化实验与总结

哈喽！今天心血来潮给大家带来了《MySQL分页优化实验与总结》，想必大家应该对数据库都不陌生吧，那么阅读本文就都不会很困难，以下内容主要涉及到MySQL、分页查询、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 

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

只查询id的sql语句：

SELECT id FROM test_salaries WHERE salary 

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

优化后的sql语句：

SELECT * FROM test_salaries INNER JOIN (SELECT id FROM test_salaries WHERE salary 

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

范围扫描

原始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条”之类的按钮。

文中关于mysql的知识介绍，希望对你的学习有所帮助！若是受益匪浅，那就动动鼠标收藏这篇《MySQL分页优化实验与总结》文章吧，也可关注golang学习网公众号了解相关技术文章。