mysql 优化教程关于 ip 地址反查

[chenqh]

源地址 http://www.cnblogs.com/czh-liyu/archive/2012/02/27/2370583.html

•实战范例 1 ： ip 地址反查

•资源： Ip 地址对应表，源数据格式为 startip, endip, area 



源数据条数为 10 万条左右，呈很大的分散性

•目标：需要通过任意 ip 查询该 ip 所属地区


性能要求达到每秒 1000 次以上的查询效率

•挑战：如使用 between startip and endip 这样的条件数据库操作，因为涉及两个字段的 betweenand, 无法有效使用索引。


如果每次查询请求需要遍历 10 万条记录，根本不行。

•方法：一次性排序（只在数据准备中进行，数据可存储在内存序列）


折半查找（每次请求以折半查找方式进行）

假如这个表为 ip_area(startip,endip,area)
我可不可以这样 
select max(startip) from ip_area where startip<=@query_ip

[realpg]

这水平 我觉得还是别写教程了
这么简单的需求竟然用这种方法……
首先这是 hot read 数据，写入变动小，应该在入库时进行预处理变为连续数据，非连续数据段使用“未知 IP ”进行替代将数据库整体连续，将表结构变为

id startip area

在 startip 上创建 unique 索引

然后查询时直接使用
select * from ip_addr where startip<='查询 IP' order by startip asc limit 1

IO 好一点一万次查询都不是啥问题

[chenqh]

@realpg 非连续数据段使用“未知 IP ”进行替代将数据库整体连续 ,是指自己插入(startip,"unkown")这种数据吗？

[8355]

直接 redis 不就行了吗?
我们查手机号码归属地的数据 30 多万存 redis 直接 get 有什么不可以?

[msg7086]

startip INT PRI KEY

然后用类似一楼的语句即可：
SELECT * FROM addr WHERE startip <= 地址 ORDER BY startip DESC LIMIT 1

就算不连续也没事，判断一下 endip 与地址的大小就好了。

都是整数搜索，很快的。

[flniu]

两个字段的 BETWEEN AND 也可以使用索引：

CREATE TABLE ip_addr(
startip int unsigned NOT NULL,
endip int unsigned NOT NULL,
area varchar(300) NOT NULL,
PRIMARY KEY(startip, endip)
);

EXPLAIN
SELECT * FROM ip_addr WHERE 2048 BETWEEN startip AND endip;

[vus520]

哦，你们不知道有个 geoip 库么

[chenqh]

@flniu 可以这个样子用 索引？

[chenqh]

@8355 不一样 啊， redis 的 get 本质还是 hash 啊， o(1)

[8355]

@chenqh #8 没太看懂你的需求 你不就是要 IP 地址获取城市么.性能要求每秒 1000 次以上. 跟 hash 什么的有什么关系 这样不满足需求吗? 还是不可以用类似 hash 这种形式?

[est]

ip 地址定位能有多大。直接丢内存里扫表啊。

[chenqh]

@8355 startip,endip ，怎么构成 key?