mysql - 为什么在第一种情况下不使用索引，而在另一种情况下使用索引？

Question

我想验证我的假设是否正确。我有两个表，它们只是索引顺序不同。

它们看起来像这样:

CREATE TABLE `ipcountry` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `ipFROM` INT(10) UNSIGNED ZEROFILL NOT NULL DEFAULT '0000000000',
    `ipTO` INT(10) UNSIGNED ZEROFILL NOT NULL DEFAULT '0000000000',
    `countrySHORT` CHAR(2) NOT NULL DEFAULT '' COLLATE 'utf8_czech_ci',
    `countryLONG` VARCHAR(255) NOT NULL DEFAULT ' ' COLLATE 'utf8_czech_ci',
    PRIMARY KEY (`id`),
    INDEX `ipINDEX` (`ipTO`, `ipFROM`)
)
COLLATE='utf8_czech_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2490331
;


CREATE TABLE `ipcountry2` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `ipFROM` INT(10) UNSIGNED ZEROFILL NOT NULL DEFAULT '0000000000',
    `ipTO` INT(10) UNSIGNED ZEROFILL NOT NULL DEFAULT '0000000000',
    `countrySHORT` CHAR(2) NOT NULL DEFAULT '' COLLATE 'utf8_czech_ci',
    `countryLONG` VARCHAR(255) NOT NULL DEFAULT ' ' COLLATE 'utf8_czech_ci',
    PRIMARY KEY (`id`),
    INDEX `ipINDEX` (`ipFROM`, `ipTO`)
)
COLLATE='utf8_czech_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2490331
;

两个表的行数完全相同，大约为 2,500,000。

执行时 EXPLAIN SELECT * FROM `ipcountry` WHERE ipFROM<=3548978221 AND ipTO>=3548978221我明白了

{
    "table": "UnknownTable",
    "rows":
    [
        {
            "id": 1,
            "select_type": "SIMPLE",
            "table": "ipcountry",
            "partitions": null,
            "type": "range",
            "possible_keys": "ipINDEX",
            "key": "ipINDEX",
            "key_len": "4",
            "ref": null,
            "rows": 83260,
            "filtered": 33.33,
            "Extra": "Using index condition"
        }
    ]
}

执行时 EXPLAIN SELECT * FROM `ipcountry2` WHERE ipFROM<=3548978221 AND ipTO>=3548978221我明白了

{
    "table": "UnknownTable",
    "rows":
    [
        {
            "id": 1,
            "select_type": "SIMPLE",
            "table": "ipcountry2",
            "partitions": null,
            "type": "ALL",
            "possible_keys": "ipINDEX",
            "key": null,
            "key_len": null,
            "ref": null,
            "rows": 2515343,
            "filtered": 16.66,
            "Extra": "Using where"
        }
    ]
}

是不是因为运算符的优先级？

Answer 1

在第一个EXPLAIN中注意:

        "key_len": "4",

这表明只有查询只读取索引中的第一个 INT(4 个字节)进行查找。您可以看到此查找将搜索范围从 250 万缩小到大约 83K，选择性约为 30:1。

        "rows": 83260,

当您在查询中有两个范围条件时，MySQL 不能将索引的两个列都用于 B 树搜索。它可以对第一列进行 B 树搜索，但索引的后续列不能用于该搜索。

您的查询还通过存储引擎级别的其他列过滤 index condition pushdown , 由额外注释表示:

        "Extra": "Using index condition"

这不是 B 树搜索的一部分，但它可以在行从存储引擎返回到 SQL 层之前过滤掉行，从而起到一点帮助。

底线是无法使用 B 树索引搜索来优化同一表中不同列上的两个范围条件。

如果 MySQL 估计读取整个表的成本与使用索引的成本大致相同，那么它也会完全跳过使用索引。符合您的条件的行越多，这种可能性就越大。 InnoDB 通过二级索引读取行是额外的工作，因此如果它估计您的索引查找将匹配大量行，则默认进行表扫描。发生这种情况的阈值不是官方的或记录的，但我观察到当您的条件与表中至少 20% 的行匹配时会发生这种情况。

在您的第二个表中，鉴于它也只能过滤第一列，我们可以推断仅 ipFROM 上的条件将匹配您表中的大部分行。您正在搜索小于 3548978221 或 211.137.28.45 的所有 IP 地址，这在 IPv4 地址范围内相当高。毫不奇怪，至少 20% 的行的值小于该数字。

因此 MySQL 优化器得出结论，在第二个查询中，它不会为使用索引提供足够的好处，因此它决定进行表扫描。它不能在不使用第一列的情况下使用索引的第二列。