VARCHAR(15)
字段来存放字符串形式的IP而不是整形的IP。如果你用整形来存放，只需要4个字节，并且你可以有定长的字段。而且，这会为你带来查询上的优势，尤其是当你需要使用这样的WHERE条件：IP
between
ip1
and
ip2。
我们必需要使用UNSIGNED
INT，因为
IP地址会使用整个32位的无符号整形。
而你的查询，你可以使用
INET_ATON()
来把一个字符串IP转成一个整形，并使用
INET_NTOA()
把一个整形转成一个字符串IP。在PHP中，也有这样的函数
ip2long()
和
long2ip()。
$r
=
"UPDATE
users
SET
ip
=
INET_ATON('{$_SERVER['REMOTE_ADDR']}')
WHERE
user_id
=
$user_id"
今天朋友打了个电话，他们网站的业务要根据客户的ip地址快速定位客户的地理位置。网上已经有一大堆类似的ip地址库可以用，但问题是这些地址库的数据表结构大多如下所示+--------------+------------------+------+-----+---------+----------------+
| Field| Type | Null | Key | Default | Extra |
+--------------+------------------+------+-----+---------+----------------+
| ip_id| int(11) unsigned | NO | PRI | NULL| auto_increment |
| ip_country | varchar(50) | NO | | NULL||
| ip_startip | bigint(11) | NO | MUL | NULL||
| ip_endip | bigint(11) | NO | MUL | NULL||
| country_code | varchar(2) | NO | | NULL||
| zone_id | int(11) | NO | | 0 ||
+--------------+------------------+------+-----+---------+----------------+
最核心的部分是三个： ip_startip ， ip_endip 以及 ip_id 。其中 ip_id 是要查询的结果，当然也可以把 zone_id 和 ip_country 包括进去。这里就用 ip_id 来特指查询结果了。
面对这个表，没什么其它办法，查询语句只能是
SELECT * FROM who_ip WHERE ip_startip <= {ip} AND ip_endip >= {ip}
其中 {ip} 是要查询的ip地址，为了方便查询，在php中一般要用 ip2long函数把它转换为一个整数。现在问题来了，这个表有400万条数据，无论你怎么优化它的索引结构（实际上我觉得这没啥用），在以上查询语句中都要耗费2秒以上的时间，对于一个高频使用的接口，这显然是不可忍受的。
REDIS能不能解决这个问题。实际上这也是朋友最关心的问题，因为知道Redis有强大数据结构和超快的速度，那么能不能设计出适应这种查询场景的结构。
范围查询，首先想到的就是Redis里面的 Sorted Sets 结构，这也是redis中唯一可以指定范围( SCORE 值)查询的结构了，所以基本上希望都寄托在它身上了。
最简单粗暴的方法就是把 ip_startip 和 ip_endip 都转化为 Sorted Sets 里的 Score ，然后把 ip_id 定义为 Member 。这样我们的查询就很简单了，只需要用 ZRANGESCORE 查询出离ip最近SCORE对应的两个 ip_id 即可。然后再分析，如果这两个 ip_id 是相同的，那么说明这个ip在这个地址段，如果不同的话证明这个ip地址没有被任何地址段所定义，是一个未知的ip。
基本逻辑是没有问题的，但是最大的问题还是性能上的挑战。根据我的经验，一个SET 里面放10万条数据以上就已经很慢了，如果放到400万这种量级，我非常怀疑它跟mysql相比还有优势吗？
我设计的存储结构
我的解决方案是把这个地址库切分，每一片区最多保存65536个地址。也就是说如果一个ip地址段为 188.88.77.22 - 188.90.78.10 ，那么我们就把它切分为
188.88.77.22 - 188.88.77.255
188.89.0.0 - 188.89.255.255
188.90.0.0 - 189.90.78.10
也就是我们保证每一个ip地址段都被保存在 xxx.xxx.0.0 - xxx.xxx.255.255的一个区段中，这个区段的理论极限是保存65536个值，实际上要远小于这个数字。而这样的区段理论上也有65536个，这都是ip地址的设计所限，当然实际上也远小于这个值。
因此这样的设计基本上就能满足我们的性能需要了。以下是我用php写的数据切分程序
<?php
// redis 参数
define('REDIS_HOST', '127.0.0.1')
define('REDIS_PORT', 6379)
define('REDIS_DB', 10)
define('MYSQL_HOST', 'localhost')
define('MYSQL_PORT', 3306)
define('MYSQL_USER', 'root')
define('MYSQL_PASS', '123456')
define('MYSQL_DB', 'who_brand')
define('MYSQL_TABLE', 'who_ip')
define('MYSQL_COLUMN_START', 'ip_startip')
define('MYSQL_COLUMN_END', 'ip_endip')
define('MYSQL_COLUMN_ID', 'ip_id')
define('MYSQL_PAGESIZE', 1000)
mysql_connect(MYSQL_HOST . ':' . MYSQL_PORT, MYSQL_USER, MYSQL_PASS)
mysql_select_db(MYSQL_DB)
function add_ip($page, $offset, $value) {
static $redis
if (!$redis) {
$redis = new Redis()
$redis->connect(REDIS_HOST, REDIS_PORT)
$redis->select(REDIS_DB)
}
$key = 'ip:' . $page
$redis->zAdd($key, $offset, $value)
}
$page = 0
do {
$offset = $page * MYSQL_PAGESIZE
$count = 0
$res = mysql_query('SELECT * FROM ' . MYSQL_TABLE . ' LIMIT ' . MYSQL_PAGESIZE . " OFFSET {$offset}")
while ($ip = mysql_fetch_assoc($res)) {
$start = $ip[MYSQL_COLUMN_START]
$end = $ip[MYSQL_COLUMN_END]
$value = $ip[MYSQL_COLUMN_ID]
$startOffset = $start % 65536
$endOffset = $end % 65536
$start -= $startOffset
$end -= $endOffset
$startPage = $start / 65536
$endPage = $end / 65536
for ($i = $startPage$i <= $endPage$i ++) {
if ($i == $startPage) {
add_ip($i, $startOffset, 's:' . $value)
if ($i != $endPage) {
add_ip($i, 65535, 'e:' . $value)
}
}
if ($i == $endPage) {
add_ip($i, $endOffset, 'e:' . $value)
if ($i != $startPage) {
add_ip($i, 0, 's:' . $value)
}
}
if ($i != $endPage &&$i != $startPage) {
add_ip($i, 0, 's:' . $value)
add_ip($i, 65535, 'e:' . $value)
}
}
echo ($page * MYSQL_PAGESIZE + $count) . "\n"
$count ++
}
$page ++
} while ($count = MYSQL_PAGESIZE)
<?php
define('REDIS_HOST', '127.0.0.1')
define('REDIS_PORT', 6379)
define('REDIS_DB', 10)
$redis = new Redis()
$redis->connect(REDIS_HOST, REDIS_PORT)
$redis->select(REDIS_DB)
$ip = ip2long('173.255.218.70')
$offset = $ip % 65536
$page = ($ip - $offset) / 65536
// 取出小于等于它的最接近值
$start = $redis->zRevRangeByScore('ip:' . $page, 0, $offset, array(
'limit' =>array(0, 1)
))
// 取出大于等于它的最接近值
$end = $redis->zRangeByScore('ip:' . $page, $offset, 65535, array(
'limit' =>array(0, 1)
))
if (empty($start) || empty($end)) {
echo 'unknown'
exit
}
$start = $start[0]
$end = $end[0]
list ($startOp, $startId) = explode(':', $start)
list ($endOp, $endId) = explode(':', $end)
if ($startId != $endId) {
echo 'unknown'
exit
}
echo $startId
思路：
获取访问用户ip，查询数据库判断该ip是否可以继续注册新用户
示例
/*** Created by PhpStorm.
* User: Administrator
* Date: 2018/11/30
* Time: 19:35
* 限制一个ip一天只能注册10个账户
* 获取访问用户ip，查询数据库判断该ip是否可以继续注册新用户
*/
//获取数据库实例
$dsn = 'mysql:dbname=testhost=127.0.0.1'
$user = 'root'
$password = ''
try {
$db = new PDO($dsn, $user, $password,array(PDO::MYSQL_ATTR_INIT_COMMAND => "set names utf8"))
} catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage()
}
//获取访问用户ip
$access_user_ip = $_SERVER['REMOTE_ADDR']
//查询数据库判断该ip是否可以继续注册新用户
$start_time = strtotime(date('Y-m-d'))//今天0点
$end_time = strtotime(date('Y-m-d').' +1 day ')//明天0点
$sth = $db->prepare('select count(*) from user where ip=:ip and created_at>:start_time and created_at<:end_time')
$sth->bindParam(':ip',$access_user_ip)
$sth->bindParam(':start_time',$start_time)
$sth->bindParam(':end_time',$end_time)
$sth->execute()
$count = $sth->fetchColumn()//当前该ip今天注册的用户总数量
if ($count>10){
exit('今天，您已注册10个新账号了，请明天再来吧')
}
源码放在github上，欢迎点星网页链接
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）