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

 

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']]
    )