人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

MySQLのインデックスについて、
質問させてください。

id1 int,
id2 int,
data1 int,
data2 int,

上記のようなカラムを持つテーブルがあり、
id1×id2においてユニークである場合

A id1,id2に複合でprimaryをはり、id2に別途indexをはる

B id1、id2にそれぞれindexをはる(primaryを設定しない)

上記のようなAとBのケースにおいて、
8000万レコード程度ある場合、
インデックスのサイズが、
下記のようにBの方が2倍以上大きくなっています。

A 1.7G
B 4.9G

この原因がわかる方いらっしゃいましたら、
教えていただけます。

またAとBではパフォーマンスにも、
大きく影響を与えるのでしょうか。

よろしくお願いします。

●質問者: the_yakisoba
●カテゴリ:コンピュータ インターネット
✍キーワード:INDEX MySQL インデックス カラム サイズ
○ 状態 :終了
└ 回答数 : 4/4件

▽最新の回答へ

1 ● chuken_kenkou
●23ポイント

MySQLのバージョンは、何でしょうか?

ストレージエンジン(MyISAMとか、InnoDBとか)は、何でしょうか?


インデックスのサイズが、下記のようにBの方が2倍以上大きくなっています。


MyISAMなのか、InnDBなのか等によって、内部のデータ形式が変わってきます。

B-TREEのインデクスの1エントリは、「キー値+行の格納アドレス+付加される管理情報」といったものになります。

1ページ内に何エントリ格納できるかは、ページ長とエントリ長によって違ってきます。

複数列でインデクスを構成した場合、キー値部分は長くなりますが、行の格納アドレスや付加される管理情報は変わりません。

例えば、1ページで100エントリを管理可能とします。

最下段のインデクスページ数

=800万件/100

=80000

下から2段目のインデクスページ数

=80000/100

=800

下から3段目のインデクスページ数

=800/100

=8

最上位のインデクスページ数=1

となり、

1+8+800+80000

=808081ページ必要となります。

一方、それぞれ個別の単一列インデクスした場合は、それぞれのキー値に、行格納アドレスや管理情報が付きます。

例えば、1ページで120エントリ管理できたとすれば、

最下段のインデクスページ数

=800万件/120

=66666

下から2段目のインデクスページ数

=66666/120

=555

下から3段目のインデクスページ数

=555/120

=4

最上位のインデクスページ数=1

となり、

1+4+555+66666

=67226ページ必要となります。

これが1個のインデクス分なので、2個のインデクスであれば、これらのページ群がもう一つ作成されることになります。


またAとBではパフォーマンスにも、大きく影響を与えるのでしょうか。


どういった検索条件、ORDER BY、GROUP BY、DISTINCTなどを使用するかによって、話がまったく異なってきます。

◎質問者からの返答

ご回答ありがとうございます。

ひとまず、

MySQLは5.0、

ストレージはInnoDBになります。

詳しく回答を読ませていただいてから、

サイド質問させていただくかもしれませんので、

よろしくお願いします。


2 ● kn1967
●23ポイント

ソースから紐解いたわけではないので

アバウトな話になりますがよろしいでしょうか?

(MySQLのバージョンと使用するDBは書いておいたほうがよろしいかと・・・)


とりあえず、仮に下記のようなデータが存在するとします。

レコード id1 id2
a 1 1
b 1 2
c 2 1
d 2 2

primary keyということは

id1 と id2 がまったく同じレコードは存在しない(重複レコードが無い)

という事を指しますので

上の例では、レコードbの前ならレコードa、後ならレコードcで済みます。


それぞれにindexをつけただけという事になれば

id1 が同じレコードが複数存在していたり

id2 が同じレコードが複数存在していたり

という事になっているはず

(8000万行もあれば複数あるでしょうと推測してます)なので

上の例では、レコードbの前ならレコードaだけのパターンですが

後ならレコードcになる場合とレコードdになる場合がありますので

次にくる可能性は(この例では)2倍になってます。

(インデックスのサイズ比からすると

id1のほうが重複するレコードが多いということかな?)


パフォーマンスについては

id1 だけで検索する際にBのほうが速く結果を出してくれそうですが

実際に運用するにあたって、一度に大量の結果を返すような処理は

そうそう無いと思いますので、Aで良いのではないかと思います。


3 ● b-wind
●22ポイント

MySQL :: MySQL 5.1 リファレンスマニュアル :: 13.5.13 InnoDB テーブルとインデックス構造

正確な情報を把握しているわけではないが、以下の3点が関係していると思われる。

全ての InnoDB テーブルは、行のデータが格納されている clustered index と呼ばれる特別なインデックスを持っています。もし PRIMARY KEY をテーブル上で定義したら、主キーのインデックスは集合インデックスになります。

もしテーブルに PRIMARY KEY を定義しなければ、MySQL は主キーとして NOT NULL カラムだけを持つ最初の UNIQUE インデックスを選択し、InnoDB がそれを集合インデックスとして利用します。

文中で「clustered index」と「集合インデックス」という2種の用語が使われているが、原文を読むと同じ事をあらわしていることが分かる。

MySQL :: MySQL 5.1 Reference Manual :: 13.5.12.1 Clustered and Secondary Indexes

InnoDB では、非集合インデックス(セカンダリ インデックスとも呼ばれる)内のレコードは、行に対して主キー値も含んでいます。InnoDB は、この主キー値を集合インデックスから行を検索するのに利用します。もし主キーが長いと、セカンダリ インデックスがより多くの領域を利用する事に注意して下さい。


問題なのはBの場合、id1 か id2 のどちらかが NOT NULL かつ UNIQUE 出ない場合別途主キーの代わりとなるINDEXが作成されること。

そして id1 と id2 に対するインデックスはそれぞれ非集合インデックスとなるので集合インデックスに大して領域を消費することが想定される。

これだけの理由で数倍の領域差が出るかどうかは分からないが、多くの部分を占めていることは想像に難くない。


このケースの場合これらの推定が正しければインデックスの張り方というよりも PRIMARY KEY が指定されているかどうかが大きく影響しているとみてよさそう。


またAとBではパフォーマンスにも、

大きく影響を与えるのでしょうか。

YES だ。おそらくAの方が圧倒的に早い。(体感できるかどうかは別として)

インデックスのサイズはそのインデックスを使用したときの検索速度に正の相関を持つ。

単に読まなければならないディスク容量が増えたのだから当然ともいえる。

これは読み書きとも同様の理由で性能低下の原因となりうる。

◎質問者からの返答

ご回答ありがとうございます。

clustered indexという考え方に基づくとしっくりきますね。

ありがとうございます。

もう一点お聞きしたいことがあります。

実際A、Bおのおのデータを作成したのですが、

データサイズは下記のようになっております。

※BのレコードからAを作ったため、

キーの重複により若干Aのレコードが少なくなっています

■A

Rows :7003万

Data_length :4.6G

Index_length :1.7G

■B

Rows :7394万

Data_length :3.3G

Index_length :4.9G

データサイズがインデックスサイズと逆転していることは、

どういった原因が考えられるかお分かりになりますか。

よろしくお願いします。


4 ● b-wind
●22ポイント

データサイズがインデックスサイズと逆転していることは、

どういった原因が考えられるかお分かりになりますか。

逆転というよりデータのサイズはインデックスのサイズに対して支配的ではない。

どちらかというとカーディナリがどの程度かのほうが重要。

これは特に MySQL や InnoDB に限った話ではないですね。

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ