Skip to content

Commit ee5344c

Browse files
youngzilyoungzil
committed
refactor(sql): Optimize database indexes and adjust field lengths
1 parent 675f2e7 commit ee5344c

7 files changed

Lines changed: 79 additions & 36 deletions

File tree

‎scripts/sql/profiles/h2-default/delta/v230-v240/apolloconfigdb-v230-v240.sql‎

Lines changed: 13 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -34,17 +34,27 @@ CREATE ALIAS IF NOT EXISTS UNIX_TIMESTAMP FOR "com.ctrip.framework.apollo.common
3434
ALTER TABLE `AccessKey` ADD COLUMN `Mode` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '密钥模式，0: filter，1: observer' AFTER `Secret`;
3535

3636
ALTER TABLE `Commit` MODIFY COLUMN `ClusterName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Cluster Name';
37-
3837
ALTER TABLE `Commit` MODIFY COLUMN `NamespaceName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Namespace Name';
3938

4039
ALTER TABLE `Namespace` MODIFY COLUMN `ClusterName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Cluster Name';
41-
4240
ALTER TABLE `Namespace` MODIFY COLUMN `NamespaceName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Namespace Name';
4341

4442
ALTER TABLE `Release` MODIFY COLUMN `ClusterName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Cluster Name';
45-
4643
ALTER TABLE `Release` MODIFY COLUMN `NamespaceName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Namespace Name';
4744

45+
ALTER TABLE `Commit` DROP INDEX `Commit_ClusterName`;
46+
ALTER TABLE `Commit` DROP INDEX `Commit_NamespaceName`;
47+
ALTER TABLE `Commit` ADD INDEX `Commit_ClusterName` (`ClusterName`);
48+
ALTER TABLE `Commit` ADD INDEX `Commit_NamespaceName` (`NamespaceName`);
49+
50+
ALTER TABLE `Namespace` DROP INDEX `Namespace_UK_AppId_ClusterName_NamespaceName_DeletedAt`;
51+
ALTER TABLE `Namespace` DROP INDEX `Namespace_IX_NamespaceName`;
52+
ALTER TABLE `Namespace` ADD UNIQUE INDEX `Namespace_UK_AppId_ClusterName_NamespaceName_DeletedAt` (`AppId`,`ClusterName`,`NamespaceName`,`DeletedAt`);
53+
ALTER TABLE `Namespace` ADD INDEX `Namespace_IX_NamespaceName` (`NamespaceName`);
54+
55+
ALTER TABLE `Namespace` DROP INDEX `Namespace_AppId_ClusterName_GroupName`;
56+
ALTER TABLE `Namespace` ADD INDEX `Namespace_AppId_ClusterName_GroupName` (`AppId`,`ClusterName`,`NamespaceName`);
57+
4858
--
4959
-- ===============================================================================
5060
-- == ==

‎scripts/sql/profiles/mysql-database-not-specified/apolloconfigdb.sql‎

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -154,8 +154,8 @@ CREATE TABLE `Commit` (
154154
PRIMARY KEY (`Id`),
155155
KEY `DataChange_LastTime` (`DataChange_LastTime`),
156156
KEY `AppId` (`AppId`),
157-
KEY `ClusterName` (`ClusterName`(191)),
158-
KEY `NamespaceName` (`NamespaceName`(191))
157+
KEY `ClusterName` (`ClusterName`),
158+
KEY `NamespaceName` (`NamespaceName`)
159159
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='commit 历史表';
160160

161161
-- Dump of table grayreleaserule
@@ -272,9 +272,9 @@ CREATE TABLE `Namespace` (
272272
`DataChange_LastModifiedBy` varchar(64) DEFAULT '' COMMENT '最后修改人邮箱前缀',
273273
`DataChange_LastTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
274274
PRIMARY KEY (`Id`),
275-
UNIQUE KEY `UK_AppId_ClusterName_NamespaceName_DeletedAt` (`AppId`,`ClusterName`(191),`NamespaceName`(191),`DeletedAt`),
275+
UNIQUE KEY `UK_AppId_ClusterName_NamespaceName_DeletedAt` (`AppId`,`ClusterName`,`NamespaceName`,`DeletedAt`),
276276
KEY `DataChange_LastTime` (`DataChange_LastTime`),
277-
KEY `IX_NamespaceName` (`NamespaceName`(191))
277+
KEY `IX_NamespaceName` (`NamespaceName`)
278278
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='命名空间';
279279

280280

@@ -323,7 +323,7 @@ CREATE TABLE `Release` (
323323
`DataChange_LastTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
324324
PRIMARY KEY (`Id`),
325325
UNIQUE KEY `UK_ReleaseKey_DeletedAt` (`ReleaseKey`,`DeletedAt`),
326-
KEY `AppId_ClusterName_GroupName` (`AppId`,`ClusterName`(191),`NamespaceName`(191)),
326+
KEY `AppId_ClusterName_GroupName` (`AppId`,`ClusterName`,`NamespaceName`),
327327
KEY `DataChange_LastTime` (`DataChange_LastTime`)
328328
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='发布';
329329

‎scripts/sql/profiles/mysql-database-not-specified/delta/v230-v240/apolloconfigdb-v230-v240.sql‎

Lines changed: 17 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -30,18 +30,29 @@ ALTER TABLE `AccessKey`
3030
ADD COLUMN `Mode` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '密钥模式，0: filter，1: observer' AFTER `Secret`;
3131

3232
ALTER TABLE `Commit`
33-
MODIFY COLUMN `ClusterName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Cluster Name';
34-
ALTER TABLE `Commit`
33+
MODIFY COLUMN `ClusterName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Cluster Name',
3534
MODIFY COLUMN `NamespaceName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Namespace Name';
3635
ALTER TABLE `Namespace`
37-
MODIFY COLUMN `ClusterName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Cluster Name';
38-
ALTER TABLE `Namespace`
36+
MODIFY COLUMN `ClusterName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Cluster Name',
3937
MODIFY COLUMN `NamespaceName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Namespace Name';
4038
ALTER TABLE `Release`
41-
MODIFY COLUMN `ClusterName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Cluster Name';
42-
ALTER TABLE `Release`
39+
MODIFY COLUMN `ClusterName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Cluster Name',
4340
MODIFY COLUMN `NamespaceName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Namespace Name';
4441

42+
ALTER TABLE `Commit`
43+
DROP INDEX `ClusterName`,
44+
DROP INDEX `NamespaceName`,
45+
ADD INDEX `ClusterName` (`ClusterName`),
46+
ADD INDEX `NamespaceName` (`NamespaceName`);
47+
ALTER TABLE `Namespace`
48+
DROP INDEX `UK_AppId_ClusterName_NamespaceName_DeletedAt`,
49+
DROP INDEX `IX_NamespaceName`,
50+
ADD UNIQUE INDEX `UK_AppId_ClusterName_NamespaceName_DeletedAt` (`AppId`,`ClusterName`,`NamespaceName`,`DeletedAt`),
51+
ADD INDEX `IX_NamespaceName` (`NamespaceName`);
52+
ALTER TABLE `Namespace`
53+
DROP INDEX `AppId_ClusterName_GroupName`,
54+
ADD INDEX `AppId_ClusterName_GroupName` (`AppId`,`ClusterName`,`NamespaceName`);
55+
4556
--
4657
-- ===============================================================================
4758
-- == ==

‎scripts/sql/profiles/mysql-default/apolloconfigdb.sql‎

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -159,8 +159,8 @@ CREATE TABLE `Commit` (
159159
PRIMARY KEY (`Id`),
160160
KEY `DataChange_LastTime` (`DataChange_LastTime`),
161161
KEY `AppId` (`AppId`),
162-
KEY `ClusterName` (`ClusterName`(191)),
163-
KEY `NamespaceName` (`NamespaceName`(191))
162+
KEY `ClusterName` (`ClusterName`),
163+
KEY `NamespaceName` (`NamespaceName`)
164164
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='commit 历史表';
165165

166166
-- Dump of table grayreleaserule
@@ -277,9 +277,9 @@ CREATE TABLE `Namespace` (
277277
`DataChange_LastModifiedBy` varchar(64) DEFAULT '' COMMENT '最后修改人邮箱前缀',
278278
`DataChange_LastTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
279279
PRIMARY KEY (`Id`),
280-
UNIQUE KEY `UK_AppId_ClusterName_NamespaceName_DeletedAt` (`AppId`,`ClusterName`(191),`NamespaceName`(191),`DeletedAt`),
280+
UNIQUE KEY `UK_AppId_ClusterName_NamespaceName_DeletedAt` (`AppId`,`ClusterName`,`NamespaceName`,`DeletedAt`),
281281
KEY `DataChange_LastTime` (`DataChange_LastTime`),
282-
KEY `IX_NamespaceName` (`NamespaceName`(191))
282+
KEY `IX_NamespaceName` (`NamespaceName`)
283283
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='命名空间';
284284

285285

@@ -328,7 +328,7 @@ CREATE TABLE `Release` (
328328
`DataChange_LastTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
329329
PRIMARY KEY (`Id`),
330330
UNIQUE KEY `UK_ReleaseKey_DeletedAt` (`ReleaseKey`,`DeletedAt`),
331-
KEY `AppId_ClusterName_GroupName` (`AppId`,`ClusterName`(191),`NamespaceName`(191)),
331+
KEY `AppId_ClusterName_GroupName` (`AppId`,`ClusterName`,`NamespaceName`),
332332
KEY `DataChange_LastTime` (`DataChange_LastTime`)
333333
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='发布';
334334

‎scripts/sql/profiles/mysql-default/delta/v230-v240/apolloconfigdb-v230-v240.sql‎

Lines changed: 17 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -32,18 +32,29 @@ ALTER TABLE `AccessKey`
3232
ADD COLUMN `Mode` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '密钥模式，0: filter，1: observer' AFTER `Secret`;
3333

3434
ALTER TABLE `Commit`
35-
MODIFY COLUMN `ClusterName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Cluster Name';
36-
ALTER TABLE `Commit`
35+
MODIFY COLUMN `ClusterName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Cluster Name',
3736
MODIFY COLUMN `NamespaceName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Namespace Name';
3837
ALTER TABLE `Namespace`
39-
MODIFY COLUMN `ClusterName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Cluster Name';
40-
ALTER TABLE `Namespace`
38+
MODIFY COLUMN `ClusterName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Cluster Name',
4139
MODIFY COLUMN `NamespaceName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Namespace Name';
4240
ALTER TABLE `Release`
43-
MODIFY COLUMN `ClusterName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Cluster Name';
44-
ALTER TABLE `Release`
41+
MODIFY COLUMN `ClusterName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Cluster Name',
4542
MODIFY COLUMN `NamespaceName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Namespace Name';
4643

44+
ALTER TABLE `Commit`
45+
DROP INDEX `ClusterName`,
46+
DROP INDEX `NamespaceName`,
47+
ADD INDEX `ClusterName` (`ClusterName`),
48+
ADD INDEX `NamespaceName` (`NamespaceName`);
49+
ALTER TABLE `Namespace`
50+
DROP INDEX `UK_AppId_ClusterName_NamespaceName_DeletedAt`,
51+
DROP INDEX `IX_NamespaceName`,
52+
ADD UNIQUE INDEX `UK_AppId_ClusterName_NamespaceName_DeletedAt` (`AppId`,`ClusterName`,`NamespaceName`,`DeletedAt`),
53+
ADD INDEX `IX_NamespaceName` (`NamespaceName`);
54+
ALTER TABLE `Namespace`
55+
DROP INDEX `AppId_ClusterName_GroupName`,
56+
ADD INDEX `AppId_ClusterName_GroupName` (`AppId`,`ClusterName`,`NamespaceName`);
57+
4758
--
4859
-- ===============================================================================
4960
-- == ==

‎scripts/sql/src/apolloconfigdb.sql‎

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -147,8 +147,8 @@ CREATE TABLE `Commit` (
147147
PRIMARY KEY (`Id`),
148148
KEY `DataChange_LastTime` (`DataChange_LastTime`),
149149
KEY `AppId` (`AppId`),
150-
KEY `ClusterName` (`ClusterName`(191)),
151-
KEY `NamespaceName` (`NamespaceName`(191))
150+
KEY `ClusterName` (`ClusterName`),
151+
KEY `NamespaceName` (`NamespaceName`)
152152
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='commit 历史表';
153153

154154
-- Dump of table grayreleaserule
@@ -265,9 +265,9 @@ CREATE TABLE `Namespace` (
265265
`DataChange_LastModifiedBy` varchar(64) DEFAULT '' COMMENT '最后修改人邮箱前缀',
266266
`DataChange_LastTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
267267
PRIMARY KEY (`Id`),
268-
UNIQUE KEY `UK_AppId_ClusterName_NamespaceName_DeletedAt` (`AppId`,`ClusterName`(191),`NamespaceName`(191),`DeletedAt`),
268+
UNIQUE KEY `UK_AppId_ClusterName_NamespaceName_DeletedAt` (`AppId`,`ClusterName`,`NamespaceName`,`DeletedAt`),
269269
KEY `DataChange_LastTime` (`DataChange_LastTime`),
270-
KEY `IX_NamespaceName` (`NamespaceName`(191))
270+
KEY `IX_NamespaceName` (`NamespaceName`)
271271
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='命名空间';
272272

273273

@@ -316,7 +316,7 @@ CREATE TABLE `Release` (
316316
`DataChange_LastTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '最后修改时间',
317317
PRIMARY KEY (`Id`),
318318
UNIQUE KEY `UK_ReleaseKey_DeletedAt` (`ReleaseKey`,`DeletedAt`),
319-
KEY `AppId_ClusterName_GroupName` (`AppId`,`ClusterName`(191),`NamespaceName`(191)),
319+
KEY `AppId_ClusterName_GroupName` (`AppId`,`ClusterName`,`NamespaceName`),
320320
KEY `DataChange_LastTime` (`DataChange_LastTime`)
321321
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='发布';
322322

‎scripts/sql/src/delta/v230-v240/apolloconfigdb-v230-v240.sql‎

Lines changed: 17 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -23,16 +23,27 @@ ALTER TABLE `AccessKey`
2323
ADD COLUMN `Mode` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '密钥模式，0: filter，1: observer' AFTER `Secret`;
2424

2525
ALTER TABLE `Commit`
26-
MODIFY COLUMN `ClusterName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Cluster Name';
27-
ALTER TABLE `Commit`
26+
MODIFY COLUMN `ClusterName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Cluster Name',
2827
MODIFY COLUMN `NamespaceName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Namespace Name';
2928
ALTER TABLE `Namespace`
30-
MODIFY COLUMN `ClusterName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Cluster Name';
31-
ALTER TABLE `Namespace`
29+
MODIFY COLUMN `ClusterName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Cluster Name',
3230
MODIFY COLUMN `NamespaceName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Namespace Name';
3331
ALTER TABLE `Release`
34-
MODIFY COLUMN `ClusterName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Cluster Name';
35-
ALTER TABLE `Release`
32+
MODIFY COLUMN `ClusterName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Cluster Name',
3633
MODIFY COLUMN `NamespaceName` VARCHAR(32) NOT NULL DEFAULT 'default' COMMENT 'Namespace Name';
3734

35+
ALTER TABLE `Commit`
36+
DROP INDEX `ClusterName`,
37+
DROP INDEX `NamespaceName`,
38+
ADD INDEX `ClusterName` (`ClusterName`),
39+
ADD INDEX `NamespaceName` (`NamespaceName`);
40+
ALTER TABLE `Namespace`
41+
DROP INDEX `UK_AppId_ClusterName_NamespaceName_DeletedAt`,
42+
DROP INDEX `IX_NamespaceName`,
43+
ADD UNIQUE INDEX `UK_AppId_ClusterName_NamespaceName_DeletedAt` (`AppId`,`ClusterName`,`NamespaceName`,`DeletedAt`),
44+
ADD INDEX `IX_NamespaceName` (`NamespaceName`);
45+
ALTER TABLE `Namespace`
46+
DROP INDEX `AppId_ClusterName_GroupName`,
47+
ADD INDEX `AppId_ClusterName_GroupName` (`AppId`,`ClusterName`,`NamespaceName`);
48+
3849
-- ${gists.autoGeneratedDeclaration}

0 commit comments

Comments
 (0)