MySQL Hints
我们可以对MySQL的对象（表、索引、触发器、自建函数、存储过程等）做注释（comment），这样做的目的是标识该对象的作用等以增强代码的可读性、方便其他同事快速读懂我们写的代码或某个数据库对象的作用，说白了，comment就是对我们人的一个提示。在MySQL中还有一种提示，叫做hint，hint是对数据库的提示，我们都知道， 在执行一条SQL语句的时候，MySQL都会生成一个执行计划，而hint就是用来告诉优化器按照我们告诉它的方式生成执行计划。Hint可以基于表连接的顺序、表连接的方法、访问路径、并行度等规则对DML语句、或者查查询语句产生作用，通过它我们可以实现：
1) 使用的优化器的类型
2) 基于代价的优化器的优化目标，是all_rows还是first_rows。
3) 表的访问路径，是全表扫描，还是索引扫描，还是直接利用rowid。
4) 表之间的连接类型
5) 表之间的连接顺序
6) 语句的并行程度
下面我们看下MySQL中hint都能实现那些功能：
1、Index Hints
在MySQL中，查询优化器用来解析查询语句，给出最优的查询路径，然而查询优化器并不是一直能给出最优的执行计划，比如某张表经过大量的insert和update操作后，索引的分布会受到很大的影响（此时我们可以使用ANALYZE TABLE来重新分析表）。当优化器不能给出合理的执行计划时，我们可以使用索引hints来告诉MySQL优化器使用某个索引或忽略某个索引，从而达到优化查询的目的。
语法：
tbl_name [ alias]
index_hint_list:
index_hint [, index_hint] ...
index_hint:
USE {INDEX|KEY}
()
| IGNORE {INDEX|KEY}
(index_list)
| FORCE {INDEX|KEY}
(index_list)
index_list:
index_name [, index_name] ...
index_hint语句放在表名后面，index_list选项后面跟索引名，不要跟列名，对于一些表可能后面跟列名也可以，但这样是不标准的，对于有主键索引的表， USE | FORCE| IGNORE {INDEX|KEY} 后面必须跟primary代替在索引的名字。
SELECT * FROM t1 USE INDEX (PRIMARY) ...;
USE INDEX
后面添加你希望MySQL查询使用的索引列表，就可以让MySQL不再考虑其他可用的索引。
SELECT * FROM t1 USE INDEX (col1_index,col2_index)
WHERE col1=1 AND col2=2 AND col3=3;
IGNORE INDEX
禁止查询优化器使用指定的索引。在具有多个索引的查询时，可以用来指定不需要优化器使用的那个索引，还可以在删除不必要的索引之前在查询中禁止使用该索引。比如，在一个有主键索引的表中，默认的执行计划是按照主键索引进行扫描，如果我们使用IGNORE INDEX忽略主键索引，则会按照全表扫描执行。
SELECT * FROM table1 IGNORE INDEX (col3_index)
WHERE col1=1 AND col2=2 AND col3=3;
FORCE INDEX
强制MySQL使用一个或者多个索引
MySQL 通常会根据统计信息选择正确的索引，但是当查询优化器选择了错误的索引或者根本没有使用索引的时候，可以使用FORCE INDEX选项来让MySQL使用正确的所以。
SELECT * FROM t1 FORCE INDEX (col1_index) ...;
在USE INDEX后面，如果省略了索引列表（USE INDEX （）WHERE ...）则表示不使用任何索引，如果在FORCE|IGNORE INDEX后面省略了索引名，则报错。
我们可以指定索引hint的范围：
index_hint:
USE | IGNORE | FORCE {INDEX|KEY}
()
分别是用于表连接（JOIN）、排序（ORDER BY）、分组（GROUP BY）
如果不跟FOR语句来指定hint的范围，则默认是应用上述所有的范围，也就是说，下面2条语句是等同的：
IGNORE INDEX (i1)
IGNORE INDEX FOR JOIN (i1)
IGNORE INDEX FOR ORDER BY (i1)
IGNORE INDEX FOR GROUP BY (i1)
在MySQL 5.0及以前，如果不知道FOR选项，则默认只是在行检索使用hint，在MySQL 5.0以后的版本都是默认应用所有范围，当然我们还可以修改OLD参数来改变这一功能：
mysql> show variables like 'old';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old | OFF |
+---------------+-------+
1 row in set (0.00 sec)
但在MySQL Replication环境下，最好不要修改此参数，二进制日志格式是statement的时候，此参数主从不一致会报错。
我们还可以同时使用多个hints：
SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER BY a;
不能在同一个hint里面使用相同的索引：
SELECT * FROM t1 USE INDEX (i1) USE INDEX (i1,i1);
一张表中不能同时使用USE INDEX和FORCE INDEX：
SELECT * FROM t1 USE INDEX FOR JOIN (i1) FORCE INDEX FOR JOIN (i2);
Hints在全文索引中的应用：
natural language mode ：默认IGNORE INDEX无效，即便加上此选项也会忽略它，但不会报错。
boolean mode：会自动IGNORE FOR ORDER BY | GROUP BY，也就是下面2个语句是等效的：
SELECT * FROM t
USE INDEX (index1)
IGNORE INDEX (index1) FOR ORDER BY
IGNORE INDEX (index1) FOR GROUP BY
WHERE ... IN BOOLEAN MODE ... ;
SELECT * FROM t
USE INDEX (index1)
WHERE ... IN BOOLEAN MODE ... ;
2、在SQL语句级别决定是否使用查询缓存
MySQL查询缓存是用来缓存SQL文本以及查询结果，如果运行相同的SQL，则会直接从缓存中提取缓存结果，这样就不用重新解析和执行SQL语句，从而提高查询效率。查询缓存由参数query_cache_type控制，0表示不使用缓冲，1表示使用缓冲，2表示根据需要使用。
查看使用缓存查询的SQL语句数量：
show status like 'qcache_hits';
关闭查询缓冲（SQL_NO_CACHE）
SELECT SQL_NO_CACHE col1,col2 FROM t1;
有一些SQL语句需要实时地查询数据，或并不经常使用（可能一天就执行一两次），这样就需要把缓冲关了，不管这条SQL语句是否被执行过，服务器都不会在缓冲区中查找，每次都会执行它。
强制查询缓冲（SQL_CACHE）
SELECT SQL_CACHE col1,col2 FROM t1;
如果在my.cnf|my.ini中的query_cache_type设成2，这样只有在使用了SQL_CACHE后，才使用查询缓冲。
强制使用临时表（SQL_BUFFER_RESULT）
SELECT SQL_BUFFER_RESULT * FROM t1 WHERE ...;
当查询的结果集中的数据比较多时，可以通过SQL_BUFFER_RESULT选项强制将结果集放到临时表中，这样便可以很快地释放MySQL的表锁（这样其它的SQL语句就可以对这些记录进行查询了），并且可以长时间地为客户端提供大记录集。
SQL_CALC_FOUND_ROWS关键字告诉MySQL计算符合查询（不需要考虑可能设置的任何LIMIT）的总行数，即加上此参数后，当执行了SELECT SQL_CALC_FOUND_ROWS * FROM t1 LIMIT 2，5；此时结果返回5行，但我们不知道此查询语句一共有多少行符合查询要求，此时就可以通过调用FOUND_ROWS()函数可以得到总行数。
SQL_BIG_RESULT 和SQL_SMALL_RESULT关键字可以指定结果集的期望大小，因此可帮助找到最佳的方法对返回的记录进行排序和存储（基于磁盘或者内存中的临时表）
3、修改语句的优先级
提升语句的优先级（HIGH_PRIORITY）
HIGH_PRIORITY可以使用在select和insert操作中，让MySQL知道，这个操作优先进行。
SELECT HIGH_PRIOPRITY * FROM t1;
降低语句的优先级（LOW_PRIORITY）
应用于delete、insert、load data和update中
UPDATE LOW_PRIOPRITY t1 SET col1='...' WHERE col2='...';
如果你希望所有支持LOW_PRIORITY选项的语句都默认地按照低优先级来处理，可以在配置文件里加上--low-priority-updates选项。此时，如果想要把INSERT语句提高到正常的写入优先级，则使用INSERT HIGH_PRIORITY来把可以消除该选项对单个INSERT语句的影响。
延时插入（INSERT DELAYED）
应用于insert和replace语句中
INSERT DELAYED INTO t1 SET col1='...';
INSERT DELAYED INTO，是客户端提交数据给MySQL，MySQL返回OK状态给客户端。而这是并不是已经将数据插入表，而是存储在内存里面等待排队，当mysql有空余时，再插入。这样做好处在于来自许多客户端的插入被集中在一起，并被编写入一个块，这比执行许多独立的插入要快很多。
缺点是：不能返回自动递增的ID，以及系统崩溃时，MySQL还没有来得及插入数据的话，这些数据将会丢失。
4、强制改变连接顺序（STRAIGHT_JOIN）
SELECT t1.col1,t2.col2 FROM t1 STRAIGHT_JOIN t2 WHERE ...;
通过STRAIGHT_JOIN强迫MySQL按t1、t2的顺序连接表。
如果认为按自己的顺序比MySQL推荐的顺序进行连接的效率高的话，就可以通过STRAIGHT_JOIN来确定连接顺序。
MySQL对多表连接的处理方式，首先MySQL优化器要确定以谁为驱动表，也就是说以哪个表为基准，在处理此类问题时，MySQL优化器采用了简单粗暴的解决方法：哪个表的结果集小，就以哪个表为驱动表。
SELECT t1.* FROM t1
INNER JOIN t2 ON t1.id = t2.tid
WHERE t1.status = 1 AND t2.gid = 123
ORDER BY t1.created DESC LIMIT 100;
SELECT t1.* FROM t1
STRAIGHT_JOIN t2 ON t1.id = t2.tid
WHERE t1.status = 1 AND t2.gid = 123
ORDER BY t1.created DESC LIMIT 100;
上面2个语句的不同之处就在于采用了STRAIGHT_JOIN，当t2表的结果集小于t1表的时候，MySQL优化器会采用t1表作为驱动表，但我们后面的ORDER BY语句中用到t1表的created列，此时如果created中有索引无疑会增加检索速度，如果默认是t2表做驱动表，执行计划中可能会出现Using filesort，甚至Using temporary，这样是很耗费资源的。
更多MySQL学习文章，关注我哦
感谢分享
页:
[1]