准备:两表,一个dept,一个emp
dept表
CREATE TABLE `dept` (
`dno` int(10) NOT NULL DEFAULT '0',
`dname` varchar(50) NOT NULL DEFAULT '',
`dlocation` varchar(30) DEFAULT '',
PRIMARY KEY (`dno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
emp表
CREATE TABLE `emp` (
`eid` int(10) NOT NULL DEFAULT '0',
`ename` varchar(50) NOT NULL DEFAULT '',
`job` varchar(50) NOT NULL DEFAULT '',
`deptno` int(10) NOT NULL DEFAULT '0',
PRIMARY KEY (`eid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
插入的数据要随机,创建函数
随机字符串的函数
CREATE DEFINER=`root`@`localhost` FUNCTION `randstring`(n int) RETURNS varchar(255) CHARSET utf8
BEGIN
declare all_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,SUBSTR(all_str,FLOOR(1+RAND()*52),1));
set i=i+1;
end while;
RETURN return_str;
END
随机数字的函数
CREATE DEFINER=`root`@`localhost` FUNCTION `rand_num`() RETURNS int(5)
BEGIN
DECLARE i int DEFAULT 0;
set i=FLOOR(RAND()*10);
RETURN i;
END
有了函数,就可以使用存储过程批量插入数据了
插入dept数据的存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_dept`(in dno_start int(10),in data_times int(10))
BEGIN
DECLARE i int DEFAULT 1;
set autocommit =0;
REPEAT
insert into dept VALUES(dno_start+i,randstring(6),randstring(8));
set i=i+1;
UNTIL i=data_times
end REPEAT;
COMMIT;
END
插入emp表的存储过程
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_emp`(in eid_start int(10),in data_times int(10))
BEGIN
DECLARE i int DEFAULT 1;
set autocommit =0;
REPEAT
insert into emp values(eid_start+i,randString(5),'other',85);
set i=i+1;
until i=data_times
end REPEAT;
COMMIT;
END
结果:
select count(*) from dept
select count(*) from emp
初始的查询语句:
select * from emp e left join dept d on e.deptno=d.dno where e.job='other' and e.deptno=85 order by e.ename desc,e.eid asc limit 5000000,20;
看执行计划
explain select * from emp e left join dept d on e.deptno=d.dno where e.job='other' and e.deptno=85 order by e.ename desc,e.eid asc limit 5000000,20;
初步问题如下:
1.emp全表查询
2.没使用索引
3.出现了using filesort
开始优化:
1.多表连接优化:小表驱动大表,左连接的索引加在左边
select * from dept d left join emp e on d.dno=e.deptno where e.job='other' and e.deptno=85 order by e.ename desc,e.eid asc limit 5000000,20;
提升了大概1s!
2.创建符合索引(index_job_deptno_ename_eid)
create index index_job_deptno_ename_eid on emp(job,deptno,ename,eid)
select * from dept d left join emp e on d.dno=e.deptno where e.job='other' and e.deptno=85 order by e.ename desc,e.eid asc limit 5000000,20;
提升了17s!
看下执行计划
explain select * from dept d left join emp e on d.dno=e.deptno where e.job='other' and e.deptno=85 order by e.ename desc,e.eid asc limit 5000000,20;
可以看出复合索引已经用上了
3.解决use filesort外排序
select * from dept d left join emp e on d.dno=e.deptno where e.job='other' and e.deptno=85 order by e.ename asc,e.eid asc limit 5000000,20;
执行计划
提升了15s
4.更换select * ,加上索引覆盖
select eid,job,deptno,ename from dept d left join emp e on d.dno=e.deptno where e.job='other' and e.deptno=85 order by e.ename asc,e.eid asc limit 5000000,20;
执行计划
5.优化limit
我这个sql的结果有1200w,属于千万级别了,没有优化过limit
网上找的解决方案:如果对于有where 条件,又想走索引用limit的,必须设计一个索引,将where 放第一位,limit用到的主键放第2位,而且只能select 主键!
所以更改复合索引
#删除旧索引
drop index index_job_deptno_ename_eid on emp
#调整排序位置,添加新索引
create index index_job_deptno_eid_ename on emp(job,deptno,eid,ename)
select eid,job,deptno,ename from dept d left join emp e on d.dno=e.deptno where e.job='other' and e.deptno=85 order by e.eid asc,e.ename asc limit 5000000,20;
提升不大!
还有什么值得优化的地方,请留言告诉我一声!!