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 2021/12/27 10:06:02 UTC
[dolphinscheduler] branch dev updated: [Feature-7569]Optimize Dependent node loading times (#7626)
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 8b29213 [Feature-7569]Optimize Dependent node loading times (#7626)
8b29213 is described below
commit 8b292132c83374b6364abf4d40505593760c27c2
Author: xiangzihao <46...@qq.com>
AuthorDate: Mon Dec 27 18:05:56 2021 +0800
[Feature-7569]Optimize Dependent node loading times (#7626)
* feature 7569
* feature_7569
Co-authored-by: SbloodyS <sb...@qq.com>
---
.../main/resources/sql/dolphinscheduler_mysql.sql | 9 +++--
.../resources/sql/dolphinscheduler_postgresql.sql | 42 ++++++++++++++++++++--
.../2.1.0_schema/mysql/dolphinscheduler_ddl.sql | 6 +++-
.../2.1.0_schema/mysql/dolphinscheduler_dml.sql | 2 +-
.../postgresql/dolphinscheduler_ddl.sql | 34 ++++++++++++++++++
.../postgresql/dolphinscheduler_dml.sql | 2 +-
6 files changed, 86 insertions(+), 9 deletions(-)
diff --git a/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_mysql.sql b/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_mysql.sql
index f2ded6d..d67e013 100644
--- a/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_mysql.sql
+++ b/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_mysql.sql
@@ -515,7 +515,8 @@ CREATE TABLE `t_ds_task_definition_log` (
`operate_time` datetime DEFAULT NULL COMMENT 'operate time',
`create_time` datetime NOT NULL COMMENT 'create time',
`update_time` datetime NOT NULL COMMENT 'update time',
- PRIMARY KEY (`id`)
+ PRIMARY KEY (`id`),
+ KEY `idx_code_version` (`code`,`version`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- ----------------------------
@@ -536,7 +537,8 @@ CREATE TABLE `t_ds_process_task_relation` (
`condition_params` text COMMENT 'condition params(json)',
`create_time` datetime NOT NULL COMMENT 'create time',
`update_time` datetime NOT NULL COMMENT 'update time',
- PRIMARY KEY (`id`)
+ PRIMARY KEY (`id`),
+ KEY `idx_project_code_project_code` (`project_code`,`process_definition_code`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
-- ----------------------------
@@ -559,7 +561,8 @@ CREATE TABLE `t_ds_process_task_relation_log` (
`operate_time` datetime DEFAULT NULL COMMENT 'operate time',
`create_time` datetime NOT NULL COMMENT 'create time',
`update_time` datetime NOT NULL COMMENT 'update time',
- PRIMARY KEY (`id`)
+ PRIMARY KEY (`id`),
+ KEY `idx_project_code_project_code` (`project_code`,`process_definition_code`)
) 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 a4d2c2e..557c664 100644
--- a/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_postgresql.sql
+++ b/dolphinscheduler-dao/src/main/resources/sql/dolphinscheduler_postgresql.sql
@@ -216,6 +216,7 @@ CREATE TABLE t_ds_alert (
update_time timestamp DEFAULT NULL ,
PRIMARY KEY (id)
) ;
+
--
-- Table structure for table t_ds_alertgroup
--
@@ -307,9 +308,6 @@ CREATE TABLE t_ds_error_command (
process_definition_version int DEFAULT 0,
PRIMARY KEY (id)
);
---
--- Table structure for table t_ds_master_server
---
--
-- Table structure for table t_ds_process_definition
@@ -340,6 +338,10 @@ CREATE TABLE t_ds_process_definition (
create index process_definition_index on t_ds_process_definition (code,id);
+--
+-- Table structure for table t_ds_process_definition_log
+--
+
DROP TABLE IF EXISTS t_ds_process_definition_log;
CREATE TABLE t_ds_process_definition_log (
id int NOT NULL ,
@@ -364,6 +366,10 @@ CREATE TABLE t_ds_process_definition_log (
PRIMARY KEY (id)
) ;
+--
+-- Table structure for table t_ds_task_definition
+--
+
DROP TABLE IF EXISTS t_ds_task_definition;
CREATE TABLE t_ds_task_definition (
id int NOT NULL ,
@@ -395,6 +401,10 @@ CREATE TABLE t_ds_task_definition (
create index task_definition_index on t_ds_task_definition (project_code,id);
+--
+-- Table structure for table t_ds_task_definition_log
+--
+
DROP TABLE IF EXISTS t_ds_task_definition_log;
CREATE TABLE t_ds_task_definition_log (
id int NOT NULL ,
@@ -426,6 +436,12 @@ CREATE TABLE t_ds_task_definition_log (
PRIMARY KEY (id)
) ;
+create index idx_task_definition_log_code_version on t_ds_task_definition_log (code,version);
+
+--
+-- Table structure for table t_ds_process_task_relation
+--
+
DROP TABLE IF EXISTS t_ds_process_task_relation;
CREATE TABLE t_ds_process_task_relation (
id int NOT NULL ,
@@ -444,6 +460,12 @@ CREATE TABLE t_ds_process_task_relation (
PRIMARY KEY (id)
) ;
+create index process_task_relation_idx_project_code_process_definition_code on t_ds_process_task_relation (project_code,process_definition_code);
+
+--
+-- Table structure for table t_ds_process_task_relation_log
+--
+
DROP TABLE IF EXISTS t_ds_process_task_relation_log;
CREATE TABLE t_ds_process_task_relation_log (
id int NOT NULL ,
@@ -464,6 +486,8 @@ CREATE TABLE t_ds_process_task_relation_log (
PRIMARY KEY (id)
) ;
+create index process_task_relation_log_idx_project_code_process_definition_code on t_ds_process_task_relation_log (project_code,process_definition_code);
+
--
-- Table structure for table t_ds_process_instance
--
@@ -936,6 +960,7 @@ INSERT INTO t_ds_version(version) VALUES ('1.4.0');
--
-- Table structure for table t_ds_plugin_define
--
+
DROP TABLE IF EXISTS t_ds_plugin_define;
CREATE TABLE t_ds_plugin_define (
id serial NOT NULL,
@@ -951,6 +976,7 @@ CREATE TABLE t_ds_plugin_define (
--
-- Table structure for table t_ds_alert_plugin_instance
--
+
DROP TABLE IF EXISTS t_ds_alert_plugin_instance;
CREATE TABLE t_ds_alert_plugin_instance (
id serial NOT NULL,
@@ -965,6 +991,7 @@ CREATE TABLE t_ds_alert_plugin_instance (
--
-- Table structure for table t_ds_environment
--
+
DROP TABLE IF EXISTS t_ds_environment;
CREATE TABLE t_ds_environment (
id serial NOT NULL,
@@ -983,6 +1010,7 @@ CREATE TABLE t_ds_environment (
--
-- Table structure for table t_ds_environment_worker_group_relation
--
+
DROP TABLE IF EXISTS t_ds_environment_worker_group_relation;
CREATE TABLE t_ds_environment_worker_group_relation (
id serial NOT NULL,
@@ -995,6 +1023,10 @@ CREATE TABLE t_ds_environment_worker_group_relation (
CONSTRAINT environment_worker_group_unique UNIQUE (environment_code,worker_group)
);
+--
+-- Table structure for table t_ds_task_group_queue
+--
+
DROP TABLE IF EXISTS t_ds_task_group_queue;
CREATE TABLE t_ds_task_group_queue (
id serial NOT NULL,
@@ -1011,6 +1043,10 @@ CREATE TABLE t_ds_task_group_queue (
PRIMARY KEY (id)
);
+--
+-- Table structure for table t_ds_task_group
+--
+
DROP TABLE IF EXISTS t_ds_task_group;
CREATE TABLE t_ds_task_group (
id serial NOT NULL,
diff --git a/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.1.0_schema/mysql/dolphinscheduler_ddl.sql b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.1.0_schema/mysql/dolphinscheduler_ddl.sql
index 1331c19..d018144 100644
--- a/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.1.0_schema/mysql/dolphinscheduler_ddl.sql
+++ b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.1.0_schema/mysql/dolphinscheduler_ddl.sql
@@ -15,4 +15,8 @@
* limitations under the License.
*/
-ALTER TABLE `t_ds_task_instance` MODIFY COLUMN `task_params` longtext COMMENT 'job custom parameters' AFTER `app_link`;
\ No newline at end of file
+ALTER TABLE `t_ds_task_instance` MODIFY COLUMN `task_params` longtext COMMENT 'job custom parameters' AFTER `app_link`;
+ALTER TABLE `t_ds_process_task_relation` ADD INDEX `idx_project_code_process_definition_code` (`project_code`, `process_definition_code`) USING BTREE;
+ALTER TABLE `t_ds_process_task_relation_log` ADD INDEX `idx_project_code_process_definition_code` (`project_code`, `process_definition_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/2.1.0_schema/mysql/dolphinscheduler_dml.sql b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.1.0_schema/mysql/dolphinscheduler_dml.sql
index 38964cc..4a14f32 100644
--- a/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.1.0_schema/mysql/dolphinscheduler_dml.sql
+++ b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.1.0_schema/mysql/dolphinscheduler_dml.sql
@@ -13,4 +13,4 @@
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
-*/
\ No newline at end of file
+*/
diff --git a/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.1.0_schema/postgresql/dolphinscheduler_ddl.sql b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.1.0_schema/postgresql/dolphinscheduler_ddl.sql
index 4a14f32..992cc77 100644
--- a/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.1.0_schema/postgresql/dolphinscheduler_ddl.sql
+++ b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.1.0_schema/postgresql/dolphinscheduler_ddl.sql
@@ -14,3 +14,37 @@
* See the License for the specific language governing permissions and
* limitations under the License.
*/
+
+delimiter d//
+CREATE OR REPLACE FUNCTION public.dolphin_update_metadata(
+ )
+ RETURNS character varying
+ LANGUAGE 'plpgsql'
+ COST 100
+ VOLATILE PARALLEL UNSAFE
+AS $BODY$
+DECLARE
+v_schema varchar;
+BEGIN
+ ---get schema name
+ v_schema =current_schema();
+
+EXECUTE 'DROP INDEX IF EXISTS "process_task_relation_idx_project_code_process_definition_code"';
+EXECUTE 'CREATE INDEX IF NOT EXISTS process_task_relation_idx_project_code_process_definition_code ON ' || quote_ident(v_schema) ||'.t_ds_process_task_relation USING Btree("project_code","process_definition_code")';
+
+EXECUTE 'DROP INDEX IF EXISTS "process_task_relation_log_idx_project_code_process_definition_code"';
+EXECUTE 'CREATE INDEX IF NOT EXISTS process_task_relation_log_idx_project_code_process_definition_code ON ' || quote_ident(v_schema) ||'.t_ds_process_task_relation_log USING Btree("project_code","process_definition_code")';
+
+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")';
+
+return 'Success!';
+exception when others then
+ ---Raise EXCEPTION '(%)',SQLERRM;
+ return SQLERRM;
+END;
+$BODY$;
+
+select dolphin_update_metadata();
+
+d//
diff --git a/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.1.0_schema/postgresql/dolphinscheduler_dml.sql b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.1.0_schema/postgresql/dolphinscheduler_dml.sql
index 38964cc..4a14f32 100644
--- a/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.1.0_schema/postgresql/dolphinscheduler_dml.sql
+++ b/dolphinscheduler-dao/src/main/resources/sql/upgrade/2.1.0_schema/postgresql/dolphinscheduler_dml.sql
@@ -13,4 +13,4 @@
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
-*/
\ No newline at end of file
+*/