非常简单的问题却很难找到解决方案。 具有 2,498,739 行的地址表具有 min_ip 和 max_ip 字段的字段。这些是过滤表的核心 anchor 。

查询非常简单。

SELECT * 
FROM address a 
WHERE min_ip < value
  AND max_ip > value;

因此为 min_ip 和 max_ip 创建索引以加快查询速度是合乎逻辑的。

为以下内容创建的索引。

CREATE INDEX ip_range ON address (min_ip, max_ip) USING BTREE;
CREATE INDEX min_ip ON address (min_ip ASC) USING BTREE;
CREATE INDEX max_ip ON address (max_ip DESC) USING BTREE;

我确实尝试只创建第一个选项(min_ip 和 max_ip 的组合)但是它没有用，所以我准备了至少 3 个索引来为 MySQL 提供更多索引选择选项。 (请注意，此表几乎是静态的，更像是一个查找表)

+------------------------+---------------------+------+-----+---------------------+-----------------------------+
| Field                  | Type                | Null | Key | Default             | Extra                       |
+------------------------+---------------------+------+-----+---------------------+-----------------------------+
| id                     | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment              |
| network                | varchar(20)         | YES  |     | NULL                |                             |
| min_ip                 | int(11) unsigned    | NO   | MUL | NULL                |                             |
| max_ip                 | int(11) unsigned    | NO   | MUL | NULL                |                             |
+------------------------+---------------------+------+-----+---------------------+-----------------------------+

现在，以min_ip和max_ip为过滤条件查询表应该是直截了当的。

EXPLAIN
SELECT * 
FROM address a 
WHERE min_ip < 2410508496
  AND max_ip > 2410508496;

查询执行了大约 0.120 到 0.200 秒。但是，在负载测试中，查询会迅速降低性能。 MySQL 服务器 CPU 使用率飙升至 100% CPU 使用率仅在几个并发查询上，性能迅速下降并且无法扩展。 mysql 服务器上的慢速查询以 10 秒或更长时间打开，最终选择查询在几秒钟的负载测试后显示在日志中。 所以我用解释检查了查询，发现它没有使用索引。

解释计划结果

    id  select_type  table   type    possible_keys           key     key_len  ref        rows  Extra        
------  -----------  ------  ------  ----------------------  ------  -------  ------  -------  -------------
     1  SIMPLE       a       ALL     ip_range,min_ip,max_ip  (NULL)  (NULL)   (NULL)  2417789  Using where  

有趣的是，它能够将 ip_range、ip_min 和 ip_max 确定为潜在索引，但从不使用其中的任何一个，如键列中所示。 我知道我可以使用 FORCE INDEX 并尝试在其上使用解释计划。

EXPLAIN
SELECT * 
FROM address a 
FORCE INDEX (ip_range)
WHERE min_ip < 2410508496
  AND max_ip > 2410508496;

用 FORCE INDEX 结果解释计划

    id  select_type  table   type    possible_keys  key       key_len  ref        rows  Extra                  
------  -----------  ------  ------  -------------  --------  -------  ------  -------  -----------------------
     1  SIMPLE       a       range   ip_range       ip_range  4        (NULL)  1208894  Using index condition  

对于 FORCE INDEX，是的，它使用 ip_range 索引作为键，行显示查询中不使用 FORCE INDEX 的子集，即 2,417,789 中的 1,208,894。 所以肯定的是，使用索引应该有更好的性能。 (除非我误解了解释结果)

但更有趣的是，经过几次测试，我发现在某些情况下，即使没有 FORCE INDEX，MySQL 也会使用索引。我的观察是当值很小的时候，它确实使用了索引。

EXPLAIN
SELECT * 
FROM address a 
WHERE min_ip < 508496
  AND max_ip > 508496;

解释结果

    id  select_type  table   type    possible_keys           key       key_len  ref       rows  Extra                  
------  -----------  ------  ------  ----------------------  --------  -------  ------  ------  -----------------------
     1  SIMPLE       a       range   ip_range,min_ip,max_ip  ip_range  4        (NULL)       1  Using index condition  

所以，让我感到困惑的是，基于传递给选择查询的值，MySQL 决定何时使用索引，何时不使用索引。 我无法想象确定何时对传递给查询的特定值使用索引的基础是什么。我明白 如果 WHERE 条件下没有合适的匹配索引，则可以不使用索引，但在这种情况下，很明显 ip_range 索引是哪个 是基于 min_ip 和 max_ip 列的索引适用于这种情况下的 WHERE 条件。

但我遇到的更大问题是，其他查询呢？我是否必须大规模地测试这些查询？ 但即便如此，随着数据的增长，我是否可以依赖并期望 MySQL 使用索引？ 是的，我总是可以使用 FORCE INDEX 来确保它使用索引。但这不是适用于所有数据库的标准 SQL。 ORM 框架在生成 SQL 时可能无法支持 FORCE INDEX 语法，并且它会将您的查询与索引名称紧密耦合。

不确定是否有人遇到过这个问题，但这对我来说似乎是一个非常大的问题。

最佳答案

完全同意 Vatev 和其他人的观点。不仅 MySQL 这样做。扫描表有时比先查看索引然后在磁盘上查找相应的条目更便宜。

唯一肯定会使用索引的情况是，当它是覆盖索引时，这意味着查询中的每一列(当然对于这个特定的表)都存在于索引中。意思是，如果您只需要例如网络列

SELECT network
FROM address a 
WHERE min_ip < 2410508496
  AND max_ip > 2410508496;

然后是覆盖索引，例如

CREATE INDEX ip_range ON address (min_ip, max_ip, network) USING BTREE;

只会查看索引，因为根本不需要在磁盘上查找其他数据。并且整个索引可以保存在内存中。

关于MySQL并不总是使用索引，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/33911589/