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


•实战范例 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

startip

endip

ip_area

地址

11 replies • 2017-03-21 16:54:04 +08:00

realpg

Mar 21, 2017

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

id startip area

在 startip 上创建 unique 索引

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

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

simple2025

Mar 21, 2017

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

8355

Mar 21, 2017

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

msg7086

Mar 21, 2017

startip INT PRI KEY

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

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

都是整数搜索，很快的。

flniu

Mar 21, 2017

两个字段的 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

Mar 21, 2017

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

simple2025

Mar 21, 2017

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

simple2025

Mar 21, 2017

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

8355

Mar 21, 2017

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

est

Mar 21, 2017

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

simple2025

Mar 21, 2017

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