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

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

select count(*) from dept d left join emp e on d.dno=e.deptno 
where e.job='other' and e.deptno=85

网上找的解决方案：如果对于有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; 

提升不大！

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