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

前言：        本文目的： 本文结合实际案例讲述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。例如：你可以在语句中为一个表启用优化，而对另一表禁用优化。语句中的提示优先级高于 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_BNLMySQL 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';

 

总结

       优化器提示的功能非常强大，熟练掌握之后能够让我们的SQL性能得到更进一步的提升。希望这篇文章对大家有所帮助。