查询优化

衡量查询开销的三个指标

• 响应时间
• 扫描的行数
• 返回的行数

关联查询的优化

原理部分

image.png
image.png
image.png

inner join的优化

STRAIGHT_JOIN功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。
接下来我们举个例子进行大致的分析：

select t1.* from Table1 t1 inner join Table2 t2
on t1.CommonID = t2.CommonID where t1.FilterID = 1

以上sql大数据量下执行需要30s，明明Table1表的FilterID字段建了索引，Table1和Table2的CommonID也建了索引。通过explain来分析，发现执行计划中表的执行顺序是Table2->Table1。，mysql中指定了连接条件时，满足查询条件的记录行数少的表为驱动表；如未指定查询条件，则扫描行数少的为驱动表。mysql优化器就是这么粗暴以小表驱动大表的方式来决定执行顺序的。
但如下sql的执行时间都少于1s：

select t1.* from Table1 t1 where t1.FilterID = 1
select t1.* from Table1 t1 inner join Table2 t2 on t1.CommonID = t2.CommonID

这个时候STRAIGHT_JOIN就派上用场，我们对sql进行改造如下：

select t1.* from Table1 t1 STRAIGHT_JOIN  Table2 t2 on t1.CommonID = t2.CommonID 
where t1.FilterID = 1

用explain进行分析，发现执行顺序为Table1->Table2，这时就由Table1来作为驱动表了，Table1中相应的索引也就用上了，执行时间竟然低于1s了。

分析到这里，必须要重点说下：

• STRAIGHT_JOIN只适用于inner join，并不适用于left join，right join。（因为left join，right join已经代表指定了表的执行顺序）
• 尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。使用STRAIGHT_JOIN一定要慎重，因为啊部分情况下认为指定的执行顺序并不一定会比优化引擎要靠谱。

union 优化

image.png

松散索引

有如下查询

索引(a,b)
select ... from tb1 where b between 2 and 3

image.png
image.png
image.png

在同一个表上查询和更新

mysql不允许同一张表同时进行查询和更新
例如

update tb1 as outer_tb1
    set cnt = (
          select count(*) from tb1 as inner_tb1
          where inner_tb1.type = outer_tb1.type
    );
ERROR 1093 (HY000):You can't specify target table 'outer_tb1' for update in FROM clause

可以通过使用生成表的形式绕过这个限制，因为mysql只会把这个表当作一个临时表处理。

update tb1 inner join(
                        select type,count(*) as cnt
                        from tb1
                        group by type
                   ) as der using(type)
set tb1.cnt = der.cnt

优化count()查询

count()有两种不同的作用

• 统计某个列值的数量,count(字段) 会统计该字段在表中出现的次数，不统计字段为null的记录
• 统计行数 count(*) 包含字段为null的记录

简单的优化

例一
例二
例二中的 OR NULL必须有！

优化关联查询

• 确保on或者using子句上(非驱动表)的列上有索引，如果优化器的关联顺序是B，A，那么就不需要在B表上建索引
• 尽量使group by和order by中的表达式只涉及一个表中的列

优化group by和distinct

如果对关联查询做分组，通常采用查找表的标志列分组的效率比其他列更高。
例如下面查询的效率不会很好

image.png

下面的查询效率会更高

image.png

group by 会自动按照其字句的字段排序(GROUP BY id相当于GROUP BY id ORDER BY id)，如果不关心结果集的顺序，则可以使用order by null，避免mysql使用filesort

优化LIMIT

在偏移量非常大时，例如limit 10000,10会查询10010条记录，再把前面的10000条记录抛弃掉，代价非常高
例如

SELECT * FROM person LIMIT 1000000,10 

这个查询需要花1.094s
延迟关联让mysql扫描尽可能少的页面，可以大大提升查询效率

SELECT * FROM person INNER JOIN (SELECT id FROM person LIMIT 1000000,10) AS lim USING(id)

使用延迟关联后，查询时间缩短为0.839s

优化UNION

mysql通过创建并填充临时表的方式来执行union查询。union会在临时表上加上DISTINCT选项消除重复的行使得数据唯一，这样做的代价非常高。
如果对数据的唯一性没有要求，则使用union all