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 b2d117f9ad fix(admin): add indexes for namespace and relation queries
(#7238)
b2d117f9ad is described below
commit b2d117f9adabccb334d4e4fbf4dd5389df9224a3
Author: Liming Deng <[email protected]>
AuthorDate: Wed Sep 30 09:59:02 2026 +0800
fix(admin): add indexes for namespace and relation queries (#7238)
---
db/init/mysql/schema.sql | 34 ++++++++
db/upgrade/2.7.1-upgrade-2.7.2-mysql.sql | 34 ++++++++
.../src/main/resources/sql-script/h2/schema.sql | 34 ++++++++
.../shenyu/admin/mapper/AdminQueryIndexTest.java | 92 ++++++++++++++++++++++
4 files changed, 194 insertions(+)
diff --git a/db/init/mysql/schema.sql b/db/init/mysql/schema.sql
index 78f351acdb..c5f6a3b2a8 100644
--- a/db/init/mysql/schema.sql
+++ b/db/init/mysql/schema.sql
@@ -2824,3 +2824,37 @@ INSERT INTO `permission` (`id`, `object_id`,
`resource_id`, `date_created`, `dat
INSERT INTO `permission` (`id`, `object_id`, `resource_id`, `date_created`,
`date_updated`) VALUES ('1953049887387303973', '1346358560427216896',
'1953048313980116913', '2026-09-21 00:00:00', '2026-09-21 00:00:00');
INSERT INTO `permission` (`id`, `object_id`, `resource_id`, `date_created`,
`date_updated`) VALUES ('1953049887387303974', '1346358560427216896',
'1953048313980116914', '2026-09-21 00:00:00', '2026-09-21 00:00:00');
INSERT INTO `namespace_plugin_rel` (`id`,`namespace_id`,`plugin_id`, `config`,
`sort`, `enabled`, `date_created`, `date_updated`) VALUES
('1907261515594055681', '649330b6-c2d7-4edc-be8e-8a54df9eb385', '67', null,
197, 0, '2026-09-21 00:00:00', '2026-09-21 00:00:00');
+
+-- Secondary indexes for namespace filtering, relation lookups and ordered
listings.
+CREATE INDEX idx_selector_ns_plugin_name ON `selector` (namespace_id,
plugin_id, selector_name);
+CREATE INDEX idx_rule_ns_selector_name ON `rule` (namespace_id, selector_id,
rule_name);
+CREATE INDEX idx_metadata_path_ns ON `meta_data` (path, namespace_id);
+CREATE INDEX idx_metadata_ns_service ON `meta_data` (namespace_id,
service_name);
+CREATE INDEX idx_metadata_ns_app ON `meta_data` (namespace_id, app_name);
+CREATE INDEX idx_app_auth_ns_key ON `app_auth` (namespace_id, app_key);
+CREATE INDEX idx_auth_path_auth ON `auth_path` (auth_id);
+CREATE INDEX idx_auth_param_auth ON `auth_param` (auth_id);
+CREATE INDEX idx_permission_object_resource ON `permission` (object_id,
resource_id);
+CREATE INDEX idx_data_permission_user_type ON `data_permission` (user_id,
data_type, data_id);
+CREATE INDEX idx_tag_relation_api_tag ON `tag_relation` (api_id, tag_id);
+CREATE INDEX idx_tag_relation_tag_api ON `tag_relation` (tag_id, api_id);
+CREATE INDEX idx_api_path_method_rpc ON `api` (api_path, http_method,
rpc_type);
+CREATE INDEX idx_api_context ON `api` (context_path);
+CREATE INDEX idx_api_state_created ON `api` (state, date_created);
+CREATE INDEX idx_api_created ON `api` (date_created);
+CREATE INDEX idx_api_rule_api_rule ON `api_rule_relation` (api_id, rule_id);
+CREATE INDEX idx_ns_plugin_ns_plugin ON `namespace_plugin_rel` (namespace_id,
plugin_id, enabled);
+CREATE INDEX idx_discovery_rel_proxy ON `discovery_rel` (proxy_selector_id);
+CREATE INDEX idx_discovery_rel_selector ON `discovery_rel` (selector_id);
+CREATE INDEX idx_discovery_rel_handler ON `discovery_rel`
(discovery_handler_id);
+CREATE INDEX idx_discovery_handler_discovery ON `discovery_handler`
(discovery_id);
+CREATE INDEX idx_discovery_ns_plugin ON `discovery` (namespace_id,
plugin_name);
+CREATE INDEX idx_proxy_selector_ns ON `proxy_selector` (namespace_id);
+CREATE INDEX idx_operation_log_time ON `operation_record_log` (operation_time);
+CREATE INDEX idx_operation_log_operator_time ON `operation_record_log`
(operator, operation_time);
+CREATE INDEX idx_instance_ns_ip ON `instance_info` (namespace_id, instance_ip);
+CREATE INDEX idx_mock_record_api ON `mock_request_record` (api_id);
+CREATE INDEX idx_namespace_user_ns_user ON `namespace_user_rel` (namespace_id,
user_id);
+CREATE INDEX idx_namespace_user_user_ns ON `namespace_user_rel` (user_id,
namespace_id);
+CREATE INDEX idx_user_role_user_role ON `user_role` (user_id, role_id);
+CREATE INDEX idx_resource_parent ON `resource` (parent_id);
diff --git a/db/upgrade/2.7.1-upgrade-2.7.2-mysql.sql
b/db/upgrade/2.7.1-upgrade-2.7.2-mysql.sql
index 555cab19ad..9cf9e4ffdd 100644
--- a/db/upgrade/2.7.1-upgrade-2.7.2-mysql.sql
+++ b/db/upgrade/2.7.1-upgrade-2.7.2-mysql.sql
@@ -53,3 +53,37 @@ INSERT INTO `permission` (`id`, `object_id`, `resource_id`,
`date_created`, `dat
INSERT INTO `permission` (`id`, `object_id`, `resource_id`, `date_created`,
`date_updated`) VALUES ('1953049887387303973', '1346358560427216896',
'1953048313980116913', '2026-09-21 00:00:00', '2026-09-21 00:00:00');
INSERT INTO `permission` (`id`, `object_id`, `resource_id`, `date_created`,
`date_updated`) VALUES ('1953049887387303974', '1346358560427216896',
'1953048313980116914', '2026-09-21 00:00:00', '2026-09-21 00:00:00');
INSERT INTO `namespace_plugin_rel` (`id`,`namespace_id`,`plugin_id`, `config`,
`sort`, `enabled`, `date_created`, `date_updated`) VALUES
('1907261515594055681', '649330b6-c2d7-4edc-be8e-8a54df9eb385', '67', null,
197, 0, '2026-09-21 00:00:00', '2026-09-21 00:00:00');
+
+-- Secondary indexes for namespace filtering, relation lookups and ordered
listings.
+CREATE INDEX idx_selector_ns_plugin_name ON `selector` (namespace_id,
plugin_id, selector_name);
+CREATE INDEX idx_rule_ns_selector_name ON `rule` (namespace_id, selector_id,
rule_name);
+CREATE INDEX idx_metadata_path_ns ON `meta_data` (path, namespace_id);
+CREATE INDEX idx_metadata_ns_service ON `meta_data` (namespace_id,
service_name);
+CREATE INDEX idx_metadata_ns_app ON `meta_data` (namespace_id, app_name);
+CREATE INDEX idx_app_auth_ns_key ON `app_auth` (namespace_id, app_key);
+CREATE INDEX idx_auth_path_auth ON `auth_path` (auth_id);
+CREATE INDEX idx_auth_param_auth ON `auth_param` (auth_id);
+CREATE INDEX idx_permission_object_resource ON `permission` (object_id,
resource_id);
+CREATE INDEX idx_data_permission_user_type ON `data_permission` (user_id,
data_type, data_id);
+CREATE INDEX idx_tag_relation_api_tag ON `tag_relation` (api_id, tag_id);
+CREATE INDEX idx_tag_relation_tag_api ON `tag_relation` (tag_id, api_id);
+CREATE INDEX idx_api_path_method_rpc ON `api` (api_path, http_method,
rpc_type);
+CREATE INDEX idx_api_context ON `api` (context_path);
+CREATE INDEX idx_api_state_created ON `api` (state, date_created);
+CREATE INDEX idx_api_created ON `api` (date_created);
+CREATE INDEX idx_api_rule_api_rule ON `api_rule_relation` (api_id, rule_id);
+CREATE INDEX idx_ns_plugin_ns_plugin ON `namespace_plugin_rel` (namespace_id,
plugin_id, enabled);
+CREATE INDEX idx_discovery_rel_proxy ON `discovery_rel` (proxy_selector_id);
+CREATE INDEX idx_discovery_rel_selector ON `discovery_rel` (selector_id);
+CREATE INDEX idx_discovery_rel_handler ON `discovery_rel`
(discovery_handler_id);
+CREATE INDEX idx_discovery_handler_discovery ON `discovery_handler`
(discovery_id);
+CREATE INDEX idx_discovery_ns_plugin ON `discovery` (namespace_id,
plugin_name);
+CREATE INDEX idx_proxy_selector_ns ON `proxy_selector` (namespace_id);
+CREATE INDEX idx_operation_log_time ON `operation_record_log` (operation_time);
+CREATE INDEX idx_operation_log_operator_time ON `operation_record_log`
(operator, operation_time);
+CREATE INDEX idx_instance_ns_ip ON `instance_info` (namespace_id, instance_ip);
+CREATE INDEX idx_mock_record_api ON `mock_request_record` (api_id);
+CREATE INDEX idx_namespace_user_ns_user ON `namespace_user_rel` (namespace_id,
user_id);
+CREATE INDEX idx_namespace_user_user_ns ON `namespace_user_rel` (user_id,
namespace_id);
+CREATE INDEX idx_user_role_user_role ON `user_role` (user_id, role_id);
+CREATE INDEX idx_resource_parent ON `resource` (parent_id);
diff --git a/shenyu-admin/src/main/resources/sql-script/h2/schema.sql
b/shenyu-admin/src/main/resources/sql-script/h2/schema.sql
index ad52a91b8a..25c90db458 100644
--- a/shenyu-admin/src/main/resources/sql-script/h2/schema.sql
+++ b/shenyu-admin/src/main/resources/sql-script/h2/schema.sql
@@ -1691,3 +1691,37 @@ INSERT IGNORE INTO `permission` (`id`, `object_id`,
`resource_id`, `date_created
INSERT IGNORE INTO `permission` (`id`, `object_id`, `resource_id`,
`date_created`, `date_updated`) VALUES ('1953049887387303973',
'1346358560427216896', '1953048313980116913', '2026-09-21 00:00:00',
'2026-09-21 00:00:00');
INSERT IGNORE INTO `permission` (`id`, `object_id`, `resource_id`,
`date_created`, `date_updated`) VALUES ('1953049887387303974',
'1346358560427216896', '1953048313980116914', '2026-09-21 00:00:00',
'2026-09-21 00:00:00');
INSERT IGNORE INTO `namespace_plugin_rel` (`id`,`namespace_id`,`plugin_id`,
`config`, `sort`, `enabled`, `date_created`, `date_updated`) VALUES
('1907261515594055681', '649330b6-c2d7-4edc-be8e-8a54df9eb385', '67', null,
197, 0, '2026-09-21 00:00:00', '2026-09-21 00:00:00');
+
+-- Secondary indexes for namespace filtering, relation lookups and ordered
listings.
+CREATE INDEX idx_selector_ns_plugin_name ON `selector` (namespace_id,
plugin_id, selector_name);
+CREATE INDEX idx_rule_ns_selector_name ON `rule` (namespace_id, selector_id,
rule_name);
+CREATE INDEX idx_metadata_path_ns ON `meta_data` (path, namespace_id);
+CREATE INDEX idx_metadata_ns_service ON `meta_data` (namespace_id,
service_name);
+CREATE INDEX idx_metadata_ns_app ON `meta_data` (namespace_id, app_name);
+CREATE INDEX idx_app_auth_ns_key ON `app_auth` (namespace_id, app_key);
+CREATE INDEX idx_auth_path_auth ON `auth_path` (auth_id);
+CREATE INDEX idx_auth_param_auth ON `auth_param` (auth_id);
+CREATE INDEX idx_permission_object_resource ON `permission` (object_id,
resource_id);
+CREATE INDEX idx_data_permission_user_type ON `data_permission` (user_id,
data_type, data_id);
+CREATE INDEX idx_tag_relation_api_tag ON `tag_relation` (api_id, tag_id);
+CREATE INDEX idx_tag_relation_tag_api ON `tag_relation` (tag_id, api_id);
+CREATE INDEX idx_api_path_method_rpc ON `api` (api_path, http_method,
rpc_type);
+CREATE INDEX idx_api_context ON `api` (context_path);
+CREATE INDEX idx_api_state_created ON `api` (state, date_created);
+CREATE INDEX idx_api_created ON `api` (date_created);
+CREATE INDEX idx_api_rule_api_rule ON `api_rule_relation` (api_id, rule_id);
+CREATE INDEX idx_ns_plugin_ns_plugin ON `namespace_plugin_rel` (namespace_id,
plugin_id, enabled);
+CREATE INDEX idx_discovery_rel_proxy ON `discovery_rel` (proxy_selector_id);
+CREATE INDEX idx_discovery_rel_selector ON `discovery_rel` (selector_id);
+CREATE INDEX idx_discovery_rel_handler ON `discovery_rel`
(discovery_handler_id);
+CREATE INDEX idx_discovery_handler_discovery ON `discovery_handler`
(discovery_id);
+CREATE INDEX idx_discovery_ns_plugin ON `discovery` (namespace_id,
plugin_name);
+CREATE INDEX idx_proxy_selector_ns ON `proxy_selector` (namespace_id);
+CREATE INDEX idx_operation_log_time ON `operation_record_log` (operation_time);
+CREATE INDEX idx_operation_log_operator_time ON `operation_record_log`
(operator, operation_time);
+CREATE INDEX idx_instance_ns_ip ON `instance_info` (namespace_id, instance_ip);
+CREATE INDEX idx_mock_record_api ON `mock_request_record` (api_id);
+CREATE INDEX idx_namespace_user_ns_user ON `namespace_user_rel` (namespace_id,
user_id);
+CREATE INDEX idx_namespace_user_user_ns ON `namespace_user_rel` (user_id,
namespace_id);
+CREATE INDEX idx_user_role_user_role ON `user_role` (user_id, role_id);
+CREATE INDEX idx_resource_parent ON `resource` (parent_id);
diff --git
a/shenyu-admin/src/test/java/org/apache/shenyu/admin/mapper/AdminQueryIndexTest.java
b/shenyu-admin/src/test/java/org/apache/shenyu/admin/mapper/AdminQueryIndexTest.java
new file mode 100644
index 0000000000..c362d9c6f6
--- /dev/null
+++
b/shenyu-admin/src/test/java/org/apache/shenyu/admin/mapper/AdminQueryIndexTest.java
@@ -0,0 +1,92 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * 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.
+ */
+
+package org.apache.shenyu.admin.mapper;
+
+import jakarta.annotation.Resource;
+import org.apache.shenyu.admin.AbstractSpringIntegrationTest;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.Arguments;
+import org.junit.jupiter.params.provider.MethodSource;
+
+import javax.sql.DataSource;
+import java.sql.Connection;
+import java.sql.ResultSet;
+import java.util.ArrayList;
+import java.util.List;
+import java.util.Locale;
+import java.util.stream.Stream;
+
+import static org.junit.jupiter.api.Assertions.assertEquals;
+
+class AdminQueryIndexTest extends AbstractSpringIntegrationTest {
+
+ @Resource
+ private DataSource dataSource;
+
+ @ParameterizedTest
+ @MethodSource("indexes")
+ void testLookupIndexColumnOrder(final String table, final String index,
final String columns) throws Exception {
+ List<String> actual = new ArrayList<>();
+ try (Connection connection = dataSource.getConnection();
+ ResultSet indexes =
connection.getMetaData().getIndexInfo(null, null,
+ connection.getMetaData().storesUpperCaseIdentifiers()
? table.toUpperCase(Locale.ROOT) : table, false, false)) {
+ while (indexes.next()) {
+ if (index.equalsIgnoreCase(indexes.getString("INDEX_NAME"))) {
+
actual.add(indexes.getString("COLUMN_NAME").toLowerCase(Locale.ROOT));
+ }
+ }
+ }
+ assertEquals(columns, String.join(",", actual));
+ }
+
+ private static Stream<Arguments> indexes() {
+ return Stream.of(
+ Arguments.of("selector", "idx_selector_ns_plugin_name",
"namespace_id,plugin_id,selector_name"),
+ Arguments.of("rule", "idx_rule_ns_selector_name",
"namespace_id,selector_id,rule_name"),
+ Arguments.of("meta_data", "idx_metadata_path_ns",
"path,namespace_id"),
+ Arguments.of("meta_data", "idx_metadata_ns_service",
"namespace_id,service_name"),
+ Arguments.of("meta_data", "idx_metadata_ns_app",
"namespace_id,app_name"),
+ Arguments.of("app_auth", "idx_app_auth_ns_key",
"namespace_id,app_key"),
+ Arguments.of("auth_path", "idx_auth_path_auth", "auth_id"),
+ Arguments.of("auth_param", "idx_auth_param_auth", "auth_id"),
+ Arguments.of("permission", "idx_permission_object_resource",
"object_id,resource_id"),
+ Arguments.of("data_permission",
"idx_data_permission_user_type", "user_id,data_type,data_id"),
+ Arguments.of("tag_relation", "idx_tag_relation_api_tag",
"api_id,tag_id"),
+ Arguments.of("tag_relation", "idx_tag_relation_tag_api",
"tag_id,api_id"),
+ Arguments.of("api", "idx_api_path_method_rpc",
"api_path,http_method,rpc_type"),
+ Arguments.of("api", "idx_api_context", "context_path"),
+ Arguments.of("api", "idx_api_state_created",
"state,date_created"),
+ Arguments.of("api", "idx_api_created", "date_created"),
+ Arguments.of("api_rule_relation", "idx_api_rule_api_rule",
"api_id,rule_id"),
+ Arguments.of("namespace_plugin_rel",
"idx_ns_plugin_ns_plugin", "namespace_id,plugin_id,enabled"),
+ Arguments.of("discovery_rel", "idx_discovery_rel_proxy",
"proxy_selector_id"),
+ Arguments.of("discovery_rel", "idx_discovery_rel_selector",
"selector_id"),
+ Arguments.of("discovery_rel", "idx_discovery_rel_handler",
"discovery_handler_id"),
+ Arguments.of("discovery_handler",
"idx_discovery_handler_discovery", "discovery_id"),
+ Arguments.of("discovery", "idx_discovery_ns_plugin",
"namespace_id,plugin_name"),
+ Arguments.of("proxy_selector", "idx_proxy_selector_ns",
"namespace_id"),
+ Arguments.of("operation_record_log", "idx_operation_log_time",
"operation_time"),
+ Arguments.of("operation_record_log",
"idx_operation_log_operator_time", "operator,operation_time"),
+ Arguments.of("instance_info", "idx_instance_ns_ip",
"namespace_id,instance_ip"),
+ Arguments.of("mock_request_record", "idx_mock_record_api",
"api_id"),
+ Arguments.of("namespace_user_rel",
"idx_namespace_user_ns_user", "namespace_id,user_id"),
+ Arguments.of("namespace_user_rel",
"idx_namespace_user_user_ns", "user_id,namespace_id"),
+ Arguments.of("user_role", "idx_user_role_user_role",
"user_id,role_id"),
+ Arguments.of("resource", "idx_resource_parent", "parent_id"));
+ }
+}
