MySQL Hints：控制查询优化器的选择

** 码到三十五 ：**
个人主页

MySQL Hints是优化数据库查询性能的一种强大工具。它们允许开发者在SQL查询中嵌入指令，以影响MySQL优化器的决策过程。在某些情况下，优化器可能无法选择最佳的查询执行计划，这时我们可以使用Hints来引导优化器做出更好的选择。

目录

一、什么是MySQL Hints

MySQL Hints是一组特殊的注释或指令，可以直接嵌入到SQL查询中，以改变MySQL优化器的默认行为。这些Hints通常被用于解决性能问题，或者当开发者比优化器更了解数据分布和查询特性时，来指导优化器选择更好的查询计划。

二、为什么需要使用Hints

1. 性能调优：在某些复杂的查询场景下，优化器可能无法自动选择最优的执行计划。通过Hints，我们可以手动指定一些执行策略，从而提升查询性能。
2. 控制执行计划：当数据库中的数据分布或表结构发生变化时，优化器可能会选择不同的执行计划。使用Hints可以确保查询的稳定性，即使在数据或表结构发生变化时，也能保持相同的执行计划。
3. 解决特定问题：有时，我们可能会遇到一些特定的问题，如索引选择不当、连接顺序不佳等。Hints提供了一种快速解决问题的方法，而无需更改表结构或重写查询。

三、如何使用Hints

Hints是通过在SQL语句前添加特殊格式的注释来使用的。通常的格式是

/*+ HintName(parameters) */

。这些Hints只对紧跟其后的SQL语句有效，并且不会影响其他查询。以下是如何在SQL语句中使用Hints的详细步骤：

1. 确定需要使用的Hint

首先，你需要确定你想要使用的Hint。这通常基于你对查询性能的分析和对MySQL优化器行为的理解。例如，如果你发现优化器没有选择你认为最优的索引，你可能会想要使用

FORCE INDEX

或

IGNORE INDEX

等Hints。

2. 编写Hint注释

在SQL语句之前，你需要添加一个特殊格式的注释来包含你的Hint。这个注释的格式是

/*+ HintName(parameters) */

，其中

HintName

是你想要使用的Hint的名称，

parameters

是该Hint所需的任何参数。

例如，如果你想要强制优化器使用特定的索引，可以这样写：

/*+ FORCE INDEX(table_name idx_name) */

在这里，

table_name

是你想要应用Hint的表的名称，而

idx_name

是你想要强制优化器使用的索引的名称。

3. 将Hint注释与SQL语句结合

一旦你编写了Hint注释，你需要将它放在SQL语句之前，并确保它们之间没有换行或其他字符。这样，优化器就能识别并应用你的Hint。

一个完整的带有Hint的SQL查询像这样：

/*+ FORCE INDEX(my_table my_index) */SELECT*FROM my_table WHERE my_column ='value';

在这个例子中，

FORCE INDEX

Hint告诉优化器在执行查询时强制使用

my_table

上的

my_index

索引。

4. 测试和验证

在应用了Hint之后，你应该测试查询以确保Hint产生了预期的效果。你可以使用

EXPLAIN

语句来查看查询的执行计划，并确认优化器是否按照你的Hint来执行查询。

EXPLAIN/*+ FORCE INDEX(my_table my_index) */SELECT*FROM my_table WHERE my_column ='value';

这将显示查询的执行计划，并允许你验证

FORCE INDEX

Hint是否已被正确应用。

语法说明

值得注意的是，/*+ … */ 这种注释语法是Oracle数据库中的一种标准方式来提供优化器hints，但在MySQL中，这种语法并不是官方的。在MySQL中，你通常不需要使用特殊的注释语法来提供FORCE INDEX hint。相反，你可以直接在查询中使用它，如下所示：

SELECT*FROM my_table FORCEINDEX(my_index)WHERE my_column ='value';

FORCE INDEX (my_index) 直接与SELECT语句结合，告诉MySQL优化器在执行查询时强制使用my_index索引。这是MySQL支持的标准语法，而不需要使用特殊的注释格式。

总结来说，FORCE INDEX 必须与查询语句一起使用，而不是作为一个独立的语句执行。在MySQL中，你不需要使用/*+ … */注释语法来提供这个hint，而是可以直接在查询中指定。如果你在使用其他数据库系统（如Oracle），那么可能需要使用该系统的特定注释语法来提供优化器hints。

四、常用的MySQL Hints

以下是对一些常用的MySQL Hints的详细介绍以及相应的代码：

1.

USE INDEX

和

FORCE INDEX

这两个Hints用于指定查询时要使用的索引。

USE INDEX

是建议性的，而

FORCE INDEX

更为强制。

-- USE INDEX 示例SELECT*FROM users USEINDEX(idx_age)WHERE age >30;-- FORCE INDEX 示例SELECT*FROM users FORCEINDEX(idx_age)WHERE age >30;

在上述示例中，我们指示MySQL在查询

users

表时优先使用

idx_age

索引。

2.

IGNORE INDEX

这个Hint用于指示MySQL在查询时忽略指定的索引。

SELECT*FROM users IGNOREINDEX(idx_age)WHERE name ='John Doe';

在这个示例中，我们告诉MySQL在执行查询时忽略

idx_age

索引。

3.

STRAIGHT_JOIN

STRAIGHT_JOIN

用于强制MySQL按照指定的表顺序进行JOIN操作，而不是由优化器自动选择。

SELECT*FROM users STRAIGHT_JOIN orders ON users.id = orders.user_id;

在这个示例中，我们强制MySQL先扫描

users

表，然后再与

orders

表进行JOIN。

4.

SQL_NO_CACHE

这个Hint用于指示MySQL不使用查询缓存，确保每次查询都直接访问数据库。

SELECT SQL_NO_CACHE *FROM users WHERE age >30;

在这个示例中，我们确保查询结果不是从缓存中获取的，而是直接查询数据库。

5.

INDEX_MERGE

和

NO_INDEX_MERGE

这两个Hints影响优化器是否使用索引合并策略。

-- INDEX_MERGE 示例（鼓励使用索引合并）SELECT*FROM users INDEX_MERGE (idx_age, idx_name)WHERE age =30OR name ='John Doe';-- NO_INDEX_MERGE 示例（阻止使用索引合并）SELECT*FROM users NO_INDEX_MERGE WHERE age =30OR name ='John Doe';

在

INDEX_MERGE

示例中，我们鼓励优化器考虑合并

idx_age

和

idx_name

索引来加速查询。在

NO_INDEX_MERGE

示例中，我们阻止优化器使用索引合并。

6. **

JOIN_FIXED_ORDER

**

• 作用：强制MySQL按照查询中指定的表顺序进行JOIN操作，不进行顺序的优化调整。

SELECT*FROM table1 JOIN_FIXED_ORDER JOIN table2 ON table1.id = table2.table1_id;

7. **

BLOCK_NESTED_LOOP

**, **

BATCHED_KEY_ACCESS

**, **

NO_BNL

**, 和 **

NO_BKA

**

• 这些Hints影响JOIN操作的执行策略。

-- BLOCK_NESTED_LOOP 示例SELECT*FROM users a BLOCK_NESTED_LOOP JOIN orders b ON a.id = b.user_id;-- BATCHED_KEY_ACCESS 示例SELECT*FROM users a BATCHED_KEY_ACCESS JOIN orders b ON a.id = b.user_id;-- NO_BNL 示例SELECT*FROM users a NO_BNL JOIN orders b ON a.id = b.user_id;-- NO_BKA 示例SELECT*FROM users a NO_BKA JOIN orders b ON a.id = b.user_id;

8. **

MRR

** 和 **

NO_MRR

**

• MRR 作用：鼓励优化器使用多范围读取优化。
• NO_MRR 作用：阻止优化器使用多范围读取优化。

-- MRR 示例SELECT*FROM users WHERE id IN(1,3,5)PROCEDURE ANALYSE() MRR;-- NO_MRR 示例SELECT*FROM users WHERE id IN(1,3,5)PROCEDURE ANALYSE() NO_MRR;

注意：

PROCEDURE ANALYSE()

是一个诊断过程，通常与

MRR

和

NO_MRR

一起使用来分析和优化查询，但它在实际应用中并不常见。

9. **

FILESORT

** 和 **

NO_FILESORT

**

-- 强制使用文件排序SELECT*FROM users ORDERBY age FILESORT;-- 阻止使用文件排序（尽管这通常不是推荐的，因为优化器通常会选择最佳方法）SELECT*FROM users ORDERBY age NO_FILESORT;

10. **

SUBQUERY

** 和 **

NO_SUBQUERY

**

-- 鼓励优化器保留子查询SELECT*FROM users WHERE id IN(SELECT user_id FROM orders WHERE amount >100) SUBQUERY;-- 鼓励优化器不使用子查询，可能转换为JOIN操作SELECT*FROM users WHERE id IN(SELECT user_id FROM orders WHERE amount >100) NO_SUBQUERY;

11. **

DERIVED_MERGE

** 和 **

NO_DERIVED_MERGE

**

-- 鼓励优化器合并派生表SELECT*FROM(SELECT*FROM users WHERE age >25)AS derived1 DERIVED_MERGE JOIN orders ON derived1.id = orders.user_id;-- 阻止优化器合并派生表SELECT*FROM(SELECT*FROM users WHERE age >25)AS derived1 NO_DERIVED_MERGE JOIN orders ON derived1.id = orders.user_id;

优化器的Hints是MySQL中一种特殊的注释语法，用于向查询优化器提供关于如何执行SQL查询的建议或指令。这些Hints为开发者提供了一种机制，以便在必要时能够更精细地控制查询的执行计划，尤其是在优化器自动选择的计划不是最优的情况下。

五、优化器Hints与

optimizer_switch

的区别

• optimizer_switch：这是一个系统变量，通过它可以开启或关闭某些优化器的特性或策略。改变这个变量会影响所有后续的查询执行。因此，如果你需要对不同的查询应用不同的优化策略，你需要在每个查询之前更改optimizer_switch，这在实际操作中可能会很不方便。
• 优化器Hints：与optimizer_switch不同，优化器Hints允许你在单个SQL语句中指定优化策略。这种方法提供了更精细的控制，因为你可以针对每个查询或查询中的特定表指定不同的优化策略。此外，语句中的Hints会覆盖optimizer_switch的设置。

六、使用Hints的注意事项

1. 谨慎使用：过度或不当地使用Hints可能会导致性能下降，因为它们可能会覆盖优化器的智能决策。
2. 测试和验证：在应用Hints之前和之后，都要对查询性能进行彻底的测试，以确保它们确实带来了预期的提升。
3. 版本兼容性：不是所有的MySQL版本都支持所有的Hints，因此在使用前要检查你的MySQL版本是否支持所需的Hints。
4. 可维护性：在SQL查询中嵌入Hints可能会降低代码的可读性和可维护性。确保团队成员都了解并同意使用这些Hints。
5. 监控和调优：即使使用了Hints，也应该定期监控查询性能，并根据需要进行调整。

七、结语

MySQL Hints是一种强大的工具，可以帮助我们解决复杂的查询性能问题。然而，它们应该谨慎使用，并且总是与彻底的测试和验证相结合。通过正确使用Hints，我们可以引导MySQL优化器做出更明智的决策，从而提高数据库查询的性能和稳定性。

参考: https://dev.mysql.com/doc/refman/8.0/en/controlling-optimizer.html

听说...关注下面公众号的人都变牛了，纯技术，纯干货 !