Mysql数据库深分页查询优化

/*
 Navicat Premium Data Transfer

 Source Server         : 192.168.19.105mariadb
 Source Server Type    : MariaDB
 Source Server Version : 100605
 Source Host           : 192.168.19.105:3306
 Source Schema         : daixk

 Target Server Type    : MariaDB
 Target Server Version : 100605
 File Encoding         : 65001

 Date: 02/03/2025 00:49:17
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for gf_file
-- ----------------------------
DROP TABLE IF EXISTS `gf_file`;
CREATE TABLE `gf_file`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `file_old_name` varchar(3000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '原文件名',
  `file_name` varchar(3000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '新文件名',
  `file_type` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '文件类型 后缀',
  `file_size` int(6) NOT NULL DEFAULT 1 COMMENT '文件大小 M',
  `file_location` varchar(3000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '文件存储位置',
  `file_url` varchar(3000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '文件url',
  `file_head` smallint(1) NOT NULL DEFAULT 0 COMMENT '是否为首要文件 0否 1是 默认0',
  `md5` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '文件md5值',
  `create_user` varchar(300) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '创建用户标识',
  `create_time` datetime NOT NULL DEFAULT current_timestamp() COMMENT '创建时间',
  `is_delete` smallint(1) NOT NULL DEFAULT 0 COMMENT '是否删除 物理删除',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_file_size`(`file_size`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 122239 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '文件存储表' ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

耗时深分页sql

SELECT SQL_NO_CACHE * FROM gf_file where file_size >= 100 and file_size <= 600 limit 20000,5;

采用覆盖索引优化(需要有联合索引)

select SQL_NO_CACHE id,file_name from gf_file where file_size >= 100 and file_size <= 600 limit 20000,5;

延迟关联法

SELECT SQL_NO_CACHE * FROM ( SELECT id FROM gf_file where file_size >= 100 and file_size <= 600 limit 20000,5 ) as t INNER JOIN gf_file g ON g.id = t.id;

标签记录法（有些许问题 需要使用id排序）

select SQL_NO_CACHE * from gf_file where file_size >= 100 and file_size <= 600 and id > 20000 limit 0,5
select * from test_big_data where name like 'itlgitlg%' and id > 800000 order by id asc limit 0,10

其他示例

SELECT * FROM orders 
WHERE id >= (
    SELECT id FROM orders 
    ORDER BY create_time DESC 
    LIMIT 19980, 1
)
ORDER BY create_time DESC
LIMIT 20;

-- 第一页
SELECT * FROM orders 
ORDER BY create_time DESC, id DESC 
LIMIT 20;

-- 后续页（记住上一页最后一条的create_time和id）
SELECT * FROM orders 
WHERE create_time < '2025-02-15 00:04:45' 
OR (create_time = '2025-02-15 00:04:45' AND id < 9999981)
ORDER BY create_time DESC, id DESC 
LIMIT 20;

SELECT * FROM orders 
INNER JOIN (
    SELECT id FROM orders 
    ORDER BY create_time DESC 
    LIMIT 19980, 20
) AS tmp USING(id);