hhdb数据库介绍(9-11)

计算节点特色功能

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。

posted @   恒辉信达  阅读(10)  评论(0)  编辑  收藏  举报
相关博文：
· hhdb数据库介绍(9-17)
· hhdb数据库介绍(9-28)
· Mysql 系列 | explain 详解 + profile
· mysql EXPLAIN 用法解读
· MySQL ---explain/desc
阅读排行：
· 全程不用写代码，我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
点击右上角即可分享
微信分享提示