mysql - 数百万行表上联接的性能

我需要给我的网站用户选择他们的国家，省和城市的能力。因此，我想显示一个国家列表，然后是所选国家的一个省份列表，然后是所选省份的一个城市列表（我现在不想使用任何其他ui解决方案）。当然，每个名字都必须用用户的语言，所以我需要额外的翻译表。
让我们关注城市的情况。这是两张桌子：

CREATE TABLE `city` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `province_id` int(10) unsigned DEFAULT NULL
  PRIMARY KEY (`id`),
  KEY `idx_fk_city_province` (`province_id`),
  CONSTRAINT `fk_city_province` FOREIGN KEY (`province_id`) REFERENCES `province` (`id`)
) ENGINE=InnoDB;

CREATE TABLE `city_translation` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `city_id` int(10) unsigned NOT NULL,
  `locale_id` int(10) unsigned DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL
  PRIMARY KEY (`id`),
  KEY `idx_fk_city_translation_city` (`city_id`),
  KEY `idx_fk_city_translation_locale` (`locale_id`),
  KEY `idx_city_translation_city_locale` (`city_id`,`locale_id`),
  CONSTRAINT `fk_city_translation_city` FOREIGN KEY (`city_id`) REFERENCES `city` (`id`),
  CONSTRAINT `fk_city_translation_locale` FOREIGN KEY (`locale_id`) REFERENCES `locale` (`id`)
) ENGINE=InnoDB;

city表包含400万行，city_translation表包含400万×我的网站上可用的语言数。现在是1200万。如果将来我想支持10种语言，那就需要4千万…
所以我想知道：使用这种大小的表是一个坏主意（从性能上看），还是一个好的索引（这里是连接字段，city_id和locale_id）足以使大小无关紧要？
如果不是，那么解决这个特定问题（但我猜是常见的）的常用解决方案是什么？我只对表演感兴趣。如果需要的话，我可以取消规格化，或者如果其他工具更合适的话，我甚至可以使用它们（elasticsearch？）是的。

最佳答案

删除城市翻译中的id。取而代之的是PRIMARY KEY(city_id, locale_id)。对于innodb，由于在JOINs中删除了不必要的步骤，因此速度可能会加倍。您还可以通过删除从city_id开始的两个索引来缩小磁盘占用空间。
你认为你会超越1600万个城市吗？我怀疑。因此，通过（在所有表中）将city_id更改为MEDIUMINT UNSIGNED来保存一个字节。
将locale_id更改为TINYINT UNSIGNED可节省3个字节。
这些节省将乘以列数和提到它们的索引数。
桌子有多大（GB）？innodb_buffer_pool_size的设置是什么？有多少内存？看看是否可以使该设置大于表的总大小，但不超过可用内存的70%。（这是唯一值得检查的“可调参数”）。
出于中国用户的考虑，我希望您的默认值为CHARACTER SET utf8mb4。（但那是另一个故事。）