MySQL千万数据优化分页查询
操作环境:
- MySQL: 5.7.26
- Navicat for MySQL
目的: MySQL千万级数据的优化查询
1. 创建1000w数据
1.1 建表
建表SQL语句
CREATE TABLE `big_data` (
`id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
`name` varchar(16) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX idx_name(`name`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
big_data表一共三个字段id(主键),name(创建索引),age,存储引擎使用的INNODB,INNODB有一个注意的地方:INNODB引擎数据量超过2000W,读写性能会有很大下降。
1.2 插入1000W条数据
向MySQL中插入数据我能想到的只有2种方式;
- 使用编程语言连接MySQL然后插入1000W数据
- 使用MySQL存储过程
为了方便我使用存储过程:
创建存储过程
CREATE PROCEDURE `insert_data`(IN num INT)
BEGIN
DECLARE n INT DEFAULT 1;
WHILE n <= num DO
INSERT INTO big_data(name,age,email)values(concat('alex',n),rand()*50);
set n=n+1;
end while;
ENd;
执行存储过程插入1000W数据
CALL insert_data(10000000);
我使用Navicat这个MySQL可视化工具,插入1000W数据比较耗时，这里说一下如何通过Navicat工具查看执行时间与插入了多少条数据。
1.png
1.png
插入数据耗时很长，我没记准确时间，但是我通过了一波死神VS火影3.3.
2. 分页查询
2.1 普通分页查询
| 编号 | SQL语句 | 耗时(s) |
|---|---|---|
| 1 | select * from big_data limit 3000000,10; |
1.381 |
| 2 | select id from big_data limit 3000000,10; |
1.107 |
| 3 | SELECT * from big_data LIMIT 9000000,10; |
4.764 |
上面5条SQL我们逐一对比：
-
1号与2号对比: 1号2号都是用limit a,b 这种方式，MySQL执行时候会进行全表扫描，从开头扫描，直到扫到300W，然后取后面10条数据。2号耗时比1号长是因为，2号只取了id，而1号还要取其他所有的字段。两者时间差为274ms，虽然减少了，但是相差页不是很明显。
-
2号与3号对比: 上面介绍了对于limit a,b 这种形式会根据主键id进行全表扫描，从1一直查到a，a值越大，耗时越久。
2.2 主键连续查询(WHERE优化分页查询)
| 编号 | SQL语句 | 耗时(s) |
|---|---|---|
| 1 | SELECT * FROM big_data WHERE id>=9000000 LIMIT 10; |
0.226 |
| 2 | SELECT * FROM big_data WHERE id>=9000000 AND id<=9000000+10; |
0.119 |
1号SQL通过WHERE可以通过主键索引快速定位到第900W条记录处，然后取10条记录(注意这里包括第900W条记录，与 limit a,b不一样,limit 不包括第a条记录)。2号和1号相同。与普通查询相比快了很多。
但是这种WHERE分页查询只能够在主键id连续的情况下使用，如果主键id不是连续，那么它的性能会大大下降(这个复现，我手工试了以下把第900W条记录删除，在查询速度没有减慢，再把第900W条记录后面的10条删除了，在查询速度还是没有减慢，复现不出来，此处暂时搁着)。
// TODO id不连续使用WHERE查询
2.3 主键不连续查询(关联查询)
在2.1普通分页查询种，只查询id的速度也是可以接收，所以我们可以先把id查询出来，然后再根据id查询数据记录。
| 编号 | SQL语句 | 耗时(s) |
|---|---|---|
| 1 | select id from big_data limit 3000000,10; |
1.127 |
| 2 | SELECT t.* FROM big_data t JOIN ( SELECT id FROM big_data LIMIT 3000000,10 ) tmp ON t.id = tmp.id; |
1.209 |
| 3 | SELECT t.* FROM big_data t JOIN ( SELECT id FROM big_data LIMIT 9000000,10 ) tmp ON t.id = tmp.id; |
3.554 |
SQL 解析：子查询( SELECT id FROM big_data LIMIT 9000000,10 ) tmp用来查找分页数据id，将结果集保存到tmp临时表种，再通过JOIN ON 连接查询记录。可以看到还是有一定优化效果，但是如果分页查询的数据靠后的话(如3号第900W条开始)，查询需要的时间也不是很理想。所以对于查询靠后的数据通常再业务处理，用户只能查询前面的数据。
比如百度搜索，只显示前面的76页数据。
1.png
