mysql - MARIADB:索引不用于选择范围内的连接

Question

我有第一个表包含我的 ips 存储为整数(500k 行)，第二个表包含黑名单 ip 的范围和黑名单的原因(10M 行)这是表结构:

    CREATE TABLE `black_lists` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `ip_start` INT(11) UNSIGNED NOT NULL,
    `ip_end` INT(11) UNSIGNED NULL DEFAULT NULL,
    `reason` VARCHAR(3) NOT NULL,
    `excluded` TINYINT(1) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `ip_range` (`ip_end`, `ip_start`),
    INDEX `ip_start` ( `ip_start`),
    INDEX `ip_end` (`ip_end`),
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=10747741
;

CREATE TABLE `ips` (
    `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'Id ips',
    `idhost` INT(11) NOT NULL COMMENT 'Id Host',
    `ip` VARCHAR(45) NULL DEFAULT NULL COMMENT 'Ip',
    `ipint` INT(11) UNSIGNED NULL DEFAULT NULL COMMENT 'Int ip',
    `type` VARCHAR(45) NULL DEFAULT NULL COMMENT 'Type',
    PRIMARY KEY (`id`),
    INDEX `host` (`idhost`),
    INDEX `index3` (`ip`),
    INDEX `index4` (`idhost`, `ip`),
    INDEX `ipsin` (`ipint`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=675651;

我的问题是当我尝试运行这个查询时没有使用索引并且需要很长时间才能完成:

select i.ip,s1.reason
from ips i
    left join black_lists s1 on i.ipint BETWEEN s1.ip_start and s1.ip_end;

我正在使用 MariaDB 10.0.16

Answer 1

是的。

优化器不知道 start..end 值是不重叠的，也不知道它们有任何其他明显的信息。所以，它能做的最好的事情就是在

s1.ip_start <= i.ipint  -- and use INDEX(ip_start), or
s1.ip_end   >= i.ipint  -- and use INDEX(ip_end)

其中任何一个都可能导致超过一半的表格被扫描。

在 2 个步骤中，您可以实现一个 ip 的预期目标；让我们说@ip:

SELECT ip_start, reason
    FROM black_lists
    WHERE ip_start <= @ip
    ORDER BY ip_start DESC
    LIMIT 1

但在那之后，你需要看那个ip_start对应的ip_end是否为<=@ip，才能判断你是否有黑名单。

SELECT reason
    FROM ( ... ) a   -- fill in the above query
    JOIN black_lists b  USING(ip_start)
    WHERE b.ip_end <= @ip

这将返回 原因 或不返回任何行。

尽管很复杂，但速度会非常快。但是，您似乎有一组 IP 需要检查。这使它变得更加复杂。

对于black_lists，似乎不需要id。建议你只用 2 个替换 4 个索引:

PRIMARY KEY(ip_start, ip_end),
INDEX(ip_end)

在ips中，ip不是唯一的吗？如果是这样，去掉 if id 并将 5 个索引更改为 3:

PRIMARY KEY(idint),
INDEX(host, ip),
INDEX(ip)

您在 VARCHAR 中为 IPv6 允许了足够多的内容，但在 INT UNSIGNED 中却没有。

More discussion .