一、永远小表驱动大表
类似嵌套循环Nested Loop
select * from tbl_emp e where e.deptid in (select id from tbl_dept);
select * from tbl_emp e where exists (select 1 from tbl_dept d where d.id = e.deptid);
二、order by关键字优化
建表sql
create table tblA( id int primary key not null auto_increment, age INT, birth timestamp not null);
insert into tblA(age,birth) values(22,now());
insert into tblA(age,birth) values(23,now());
insert into tblA(age,birth) values(24,now());
create index idx_A_ageBirth on tblA(age, birth);
select * from tblA;
explain select * from tblA where age > 20 order by age;
explain select * from tblA where age > 20 order by age,birth;
explain select * from tblA where age > 20 order by birth;
explain select * from tblA where age > 20 order by birth,age;
更多的例子
总结:
(1)ORDER BY子句,尽量使用Index方式排序,避免使用FileSort方式排序
(2)MySQL支持二种方式的排序,FileSort和Index,Index效率高。它指MySQL扫描索引本身完成排序。FileSort方式效率较低。
(3)ORDER BY满足两情况,会使用Index方式排序。ORDER BY语句使用索引最左前列;使用where子句与OrderBy子句条件列组合满足索引最左前列
(4)如果不在索引列上,filesort有两种算法:mysql就要启动双路排序和单路排序
单路问题:
优化策略:
小总结