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是會話變數的取值

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: 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提示併發出警告

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

• https://dev.mysql.com/doc/refman/8.0/en/hash-joins.html

---

Enjoy GreatSQL 😃

關於 GreatSQL

GreatSQL是適用於金融級應用的國內自主開源資料庫，具備高效能、高可靠、高易用性、高安全等多個核心特性，可以作為MySQL或Percona Server的可選替換，用於線上生產環境，且完全免費併相容MySQL或Percona Server。

相關連結： GreatSQL社群 Gitee GitHub Bilibili

GreatSQL社群：

社群部落格有獎徵稿詳情：https://greatsql.cn/thread-100-1-1.html

技術交流群：

微信：掃碼新增GreatSQL社群助手微信好友，傳送驗證資訊加群。