MySQL之查询优化器的提示

一时失言乱红尘 2022-05-15 15:28 598阅读 0赞

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

### 文章目录 ###

*  HIGN\_PRIORITY和LOW\_PRIORITY
 *  DELAYED
 *  STRAIGHT\_JOIN
 *  SQL\_SMALL\_RESULT和SQL\_BIG\_RESULT
 *  SQL\_BUFFER\_RESULT
 *  SQL\_CACHE和SQL\_NO\_CACHE
 *  SQL\_CALC\_FOUND\_ROWS
 *  FOR UPDATE和LOCK IN SHARE MODE
 *  USE INDEX、IGNORE INDEX和FORCE INDEX
 *  optimizer\_search\_depth
 *  optimizer\_prune\_level
 *  optimizer\_switch

# 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>**