MySQL 8.0 Optimizer Hints

原创 CuiHulong 2023-02-06

2810

Optimizer优化器是关系数据库的重要模块，它决定SQL执行计划的优劣。在瞬息万变生产数据库中，影响优化器的因素有很多（数据变化和估计准确性等因素），它不能总是产出最优的执行计划。这时就需要DBA通过Hints提示优化SQL语句。可以说是常用的手段。

Hints方式是向优化器关键决策点提供具体决策，就可以规避错误的执行计划，能够尽快地解决执行计划走错的问题。

目前MySQL当中Hints使用场景，主要是初期系统上线之前SQL的优化，后期上线之后变化导致的执行计划变更。

当然，绝大部分运行当中的执行计划还是比较准确的。

Optimizer Hints

优化提示应用于不同的作用域级别:4个级别，每个级别都有不一样的策略。

级别	说明
Global	影响整个全局语句
Query block	影响语句中的特定查询块
Table-leve	影响查询块中的特定表
Index-level	影响表中的特定索引

可设置优化器策略以及它们应用的范围：

从上诉可以总结出，优化器Hint涉及的内容：

连接顺序；
表级优先；
索引选择；
子查询；
语句执行时间；
变量设置；
资源组；
不同算法应用；
命名查询块；

Hint实现

MySQL中优化器Hint 必须在/+… /注释。也就是说，优化器Hint使用/+…/ 风格的注释语法：

/*+ BKA(t1) */
/*+ BNL(t1, t2) */
/*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */
/*+ SET_VAR(join_buffer_size = 8M) */
/*+ MAX_EXECUTION_TIME(1000) */
/*+ BNL(t1) BKA(t2) */
/*+ QB_NAME(qb1) */

优化器Hint在SELECT, UPDATE, INSERT, REPLACE, DELETE 语句是使用。当然EXPLAIN也是支持的。
重复:对于/*+ MRR(idx1) MRR(idx1) */这样的提示，MySQL使用第一个提示并发出关于重复提示的警告。
查询块名称是标识符，并遵循关于哪些名称有效以及如何引用它们的常规规则
提示名称、查询块名称和策略名称不区分大小写。对表和索引名的引用遵循大小写敏感规(lower_case_table_names)
可通过SHOW WARNINGS查看一些提示发出的冲突，错误等警告。

Index-Level Hint

对于索引的hint使用需要注意，前面是表明后面是索引名，也可以是多个组合策略

hint_name([@query_block_name] tbl_name [index_name [, index_name] ...])
hint_name(tbl_name@query_block_name [index_name [, index_name] ...])

语法实例：

#多个索引
SELECT /*+ NO_INDEX(t1 PRIMARY, i_a, i_b, i_c)*/    * 
FROM t1
  WHERE a = 1 AND b = 2 AND c = 3 AND d = 4;

#多个hint
SELECT /*+ INDEX_MERGE(t1 i_a, i_b) NO_ORDER_INDEX(t1 i_b) */  *
FROM t1
  WHERE a = 1 AND b = 2 AND c = 3 AND d = 4;

#session级别 optimizer结合
SET optimizer_switch='index_merge_intersection=off';
SELECT /*+ INDEX_MERGE(t1 i_b) */ * FROM t1 
WHERE b = 1 AND c = 2 AND d = 3; 

#纯index hint结合
SELECT /*+ INDEX_MERGE(t1 i_a, i_b, i_c) */ * 
FROM t1 IGNORE INDEX(i_a)   
WHERE b = 1 AND c = 2 AND d = 3;

是否生效：

mysql> EXPLAIN SELECT  en_name  FROM city;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | index | NULL          | idx_name | 141     | NULL |   26 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT /*+ NO_INDEX(city idx_name,PRIMARY) */ en_name  FROM city;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   26 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

当optimizer hint 和index hint 同时存在的时候 optimizer > index

mysql> EXPLAIN SELECT  en_name  
       FROM city USE INDEX(idx_name) 
       WHERE en_name='Guba';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ref  | idx_name      | idx_name | 141     | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT /*+ NO_INDEX(city idx_name) */ en_name  
       FROM city  USE INDEX(idx_name)  
       WHERE en_name='Guba';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   26 |     3.85 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

Variable-Setting Hint

SET_VAR方式是临时设置变量的会话值，在单个语句的持续时间内有效。在MySQL早起版本就支持session级别的变量，在退出这个session之前一直生效。现在支持SET_VAR临时方式，带来了更多地便利。

SELECT /*+ SET_VAR(sort_buffer_size = 16M) */ * FROM city ORDER BY en_name;
INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO city  VALUES(1);
SELECT /*+ SET_VAR(optimizer_switch='use_invisible_indexes=off') */ 1;
SELECT /*+ SET_VAR(optimizer_switch='mrr_cost_based=off')
           SET_VAR(max_heap_table_size=1G) */ 1;

在sql语法中增加SET_VAR语法，动态调整部分参数，不需要保存和恢复变量值,有利于提升语句性能。

mysql> SELECT @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT /*+ SET_VAR(unique_checks=OFF) */ @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
|               0 |
+-----------------+
1 row in set (0.00 sec)

mysql> SELECT @@unique_checks;
+-----------------+
| @@unique_checks |
+-----------------+
|               1 |
+-----------------+
1 row in set (0.00 sec)

并非所有会话变量都允许用于SET_VAR
单独的系统变量描述表明每个变量是否可提示，如出现WARNING需要查看下：

mysql> SELECT /*+ SET_VAR(collation_server = 'utf8mb4') */ 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 3637 | Variable 'collation_server' cannot be set using SET_VAR hint. |
+---------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)

如果同一个语句中出现了几个具有相同变量名的提示，则应用第一个提示，忽略其他提示并发出警告。
Replication忽略复制语句中的SET_VAR提示，以避免潜在的安全问题。

Resource Group Hint

MySQL8.0之后，由于Resource Group特性的引入，可以来通过资源组的方式修改线程的优先级以及所能使用的资源，可以指定不同的线程使用特定的资源。
hint支持RESOURCE_GROUP：SELECT, INSERT, REPLACE, UPDATE, DELETE 语句

#查看资源组
mysql> select * from information_schema.resource_groups;
+---------------------+---------------------+------------------------+--------------------+-----------------+
| RESOURCE_GROUP_NAME | RESOURCE_GROUP_TYPE | RESOURCE_GROUP_ENABLED | VCPU_IDS           | THREAD_PRIORITY |
+---------------------+---------------------+------------------------+--------------------+-----------------+
| USR_default         | USER                |                      1 | 0x302D30           |               0 |
| SYS_default         | SYSTEM              |                      1 | 0x302D30           |               0 |
| SYS_internal        | SYSTEM              |                      1 | 0x302D30           |               0 |
+---------------------+---------------------+------------------------+--------------------+-----------------+
3 rows in set (0.00 sec)

#指定资源组，执行账号需要SUPER OR RESOURCE_GROUP_ADMIN OR RESOURCE_GROUP_USER权限
mysql> SELECT /*+ RESOURCE_GROUP(USR_default) */ *  FROM t1;

Subquery Optimizer Hint

目前支持半连接和子查询策略。通常使用IN 或 EXISTS语句中使用。

SEMIJOIN半连接策略里包含:DUPSWEEDOUT, FIRSTMATCH, LOOSESCAN, MATERIALIZATION

mysql> EXPLAIN SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * 
       FROM city    
       WHERE CountryCode  IN (SELECT /*+ QB_NAME(subq1) */ Code  FROM country);
+----+--------------+-------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
| id | select_type  | table       | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                      |
+----+--------------+-------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
|  1 | SIMPLE       | <subquery2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |   100.00 | NULL                                       |
|  1 | SIMPLE       | city        | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   26 |    10.00 | Using where; Using join buffer (hash join) |
|  2 | MATERIALIZED | country     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL                                       |
+----+--------------+-------------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> EXPLAINSELECT /*+ NO_SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * 
       FROM city 
       WHERE CountryCode  IN (SELECT /*+ QB_NAME(subq1) */ Code  FROM country);
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                                        |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------------------------+
|  1 | SIMPLE      | city    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   26 |   100.00 | NULL                                                         |
|  1 | SIMPLE      | country | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where; FirstMatch(city); Using join buffer (hash join) |
+----+-------------+---------+---------

SUBQUERY策略里包含:INTOEXISTS, MATERIALIZATION

mysql> EXPLAIN SELECT id, Name,
             CountryCode IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ Code FROM country) 
       FROM city;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | PRIMARY     | city    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   26 |   100.00 | NULL  |
|  2 | SUBQUERY    | country | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT id, Name  
       FROM city 
       WHERE CountryCode  IN (
          SELECT /*+ SUBQUERY(INTOEXISTS) */ CountryCode  FROM country);
+----+--------------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type        | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+--------------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | PRIMARY            | city    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   26 |   100.00 | Using where |
|  2 | DEPENDENT SUBQUERY | country | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    5 |   100.00 | Using where |
+----+--------------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
2 rows in set, 2 warnings (0.00 sec)

Optimizer Hints for Naming Query Blocks

使用QB_NAME提示，它会为它所在的查询块分配名称。
QB_NAME提示可用于显式地明确查询阻塞其他提示应用的对象。还允许在单个提示注释中指定所有非查询块名称提示，以便更容易理解复杂的语句。

SELECT /*+ QB_NAME(qb1) MRR(@qb1 t1) BKA(@qb2) NO_MRR(@qb3t1 idx1, id2) */ ...
  FROM (SELECT /*+ QB_NAME(qb2) */ ...
  FROM (SELECT /*+ QB_NAME(qb3) */ ... FROM ...)) ...

SELECT /*+ BKA(@`my hint name`) */ ...
  FROM (SELECT /*+ QB_NAME(`my hint name`) */ ...) ...

Execution Time Hint

指定MAX_EXECUTION_TIME执行的时间设置了一个限制N（毫秒）。只允许用于SELECT语句。不支持存储过程。

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE ...

Join-Order Optimizer Hints

优化器连接表的顺序。就是驱动表和被驱动表的选择。
连接hint包含：JOIN_FIXED_ORDER，JOIN_ORDER，JOIN_PREFIX，JOIN_SUFFIX

其语法:

hint_name([@query_block_name] tbl_name [, tbl_name] ...)
hint_name(tbl_name[@query_block_name] [, tbl_name[@query_block_name]] ...)

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.id IN (SELECT /*+ QB_NAME(subq1) */ id FROM t4)
             AND t2.id IN (SELECT /*+ QB_NAME(subq2) */ id FROM t5);

Table-Level Optimizer Hints

表级提示影响：

使用块嵌套循环(BNL)和批处理密钥访问(BKA)连接处理算法
派生表、视图引用或公共表表达式都应该合并到外部查询块中，或者使用内部临时表具体化。
使用派生表条件下推优化

表级别hint包含：
BKA, NO_BKA，BNL, NO_BNL，DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN，HASH_JOIN, NO_HASH_JOIN，MERGE, NO_MERGE

SELECT /*+ NO_BKA(t1, t2) */ t1.* FROM t1 INNER JOIN t2 INNER JOIN t3;
SELECT /*+ NO_BNL() BKA(t1) */ t1.* FROM t1 INNER JOIN t2 INNER JOIN t3;
SELECT /*+ NO_MERGE(dt) */ * FROM (SELECT * FROM t1) AS dt;

备注：表级别Hint场景下，考虑的因素还是驱动表，多表合并等。

总结

Optimizer Hints是MySQL8.0优化SQL的另一个深度技术手段。语句级别实现，多种组合策略选择，更深入的控制了优化器成本模型决策。

参考：https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html
示例数据库脚本：

CREATE TABLE `city` (
  `ID` int NOT NULL AUTO_INCREMENT,
  `Name` char(35) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `en_name` char(35) COLLATE utf8mb4_bin DEFAULT NULL,
  `CountryCode` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `District` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL DEFAULT '',
  `Population` int NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `idx_name` (`en_name`)
) ENGINE=InnoDB;

CREATE TABLE `country` (
  `Code` char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `Continent` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL
) ENGINE=InnoDB;

INSERT INTO `city` VALUES (1,'上海','shanghai','001','100021',1),(2,'北京','beijing','001','100031',2),(3,'南京','nanjing','001','100041',3),(
4,'深圳','shenzhen','001','100051',4),(5,'成都','chengdu','001','100061',5),(6,'云南','yunnan','001','100071',5),(8,'桂林','guilin','001','100
081',6),(9,'西安','xian','001','100091',7),(10,'黑龙江','heilongjiang','001','100092',7),(11,'芬兰','fenlan','002','200001',3),(12,'瑞典','rui
dian','006','200001',1),(13,'冰岛','bingdao','002','200001',1),(14,'俄罗斯','rosia','002','200001',1),(15,'阿尔及利亚','Algeria','003','200001
',8),(16,'中非','Central Africa','003','300001',9),(17,'埃及','Egypt','003','300024',9),(18,'利比亚','Libya','003','300007',9),(19,'美国','usa
','004','400002',1),(20,'加拿大','Canada','004','400003',1),(21,'墨西哥','Mexico','004','400012',6),(22,'古巴','Cuba','004','400070',5),(23,'
阿根廷','Argentina','004','400090',7),(24,'巴西','Brazil','004','400004',7),(25,'澳大利亚','Australia','005','500002',7),(26,'新西兰','New Zea
land','005','500004',5),(27,'所罗门群岛','Solomon Islands','005','500010',10);

INSERT INTO `country` VALUES ('001','Asia'),('002','Europe'),('003','Africa'),('004','America'),('005','Oceania');

墨力计划 mysql

最后修改时间：2023-02-28 17:51:42

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者