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是什么的方法了嗎？如果還想學到更多技能或想了解更多相關內容，歡迎關注億速云行業資訊頻道，感謝各位的閱讀！