3、编写存储过程,模拟500W的员工数据
/*建立存储过程:往emp表中插入数据*/ DELIMITER $ drop PROCEDURE if EXISTS insert_emp; CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10)) BEGINDECLARE i INT DEFAULT 0;/*set autocommit =0 把autocommit设置成0,把默认提交关闭*/SET autocommit = 0;REPEATSET 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_numEND REPEAT;COMMIT; END $ DELIMITER; /*插入500W条数据*/ call insert_emp(0,5000000);
4、编写存储过程,模拟120的部门数据
/*建立存储过程:往dep表中插入数据*/ DELIMITER $ drop PROCEDURE if EXISTS insert_dept; CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10)) BEGINDECLARE i INT DEFAULT 0;SET autocommit = 0;REPEATSET i = i+1;INSERTINTO dep( depno,depname,memo) VALUES((START+i),rand_string(10),rand_string(8));UNTIL i = max_numEND REPEAT;COMMIT; END $ DELIMITER; /*插入120条数据*/ call insert_dept(1,120);
5、建立关键字段的索引
这便是跑完数据之后再建索引,会导致建索引耗时长,但是跑数据就会快一些 。
/*建立关键字段的索引:排序、条件*/CREATE INDEX idx_emp_id ON emp(id);CREATE INDEX idx_emp_depno ON emp(depno);CREATE INDEX idx_dep_depno ON dep(depno);
四、测试
1、测试数据
/*偏移量为100,取25*/SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depnamefrom 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.depnamefrom emp a left join dep b on a.depno = b.depno order by a.id desc limit 4800000,25;
2、执行结果
[SQL]SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depnamefrom 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.depnamefrom emp a left join dep b on a.depno = b.depno order by a.id desc limit 4800000,25;受影响的行: 0时间: 12.275s
因为扫描的数据多,所以这个明显不是一个量级上的耗时 。
五、解决方案
1、使用索引覆盖+子查询优化
因为我们有主键id,并且在上面建了索引,所以可以先在索引树中找到开始位置的 id值,再根据找到的id值查询行数据 。
/*子查询获取偏移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
2、起始位置重定义
记住上次查找结果的主键位置,避免使用偏移量 offset 。
/*记住了上次的分页的最后一条数据的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.depnamefrom emp a left join dep b on a.depno = b.depnowhere 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.depnamefrom emp a left join dep b on a.depno = b.depnowhere a.id > 4800000order by a.id limit 25;受影响的行: 0时间: 0.000s
推荐阅读
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
- MySQL 数据库设计规范
- lnmp一键安装包使用Navicat远程访问mysql8
- 真菌中耳炎
- 十女九寒 宫寒是什么原因导致的
- 贫血导致头晕眼花六大食疗法
- 中国足球|说起国足,不免与差劲挂钩,那么是什么导致他们基本功如此差?
- 奔驰|一个橡胶圈或导致“刹车失灵”!奔驰全球召回近百万辆汽车
- 微软|兑换微软积分导致大批用户被封号 官方回应:正在修复
- 吃谷物可以减肥吗
- 谷歌|导致手机无法联网:紫光展锐处理器出现9.4级严重网络漏洞