V2EX 首页   注册   登录
V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
V2EX  ›  MySQL

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

  •  
  •   chenqh · 243 天前 · 1039 次点击
    这是一个创建于 243 天前的主题,其中的信息可能已经有所发展或是发生改变。

    源地址 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
    
    11 回复  |  直到 2017-03-21 16:54:04 +08:00
        1
    realpg   243 天前   ♥ 1
    这水平 我觉得还是别写教程了
    这么简单的需求竟然用这种方法……
    首先这是 hot read 数据,写入变动小,应该在入库时进行预处理变为连续数据,非连续数据段使用“未知 IP ”进行替代将数据库整体连续,将表结构变为

    id startip area

    在 startip 上创建 unique 索引

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

    IO 好一点一万次查询都不是啥问题
        2
    chenqh   243 天前
    @realpg 非连续数据段使用“未知 IP ”进行替代将数据库整体连续 ,是指自己插入(startip,"unkown")这种数据吗?
        3
    8355   243 天前
    直接 redis 不就行了吗?
    我们查手机号码归属地的数据 30 多万存 redis 直接 get 有什么不可以?
        4
    msg7086   243 天前
    startip INT PRI KEY

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

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

    都是整数搜索,很快的。
        5
    flniu   243 天前
    两个字段的 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;
        6
    vus520   243 天前
    哦,你们不知道有个 geoip 库么
        7
    chenqh   243 天前
    @flniu 可以这个样子用 索引?
        8
    chenqh   243 天前
    @8355 不一样 啊, redis 的 get 本质还是 hash 啊, o(1)
        9
    8355   243 天前
    @chenqh #8 没太看懂你的需求 你不就是要 IP 地址获取城市么.性能要求每秒 1000 次以上. 跟 hash 什么的有什么关系 这样不满足需求吗? 还是不可以用类似 hash 这种形式?
        10
    est   243 天前
    ip 地址定位能有多大。直接丢内存里扫表啊。
        11
    chenqh   243 天前
    @8355 startip,endip ,怎么构成 key?
    DigitalOcean
    关于   ·   FAQ   ·   API   ·   我们的愿景   ·   广告投放   ·   鸣谢   ·   1318 人在线   最高记录 3541   ·  
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.0 · 30ms · UTC 16:16 · PVG 00:16 · LAX 08:16 · JFK 11:16
    ♥ Do have faith in what you're doing.
    沪ICP备16043287号-1