MySQL索引获取用于范围的2列之间的记录

Question

我有一个用于城市 ip block 的 MySQL 表，

ip_ranges(city_id, CIDR, start_ip_decimal, end_ip_decimal)

数据类型如下

1. city_id [unsigned bigint]
2. CIDR [varchar 255]
3. start_ip_decimal [unsigned bigint] : 起始ip范围转为十进制
4. end_ip_decimal [unsigned bigint] : 结束ip范围转为十进制

所以我所做的是，将用户 ip 转换为十进制，并检查此表以获取 city_id .但是查询花费了太多时间 70 秒 来查找 city_id

SELECT city_id FROM ip_ranges WHERE 658206441 BETWEEN start_ip_decimal and end_ip_decimal 

或

SELECT city_id FROM ip_ranges WHERE start_ip_decimal <= 658206441 AND end_ip_decimal >= 658206441

注意:658206441是用户ip地址转换后的十进制值

InnoDB 用作数据库引擎。该表共有10664916条记录。

Corei7 2.0GHz and 2.6 GHz Processor with 8 GB Ram (windows 10)

所以我的问题是如何加快查找速度。

我尝试在开始和结束字段上应用索引索引类型 Normal和索引方法 BTREE , 但没有影响。

这是 DDL 的样子

CREATE TABLE `ip_ranges` (
  `cidr` varchar(255) DEFAULT NULL,
  `start_ip_decimal` bigint(20) unsigned DEFAULT NULL,
  `end_ip_decimal` bigint(20) unsigned DEFAULT NULL,
  `city_id` bigint(20) unsigned DEFAULT NULL,
  KEY `my_index` (`start_ip_decimal`,`end_ip_decimal`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

解决方案:我已经根据@RickJames 的评论解决了(我认为是)我的问题，但我可能遗漏了他回复的一些部分。

首先学到的是

Range tests like IP-addresses are difficult. No simple index works well.

所以我执行了以下步骤来实现我想要的。

1 忽略了 end字段，在 Jame 的评论之后我意识到，在每一行中 start字段等于 end+1上一行。

However, if, instead of a range, you have just a starting IP, and have the 'end' being the start of the next row, it can be made much more efficient.

network     start       end
--------------------------------
1.0.0.0/24  16777216    16777471
1.0.1.0/24  16777472    16777727
1.0.2.0/23  16777728    16778239

但是可能存在一些遗漏范围的问题

2 已申请 UNIQUE key index在 start列使用 BTREE

3 已修改 SELECT查询如下

SELECT * FROM ip_ranges
WHERE $ipNumberToCheck >= `start` ORDER BY `start` DESC LIMIT 1

Answer 1

我希望它是INT UNSIGNED(未签名)。

我想您不担心 IPv6？

像 IP 地址这样的范围测试很困难。没有简单的索引可以很好地工作。您拥有的 KEY 将平均扫描 500 万行。

但是，如果您没有范围，而只有一个起始 IP，并且“结束”是下一行的开始，则可以提高效率。这也涉及 ORDER BY ip LIMIT 1。它涉及拥有未使用的 IP 范围的条目。我在 my blog 中介绍了所有这些以及高效的代码.它包括 IPv4(就像您正在使用的)和 IPv6 的存储例程。无论表格大小如何，它都会在单行提取中找到城市。所以它仅限于一次磁盘命中(粗略地说)。从逻辑上讲，它的速度是它的 500 万倍；但实际上 70 秒应该减少到几毫秒。