封面 《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 | ```SQL |
此外必须更在查询和数据更改关键词的后面,如下
1 | SELECT /*+ ... */ ... |
一个Hint注释中可以有多个Hint,但是一个查询中只能有一个Hint注释。
1 | -- 合法 |
种类
影响范围分类
- 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 | CREATE TABLE employees ( |
首先是正常查询,可以看到是走了合并索引
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是一种硬编码,可能会导致查询性能下降。此外平时自己的小项目里面可以还是用不上这种优化,毕竟数据量不够,但是知道有这么一个优化方式,以后遇到问题可以尝试用用。
参考文献
本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 qxdn的乐园!
评论
TwikooDisqus