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ではパフォーマンスにも、
大きく影響を与えるのでしょうか。

よろしくお願いします。

回答の条件
  • 1人2回まで
  • 登録:2008/12/08 15:39:15
  • 終了:2008/12/15 15:40:03

回答(4件)

id:chuken_kenkou No.1

chuken_kenkou回答回数722ベストアンサー獲得回数542008/12/08 17:15:31

ポイント23pt

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などを使用するかによって、話がまったく異なってきます。

id:the_yakisoba

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

ひとまず、

MySQLは5.0、

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

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

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

よろしくお願いします。

2008/12/08 17:42:22
id:kn1967 No.2

kn1967回答回数2915ベストアンサー獲得回数3012008/12/08 17:23:52

ポイント23pt

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

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

(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で良いのではないかと思います。

id:b-wind No.3

b-wind回答回数3344ベストアンサー獲得回数4402008/12/08 20:50:07

ポイント22pt

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の方が圧倒的に早い。(体感できるかどうかは別として)

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

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

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

id:the_yakisoba

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

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

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

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

よろしくお願いします。

2008/12/09 11:03:35
id:b-wind No.4

b-wind回答回数3344ベストアンサー獲得回数4402008/12/09 23:05:26

ポイント22pt

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

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

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

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

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

  • id:chuken_kenkou
    #1で回答したものです。

    #1回答中に、複合キーでインデクスを作った場合と、単一キーで2個のインデクスを作った場合、800万件のデータを管理する場合、必要なインデクスページ数はどの程度になるかという例(計算)を示しています。

    説明が少ない状況で、計算例を示したので捕捉しておきます。
  • id:chuken_kenkou
    #1で回答したものです。

    MySQLのInnoDBのインデクスも、B-TREE構造です。

    http://dev.mysql.com/doc/refman/4.1/ja/innodb-physical-structure.html


    B-TREEインデクスの構造等について、Oracleに関する記事があるので紹介しておきます。

    http://www.atmarkit.co.jp/fdb/rensai/oraobstacle03/oraobstacle03_1.html

    各部の名称が違ったり、一部、管理情報や細かな構造に差はありますが、考え方や基本的な構造はMySQLでも同じだと思います。

  • id:the_yakisoba
    chuken_kenkouさんコメントありがとうございます。

    一通り拝見しました。

    1つ目の回答において、
    計算された内容についてお聞かせください。

    Aのprimaryの複合インデックスにおいて、
    1ページで100エントリで、
    Bの単一カラムの場合は、
    1ページで120エントリと仮定したのは、
    複合インデックスのほうがキー値の情報が増えるため、
    1ページでのエントリ数が単一カラムの場合より少ないと、
    想定されるためでよろしいでしょうか。

    とすると、
    Aは複合インデックス+単一カラムインデックスで、
    単純にインデックスサイズは、
    808081+67226=875307

    Bは単一カラムインデックス×2なので
    67226+67226=134452

    とするとAのほうがサイズが大きくなるような気がしますが、
    いかがでしょうか。

    たびたびお手数おかけしますが、
    よろしくお願いします。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません