加入收藏 | 设为首页 | 会员中心 | 我要投稿 51站长网 （https://www.51jishu.cn/）- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置： 首页 > 站长学院 > MySql教程 > 正文

Percona MySQL 5.6 HINT是啥

发布时间：2022-01-12 12:53:05 所属栏目：MySql教程 来源：互联网
导读：Percona MySQL 5.6 HINT是什么，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。 SQL_BUFFER_RESULT 会强制将查询结果放入一张临时表中。当消耗很长时间来讲结果集发送到客户端时，这有助于
      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)
 
 
 
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)
 
 
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是什么的方法了吗？

（编辑：51站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
    相关内容
      推荐文章
      站长推荐
      热点阅读

        【免责声明】本站内容转载自互联网，其发布内容言论不代表本站观点，如果其链接、内容的侵犯您的权益，烦请提交相关链接至邮箱bqsm@foxmail.com我们将及时予以处理。

        建议您使用1920×1080分辨率、谷歌浏览器Google Chrome、Microsoft Edge以获得本站的最佳浏览效果

        Copygight © 2019-2023 https://www.51jishu.cn/ All Rights Reserved. 51站长网