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
chenqh
V2EX  ›  MySQL

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

  •  
  •   chenqh · 2017-03-21 09:41:47 +08:00 · 3843 次点击
    这是一个创建于 2802 天前的主题,其中的信息可能已经有所发展或是发生改变。

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

    id startip area

    在 startip 上创建 unique 索引

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

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

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

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

    都是整数搜索,很快的。
    flniu
        5
    flniu  
       2017-03-21 15:18:31 +08:00
    两个字段的 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
        6
    vus520  
       2017-03-21 15:27:41 +08:00
    哦,你们不知道有个 geoip 库么
    chenqh
        7
    chenqh  
    OP
       2017-03-21 15:41:35 +08:00
    @flniu 可以这个样子用 索引?
    chenqh
        8
    chenqh  
    OP
       2017-03-21 15:42:20 +08:00
    @8355 不一样 啊, redis 的 get 本质还是 hash 啊, o(1)
    8355
        9
    8355  
       2017-03-21 15:47:26 +08:00
    @chenqh #8 没太看懂你的需求 你不就是要 IP 地址获取城市么.性能要求每秒 1000 次以上. 跟 hash 什么的有什么关系 这样不满足需求吗? 还是不可以用类似 hash 这种形式?
    est
        10
    est  
       2017-03-21 15:54:37 +08:00
    ip 地址定位能有多大。直接丢内存里扫表啊。
    chenqh
        11
    chenqh  
    OP
       2017-03-21 16:54:04 +08:00
    @8355 startip,endip ,怎么构成 key?
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   4943 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 28ms · UTC 01:11 · PVG 09:11 · LAX 17:11 · JFK 20:11
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.