SQL优化 - 修改执行计划加速SQL查询

在某些复杂sql语句，或者数据分布不均衡的table中（未启动直方图统计），默认的执行计划可能不是最佳方案，此时我们可以通过控制优化器策略引导执行计划发生改变。

先看一个案例

某生产库中的sql执行效率非常低，查看执行计划发现驱动表扫描行数很多且不走索引。

explain 
SELECT count(distinct u.id)
     FROM sys_t_audience_member m 
     JOIN org_user u on u.id = m.user_id
     JOIN sys_t_audience_object o ON m.condition_id = o.condition_id 
     AND o.activity_type = 'E' 
     AND m.is_deleted = 0 
     AND o.business_id = 1580755887218814978
     where  u.shared_1 & 4096 = 4096 and u.`status` = 1  and u.IS_DELETED = 1;
+----+-------------+-------+------------+------+------------------------------------+------------------+---------+-------------------------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys                      | key              | key_len | ref                     | rows    | filtered | Extra       |
+----+-------------+-------+------------+------+------------------------------------+------------------+---------+-------------------------+---------+----------+-------------+
|  1 | SIMPLE      | u     | NULL       | ALL  | PRIMARY                            | NULL             | NULL    | NULL                    | 1041455 |     1.00 | Using where |
|  1 | SIMPLE      | m     | NULL       | ref  | idx_condition_id,idx_tenant_user   | idx_tenant_user  | 9       | rxt_exam.u.ID           |       5 |    10.00 | Using where |
|  1 | SIMPLE      | o     | NULL       | ref  | idx_condition_id,idx_activity_type | idx_condition_id | 9       | rxt_exam.m.CONDITION_ID |       5 |     3.38 | Using where |
+----+-------------+-------+------------+------+------------------------------------+------------------+---------+-------------------------+---------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

使用explain analyze查看执行流程，首先以table scan的模式扫描u表，然后loop m表和o表，虽然loop次数不高，但filter的过滤性很差。执行SQL的总耗时达到476.106秒。

优化方案
改写SQL，把原有的sql语句作为子查询，然后将条件v.is_deleted = 1放在最外层。改写SQL后执行计划依然发生了改变，然后使用优化器提示，禁止条件下推的方法固定执行计划。

explain analyze
SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN(v) */ COUNT(*) FROM(
            SELECT DISTINCT u.id, u.is_deleted
            FROM sys_t_audience_member m
            JOIN org_user u ON u.id = m.user_id AND u.`status` = 1
            JOIN sys_t_audience_object o ON m.condition_id = o.condition_id
                AND o.activity_type = 'E'
                AND m.is_deleted = 0
                AND o.business_id = 1580755887218814978
        ) v WHERE v.is_deleted = 1\G;

优化后执行计划
前半部分同上，第5行临时表去重耗时54秒，第4行扫描临时表总耗时0.101秒，第3行物化这0行记录，第2行扫描物化记录，最后数据做聚合，返回count数量，这里的耗时也就是整个SQL执行总耗时：56.234秒

上面案例就用到了优化器提示，在一些复杂sql中，遇到执行计划不佳引发的性能问题时，我们就可以通过控制优化器策略选择更优的执行计划。

下面介绍控制优化器的两种方法：

• 修改优化器选项
• 使用优化器提示（hint）

1、修改优化器选项
mysql优化器程序通过optimizer_switch系统变量控制。该变量由多个优化器选项组成，每个选项的值为off或者on，其对应优化器行为为禁用或者开启。

在不同版本中，优化器选项的值可能有所不同，所以在数据库版本升级后，可能会引发sql执行计划变化，影响到sql执行效率。

控制优化器策略的一种方法是设置 optimizer_switch 系统变量，可以在全局和会话层修改。修改变量后，所有后续查询操作会立刻受到影响。

# 查看优化器选项集
mysql> SELECT @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,
                    index_merge_sort_union=on,index_merge_intersection=on,
                    engine_condition_pushdown=on,index_condition_pushdown=on,
                    mrr=on,mrr_cost_based=on,block_nested_loop=on,
                    batched_key_access=off,materialization=on,semijoin=on,
                    loosescan=on,firstmatch=on,duplicateweedout=on,
                    subquery_materialization_cost_based=on,
                    use_index_extensions=on,condition_fanout_filter=on,
                    derived_merge=on,use_invisible_indexes=off,skip_scan=on,
                    hash_join=on,subquery_to_derived=off,
                    prefer_ordering_index=on,hypergraph_optimizer=off,
                    derived_condition_pushdown=on,hash_set_operations=on
1 row in set (0.00 sec)

# 修改其中一个优化器选项
SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';

# 示例
mysql> SET GLOBAL optimizer_switch="prefer_ordering_index=on";
mysql> SELECT @@optimizer_switch LIKE '%prefer_ordering_index=on%';
+------------------------------------------------------+
| @@optimizer_switch LIKE '%prefer_ordering_index=on%' |
+------------------------------------------------------+
|                                                    1 |
+------------------------------------------------------+

应用

例如，使用IN (subquery的查询：

mysql> CREATE TABLE t1 (a INT, b INT);
mysql> CREATE TABLE t2 (a INT, b INT);

mysql> INSERT INTO t1 VALUES ROW(1,10), ROW(2,20), ROW(3,30);
mysql> INSERT INTO t2 VALUES ROW(1,10), ROW(2,20), ROW(3,30), ROW(1,110), ROW(2,120),ROW(3,130);

mysql> SELECT * FROM t1 WHERE t1.b < 0 OR t1.a IN (SELECT t2.a + 1 FROM t2);
+------+------+
| a    | b    |
+------+------+
|    2 |   20 |
|    3 |   30 |
+------+------+

mysql> SET @@optimizer_switch="subquery_to_derived=off";

mysql> EXPLAIN SELECT * FROM t1 WHERE t1.b < 0 OR t1.a IN (SELECT t2.a + 1 FROM t2)\G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where
*************************** 2. row ***************************
           id: 2
  select_type: DEPENDENT SUBQUERY
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using where

mysql> SET @@optimizer_switch="subquery_to_derived=on";

mysql> EXPLAIN SELECT * FROM t1 WHERE t1.b < 0 OR t1.a IN (SELECT t2.a + 1 FROM t2)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: t1
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ref
possible_keys: <auto_key0>
          key: <auto_key0>
      key_len: 9
          ref: std2.t1.a
         rows: 2
     filtered: 100.00
        Extra: Using where; Using index
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: t2
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 100.00
        Extra: Using temporary

执行EXPLAIN后，执行SHOW WARNINGS的结果显示，当启用subquery_to_derived标志时，SELECT * FROM t1 WHERE t1.b < 0 OR t1.a IN (SELECT t2.a + 1 FROM t2)将以类似于此处所示的形式重写：

SELECT a, b FROM t1
    LEFT JOIN (SELECT DISTINCT a + 1 AS e FROM t2) d
    ON t1.a = d.e
    WHERE   t1.b < 0
            OR
            d.e IS NOT NULL;

2、优化器提示（hint）
控制优化器的另一种方法是使用优化器提示（hint），该提示可以在单个语句中指定。由于优化程序提示是基于每个语句应用的，因此它比控制optimizer_switch更为灵活方便。

语法

优化器提示必须在/*+ … */注释中指定

#1、放在查询和数据更改语句的开头
SELECT /*+ ... */ ...
INSERT /*+ ... */ ...
REPLACE /*+ ... */ ...
UPDATE /*+ ... */ ...
DELETE /*+ ... */ ...

--示例
SELECT /*+ NO_RANGE_OPTIMIZATION(t3 PRIMARY, f2_idx) */ f1 FROM t3 WHERE f1 > 30 AND f1 < 33;
SELECT /*+ BKA(t1) NO_BKA(t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ NO_ICP(t1, t2) */ * FROM t1 INNER JOIN t2 WHERE ...;
SELECT /*+ SEMIJOIN(FIRSTMATCH, LOOSESCAN) */ * FROM t1 ...;
select /*+ MAX_EXECUTION_TIME */ * FROM t1 ...;

#2、在查询块的开头
(SELECT /*+ ... */ ... )
(SELECT ... ) UNION (SELECT /*+ ... */ ... )
(SELECT /*+ ... */ ... ) UNION (SELECT /*+ ... */ ... )

#3、使用EXPLAIN查看执行计划时
EXPLAIN SELECT /*+ ... */ ...
EXPLAIN UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)

#4、提示注释可以包含多个提示，但是查询块不能包含多个提示注释
SELECT /*+ BNL(t1) BKA(t2) */ ...     	  #有效
SELECT /*+ BNL(t1) */ /* BKA(t2) */ ...	  #无效

MySQL8.0版本中支持的优化器提示

从某种程度上，“修改优化器选项”属于“使用优化器提示”中的一种，因为在语句执行期间，可以用优化器提示的方式(SET_VAR)修改优化器选项。

示例：

## 关闭ICP
SELECT /*+ SET_VAR(optimizer_switch = 'index_condition_pushdown=off') */ 1;

SET_VAR应用比较广泛，我们还可以修改单个sql语句使用的内存大小。

## 增加排序缓存
SELECT /*+ SET_VAR(sort_buffer_size = 16M) */ * from ...;
SELECT /*+ SET_VAR(join_buffer_size = 32M) */ * from ...;

写在最后

在某些特定的业务中，修改优化器选项可以加速查询，在一些低频的统计查询业务中，该方法灵活且高效。但是在生产业务中，尤其在高频的前端业务中，我们不推荐此方法，因为数据发生较大变化时可能会影响执行计划作出有效改变，核心业务发版变更比较困难，所以建议通过加索引或者改写sql引导执行计划选择最佳方案。