Mysql调优-join-子查询-limit

join

类别

https://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg

尽可能减少Join语句中的NestedLoop的循环总次数；“永远用小结果集驱动大的结果集”

优先优化NestedLoop的内层循环

保证Join语句中被驱动表上Join条件字段已经被索引；

当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置；

inner join 时, mysql会自己帮你把小结果集的表选为驱动表

LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有,所以右边是我们的关键点,一定需要建立索引

RIGHT JOIN 条件用于确定如何从左表搜索行,右边一定都有,所以左边是我们的关键点,一定需要建立索引

示例

不加索引

EXPLAIN SELECT *
FROM class
LEFT JOIN book ON class.card = book.card;

1连接字段加索引

ALTER TABLE `book` ADD INDEX Y (`card`);
ALTER TABLE `class` ADD INDEX Y (`card`);

EXPLAIN SELECT *
FROM class
LEFT JOIN book ON class.card = book.card;

type 变为了 ref,rows 也变成了优化比较明显。

子查询优化

1. 尽量不要使用not in 或者 not exists
2. 用left outer join on xxx is null 替代

SELECT SQL_NO_CACHE 
age,
count(*)  
FROM emp a 
WHERE  id  NOT  IN(SELECT ceo FROM dept b2 WHERE ceo IS NOT NULL)
group by age 
having count(*)<10000

limit

语法

limit <offset>,<size>

offset:返回结果第一行的偏移量（想要跳过多少行）

size:指定返回多少条

问题

offset 变大的时候 慢

explain
select *
from employees
limit 300000,10;

解决

覆盖索引 (108)

explain
select emp_no
from employees
limit 300000,10;

覆盖索引+join(109ms)

select * from employees e
inner join
(select emp_no from employees limit 300000,10) t
using (emp_no);

覆盖索引+子查询（126ms）

select *
from employees
where emp_no >=
  (select emp_no from employees limit 300000,1)
limit 10;

范围查询+limit语句

上一页主键最大值

select *
from employees
limit 10;

select *
from employees
where emp_no > 10010
limit 10;

如果能获得起始主键值& 结束主键值

select *
from employees
where emp_no between 20000 and 20010;

禁止传入过大的页码百度搜素