mysql hint_提示（Hint） - 非官方 MySQL 8.0 优化指南 - 学习笔记

和在 MySQL 系统数据库里调整代价常量一样，MySQL 还提供了覆盖已选计划的方法。提示(Hint)在两种情形下很好用：

调试。EXPLAIN 展示了执行计划是如何基于已有的信息决定的，添加提示可以强制使用其他执行计划，以对比时间花费。

生产。如果调试中你注意到另一种执行计划快很多，你或许可以添加提示以提升性能。

虽然调试中可以频繁的使用提示，但生产中使用还是要谨慎。数据分布和索引总在改变，可能会因此导致维护负担，提示会一直把我们锁定在特定的执行计划中。

最佳实践是，在 MySQL 的大版本更新后要经常重审提示(例如，升级到 MySQL 9.0)。你可能发现有新加入的优化特性，一些提示可以不再使用了。

早期风格的提示

早期的 MySQL 版本中只支持一系列作为 SQL 语法扩展的提示，例如：

-- 指定连表顺序

SELECT STRAIGHT_JOIN Country.Name as CountryName, City.Name AS City

FROM Country INNER JOIN City ON City.CountryCode=Country.Code;

-- 强制使用指定索引

SELECT * FROM Country FORCE INDEX (p)

WHERE continent='Asia' and population > 5000000;

-- 忽略索引

SELECT * FROM Country IGNORE INDEX (p)

WHERE continent='Asia' and population > 5000000;

-- 建议使用指定索引

SELECT * FROM Country USE INDEX (p)

WHERE continent='Asia' and population > 5000000;

在 MySQL 8.0 中仍支持这些提示，它们已部分被 “注释风格” 的提示取代。在例子5中，我们可以看到原本不被使用的索引p，因为提示FORCE INDEX而被使用， 尽管代价比全表扫描更高。

例子5：强制使用索引，不顾代价计算

EXPLAIN FORMAT=JSON

SELECT * FROM Country FORCE INDEX (p) WHERE continent='Asia' and population > 5000000;

{

"query_block": {

"select_id": 1,

"cost_info": {

"query_cost": "152.21" # 代价比全表扫描更高

},

"table": {

"table_name": "Country",

"access_type": "range",

"possible_keys": [

"p"

],

"key": "p",

"used_key_parts": [

"Population"

],

"key_length": "4",

"rows_examined_per_scan": 108,

"rows_produced_per_join": 15,

"filtered": "14.29",

"index_condition": "(`world`.`Country`.`Population` > 5000000)",

"cost_info": {

"read_cost": "149.12",

"eval_cost": "3.09",

"prefix_cost": "152.21",

"data_read_per_join": "3K"

},

"used_columns": [

...

],

"attached_condition": "(`world`.`Country`.`Continent` = 'Asia')"

}

}

}

新的注释风格提示

MySQL 8.0 扩展了在 5.7 版本加入的注释风格提示，提供了改变连表顺序功能(和STRAIGHT_JOIN相似)。我更偏好新的注释风格，有以下 3 个原因：

新的注释风格是易读、易写的。和 SQL 的陈述语句相分离，它们暗示了如何去执行。

它们有作为“提示”而言的清晰的语义，不是去主导。这意味着如果一个提示是无法操作的，就会给出语句警告结果而不是异常错误。与之相反的旧风格FORCE INDEX，没有这个索引的话就会报错。

它们的控制粒度更细。赋予了 DBA 使用提示时更多的灵活性。

提示名

描述

BKA, NO_BKA

启用或禁用对指定表的 批量关键字访问(Batched Key Access)优化，默认是关闭的。

BNL, NO_BNL

启用或禁用对指定表的 块嵌套循环连接算法(Block Nested Loop)。

MAX_EXECUTION_TIME

设定执行时间的最大毫秒值。这个提示只影响 SELECT 语句。

MRR, NO_MRR

影响优化 多范围查找(Multi-Range Read)。

NO_ICP

影响优化 索引状态下推(Index Condition Pushdown )。

NO_RANGE_OPTIMIZATION

禁用对指定表或索引的范围相关优化。

QB_NAME

为查找的块命名。

SEMIJOIN, NO_SEMIJOIN

控制子查询的半连接策略(可选项包括去重、首次匹配、松散扫描、临时表)。

SUBQUERY

和半连接控制相似。控制子查询策略，包括把 IN 转为 EXISTS的优化。

在例子6中，指定表的范围优化被禁用了。这导致 索引p 被忽略，尽管其有很高的选择性。这个表里只有 2 行满足条件的数据。

例子6：禁用范围优化，意味着该索引无法使用

EXPLAIN FORMAT=JSON

SELECT /*+NO_RANGE_OPTIMIZATION(Country) */ * FROM Country

WHERE Population > 1000000000 AND Continent='Asia';

{

"query_block": {

"select_id": 1,

"cost_info": {

"query_cost": "56.80"

},

"table": {

"table_name": "Country",

"access_type": "ALL", # 访问方式是全表扫描

"possible_keys": [ # 不使用可行的索引，原因是范围优化被禁用

"p"

],

"rows_examined_per_scan": 239,

"rows_produced_per_join": 11,

"filtered": "4.76",

"cost_info": {

"read_cost": "54.52",

"eval_cost": "2.28",

"prefix_cost": "56.80",

"data_read_per_join": "2K"

},

"used_columns": [

...

],

"attached_condition": "((`world`.`Country`.`Continent` = 'Asia') and (`world`.`Country`.`Population` > 1000000000))"

}

}

}