mysql深分页强制使用主键索引时反而更快

准备工作

先准备1KW条数据，我也是百度搜索 批量生成1KW数据的方式。有做轻微改动。

#创建随机字符串函数，便于创建名称
DROP function if EXISTS rand_string;
#创建一个指定字符个数的函数
create function rand_string(n INT)
#返回字符串，注意：此处关键字是returns 而不是return
returns varchar(255)
BEGIN
    #定义一个临时变量，给变量赋值'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'
    DECLARE chars_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
    # 定义返回结果字符串
    DECLARE return_str varchar(255) DEFAULT '';
    DECLARE i int DEFAULT 0;
    WHILE i < n DO
        set return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
        set i=i+1;
    END while;
    RETURN return_str;
END
 
#创建随机编号生成函数
drop function if exists rand_num;
CREATE function rand_num()
returns int(5)
BEGIN
	DECLARE i int default 0;
	set i = FLOOR(10+RAND()*500);
	return i;
END
 
#创建数据表 -- 员工表 emp
drop table if EXISTS emp1;
CREATE TABLE `emp1` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT,
  `empno` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `ename` varchar(20) NOT NULL DEFAULT '',
  `job` varchar(9) NOT NULL DEFAULT '',
  `mgr` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '上级编号',
  `hiredate` datetime NOT NULL COMMENT '入职日期',
  `salary` decimal(7,2) NOT NULL COMMENT '薪水',
  `comm` decimal(7,2) NOT NULL COMMENT '红利',
  `deptno` mediumint(8) unsigned NOT NULL DEFAULT '0' COMMENT '部门编号',
  PRIMARY KEY (`id`),
  KEY `inx_salary` (`salary`)
) ENGINE=InnoDB
 
#插入测试数据
drop PROCEDURE IF EXISTS insert_emp1 ;
CREATE PROCEDURE insert_emp1 (in start_no int(10),in max_num int(10)) 
BEGIN
	DECLARE i int default 0;
	# 设置自动提交为false
	set autocommit =0;
	# 开启循环
	REPEAT
		set i = i+1;
		insert into emp1 values(null, (start_no+i),rand_string(6),'SALESMAN',0001,now(),rand_num(),rand_num(),rand_num());
	
	UNTIL i=max_num
	END REPEAT;
	COMMIT;
END
 
#调用存储过程，生成千万数据
call insert_emp1(10000,1000);
# 再来几条
INSERT INTO `product`.`emp` (`id`, `empno`, `ename`, `job`, `mgr`, `hiredate`, `salary`, `comm`, `deptno`) VALUES (10000001, 10009997, 'lVXXYX', 'SALESMAN', 1, '2024-08-26 00:00:00', 447.00, 334.00, 230);
INSERT INTO `product`.`emp` (`id`, `empno`, `ename`, `job`, `mgr`, `hiredate`, `salary`, `comm`, `deptno`) VALUES (10000002, 10009998, 'AVdFSv', 'SALESMAN', 1, '2024-08-26 00:00:00', 245.00, 140.00, 498);
INSERT INTO `product`.`emp` (`id`, `empno`, `ename`, `job`, `mgr`, `hiredate`, `salary`, `comm`, `deptno`) VALUES (10000003, 10009999, 'hpdwYE', 'SALESMAN', 1, '2024-08-26 00:00:00', 12.00, 63.00, 311);
INSERT INTO `product`.`emp` (`id`, `empno`, `ename`, `job`, `mgr`, `hiredate`, `salary`, `comm`, `deptno`) VALUES (10000004, 10010000, 'npKERv', 'SALESMAN', 1, '2024-08-26 00:00:00', 286.00, 357.00, 343);

说明

环境

windows10
mysql
SELECT version(); # 5.7.44-log

数据

salary、comm、deptno 内容随机。salary为索引字段。

统计下要用到数据的量，轻易别试很慢很慢很慢~。

# count
select count(1) from emp;								# 我这里是 10000000 
SELECT count(1) FROM emp where`salary` > 400;			# 我这里是 2182086
SELECT count(1) FROM emp where`comm` > 400;				# 我这里是 2178551

有个不理解的地方，都说深分页是因为offset过大，但是一般页面的分页都会获取下数据总数用于显示总页数等，但其实count(1)就已经很慢了。（当然，不显示总页数的不算哈）

开始

先看下普通无where条件的limit

SELECT id, salary, comm FROM emp LIMIT 0,10;							# 0.1S内
SELECT id, salary, comm FROM emp LIMIT 10,10;							# 0.1S内
SELECT id, salary, comm FROM emp LIMIT 100,10;							# 0.1S内
SELECT id, salary, comm FROM emp LIMIT 1000,10;							# 0.1S内
SELECT id, salary, comm FROM emp LIMIT 10000,10;						# 0.1S内
SELECT id, salary, comm FROM emp LIMIT 50000,10;						# 0.1S内
SELECT id, salary, comm FROM emp LIMIT 100000,10;						# 0.3S内
SELECT id, salary, comm FROM emp LIMIT 500000,10;						# 0.25S内
SELECT id, salary, comm FROM emp LIMIT 1000000,10;						# 0.5S内
SELECT id, salary, comm FROM emp LIMIT 5000000,10;						# 3秒内 还是比较快
SELECT id, salary, comm FROM emp LIMIT 10000000,10;						# 6秒内 还算比较快

offset在1KW下执行结果仍然较快，算可用状态吧。

在10W的offset示例

1、直接查询，使用了id_salary索引，查询很慢
SELECT id, salary, comm FROM emp where salary > 400 LIMIT 100000,10;

2、加上排序后
2.1、使用salary排序后，用的idx_salary索引，查询很慢
SELECT id FROM emp WHERE salary > 400 order by salary LIMIT 100000,10;


2.1、使用id排序后，用的主键索引，查询反而比二级索引快
SELECT id FROM emp WHERE salary > 400 order by id LIMIT 100000,10;

3、从百度上找的深分页解决方式【子查询优化】
3.1、使用了idx_salary索引，有改善但还是有点慢
SELECT t1.id, salary, comm FROM emp t1, (SELECT id FROM emp WHERE salary > 400 LIMIT 100000,10) t2 where t1.id = t2.id;


3.2、使用了主键索引，比idx_salary索引快很多。
SELECT t1.id, salary, comm FROM emp t1, (SELECT id FROM emp WHERE salary > 400 order by id LIMIT 100000,10) t2 where t1.id = t2.id;

在100W的offset示例

1、使用的idx_salary索引，查询速度非常慢
SELECT t1.id, salary, comm FROM emp t1, (SELECT id FROM emp WHERE salary > 400 LIMIT 1000000,10) t2 where t1.id = t2.id;

2、强制使用主键索引，查询速度有明显改善
SELECT t1.id, salary, comm FROM emp t1, (SELECT id FROM emp force index(PRIMARY) WHERE salary > 400 LIMIT 1000000,10) t2 where t1.id = t2.id;

标签记录法

从百度上找的深分页解决方式【标签记录法】，1KW数据下查询性能依然不错
SELECT id, salary, comm FROM emp where id > 10000000 and salary > 400 LIMIT 10;

不知道为什么走主键索引，扫描全表反而比加索引更快