mysql查询之间_MySQL：查询之间的最佳索引

我有一个具有以下结构的表：

CREATE TABLE `geo_ip` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`start_ip` int(10) unsigned NOT NULL,

`end_ip` int(10) unsigned NOT NULL,

PRIMARY KEY (`id`),

KEY `start_ip` (`start_ip`),

KEY `end_ip` (`end_ip`),

KEY `start_end` (`start_ip`,`end_ip`),

KEY `end_start` (`end_ip`,`start_ip`)) ENGINE=InnoDB;

MySQL似乎无法在我的大多数查询中使用索引,因为where子句使用介于start_ip和end_ip之间的介于两者之间：

select * from geo_ip where 2393196360 between start_ip and end_ip;

+----+-------------+--------+------+-------------------------------------+------+---------+------+---------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------+------+-------------------------------------+------+---------+------+---------+-------------+

| 1 | SIMPLE | geo_ip | ALL | start_ip,end_ip,start_end,end_start | NULL | NULL | NULL | 2291578 | Using where |

+----+-------------+--------+------+-------------------------------------+------+---------+------+---------+-------------+

该表有几百万条记录.我尝试通过删除start_ip和end_ip列来扩展表,并为start_ip和end_ip的每个可能值创建一个行作为id,然后查询id.虽然这大大提高了查询性能,但它导致表大小从不到1千兆字节增长到数十千兆字节(表中显然还有其他列).

还有什么可以提高查询性能？我可以以某种方式更改查询,还是可以不同地索引列以导致命中？或者也许是我还没有想到的东西？

编辑：

奇怪的是,索引用于某些值.例如：

explain select * from geo_ip where 3673747503 between start_ip and end_ip;

+----+-------------+--------+-------+-------------------------------------+--------+---------+------+-------+-------------+

| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+----+-------------+--------+-------+-------------------------------------+--------+---------+------+-------+-------------+

| 1 | SIMPLE | geo_ip | range | start_ip,end_ip,start_end,end_start | end_ip | 4 | NULL | 19134 | Using where |

+----+-------------+--------+-------+-------------------------------------+--------+---------+------+-------+-------------+