MySQL优化案例

准备:两表,一个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

MySQL优化案例

select count(*) from emp

MySQL优化案例

初始的查询语句:

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; 

MySQL优化案例

看执行计划

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
MySQL优化案例
开始优化:
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;  

MySQL优化案例

提升了大概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;  

MySQL优化案例
提升了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;  

MySQL优化案例
可以看出复合索引已经用上了
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;  

MySQL优化案例
执行计划
MySQL优化案例
提升了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;   

MySQL优化案例
执行计划
MySQL优化案例
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; 

MySQL优化案例
提升不大!

还有什么值得优化的地方,请留言告诉我一声!!

上一篇:数据库中的约束


下一篇:Mybatis的学习