写点什么

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

作者:GreatSQL
  • 2024-10-16
    福建
  • 本文字数:4055 字

    阅读完需:约 13 分钟

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

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

SET_VAR 语法

SET_VAR 这个 hint 用于临时设置系统变量的会话值(在单个语句的持续时间内有效)


SET_VAR 的用法: SET_VAR(var_name=value)


var_name 是被临时修改的会话变量名,value 是会话变量的取值


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

GreatSQL 8.0 之前的操作方法

在 GreatSQL 8.0 之前要对一个查询进行会话变量修改,需要怎么操作:


1.查询之前的系统变量


greatsql> SELECT @@optimizer_switch;
复制代码


2.备份系统变量


greatsql> SET @old_optimizer_switch = @@optimizer_switch;
复制代码


3.设置新的变量


greatsql> SET optimizer_switch='index_merge=off';
复制代码


4.运行查询语句


greatsql> SELECT empno,ename,deptno from emp limit 1;
复制代码


5.恢复之前的系统变量


greatsql> SET optimizer_switch = @old_optimizer_switch;
复制代码


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

GreatSQL 8.0 的操作方法

greatsql>explain SELECT  empno,ename,deptno FROM emp WHERE  deptno=10 or ename='CLARK';+----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+--------------------------------------------+| id | select_type | table | partitions | type        | possible_keys    | key              | key_len | ref  | rows | filtered | Extra                                      |+----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+--------------------------------------------+|  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 |+----+-------------+-------+------------+-------------+------------------+------------------+---------+------+------+----------+--------------------------------------------+1 row in set, 1 warning (0.01 sec)
复制代码


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


greatsql>explain SELECT /*+ SET_VAR(optimizer_switch='index_merge=off') */  empno,ename,deptno FROM emp WHERE deptno=10 or ename='CLARK';+----+-------------+-------+------------+------+------------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys    | key  | key_len | ref  | rows | filtered | Extra       |+----+-------------+-------+------------+------+------------------+------+---------+------+------+----------+-------------+|  1 | SIMPLE      | emp   | NULL       | ALL  | deptno,idx_ename | NULL | NULL    | NULL |   14 |    38.10 | Using where |+----+-------------+-------+------------+------+------------------+------+---------+------+------+----------+-------------+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。


CREATE TABLE t1 (c1 INT, c2 INT);CREATE TABLE t2 (c1 INT, c2 INT);CREATE TABLE t3 (c1 INT, c2 INT);
复制代码


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


greatsql> SET @@cte_max_recursion_depth = 99999999;greatsql> INSERT INTO t1       WITH recursive t AS (       SELECT 1 AS c1  ,1 AS c2       UNION ALL       SELECT t.c1+1,t.c1*2       FROM t       WHERE t.c1 <1000000      )       SELECT * FROM t;Query OK, 1000000 rows affected (10.63 sec)Records: 1000000  Duplicates: 0  Warnings: 0
greatsql> SELECT @@join_buffer_size;+--------------------+| @@join_buffer_size |+--------------------+| 262144 |+--------------------+1 row in set (0.00 sec)
greatsql> SELECT * FROM t1 JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2) JOIN t3 ON (t2.c1 = t3.c1);Empty set (6.91 sec)
greatsql> SELECT /*+ SET_VAR(join_buffer_size=16777216) */ * FROM t1 JOIN t2 ON (t1.c1 = t2.c1 AND t1.c2 < t2.c2) JOIN t3 ON (t2.c1 = t3.c1);Empty set (5.87 sec)
复制代码

注意事项

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


greatsql> SELECT /*+ SET_VAR(collation_server = 'utf8mb4') */ 1;+---+| 1 |+---+| 1 |+---+1 row in set, 1 warning (0.01 sec)
greatsql> SHOW WARNINGS\G*************************** 1. row *************************** Level: Warning Code: 3637Message: Variable 'collation_server' cannot be set using SET_VAR hint.1 row in set (0.00 sec)
复制代码


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


SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=off') SET_VAR(max_heap_table_size = 1G) */ 1;
复制代码


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


SELECT /*+ SET_VAR(max_size = 1G) */ 1;SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=yes') */ 1;
复制代码


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


greatsql> SELECT /*+ SET_VAR(max_size = 1G) */ 1;+---+| 1 |+---+| 1 |+---+1 row in set, 1 warning (0.00 sec)
greatsql> show warnings;+---------+------+---------------------------------------------+| Level | Code | Message |+---------+------+---------------------------------------------+| Warning | 3128 | Unresolved name 'max_size' for SET_VAR hint |+---------+------+---------------------------------------------+1 row in set (0.00 sec)
greatsql> SELECT /*+ SET_VAR(optimizer_switch = 'mrr_cost_based=yes') */ 1;+---+| 1 |+---+| 1 |+---+1 row in set, 1 warning (0.00 sec)
greatsql> show warnings;+---------+------+-------------------------------------------------------------------------------+| Level | Code | Message |+---------+------+-------------------------------------------------------------------------------+| Warning | 1231 | Variable 'optimizer_switch' can't be set to the value of 'mrr_cost_based=yes' |+---------+------+-------------------------------------------------------------------------------+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

参考文档


发布于: 刚刚阅读数: 3
用户头像

GreatSQL

关注

GreatSQL社区 2023-01-31 加入

GreatSQL是由万里数据库维护的MySQL分支,专注于提升MGR可靠性及性能,支持InnoDB并行查询特性,是适用于金融级应用的MySQL分支版本。 社区:https://greatsql.cn/ Gitee: https://gitee.com/GreatSQL/GreatSQL

评论

发布
暂无评论
GreatSQL 在SQL中使用 HINT 语法修改会话变量_数据库_GreatSQL_InfoQ写作社区