我有两个看起来像这样的表:
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
表中索引了 max
和 ipNumbers
列,我还在 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;
这应该使用
where
和 order by
的索引。但是,它不会验证结束条件。如果需要,可以用同样的逻辑把max
取出来,然后做一个比较,看看ip地址是否在范围内。关于mysql - 按值有效地将表连接(并更新)到最小/最大值范围内,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/35122686/