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秒

控制优化器策略的两种方法

• 修改优化器选项
• 使用优化器提示（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)

# 示例
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> 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:
partitions: NULL
type: ref
possible_keys:
key:
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 /*+ ... */ ...

#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引导执行计划选择最佳方案。