Percona MySQL 5.6 HINT是什么

Percona MySQL 5.6 HINT是什么，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。

SQL_BUFFER_RESULT
会强制将查询结果放入一张临时表中。当消耗很长时间来讲结果集发送到客户端时，这有助于MySQL尽早释放表锁。这个提示只用在最外层的SELECT语句，而不适用于子查询或UNION语句。

 mysql> explain select * from test;
 +----+-------------+-------+-------+---------------+------------------+---------+------+------+-------------+
 | id | select_type | table | type  | possible_keys | key              | key_len | ref  | rows | Extra       |
 +----+-------------+-------+-------+---------------+------------------+---------+------+------+-------------+
 |  1 | SIMPLE      | test  | index | NULL          | idx_test_id_name | 23      | NULL |    5 | Using index |
 +----+-------------+-------+-------+---------------+------------------+---------+------+------+-------------+
 1 row in set (0.00 sec)
 mysql> explain select SQL_BUFFER_RESULT * from test;
 +----+-------------+-------+-------+---------------+------------------+---------+------+------+------------------------------+
 | id | select_type | table | type  | possible_keys | key              | key_len | ref  | rows | Extra                        |
 +----+-------------+-------+-------+---------------+------------------+---------+------+------+------------------------------+
 |  1 | SIMPLE      | test  | index | NULL          | idx_test_id_name | 23      | NULL |    5 | Using index; Using temporary |
 +----+-------------+-------+-------+---------------+------------------+---------+------+------+------------------------------+
 1 row in set (0.00 sec)
 
 
STRAIGHT_JOIN
会强制优化器按照FROM后面表的顺序来做连接。如果优化器以不恰当的顺序来连接表，可以使用这个提示来加速查询的速度。STRAIGHT_JOIN提示不会应用到执行计划中类型为const或system的表。

 mysql> explain select e.* from emp e join dept d on e.deptno=d.deptno;
 +----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
 | id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                                              |
 +----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
 |  1 | SIMPLE      | d     | index | PRIMARY       | PRIMARY | 4       | NULL |    5 | Using index                                        |
 |  1 | SIMPLE      | e     | ALL   | NULL          | NULL    | NULL    | NULL |   14 | Using where; Using join buffer (Block Nested Loop) |
 +----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------------+
 2 rows in set (0.00 sec)
 mysql> explain select STRAIGHT_JOIN  e.* from emp e join dept d on e.deptno=d.deptno;
 +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
 | id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra       |
 +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
 |  1 | SIMPLE      | e     | ALL    | NULL          | NULL    | NULL    | NULL          |   14 | Using where |
 |  1 | SIMPLE      | d     | eq_ref | PRIMARY       | PRIMARY | 4       | test.e.deptno |    1 | Using index |
 +----+-------------+-------+--------+---------------+---------+---------+---------------+------+-------------+
 2 rows in set (0.00 sec)
 
 
USE INDEX
告诉MySQL使用指定的索引。当MySQL使用了错误的索引时，这个提示会很有用。

 mysql> show keys from test;
 +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 | Table | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 | test  |          1 | idx_test_id_name |            1 | id          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
 | test  |          1 | idx_test_id_name |            2 | name        | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
 | test  |          1 | idx_test_id      |            1 | id          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
 | test  |          1 | idx_test_name    |            1 | name        | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
 +-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 4 rows in set (0.00 sec)
 mysql> explain select count(*) from test;
 +----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
 | id | select_type | table | type  | possible_keys | key         | key_len | ref  | rows | Extra       |
 +----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
 |  1 | SIMPLE      | test  | index | NULL          | idx_test_id | 5       | NULL |    5 | Using index |
 +----+-------------+-------+-------+---------------+-------------+---------+------+------+-------------+
 1 row in set (0.00 sec)
 mysql> explain select count(*) from test use index (idx_test_name);
 +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
 | id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra       |
 +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
 |  1 | SIMPLE      | test  | index | NULL          | idx_test_name | 18      | NULL |    5 | Using index |
 +----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
 1 row in set (0.00 sec)
 mysql> explain select count(*) from test use index (idx_test_id_name);
 +----+-------------+-------+-------+---------------+------------------+---------+------+------+-------------+
 | id | select_type | table | type  | possible_keys | key              | key_len | ref  | rows | Extra       |
 +----+-------------+-------+-------+---------------+------------------+---------+------+------+-------------+
 |  1 | SIMPLE      | test  | index | NULL          | idx_test_id_name | 23      | NULL |    5 | Using index |
 +----+-------------+-------+-------+---------------+------------------+---------+------+------+-------------+
 1 row in set (0.00 sec)
 
 
IGNORE INDEX
告诉MySQL不要使用指定的索引。当MySQL使用了错误的索引时，这个提示会很有用。

 mysql> show keys from dept;
 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 | dept  |          0 | PRIMARY  |            1 | deptno      | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
 +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 1 row in set (0.00 sec)
 mysql> explain select deptno from dept;
 +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
 | id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
 +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
 |  1 | SIMPLE      | dept  | index | NULL          | PRIMARY | 4       | NULL |    5 | Using index |
 +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
 1 row in set (0.00 sec)
 mysql> explain select deptno from dept ignore index (PRIMARY);
 +----+-------------+-------+------+---------------+------+---------+------+------+-------+
 | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
 +----+-------------+-------+------+---------------+------+---------+------+------+-------+
 |  1 | SIMPLE      | dept  | ALL  | NULL          | NULL | NULL    | NULL |    5 | NULL  |
 +----+-------------+-------+------+---------------+------+---------+------+------+-------+
 1 row in set (0.00 sec)
 
 
FORCE INDEX
和USE INDEX相似。这个提示会让查询一直使用索引，除非表的查询条件无法使用表中的索引。

mysql> show keys from buy_log;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| buy_log |          1 | userid   |            1 | userid      | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| buy_log |          1 | userid_2 |            1 | userid      | A         |           7 |     NULL | NULL   |      | BTREE      |         |               |
| buy_log |          1 | userid_2 |            2 | buy_date    | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> explain select * from buy_log force index(userid) where userid=1;
+----+-------------+---------+------+---------------+--------+---------+-------+------+-------+
| id | select_type | table   | type | possible_keys | key    | key_len | ref   | rows | Extra |
+----+-------------+---------+------+---------------+--------+---------+-------+------+-------+
|  1 | SIMPLE      | buy_log | ref  | userid        | userid | 4       | const |    4 | NULL  |
+----+-------------+---------+------+---------------+--------+---------+-------+------+-------+
1 row in set (0.00 sec)

mysql> explain select * from buy_log force index(userid_2) where userid=1;
+----+-------------+---------+------+---------------+----------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys | key      | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+---------------+----------+---------+-------+------+-------------+
|  1 | SIMPLE      | buy_log | ref  | userid_2      | userid_2 | 4       | const |    4 | Using index |
+----+-------------+---------+------+---------------+----------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> show keys from emp;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| emp   |          0 | PRIMARY        |            1 | empno       | A         |          14 |     NULL | NULL   |      | BTREE      |         |               |
| emp   |          1 | idx_emp_deptno |            1 | deptno      | A         |           7 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> explain select * from emp e force index(PRIMARY)  join dept d on e.deptno=d.deptno;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | e     | ALL  | NULL          | NULL | NULL    | NULL |   14 | NULL                                               |
|  1 | SIMPLE      | d     | ALL  | PRIMARY       | NULL | NULL    | NULL |    5 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

mysql> explain select * from emp e force index(idx_emp_deptno)  join dept d on e.deptno=d.deptno;
+----+-------------+-------+------+----------------+----------------+---------+---------------+------+-------+
| id | select_type | table | type | possible_keys  | key            | key_len | ref           | rows | Extra |
+----+-------------+-------+------+----------------+----------------+---------+---------------+------+-------+
|  1 | SIMPLE      | d     | ALL  | PRIMARY        | NULL           | NULL    | NULL          |    5 | NULL  |
|  1 | SIMPLE      | e     | ref  | idx_emp_deptno | idx_emp_deptno | 5       | test.d.deptno |    2 | NULL  |
+----+-------------+-------+------+----------------+----------------+---------+---------------+------+-------+
2 rows in set (0.00 sec)

mysql> show keys from test;
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          1 | idx_test_id_name |            1 | id          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
| test  |          1 | idx_test_id_name |            2 | name        | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
| test  |          1 | idx_test_id      |            1 | id          | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
| test  |          1 | idx_test_name    |            1 | name        | A         |           5 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
mysql> explain select * from test where id > 20;
+----+-------------+-------+-------+------------------------------+------------------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys                | key              | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+------------------------------+------------------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | range | idx_test_id_name,idx_test_id | idx_test_id_name | 5       | NULL |    3 | Using where; Using index |
+----+-------------+-------+-------+------------------------------+------------------+---------+------+------+--------------------------+
1 row in set (0.00 sec)
mysql> explain select * from test use index (idx_test_id)  where id > 20;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | idx_test_id   | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from test force index (idx_test_id)  where id > 20;
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
| id | select_type | table | type  | possible_keys | key         | key_len | ref  | rows | Extra                 |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | test  | range | idx_test_id   | idx_test_id | 5       | NULL |    3 | Using index condition |
+----+-------------+-------+-------+---------------+-------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
mysql> explain select * from test force index (idx_test_name)  where id > 20;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | ALL  | NULL          | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

看完上述内容，你们掌握Percona MySQL 5.6 HINT是什么的方法了吗？如果还想学到更多技能或想了解更多相关内容，欢迎关注亿速云行业资讯频道，感谢各位的阅读！