mysql - 降低 MySQL 中的索引增长率

Question

在我的服务https://simibat.ru/fftracker我有一些多对多（联结）表，例如用于角色成就的表，它由 2 个带有外键的列组成（其中一个也是复合主键）以及带有日期或其他相关内容的第三列。在成就表的示例中，它有 36+ 百万行，总大小约为 1 GB，索引略低于 500 MB。没什么特别的，我知道。我担心的是指数的增长速度。

在大约一周内，索引至少获得了约 400 MB 的额外 MB，用于约 10 MB（最多）的实际新数据。在表优化之后，这 400 MB 变成了约 5 MB。每周运行优化并不是什么大问题，但我很好奇是否有办法降低如此显着（且毫无意义）的增长率，因为这似乎是一种合适的方法。

我知道，我可以尝试简单地删除成就 ID 的外键（因为它已经在我的所有查询中使用的复合索引中使用），但这感觉像是将逻辑过度复杂地迁移到 PHP，而不是利用本机函数。

那么，知道如何解决这个问题吗？

更新 另一件需要注意的事情：我正在运行 INSERT...ON DUPLICATE KEY UPDATE。会不会，这会更新索引，从而导致它增长？

根据要求创建表格。我关心的表是 ff__character_achievement (junctions)，其余的主要是供参考（以防万一）。

   CREATE TABLE `ff__character_achievement` (
     `characterid` int(10) unsigned NOT NULL,
     `achievementid` smallint(5) unsigned NOT NULL,
     `time` date NOT NULL,
     PRIMARY KEY (`characterid`,`achievementid`),
     KEY `ach` (`achievementid`) USING BTREE,
     CONSTRAINT `char_ach_ach` FOREIGN KEY (`achievementid`) REFERENCES `ff__achievement` (`achievementid`) ON DELETE CASCADE ON UPDATE CASCADE,
     CONSTRAINT `char_ach_char` FOREIGN KEY (`characterid`) REFERENCES `ff__character` (`characterid`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

CREATE TABLE `ff__achievement` (
     `achievementid` smallint(5) unsigned NOT NULL,
     `name` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
     `category` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
     `subcategory` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
     `icon` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
     `howto` text COLLATE utf8mb4_unicode_ci,
     `points` tinyint(3) unsigned DEFAULT NULL,
     `title` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
     `item` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
     `itemicon` varchar(150) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
     `itemid` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
     PRIMARY KEY (`achievementid`),
     KEY `name` (`name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

    CREATE TABLE `ff__character` (
     `characterid` int(10) unsigned NOT NULL,
     `userid` int(10) unsigned DEFAULT NULL,
     `serverid` tinyint(2) unsigned NOT NULL,
     `name` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
     `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
     `freecompanyid` bigint(20) unsigned DEFAULT NULL,
     `biography` text COLLATE utf8mb4_unicode_ci,
     `titleid` smallint(5) unsigned DEFAULT NULL,
     `bio_avatar` varchar(66) COLLATE utf8mb4_unicode_ci NOT NULL,
     `clanid` tinyint(2) unsigned NOT NULL,
     `genderid` tinyint(1) unsigned NOT NULL,
     `namedayid` smallint(3) unsigned NOT NULL,
     `guardianid` tinyint(2) unsigned NOT NULL,
     `cityid` tinyint(1) unsigned NOT NULL,
     `fc_rankidprev` tinyint(2) unsigned DEFAULT NULL,
     `fc_rankid` tinyint(2) unsigned DEFAULT NULL,
     `fc_ranklvlupd` timestamp NULL DEFAULT NULL,
     `fc_nextprom` text COLLATE utf8mb4_unicode_ci,
     `gcrankid` tinyint(2) unsigned DEFAULT NULL,
     `Alchemist` tinyint(3) unsigned DEFAULT NULL,
     `Armorer` tinyint(3) unsigned DEFAULT NULL,
     `Astrologian` tinyint(3) unsigned DEFAULT NULL,
     `Bard` tinyint(3) unsigned DEFAULT NULL,
     `BlackMage` tinyint(3) unsigned DEFAULT NULL,
     `Blacksmith` tinyint(3) unsigned DEFAULT NULL,
     `Botanist` tinyint(3) unsigned DEFAULT NULL,
     `Carpenter` tinyint(3) unsigned DEFAULT NULL,
     `Culinarian` tinyint(3) unsigned DEFAULT NULL,
     `DarkKnight` tinyint(3) unsigned DEFAULT NULL,
     `Dragoon` tinyint(3) unsigned DEFAULT NULL,
     `Fisher` tinyint(3) unsigned DEFAULT NULL,
     `Goldsmith` tinyint(3) unsigned DEFAULT NULL,
     `Leatherworker` tinyint(3) unsigned DEFAULT NULL,
     `Machinist` tinyint(3) unsigned DEFAULT NULL,
     `Miner` tinyint(3) unsigned DEFAULT NULL,
     `Monk` tinyint(3) unsigned DEFAULT NULL,
     `Ninja` tinyint(3) unsigned DEFAULT NULL,
     `Paladin` tinyint(3) unsigned DEFAULT NULL,
     `RedMage` tinyint(3) unsigned DEFAULT NULL,
     `Samurai` tinyint(3) unsigned DEFAULT NULL,
     `Scholar` tinyint(3) unsigned DEFAULT NULL,
     `Summoner` tinyint(3) unsigned DEFAULT NULL,
     `Warrior` tinyint(3) unsigned DEFAULT NULL,
     `Weaver` tinyint(3) unsigned DEFAULT NULL,
     `WhiteMage` tinyint(3) unsigned DEFAULT NULL,
     PRIMARY KEY (`characterid`,`name`) USING BTREE,
     KEY `userid` (`userid`),
     KEY `char_fc` (`freecompanyid`) USING BTREE,
     KEY `genderid` (`genderid`),
     KEY `clanid` (`clanid`),
     KEY `guardianid` (`guardianid`),
     KEY `namedayid` (`namedayid`),
     KEY `cityid` (`cityid`),
     KEY `serverid` (`serverid`),
     KEY `gcrankid` (`gcrankid`),
     KEY `titleid` (`titleid`),
     KEY `fc_rankid` (`fc_rankid`),
     KEY `fc_rankidprev` (`fc_rankidprev`),
     CONSTRAINT `character_fcid` FOREIGN KEY (`freecompanyid`) REFERENCES `ff__freecompany` (`freecompanyid`) ON DELETE SET NULL ON UPDATE CASCADE,
     CONSTRAINT `cityid` FOREIGN KEY (`cityid`) REFERENCES `ff__city` (`cityid`) ON DELETE CASCADE ON UPDATE CASCADE,
     CONSTRAINT `clanid` FOREIGN KEY (`clanid`) REFERENCES `ff__clan` (`clanid`) ON DELETE CASCADE ON UPDATE CASCADE,
     CONSTRAINT `fc_rankid` FOREIGN KEY (`fc_rankid`) REFERENCES `ff__freecompany_rank` (`rankid`) ON DELETE SET NULL ON UPDATE SET NULL,
     CONSTRAINT `fc_rankidprev` FOREIGN KEY (`fc_rankidprev`) REFERENCES `ff__freecompany_rank` (`rankid`) ON DELETE SET NULL ON UPDATE SET NULL,
     CONSTRAINT `gcrankid` FOREIGN KEY (`gcrankid`) REFERENCES `ff__grandcompany_rank` (`gcrankid`) ON DELETE SET NULL ON UPDATE SET NULL,
     CONSTRAINT `genderid` FOREIGN KEY (`genderid`) REFERENCES `usersys__gender` (`genderid`) ON DELETE CASCADE ON UPDATE CASCADE,
     CONSTRAINT `guardianid` FOREIGN KEY (`guardianid`) REFERENCES `ff__guardian` (`guardianid`) ON DELETE CASCADE ON UPDATE CASCADE,
     CONSTRAINT `namedayid` FOREIGN KEY (`namedayid`) REFERENCES `ff__nameday` (`namedayid`) ON DELETE CASCADE ON UPDATE CASCADE,
     CONSTRAINT `serverid` FOREIGN KEY (`serverid`) REFERENCES `ff__server` (`serverid`) ON DELETE CASCADE ON UPDATE CASCADE,
     CONSTRAINT `titleid` FOREIGN KEY (`titleid`) REFERENCES `ff__achievement` (`achievementid`) ON DELETE SET NULL ON UPDATE SET NULL,
     CONSTRAINT `userid` FOREIGN KEY (`userid`) REFERENCES `usersys__logins` (`userid`) ON DELETE SET NULL ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=COMPACT

标签： mysqlindexingsize