hhdb資料庫介紹(9-11)

恒辉信达發表於2024-11-27
資料庫

計算節點特色功能

EXPLAIN

在計算節點中，EXPLAIN語句用於顯示SQL語句的路由計劃。

mysql> explain  select t1.name,t2.name from test3 t1,test31 t2 where (t1.id>5 and t1.id<8) and (t2.id>5 and t2.id<18) group by t1.name,t2.name;
+----------+-------------------------------+-------------------------------------------------------------------------+
| datanode | type                          | sql                                                                     |
+----------+-------------------------------+-------------------------------------------------------------------------+
| 1        | PUSHDOWN_MULTINODE_QUERY::_$1 | SELECT `t1`.name FROM `TEST3` AS T1 WHERE `t1`.id < 8 AND `t1`.id > 5   |
| 2        | PUSHDOWN_MULTINODE_QUERY::_$1 | SELECT `t1`.name FROM `TEST3` AS T1 WHERE `t1`.id < 8 AND `t1`.id > 5   |
| 3        | PUSHDOWN_MULTINODE_QUERY::_$1 | SELECT `t1`.name FROM `TEST3` AS T1 WHERE `t1`.id < 8 AND `t1`.id > 5   |
| 4        | PUSHDOWN_MULTINODE_QUERY::_$1 | SELECT `t1`.name FROM `TEST3` AS T1 WHERE `t1`.id < 8 AND `t1`.id > 5   |
| 5        | PUSHDOWN_MULTINODE_QUERY::_$1 | SELECT `t1`.name FROM `TEST3` AS T1 WHERE `t1`.id < 8 AND `t1`.id > 5   |
| 6        | PUSHDOWN_MULTINODE_QUERY::_$1 | SELECT `t1`.name FROM `TEST3` AS T1 WHERE `t1`.id < 8 AND `t1`.id > 5   |
| 7        | PUSHDOWN_MULTINODE_QUERY::_$1 | SELECT `t1`.name FROM `TEST3` AS T1 WHERE `t1`.id < 8 AND `t1`.id > 5   |
| 8        | PUSHDOWN_MULTINODE_QUERY::_$1 | SELECT `t1`.name FROM `TEST3` AS T1 WHERE `t1`.id < 8 AND `t1`.id > 5   |
| 0        | JOIN_SUB:_$1                  |                                                                         |
| 1        | PUSHDOWN_MULTINODE_QUERY::_$1 | SELECT `t2`.name FROM `TEST31` AS T2 WHERE `t2`.id < 18 AND `t2`.id > 5 |
| 2        | PUSHDOWN_MULTINODE_QUERY::_$1 | SELECT `t2`.name FROM `TEST31` AS T2 WHERE `t2`.id < 18 AND `t2`.id > 5 |
| 3        | PUSHDOWN_MULTINODE_QUERY::_$1 | SELECT `t2`.name FROM `TEST31` AS T2 WHERE `t2`.id < 18 AND `t2`.id > 5 |
| 4        | PUSHDOWN_MULTINODE_QUERY::_$1 | SELECT `t2`.name FROM `TEST31` AS T2 WHERE `t2`.id < 18 AND `t2`.id > 5 |
| 5        | PUSHDOWN_MULTINODE_QUERY::_$1 | SELECT `t2`.name FROM `TEST31` AS T2 WHERE `t2`.id < 18 AND `t2`.id > 5 |
| 6        | PUSHDOWN_MULTINODE_QUERY::_$1 | SELECT `t2`.name FROM `TEST31` AS T2 WHERE `t2`.id < 18 AND `t2`.id > 5 |
| 7        | PUSHDOWN_MULTINODE_QUERY::_$1 | SELECT `t2`.name FROM `TEST31` AS T2 WHERE `t2`.id < 18 AND `t2`.id > 5 |
| 8        | PUSHDOWN_MULTINODE_QUERY::_$1 | SELECT `t2`.name FROM `TEST31` AS T2 WHERE `t2`.id < 18 AND `t2`.id > 5 |
| 0        | JOIN_SUB:_$2                  |                                                                         |
| 0        | JOIN_$1_$2                    |                                                                         |
| 0        | AGGREGATION_$1                |                                                                         |
+----------+-------------------------------+-------------------------------------------------------------------------+
20 rows in set (0.012 sec)

XPLAIN支援顯示JOIN語句、UNION/UNION ALL、子查詢語句的路由計劃。其中：

  • DATANODE列為資料節點ID，0表示不會實際下發到儲存節點；
  • TYPE列為SQL下發的執行動作型別；
  • SQL列則是在對應節點上會實際執行的SQL。

TYPE型別包括：

  • RESPONSE_DIRECT：直接返回的查詢
  • FORWARD_PRIMARY： 轉發到PRIMARY執行
  • CONSTANT_RANDOM_QUERY：下發到隨機節點的常量查詢
  • PUSHDOWN_ONENODE_QUERY：直接下發的單節點普通查詢
  • PUSHDOWN_MULTINODE_QUERY：直接下發的多節點普通查詢
  • PUSHDOWN_RANDOM_QUERY：下發到隨機節點的普通查詢
  • PUSHDOWN_AGGREGATION_QUERY：直接下發的聚合查詢
  • COMMON_QUERY：普通查詢
  • AGGREGATION：結果集需要簡單的聚合處理，一次SQL就能返回結果
  • HAVING_FILTER：結果集過濾
  • BIG_OFFSET_LIMIT：超大offset的limit查詢語句
  • SUBQUERY：子查詢
  • GLOBAL_LOCK_SELECT：涉及全域性表鎖的查詢
  • GLOBAL_RANDOM_IUD：涉及全域性表隨機函式的IUD語句
  • USER_VAR_FETCH：更新使用者變數的值
  • UPDATE_DELETE_SUBQUERY：UPDATE/DELETE 子查詢
  • ROUTE_BY_GLOBAL_INDEX：根據全域性索引路由
  • LOAD_DATA：匯入資料
  • INTO_OUT_FILE：匯出資料
  • ONE_NODE_IUD：單節點IUD語句
  • MULTI_NODE_IUD：多節點IUD語句
  • INNER_TEMP_TABLE：內部臨時表
  • UPDATE_SHARDING_KEY：更新分片欄位的值
  • RELATIVE_IUD：父子表相關的IUD語句
  • INFORMATION_SCHEMA：information_schema相關查詢
  • DAL_SHOW：show 命令聚合
  • IUD_TRIGGER：會觸發操作的IUD語句
  • GLOBAL_IUD：全域性表的IUD
  • 其他型別，還包括：SUM_DISTINCT、COUNT_DISTINCT、AVG_DISTINCT、GROUP_CONCAT、JOIN、JOIN_SUB、BNL_JOIN、BNL_JOIN_SUB、UNION、UPDATE_DELETE_JOIN、UPDATE_DELETE_LIMIT、DDL、ORACLE_SEQUENCE、FORWARD_CALCITE、HINT、CALL、EXCEPT、MINUS、INTERSECT、、ROWNUM、DROP_DATABASE

EXPLAIN的注意點：

  • 對於直接下發的SQL，EXPLAIN顯示的SQL，和儲存節點general_log中顯示的會在格式上存在差異，EXPLAIN顯示的是格式化的SQL；
  • EXPLAIN顯示SQL需要執行多個DATANODE，在實際查詢時，會根據上條子SQL的返回結果，進行二次路由，後執行的SQL最終可能只會在部分節點執行；
  • 部分SQL（即計算節點本身不支援）轉發到第三方庫解析的，EXPLAIN可能會顯示不完全。

EXPLAINDN

在計算節點中，EXPLAINDN語句可直接下發到路由的資料節點上執行SQL語句執行計劃,作用等同於MySQL中的EXPLAIN。

EXPLAINDN語法標準格式:

EXPLAINDN[=dnid_value] 要執行的SQL

注意
dnid_value的值為某個資料節點的ID號。使用者可以替換dnid_value的值來指定具體的分片節點。

查詢出的表欄位結構資訊如下：

欄位名 說明
id select查詢的序列號，包含一組數字，表示查詢中執行select子句或操作表的順序
select_type 查詢的型別，主要用於區別普通查詢、聯合查詢、子查詢等的複雜查詢
table 顯示這一行的資料是關於哪張表的
type 顯示查詢使用了何種型別 system>const>eq_ref>ref>range>index>ALL
possible_keys 可能用在這條語句上的索引
key 顯示計算節點實際決定使用的鍵。如果沒有索引被選擇，鍵是NULL
key_len 顯示計算節點決定使用的鍵長度。如果鍵是NULL，長度是NULL
ref 顯示哪個列或常數與key一起用於從表中選擇行
rows rows列顯示計算節點相信它必須檢驗以執行查詢的行數
Extra Only index，表示資訊只用索引樹中的資訊檢索出的
where used，表示一個WHERE子句將被用來限制哪些行與下一個表匹配
impossible where 表示用不到where

例如：

cara@127.0.0.1:cara 5.7.25 04:37:57> explaindn=1 select * from hotdb_tableinfo;
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table           | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | hotdb_tableinfo | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   26 |   100.00 | NULL  |
+----+-------------+-----------------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.14 sec)
cara@127.0.0.1:cara 5.7.25 05:06:38> explaindn=all select * from hotdb_logic_db;
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table          | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | hotdb_logic_db | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | hotdb_logic_db | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | hotdb_logic_db | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
|  1 | SIMPLE      | hotdb_logic_db | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+----------------+------------+------+---------------+------+---------+------+------+----------+-------+
4 rows in set, 1 warning (0.01 sec)

EXPLAINDN的注意點：

1.目前支援SELECT、INSERT、UPDATE、DELETE、REPLACE語句的執行計劃；

在這裡插入程式碼片root@127.0.0.1:hotdb 8.0.30 02:15:46> EXPLAINDN=1 SELECT * FROM HOTDB_T1 WHERE ID=100;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | hotdb_t1 | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

root@127.0.0.1:hotdb 8.0.30 02:43:08> EXPLAINDN=ALL INSERT INTO HOTDB_T1 VALUES(1002,876,'501','979');
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | INSERT      | hotdb_t1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

EXPLAINDN=1 UPDATE HOTDB_T1 set k='876' where id=100;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | UPDATE      | HOTDB_T1 | NULL       | range | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

root@127.0.0.1:hotdb 8.0.30 02:52:01> EXPLAINDN=2 DELETE FROM HOTDB_T1 WHERE ID=200;
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | DELETE      | HOTDB_T1 | NULL       | range | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using where |
+----+-------------+----------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

root@127.0.0.1:hotdb 8.0.30 05:08:04> explaindn replace into HOTDB_T2 select * from HOTDB_T1 limit 1;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | REPLACE     | HOTDB_T2 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | NULL  |
|  1 | SIMPLE      | HOTDB_T1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  326 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)

2.支援跨邏輯庫的執行計劃；

root@127.0.0.1:hotdb 8.0.30 05:07:59> EXPLAINDN INSERT INTO HOTDBTEST2.HOTDB_T3 SELECT * FROM HOTDBTEST.HOTDB_T1 LIMIT 1;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | INSERT      | HOTDB_T3 | NULL       | ALL  | NULL          | NULL | NULL    | NULL | NULL |     NULL | NULL  |
|  1 | SIMPLE      | HOTDB_T1 | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  326 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
2 rows in set, 1 warning (0.01 sec)

3.暫不支援跨分片複雜查詢；
4.當指定查詢的資料節點與實際路由節點不一致時，會有相應的錯誤提示；

cara@127.0.0.1:cara 5.7.25 05:04:47> select * from hotdb_logic_db where logic_dbid=45;
+------------+--------------+---------------------------+-----------------+--------------------+------+
| logic_dbid | logic_dbname | default_nodes             | default_charset | default_collate    | DNID |
+------------+--------------+---------------------------+-----------------+--------------------+------+
|         45 | EZOFFICE     | 1,2,3,5,94,95,96,97,98,99 | utf8mb4         | utf8mb4_general_ci |   44 |
+------------+--------------+---------------------------+-----------------+--------------------+------+
1 row in set (0.00 sec)

cara@127.0.0.1:cara 5.7.25 05:06:32> explaindn=1 select * from hotdb_logic_db where logic_dbid=45;
ERROR 10010 (HY000): unsupported explaindn statement

OnlineDDL

計算節點管理端（3325）支援OnlineDDL功能，保證了在進行表變更時，不會阻塞線上業務讀寫，庫依然能正常對外提供訪問，具體使用方法如下：

登入3325端管理埠，使用onlineddl "[DDLSTATEMENT]"語法可以執行onlineddl語句，例如：onlineddl "alter table customer add column testddl varchar(20) default '測試onlineddl'";
執行show @@onlineddl語句，即可顯示當前正在執行的OnlineDDL語句及語句執行速度，progress顯示當前DDL執行進度（單位：%），speed顯示為當前DDL執行速度（單位：行/ms），例如：

mysql> show @@onlineddl;
+--------------+-------------------------------------------------------------------------------+----------+---------+
| schema       | onlineddl                                                                     | progress | speed   |
+--------------+-------------------------------------------------------------------------------+----------+---------+
| TEST_DML_JWY | ALTER TABLE CUSTOMER ADD COLUMN TESTDDL VARCHAR(20) DEFAULT '測試ONLINEDDL'   |   0.2300 | 23.3561 |
+--------------+-------------------------------------------------------------------------------+----------+---------+

注意
onlineddl 語句不是執行下去就代表DDL完成， 返回了"Query OK, 0 rows affected
"僅代表DDL語句可以執行， 如果想看是否執行完成，要檢視show @@onlineddl中progress 顯示的進度。show
@@onlineddl結果為空時，代表所有DDL執行完畢且當前無其他DDL任務，如果中途因為網路或其他異常DDL中斷，會回滾整個DDL。

相關文章