You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@dolphinscheduler.apache.org by zh...@apache.org on 2022/06/16 07:24:12 UTC
[dolphinscheduler] branch dev updated: [imporve] Add index to table t_ds_relation_process_instance (#10461)
This is an automated email from the ASF dual-hosted git repository.
zhongjiajie pushed a commit to branch dev
in repository https://gitbox.apache.org/repos/asf/dolphinscheduler.git
The following commit(s) were added to refs/heads/dev by this push:
new 7aa9fa4965 [imporve] Add index to table t_ds_relation_process_instance (#10461)
7aa9fa4965 is described below
commit 7aa9fa496503423235414ec12be1794dd1cf3808
Author: SmallProgrammer <qi...@163.com>
AuthorDate: Thu Jun 16 15:24:05 2022 +0800
[imporve] Add index to table t_ds_relation_process_instance (#10461)
---
.../src/main/resources/sql/dolphinscheduler_mysql.sql | 4 +++-
.../src/main/resources/sql/dolphinscheduler_postgresql.sql | 2 ++
.../sql/upgrade/3.0.0_schema/mysql/dolphinscheduler_ddl.sql | 2 ++
.../sql/upgrade/3.0.0_schema/postgresql/dolphinscheduler_ddl.sql | 6 ++++++
4 files changed, 13 insertions(+), 1 deletion(-)
diff --git a/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_mysql.sql b/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_mysql.sql
index f5920cf6d1..3b929a2b70 100644
--- a/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_mysql.sql
+++ b/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_mysql.sql
@@ -694,7 +694,9 @@ CREATE TABLE `t_ds_relation_process_instance` (
`parent_process_instance_id` int(11) DEFAULT NULL COMMENT 'parent process instance id',
`parent_task_instance_id` int(11) DEFAULT NULL COMMENT 'parent process instance id',
`process_instance_id` int(11) DEFAULT NULL COMMENT 'child process instance id',
- PRIMARY KEY (`id`)
+ PRIMARY KEY (`id`),
+ KEY `idx_parent_process_task` (`parent_process_instance_id`,`parent_task_instance_id`) ,
+ KEY `idx_process_instance_id` (`process_instance_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- ----------------------------
diff --git a/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_postgresql.sql b/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_postgresql.sql
index 6bf671ecf8..e7045ba71e 100644
--- a/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_postgresql.sql
+++ b/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_postgresql.sql
@@ -615,6 +615,8 @@ CREATE TABLE t_ds_relation_process_instance (
process_instance_id int DEFAULT NULL ,
PRIMARY KEY (id)
) ;
+create index idx_relation_process_instance_parent_process_task on t_ds_relation_process_instance (parent_process_instance_id, parent_task_instance_id);
+create index idx_relation_process_instance_process_instance_id on t_ds_relation_process_instance (process_instance_id);
--
diff --git a/dolphinscheduler-dao/src/main/resources/sql/upgrade/3.0.0_schema/mysql/dolphinscheduler_ddl.sql b/dolphinscheduler-dao/src/main/resources/sql/upgrade/3.0.0_schema/mysql/dolphinscheduler_ddl.sql
index fc750ce6b0..8a8cc7f27f 100644
--- a/dolphinscheduler-dao/src/main/resources/sql/upgrade/3.0.0_schema/mysql/dolphinscheduler_ddl.sql
+++ b/dolphinscheduler-dao/src/main/resources/sql/upgrade/3.0.0_schema/mysql/dolphinscheduler_ddl.sql
@@ -67,6 +67,8 @@ ALTER TABLE `t_ds_process_task_relation` ADD KEY `idx_code` (`project_code`, `pr
ALTER TABLE `t_ds_process_task_relation` ADD KEY `idx_pre_task_code_version` (`pre_task_code`,`pre_task_version`);
ALTER TABLE `t_ds_process_task_relation` ADD KEY `idx_post_task_code_version` (`post_task_code`,`post_task_version`);
ALTER TABLE `t_ds_process_task_relation_log` ADD KEY `idx_process_code_version` (`process_definition_code`,`process_definition_version`) USING BTREE;
+ALTER TABLE `t_ds_relation_process_instance` ADD KEY `idx_parent_process_task`( `parent_process_instance_id`, `parent_task_instance_id` );
+ALTER TABLE `t_ds_relation_process_instance` ADD KEY `idx_process_instance_id`(`process_instance_id`);
ALTER TABLE `t_ds_task_definition_log` ADD INDEX `idx_project_code` (`project_code`) USING BTREE;
ALTER TABLE `t_ds_task_definition_log` ADD INDEX `idx_code_version` (`code`,`version`) USING BTREE;
diff --git a/dolphinscheduler-dao/src/main/resources/sql/upgrade/3.0.0_schema/postgresql/dolphinscheduler_ddl.sql b/dolphinscheduler-dao/src/main/resources/sql/upgrade/3.0.0_schema/postgresql/dolphinscheduler_ddl.sql
index c1a565ccad..4c1cb7e63f 100644
--- a/dolphinscheduler-dao/src/main/resources/sql/upgrade/3.0.0_schema/postgresql/dolphinscheduler_ddl.sql
+++ b/dolphinscheduler-dao/src/main/resources/sql/upgrade/3.0.0_schema/postgresql/dolphinscheduler_ddl.sql
@@ -89,6 +89,12 @@ EXECUTE 'CREATE INDEX IF NOT EXISTS process_task_relation_log_idx_project_code_p
EXECUTE 'DROP INDEX IF EXISTS "idx_task_definition_log_code_version"';
EXECUTE 'CREATE INDEX IF NOT EXISTS idx_task_definition_log_code_version ON ' || quote_ident(v_schema) ||'.t_ds_task_definition_log USING Btree("code","version")';
+EXECUTE 'DROP INDEX IF EXISTS "idx_relation_process_instance_parent_process_task"';
+EXECUTE 'CREATE INDEX IF NOT EXISTS idx_relation_process_instance_parent_process_task ON ' || quote_ident(v_schema) ||'.t_ds_relation_process_instance USING Btree("parent_process_instance_id","parent_task_instance_id")';
+
+EXECUTE 'DROP INDEX IF EXISTS "idx_relation_process_instance_process_instance_id"';
+EXECUTE 'CREATE INDEX IF NOT EXISTS idx_relation_process_instance_process_instance_id ON ' || quote_ident(v_schema) ||'.t_ds_relation_process_instance USING Btree("process_instance_id")';
+
EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_user ADD COLUMN IF NOT EXISTS "time_zone" varchar(32) DEFAULT NULL';
EXECUTE 'ALTER TABLE ' || quote_ident(v_schema) ||'.t_ds_alert ADD COLUMN IF NOT EXISTS "warning_type" int DEFAULT 2';