MySQL Hints控制查询优化器的选择问题小结_Mysql

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

一、什么是mysql hints

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

二、为什么需要使用hints

性能调优：在某些复杂的查询场景下，优化器可能无法自动选择最优的执行计划。通过hints，我们可以手动指定一些执行策略，从而提升查询性能。
控制执行计划：当数据库中的数据分布或表结构发生变化时，优化器可能会选择不同的执行计划。使用hints可以确保查询的稳定性，即使在数据或表结构发生变化时，也能保持相同的执行计划。
解决特定问题：有时，我们可能会遇到一些特定的问题，如索引选择不当、连接顺序不佳等。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 force index (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 use index (idx_age) where age > 30;
-- force index 示例
select * from users force index (idx_age) where age > 30;

在上述示例中，我们指示mysql在查询users表时优先使用idx_age索引。

2. ignore index

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

select * from users ignore index (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 = 30 or name = 'john doe';
-- no_index_merge 示例（阻止使用索引合并）
select * from users no_index_merge where age = 30 or 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 order by age filesort;
-- 阻止使用文件排序（尽管这通常不是推荐的，因为优化器通常会选择最佳方法）
select * from users order by 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的设置。