ipv4 범위 필터링 시 SQL 성능

ipv4 범위 필터링 시 SQL 성능

Ipv4 범위가 32bit int로 표시된 데이터베이스(InnoDB)가 있으며, 둘 다 부호가 없는 int이며 특정 IP 범위(start-ip to end-ip)를 다른 열에 있는 데이터로 나타냅니다.두 열 모두 개별적으로 색인을 작성했습니다(Btree 색인 유형)

id|start-ip|end-ip
 4|16777216|16777471
 5|16777472|16778239
 6|16778240|16779263
 7|16779264|16781311
 8|16781312|16785407
 9|16785408|16793599
10|16793600|16809983
11|16809984|16810018
12|16810019|16810019

특정 IP를 선택할 때 한 행 안에 들어감에 따라 쉽게 찾을 수 있습니다.하지만 여러 행에 맞는 ips의 범위를 찾아야 할 때, 제가 생각한 유일한 방법은

select * from `ips` where 
(`start-ip` <= min and `end-ip` >= min) 
or 
(`start-ip` >= min and `end-ip` <= max) 
or 
(`start-ip` <= max and `end-ip` >= max)

예를 들어, 16777300(min) - 16779200(max) 범위에서 출력을 선택하려고 하면 다음과 같습니다.

 4|16777216|16777471
 5|16777472|16778239
 6|16778240|16779263

기본적으로 start-ip은 'min'보다 작거나 같아야 하며 end-ip은 'max'보다 크거나 같아야 합니다.이것은 여러 행에 걸쳐 있을 수 있습니다.

현재 ipv4의 전체 스펙트럼에 대해 ip 범위가 있는 약 1,000만 개의 행이 있으며 범위 내에 맞는 모든 행을 선택합니다. 그러나 성능 측면에서 데이터를 추출하는 데는 몇 초가 걸립니다.어떻게 하면 성능을 향상시킬 수 있습니까?

다음과 같은 논리를 사용할 수 있습니다.

select *
from `ips`
where min <= `end-ip` and
      max >= `start-ip`

즉, 한 범위는 다른 범위가 끝나기 전에 시작되고, 그 반대도 마찬가지입니다.

MySQL/MariaDB는 사용자의 범위가 중복되는지 여부를 알지 못합니다.고든을 포함한 어떤 명백한 공식보다도 성능이 형편없죠

다음은 성능을 발휘하는 접근 방식입니다. http://mysql.rjweb.org/doc.php/ipranges

언급URL : https://stackoverflow.com/questions/63624442/sql-performance-when-filtering-ipv4-ranges