Yet Another Hackadelic

効率的なインデックスの生成と管理について

MySQL

不思議とアプリケーションチューニングに置いて、余り語られていないINDEX化ですが、

意外ときちんと調べて見ると色んな機能があるんだなと再認識させられます。

複合INDEX

例えば複合INDEXなんかですが、

多くのプログラマが勘違いしていたりとか、あるいは適切に張る努力を怠っていたりしませんかね。

テーブルに複合インデックスがある場合、オプティマイザではインデックスの左端の先頭部分のいずれかをレコードの検索に使用できます。 たとえば、(col1, col2, col3)に3カラムのインデックスがある場合、(col1)、(col1, col2)、および (col1, col2, col3) に対して、インデックスの検索機能を使用できます。 MySQL 4.1 リファレンスマニュアル :: 5.4.3 MySQL でのインデックスの使用

上記の引用でもあるように、(col1), (col1, col2), (col1, col2, col3)のいずれかの組み合わせがANDで指定されている時にこの複合INDEXが適用されます。

カラムの一部のみを使用するインデックス

こんな機能があるのは始めて知った訳ですが…。

CHAR 型と VARCHAR 型については、カラムの一部のみを使用するインデックスを作成できます。この場合、col_name(length) 構文を使用して、各カラム値の最初から length に指定した数のバイトのインデックスを作成します（BLOB 型と TEXT 型では、プリフィックスの長さを必ず指定する必要があります。length には 255 までの数値を指定できます）。次のステートメントでは、name カラムの最初の 10 文字を使用したインデックスが作成されます。

mysql> CREATE INDEX part_of_name ON customer (name(10));

ほとんどの名前は最初の10文字が異なるため、このインデックスの場合、name カラム全体から作成したインデックスよりはるかに遅くなるということはありません。 また、カラムの一部でインデックスを作成するとインデックスファイルのサイズを大幅に削減できるため、ディスク領域が節約されるとともに、INSERT 操作が迅速化される場合があります。 MySQL 4.1 リファレンスマニュアル :: 6.5.7 CREATE INDEX 構文

そういえば、どこかで検索技術な話を見た時に、

ハッシュインデックスのような話を聞いた気がする。

その他

結構ストレージエンジンごとにINDEXの扱いも異なるようで、

特に色々と面白い機能があったりします。

遅延キー書き込み(MyISAM)

デフォルトがONみたいなんで、余り気にしないでよいと思うんですが、

delay_key_write変数をONにしておくと、INDEXの生成が遅延実行されるみたいです。

これでwriteも多いサービスでも速度の向上が見込めるって寸法です。

但し遅延させると言うトレードオフとして、INDEX化が終了しないでMySQLがクラッシュすると、同期が取れなくなるのでREPAIR TABLEを実行しないとダメです。

プライマリキーを指定しない場合の挙動(InnoDB)

InnoDBでは64ビットの値がレコードに割り振られるようです。

従ってある程度の速度は保障されると考えて良いと思います。

INDEXの再構築(InnoDB)

MyISAMの場合はOPTIMIZE TABLEでindexの再構築が出来ます*1が、InnoDBでは出来ません。

ではどうやるかと言えば、

インデックスのスキャンを速くするには、定期的に mysqldump を使ってテーブルをテキストファイルにダンプしてからテーブルを破棄し、ダンプからテーブルを再ロードします。 デフラグメント化のもう 1 つの方法として、テーブル変更操作 ALTER TABLE tablename TYPE=InnoDB を実行します。 これによって、MySQL がテーブルを再構築します。 MySQL 4.1 リファレンスマニュアル :: 7.5.13.3 InnoDB テーブルのデフラグメント化

と言う訳で敢えてALTER TABLEでストレージエンジンをInnoDBとしてやる事によって、再生成を行う事が出来るみたいです。*2

しかしMySQLの公式マニュアルはかなり突っ込んだネタまで書いてあって面白いですね。

Permalink | コメント(0) | トラックバック(1) | 02:48