封面 《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 */

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

1
2
3
4
5
SELECT /*+ ... */ ...
INSERT /*+ ... */ ...
REPLACE /*+ ... */ ...
UPDATE /*+ ... */ ...
DELETE /*+ ... */ ...

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

1
2
3
4
-- 合法
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,下面将结合例子进行讲述

先建立一张表插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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);

首先是正常查询,可以看到是走了合并索引

1
EXPLAIN SELECT  * FROM employees WHERE last_name = 'Doe' OR department_id = 2;

有些情况下希望走全表扫描,此时可以选择NO_INDEX,此时可以看到是没有执行计划走索引,

1
EXPLAIN SELECT /*+ NO_INDEX(employees) */ * FROM employees WHERE last_name = 'Doe' OR department_id = 2;

总结

SQL Hint是一种手动干预优化器的方法,可以通过Hint来指定查询的执行计划,但是使用时需要谨慎,因为Hint是一种硬编码,可能会导致查询性能下降。此外平时自己的小项目里面可以还是用不上这种优化,毕竟数据量不够,但是知道有这么一个优化方式,以后遇到问题可以尝试用用。

参考文献

MySQL Hints:控制查询优化器的选择

MySQL Optimizer Hints

SQL进阶理论篇(十三):数据库的查询优化器是什么?

优化器提示