MySQL之sql优化

关联查询

• left join
  优化规则：在匹配表建立关联字段的索引（左侧为驱动表，右侧有匹配表）。

-- 匹配表不创建索引时，两张表都是全盘扫描
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

-- 匹配表关联字段创建索引，匹配表只扫描关联字段
ALTER TABLE `book` ADD INDEX  idx_card(`card`);
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card ;

• inner join
  优化规则：同left join，为关联字段添加索引。inner join会自动选择小表作为驱动表。
• 总结
  1.匹配表的关联字段创建索引。
  2.left join或者right join时，选择小表作为驱动表，因为驱动表会全盘扫描。
  3.子查询尽可能不放在匹配表，有可能使得索引失效。
  4.能直接关联表的，就尽可能不使用子查询。

子查询

看一个例子：
取所有不为CEO的员工人数，按年龄分组。

-- 子查询方式，即使子表有索引，也会全盘扫描
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);
EXPLAIN SELECT age,COUNT(*) FROM emp WHERE id NOT IN 
	(SELECT ceo FROM dept WHERE ceo IS NOT NULL) GROUP BY age;

-- 为子表ceo字段建立索引
CREATE INDEX idx_dept_ceo ON dept(ceo);
EXPLAIN SELECT age,COUNT(*) FROM emp WHERE id NOT IN 
	(SELECT ceo FROM dept WHERE ceo IS NOT NULL) GROUP BY age;

-- 用left join 和 is null 代替子查询not in
EXPLAIN SELECT age,COUNT(*) FROM emp LEFT JOIN dept ON emp.id = dept.ceo WHERE dept.id IS NULL GROUP BY age;

总结：
子查询为从表关联字段创建索引，用left join和is null代替子查询not in

排序分组

• 无过滤，不索引。
  没有过滤条件，查询不使用索引。where和limit都是过滤条件，会使用索引。

CREATE INDEX idx_age_deptid_name ON emp (age,deptid,NAME);
EXPLAIN  SELECT * FROM emp ORDER BY age,deptid;

EXPLAIN SELECT * FROM emp WHERE age=40 ORDER BY deptid;

EXPLAIN  SELECT * FROM emp ORDER BY age,deptid LIMIT 10;

• 顺序错，必排序
  过滤条件有索引列，查询才会使用索引。如果索引是复合索引，那么过滤条件必须包含复合索引的第一列。
  order by后面的顺序要和符合索引的顺序一致。

-- 可以使用索引且没有发生手动排序（using filesort）的情况
CREATE INDEX idx_age_deptid_name ON emp (age,deptid,NAME);
EXPLAIN  SELECT * FROM emp WHERE age=45 ORDER BY deptid,NAME;

-- order by后面的顺序和复合索引顺序不一致，会使用手动排序
explain  select * from emp where age=45 order by  name,deptid;

-- 过滤条件没有复合索引的第一列
EXPLAIN SELECT * FROM emp WHERE deptid=45 ORDER BY age;

• 方向反，必排序
  如果排序的方向不一致，也会使用手工排序。

-- 排序方向不一致
explain select * from emp where age=45 order by  deptid asc, name desc ;

-- 排序方向一致
EXPLAIN SELECT * FROM emp WHERE age=45 ORDER BY  deptid DESC, NAME DESC ;

• goup by同order by
  group by的规则基本上同order by一致。不同的是，group by即使不使用索引字段过滤，也会使用索引。

EXPLAIN SELECT age FROM emp GROUP BY age;

• 使用覆盖索引
  select 和 from之间的查询字段<=索引字段+主键。

总结

1.为匹配表关联字段创建索引。
2.小表作为驱动表，因为驱动表会进行全盘扫描。
3.匹配表尽可能不使用子查询。
4.使用left join和is null替换子查询not in。
5.子查询为子表的关联字段创建索引。
6.查询尽可能使用过滤条件。
7.order by后面的字段顺序要和复合索引字段顺序一致。
8.order by字段排序顺序要一致。
9.使用覆盖索引。