聊聊 Mysql 8.0 hint 之上篇

原创大表哥 2022-03-16

6501

大家好！大表哥这次带来的专题是mysql hint.

想必大家都会有了解 hint 的概念：就是通过人工方式来干预一下SQL语句的执行计划。

业界来说，ORALCE 可能是在HINT 发挥到了极致，甚至是登峰造极！作为OLTP和OLAP 混动型商业数据库的代表，

ORALCE 官方提供的hint 种类繁多，尤其是在一些OLAP分析场景下的大报表和跑批的jobs， oracle 的hint 结合 SPM（SQL plan baseline）神一样的存在！

还有一些 internal 和 undocument 的 HINT，也是一些DBA老专家的分析调优利器。

PG 作为典型的学院派数据库代表，原生的PG是拒绝HINT的，但是国产数据库厂商的以PG内核的产品，也都实现了hint的功能。

我们回归正题， MYSQL作为国内目前OLTP的业务交易系统的首选非商业数据库，自然也少不了 HINT的支持。虽然没有oracle 那种天花板级别的存在，

但是mysql 也是在不停的加速迭代自己的hint支持。我们可以看到mysql 8.0 目前所支持的hint类型：

https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html

Mysql 的hint 大致分为如下几大类：

Join-Order Optimizer Hints --连接顺序相关的

Index-Level Optimizer Hints --索引级别的

Table-Level Optimizer Hints --表级别的

Subquery Optimizer Hints --子查询级别的

Statement Execution Time Optimizer Hints --语句执行时间级别的

Variable-Setting Hint Syntax --参数设置级别的

Resource Group Hint Syntax – 资源设置级别的

Optimizer Hints for Naming Query Blocks – 命名块级别的

Hint 的语法如下：目前是支持了和oracle一样的注释型的hint

/*+ BKA(t1) */
/*+ BNL(t1, t2) */
/*+ NO_RANGE_OPTIMIZATION(t4 PRIMARY) */
/*+ QB_NAME(qb2) */

复制

当然了mysql 之前的版本提供了一些，像是 use index , straight join 等等的老的形式的hint 的语法。

下面大表哥带你一个一个的分析一下：

Join-Order Optimizer Hints --连接顺序相关的

预先创建2张表

mysql> create table tt1 (id int not null primary key, name varchar(20));
Query OK, 0 rows affected (0.01 sec)


mysql> create table tt2 (id int not null primary key, name varchar(20));
Query OK, 0 rows affected (0.02 sec)


mysql> insert into tt1 values (1,'tt1');
Query OK, 1 row affected (0.00 sec)


mysql> insert into tt2 values (1,'tt2');
Query OK, 1 row affected (0.00 sec)

复制

我们先看着几个hint :
JOIN_ORDER: 指定表的连接顺序
JOIN_PREFIX: 设定第一个表顺序
JOIN_SUFFIX: 设定最后一个表的顺序
JOIN_FIXED_ORDER: 按照SQL写的顺序连接类似于 STRAIGHT_JOIN

JOIN_ORDER：，来固定表的连接顺序 tt2->tt1 /+join_order(tt2,tt1)/

desc select/*+join_order(tt2,tt1)*/ tt1.*,tt2.* from tt1,tt2 where tt1.id =tt2.id
--------------


+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
|  1 | SIMPLE      | tt2   | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL          |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | tt1   | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | testdb.tt2.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
2 rows in set, 1 warning (0.01 sec)

复制

我们再次反过来，来固定表的连接顺序 tt1->tt2 /+join_order(tt1,tt2)/

desc select/*+join_order(tt1,tt2)*/ tt1.*,tt2.* from tt1,tt2 where tt1.id =tt2.id
--------------


+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
|  1 | SIMPLE      | tt1   | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL          |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | tt2   | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | testdb.tt1.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

复制

JOIN_PREFIX: 设定第一个表顺序，我们来固定 tt2 作为驱动表 /+JOIN_PREFIX(tt2)/

desc select/*+JOIN_PREFIX(tt2)*/ tt1.*,tt2.* from tt1,tt2 where tt1.id =tt2.id
--------------


+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
|  1 | SIMPLE      | tt2   | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL          |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | tt1   | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | testdb.tt2.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

复制

JOIN_SUFFIX：设定最后一个表的顺序，我们来固定 tt2 作为被驱动表 /+JOIN_SUFFIX(tt2)/

desc select/*+JOIN_SUFFIX(tt2)*/ tt1.*,tt2.* from tt1,tt2 where tt1.id =tt2.id
--------------


+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
|  1 | SIMPLE      | tt1   | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL          |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | tt2   | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | testdb.tt1.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

复制

JOIN_FIXED_ORDER: 按照SQL写的顺序连接类似于 STRAIGHT_JOIN ，是按照where 语句后面SQL写的顺序，来连接。

from tt1,tt2 --》 tt1连tt2
from tt2,tt1 --》 tt2连tt1

root@localhost:mysql_uatDB.sock [testdb]> desc select/*+JOIN_FIXED_ORDER*/ tt1.*,tt2.* from tt1,tt2 where tt1.id =tt2.id;
--------------
desc select/*+JOIN_FIXED_ORDER*/ tt1.*,tt2.* from tt1,tt2 where tt1.id =tt2.id
--------------


+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
|  1 | SIMPLE      | tt1   | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL          |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | tt2   | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | testdb.tt1.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
2 rows in set, 2 warnings (0.00 sec)


root@localhost:mysql_uatDB.sock [testdb]> desc select/*+JOIN_FIXED_ORDER*/ tt1.*,tt2.* from tt2,tt1 where tt1.id =tt2.id;
--------------
desc select/*+JOIN_FIXED_ORDER*/ tt1.*,tt2.* from tt2,tt1 where tt1.id =tt2.id
--------------


+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref           | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
|  1 | SIMPLE      | tt2   | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL          |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | tt1   | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | testdb.tt2.id |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+--------+---------------+---------+---------+---------------+------+----------+-------+
2 rows in set, 2 warnings (0.01 sec)

复制

Index-Level Optimizer Hints --索引级别的

我们继续看一下索引级别的几个hint:
INDEX, NO_INDEX: 指定固定的索引访问
INDEX_MERGE, NO_INDEX_MERGE: 指定索引之间是否进行交，并集的操作
JOIN_INDEX, NO_JOIN_INDEX：指定表连接之间是否是走索引连接
MRR, NO_MRR: 是否启用multiple range read
NO_ICP: 关闭索引下推
NO_RANGE_OPTIMIZATION: 关闭索引范围的扫描
ORDER_INDEX, NO_ORDER_INDEX: 是否通过指定的索引来排序
SKIP_SCAN, NO_SKIP_SCAN: 是否进行索引的跳跃扫描

INDEX, NO_INDEX: 是否指定固定的索引访问：

我们来看一下这条语句： explain select * from t2 where id > 10 and a >20; 很显然走主键索引是最正确的选择 -》（PRIMARY）

root@localhost:mysql_uatDB.sock [testdb]> explain select  *  from t2 where id > 10 and a >20;
--------------
explain select  *  from t2 where id > 10 and a >20
--------------


+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | range | PRIMARY,a     | PRIMARY | 4       | NULL | 499193 |    50.00 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
复制

我们还可以通过hint -》 /+index(t2 a)/ 让MYSQL大叔的优化器走索引 a, 虽然这不是优化器最优的选择：

root@localhost:mysql_uatDB.sock [testdb]> explain select/*+index(t2 a)*/  *  from t2 where id > 10 and a >20;
--------------
explain select/*+index(t2 a)*/  *  from t2 where id > 10 and a >20
--------------


+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------+
|  1 | SIMPLE      | t2    | NULL       | range | a             | a    | 5       | NULL | 499193 |    33.33 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+--------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

复制

或者我们还可以通过 NO_INDEX(PRIMARY) 让MYSQL大叔不强制走主键索引树。
这次MYSQL的大叔选择了全表扫描： ALL

root@localhost:mysql_uatDB.sock [testdb]> explain select/*+no_index(t2 PRIMARY)*/  *  from t2 where id > 10 and a >20;
--------------
explain select/*+no_index(t2 PRIMARY)*/  *  from t2 where id > 10 and a >20
--------------


+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | a             | NULL | NULL    | NULL | 998386 |    16.66 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

复制

INDEX_MERGE, NO_INDEX_MERGE: 表示是否指定不同的索引之间的交集或者并集

一般情况下情况下： where 条件中使用了 AND 例如 a = 1 and b =1 一般会触发index merge的交集
where 条件中使用了 OR 例如 a = 1 and b =1 一般会触发index merge的并集

我们看一下例子： where (t2.id > 10 OR t2.a < 1000) AND t2.a < 10000 这个是触发了索引之间的 sort_union(a,PRIMARY) 交集
也就是说索引 a和主键 PRIMARY的 soft_union 去重合并的操作

desc select/*+index_merge(t2 a,PRIMARY)*/ * from t2 where (t2.id > 10 OR  t2.a < 1000) AND  t2.a < 10000
--------------




+----+-------------+-------+------------+-------------+---------------+-----------+---------+------+--------+----------+------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys | key       | key_len | ref  | rows   | filtered | Extra                                    |
+----+-------------+-------+------------+-------------+---------------+-----------+---------+------+--------+----------+------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | index_merge | PRIMARY,a     | a,PRIMARY | 5,4     | NULL | 500192 |   100.00 | Using sort_union(a,PRIMARY); Using where |
+----+-------------+-------+------------+-------------+---------------+-----------+---------+------+--------+----------+------------------------------------------+
1 row in set, 1 warning (0.00 sec)

复制

我们再来看一个例子： where t2.b = 1 AND t2.a =2; 这个是触发了索引之间的 Using intersect(a,idx_b) 并集操作
也就是说索引 a和索引idx_b 进行了交集的操作

explain select/*+ index_merge(t2 idx_b,a)*/ * from t2 where t2.b = 1  AND  t2.a =2
--------------


+----+-------------+-------+------------+-------------+---------------+---------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type        | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------------+---------------+---------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | index_merge | a,idx_b       | a,idx_b | 5,5     | NULL |    1 |   100.00 | Using intersect(a,idx_b); Using where; Using index |
+----+-------------+-------+------------+-------------+---------------+---------+---------+------+------+----------+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

复制

JOIN_INDEX, NO_JOIN_INDEX：指定表连接之间是否是走索引连接

我们可以看到在使用了 hint /+NO_JOIN_INDEX(t2 idx_b)/ 之后，表 t1,t2 的连接方式从原来的 index nested loop join 改成了 hash join

root@localhost:mysql_uatDB.sock [testdb]> explain select * from t1,t2 where t1.b = t2.b ;
--------------
explain select * from t1,t2 where t1.b = t2.b
--------------


+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key   | key_len | ref         | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL  | NULL    | NULL        | 1000 |   100.00 | Using where |
|  1 | SIMPLE      | t2    | NULL       | ref  | idx_b         | idx_b | 5       | testdb.t1.b |    1 |   100.00 | NULL        |
+----+-------------+-------+------------+------+---------------+-------+---------+-------------+------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)


root@localhost:mysql_uatDB.sock [testdb]> explain select/*+NO_JOIN_INDEX(t2 idx_b)*/ * from t1,t2 where t1.b = t2.b ;
--------------
explain select/*+NO_JOIN_INDEX(t2 idx_b)*/ * from t1,t2 where t1.b = t2.b
--------------


+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                      |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   1000 |   100.00 | NULL                                       |
|  1 | SIMPLE      | t2    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 998386 |     0.00 | Using where; Using join buffer (hash join) |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+--------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

复制

MRR, NO_MRR: 是否启用multiple range read :

MRR 这个概念大表哥在之前的文章中已经分享过了 : https://www.modb.pro/db/365827

目前我们可以通过2种方式使用MRR：
1） set optimizer_switch = “mrr_cost_based=off”;
2）hint mrr

root@localhost:mysql_uatDB.sock [testdb]> set optimizer_switch =  "mrr_cost_based=off";
--------------
set optimizer_switch =  "mrr_cost_based=off"
--------------


Query OK, 0 rows affected (0.00 sec)


root@localhost:mysql_uatDB.sock [testdb]> explain  select  * from t1 where a > 20 and a < 200;
--------------
explain  select  * from t1 where a > 20 and a < 200
--------------


+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                  |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | a             | a    | 5       | NULL |  179 |   100.00 | Using where; Using MRR |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------+
1 row in set, 1 warning (0.00 sec)



root@localhost:mysql_uatDB.sock [testdb]> explain  select/*+mrr(t1)*/ * from t1 where a > 20 and a < 200;
--------------
explain  select/*+mrr(t1)*/ * from t1 where a > 20 and a < 200
--------------


+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                  |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | a             | a    | 5       | NULL |  179 |   100.00 | Using where; Using MRR |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+------------------------+
1 row in set, 1 warning (0.00 sec)

复制

NO_ICP: 关闭索引下推索引下推在实行计划里面对应的是 Using index condition

我们来看一个例子： a索引会在innodb 的存储引擎层进行 id > 10的过滤再将最终的结果返回给mysql server

desc select * from t2 where t2.id > 10 and t2.a < 1000
--------------


+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t2    | NULL       | range | PRIMARY,a     | a    | 5       | NULL |  999 |    50.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

复制

我们使用 NO_ICP 尝试一下：果然索引下推没有了，改为了 Using where 表示需要在mysql server的内存中对ID的值进行过滤

root@localhost:mysql_uatDB.sock [testdb]> desc select/*+no_icp(t2)*/ * from t2 where t2.id > 10 and t2.a < 1000;
--------------
desc select/*+no_icp(t2)*/ * from t2 where t2.id > 10 and t2.a < 1000
--------------


+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | range | PRIMARY,a     | a    | 5       | NULL |  999 |    50.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

复制

NO_RANGE_OPTIMIZATION: 关闭索引范围的扫描

我们来看一个例子本来是 t2表走的是索引范围的扫描，t2.a > 10 and t2.a < 10000;
加上hint /+NO_RANGE_OPTIMIZATION(t2 a)/ 之后，弃用了索引范围访问，改为了全表扫描

root@localhost:mysql_uatDB.sock [testdb]> desc select * from t2 where t2.a > 10 and t2.a < 10000;
--------------
desc select * from t2 where t2.a > 10 and t2.a < 10000
--------------


+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t2    | NULL       | range | a             | a    | 5       | NULL | 9989 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)


root@localhost:mysql_uatDB.sock [testdb]> desc select/*+NO_RANGE_OPTIMIZATION(t2 a)*/ * from t2 where t2.a > 10 and t2.a < 10000;
--------------
desc select/*+NO_RANGE_OPTIMIZATION(t2 a)*/ * from t2 where t2.a > 10 and t2.a < 10000
--------------


+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | ALL  | a             | NULL | NULL    | NULL | 998386 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

复制

ORDER_INDEX, NO_ORDER_INDEX: 是否通过指定的索引来排序

我们来看这个例子中， where t2.a > 10 and t2.a < 10000 order by b; 走的是 a 列的索引，然后在进行ID的排序操作

root@localhost:mysql_uatDB.sock [testdb]> desc select  * from t2 where t2.a > 10 and t2.a < 10000 order by b;
--------------
desc select  * from t2 where t2.a > 10 and t2.a < 10000 order by b
--------------


+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | t2    | NULL       | range | a             | a    | 5       | NULL | 9989 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

复制

我们尝试加个 hint /+ORDER_INDEX(t2 PRIMARY)/ 强制走主键ID的排序
我们看到MYSQL大叔的优化器改成了走主键索引，这样就可以用到了主键索引树的排序。

root@localhost:mysql_uatDB.sock [testdb]> desc select/*+ORDER_INDEX(t2 PRIMARY)*/  * from t2 where t2.a > 10 and t2.a < 10000 order by id;
--------------
desc select/*+ORDER_INDEX(t2 PRIMARY)*/  * from t2 where t2.a > 10 and t2.a < 10000 order by id
--------------


+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t2    | NULL       | index | NULL          | PRIMARY | 4       | NULL | 998386 |    11.11 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

复制

SKIP_SCAN, NO_SKIP_SCAN: 是否进行索引的跳跃扫描

熟悉oracle 的小伙伴都应该知道当索引是多键值复合索引的时候，当索引的第一列如果在WHERE 条件中不能够有效的触发的话，ORACLE 大爷的优化器会选择 index skip scan .
同样的 mysql 大叔的优化器也是从version 8.0.13版本开始，也会有索引 skip scan这种方式。

我们采用官方文档的用例：

CREATE TABLE t1 (f1 INT NOT NULL, f2 INT NOT NULL, PRIMARY KEY(f1, f2));
INSERT INTO t1 VALUES
  (1,1), (1,2), (1,3), (1,4), (1,5),
  (2,1), (2,2), (2,3), (2,4), (2,5);
INSERT INTO t1 SELECT f1, f2 + 5 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 10 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 20 FROM t1;
INSERT INTO t1 SELECT f1, f2 + 40 FROM t1;
ANALYZE TABLE t1;

复制

我们来测试一下： WHERE f2 > 40 ，主键为复合索引（f1,f2）, 当前只有 f2这个列来触发。
我们可以看到了索引扫描进行了 skip scan (Using index for skip scan)

EXPLAIN SELECT f1, f2 FROM t1 WHERE f2 > 40
--------------


+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                  |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
|  1 | SIMPLE      | t1    | NULL       | range | PRIMARY       | PRIMARY | 8       | NULL |   53 |   100.00 | Using where; Using index for skip scan |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)

复制

我们同样可以用hint NO_SKIP_SCAN 来关闭 skip scan 这种索引跳跃的扫描方式。

可以看到了索引改成了普通的扫描

root@localhost:mysql_uatDB.sock [sbtest]> EXPLAIN SELECT/*+no_skip_scan(t1 PRIMARY)*/ f1, f2 FROM t1 WHERE f2 > 40;
--------------
EXPLAIN SELECT/*+no_skip_scan(t1 PRIMARY)*/ f1, f2 FROM t1 WHERE f2 > 40
--------------


+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t1    | NULL       | index | NULL          | PRIMARY | 8       | NULL |  160 |    33.33 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

复制

最后大表哥总结一下文本介绍了2大类型的HINT：

1)Join-Order Optimizer Hints --连接顺序相关的
JOIN_ORDER: 指定表的连接顺序
JOIN_PREFIX: 设定第一个表顺序
JOIN_SUFFIX: 设定最后一个表的顺序
JOIN_FIXED_ORDER: 按照SQL写的顺序连接类似于 STRAIGHT_JOIN

2)Index-Level Optimizer Hints --索引级别的
INDEX, NO_INDEX: 指定固定的索引访问
INDEX_MERGE, NO_INDEX_MERGE: 指定索引之间是否进行交，并集的操作
JOIN_INDEX, NO_JOIN_INDEX：指定表连接之间是否是走索引连接
MRR, NO_MRR: 是否启用multiple range read
NO_ICP: 关闭索引下推
NO_RANGE_OPTIMIZATION: 关闭索引范围的扫描
ORDER_INDEX, NO_ORDER_INDEX: 是否通过指定的索引来排序
SKIP_SCAN, NO_SKIP_SCAN: 是否进行索引的跳跃扫描