MySQL系列-延迟关联优化多分页查询

利用延迟关联或者子查询优化超多分页的场景

首先创建测试表test,然后分配两个字段,id是逻辑自增主键,然后一个name的varchar字符串

1
2
3
4
5
6
7
8
9
DROP DATABASE IF EXISTS test;
CREATE DATABASE test;
CREATE TABLE test.test (
id INT (10) NOT NULL AUTO_INCREMENT,
username VARCHAR (10) NOT NULL,
content VARCHAR (10) NOT NULL,
description VARCHAR (10) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE INNODB DEFAULT CHARSET utf8 COMMENT '测试表';

然后我们往表中插入1W条测试数据,这里利用MySQL的存储过程来做

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
TRUNCATE TABLE test.test;

# 定义存储过程
delimiter $$
DROP PROCEDURE IF EXISTS insert_test_val;
CREATE PROCEDURE insert_test_val()
BEGIN

DECLARE i INT DEFAULT 1;
WHILE i <= 10000 DO
INSERT INTO test.test VALUES (NULL, "test", "x", "x");
SET i = i + 1;
END WHILE;

END
$$

# 调用存储过程
CALL insert_test_val();

进入第一步创建的test数据库执行上面的脚本就行了

时间: 35.632s

存储过程插入挺慢呀,我插入1W行用了35秒

然后先看下面原始的limit语句执行分析

EXPLAIN SELECT * FROM test LIMIT 9990,10;

然后使用延迟关联。先快速定位需要获取的id段,然后再自关联或join关联

EXPLAIN SELECT * FROM test a, (SELECT id FROM test LIMIT 9990,10) b WHERE a.id=b.id;

可以看到使用了覆盖索引,不是全表扫描了

MySQL并不是跳过offset行,而是取offset+N行,然后返回放弃前offset行。返回N行。那当offset特别大的时候,效率就非常的低下,要么控制返回的总页数,要么对超过特定阈值的页数进行SQL改写。

文章作者: Eric Qin
文章链接: https://www.qugcloud.cn/2019/02/13/mysql-article-01/
版权声明: 本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 Eric's Blog