我有两个看起来像这样的表:

                  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 and 20 之间,则具有该 IP 地址的用户来自美国 (US)。

我的问题:我在 Users 表中有大约 60,000 个用户 - 每个用户都有一个与之关联的 IP 号码。我还有一个 IpNumbers 表,里面有大约 4,000,000 条 Ip Number vs Country 的记录。

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

注意:我在 min 表中索引了 maxipNumbers 列,我还在 userId 表中索引了 Users 列。 userId 字段也是唯一的。

编辑:users 表中的 ipNumber 也是派生的整数值

最佳答案

这是一个比较常见的问题。我认为最好的方法是从 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;

这应该使用 whereorder by 的索引。但是,它不会验证结束条件。如果需要,可以用同样的逻辑把max取出来,然后做一个比较,看看ip地址是否在范围内。

关于mysql - 按值有效地将表连接(并更新)到最小/最大值范围内,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/35122686/

10-12 07:38
查看更多