GreatSQL 在SQL中如何使用 HINT 語法修改會話變量
更新時間：2024年10月17日 10:08:58 作者：GreatSQL
GreatSQL推出新的優(yōu)化Hint功能,名為SET_VAR,允許用戶在查詢語句中修改數據庫的會話變量,但只對當前查詢會話生效,不影響其他會話,本文給大家介紹GreatSQL 在SQL中如何使用 HINT 語法修改會話變量,感興趣的朋友一起看看吧
GreatSQL 在SQL中使用 HINT 語法修改會話變量
在 GreatSQL 支持一種新的優(yōu)化Hint，名字叫SET_VAR，這個特性支持用戶在查詢語句里修改 GreatSQL 數據庫的一些會話變量，當然修改只是對當前查詢會話生效，不會影響到其他會話。
SET_VAR語法
SET_VAR這個hint用于臨時設置系統變量的會話值(在單個語句的持續(xù)時間內有效)
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)
從執(zhí)行計劃上看，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
這個新特性是不是很方便呢，之前由于優(yōu)化器的某些設置，少量sql語句選擇了錯誤的執(zhí)行計劃，導致查詢語句性能低下，又不能隨意更改線上數據庫的變量，有了SET_VAR這個新特性,對于這種情況，可以考慮在查詢語句中使用set_var優(yōu)化這條語句。
我們知道，使用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: 3637 Message: 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提示并發(fā)出警告
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
參考文檔
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
Enjoy GreatSQL ??
關于 GreatSQL
GreatSQL是適用于金融級應用的國內自主開源數據庫，具備高性能、高可靠、高易用性、高安全等多個核心特性，可以作為MySQL或Percona Server的可選替換，用于線上生產環(huán)境，且完全免費并兼容MySQL或Percona Server。
到此這篇關于GreatSQL 在SQL中使用 HINT 語法修改會話變量的文章就介紹到這了,更多相關GreatSQL使用 HINT 語法修改會話變量內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！
相關文章
SQL?Server數據庫中已存在名為'student'對象的解決辦法
這篇文章主要給大家介紹了關于SQL?Server數據庫中已存在名為'student'對象的解決辦法,解決方法很簡單,并且也很實用,不止有這一個用處,文中通過圖文介紹的非常詳細,需要的朋友可以參考下2023-11-11
這篇文章主要介紹了關于SQL查詢語句關鍵字方法，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教2022-10-10
在開發(fā)網站時，我們需要對用戶注冊的郵箱進行核對與驗證，用戶填寫的郵箱是否有效郵箱。這篇文章主要介紹了如何驗證會員系統中用戶的郵箱是否真實存在的相關資料,需要的朋友可以參考下2016-10-10
五種提高 SQL 性能的方法...2007-03-03
本文主要介紹了SQL?server?中的any和all運算符，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧2023-01-01
過程需要參數 ''@statement'' 為 ''ntext/nchar/nvarchar'' 類型
過程需要參數2009-04-04
創(chuàng)建表之前一定要先了解數據類型與約束,這樣我們才能創(chuàng)建正確的表,下面這篇文章主要給大家介紹了關于SQL?Server如何建表的相關資料,文中通過圖文介紹的非常詳細,需要的朋友可以參考下2022-10-10
本文將詳細介紹SQL SERVER如何判斷某個字段包含大寫字母的方法，需要的朋友可以參考下2012-11-11
在T-Sql中，一般top數據不確定的情況下，都是拼sql，這樣無論是效率還是可讀性都不好。應該使用下面參數化Top方式2009-07-07
目前工作中很少用到SQL?Server了,最近需要測試幾個表,需要搭建一個SQL?Server數據庫服務,這里做個總結吧,安裝這里就不做詳細介紹了,本文只介紹如何開啟SQL?Server網絡訪問,感興趣的小伙伴跟著小編一起來看看吧2024-09-09