Yc-Ma Blog

虚拟图ID系统相关存储过程实现

 
  • Yc-Ma
  • Apr 07, 2021

Here’s the table of contents:

  1. 虚拟图ID系统需要的函数如下
  2. 相关MySQL表结构
  3. 相关MySQL表-SQL存储实现数据的更新查询一起执行
  4. 过程方式实现虚拟ID获取
  5. 函数方式实现虚拟ID获取
  6. 函数方式实现虚拟ID获取-优化的函数

虚拟图ID系统需要的函数如下

  • 全局唯一ID表【使用MySQL实现】【根据查询性能考虑是否使用ES】【数据量可能会在千百亿】【用途：合并子图数据时使用的ID系统】 
    核心字段：
自增ID，限制字段

    // 节点ID表
输入：节点ID【唯一字段值】
返回：返回一个自增ID【虚拟图需要的自增ID】
数据存储在MySQL-ONGDB_UNIQUE_WITH_AUTO_NODE_ID
数据写入逻辑：INSERT IGNORED
函数：RETURN custom.auto.node.id('HORG20f2833a17034a812349e1933d9c5e5f') AS nodeVAutoId

    // 关系ID表
输入：主节点ID【唯一字段值】，关系类型，从节点ID【唯一字段值】
返回：返回一个自增ID
数据存储在MySQL-ONGDB_UNIQUE_WITH_AUTO_REL_ID
数据写入逻辑：INSERT IGNORED
函数：RETURN custom.auto.rel.id('HORG20f2833a17034a812349e1933d9c5e5f','担保','HORGa334e4057b12f53499c93d483c426993') AS relVAutoId

相关MySQL表结构

  • ONGDB_UNIQUE_WITH_AUTO_NODE_ID 
    CREATE TABLE `ONGDB_UNIQUE_WITH_AUTO_NODE_ID`  (
`huid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`hcode` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '唯一字段',
PRIMARY KEY (`huid`) USING BTREE,
UNIQUE INDEX `unique_key`(`hcode`) USING BTREE COMMENT '唯一索引'
) ENGINE = InnoDB AUTO_INCREMENT = 417310709 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '唯一字段对应的自增ID' ROW_FORMAT = Dynamic;
  • ONGDB_UNIQUE_WITH_AUTO_REL_ID 
    CREATE TABLE `ONGDB_UNIQUE_WITH_AUTO_REL_ID`  (
`huid` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`from_hcode` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '主节点唯一字段',
`to_hcode` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '从节点唯一字段',
`rel_type` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '关系类型字段',
PRIMARY KEY (`huid`) USING BTREE,
UNIQUE INDEX `unique_key`(`from_hcode`, `to_hcode`, `rel_type`) USING BTREE COMMENT '唯一索引'
) ENGINE = InnoDB AUTO_INCREMENT = 417310709 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '唯一字段对应的自增ID' ROW_FORMAT = Dynamic;

相关MySQL表-SQL存储实现数据的更新查询一起执行

  • 查询【存在返回结果；不存在写入一行数据】 
    SELECT autoNodeId('HORG20f2833a17034a812349e1933d9c5e5f11') AS autoNodeId;
  • 存储过程实现 
    DELIMITER //
CREATE FUNCTION autoNodeId(paramHcode VARCHAR(512)) RETURNS BIGINT
BEGIN
DECLARE resultHuid BIGINT DEFAULT -1;
IF(paramHcode IS NULL)
THEN
RETURN NULL;
ELSE
SELECT huid INTO resultHuid FROM ONGDB_UNIQUE_WITH_AUTO_NODE_ID WHERE hcode = paramHcode;
IF(resultHuid = -1)
THEN
INSERT IGNORE INTO ONGDB_UNIQUE_WITH_AUTO_NODE_ID(hcode) VALUES(paramHcode);
RETURN @@IDENTITY;
ELSE
RETURN resultHuid;
END IF;
END IF;
END;
//
  • 查询【存在返回结果；不存在写入一行数据】 
    SELECT autoRelId('HORG20f2833a17034a812349e1933d9c5e5f','HORGa334e4057b12f53499c93d483c426993','担保') AS autoRelId
  • 存储过程实现 
    DELIMITER //
CREATE FUNCTION autoRelId(paramFrom_hcode varchar(512), paramTo_hcode varchar(512), paramRel_type varchar(512)) RETURNS BIGINT
BEGIN
DECLARE resultHuid BIGINT DEFAULT -1;
IF(paramFrom_hcode IS NULL OR paramTo_hcode IS NULL OR paramRel_type IS NULL)
THEN
RETURN NULL;
ELSE
SELECT huid INTO resultHuid FROM ONGDB_UNIQUE_WITH_AUTO_REL_ID WHERE from_hcode = paramFrom_hcode AND to_hcode = paramTo_hcode AND rel_type = paramRel_type;
IF(resultHuid = -1)
THEN
INSERT INTO ONGDB_UNIQUE_WITH_AUTO_REL_ID(from_hcode,to_hcode,rel_type) VALUES(paramFrom_hcode,paramTo_hcode,paramRel_type);
RETURN @@IDENTITY;
ELSE
RETURN resultHuid;
END IF;
END IF;
END;
//

过程方式实现虚拟ID获取

  • 获取虚拟图的节点自增ID 
    // CALL custom.auto.node.id('HORG20f2833a17034a812349e1933d9c5e5f') YIELD nodeVAutoId
CALL apoc.custom.asProcedure(
 'auto.node.id',
 'WITH $hcode as hcode
CALL apoc.load.jdbcUpdate(\'jdbc:mysql://testlab-contentdb-dev.crkldnwly6ki.rds.cn-north-1.alibaba.com.cn:3306/analytics_graph_data?user=dev&password=testlabgogo&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC\',\'INSERT IGNORE INTO ONGDB_UNIQUE_WITH_AUTO_NODE_ID(hcode) VALUES(?);\',[hcode])
YIELD row
WITH hcode
CALL apoc.load.jdbc(\'jdbc:mysql://testlab-contentdb-dev.crkldnwly6ki.rds.cn-north-1.alibaba.com.cn:3306/analytics_graph_data?user=dev&password=testlabgogo&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC\',\'SELECT huid FROM ONGDB_UNIQUE_WITH_AUTO_NODE_ID WHERE hcode = ?;\',[hcode])
YIELD row
WITH row.huid as nodeVAutoId
RETURN nodeVAutoId',
 'read',
  [['nodeVAutoId','LONG']],
 [['hcode','STRING']],
 '全局唯一ID表返回一个自增ID'
)
  • 获取虚拟图的关系自增ID 
    // CALL custom.auto.rel.id('HORG20f2833a17034a812349e1933d9c5e5f','担保','HORGa334e4057b12f53499c93d483c426993') YIELD relVAutoId RETURN relVAutoId
CALL apoc.custom.asProcedure(
 'auto.rel.id',
 'WITH $from_hcode as from_hcode, $to_hcode as to_hcode, $rel_type as rel_type
CALL apoc.load.jdbcUpdate(\'jdbc:mysql://testlab-contentdb-dev.crkldnwly6ki.rds.cn-north-1.alibaba.com.cn:3306/analytics_graph_data?user=dev&password=testlabgogo&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC\',\'INSERT IGNORE INTO ONGDB_UNIQUE_WITH_AUTO_REL_ID(from_hcode,to_hcode,rel_type) VALUES(?,?,?);\',[from_hcode,to_hcode,rel_type])
YIELD row
WITH from_hcode, to_hcode, rel_type
CALL apoc.load.jdbc(\'jdbc:mysql://testlab-contentdb-dev.crkldnwly6ki.rds.cn-north-1.alibaba.com.cn:3306/analytics_graph_data?user=dev&password=testlabgogo&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC\',\'SELECT huid FROM ONGDB_UNIQUE_WITH_AUTO_REL_ID WHERE from_hcode = ? AND to_hcode = ? AND rel_type = ?;\',[from_hcode,to_hcode,rel_type])
YIELD row
WITH row.huid as relVAutoId
RETURN relVAutoId',
 'read',
  [['relVAutoId','LONG']],
 [['from_hcode','STRING'],['rel_type','STRING'],['to_hcode','STRING']],
 '全局唯一ID表返回一个自增ID'
)

函数方式实现虚拟ID获取

// RETURN custom.auto.node.id('HORG20f2833a17034a812349e1933d9c5e5f') AS nodeVAutoId
CALL apoc.custom.asFunction(
   'auto.node.id',
   'WITH $hcode as hcode
CALL apoc.load.jdbcUpdate(\'jdbc:mysql://testlab-contentdb-dev.crkldnwly6ki.rds.cn-north-1.alibaba.com.cn:3306/analytics_graph_data?user=dev&password=testlabgogo&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC\',\'INSERT IGNORE INTO ONGDB_UNIQUE_WITH_AUTO_NODE_ID(hcode) VALUES(?);\',[hcode])
YIELD row
WITH hcode
CALL apoc.load.jdbc(\'jdbc:mysql://testlab-contentdb-dev.crkldnwly6ki.rds.cn-north-1.alibaba.com.cn:3306/analytics_graph_data?user=dev&password=testlabgogo&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC\',\'SELECT huid FROM ONGDB_UNIQUE_WITH_AUTO_NODE_ID WHERE hcode = ?;\',[hcode])
YIELD row
WITH row.huid as nodeVAutoId
RETURN nodeVAutoId',
   'LONG',
   [['hcode','STRING']]
)

// RETURN custom.auto.rel.id('HORG20f2833a17034a812349e1933d9c5e5f','担保','HORGa334e4057b12f53499c93d483c426993') AS relVAutoId
CALL apoc.custom.asFunction(
   'auto.rel.id',
   'WITH $from_hcode as from_hcode, $to_hcode as to_hcode, $rel_type as rel_type
CALL apoc.load.jdbcUpdate(\'jdbc:mysql://testlab-contentdb-dev.crkldnwly6ki.rds.cn-north-1.alibaba.com.cn:3306/analytics_graph_data?user=dev&password=testlabgogo&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC\',\'INSERT IGNORE INTO ONGDB_UNIQUE_WITH_AUTO_REL_ID(from_hcode,to_hcode,rel_type) VALUES(?,?,?);\',[from_hcode,to_hcode,rel_type])
YIELD row
WITH from_hcode, to_hcode, rel_type
CALL apoc.load.jdbc(\'jdbc:mysql://testlab-contentdb-dev.crkldnwly6ki.rds.cn-north-1.alibaba.com.cn:3306/analytics_graph_data?user=dev&password=testlabgogo&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC\',\'SELECT huid FROM ONGDB_UNIQUE_WITH_AUTO_REL_ID WHERE from_hcode = ? AND to_hcode = ? AND rel_type = ?;\',[from_hcode,to_hcode,rel_type])
YIELD row
WITH row.huid as relVAutoId
RETURN relVAutoId',
   'LONG',
   [['from_hcode','STRING'],['rel_type','STRING'],['to_hcode','STRING']]
)

函数方式实现虚拟ID获取-优化的函数

  • olab.load.jdbc过程支持执行SQL查询与更新的复杂存储过程 
    // RETURN custom.auto.node.id('HORG20f2833a17034a812349e1933d9c5e5f') AS nodeVAutoId
CALL apoc.custom.asFunction(
 'auto.node.id',
 'WITH $hcode as hcode
CALL olab.load.jdbc(\'jdbc:mysql://testlab-contentdb-dev.crkldnwly6ki.rds.cn-north-1.alibaba.com.cn:3306/analytics_graph_data?user=dev&password=testlabgogo&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC\',\'SELECT autoNodeId(?) as autoNodeId\',[hcode])
YIELD row
WITH row.autoNodeId as nodeVAutoId
RETURN nodeVAutoId',
 'LONG',
 [['hcode','STRING']]
)

    // RETURN custom.auto.rel.id('HORG20f2833a17034a812349e1933d9c5e5f','担保','HORGa334e4057b12f53499c93d483c426993') AS relVAutoId
CALL apoc.custom.asFunction(
 'auto.rel.id',
 'WITH $from_hcode as from_hcode, $to_hcode as to_hcode, $rel_type as rel_type
CALL olab.load.jdbc(\'jdbc:mysql://testlab-contentdb-dev.crkldnwly6ki.rds.cn-north-1.alibaba.com.cn:3306/analytics_graph_data?user=dev&password=testlabgogo&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC\',\'SELECT autoRelId(?,?,?) as autoRelId\',[from_hcode,to_hcode,rel_type])
YIELD row
WITH row.autoRelId as relVAutoId
RETURN relVAutoId',
 'LONG',
 [['from_hcode','STRING'],['rel_type','STRING'],['to_hcode','STRING']]
)
Search