对两个整数列的查询花费了荒谬的时间

Question

我有一个（由 Django ）生成的查询，如下所示：

SELECT `geo_ip`.`id`, `geo_ip`.`start_ip`,
       `geo_ip`.`end_ip`, `geo_ip`.`start`,
       `geo_ip`.`end`, `geo_ip`.`cc`, `geo_ip`.`cn`
FROM `geo_ip`
WHERE (`geo_ip`.`start` <= 2084738290 AND `geo_ip`.`end` >= 2084738290 )
LIMIT 1

它查询包含 134189 个条目的 GeoLocating 表。添加索引后，每个查询需要 100 毫秒以上的时间来执行，这使得它无法用于一次性的事情。我将缓存响应，这样我只需进行一次 IP 查找，但我很好奇我是否错过了一些使其速度更快的明显方法。我的表：

CREATE TABLE `geo_ip` (
  `start_ip` char(15) NOT NULL,
  `end_ip` char(15) NOT NULL,
  `start` bigint(20) NOT NULL,
  `end` bigint(20) NOT NULL,
  `cc` varchar(6) NOT NULL,
  `cn` varchar(150) NOT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=134190 DEFAULT CHARSET=latin1

像这样在两列上创建索引：

ALTER TABLE geo_ip ADD INDEX (start, end);

给出以下解释：

EXPLAIN SELECT geo_ip.id, geo_ip.start_ip, geo_ip.end_ip,
               geo_ip.start, geo_ip.end, geo_ip.cc, geo_ip.cn
FROM geo_ip
WHERE (geo_ip.end >= 2084738290 AND geo_ip.start < 2084738290)
LIMIT 1;
+----+-------------+--------+-------+---------------+-------+---------+------+-------+----------+-------------+
| id | select_type | table  | type  | possible_keys | key   | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+--------+-------+---------------+-------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | geo_ip | range | start         | start | 8       | NULL | 67005 |   100.00 | Using where |
+----+-------------+--------+-------+---------------+-------+---------+------+-------+----------+-------------+

完成选择需要超过 100 毫秒：

SELECT geo_ip.id, geo_ip.start_ip, geo_ip.end_ip,
       geo_ip.start, geo_ip.end, geo_ip.cc,
       geo_ip.cn
FROM geo_ip
WHERE (geo_ip.end >= 2084738290 and geo_ip.start < 2084738290)
LIMIT 1;
+-------+--------------+----------------+------------+------------+----+-----------+
| id    | start_ip     | end_ip         | start      | end        | cc | cn        |
+-------+--------------+----------------+------------+------------+----+-----------+
| 51725 | 124.66.128.0 | 124.66.159.255 | 2084732928 | 2084741119 | SG | Singapore |
+-------+--------------+----------------+------------+------------+----+-----------+
1 row in set (0.18 sec)

比拥有单个索引更昂贵：

ALTER TABLE geo_ip ADD INDEX (`start`);
ALTER TABLE geo_ip ADD INDEX (`end`);
+----+-------------+--------+-------+---------------+-------+---------+------+-------+-------------+
| id | select_type | table  | type  | possible_keys | key   | key_len | ref  | rows  | Extra       |
+----+-------------+--------+-------+---------------+-------+---------+------+-------+-------------+
|  1 | SIMPLE      | geo_ip | range | start,end     | start | 8       | NULL | 68017 | Using where |
+----+-------------+--------+-------+---------------+-------+---------+------+-------+-------------+

完成这些请求大约需要 100 毫秒：

SELECT geo_ip.id, geo_ip.start_ip, geo_ip.end_ip, geo_ip.start, geo_ip.end, geo_ip.cc, geo_ip.cn FROM geo_ip
WHERE (geo_ip.end >= 2084738290 AND geo_ip.start < 2084738290) limit 1;
+-------+--------------+----------------+------------+------------+----+-----------+
| id    | start_ip     | end_ip         | start      | end        | cc | cn        |
+-------+--------------+----------------+------------+------------+----+-----------+
| 51725 | 124.66.128.0 | 124.66.159.255 | 2084732928 | 2084741119 | SG | Singapore |
+-------+--------------+----------------+------------+------------+----+-----------+
1 row in set (0.11 sec)

但这两种方法时间太长了，有什么办法可以解决这个问题吗？

Answer 1

时间总是消耗在“where”子句中。

而且由于您正在处理“低于”或“大于”的两个不同字段，因此它必须读取大量索引才能找出您想要的记录。

我应该这样完成我的表格：

+-------+-------+----------------+------------+----+-----------+
| id    | type  | ip             | geo        | cc | cn        |
+-------+-------+----------------+------------+----+-----------+
| 51725 | start | 124.66.159.255 | 2084732928 | SG | Singapore |
+-------+-------+----------------+------------+----+-----------+
| 51726 | end   | 124.66.159.255 | 2084732928 | SG | Singapore |
+-------+-------+----------------+------------+----+-----------+

这样我就可以选择这个：

select * from table where geo between '2084732927' and '2084732928'

在地理上有一个索引。
应该快得多。但抱歉，我没有时间尝试。