封面《9-nine 动画版》（光速蹭热度）

前言

因为看公司代码的时候看到了类似下面的代码

1	SELECT /+ INDEX (table_name index_name) / column_name FROM table_name;

了解到有 SQL Hint 这么一个东西，因此记录一下学习过程

SQL Hint 是什么

一条 SQL 语句的执行，一般会经过以下环节

分析器：执行语法分析和语义分析
优化器：优化 sql 语句，生成执行计划
执行器：执行 sql 语句

优化器（Optimizier）是 MySQL 中的一个重要组件，它负责决定如何执行查询。优化器会根据查询的条件、表的大小、索引的情况等因素，选择最优的执行计划。但是有时候优化器的选择并不是最优的，这时候就需要我们手动干预，这就是 SQL Hint。

MySQL Hint

因为我平时 MySQL 用的比较多，因此这里主要讲讲 MySQL Hint，MySQL 官方关于优化器提示的文档在这里。

语法

SQL Hint 的语法主要类似 C 语言的注释，采用 /*+ ... */ 的格式，注释开始后面紧跟 +。

1 2	```SQL /+ hint /

此外必须更在查询和数据更改关键词的后面，如下

SELECT /*+ ... */ ...
INSERT /*+ ... */ ...
REPLACE /*+ ... */ ...
UPDATE /*+ ... */ ...
DELETE /*+ ... */ ...

一个 Hint 注释中可以有多个 Hint，但是一个查询中只能有一个 Hint 注释。

-- 合法
SELECT /*+ hint1 hint2 hint3 */ ...
-- 无效
SELECT /*+ hint1 */ /*+ hint2 */ ...

种类

影响范围分类

global: 影响整个语句
query block: 影响特定的查询块
table: 影响查询块中特定的表
index: 影响查询块中特定的索引

Hint Name	Description	Applicable Scopes
BKA, NO_BKA	Affects Batched Key Access join processing	Query block, table
BNL, NO_BNL	Prior to MySQL 8.0.20: affects Block Nested-Loop join processing; MySQL 8.0.18 and later: also affects hash join optimization; MySQL 8.0.20 and later: affects hash join optimization only	Query block, table
DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN	Use or ignore the derived condition pushdown optimization for materialized derived tables (Added in MySQL 8.0.22)	Query block, table
GROUP_INDEX, NO_GROUP_INDEX	Use or ignore the specified index or indexes for index scans in GROUP BY operations (Added in MySQL 8.0.20)	Index
HASH_JOIN, NO_HASH_JOIN	Affects Hash Join optimization (MySQL 8.0.18 only	Query block, table
INDEX, NO_INDEX	Acts as the combination of JOIN_INDEX, GROUP_INDEX, and ORDER_INDEX, or as the combination of NO_JOIN_INDEX, NO_GROUP_INDEX, and NO_ORDER_INDEX (Added in MySQL 8.0.20)	Index
INDEX_MERGE, NO_INDEX_MERGE	Affects Index Merge optimization	Table, index
JOIN_FIXED_ORDER	Use table order specified in FROM clause for join order	Query block
JOIN_INDEX, NO_JOIN_INDEX	Use or ignore the specified index or indexes for any access method (Added in MySQL 8.0.20)	Index
JOIN_ORDER	Use table order specified in hint for join order	Query block
JOIN_PREFIX	Use table order specified in hint for first tables of join order	Query block
JOIN_SUFFIX	Use table order specified in hint for last tables of join order	Query block
MAX_EXECUTION_TIME	Limits statement execution time	Global
MERGE, NO_MERGE	Affects derived table/view merging into outer query block	Table
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
ORDER_INDEX, NO_ORDER_INDEX	Use or ignore the specified index or indexes for sorting rows (Added in MySQL 8.0.20)	Index
QB_NAME	Assigns name to query block	Query block
RESOURCE_GROUP	Set resource group during statement execution	Global
SEMIJOIN, NO_SEMIJOIN	Affects semijoin strategies; beginning with MySQL 8.0.17, this also applies to antijoins	Query block
SKIP_SCAN, NO_SKIP_SCAN	Affects Skip Scan optimization	Table, index
SET_VAR	Set variable during statement execution	Global
SUBQUERY	Affects materialization, IN-to-EXISTS subquery strategies	Query block

示例

现实中我们常用的应该是索引相关的 Hint，下面将结合例子进行讲述

先建立一张表插入数据

CREATE TABLE employees (
  id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(30),
  last_name VARCHAR(30),
  department_id INT,
  salary DECIMAL(10, 2),
  PRIMARY KEY (id),
  INDEX idx_last_name (last_name),
  INDEX idx_department_salary (department_id, salary)
);

INSERT INTO employees (first_name, last_name, department_id, salary) VALUES
('John', 'Doe', 1, 70000),
('Jane', 'Doe', 2, 75000),
('Jim', 'Beam', 1, 80000),
('James', 'Brown', 3, 90000),
('Jamie', 'Fox', 2, 72000);