/* 产生随机字符串的函数*/ DELIMITER $ drop FUNCTIONifEXISTSrand_string; CREATE FUNCTIONrand_string(n INT)RETURNSVARCHAR(255) BEGIN DECLAREchars_strVARCHAR(100)DEFAULT 'abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; DECLARE return_str VARCHAR(255) DEFAULT''; DECLARE i INT DEFAULT0; 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 FUNCTIONifEXISTSrand_num; CREATE FUNCTIONrand_num()RETURNSINT(5) BEGIN DECLAREiINTDEFAULT 0; SET i = FLOOR(100+RAND()*10); RETURN i; END $ DELIMITER;
③编写存储过程,模拟 500W 的员工数据
/*建立存储过程:往emp表中插入数据*/ DELIMITER $ dropPROCEDUREifEXISTS insert_emp; CREATEPROCEDURE insert_emp(INSTARTINT(10),IN max_num INT(10)) BEGIN DECLARE i INTDEFAULT0; /*set autocommit =0 把autocommit设置成0,把默认提交关闭*/ SET autocommit = 0; REPEAT SET i = i + 1; INSERTINTO 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 ENDREPEAT; COMMIT; END $ DELIMITER; /*插入500W条数据*/ call insert_emp(0,5000000);
④编写存储过程,模拟 120 的部门数据
/*建立存储过程:往dep表中插入数据*/ DELIMITER $ dropPROCEDUREifEXISTS insert_dept; CREATEPROCEDURE insert_dept(INSTARTINT(10),IN max_num INT(10)) BEGIN DECLARE i INTDEFAULT0; SET autocommit = 0; REPEAT SET i = i+1; INSERTINTO dep( depno,depname,memo) VALUES((START+i),rand_string(10),rand_string(8)); UNTIL i = max_num ENDREPEAT; COMMIT; END $ DELIMITER; /*插入120条数据*/ call insert_dept(1,120);
⑤建立关键字段的索引,这边是跑完数据之后再建索引,会导致建索引耗时长,但是跑数据就会快一些。
/*建立关键字段的索引:排序、条件*/ CREATEINDEX idx_emp_id ON emp(id); CREATEINDEX idx_emp_depno ON emp(depno); CREATEINDEX idx_dep_depno ON dep(depno);
测试
测试数据:
/*偏移量为100,取25*/ SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname from emp a leftjoin dep b on a.depno = b.depno orderby a.id desclimit100,25; /*偏移量为4800000,取25*/ SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname from emp a leftjoin dep b on a.depno = b.depno orderby a.id desclimit4800000,25;
执行结果:
[SQL] SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname from emp a leftjoin dep b on a.depno = b.depno orderby a.id desclimit100,25; 受影响的行: 0 时间: 0.001s [SQL] SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname from emp a leftjoin dep b on a.depno = b.depno orderby a.id desclimit4800000,25; 受影响的行: 0 时间: 12.275s
因为扫描的数据多,所以这个明显不是一个量级上的耗时。
解决方案
①使用索引覆盖+子查询优化
因为我们有主键 id,并且在上面建了索引,所以可以先在索引树中找到开始位置的 id 值,再根据找到的 id 值查询行数据。
/*子查询获取偏移100条的位置的id,在这个位置上往后取25*/ SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname from emp a leftjoin dep b on a.depno = b.depno where a.id >= (selectidfrom emp orderbyidlimit100,1) orderby a.id limit25;
/*子查询获取偏移4800000条的位置的id,在这个位置上往后取25*/ SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname from emp a leftjoin dep b on a.depno = b.depno where a.id >= (selectidfrom emp orderbyidlimit4800000,1) orderby a.id limit25;
执行结果
执行效率相比之前有大幅的提升:
[SQL] SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname from emp a leftjoin dep b on a.depno = b.depno where a.id >= (selectidfrom emp orderbyidlimit100,1) orderby a.id limit25; 受影响的行: 0 时间: 0.106s
[SQL] SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname from emp a leftjoin dep b on a.depno = b.depno where a.id >= (selectidfrom emp orderbyidlimit4800000,1) orderby a.id limit25; 受影响的行: 0 时间: 1.541s
②起始位置重定义
记住上次查找结果的主键位置,避免使用偏移量 offset:
/*记住了上次的分页的最后一条数据的id是100,这边就直接跳过100,从101开始扫描表*/ SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname from emp a leftjoin dep b on a.depno = b.depno where a.id > 100orderby a.id limit25;
/*记住了上次的分页的最后一条数据的id是4800000,这边就直接跳过4800000,从4800001开始扫描表*/ SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname from emp a leftjoin dep b on a.depno = b.depno where a.id > 4800000 orderby a.id limit25;
执行结果:
[SQL] SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname from emp a leftjoin dep b on a.depno = b.depno where a.id > 100orderby a.id limit25; 受影响的行: 0 时间: 0.001s
[SQL] SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname from emp a leftjoin dep b on a.depno = b.depno where a.id > 4800000 orderby a.id limit25; 受影响的行: 0 时间: 0.000s