MySQL之查询优化器的提示

如果对MySQL选择的执行计划不满意，可以使用优化器的几个提示（hint）来控制最终的执行计划。

HIGN_PRIORITY和LOW_PRIORITY

• 这个提示告诉MySQL，当多个语句访问同一个表的时候，那些语句的优先级相对高些，那些语句的优先级相对低些。
• HIGN_PRIORITY用于SElECT语句的时候，MySQL会将其重新调度到其他正在等待表锁以便修改数据的语句前面。实际上是将其放在表的队列的最前面。用于INSERT语句的时候，其效果只是简单的抵消了全局LOW_PRIORITY设置对该语句的影响。
• LOW_PRIORITY提示用作SELECT、 INSERT、UPDATE、DELETE语句。和HIGH_PRIORITY正好相反，它会让语句一直处于等待状态，只要队列中还有需要访问同一个表的语句。
• 这两个提示只是简单地控制了MySQL访问某个数据表的队列顺序。
• 这两个提示只对使用表锁的存储引擎有效。

DELAYED

这个提示对INSERT和REPLACE有效。MySQL会将使用该提示的语句立即返回给客户端，并将插入的数据放入到缓存区，然后在表空闲的时候批量将数据写入。适用于日志系统，或者是需要写入大量数据但是客户端却不需要等待单条语句完成I/O的应用。
这个用法的限制是：并不是所有的存储引擎都支持这样的做法；并且该提示会导致LAST_INSERT_ID()无法正常工作。

STRAIGHT_JOIN

• 可以放置在SELECT关键字之后或者任何两个联表的名字之间。第一个用法是让查询中所有的表按照在语句中出现的顺序进行关联。第二个用法是固定其前后两个表的关联顺序。
• 适用于当MySQL没能选择正确的关联顺序的时候，或者由于可能的顺序太多导致MySQL无法正确评估所有的关联顺序的时候（MySQL可能会花大量时间在”statistics“状态，加上这个提示则会大大减少优化器的搜索空间）。

SQL_SMALL_RESULT和SQL_BIG_RESULT

• 这两个提示只对SELECT语句有效。它们告诉优化器对GROUP BY或者DISTINCT查询如何使用临时表及排序。SQL_SMALL_RESULT表示结果集很小，可以将结果集放到内存中的索引临时表，以避免排序操作。如果是SQL_BIG_RESULT,则表示结果集很大，建议使用磁盘临时表做排序操作。

SQL_BUFFER_RESULT

这个提示告诉优化器将查询结果放入到一个临时表，然后尽可能快地释放表锁。

SQL_CACHE和SQL_NO_CACHE

这个提示告诉MySQL这个结果集是否应该缓存在查询缓存中。

SQL_CALC_FOUND_ROWS

它会让MySQL返回的结果集中包含更多的信息。查询中加上该提示会计算去除LIMIT子句后这个查询要返回的结果集，而实际上只返回LIMIT要求的结果集。不建议使用。

FOR UPDATE和LOCK IN SHARE MODE

• 这两个提示主要控制SELECT语句的锁机制，但只对实现了行级锁的存储引擎有效。使用该提示会对符合查询条件的数据行加锁。使用INSERT…SELECT是不需要这两个提示的，因为MySQL会默认给这些记录加上读锁。
• 唯一内置支持这两个提示的引擎就是InnoDB。这两个提示会让某些优化无法正常使用，例如索引覆盖扫描。InnoDB无法在不访问主键的情况下排他地锁定行，因为行的版本信息保存在主键中。
• 应该尽量地避免使用这两个提示，它们很容易造成锁争用的问题。

USE INDEX、IGNORE INDEX和FORCE INDEX

• 这几个提示会告诉优化器使用或不使用那些索引来查询记录。可以通过新增选项FOR ORDER BY和FOR GROUP BY来指定是否对排序和分组有效。
• FORCE INDEX和USE INDEX的唯一区别是：FORCE INDEX会告诉优化器全表扫描的 成本远远高于索引扫描，哪怕实际上改索引用处不大。

optimizer_search_depth

这个参数控制优化器在穷举执行计划时的限度。如果查询长时间处于”Statistics“状态，可以考虑调低此参数。

optimizer_prune_level

该参数默认是打开的，这让优化器根据需要扫描的行数来决定是否跳过某些执行计划。

optimizer_switch

包含了一下开启/关闭优化器特性的标志位。例如，可以通过这个参数来控制是否禁用索引合并的特性。

---

参考：<高性能MySQL>