MySQL 优化器提示(三)之 Optimizer Hints for Variable-Setting Hint Syntax

MySQL8.0版本增加了 SET_VAR hint 可以指定变量只影响当前语句，对于调试生产环境非常有用，因为有可能在特定的语句我们希望更改一个变量值，因为是生产环境，又不想影响其它语句。比如一个排序语句特别慢。我们想加大sort_buffer_size 看看效果。下面就来慢慢聊聊一下。

一、定义

Syntax of the SET_VAR hint:
SET_VAR(var_name = value)

检查是否生效：

select @@max_length_for_sort_data

 SELECT /*+ SET_VAR(max_length_for_sort_data = 2K) */ @@max_length_for_sort_data;
复制

通上图能看到。这个Variable-Setting Hint 生效了，在这个语句级别改为了2K

多个变量影响一个语句

 SELECT /*+ SET_VAR(max_length_for_sort_data = 3M) SET_VAR(sort_buffer_size = 1G) SET_VAR(max_sort_length = 1M) */ @@max_length_for_sort_data,@@sort_buffer_size,@@max_sort_lengt
复制

通过上图可以看到三个变量都改了

影响范围

优化器影响范围有四种。Global、Query block、Table、index
这里贴上官方说明:
Optimizer Hint Overview
Optimizer hints apply at different scope levels:

Global: The hint affects the entire statement

Query block: The hint affects a particular query block within a statement

Table-level: The hint affects a particular table within a query block

Index-level: The hint affects a particular index within a table

而今天讲的 SET_VAR 就是 Global级的。

二、利用SET_VAR调优

顺便说一个MySQL排序

MySQL排序有两种：索引排索与文件排序
索引排索就是利用数据库中现有索引顺序输出，无需额外的排序。
文件排序是指无法利用索引排序，需要额外排序，当使用文件排序的时候，我们的执行计划中就会出现 Using filesort
文件排序又分为单路排序与双路排序，是全部在内存中完成的排序，还是在磁盘上排序了，然后归并排并这个执行计划并不知道。只有查optimizer_trace才能知道。

语句实例

先打开跟踪

set optimizer_trace = 'enabled=on' 
复制

我先将max_length_for_sort_data改小。强制走双路排序

 select  
/*+ 
 SET_VAR(max_length_for_sort_data = 20) 
*/
 AGENTID,USERID,BEGINTIME from services 
 where agentid = 'eeda9317cf4e44ac869f127be1b417b1'
 order by begintime desc
 limit 3000
复制

确认MySQL是否走的双路排序

select * from information_schema.optimizer_trace
复制

通过sort_mode可以确认上述语句就是采用的双路排序。

我们改大max_length_for_sort_data 试试

 select  
/*+ 
 SET_VAR(max_length_for_sort_data = 400) 
*/
 AGENTID,USERID,BEGINTIME from services 
 where agentid = 'eeda9317cf4e44ac869f127be1b417b1'
 order by begintime desc
 limit 3000
复制

select * from information_schema.optimizer_trace
复制

可以看到的确走了单路排序了，因为单路排序把我查询的字段都放入了soft_buffer. 但sort_buffer_size太小，放不下。所以从num_initial_chunks_spilled_to_disk 看。产生了磁盘临时文件,不是在内存中完成的排序。

我们再继续调大 sort_buffer_size

 select  
/*+ 
 SET_VAR(max_length_for_sort_data = 400) 
  SET_VAR(sort_buffer_size =1M )
*/
 AGENTID,USERID,BEGINTIME from services 
 where agentid = 'eeda9317cf4e44ac869f127be1b417b1'
 order by begintime desc
 limit 3000
复制

select * from information_schema.optimizer_trace
复制

我们调大了sort_buffer_size 就可以看到直接走的内存排序了。

最后我们再关注一下。对于这个语句，当底 max_length_for_sort_data 与sort_buffer_size 设置多少合理呢。

上图中两个参数可以给我们答案：
max_length_for_sort_data : max_length_for_sort_data 只需要大于row_size 就会走单路排序。
sort_buffer_size： sort_buffer_size者 sort_buffer_size > peak_memory_used 就可以了。

如上图 row_size = 303 peak_memory_used = 911.4365KB。
我把这个语句设置为(max_length_for_sort_data = 304、sort_buffer_size =912k ）

 select  
/*+ 
 SET_VAR(max_length_for_sort_data = 304) 
  SET_VAR(sort_buffer_size =912k )
*/
 AGENTID,USERID,BEGINTIME from services 
 where agentid = 'eeda9317cf4e44ac869f127be1b417b1'
 order by begintime desc
 limit 3000
复制

select * from information_schema.optimizer_trace
复制

可以看到。此语句还是采用的单路排序，且是在内存中完成的排序。

MySQL优化器提示系列文章

MySQL 优化器提示(一)之 Join-Order Optimizer Hints
MySQL 优化器提示(二)之 Optimizer Hints for Naming Query Blocks
MySQL 优化器提示(三)之 Optimizer Hints for Variable-Setting Hint Syntax