MARIADB: 범위에 조인된 선택 항목에는 인덱스가 사용되지 않습니다.

MARIADB: 범위에 조인된 선택 항목에는 인덱스가 사용되지 않습니다.

첫 번째 테이블에는 ips가 정수(500k 행)로 저장되어 있습니다.두 번째 테이블에는 블랙리스트의 ips 범위와 블랙리스트의 이유(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을 사용하고 있습니다.

진실의.

옵티마이저는 시작하는 지식이 없습니다.엔드 값은 중복되지 않으며, 그 외의 명확한 값은 없습니다.그래서 할 수 있는 최선의 선택은

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

어느 쪽이든 테이블이 절반 이상 스캔될 수 있습니다.

2단계로 1개의 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

그러면 다음 값이 반환됩니다.reason또는 행이 없습니다.

복잡함에도 불구하고 매우 빠를 것입니다.하지만 확인해야 할 IP 세트가 있는 것 같습니다.그래서 더 복잡해지죠.

위해서black_lists, 는 필요없을 것 같습니다.id. 4개의 인덱스를 2개만 교환할 것을 권장합니다.

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

인ips안 그래?ip독특하다?만약 그렇다면, 만약id5개의 인덱스를 3으로 변경합니다.

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

이미 충분히 허용했습니다.VARCHARIPv6 의 경우는 있습니다만, 에는 없습니다.INT UNSIGNED.

더 많은 토의.

언급URL : https://stackoverflow.com/questions/38380493/mariadb-index-not-used-for-a-select-with-join-on-a-range