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 · 7 天前 · 702 次点击

    源地址 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   7 天前   ♥ 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   7 天前
    @realpg 非连续数据段使用“未知 IP ”进行替代将数据库整体连续 ,是指自己插入(startip,"unkown")这种数据吗?
        3
    8355   7 天前
    直接 redis 不就行了吗?
    我们查手机号码归属地的数据 30 多万存 redis 直接 get 有什么不可以?
        4
    msg7086   7 天前
    startip INT PRI KEY

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

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

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