「性別」列のインデックス付与について

2012 年 5 月 14 日 by yasukuni

データベースのインデックスを張る際によく「性別」に付けても意味が無いと言われたりしますが、実際の所どうなのでしょうか？(SQL Serverで)試してみました。

で。結論から言うと遅くなります。(というか使用されません) ただし…

1.サンプルデータ作成

まず、データを準備します。100万件のサンプルデータを用意し、”sex”カラムを性別と見立て(0=男性, 1=女性)ランダムに設定します。

/*テーブル作成*/
CREATE TABLE users(
id       INT IDENTITY(1,1) NOT NULL
,name    NCHAR(20)         NULL
,sex     TINYINT           NULL
,age     TINYINT           NULL
,address NCHAR(50)         NULL
,tel     NCHAR(15)         NULL
,CONSTRAINT PK_USERS PRIMARY KEY CLUSTERED (id ASC)
)

/*ダミーデータ作成*/
DECLARE @CNT int
SET @CNT = 0

WHILE @CNT &lt; 1000000
BEGIN
INSERT INTO users(name, sex, age, address, tel) VALUES(
'スズキ イチロウ' + CAST(@CNT AS VARCHAR)
,FLOOR(RAND(CONVERT(INT, CONVERT(VARBINARY(4), NEWID()))) * 2)
,FLOOR(RAND(CONVERT(INT, CONVERT(VARBINARY(4), NEWID()))) * 80)
,'東京都千代田区1-1-1'
,'03-1234-5678'
)
SET @CNT = @CNT + 1
END

/*性別の分布を確認*/
SELECT COUNT(*) FROM users
SELECT sex, COUNT(*) FROM users GROUP BY sex

サンプルデータ - 性別分布

2.インデックス付与前

それでは、データの準備が出来たので、インデックスなしの状態でクエリを流してみます。
[SQL]
DBCC DROPCLEANBUFFERS /*clear cache*/
DBCC FREEPROCCACHE /*clear cache*/
SELECT * FROM users WHERE sex = 0
[/SQL]
[インデックス付与前 – 実行計画]
インデックス付与前 - 実行計画

[インデックス付与前 – 実行時間]
インデックス付与前 - 実行時間

3.インデックス付与後

次にインデックスを付与した状態でクエリを流します。
(何か切ないですが、そのままだとインデックスは使用されないのでテーブルヒントを与えます。。。)

/*インデックスの作成*/
CREATE INDEX IDX_USERS_SEX ON users(sex)

DBCC DROPCLEANBUFFERS  /*clear cache*/
DBCC FREEPROCCACHE     /*clear cache*/
SELECT * FROM users WITH (INDEX = IDX_USERS_SEX) WHERE sex = 0

[インデックス付与後 – 実行計画]
インデックス付与後 - 実行計画

[インデックス付与後 – 実行時間]
インデックス付与後 - 実行時間

4.理由

ここまで、茶番劇のようですが確かに遅くなりました。なぜでしょうか？
よくインデックスは書籍の索引に例えられますが今回作成したインデックスを例えるとこうなるはずです。

索引 – 性別
男性・・・1, 3, 6, 10
女性・・・2, 4, 5, 7, 8, 9

もし、人がこの本の男性を探すというのであれば、100万ページをめくる作業が約1/2に絞り込めるのでこの索引は非常に有用だと思います。しかしRDBMSでは効率的にデータを読み込む為に数ページをまとめてディスクから読み込みます。（まとめて読み込むサイズは「ブロックサイズ」「ページサイズ」などと呼ばれます）

本で例えると1,3,6,10のページをめくる為には必然的に（ブロックサイズを5ページと仮定）1～10ページをめくっているのと変わらないことになります。そこでRDBMSはこう考えます「結局全部めくるんなら、索引とか開くの面倒だからインデックス使うのや～めた」と。

5.本のページをめくる必要がない場合

ここまでで、「性別」にインデックスを付与しても意味がない(使われない。使っても+αの処理が発生するため遅くなる)の理由ですが例外があります。それは、本のページをめくる必要がない場合です。

例えば、件数を取得するだけなら人もRDBMSも同じで索引の男性に書かれているページ番号を数えるだけで事足ります。仮にインデックスがない場合(全データ走査)と比較すると100万件で約25倍の差が出ました。

/*男性の件数を取得*/
SELECT COUNT(*) FROM users WHERE sex = 0

[インデックス付与前後 – 件数取得(左:付与後, 右:付与前)]
インデックス付与前後 - 件数取得

6.本のページをめくらせない

結局、本のページをめくらなければ(データへのアクセスがなく、インデックスで事足りる場合)「性別」のインデックスは有用だということが解りました。だたし、性別毎の平均年齢を調べようものなら、やはり全てのページをめくる必要があります。(「4.理由」の通りインデックスは使われない)

そこで、ページをめくらないようにする方法にカバーリングインデックスと呼ばれるSELECTやWHEREに含まれるフィールドを全て網羅(カバー)するものがあります。「インデックスに年齢も含めてしまえば事足りるよね」の考えで本の索引で例えるとこのようなものです。

索引 – 性別(年齢情報付き)
男性・・・1(15), 3(23), 6(9), 10(45)
女性・・・2(7), 4(35), 5(16), 7(63), 8(27), 9(30)

インデックスが大きくなった分I/Oが増えた為、先ほど同様25倍と言えませんが、インデックスがない場合と比べて10倍以上高速です。(サンプルデータはフィールド数が少ないので…実際はもう少し有益なはずです。)
[SQL]
/*カバーリングインデックス*/
CREATE INDEX IDX_USERS_SEX_WITH_AGE ON users(sex) INCLUDE(age)
SELECT sex, AVG(age) FROM users GROUP BY sex
[/SQL]
[カバーインデックス付与前後 – 性別毎平均年齢(左:付与後, 右:付与前)]
カバーリングインデックス付与前後

ということで、「性別」のインデックスは一概に意味がないとは言えませんが、インデックスを付与するとデータサイズの肥大化や更新処理の速度低下を招くため用途に応じた適切なインデックスが必要です。

また、今回一般的なB-Treeインデックスについて記載しましたが、ガーディナリティが低い(性別、各種フラグ、カテゴリの様にデータの種類が少ない)フィールドには「ビットマップインデックス」という種類のインデックスが効果的な様です。

タグ: インデックス, データベース

トラックバックURL:

FreeStyleVision