为我效劳https://simibat.ru/fftracker 我有一些多对多（junction）表，例如字符成就表，它包含两列外键（其中一列也是复合主键）和第三列日期或其他相关内容。在成就表的示例中，它有3600多万行，总大小约为1GB，索引略低于500MB。没什么特别的，我知道。我关心的是指数的增长率。
在大约一周的时间里，索引至少会额外获得约400兆字节的数据，而实际的新数据最多只能获得约10兆字节。经过表优化后，这400兆字节变成了~5兆字节。每周运行优化并不是什么大问题，但我很好奇是否有办法降低如此显著（而且毫无意义）的增长率，因为这似乎是一种合适的方法。
我知道，我可以尝试简单地删除id的外键（因为它已经在我的所有查询中使用的复合索引中使用），但这感觉像是一个过度复杂的逻辑到php的迁移，而不是利用本机函数。
你知道我该怎么做吗？
更新另一件事要注意：我正在运行insert…on duplicate key update。会不会，这会更新索引，导致它增长？
按要求创建表的名称。我关注的表格是ff\字符\成就（连接），其余主要是供参考（以防万一）。

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