mysql 8.0 optimizer hints -m6米乐安卓版下载

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

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

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

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

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

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

从上诉可以总结出，优化器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       |  | 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');