||
往期回顾
MySQL8.0在优化器上做了很多努力和优化，仍然不足以保证每条sql，都能拥有合理的执行计划，有些情况需要通过hint来干预。MySQL8.0在hint的种类上进行了新增。本篇主要讲我们可以有哪些方法影响优化器。
有很多方法影响优化器。
mysql.engine_cost , mysql.server_cost 具体表字段的含义，怎么配置，配置后怎么生效的细节见官网。需要注意的是，配置完以上表后，得做一个FLUSH OPTIMIZER_COSTS; 的动作。
另外还有两个重要参数 optimizer_prune_level 、optimizer_search_depth 值得注意。
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-optimizations.html）
（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
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的使用？
这个大家应该很熟悉，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;（https://dev.mysql.com/doc/refman/8.0/en/resource-groups.html#resource-group-restrictions）
对于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/）
合作电话：010-64087828
社区邮箱：greatsql@greatdb.com
