MySQL的HINT用法

 

一、mysql常用的hint

 

对于经常使用oracle的朋友可能知道，oracle的hint功能种类很多，对于优化sql语句提供了很多方法。同样，在mysql里，也有类似的hint功能。下面介绍一些常用的。

 

 

强制索引 FORCE INDEX

 

SELECT * FROM TABLE1 FORCE INDEX (FIELD1) …

以上的SQL语句只使用建立在FIELD1上的索引，而不使用其它字段上的索引。

 

忽略索引 IGNORE INDEX

SELECT * FROM TABLE1 IGNORE INDEX (FIELD1, FIELD2) …

在上面的SQL语句中，TABLE1表中FIELD1和FIELD2上的索引不被使用。

 

 

关闭查询缓冲 SQL_NO_CACHE

 

SELECT SQL_NO_CACHE field1, field2 FROM TABLE1;

有一些SQL语句需要实时地查询数据，或者并不经常使用（可能一天就执行一两次）,这样就需要把缓冲关了,不管这条SQL语句是否被执行过，服务器都不会在缓冲区中查找，每次都会执行它。

 

强制查询缓冲 SQL_CACHE

 

SELECT SQL_CALHE * FROM TABLE1;

如果在my.ini中的query_cache_type设成2，这样只有在使用了SQL_CACHE后，才使用查询缓冲。

 

 

优先操作 HIGH_PRIORITY

HIGH_PRIORITY可以使用在select和insert操作中，让MYSQL知道，这个操作优先进行。

SELECT HIGH_PRIORITY * FROM TABLE1;

 

 

滞后操作 LOW_PRIORITY

LOW_PRIORITY可以使用在insert和update操作中，让mysql知道，这个操作滞后。

update LOW_PRIORITY table1 set field1= where field1= …

 

 

延时插入 INSERT DELAYED

INSERT DELAYED INTO table1 set field1= …

INSERT DELAYED INTO，是客户端提交数据给MySQL，MySQL返回OK状态给客户端。而这是并不是已经将数据插入表，而是存储在内存里面等待排队。当mysql

 

有空余时，再插入。另一个重要的好处是，来自许多客户端的插入被集中在一起，并被编写入一个块。这比执行许多独立的插入要快很多。坏处是，不能返回自动递增的ID，以及系统崩溃时，MySQL还没有来得及插入数据的话，这些数据将会丢失。

 

强制连接顺序 STRAIGHT_JOIN

SELECT TABLE1.FIELD1, TABLE2.FIELD2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …

由上面的SQL语句可知，通过STRAIGHT_JOIN强迫MySQL按TABLE1、TABLE2的顺序连接表。如果你认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就可以通过STRAIGHT_JOIN来确定连接顺序。

 

 

强制使用临时表 SQL_BUFFER_RESULT

SELECT SQL_BUFFER_RESULT * FROM TABLE1 WHERE …

当我们查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT.选项强制将结果集放到临时表中，这样就可以很快地释放MySQL的表锁（这样其它的SQL语句就可以对这些记录进行查询了），并且可以长时间地为客户端提供大记录集。

 

 

分组使用临时表 SQL_BIG_RESULT和SQL_SMALL_RESULT

 

SELECT SQL_BUFFER_RESULT FIELD1, COUNT(*) FROM TABLE1 GROUP BY FIELD1;

一般用于分组或DISTINCT关键字，这个选项通知MySQL，如果有必要，就将查询结果放到临时表中，甚至在临时表中进行排序。SQL_SMALL_RESULT比起SQL_BIG_RESULT差不多，很少使用。

 

 

二、优化器hint

 

1、简单规则

禁用优化会阻止优化器使用它。启用优化意味着，如果优化器适用于语句执行，则它可以自由使用该策略，而不是优化器必然会使用它。

 

MySQL支持SQL语句中的注释，优化器提示必须在/*+ ... */注释中指定。例如：

/*+ BKA(t1) */

/*+ BNL(t1, t2) */

/*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */

/*+ QB_NAME(qb2) */

 

+ 符号后允许有空格。

 

（1）在查询和数据更改语句的开头：

 

SELECT /*+ ... */ ...

INSERT /*+ ... */ ...

REPLACE /*+ ... */ ...

UPDATE /*+ ... */ ...

DELETE /*+ ... */ ...

 

（2）查询修改语句块中

 

(SELECT /*+ ... */ ... )

(SELECT ... ) UNION (SELECT /*+ ... */ ... )

(SELECT /*+ ... */ ... ) UNION (SELECT /*+ ... */ ... )

UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)

INSERT ... SELECT /*+ ... */ ...

 

（3）explain中使用

EXPLAIN SELECT /*+ ... */ ...

EXPLAIN UPDATE ... WHERE x IN (SELECT /*+ ... */ ...)

 

EXPLAIN用来查看优化器提示如何影响执行计划。EXPLAIN之后SHOW WARNINGS显示的扩展输出指示使用了哪些提示。不显示忽略的提示。

 

 

提示注释可以包含多个提示，但是查询块不能包含多个提示注释。这是有效的：

SELECT /*+ BNL(t1) BKA(t2) */ ...

 

但这是无效的：

SELECT /*+ BNL(t1) */ /* BKA(t2) */ ...

 

当提示注释包含多个提示时，存在重复和冲突的可能性。以下一般准则适用。对于特定的提示类型，可能会应用其他规则。

 

重复提示：对于诸如的提示/*+ MRR(idx1) MRR(idx1) */，MySQL使用第一个提示并发出有关重复提示的警告。

冲突的提示：对于诸如的提示/*+ MRR(idx1) NO_MRR(idx1) */，MySQL使用第一个提示，并发出有关第二个冲突提示的警告。

 

2、表级提示

 

BKA， NO_BKA：为指定的表启用或禁用BKA。

BNL， NO_BNL：为指定的表启用或禁用BNL。

 

注意

若要使用BNL或BKA提示为外部联接的任何内部表启用联接缓冲，必须为外部联接的所有内部表启用联接缓冲。

 

 

3、索引级优化器提示

 

MRR， NO_MRR：启用或禁用指定表或索引的MRR。MRR提示仅适用于InnoDB和 MyISAM表。

NO_ICP：对指定的表或索引禁用ICP。默认情况下，ICP是一种候选优化策略，因此没有启用它的提示。

NO_RANGE_OPTIMIZATION：禁用指定表或索引的索引范围访问。此提示还禁用了表或索引的索引合并和松散索引扫描。默认情况下，范围访问是一种候选优化策略，因此没有启用它的提示。

 

当范围数可能很高并且范围优化将需要许多资源时，此提示可能很有用。

 

4、子查询优化器提示

 

SEMIJOIN， NO_SEMIJOIN：启用或禁用命名的半连接策略。

这些策略名允许：DUPSWEEDOUT， FIRSTMATCH， LOOSESCAN， MATERIALIZATION。

对于SEMIJOIN提示，如果未命名策略，则根据optimizer_switch系统变量启用的策略（如果可能）使用半联接 。如果策略已命名但不适用于该语句，DUPSWEEDOUT则使用。

 

对于NO_SEMIJOIN提示，如果未命名策略，则不使用半联接。如果命名策略，则排除该语句的所有适用策略 DUPSWEEDOUT。

 

如果一个子查询嵌套在另一个子查询中，并且两个子查询都合并到外部查询的半联接中，则将忽略最内部查询的任何半联接策略规范。 SEMIJOIN并且 NO_SEMIJOIN提示仍然可以用于启用或禁用此类嵌套子查询的半联接转换。

 

如果DUPSWEEDOUT已禁用，则有时优化器可能会生成远非最佳的查询计划。发生这种情况的原因是贪婪搜索期间的启发式修剪，可以通过设置来避免 optimizer_prune_level=0。

 

例如：

SELECT /*+ NO_SEMIJOIN(@subq1 FIRSTMATCH, LOOSESCAN) */ * FROM t2

  WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);

SELECT /*+ SEMIJOIN(@subq1 MATERIALIZATION, DUPSWEEDOUT) */ * FROM t2

  WHERE t2.a IN (SELECT /*+ QB_NAME(subq1) */ a FROM t3);

 

影响是否使用子查询实现或IN-to- EXISTS 转换的提示：

 

SELECT id, a IN (SELECT /*+ SUBQUERY(MATERIALIZATION) */ a FROM t1) FROM t2;

SELECT * FROM t2 WHERE t2.a IN (SELECT /*+ SUBQUERY(INTOEXISTS) */ a FROM t1);

 

5、语句执行时间优化器提示

 

超时为1秒（1000毫秒）的示例：

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM t1 INNER JOIN t2 WHERE ...

如果此选项不存在或为0，则应用系统变量建立的语句超时 。

 

该MAX_EXECUTION_TIME提示适用于以下情况：

 

对于具有多个SELECT 关键字的语句，例如并集或带有子查询的语句， MAX_EXECUTION_TIME 将应用于整个语句，并且必须出现在第一个之后SELECT。

 

它适用于只读 SELECT语句。非只读的语句是那些调用存储函数修改数据的副作用的语句。

 

它不适用于SELECT 存储程序中的语句，将被忽略。