mysql 5.7 hint_MySQL 5.7 下的 hint

Hint Name

Description

Applicable Scopes

BKA, NO_BKA

Affects Batched Key Access join processing

Query block, table

BNL, NO_BNL

Affects Block Nested-Loop join processing

Query block, table

MAX_EXECUTION_TIME

Limits statement execution time

Global

MRR, NO_MRR

Affects Multi-Range Read optimization

Table, index

NO_ICP

Affects Index Condition Pushdown optimization

Table, index

NO_RANGE_OPTIMIZATION

Affects range optimization

Table, index

QB_NAME

Assigns name to query block

Query block

SEMIJOIN, NO_SEMIJOIN

Affects semijoin strategies

Query block

SUBQUERY

Affects materialization, IN-to-EXISTS subquery stratgies

Query block

表级hint，

BKA, NO_BKA: Enable or disable BKA for the specified tables.

BNL, NO_BNL: Enable or disable BNL for the specified tables.

索引级别的hint

MRR, NO_MRR: Enable or disable MRR for the specified table or indexes. MRR hints apply only to InnoDB and MyISAM tables.

NO_ICP: Disable ICP for the specified table or indexes. By default, ICP is a candidate optimization strategy, so there is no hint for enabling it.

NO_RANGE_OPTIMIZATION: Disable index range access for the specified table or indexes. This hint also disables Index Merge and Loose Index Scan for the table or indexes. By default, range access is a candidate optimization strategy, so there is no hint for enabling it.

子查询的hint

SEMIJOIN, NO_SEMIJOIN: Enable or disable the named semijoin strategies.

控制SQL执行时间的hint

MAX_EXECUTION_TIME(N)

还有个QB_NAME(name)没搞明白。。。

测试了下，好像故意写错了，也没什么影响

mysql> explain select /*+ BNA(fuck_wisedu)*/ a.clusterid,a.ip,a.bak_date,a.start_time,a.end_time,state from mysql_backup_job a left join mysql_conf b on a.ip=b.ip where bak_date = '2020-07-08' and b.group_id='23333' and state = 'OK' ;

+----+-------------+-------+------------+------+-------------------------------------------------------+-------------------------------+---------+----------------+-------+----------+------------------------------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-------+------------+------+-------------------------------------------------------+-------------------------------+---------+----------------+-------+----------+------------------------------------+

| 1 | SIMPLE | a | NULL | ref | ip_idx,date_idx | date_idx | 3 | const | 15574 | 10.00 | Using where |

| 1 | SIMPLE | b | NULL | ref | IDX_IP | IDX_IP | 47 | mysql_bak.a.ip | 1 | 10.00 | Using index condition; Using where |

+----+-------------+-------+------------+------+-------------------------------------------------------+-------------------------------+---------+----------------+-------+----------+------------------------------------+

2 rows in set, 1 warning (0.00 sec)