Yakstは、海外の役立つブログ記事などを人力で翻訳して公開するプロジェクトです。
4年以上前投稿 修正あり

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

PostgreSQL, MySQL, SQL Server, Redshift上でIPアドレスを扱う際にどのようにすれば良いか、についてのまとめ

原文
Comparing IP Addresses in SQL (English)
翻訳依頼者
B5aa4f809000b9147289650532e83932
翻訳者
B5aa4f809000b9147289650532e83932 taka-h
原著者への翻訳報告
未報告


出典について

この記事は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として保存し、クエリで一番便利な方を使うことである。

次の記事
Engineering at Monsanto: AWS上でのetcdのクラスタリング
前の記事
MySQL 5.7におけるCREATE USERコマンドの改善(MySQL Server Blogより)

Feed small 記事フィード

新着記事Twitterアカウント

新着翻訳リクエストTwitterアカウント