mysql - 按值有效地将表连接(和更新)到最小/最大值范围

Question

我有两个表格，如下所示:

                  IpNumbers
+----------+----------+-----------------------+
|____min___|____max___|__correspondingCountry_|
|          |          |                       |
|    0     |   10     |       Australia       |
|    11    |   20     |           US          |
|    21    |   30     |         Taiwan        |
|____31____|___40_____|_________Canada________|



                  Users
+----------+----------+----------------+
|__userId__|__ipNumber__|____country___|
|    1     |      6     |              |
|    2     |     13     |              |
|    3     |      7     |              |
|____4_____|_____21_____|______________|

IpNumbers 表具有从 IP 地址派生的数字 - 这对应于 IP 地址所属的特定国家/地区。例如，如果 IP 编号介于 11 和 20 之间，则具有该 IP 地址的用户来自美国 (US)。

我的问题:我在 Users 表中有大约 60,000 个用户 - 每个用户都有一个与之关联的 IP 号。我还有 IpNumbers 表，其中包含大约 4,000,000 条 IP 编号与国家/地区的记录。

我可以构造什么最有效的 SQL 查询来为 Users 表中的每个用户分配一个国家/地区？我预计查询将在多长时间内完成？

注意:我已经为 ipNumbers 表中的 min 和 max 列建立了索引，并且还为 userId 建立了索引Users 表中的列。 userId 字段也是唯一的。

编辑:users 表中的 ipNumber 也是导出的整数值

Answer 1

这是一个相当普遍的问题。我认为最好的方法是从 IpNumbers(min, max, corresponding_country) 上的索引开始。 (最后一列不是绝对必要的)。

然后，您可以使用以下查询:

select u.*,
       (select ipn.corresponding_country
        from IpNumbers ipn
        where ipn.min <= u.ipNumber
        order by ipn.min desc
        limit 1
       ) as corresponding_country
from users u;

这应该使用where 和order by 的索引。但是，它不会验证结束条件。如果需要，您可以使用相同的逻辑获取max，然后进行比较以查看ip 地址是否在该范围内。