mysql - 范围查询的高效数据模型

Question

我正在努力想出一种有效的模型来描述 IPv4 地址数据。我希望能够对 MySQL 中的数据集执行“whois”类型的查找。目前我有这个:

CREATE TABLE inetnum (
 `from_ip` int(11) unsigned NOT NULL,
 `to_ip` int(11) unsigned NOT NULL,
 `netname` varchar(40) default NULL,
 `ip_txt` varchar(60) default NULL,
 `descr` varchar(60) default NULL,
 `country` varchar(2) default NULL,
 `recurse_limit` int(11) NOT NULL default '0',
 `unexpected` int(11) NOT NULL default '0',
 `rir` enum('APNIC','AFRINIC','ARIN','RIPE','LACNIC') NOT NULL default 'RIPE',
 PRIMARY KEY  (`from_ip`,`to_ip`)
) ENGINE=MyISAM DEFAULT CHARSET=ascii;

我想做这样的查询:

SELECT *
FROM inetnum
WHERE INET_ATON('192.168.0.1') BETWEEN from_ip AND to_ip;

但是因为地址范围的上限和下限保存在不同的字段中，所以会导致全表扫描:

mysql> EXPLAIN SELECT * FROM `inetnum` WHERE INET_ATON('192.168.0.1') BETWEEN from_ip AND to_ip;
+----+-------------+---------+------+---------------+------+---------+------+---------+------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | inetnum | ALL  | NULL          | NULL | NULL    | NULL | 3800440 | Using where |
 +----+-------------+---------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

(我敢肯定有人会试图指出 - 不是因为 INET_ATON 函数 - 使用文字整数没有区别，使用 <=to_ip AND >=from_ip 也没有区别)。

当前运行在 MySQL 5.0.67 上。我更改/升级 DBMS 的范围有限。

Answer 1

实际上，您的主键对于此类范围查询而言意义不大。它仅表示 <from_ip, to_ip> 的唯一对元组 - 因此，MySQL 将无法使用该索引进行此类范围比较。

Unless you're running some query that involves both parts of your primary key, it will have no effect (well, actually, MySQL also will use it - when selection condition uses left-subset of compound index , but that's not your case).例如，这将使用主键:

-- @x and @y are derived from somewhere else
SELECT * FROM inetnum WHERE from_ip=@x && to_ip=@y

在您的情况下，复合键可能是主键，是的，但它的唯一好处是 - 提供唯一性。所以，你可以保持原样，或者创建代理 id主键(用 UNIQUE 约束替换当前主键)。

改善情况的可能解决方案之一是 - 为 from_ip 创建单列键和 to_ip .由于它们是整数，因此结果索引很有可能具有高基数。然而，MySQL 只能使用一个索引，因此，您将失去“一半”的范围有效比较。你还应该记住，如果大于(或小于)比较会影响太多的行，MySQL 也不会使用索引(因为，显然，因为要选择的行太多，所以没有意义)。

而且 - 是的，避免使用 WHERE 中的函数条款。我并不是说 MySQL 在这种情况下总是会丢失索引使用(但很可能在大多数情况下它会丢失它)——但请考虑会导致函数调用的开销。即使它很小 - 您始终可以通过传递由您的应用程序形成的正确值来摆脱它。