SQLにおけるIPアドレスの比較

出典について

この記事はThe High-Performance SQL Blog内のComparing IP Addresses in SQL(2015/06/24)を翻訳したものです。

イベントテーブルに操作を記録する際にユーザーのIPアドレスを保存するのは日常的な行為である。 今まで見てきた中では、大多数のケースではIPアドレスをvarcharで保存する。

この手法では、特定のIPアドレスに対してのクエリの発行は簡易となるが、データベースによってはIPアドレスの範囲でフィルタリングするのがきわめて難しくなる。 IPアドレスをvarcharとして比較すると誤った結果を返すことになる。

-- 100 is not less than 2, but is when compared as varchars!
select '100.1.1.1' < '2.1.1.1'
-- => true

IPアドレスの範囲でフィルタリングする為には、2つのIPアドレスを比較できる必要がある。 ひとたび比較が可能となれば、範囲で絞込みを行う際に気をつけることはほんのわずかとなる。 ここでPostgres、MySQL、SQL Server、そしてRedshiftでどのように扱うかを記載する。

Postgres

PostgresでIPアドレスを扱うのは非常に容易である。 列の型としてinetをサポートしており、IPアドレスと、IPの範囲に対するネイティブのサポートがある。 Postgresを使っていて、列の型としてinetを使っていなかったとしても、心配することはない。varcharをinetに変換することが可能であり、これにより簡単に比較が可能である。

select '192.168.1.1'::inet < '255.1.1.1'::inet

もしある範囲にある1つのIPが含まれることを確認したい場合は、<<演算子を使うと良い。

select '192.168.1.1'::inet << '192.168.0.0/16'::inet

MySQL

MySQLではIPアドレスに対するデータ型がないが、MySQLでIPを比較するのは容易である。 比較の前に、inet_atonを使うことでIPアドレスをintegerに変換することができる。

select inet_aton('192.168.1.1')
-- => 3,232,235,777 

IPv4アドレスは、32ビットの文字で表すには非常に都合が良い。IPv4の各オクテットは8ビットをあらわしている。 IPアドレスがintegerに変換されれば、比較するのはたやすい。

select inet_aton('192.168.1.1') < inet_aton('255.1.1.1')

SQL Server

SQL ServerではvarcharのIPをintegerに変換する組込みの関数は存在しないが、自作可能である! Dennis Goboのsqlblog.comで紹介されている関数を使うこととする。

create function dbo.IPAddressToInteger(@ip as varchar(15))
  returns bigint
as
  begin
    return (
      convert(bigint, parsename(@ip, 1)) +
      convert(bigint, parsename(@ip, 2)) * 256 +
      convert(bigint, parsename(@ip, 3)) * 65536 +
      convert(bigint, parsename(@ip, 4)) * 16777216
    )
  end
go

SQL Serverはsplit関数を持たないが、このユースケースに対してはparsename関数がとても役に立つ。 これは通常はdbo.table1.column1のようなオブジェクト識別子を抽出するのに利用される。 IPアドレスもドット区切りであるためIPアドレスをパースするのに利用する。

select dbo.IPAddressToInteger('192.168.1.1')
-- => 3,232,235,777

最終的に得られる32ビットの数値がIPアドレスを表すように、抽出されたオクテットの数値に大きな数をかけている。 この掛け算と足し算は、ビットシフト操作とバイナリでオクテットをOR操作を行い結合するのと同じであり、これはRedshiftの章でも実施している。

Redshift

SQL Serverと同じように、RedshiftもvarcharのIPをintegerのIPに変換するのにいくらかの操作が必要である。 varcharをドットで分割し、各オクテットをintegerにに変換する。 各オクテットを<<演算子で32ビット版のIPアドレスにビットシフトし、最終的に|演算子を利用しバイナリまたは4つのintegerを結合する。

select 
  split_part(user_ip, '.', 1)::integer << 24 |
  split_part(user_ip, '.', 2)::integer << 16 |
  split_part(user_ip, '.', 3)::integer << 8 |
  split_part(user_ip, '.', 4)::integer
from events

多くのvarcharのIPを分割しシフトするのは少し時間がかかる。 これを高速化するには、IPアドレスを比較する前に、CTE(共通テーブル式)または一時テーブルをこの変換結果を格納する為に使うと良いだろう。

しかし、もっと良い方法がある。すなわちintegerの変換はきわめて遅いのである。 varcharのIPをintegerに変換する代わりに、'0'のパディングの固まりをvarcharに加え、いかなる2つのvarcharのIPも比較可能にするのだ。

-- Octets with under 3 chars get 0-padded
select 
  lpad(split_part(user_ip, '.', 1), 3, '0') ||
  lpad(split_part(user_ip, '.', 2), 3, '0') ||
  lpad(split_part(user_ip, '.', 3), 3, '0') || 
  lpad(split_part(user_ip, '.', 4), 3, '0')
from events
-- '192.168.1.1' => 192168001001

結果の値は真のIPアドレスではないが、正確に比較を行うことができる。IPの範囲の境界も同じ形式に変換できることに注意したい。 おまけとしては、このvarcharの手法は、IPアドレスをintegerに変換する手法より15倍高速に動作する。

最速の比較

パフォーマンス観点での最善の選択肢は、IPアドレスをネイティブに比較できる形式で保存することである。 Postgresでは、inet型を使うことだ。その他に関してはintegerを推奨する。

そしてクエリをシンプルにするには、IPアドレスを2度保存することだ。 すなわち、1つはvarcharとして、そしてもう1つはintegerとして保存し、クエリで一番便利な方を使うことである。