MySQL BETWEEN 쿼리가 인덱스를 사용하지 않음

MySQL BETWEEN 쿼리가 인덱스를 사용하지 않음

테이블에 지리정보가 있는데network_start_ip그리고.network_end_ip이다varbinary(16)결과가 있는 컬럼INET6_ATON(ip_start/end)가치관으로서.다른 두 열은 위도와 경도입니다.

CREATE TABLE `ipblocks` (
 `network_start_ip` varbinary(16) NOT NULL,
 `network_last_ip` varbinary(16) NOT NULL,
 `latitude` double NOT NULL,
 `longitude` double NOT NULL,
 KEY `network_start_ip` (`network_start_ip`),
 KEY `network_last_ip` (`network_last_ip`),
 KEY `idx_range` (`network_start_ip`,`network_last_ip`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

보시는 바와 같이 테스트용 인덱스를 3개 만들었습니다.(quit simple) 쿼리는 왜

SELECT 
    latitude, longitude
FROM
    ipblocks b
WHERE
    INET6_ATON('82.207.219.33') BETWEEN b.network_start_ip AND b.network_last_ip

이 인덱스를 사용하지 않는가?

쿼리는 프로덕션에서 사용하기에는 너무 긴 최대 3초 정도 걸립니다.

2개의 컬럼이 참조되어 있기 때문에 동작하지 않습니다.이것은 최적화가 매우 어렵습니다.중복되는 IP 범위가 없다고 가정하면 다음과 같이 쿼리를 재구성할 수 있습니다.

SELECT b.*
FROM (SELECT b.*
      FROM ipblocks b
      WHERE b.network_start_ip <= INET6_ATON('82.207.219.33')
      ORDER BY b.network_start_ip DESC
      LIMIT 1
     ) b
WHERE INET6_ATON('82.207.219.33') <= network_last_ip;

내부 쿼리는 다음에 인덱스를 사용해야 합니다.ipblocks(network_start_ip)외부 쿼리는 한 행만 비교하므로 인덱스가 필요하지 않습니다.

또는 다음과 같이 합니다.

SELECT b.*
FROM (SELECT b.*
      FROM ipblocks b
      WHERE b.network_last_ip >= INET6_ATON('82.207.219.33')
      ORDER BY b.network_end_ip ASC
      LIMIT 1
     ) b
WHERE network_last_ip <= INET6_ATON('82.207.219.33');

이것은 다음 항목에 대한 인덱스를 사용합니다.(network_last_ip)MySQL(및 MariaDB)은 내림차순 정렬보다 오름차순 정렬을 더 잘합니다.

Gordon Linoff 덕분에 나는 내 질문에 대한 최적의 질문을 찾았다.

SELECT b.* FROM 
  (SELECT b.* FROM ipblocks b WHERE b.network_start_ip <= INET6_ATON('82.207.219.33') 
                              ORDER BY b.network_start_ip DESC LIMIT 1 ) 
b WHERE INET6_ATON('82.207.219.33') <= network_last_ip

이제 다음보다 작은 블록을 선택합니다.INET6_ATON(82.207.219.33)내림차순을 정해서 우리가 사용할 수 있게 합니다.LIMIT 1다시.

현재 쿼리 응답 시간은 .002 ~ .004초입니다좋았어!

이 쿼리는 올바른 결과를 제공합니까?정수 표현을 검색하는 동안 시작/끝 IP가 이진 문자열로 저장되는 것 같습니다.먼저 network_start_ip 및 network_last_ip이 IP 주소를 정수화한 부호 없는 INT 필드인지 확인합니다.이것은, IPv4 만을 사용하고 있는 것을 전제로 하고 있습니다.

CREATE TABLE ipblocks_int AS
SELECT
    INET_ATON(network_start_ip) as network_start_ip,
    INET_ATON(network_last_ip) as network_last_ip,
    latitude,
    longitude
FROM ipblocks

다음으로 (network_start_ip, network_last_ip)를 프라이머리 키로 사용합니다.

어려운 문제입니다.간단한 해결책은 없습니다.

그것이 힘든 이유는 그것이 효과적이기 때문이다.

   start <= 123  AND
   last  >= 123

사용 가능한 인덱스에 관계없이 Optimizer는 이러한 인덱스와 함께 작동합니다.와 함께INDEX(start, ...), 그것은 선택될 것이다.start <= 123인덱스의 첫 번째 부분을 스캔합니다.다른 절도 마찬가지입니다.이 중 하나는 인덱스의 절반 이상을 스캔하고 다른 하나는 더 적게 스캔하지만 인덱스를 사용할 가치가 있을 만큼 충분히 스캔하지는 않습니다.로의 이동PRIMARY KEY어떤 경우에는 도움이 되지만, 노력할 가치가 거의 없습니다.

요컨대, 무엇을 하든INDEX또는PRIMARY KEY대부분의 IP 상수는 쿼리에 1.5초 이상 걸립니다.

"/" IP "/" IP "/" IP "/"?렇면의 '고든', '고든', '고든', '고든', '고든', '고든', '고든', '고든', '고든', '고든', '고든', '고든', '고든', '고든'이 무효가 될 수 있습니다.LIMIT 1.

이 솔루션에는 중복되지 않는 지역이 포함되어 있습니다.IP 의 갭은, 「소유되지 않은」IP 의 범위를 필요로 합니다.이는 start_ip만 존재하기 때문입니다.last_ip은 테이블 내의 다음 항목의 시작보다 작음을 암시합니다.http://mysql.rjweb.org/doc.php/ipranges 를 참조해 주세요(IPv4 및 IPv6 의 코드가 포함되어 있습니다).

한 meanwhile.DOUBLElat/lng은 과잉입니다.http://mysql.rjweb.org/doc.php/latlng#representation_choices

언급URL : https://stackoverflow.com/questions/62738076/mysql-between-query-not-using-index