MySQL使用Limit分页查询的性能优化

电玩女神 2021-09-17 14:10 514阅读 0赞

当数据表中的记录越来越多的时候，可以通过分页查询的方法，对数据进行筛选，仅返回当前页面所需的数据内容。Limit是MySQL中的一个特殊关键字。Limit子句可以对查询结果的记录条数进行限定，控制它输出的行数。

在MySQL数据库中创建用户信息表（tb\_user），并添加数据用于测试使用。

-- 判断数据表是否存在，存在则删除
    DROP TABLE IF EXISTS tb_user;
     
    -- 创建“用户信息”数据表
    CREATE TABLE IF NOT EXISTS tb_user
    ( 
    	user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号',
    	user_name VARCHAR(50) NOT NULL COMMENT '用户姓名',
    	province VARCHAR(50) NOT NULL COMMENT '省份'
    ) COMMENT = '用户信息表';
     
    -- 添加数据
    INSERT INTO tb_user(user_name,province) VALUES
    ('pan_junbiao的博客_01','广东省'),('pan_junbiao的博客_02','黑龙江省'),('pan_junbiao的博客_03','山东省'),('pan_junbiao的博客_04','安徽省'),('pan_junbiao的博客_05','黑龙江省'),
    ('pan_junbiao的博客_06','江苏省'),('pan_junbiao的博客_07','黑龙江省'),('pan_junbiao的博客_08','广东省'),('pan_junbiao的博客_09','陕西省'),('pan_junbiao的博客_10','广东省'),
    ('pan_junbiao的博客_11','广东省'),('pan_junbiao的博客_12','江苏省'),('pan_junbiao的博客_13','陕西省'),('pan_junbiao的博客_14','安徽省'),('pan_junbiao的博客_15','山东省'),
    ('pan_junbiao的博客_16','陕西省'),('pan_junbiao的博客_17','安徽省'),('pan_junbiao的博客_18','江苏省'),('pan_junbiao的博客_19','黑龙江省'),('pan_junbiao的博客_20','安徽省'),
    ('pan_junbiao的博客_21','江苏省'),('pan_junbiao的博客_22','广东省'),('pan_junbiao的博客_23','安徽省'),('pan_junbiao的博客_24','陕西省'),('pan_junbiao的博客_25','广东省'),
    ('pan_junbiao的博客_26','广东省'),('pan_junbiao的博客_27','安徽省'),('pan_junbiao的博客_28','山东省'),('pan_junbiao的博客_29','山东省'),('pan_junbiao的博客_30','黑龙江省'),
    ('pan_junbiao的博客_31','广东省'),('pan_junbiao的博客_32','江苏省'),('pan_junbiao的博客_33','陕西省'),('pan_junbiao的博客_34','安徽省'),('pan_junbiao的博客_35','山东省');

**基本语法格式：**

LIMIT m;

m：表示查询多少条记录。

**【示例】**查询用户信息表（tb\_user），按照 user\_id 编号进行升序排列，显示前5条记录。

SELECT * FROM tb_user
    ORDER BY user_id
    LIMIT 5

**执行结果：**

![20200509173805904.png][]

# 1、使用一般分页查询（**不推荐**） #

**语法格式：**

LIMIT m , n;

m：表示开始查询的第一条记录的编号（注意：在查询结果中，第一个结果的记录编号是0，而不是1）。

n：表示查询多少条记录。

DELIMITER $$
    
    -- 方式一：使用一般分页查询（不推荐）
    DROP PROCEDURE IF EXISTS proc_user_page$$
    CREATE PROCEDURE proc_user_page(IN page_index INT,IN page_size INT)
    BEGIN
    	DECLARE begin_no INT;
    	SET begin_no = (page_index-1)*page_size;
    
    	SELECT * FROM tb_user
    	ORDER BY user_id ASC
    	LIMIT begin_no,page_size;
    END$$
    
    DELIMITER ;

调用存储过程：查询第2页，每页5条记录：

-- 调用存储过程：查询第2页，每页5条记录
    CALL proc_user_page(2,5);

**执行结果：**

![2020050917274492.png][]

> **弊端：**方式一虽然实现了分页功能，但随着查询偏移的增大，尤其查询偏移大于10万以后，查询时间将急剧增加。这种分页查询方式会从数据库第一条记录开始扫描，所以越往后，查询速度越慢，而且查询的数据越多，也会拖慢总查询速度。

# 2、使用子句优化查询（推荐） #

DELIMITER $$
    
    -- 方式二：使用子句优化查询（推荐）
    DROP PROCEDURE IF EXISTS proc_optimize$$
    CREATE PROCEDURE proc_optimize(IN page_index INT,IN page_size INT)
    BEGIN
    	DECLARE begin_no INT;
    	SET begin_no = (page_index-1)*page_size;
    
    	SELECT * FROM tb_user
    	WHERE user_id >= (
    		SELECT user_id FROM tb_user
    		ORDER BY user_id ASC
    		LIMIT begin_no,1
    	)
    	ORDER BY user_id ASC
    	LIMIT page_size;
    END$$
    
    DELIMITER ;

**调用存储过程：**查询第3页，每页5条记录。

-- 调用存储过程：查询第3页，每页5条记录
    CALL proc_optimize(3,5);

**执行结果：**

![20200509173136461.png][]

> **优点：**方式二适合数据表的id是**连续递增**的，则可以根据查询的页数和查询的记录数可以算出查询的id的范围。这种查询方式能够极大地优化查询速度，基本能够在几十毫秒之内完成。限制是只能使用于明确知道id的情况，不过一般建立表的时候，都会添加基本的自增的主键id字段，这为分页查询带来很多便利。

[20200509173805904.png]: /images/20210724/16dbee02c23d4400badd2fcc5281bc48.png
[2020050917274492.png]: /images/20210724/30b53a77b1c446cdb5435964f639ddc5.png
[20200509173136461.png]: /images/20210724/9533ae32a3dc4268b72ff3619bb6f06a.png