GreatSQL 在SQL中如何使用 HINT 語(yǔ)法修改會(huì)話變量

更新時(shí)間：2024年10月17日 10:08:58 作者：GreatSQL

GreatSQL推出新的優(yōu)化Hint功能,名為SET_VAR,允許用戶在查詢語(yǔ)句中修改數(shù)據(jù)庫(kù)的會(huì)話變量,但只對(duì)當(dāng)前查詢會(huì)話生效,不影響其他會(huì)話,本文給大家介紹GreatSQL 在SQL中如何使用 HINT 語(yǔ)法修改會(huì)話變量,感興趣的朋友一起看看吧

GreatSQL 在SQL中使用 HINT 語(yǔ)法修改會(huì)話變量

在 GreatSQL 支持一種新的優(yōu)化Hint，名字叫SET_VAR，這個(gè)特性支持用戶在查詢語(yǔ)句里修改 GreatSQL 數(shù)據(jù)庫(kù)的一些會(huì)話變量，當(dāng)然修改只是對(duì)當(dāng)前查詢會(huì)話生效，不會(huì)影響到其他會(huì)話。

SET_VAR語(yǔ)法

SET_VAR這個(gè)hint用于臨時(shí)設(shè)置系統(tǒng)變量的會(huì)話值(在單個(gè)語(yǔ)句的持續(xù)時(shí)間內(nèi)有效)

SET_VAR的用法: SET_VAR(var_name=value)

var_name是被臨時(shí)修改的會(huì)話變量名，value是會(huì)話變量的取值

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 之前要對(duì)一個(gè)查詢進(jìn)行會(huì)話變量修改，需要怎么操作：

1.查詢之前的系統(tǒng)變量

greatsql> SELECT @@optimizer_switch;

2.備份系統(tǒng)變量

greatsql> SET @old_optimizer_switch = @@optimizer_switch;

3.設(shè)置新的變量

greatsql> SET optimizer_switch='index_merge=off';

4.運(yùn)行查詢語(yǔ)句

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

5.恢復(fù)之前的系統(tǒng)變量

greatsql> SET optimizer_switch = @old_optimizer_switch;

是不是有點(diǎn)繁瑣，現(xiàn)在我們使用SET_VAR這個(gè)新特性，很方便的就可以做這個(gè)操作了。

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)

從執(zhí)行計(jì)劃上看，SQL語(yǔ)句使用了索引合并（type=index_merge），如果不想該sql使用索引合并，則可以通過(guò)SET_VAR進(jìn)行控制。

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

這個(gè)新特性是不是很方便呢，之前由于優(yōu)化器的某些設(shè)置，少量sql語(yǔ)句選擇了錯(cuò)誤的執(zhí)行計(jì)劃，導(dǎo)致查詢語(yǔ)句性能低下，又不能隨意更改線上數(shù)據(jù)庫(kù)的變量，有了SET_VAR這個(gè)新特性,對(duì)于這種情況，可以考慮在查詢語(yǔ)句中使用set_var優(yōu)化這條語(yǔ)句。

我們知道，使用hash jion時(shí)，會(huì)使用到j(luò)oin buffer，join buffer的大小由join_buffer_size控制，其默認(rèn)值為256k，哈希連接不能使用超過(guò)此數(shù)量的內(nèi)存。當(dāng)哈希連接所需的內(nèi)存超過(guò)可用量時(shí)，GreatSQL將使用磁盤上的文件來(lái)處理此問題，使用到了磁盤文件，性能會(huì)下降，如果只想針對(duì)單條語(yǔ)句設(shè)置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);

分別對(duì)t1,t2,t3 插入100萬(wàn)，200萬(wàn)，300萬(wàn)數(shù)據(jù)

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)

注意事項(xiàng)

1、并非所有會(huì)話變量都允許與SET_VAR一起使用。如果設(shè)置不支持用SET_VAR更改的系統(tǒng)變量，則會(huì)出現(xiàn)警告。

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: 3637
Message: Variable 'collation_server' cannot be set using SET_VAR hint.
1 row in set (0.00 sec)

2、SET_VAR語(yǔ)法只允許設(shè)置單個(gè)變量，但可以給出多個(gè)提示來(lái)設(shè)置多個(gè)變量:

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

3、如果沒有這個(gè)系統(tǒng)變量或變量值不正確，則忽略SET_VAR提示并發(fā)出警告

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

第1條語(yǔ)句沒有 max_size 這個(gè)變量，語(yǔ)句2 的mrr_cost_based= on或者off, 企圖將其設(shè)置為 yes是錯(cuò)誤的，這兩個(gè)語(yǔ)句的 hint 都會(huì)被忽略，并產(chǎn)生一個(gè)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提示只允許在語(yǔ)句級(jí)別使用。如果在子查詢中使用，則會(huì)被忽略并給出警告。

復(fù)制會(huì)忽略復(fù)制語(yǔ)句中的SET_VAR，以避免潛在的安全問題。

SET_VAR支持的變量

SET_VAR只是對(duì)部分變量可以用的，整理了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 ??

關(guān)于 GreatSQL

GreatSQL是適用于金融級(jí)應(yīng)用的國(guó)內(nèi)自主開源數(shù)據(jù)庫(kù)，具備高性能、高可靠、高易用性、高安全等多個(gè)核心特性，可以作為MySQL或Percona Server的可選替換，用于線上生產(chǎn)環(huán)境，且完全免費(fèi)并兼容MySQL或Percona Server。

到此這篇關(guān)于GreatSQL 在SQL中使用 HINT 語(yǔ)法修改會(huì)話變量的文章就介紹到這了,更多相關(guān)GreatSQL使用 HINT 語(yǔ)法修改會(huì)話變量?jī)?nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: