This is an automated email from the ASF dual-hosted git repository.
Aias00 pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/shenyu.git
The following commit(s) were added to refs/heads/master by this push:
new cd00ac4cbb perf(admin): index operation log queries (#6675) (#7404)
cd00ac4cbb is described below
commit cd00ac4cbb97936143187bbc1c450524c408a629
Author: Jerry聊AI <[email protected]>
AuthorDate: Sat Oct 17 20:48:26 2026 +0800
perf(admin): index operation log queries (#6675) (#7404)
* perf(admin): index operation log queries (#6675)
* fix(admin): keep operation log index Oracle compatible (#6675)
* docs(db): explain Oracle operation index name limit
---
db/init/oracle/schema.sql | 4 ++++
db/init/pg/create-table.sql | 2 ++
db/upgrade/2.7.1-upgrade-2.7.2-ob.sql | 2 ++
db/upgrade/2.7.1-upgrade-2.7.2-og.sql | 2 ++
db/upgrade/2.7.1-upgrade-2.7.2-oracle.sql | 3 +++
db/upgrade/2.7.1-upgrade-2.7.2-pg.sql | 2 ++
6 files changed, 15 insertions(+)
diff --git a/db/init/oracle/schema.sql b/db/init/oracle/schema.sql
index 3b0a1f08ea..bb6338ea2c 100644
--- a/db/init/oracle/schema.sql
+++ b/db/init/oracle/schema.sql
@@ -419,6 +419,10 @@ comment on column OPERATION_RECORD_LOG.operation_time
comment on column OPERATION_RECORD_LOG.operation_type
is 'operation type：create/update/delete/register...';
+create index idx_operation_log_time on operation_record_log (operation_time);
+-- Keep the index name within Oracle's 30-character identifier limit.
+create index idx_operation_log_oper_time on operation_record_log (operator,
operation_time);
+
create sequence operation_record_log_seq
increment by 1
START WITH 1
diff --git a/db/init/pg/create-table.sql b/db/init/pg/create-table.sql
index cb0c92bdcb..d772c42102 100644
--- a/db/init/pg/create-table.sql
+++ b/db/init/pg/create-table.sql
@@ -2063,6 +2063,8 @@ CREATE INDEX "idx_meta_data_namespace_path" ON
"public"."meta_data" USING btree
-- Primary Key structure for table operation_record_log
-- ----------------------------
ALTER TABLE "public"."operation_record_log" ADD CONSTRAINT
"operation_record_log_pkey" PRIMARY KEY ("id");
+CREATE INDEX "idx_operation_log_time" ON "public"."operation_record_log" USING
btree ("operation_time");
+CREATE INDEX "idx_operation_log_operator_time" ON
"public"."operation_record_log" USING btree ("operator","operation_time");
-- ----------------------------
-- Primary Key structure for table permission
diff --git a/db/upgrade/2.7.1-upgrade-2.7.2-ob.sql
b/db/upgrade/2.7.1-upgrade-2.7.2-ob.sql
index 84a631d0b4..3f576e7f44 100644
--- a/db/upgrade/2.7.1-upgrade-2.7.2-ob.sql
+++ b/db/upgrade/2.7.1-upgrade-2.7.2-ob.sql
@@ -88,6 +88,8 @@ INSERT INTO `permission` VALUES ('1960000000000001029',
'1346358560427216896', '
INSERT INTO `namespace_plugin_rel` (`id`,`namespace_id`,`plugin_id`, `config`,
`sort`, `enabled`, `date_created`, `date_updated`) VALUES
('1960000000000001030','649330b6-c2d7-4edc-be8e-8a54df9eb385','68', NULL, 198,
0, '2026-09-19 00:00:00.000', '2026-09-19 00:00:00.000');
-- add index to speed up the meta data path uniqueness check
ALTER TABLE `meta_data` ADD INDEX `idx_meta_data_namespace_path`
(`namespace_id`, `path`) USING BTREE;
+ALTER TABLE `operation_record_log` ADD INDEX `idx_operation_log_time`
(`operation_time`) USING BTREE;
+ALTER TABLE `operation_record_log` ADD INDEX `idx_operation_log_operator_time`
(`operator`, `operation_time`) USING BTREE;
-- Retain the smallest existing id for each grant before enforcing uniqueness.
DELETE duplicate_permission FROM data_permission duplicate_permission
diff --git a/db/upgrade/2.7.1-upgrade-2.7.2-og.sql
b/db/upgrade/2.7.1-upgrade-2.7.2-og.sql
index b4be51b9a8..b2afcdc7b6 100644
--- a/db/upgrade/2.7.1-upgrade-2.7.2-og.sql
+++ b/db/upgrade/2.7.1-upgrade-2.7.2-og.sql
@@ -88,6 +88,8 @@ INSERT INTO "public"."permission" VALUES
('1960000000000001029', '13463585604272
INSERT INTO "public"."namespace_plugin_rel" VALUES
('1960000000000001030','649330b6-c2d7-4edc-be8e-8a54df9eb385','68', NULL, 198,
0, '2026-09-19 00:00:00.000', '2026-09-19 00:00:00.000');
-- add index to speed up the meta data path uniqueness check
CREATE INDEX "idx_meta_data_namespace_path" ON "public"."meta_data" USING
btree ("namespace_id","path");
+CREATE INDEX "idx_operation_log_time" ON "public"."operation_record_log" USING
btree ("operation_time");
+CREATE INDEX "idx_operation_log_operator_time" ON
"public"."operation_record_log" USING btree ("operator","operation_time");
-- Retain the smallest existing id for each grant before enforcing uniqueness.
DELETE FROM data_permission duplicate_permission
diff --git a/db/upgrade/2.7.1-upgrade-2.7.2-oracle.sql
b/db/upgrade/2.7.1-upgrade-2.7.2-oracle.sql
index ed839909cd..136dc1b497 100644
--- a/db/upgrade/2.7.1-upgrade-2.7.2-oracle.sql
+++ b/db/upgrade/2.7.1-upgrade-2.7.2-oracle.sql
@@ -102,6 +102,9 @@ INSERT /*+ IGNORE_ROW_ON_DUPKEY_INDEX
(namespace_plugin_rel(id)) */ INTO namespa
VALUES ('1960000000000001030','649330b6-c2d7-4edc-be8e-8a54df9eb385','68',
NULL, 198, 0);
-- add index to speed up the meta data path uniqueness check
create index idx_meta_data_namespace_path on meta_data (namespace_id, path);
+create index idx_operation_log_time on operation_record_log (operation_time);
+-- Keep the index name within Oracle's 30-character identifier limit.
+create index idx_operation_log_oper_time on operation_record_log (operator,
operation_time);
-- Retain the smallest existing id for each grant before enforcing uniqueness.
DELETE FROM data_permission duplicate_permission
diff --git a/db/upgrade/2.7.1-upgrade-2.7.2-pg.sql
b/db/upgrade/2.7.1-upgrade-2.7.2-pg.sql
index ec034e6d50..750b908564 100644
--- a/db/upgrade/2.7.1-upgrade-2.7.2-pg.sql
+++ b/db/upgrade/2.7.1-upgrade-2.7.2-pg.sql
@@ -88,6 +88,8 @@ INSERT INTO "public"."permission" VALUES
('1960000000000001029', '13463585604272
INSERT INTO "public"."namespace_plugin_rel" VALUES
('1960000000000001030','649330b6-c2d7-4edc-be8e-8a54df9eb385','68', NULL, 198,
0, '2026-09-19 00:00:00.000', '2026-09-19 00:00:00.000');
-- add index to speed up the meta data path uniqueness check
CREATE INDEX "idx_meta_data_namespace_path" ON "public"."meta_data" USING
btree ("namespace_id","path");
+CREATE INDEX "idx_operation_log_time" ON "public"."operation_record_log" USING
btree ("operation_time");
+CREATE INDEX "idx_operation_log_operator_time" ON
"public"."operation_record_log" USING btree ("operator","operation_time");
-- Retain the smallest existing id for each grant before enforcing uniqueness.
DELETE FROM data_permission duplicate_permission
