Percona MySQL 5.6 HINT是什么

发布时间:2021-10-25 16:29:43 作者:柒染
来源:亿速云 阅读:144

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是什么的方法了吗?如果还想学到更多技能或想了解更多相关内容,欢迎关注亿速云行业资讯频道,感谢各位的阅读!

推荐阅读:
  1. Percona Xtrabackup 8.0备份MySQL8
  2. 如何根据生产环境mysql版本选择合适的percona xtrabackup版本

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

hint percona

上一篇:MySQL 5.7 NOT EXISTS的用法是什么

下一篇:Python3.9的新功能有哪些

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》