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

查询非常简单.

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;

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