MySQL 亿级数据分页的优化
浪尖聊大数据
共 7019字,需浏览 15分钟
· 2021-07-21
POST domain/v1.0/module/method?order=condition&orderType=desc&offset=1800000&limit=500
select * from t_name where c_name1='xxx' order by c_name2 limit 2000000,25;
/*部门表,存在则进行删除 */
drop table if EXISTS dep;
create table dep(
id int unsigned primary key auto_increment,
depno mediumint unsigned not null default 0,
depname varchar(20) not null default "",
memo varchar(200) not null default ""
);
/*员工表,存在则进行删除*/
drop table if EXISTS emp;
create table emp(
id int unsigned primary key auto_increment,
empno mediumint unsigned not null default 0,
empname varchar(20) not null default "",
job varchar(9) not null default "",
mgr mediumint unsigned not null default 0,
hiredate datetime not null,
sal decimal(7,2) not null,
comn decimal(7,2) not null,
depno mediumint unsigned not null default 0
);
/* 产生随机字符串的函数*/
DELIMITER $
drop FUNCTION if EXISTS rand_string;
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
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 $
DELIMITER;
/*产生随机部门编号的函数*/
DELIMITER $
drop FUNCTION if EXISTS rand_num;
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(100+RAND()*10);
RETURN i;
END $
DELIMITER;
/*建立存储过程:往emp表中插入数据*/
DELIMITER $
drop PROCEDURE if EXISTS insert_emp;
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
/*set autocommit =0 把autocommit设置成0,把默认提交关闭*/
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO emp(empno,empname,job,mgr,hiredate,sal,comn,depno) VALUES ((START+i),rand_string(6),'SALEMAN',0001,now(),2000,400,rand_num());
UNTIL i = max_num
END REPEAT;
COMMIT;
END $
DELIMITER;
/*插入500W条数据*/
call insert_emp(0,5000000);
/*建立存储过程:往dep表中插入数据*/
DELIMITER $
drop PROCEDURE if EXISTS insert_dept;
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i+1;
INSERT INTO dep( depno,depname,memo) VALUES((START+i),rand_string(10),rand_string(8));
UNTIL i = max_num
END REPEAT;
COMMIT;
END $
DELIMITER;
/*插入120条数据*/
call insert_dept(1,120);
/*建立关键字段的索引:排序、条件*/
CREATE INDEX idx_emp_id ON emp(id);
CREATE INDEX idx_emp_depno ON emp(depno);
CREATE INDEX idx_dep_depno ON dep(depno);
/*偏移量为100,取25*/
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno order by a.id desc limit 100,25;
/*偏移量为4800000,取25*/
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno order by a.id desc limit 4800000,25;
[SQL]
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno order by a.id desc limit 100,25;
受影响的行: 0
时间: 0.001s
[SQL]
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno order by a.id desc limit 4800000,25;
受影响的行: 0
时间: 12.275s
解决方案
/*子查询获取偏移100条的位置的id,在这个位置上往后取25*/
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id >= (select id from emp order by id limit 100,1)
order by a.id limit 25;
/*子查询获取偏移4800000条的位置的id,在这个位置上往后取25*/
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id >= (select id from emp order by id limit 4800000,1)
order by a.id limit 25;
[SQL]
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id >= (select id from emp order by id limit 100,1)
order by a.id limit 25;
受影响的行: 0
时间: 0.106s
[SQL]
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id >= (select id from emp order by id limit 4800000,1)
order by a.id limit 25;
受影响的行: 0
时间: 1.541s
/*记住了上次的分页的最后一条数据的id是100,这边就直接跳过100,从101开始扫描表*/
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 100 order by a.id limit 25;
/*记住了上次的分页的最后一条数据的id是4800000,这边就直接跳过4800000,从4800001开始扫描表*/
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 4800000
order by a.id limit 25;
[SQL]
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 100 order by a.id limit 25;
受影响的行: 0
时间: 0.001s
[SQL]
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 4800000
order by a.id limit 25;
受影响的行: 0
时间: 0.000s
·END·
作者:翁智华
来源:https://www.cnblogs.com/wzh2010/
版权申明:内容来源网络,版权归原创者所有。除非无法确认,我们都会标明作者及出处,如有侵权烦请告知,我们会立即删除并表示歉意。谢谢!
评论
多人同时导出 Excel 干崩服务器!新来的阿里大佬给出的解决方案太优雅了!
点击关注公众号,Java 干货及时推送↓推荐阅读:面试辅导,我们出大成果了!来源:juejin.cn/post/7259249904777838629前言 业务诉求:考虑到数据库数据日渐增多,导出会有全量数据的导出,多人同时导出可以会对服务性能造成影响,导出涉及到mysql查询的io操作,
Java技术栈
1
Go 1.22 的新增功能系列之二:reflect.TypeFor
Go 1.22 的第一个候选版本已经发布,这意味着最终版本即将发布,现在是我在博客中介绍我在这个周期中所做工作的时候了。像往常一样,我的贡献很小,但它们是我的,所以我将从幕后的角度来谈谈它们。首先是reflect.TypeFor。这是整个函数:// TypeFor returns the [Type
GoCN
0
有意思!一个关于 Spring 历史的在线小游戏
发现 Spring One 的官网上有个好玩的彩蛋,分享给大家!进到Spring One的官网,可以看到右下角有个类似马里奥游戏中的金币图标。点击该金币之后,会打开一个新的页面,进入下面这样一个名为:The History Of Spring 的在线小游戏你可以使用上下左右的方向键来控制Spring
公众号程序猿DD
1
了解加密货币到加密货币的互换
1、什么是加密货币互换?加密货币到加密货币的互换是指以现行市场汇率将一种加密货币直接兑换为另一种加密货币。与需要法定货币存款和较长流程的传统交易所不同,加密货币到加密货币的互换可以无缝地促进交换。掉期在提高加密货币的流动性和效率方面发挥着重要作用。该功能使用户能够将他们的加密货币与钱包中的其他代币进
区块链头条
0
李彦宏:开源大模型不如闭源,后者会持续领先;周鸿祎:“开源不如闭源” 的言论是胡说八道
架构师大咖
架构师大咖,打造有价值的架构师交流平台。分享架构师干货、教程、课程、资讯。架构师大咖,每日推送。
公众号该公众号已被封禁0、李彦宏:开源大模型不如闭源,后者会持续领先当今
源码共读
0
Go早期是如何在Google内部发展起来的
2007年Go诞生于Google,2009年Google正式对外宣布了Go语言的开源!时至今日,距离Go开源已经过去了近15个年头了[1]!Go在Google公司内部究竟是怎样的一个状态呢?前Google员工Yves Junqueira近期撰文从其个人所见所闻谈了Go在Google的历程[2]!这里
GoCN
0
【第129期】程序员的新宠:三款终端工具,让你告别Xshell!
概述 WindTerm:跨平台的SSH利器 首先介绍的是WindTerm,这是一款使用C语言开发的跨平台SSH客户端。它不仅完全免费,而且没有商业使用的限制。WindTerm支持SSH v2、Telnet、Raw Tcp等协议,而且性能出色,甚至超过了FinalShell和Electerm。功能
前端微服务
0
字节员工:35岁以后被裁员的,后来都走了哪条路?现在2-2,要不要利用最后一年拼命上个岸。
架构师大咖
架构师大咖,打造有价值的架构师交流平台。分享架构师干货、教程、课程、资讯。架构师大咖,每日推送。
公众号该公众号已被封禁在当今竞争激烈的职场环境中,年龄并不总是一个决定性
源码共读
0