SQL高级优化--优化器提示

最新推荐文章于 2024-09-14 13:31:30 发布

keep丶

最新推荐文章于 2024-09-14 13:31:30 发布

阅读量1.1k

点赞数 4

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_38688267/article/details/108375822

版权

mysql 专栏收录该内容

14 篇文章 5 订阅

订阅专栏

前言：
       本文目的： 本文结合实际案例讲述MySQL的Comment Syntax(注释语法)中Optimizer Hints(优化器提示)的作用和语法。绝对的干货文章哦！
       环境说明： MySQL版本：8.0.20
       版权说明： 本文由博主keep丶原创，转载请注明出处。
       原文地址： https://blog.csdn.net/qq_38688267/article/details/108375822

文章目录

优化器提示

控制优化器策略的一种方法是设置 optimizer_switch ¹ 系统变量，对该变量的更改会影响所有后续查询的执行。

控制优化器的另一种方法是使用优化器提示，该提示可以在单个语句中指定。由于优化程序提示是基于每个语句应用的，因此它们提供了比使用更好的控制语句执行计划 optimizer_switch。例如：你可以在语句中为一个表启用优化，而对另一表禁用优化。语句中的提示优先级高于 optimizer_switch变量的值。

语法

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

-- + 字符 后允许有空格。
/*+ BKA(t1) */
/*+ BNL(t1, t2) */
/*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */
/*+ QB_NAME(qb2) */

放在查询和数据更改语句的开头：

SELECT /*+ ... */ ...
INSERT /*+ ... */ ...
REPLACE /*+ ... */ ...
UPDATE /*+ ... */ ...
DELETE /*+ ... */ ...

在查询块的开头：

(SELECT /*+ ... */ ... )
(SELECT ... ) UNION (SELECT /*+ ... */ ... )
(SELECT /*+ ... */ ... ) UNION (SELECT /*+ ... */ ... )
UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)
INSERT ... SELECT /*+ ... */ ...

使用EXPLAIN查看执行计划时也能用：

EXPLAIN SELECT /*+ ... */ ...
EXPLAIN UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)

提示注释可以包含多个提示，但是查询块不能包含多个提示注释：

-- 有效
SELECT /*+ BNL(t1) BKA(t2) */ ...

--无效
SELECT /*+ BNL(t1) */ /* BKA(t2) */ ...

当提示注释包含多个提示时，存在重复和冲突的可能性。对于特定的提示类型，可能会应用其他规则，如提示说明中所述：

· 重复提示：/*+ MRR(idx1) MRR(idx1) */，MySQL会使用第一个提示并发出有关重复提示的警告。
· 冲突的提示：/*+ MRR(idx1) NO_MRR(idx1) */，MySQL会使用第一个提示并发出有关第二个冲突提示的警告。

提示名称，查询块名称和策略名称不区分大小写。
对表和索引名称的引用遵循通常的标识符区分大小写规则

列表

优化器提示适用于不同的作用域级别：

全局： 提示会影响整个语句
查询块： 提示会影响语句中的特定查询块
表级： 提示会影响查询块中的特定表
索引级： 提示会影响表中的特定索引

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

提示名称	描述	适用范围
BKA， NO_BKA	影响批量密钥访问联接处理	查询块，表
BNL， NO_BNL	MySQL 8.0.20之前的版本：影响块嵌套循环的连接处理；MySQL 8.0.18及更高版本：还影响哈希联接优化；MySQL 8.0.20及更高版本：仅影响哈希联接优化	查询块，表
DERIVED_CONDITION_PUSHDOWN， NO_DERIVED_CONDITION_PUSHDOWN	对物化派生表使用或忽略派生条件下推优化（在MySQL 8.0.22中添加）	查询块，表
GROUP_INDEX， NO_GROUP_INDEX	在GROUP BY操作中使用或忽略指定的一个或多个索引进行索引扫描（在MySQL 8.0.20中添加）	指数
HASH_JOIN， NO_HASH_JOIN	影响哈希联接优化（仅适用于MySQL 8.0.18	查询块，表
INDEX， NO_INDEX	充当的组合JOIN_INDEX， GROUP_INDEX以及 ORDER_INDEX，或作为组合 NO_JOIN_INDEX， NO_GROUP_INDEX和 NO_ORDER_INDEX（由在MySQL 8.0.20）	指数
INDEX_MERGE， NO_INDEX_MERGE	影响索引合并优化	表，索引
JOIN_FIXED_ORDER	使用FROM子句中指定的表顺序作为连接顺序	查询块
JOIN_INDEX， NO_JOIN_INDEX	对任何访问方法使用或忽略指定的索引（在MySQL 8.0.20中添加）	指数
JOIN_ORDER	使用提示中指定的表顺序作为连接顺序	查询块
JOIN_PREFIX	将提示中指定的表顺序用于连接顺序的第一张表	查询块
JOIN_SUFFIX	将提示中指定的表顺序用于联接顺序的最后一个表	查询块
MAX_EXECUTION_TIME	限制语句执行时间	全局
MERGE， NO_MERGE	影响派生表/视图合并到外部查询块中	表
MRR， NO_MRR	影响多范围读取优化	表，索引
NO_ICP	影响索引条件下推式优化	表，索引
NO_RANGE_OPTIMIZATION	影响范围优化	表，索引
ORDER_INDEX， NO_ORDER_INDEX	使用或忽略指定的一个或多个索引对行进行排序（在MySQL 8.0.20中添加）	指数
QB_NAME	为查询块分配名称	查询块
RESOURCE_GROUP	在语句执行期间设置资源组	全局
SEMIJOIN， NO_SEMIJOIN	影响半联接策略；从MySQL 8.0.17开始，这也适用于antijoins	查询块
SKIP_SCAN， NO_SKIP_SCAN	影响跳过扫描优化	表，索引
SET_VAR	在语句执行期间设置变量	全局
SUBQUERY	影响物化， IN-to-EXISTS 子查询策略	查询块

应用场景

优化器提示的应用场景很多，这里举几个小栗子给大家抛砖引玉。

为复杂查询设置临时系统变量

编码过程中不可避免的会需要编写复杂或大数据量的SQL，这时我们可以提示MySQL给这条SQL分配更多内存以加快执行速度。

-- 给复杂关联查询设置较多关联缓存
SELECT
	/*+ SET_VAL(join_buffer_size=1024 * 1024 * 2)*/
	*
FROM
	t1
	LEFT JOIN t2
	LEFT JOIN t3
	LEFT JOIN t4
......

-- 增加排序缓存
SELECT /*+ SET_VAR(sort_buffer_size = 16M) */ name FROM people ORDER BY name;

取消/开启某些优化策略

-- 关闭 Block Nested-Loop 块嵌套循环优化
SELECT /*+ NO_BKA(t1, t2) */ t1.* FROM t1 INNER JOIN t2 INNER JOIN t3;

-- 开启索引条件下推
SELECT /*+ SET_VAR(optimizer_switch = 'index_condition_pushdown=on') */ 1;

指定表关联/加载顺序

-- 这里 IN 后面的子查询没法指定别名，因此需要使用/*+ QB_NAME(subq1) */来为该子查询命名
-- 表关联/加载顺序为： t2 、 t5@subq2、 t4@subq1、 t4@subq1、 t3、 t1
SELECT
/*+ JOIN_PREFIX(t2, t5@subq2, t4@subq1)
    JOIN_ORDER(t4@subq1, t3)
    JOIN_SUFFIX(t1) */
COUNT(*) FROM t1 JOIN t2 JOIN t3
           WHERE t1.f1 IN (SELECT /*+ QB_NAME(subq1) */ f1 FROM t4)
             AND t2.f1 IN (SELECT /*+ QB_NAME(subq2) */ f1 FROM t5);

补充说明

其他注释语法

MySQL支持三种注释：

SELECT 1+1;     # This comment continues to the end of line
SELECT 1+1;     -- This comment continues to the end of line
SELECT 1 /* this is an in-line comment */ + 1;
SELECT 1 +
/*
this is a
multiple-line comment
*/
1;

MySQL Server还支持/* */注释的另外一个变体/*! ...*/。MySQL服务器将像其他任何SQL语句一样解析并执行注释中的代码，但是其他SQL Server将忽略这些扩展。例如，MySQL服务器在以下语句中识别出STRAIGHT_JOIN关键字，而其他服务器则不能：

SELECT /*! STRAIGHT_JOIN */ col1 FROM table1,table2 WHERE ...

如果在! 字符后添加版本号，则仅当MySQL版本大于或等于指定的版本号时，才会执行注释中的语法。KEY_BLOCK_SIZE以下注释中的关键字仅由MySQL 5.1.10或更高版本的服务器执行：

CREATE TABLE t1(a INT, KEY (a)) /*!50110 KEY_BLOCK_SIZE=1024 */;

optimizer_switch

使用optimizer_switch系统变量可以控制优化程序的行为。该变量的值是一组指标，每个指标的值都为on或off指示相应的优化器行为是启用还是禁用。此变量具有全局值和会话值，可以在运行时更改。可以在服务器启动时设置全局默认值。

基本操作如下，详细请阅读官方文档：传送门

-- 查看优化器选项
select @@optimizer_switch;

-- 设置优化器选项
set @@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';