Percona MySQL 5.6 HINT介绍

落日映苍穹つ 2024-04-18 02:03 3阅读 0赞

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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2123778/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2123778/

发表评论取消回复

表情：

评论列表（有 0 条评论，3人围观）

还没有评论，来说两句吧...

相关阅读

相关 Percona MySQL 5.6 HINT介绍

QL\_BUFFER\_RESULT 会强制将查询结果放入一张临时表中。当消耗很长时间来讲结果集发送到客户端时，这有助于MySQL尽早释放表锁。这个提示只用在最外层...

落日映苍穹つ/ 2024年04月18日 02:03/ 0 赞/ 4 阅读

相关 zabbix使用percona模板监控Mysql

文章目录前言 percona介绍使用percona监控mysql步骤安装percona-zabbix-template

忘是亡心i/ 2023年02月13日 12:24/ 0 赞/ 3 阅读

相关 Mac 升级到php56 ,手动启动mysql56方法

升级php55 到 php56 brew unlink php55 brew install php56 再修改 `/etc/apache2/httpd

喜欢ヅ旅行/ 2022年08月21日 05:52/ 0 赞/ 152 阅读

相关 MySQL：HINT

mysql常用的hint 1、强制索引 FORCE INDEX SELECT FROM TABLE1 FORCE INDEX (FIELD1) …

谁借莪１个温暖的怀抱￠/ 2022年07月19日 02:20/ 0 赞/ 93 阅读

相关 percona mysql备份与恢复(一)

author：skate time：2012/06/27 percona mysql备份与恢复(一) XtraBackup 是开源的热备mysql的软件，可以备份

男娘i/ 2022年06月10日 03:21/ 0 赞/ 159 阅读

相关 mysql8新特性 hint variables

mysql 8 开始支持对sql临时生效variables 语法格式： SELECT /\+ SET\_VAR(optimizer\_switch = 'mrr\_

怼烎@/ 2022年03月25日 03:50/ 0 赞/ 265 阅读

相关 Percona Toolkit工具集介绍

部署mysql工具是一个非常重要的部分，所以工具的可靠性和很好的设计非常重要。percona toolkit是一个有30多个mysql工具的工具箱。兼容mysql,percon

小灰灰/ 2022年03月19日 01:18/ 0 赞/ 157 阅读

相关 mysql管理工具percona-toolkit-3简单使用介绍

安装percona-toolkit-3 yum localinstall percona-toolkit-3.0.3-1.el6.x86_64.rpm perl-T

ゝ一纸荒年。/ 2022年03月18日 21:28/ 0 赞/ 202 阅读

相关 Docker安装MySQL、Percona

Docker安装MySQL、Percona MySQL安装获取mysql镜像简单创建容器可配置创建容器

男娘i/ 2021年10月06日 02:54/ 0 赞/ 381 阅读

相关 mysql 分支 XtraDB Percona MariaDB 简介

导读：尽管MySQL是最受欢迎的程序之一，但是许多开发人员认为有必要将其拆分成其他项目，并且每个分支项目都有自己的专长。该需求以及Oracle对核心产品增长缓慢的担忧，导致出

以你之姓@/ 2021年07月28日 21:50/ 0 赞/ 248 阅读