mysql表性能升级(索引

Question

我正在尝试找到一种方法来提高包含 IP 范围的 mysql 表的性能(在高峰时段每秒最多有 500 个 SELECT 查询(!)，所以我有点担心)。

我有一个这种结构的表:

id  smallint(5) Auto Increment   
ip_start    char(16)     
ip_end  char(16)

编码为utf8_general_ci(整个表和除id之外的每一列)，表是MyISAM类型(仅SELECT查询，此处不需要插入/删除)。该表的索引是PRIMARY id。

此时表有近 2000 行。它们都包含 ip 范围。例如:

ip_start 128.6.230.0
ip_end 128.6.238.255

当用户访问某个网站时，我会检查他的 IP 是否在我表中的某些范围内。我使用这个查询(dibi sql 库):

 SELECT COUNT(*)
 FROM ip_ranges
 WHERE %s", $user_ip, " BETWEEN ip_start AND ip_end

如果查询结果不为零，则用户的 IP 位于表中的这些范围之一 - 这就是我需要它做的。

我在考虑是否可以在该表中添加一些索引？但我不太确定它是如何工作的，以及它是否是一个好主意(因为可能没有什么可以真正索引，对吧？大多数 IP 范围都是不同的)。

我在 ip_start 和 ip_end 列上也有 varchar 类型，但我将其切换为 char (猜猜它更快？)。

有人对如何进一步改进此表/查询有任何想法吗？

Answer 1

您不想使用聚合。相反，检查以下是否返回任何行:

 SELECT 1
 FROM ip_ranges
 WHERE %s", $user_ip, " BETWEEN ip_start AND ip_end
 LIMIT 1;

LIMIT 1 表示在第一个匹配处停止，因此速度更快。

对于此查询，您需要在 ip_ranges(ip_start, ip_end) 上建立索引。

当没有匹配时，这仍然存在性能问题。需要扫描被测ip之后的整个索引。我认为以下应该是一个改进:

SELECT COUNT(*)
FROM (SELECT i.start, ip_end
      FROM ip_ranges i
      WHERE %s", $user_ip, " >= ip_start
      ORDER BY ip_start
      LIMIT 1
     ) i
WHERE $user_ip <= ip_end;

内部子查询应该使用索引，但拉回第一个匹配项。然后，外部查询应该检查范围的末尾。这里的 count(*) 没问题，因为只有一行。