MySQL 优化器提示(一)之 Join-Order Optimizer Hints

原创 aisql 2022-03-06

4266

我在实际优化的SQL语句中，对于多表Join的语句，有多次是因为join顺序优化器选择错误，而引起的查询变慢，这篇文章就专门讲一下 MySQL 中影响Join 顺序的方法。

1、5.7版本

在MySQL 5.7版本，没有 Join-Order Optimizer Hints 只有一个STRIGHT_join,那就先讲一下STRIGHT_join的用法吧。STRIGHT_join的作用就是强制左表为驱动表。
我们来看看SQL例子来理解。

select * from  user a 
inner join services b on a.id = b.userid 
where a.tel like '136%'
order  by  b.CreatedTime desc 
limit  10 
复制

简单说一下以上语句中，索引情况。上面a表中 tel字段有索引。b表 CreatedTime 也有索引
我们来看看MySQL优化器选择的关联顺序

explain 
select * from   services  b  
inner join user a  on a.id = b.userid 
where a.tel like '136%'
order  by  b.CreatedTime desc 
limit  10 
复制

通过执行计划看出 MySQL优化器选择了a表做驱动表。优化器认为在这个SQL中过滤重要于排序。所以走了 a表tel索引。但因为b.CreatedTime需要排序。所以执行计划走了Using temporary; Using filesort

优化器选择就正确了吗。我们先来看看实际的执行时间

执行了 0.797秒

如果想去掉 Using temporary; Using filesort 那么我们就强制b表为驱动表。就用到了STRIGHT_join

explain 
select * from  services  b 
straight_join user a on a.id = b.userid 
where a.tel like '136%'
order  by  b.CreatedTime desc 
limit  10 
复制

可以看到。强制b表为驱动表后，就用了上b表的 createdTime索引 ,从而消除了 Using temporary; Using filesort
我们再来看看执行时间

执行了0.094秒。
相对于0.797秒。性能有8倍的提升。所以也不能完全信赖MySQL优化器的选择。

对于这个语句，很明显，要么选择走过滤的索引，要么选择走排序的索引。那么到底在语句中以什么样的标准来确认选择哪个呢？

2、MySQL8.x版本

MySQL8.x版本除了STRIGHT_join 提供了丰富的 Join-Order Optimizer Hints
JOIN_FIXED_ORDER、JOIN_ORDER、JOIN_PREFIX、JOIN_SUFFIX

a、JOIN_FIXED_ORDER

官网定义 JOIN_FIXED_ORDER: Force the optimizer to join tables using the order in which they appear in the FROM clause. This is the same as specifying SELECT STRAIGHT_JOIN.
JOIN_FIXED_ORDER 类似于STRAIGHT_JOIN 要求优化器按语句书写的顺序来join表。还有一个是 JOIN_FIXED_ORDER 作用的是From语句后的所有表，而会是单一表。
那我们用 JOIN_FIXED_ORDER 来代替 STRAIGHT_JOIN

explain 
select 
/*+
qb_name(qb1)
JOIN_FIXED_ORDER(@qb1) 
*/
* from  services  b 
inner join user a on  b.userid  = a.id
where a.tel like '136%'
order  by  b.CreatedTime desc 
limit  10 
复制

看执行计划，和STRAIGHT_JOIN 结果一样了。

b、JOIN_ORDER

JOIN_ORDER就是以指定的连接顺序为关联顺序

explain 
select 
/*+
JOIN_ORDER(b,a) 
*/
* from  services  b 
inner join user a on  b.userid  = a.id
where a.tel like '136%'
order  by  b.CreatedTime desc 
limit  10 
复制

我指定了b,a为顺序。就以b,a顺序为关联顺序了。

c、JOIN_PREFIX

JOIN_PREFIX 指定第一个驱动表，其它表在第一个表之后

explain 
select 
/*+
JOIN_PREFIX(b) 
*/
* from  services  b 
inner join user a on  b.userid  = a.id
where a.tel like '136%'
order  by  b.CreatedTime desc 
limit  10 
复制

d、JOIN_SUFFIX

JOIN_SUFFIX指定第一个表为最后连接的表，其它表在这个表之前

explain 
select 
/*+
JOIN_SUFFIX(b) 
*/
* from  services  b 
inner join user a on  b.userid  = a.id
where a.tel like '136%'
order  by  b.CreatedTime desc 
limit  10 
复制

e、4个join hints都可以同时使用。当有冲突时，以hint的顺序为准。后面冲突者就会忽略。

explain 
select 
/*+
JOIN_SUFFIX(b) 
JOIN_PREFIX(b) 
*/
* from  services  b 
inner join user a on  b.userid  = a.id
where a.tel like '136%'
order  by  b.CreatedTime desc 
limit  10 
复制