多表查询优化

驱动表理论

外连接

左外连接：一般来说：坐表为驱动表，右表为被驱动表
内连接

对于内连接来讲，如果表的连接条件中只能有一个字段有索引，则有索引的字段所在表会被作为被驱动表出现

对于内连接来讲，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表。以小表驱动大表

join语句原理

整体效率比较：INLJ > BNLJ > SNLJ

SNLJ(Simple-Nest-Loop-Join)
  最朴素的Join查询方式，执行流程就是：从t1中取出一条数据，拿这条数据的a字段与t2中比较，找到目标行返回，重复次操作。这样的时间复杂度是最高的，O(N*M)
INLJ(Index-Nest-Loop-Join)
  加入索引，也就是优化了拿t1的数据去匹配t2的操作，时间复杂度O(n*logm)
BNLJ(Block-Nest-Loop-Join)
  一整块数据拿出来，匹配速度快了，但是需要更多的内存空间

永远用小结果集驱动大结果集（其本质就是减少外层循环的数据数量）

# 推荐
select t1.b, t2.* from t1 straight_join t2 on (t1.b = t2.b) where t2.id <= 100
# 不推荐
select t1.b, t2.* from t2 straight_join t1 on (t1.b = t2.b) where t2.id <= 100

为被驱动表匹配的条件增加索引（减少内层表的循环匹配次数）
增大join buffer size的大小（一次缓存的数据越多，那么内层包扫表次数就越少）
减少驱动表不必要的字段查询（字段越少，join buffer所缓存的数据就越多）

子查询优化

子查询效率不高原因

执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。
对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

优化

在MySQL中，可以使用JOIN查询代替子查询，性能会更好

在MySQL中，使用连接（JOIN）查询来替代子查询，不需要建立临时表，其查询速度比子查询要快，如果查询中使用索引的话，性能会更好。

尽量不要使用NOT IN 或者NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL 替代

ORDER BY 优化

在MySQL中，支持两种排序方式，分别是FileSort和Index排序

Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
FileSort排序则一般在内存中进行排序，占用CPU较多。如果待排序结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率较低。

优化建议

SQL中，可以在WHERE子句和ORDER BY子句中使用索引，目的是在WHERE子句中避免全表扫描，在ORDER BY子句避免使用FileSort排序。当然，某些情况下扫描全表，或者FileSort排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。
尽量使用Index完成ORDER BY排序。如果WHERE 和ORDER BY后面是相同的列就使用单索引列；如果不同就使用联合索引。
无法使用Index时，需要对FileSort方式进行调优。

GROUP BY 优化

group by 使用索引的原则几乎跟order by一致，group by即使没有过滤条件用到索引，也可以直接使用索引。
group by 先排序再分组，遵照索引建的最佳左前缀法则。
当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数的设置。
where效率高于having，能写在where的限定条件就不要写在having中了。
减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
包含order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

分页查询优化

一般分页查询中，通过创建覆盖索引能够比较好的提高性能。一个常见又非常头疼的问题就是limit 2000000, 10，此时需要MySQL排序前2000010记录，仅返回2000000 - 2000010的记录，其他记录丢弃，查询排序的代价非常高。

优化思路1

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。

1 2	select * from student t, (select id from student order by id limit 2000000, 10) a where t.id = a.id

优化思路2

该方案适用于主键自增的表，可以把limit查询转换成某个位置的查询。

1	select * from student where id > 2000000 limit 10;

Jlin's Blog

MySQL调优-7-多表查询、子查询、order by、group by、分页优化