如何使 MySQL 在整數範圍選擇查詢中使用索引

我正在表上進行選擇以查找在上限和下限之間匹配的行

SELECT * FROM ranges WHERE lolong <= 2091484391 AND hilong >= 2091484391 ;

（簡化）創建表是：

CREATE TABLE `ranges` (
 `id` bigint(10) unsigned NOT NULL AUTO_INCREMENT,
 `hi` varchar(15) DEFAULT NULL,
 `hilong` bigint(20) DEFAULT NULL,
 `lo` varchar(15) DEFAULT NULL,
 `lolong` bigint(20) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `hilong` (`hilong`),
 KEY `lolong` (`lolong`)
) ENGINE=InnoDB AUTO_INCREMENT=234447 DEFAULT CHARSET=utf8

解釋表明它沒有使用索引：

mysql> explain SELECT * FROM ranges WHERE lolong <= 2091484391 AND hilong >= 2091484391 ;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | ranges | NULL       | ALL  | hilong,lolong | NULL | NULL    | NULL | 7232 |    24.83 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+-------------+

即使我試圖強制它，它也只使用一個索引

mysql> explain SELECT * FROM ranges force index (hilong,lolong) WHERE lolong <= 2091484391 AND hilong >= 2091484391 ;
+----+-------------+------------+------------+-------+---------------+--------+---------+------+------+----------+------------------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key    | key_len | ref  | rows | filtered | Extra                              |
+----+-------------+------------+------------+-------+---------------+--------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | ranges | NULL       | range | hilong,lolong | hilong | 9       | NULL | 2757 |    65.14 | Using index condition; Using where |
+----+-------------+------------+------------+-------+---------------+--------+---------+------+------+----------+------------------------------------+

這似乎不對，我怎樣才能讓 MySQL 使用這兩個索引，它似乎是不必要地掃描行來尋找這個簡單的查詢

每當您使用AND子句並使用 equals = 選擇精確的字元串/整數類型時。使用複合索引是有效的。

嘗試擁有*composite index on hilong and lolong*. 查看查詢是否選擇索引，因為您在自己的範圍內有兩個不同的列我不確定它是否有幫助。

這主要取決於如何定義 app-db 架構。如果我是你。

• 查看這是在事務路徑還是分析路徑中？
• 看看您是否可以辨識任何其他具有 PK/更高基數的 AND 條件會減少列的範圍掃描。
• 如果表增長有限或很高，如果最終使用者接受它的有限和具有數據保留的延遲，您可以將邏輯保留在 Mysql 中。如果不適用於其他數據庫儲存以滿足其在 ElasticSearch/Cassandra 或任何具有保留的分佈式系統等中的要求。

在兩列之間進行檢查基本上是不可優化的。優化器不知道將匹配多少對，所以它一直持續到表的末尾。

通過只有一列，並做很多額外的工作，就有可能解決它。 使用 IP 地址範圍進行操作。

引用自：https://dba.stackexchange.com/questions/171792