Mysql，使用索引进行带有运算符>、<的选择提高性能？

Question

我需要从包含超过 500 万行的表中选择一行。它是所有 IP 范围的表。每行都有列 upperbound 和 lowerbound。都是大整数，数字是IP地址的整数表示。

选择是：

select * 
  from iptocity 
 where lowerbound < 3529167967 
   and upperbound >= 3529167967 
 limit 1;

我的问题是

......选择花费的时间太长。如果 InnoDB 表上没有索引，则需要 20 秒。如果是在 MyISAM 上则需要 6 秒。我需要它少于 0.1 秒。我需要能够每秒处理数百个这样的选择。

我尝试在 InnoDB 上的上限和下限两列上创建索引。但这并没有帮助，花了更多的时间，比如40秒来选择ip。怎么了？为什么索引没有帮助？索引是否仅适用于 = 运算符和 >、<运算符不能使用索引吗？我应该怎样做才能使选择时间更快到少于 0.1 秒？

Answer 1

您是在两列上创建一个索引，还是在每一列上创建两个索引？如果只有一个索引，那么这可能是您的问题，为每个索引创建一个索引。索引应该仍然适用于 < 和 >

除了更改索引之外，运行：

EXPLAIN
select *
from iptocity
where lowerbound<3529167967
  and upperbound>=3529167967
limit 1;

与您相同的查询，只是添加了 EXPLAIN 和一些为了可读性而换行。

EXPLAIN 关键字将使 MySQL 向您解释它是如何运行查询的，并且它会告诉您索引是否正在使用。对于任何运行缓慢的查询，请始终使用 explain 来尝试弄清楚发生了什么。