分页的优化:
select * from employees limit 10000,10;
要想优化这个SQL,首先得理解这条SQL是怎么执行的。MySQL会从数据库把前10010条数据都查询出来,然后过滤掉前10000条记录,留下后面10条记录,最终返回给客户端。
优化手段:尽量返回少的字段在排序阶段,尽量走索引
如果employees这张表的主键是自增且连续的,可以这样优化走主键
select * from employees where id > 10000 limit 10;
如果这张表的数据不是连续的,可以这样优化
select * from employees ORDER BY name limit 90000,5;
优化结果:在order by 和limit的中仅返回id字段,id还是主键,然后根据主键去排序效率高了不少,可以用EXPLAN来查询比较执行计划的区别
select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;
Join关联查询
多张表关联涉及到两中算法:
EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;
算法里面涉及到两个概念:
- inner join
驱动表:如这条SQL,根据过滤字段a,得到的数据量小的表,我们称为驱动表
被驱动表: 根据过滤字段a,得到的数据量大的表,我们称为被驱动表
- left join 默认:左驱动表,右被驱动表
- right join 默认:左被驱动表,右驱动表
NLJ算法:从驱动表中读取一行数据,然后根据驱动表中的过滤字段a,去被驱动表中匹配出对应的数据,循环这个流程,最后结果合集给客户端
BNL算法:把驱动表中所有数据都放入buffer_join中(如果驱动表中数据一次性放不完,分段放,buffer_join默认256kb),此时buffer_join中的数据是无序的,然后从被驱动表中拿出一行数据,根据条件和buffer_join中的数据做比较,重复此操作,最后结果合集给客户端
过滤字段有索引,MySQL一般用NLJ算法;过滤字段没索引,MySQL一般用BNL算法
in和exsits优化:
in的里面放小表,exsits外面的放小表
Mysql规范解读:尽在阿里巴巴手册