关于数据库:MySQL80-优化器介绍四

32次阅读

共计 5717 个字符，预计需要花费 15 分钟才能阅读完成。

  • GreatSQL 社区原创内容未经受权不得随便应用，转载请分割小编并注明起源。
  • GreatSQL 是 MySQL 的国产分支版本，应用上与 MySQL 统一。
  • 作者：奥特曼爱小怪兽
  • 文章起源：GreatSQL 社区原创

往期回顾

MySQL8.0 优化器介绍（一）

MySQL8.0 优化器介绍（二）

MySQL8.0 优化器介绍（三）

MySQL8.0 在优化器上做了很多致力和优化，依然不足以保障每条 sql，都能领有正当的执行打算，有些状况须要通过 hint 来干涉。MySQL8.0 在 hint 的品种上进行了新增。本篇次要讲咱们能够有哪些办法影响优化器。

优化器配置 Configuring the Optimizer

有很多办法影响优化器。

configuration options

mysql.engine_cost , mysql.server_cost 具体表字段的含意，怎么配置，配置后怎么失效的细节见官网。须要留神的是，配置完以上表后，得做一个 FLUSH OPTIMIZER_COSTS; 的动作。

另外还有两个重要参数 optimizer_prune_level、optimizer_search_depth 值得注意。

Optimizer Switches

optimizer switches 是一个复合的 option 集。8.0.25 默认的 optimizer switches

mysql> show  variables  like '%swi%' \G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on,
               index_merge_sort_union=on,index_merge_intersection=on,
               engine_condition_pushdown=on,index_condition_pushdown=on,
               mrr=on,mrr_cost_based=on,block_nested_loop=on,
               batched_key_access=off,materialization=on,
               semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,
               subquery_materialization_cost_based=on,
               se_index_extensions=on,condition_fanout_filter=on,
               derived_merge=on,use_invisible_indexes=off,skip_scan=on,
               hash_join=on,subquery_to_derived=off,prefer_ordering_index=on,
               hypergraph_optimizer=off,derived_condition_pushdown=on
1 row in set (0.00 sec)

optimizer switches 只管能够会话级设置，但大多数状况下，咱们都当作一个全局参数在用。

特地是 5.7 降级 8.0，8.0 小版本升级的时候，optimizer switches 的查看必须作为一个独自项。后面的文章中曾经介绍了 10 多种优化器的策略、算法。更多细节参考（https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizati…）

Optimizer Hints

（https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html）

Optimizer Hints 的个性是 5.7 引入的，并在 8.0 做了扩大。能够通过 hint 来影响查问打算的执行。

Hint 比 optimizer switches 的选项管制更精密，能够做到 query block，table，index 三级。并且反对在查问期间更改配置选项的值，比会话级批改参数更细。比方能够限度一个 sql 的执行工夫：

SELECT /*+ MAX_EXECUTION_TIME(2000) */
 id, Name, District
 FROM world.city
 WHERE CountryCode = 'AUS';
 ## 查问被限度在 2s 之内。

Table 8.2 Optimizer Hints Available

  • Global: The hint applies to the whole query.
  • Query Block: The hint applies to a group of joins. For example, the top level of the query is a query block; a subquery is another query block. Hints that apply to a query block can in some cases also take the table names for a join to limit the hint to a specific join.
  • Table: The hint applies to a specific table.
  • Index: The hint applies to the use of a specific index.

hint 的语法 /+ … / 留神三个点的前后都有一个空格。语法树都比拟难看，举例几个比拟难懂的语法。

#QB_NAME() 的用法。能够把一个简单的查问，划分成多个 query_block，而后再针对每个 qb 进行 hint 的调优

#定义一个 qb
SELECT /*+ QB_NAME(payment) */
 rental_id
 FROM sakila.payment
 WHERE staff_id = 1 AND customer_id = 75;
 
#简单查问中，对一个 qb 进行 hint
 SELECT /*+ NO_INDEX_MERGE(@payment payment) */
       rental_id, 
       rental_date,
       return_date
  FROM sakila.rental
 WHERE rental_id IN (SELECT /*+ QB_NAME(payment) */
                            rental_id
                       FROM sakila.payment
                      WHERE staff_id = 1 AND customer_id = 75);
                      
SELECT /*+ NO_INDEX_MERGE(payment@payment) */
       rental_id, 
       rental_date,
       return_date
  FROM sakila.rental
 WHERE rental_id IN (SELECT /*+ QB_NAME(payment) */
                            rental_id
                       FROM sakila.payment
                      WHERE staff_id = 1 AND customer_id = 75);
#查问期间更改配置选项的值
SELECT /*+ SET_VAR(join_buffer_size = 1048576)
           SET_VAR(optimizer_search_depth = 0) */
       CountryCode, 
       country.Name AS Country,
       city.Name AS City, 
       city.District
  FROM world.country IGNORE INDEX (Primary)
 INNER JOIN world.city IGNORE INDEX (CountryCode)
         ON city.CountryCode = country.Code
      WHERE Continent = 'Asia';
      
 #SET_VAR 一次只能批改一个 vairable，多个 vairable 须要多个 SET_VAR
 #SET_VAR 不反对表达式，= 号左边必须是具体的值

ps: 怎么练习 sql 级别的 hint 的应用？

  1. 多读几遍 25 种 hint 的形容。
  2. 拿着一个失常的执行打算做参考，把一个异样的执行打算试着用 hint 改到冀望的打算。
  3. 大多数的 hint 都是成对呈现的，无关，有开两种组合。把一个失常的打算，试着用 hint 把打算改得蹩脚。
  4. 从简略的 sql，单表的动手。逐步过渡到多表，单机，分布式数据库
  5. google MySQL hints 的教训 并实际。

Index Hints

这个大家应该很相熟，ignore、use、force index

SELECT ci.CountryCode, 
       co.Name AS Country,
       ci.Name AS City, 
       ci.District
  FROM world.country co IGNORE INDEX (Primary)
 INNER JOIN world.city ci IGNORE INDEX (CountryCode)
    ON ci.CountryCode = co.Code
 WHERE co.Continent = 'Asia';
 
 SELECT *
   FROM world.city USE INDEX FOR ORDER BY (Primary)
  WHERE CountryCode = 'AUS'
  ORDER BY ID;

资源组（Resource Groups）

（https://dev.mysql.com/doc/refman/8.0/en/resource-groups.html#…）

对于 high-concurrency systems 的场景，MySQL8.0 在 server 层，提供了一个 resource groups 个性。

select  *  from  information_schema.RESOURCE_GROUPS \G
*************************** 1. row ***************************
   RESOURCE_GROUP_NAME: USR_default
   RESOURCE_GROUP_TYPE: USER
RESOURCE_GROUP_ENABLED: 1
              VCPU_IDS: 0x302D35
       THREAD_PRIORITY: 0
*************************** 2. row ***************************
   RESOURCE_GROUP_NAME: SYS_default
   RESOURCE_GROUP_TYPE: SYSTEM
RESOURCE_GROUP_ENABLED: 1
              VCPU_IDS: 0x302D35
       THREAD_PRIORITY: 0
2 rows in set (0.00 sec)

CREATE RESOURCE GROUP my_group  
TYPE = USER  
VCPU = 2-3,6,7 
THREAD_PRIORITY = 0 
ENABLE;

##RESOURCE_GROUP 是影响线程级别的，须要装置商业版本的 thread_pool 
##（http://www.tdpub.cn/Blog/detail/id/1007.html）thread_pool 相干介绍 

##（https://zhuanlan.zhihu.com/p/114149600）##MySQL 源码级线程 init 过程的剖析 高并发创立 MySQL 线程时，遇到瓶颈，可能用失去。留神 MySQL 能够容许 2000 个并发同时运行与 MySQL 1 秒内创立 200 个并发 是不一样的概念。前者相似于高速上的车，后者相似于高速的入口。## 另外创立会话（连贯）时的 init 与 show  processlist 看到 State='init' 不一样。## 前面我的共事会投一篇 < 从 processlist.state 剖析 SQL 执行阶段 > 的文章进去介绍。##（https://zhuanlan.zhihu.com/p/114343815）MySQL 源码级连贯与线程治理

 SELECT THREAD_ID, RESOURCE_GROUP  FROM performance_schema.threads  limit 5;
+-----------+----------------+
| THREAD_ID | RESOURCE_GROUP |
+-----------+----------------+
|         1 | SYS_default    |
|         3 | SYS_default    |
|         4 | SYS_default    |
|         5 | SYS_default    |
|         6 | SYS_default    |
+-----------+----------------+

资源组可用于指定线程容许应用哪些 CPU，以及线程应应用哪个优先级执行。这对于确定某些线程的优先级十分有用
执行优先级高于其余线程或避免资源争用。

本系列文章一共 4 篇，概括地介绍了优化器是怎么工作的，join 的优化算法，以及优化 join 的办法；以及怎么配置 optimizer。

MySQL 应用基于老本的优化器，其中预计查问执行的每个局部的老本，并抉择总体查问打算以最小化老本。作为优化的一部分，优化器将应用各种转换重写查问，找到最佳连贯程序，并做出其余决定，例如应应用哪些索引。

MySQL 曾经有三种根本的 join 算法: NL，BNL,HASH JOIN。HASH JOIN 补救了 NL 在短少索引，或者索引选择性不佳时，触发的性能问题。同时 HASH JOIN 也带来本身的一些性能问题。

重点聚焦了三种 join 优化，index_merge 能够应用多个索引来提高效率。MRR 是通过缩小随机 IO 来提高效率。BKA=BNL+MRR

另外还介绍了多种影响优化器的办法。

一些有意思的链接：

（http://www.unofficialmysqlguide.com/index.html）（https://www.percona.com/blog/count-vs-countcol-in-mysql/）

Enjoy GreatSQL :)

## 对于 GreatSQL

GreatSQL 是由万里数据库保护的 MySQL 分支，专一于晋升 MGR 可靠性及性能，反对 InnoDB 并行查问个性，是实用于金融级利用的 MySQL 分支版本。

相干链接：GreatSQL 社区 Gitee GitHub Bilibili

GreatSQL 社区：

社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html

技术交换群：

微信：扫码增加 GreatSQL 社区助手 微信好友，发送验证信息 加群。

正文完
数据库
发表至： 数据库
2023-05-16
 0
关于云原生:Fluid-云原生环境下的高效数据物流系统
关于数据库:数据库系统概论王珊第二章关系数据库第一节关系数据结构及其形式化定义
关于数据库:滴滴推理引擎IFX千万规模设备下AI部署实践
关于数据库:个推大数据降本提效实战手册分享独家数据智能技术实践