MySQL8.0 优化器介绍（四）

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)

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

#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 不支持表达式，=号右边必须是具体的值

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;

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，以及线程应使用哪个优先级执行。
这对于确定某些线程的优先级非常有用
执行优先级高于其他线程或防止资源争用。