mysql - 在具有数百万行的 MySQL 表中进行高效查找

Question

我有一个包含大约 2000 万行的 CSV 文件，我想在我的 Web 应用程序中使用它。数据是邮政编码到实际街道地址的映射，格式如下:

[zip_or_postal_code] [street_number] [street_name] [city] [state_or_province] [country]

我的目标是将查找(按邮政编码搜索)时间控制在 200 毫秒以内。

我不确定这是否会有所作为，但我正计划执行以下操作:

• 将 state/province、country 和 city 列移动到它们自己的表中，并在我的主表中引用它们以避免不必要的膨胀。
• 一些邮政编码涵盖多个街道和地址，因此我将合并数据并拥有 1 个邮政编码，并将多个地址存储在类似 varchar 的内容中。这应该会从表中减少几百万行。

我可以进行哪些优化来帮助提高查找速度？例如，Google 的反向地理定位 API 在 300 毫秒内返回结果，其中包括 HTTP 开销。他们是怎么做到的？

此外，我对使用其他数据库持开放态度，但由于我已经在使用 MySQL，因此更好。

编辑:查找将始终通过 zip /邮政编码完成，举个例子:给定 zip 12345，我需要返回街道 #( s)/姓名、城市、州和国家。但是，街道 #(s)/name(s) 将存储为单个字符串字段，因此我的应用程序将负责解析它们。

Answer 1

2000 万行对于 MySQL 来说不算多。只需索引邮政编码，速度就会很快。速度不到 200 毫秒。无需在表之间拆分。当结果集很大时，MySQL 确实会变慢，但您似乎不会遇到该问题。对于像您这样的基本查询，MySQL 可以很好地处理数亿条记录。

您需要调整 MySQL 设置，以便它使用更多内存。默认设置非常低。

MySQL 确实支持空间索引。因此，您可以提取邮政编码的经度/纬度，并使用空间索引进行邻近搜索。不过，您似乎并没有在寻找它。

如果你真的想要非常非常快的东西，走你想的路线，但使用 memcache 或 redis。您可以使用邮政编码作为查找键。您仍然需要一个基于持久磁盘的数据存储来加载数据。我认为 memcache/redis 不是必需的，但它是一个选项。