如何提升MySQL分页查询的效率?

一、前言

1、本文采用MySQL 5.7.30
2、相关表结构为：

CREATE TABLE `orders` (
	`id` INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键',
	`order_id` VARCHAR ( 32 ) NOT NULL COMMENT '订单ID',
	`user_id` VARCHAR ( 32 ) NOT NULL COMMENT '用户ID',
	`address_id` VARCHAR ( 32 ) NOT NULL COMMENT '地址ID',
	`user_name` VARCHAR ( 64 ) NOT NULL COMMENT '客户姓名',
	`order_date` DATE NOT NULL COMMENT '订单日期',
	`total_amount` DECIMAL ( 10, 2 ) NOT NULL COMMENT '订单总额',
	`payment_method` VARCHAR ( 50 ) COMMENT '支付方式',
	`payment_status` ENUM ( 'paid', 'unpaid', 'refunded' ) NOT NULL COMMENT '支付状态',
	`order_status` ENUM ( 'pending', 'shipped', 'delivered', 'cancelled' ) NOT NULL COMMENT '订单状态',
	`shipping_company` VARCHAR ( 100 ) COMMENT '物流公司',
	`shipping_number` VARCHAR ( 100 ) COMMENT '物流单号',
	`shipping_address` TEXT COMMENT '收货地址',
	`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
	`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
INDEX `idx_orders_orderid` ( `order_id` ) COMMENT '订单号索引' 
) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '订单表';

3、使用到的造数存储过程为（使用存储过程前，请先保存好未完成的任务！！！）：

DROP PROCEDURE IF EXISTS InsertOrders;
DELIMITER $$
CREATE PROCEDURE InsertOrders()
BEGIN
  DECLARE i INT DEFAULT 1;
  DECLARE max INT DEFAULT 1000000; -- 100万
  DECLARE order_id VARCHAR(32);
  DECLARE user_id VARCHAR(32);
  DECLARE address_id VARCHAR(32);
  DECLARE user_name VARCHAR(64);
  DECLARE order_date DATE;
  DECLARE total_amount DECIMAL(10, 2);
  DECLARE payment_method VARCHAR(50);
  DECLARE payment_status ENUM('paid', 'unpaid', 'refunded');
  DECLARE order_status ENUM('pending', 'shipped', 'delivered', 'cancelled');
  DECLARE shipping_company VARCHAR(100);
  DECLARE shipping_number VARCHAR(100);
  DECLARE shipping_address TEXT;

  WHILE i <= max DO
    -- 生成随机订单ID
    SET order_id = CONCAT(DATE_FORMAT(NOW(), '%Y%m%d%H%i%s'), '_', FLOOR(1 + (RAND() * 100000)));
    -- 生成随机用户ID
    SET user_id = CONCAT('user_', FLOOR(1 + (RAND() * 100000)));
    -- 生成随机地址ID
    SET address_id = CONCAT('address_', FLOOR(1 + (RAND() * 100000)));
    -- 生成随机用户姓名
    SET user_name = CONCAT('Customer_', FLOOR(1 + (RAND() * 1000)));
    -- 生成随机订单日期
    SET order_date = DATE_ADD(CURDATE(), INTERVAL FLOOR(-1 + (RAND() * 5)) DAY);
    -- 生成随机订单总额
    SET total_amount = ROUND(100 + (RAND() * 1000), 2);
    -- 生成随机支付方式
    SET payment_method = CASE FLOOR(1 + (RAND() * 3))
                           WHEN 1 THEN 'Credit Card'
                           WHEN 2 THEN 'PayPal'
                           WHEN 3 THEN 'Bank Transfer'
END;
    -- 生成随机支付状态
    SET payment_status = CASE FLOOR(1 + (RAND() * 3))
                           WHEN 1 THEN 'paid'
                           WHEN 2 THEN 'unpaid'
                           WHEN 3 THEN 'refunded'
END;
    -- 生成随机订单状态
    SET order_status = CASE FLOOR(1 + (RAND() * 4))
                         WHEN 1 THEN 'pending'
                         WHEN 2 THEN 'shipped'
                         WHEN 3 THEN 'delivered'
                         WHEN 4 THEN 'cancelled'
END;
    -- 生成随机物流公司
    SET shipping_company = CASE FLOOR(1 + (RAND() * 3))
                            WHEN 1 THEN 'FedEx'
                            WHEN 2 THEN 'UPS'
                            WHEN 3 THEN 'DHL'
END;
    -- 生成随机物流单号
    SET shipping_number = CONCAT('SN', FLOOR(1 + (RAND() * 100000)));
    -- 生成随机收货地址
    SET shipping_address = CONCAT('Address Line 1, Address Line 2, City, State, Zip Code', FLOOR(1 + (RAND() * 100)));

    -- 批量插入数据
INSERT INTO orders (order_id, user_id, address_id, user_name, order_date, total_amount, payment_method, payment_status, order_status, shipping_company, shipping_number, shipping_address)
VALUES (order_id, user_id, address_id, user_name, order_date, total_amount, payment_method, payment_status, order_status, shipping_company, shipping_number, shipping_address);

SET i = i + 1;
END WHILE;
END$$

DELIMITER ;
CALL InsertOrders();

3、需求：分页查询指定日期的订单数据。

二、基本实现

分页查询肯定免不了使用limit关键字了，limit 100 offset 10000表示偏移量为10000，返回结果集中的100条数据。
现在我们来查询订单日期在2024-06-01至2024-07-30之间的订单数据，偏移量为10000，返回100条数据。

SELECT
	* 
FROM
	orders
WHERE
	order_date BETWEEN '2024-06-01' AND '2024-07-30' order by order_date LIMIT 100 OFFSET 10000

我们来看一下数据分布：

再查询区间的数据共计：

运行基本查询

怎么优化呢？第一反应肯定是给order_date增加索引呀，我们来试一试。

create index index_orderdate on orders(order_date);

查询结果：

没错，使用了索引，这个查询的效率变成了567毫秒，这个效率虽然差，但还是能够接受的，但是索引就真的完全有效吗？

三、如何对索引再次优化？

我们观察到MySQL对于非聚集索引，当选取的行数不能满足要求，会根据非聚集索引上保存的id，进行回表，然后执行跳过操作。那我们可以根据非聚集索引先查询出id，然后去主表根据id查询，避免MySQL逐条回表。
这种方式有很多种写法，我们采用join方式写。

SELECT
	* 
FROM
	orders
RIGHT JOIN ( 
	SELECT
		id 
	FROM
		orders 
	WHERE
		order_date BETWEEN '2024-06-01' 
		AND '2024-07-30' 
	ORDER BY
		order_date 
		LIMIT 100 OFFSET 80000
 ) AS t1 ON t1.id = orders.id

我们可以看到使用这个查询用时仅仅36毫秒

之前耗时5秒的SQL，现在耗时390毫秒

四、其他的优化思路

除此以外，还有一些其他的优化思路大家可以参考一下。
1、游标：

SELECT
    *
FROM
    orders
WHERE
    id > [上一批查询的最后一个id]
    AND order_date BETWEEN '2024-05-01' AND '2024-07-30'
ORDER BY
    id
LIMIT 100;

2、覆盖索引：
如果能够不进行回表当然最好的，从非聚集索引中获取到查询的所有数据，但是生产上大部分场景都不太可能使用覆盖索引就能够满足需求。
3、利用id，结合使用exist或者in关键字