GreatSQL 在SQL中使用 HINT 语法修改会话变量

我是你强哥呢

GreatSQL 在SQL中使用 HINT 语法修改会话变量

在 GreatSQL 支持一种新的优化Hint，名字叫SET_VAR，这个特性支持用户在查询语句里修改 GreatSQL 数据库的一些会话变量，当然修改只是对当前查询会话生效，不会影响到其他会话。
SET_VAR语法

SET_VAR这个hint用于临时设置系统变量的会话值(在单个语句的持续时间内有效)
SET_VAR的用法: SET_VAR(var_name=value)
var_name是被临时修改的会话变量名，value是会话变量的取值

1. greatsql> SELECT @@unique_checks;SELECT /*+ SET_VAR(unique_checks=OFF) */ @@unique_checks;SELECT @@unique_checks;
2. SELECT /*+ SET_VAR(sort_buffer_size = 16M) */ name FROM people ORDER BY name;
3. INSERT /*+ SET_VAR(foreign_key_checks=OFF) */ INTO t2 VALUES(2);
4. SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off') */ 1;

复制代码

GreatSQL 8.0 之前的操作方法

在GreatSQL 8.0 之前要对一个查询进行会话变量修改，需要怎么操作：
1.查询之前的系统变量

1. greatsql> SELECT @@optimizer_switch;

复制代码

2.备份系统变量

1. greatsql> SET @old_optimizer_switch = @@optimizer_switch;

复制代码

3.设置新的变量

1. greatsql> SET optimizer_switch='index_merge=off';

复制代码

4.运行查询语句

1. greatsql> SELECT empno,ename,deptno from emp limit 1;

复制代码

5.恢复之前的系统变量

1. greatsql> SET optimizer_switch = @old_optimizer_switch;

复制代码

是不是有点繁琐，现在我们使用SET_VAR这个新特性，很方便的就可以做这个操作了。
GreatSQL 8.0的操作方法

1. greatsql>explain SELECT  empno,ename,deptno FROM emp WHERE  deptno=10 or ename='CLARK';
2. +----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+--------------------------------------------+
3. | id | select_type | table | partitions | type        | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                                      |
4. +----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+--------------------------------------------+
5. |  1 | SIMPLE      | emp   | NULL       | index_merge | deptno,idx_ename | deptno,idx_ename | 5,63    | NULL |    4 |   100.00 | Using union(deptno,idx_ename); Using where |
6. +----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+--------------------------------------------+
7. 1 row in set, 1 warning (0.01 sec)

复制代码

从执行计划上看，SQL语句使用了索引合并（type=index_merge），如果不想该sql使用索引合并，则可以通过SET_VAR进行控制。

1. greatsql>explain SELECT /*+ SET_VAR(optimizer_switch='index_merge=off') */  empno,ename,deptno FROM emp WHERE deptno=10 or ename='CLARK';
2. +----+-------------+-------+------------+------+------------------+------+---------+------+------+----------+-------------+
3. | id | select_type | table | partitions | type | possible_keys    | key  | key_len | ref  | rows | filtered | Extra       |
4. +----+-------------+-------+------------+------+------------------+------+---------+------+------+----------+-------------+
5. |  1 | SIMPLE      | emp   | NULL       | ALL  | deptno,idx_ename | NULL | NULL    | NULL |   14 |    38.10 | Using where |
6. +----+-------------+-------+------------+------+------------------+------+---------+------+------+----------+-------------+
7. 1 row in set, 1 warning (0.00 sec

复制代码

这个新特性是不是很方便呢，之前由于优化器的某些设置，少量sql语句选择了错误的执行计划，导致查询语句性能低下，又不能随意更改线上数据库的变量，有了SET_VAR这个新特性,对于这种情况，可以考虑在查询语句中使用set_var优化这条语句。
我们知道，使用hash jion时，会使用到join buffer，join buffer的大小由join_buffer_size控制，其默认值为256k，哈希连接不能使用超过此数量的内存。当哈希连接所需的内存超过可用量时，GreatSQL将使用磁盘上的文件来处理此问题，使用到了磁盘文件，性能会下降，如果只想针对单条语句设置join buffer就可以使用SET_VAR。

1. CREATE TABLE t1 (c1 INT, c2 INT);
2. CREATE TABLE t2 (c1 INT, c2 INT);
3. CREATE TABLE t3 (c1 INT, c2 INT);

复制代码

分别对t1,t2,t3 插入100万，200万，300万数据

1. greatsql> SET @@cte_max_recursion_depth = 99999999;
2. greatsql> INSERT INTO t1
3.        WITH recursive t AS (
4.        SELECT 1 AS c1  ,1 AS c2
5.        UNION ALL
6.        SELECT t.c1+1,t.c1*2
7.        FROM t
8.        WHERE t.c1 <1000000
9.       )
10.        SELECT * FROM t;
11. Query OK, 1000000 rows affected (10.63 sec)
12. Records: 1000000  Duplicates: 0  Warnings: 0
14. greatsql> SELECT @@join_buffer_size;
15. +--------------------+
16. | @@join_buffer_size |
17. +--------------------+
18. |             262144 |
19. +--------------------+
20. 1 row in set (0.00 sec)
22. greatsql> SELECT * FROM t1
23.            JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
24.            JOIN t3 ON (t2.c1 = t3.c1);
25. Empty set (6.91 sec)
27. greatsql> SELECT /*+ SET_VAR(join_buffer_size=16777216) */ * FROM t1
28.            JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2)
29.            JOIN t3 ON (t2.c1 = t3.c1);
30. Empty set (5.87 sec)

复制代码

注意事项

1、并非所有会话变量都允许与SET_VAR一起使用。如果设置不支持用SET_VAR更改的系统变量，则会出现警告。

1. greatsql> SELECT /*+ SET_VAR(collation_server = 'utf8mb4') */ 1;
2. +---+
3. | 1 |
4. +---+
5. | 1 |
6. +---+
7. 1 row in set, 1 warning (0.01 sec)
9. greatsql> SHOW WARNINGS\G
10. *************************** 1. row ***************************
11. Level: Warning
12.   Code: 3637
13. Message: Variable 'collation_server' cannot be set using SET_VAR hint.
14. 1 row in set (0.00 sec)

复制代码

2、SET_VAR语法只允许设置单个变量，但可以给出多个提示来设置多个变量:

1. SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off')
2. SET_VAR(max_heap_table_size = 1G) */ 1;

复制代码

3、如果没有这个系统变量或变量值不正确，则忽略SET_VAR提示并发出警告

1. SELECT /*+ SET_VAR(max_size = 1G) */ 1;
2. SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=yes') */ 1;

复制代码

第1条语句没有 max_size 这个变量，语句2 的mrr_cost_based= on或者off, 企图将其设置为 yes是错误的，这两个语句的 hint 都会被忽略，并产生一个warning。

1. greatsql> SELECT /*+ SET_VAR(max_size = 1G) */ 1;
2. +---+
3. | 1 |
4. +---+
5. | 1 |
6. +---+
7. 1 row in set, 1 warning (0.00 sec)
9. greatsql> show warnings;
10. +---------+------+---------------------------------------------+
11. | Level   | Code | Message                                     |
12. +---------+------+---------------------------------------------+
13. | Warning | 3128 | Unresolved name 'max_size' for SET_VAR hint |
14. +---------+------+---------------------------------------------+
15. 1 row in set (0.00 sec)
17. greatsql> SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=yes') */ 1;
18. +---+
19. | 1 |
20. +---+
21. | 1 |
22. +---+
23. 1 row in set, 1 warning (0.00 sec)
25. greatsql> show warnings;
26. +---------+------+-------------------------------------------------------------------------------+
27. | Level   | Code | Message                                                                       |
28. +---------+------+-------------------------------------------------------------------------------+
29. | Warning | 1231 | Variable 'optimizer_switch' can't be set to the value of 'mrr_cost_based=yes' |
30. +---------+------+-------------------------------------------------------------------------------+
31. 1 row in set (0.00 sec)

复制代码

4、SET_VAR提示只允许在语句级别使用。如果在子查询中使用，则会被忽略并给出警告。
复制会忽略复制语句中的SET_VAR，以避免潜在的安全问题。
SET_VAR支持的变量

SET_VAR只是对部分变量可以用的，整理了GreatSQL主要支持的变量供参考：

• bulk_insert_buffer_size
  
• default_table_encryption
  
• default_tmp_storage_engine
  
• div_precision_increment
  
• end_markers_in_json
  
• eq_range_index_dive_limit
  
• foreign_key_checks
  
• group_concat_max_len
  
• internal_tmp_mem_storage_engine
  
• join_buffer_size
  
• lock_wait_timeout
  
• max_error_count
  
• max_execution_time
  
• max_heap_table_size
  
• max_join_size
  
• max_length_for_sort_data
  
• max_points_in_geometry
  
• max_seeks_for_key
  
• max_sort_length
  
• optimizer_prune_level
  
• optimizer_search_depth
  
• optimizer_switch
  
• optimizer_trace_max_mem_size
  
• range_alloc_block_size
  
• read_buffer_size
  
• read_rnd_buffer_size
  
• secondary_engine_cost_threshold
  
• select_into_buffer_size
  
• select_into_disk_sync
  
• select_into_disk_sync_delay
  
• show_create_table_skip_secondary_engine
  
• sort_buffer_size
  
• sql_auto_is_null
  
• sql_big_selects
  
• sql_buffer_result
  
• sql_mode
  
• sql_require_primary_key
  
• sql_safe_updates
  
• sql_select_limit
  
• time_zone (≥ 8.0.17)
  
• timestamp
  
• tmp_table_size
  
• unique_checks
  
• updatable_views_with_limit
  
• use_secondary_engine
  
• windowing_use_high_precision
  

参考文档

• https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html#optimizer-hints-set-var
  
• https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
  
• https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html
  

Enjoy GreatSQL
来源:https://www.cnblogs.com/greatsql/p/18469374
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！