俺の報告

RoomClipを運営するエンジニアの日報（多分）です。

MySQLのインデックスの運用 - 日報 #144

高速でGWが過ぎ去りましたが、日々充実しております。
ありがたいことですね。

さて、表題の通りMySQLのインデックスについて少し触れたいと思います。
というのも、とあるクエリが「インデックスを使ったり使わなかったりする」というような現象にでくわしたのです。
まぁ色々要因があると思いますが、
少なくともインデックスについて知見を深めなければなりません。
ということでちょいと勉強したことをば。

インデックスは言わずと知れたB-Tree構造です。
そこまではいいとして、運用保守についてやや理解が及んでなかったのです。
その辺について。

インデックス周りで、今回のケースみたいな奇妙な動きがあった時は、大体下記の手順で疑いにかかるとよいという結論になりました。

1. インデックス破損していないか？

インデックスは強制終了やハードウェア故障、OSのバグなど発生するようです。
中でもMyISAMは強制終了（クラッシュ）でよく起こるそうです。
InnoDBではほぼ起きないらしいので、もしこのエンジンを使っているならば、 あんまりこの項目は重要ではないですが、
とにかく、どうすればこの破損を捕捉できるかというと、

CHECK TABLE table_name

だそうです。
規模がでかいテーブルだとそこそこ秒数かかるので注意が必要です。
でも前述のとおりInnoDBではほとんどこの可能性は低いとみていいみたいです。
じゃぁ次は何かというと、、、

2. インデックス統計情報が間違ってない？

インデックスが正しく使われるかどうかについては、
全てクエリオプティマイザが実権を握っているわけです。
で、クエリオプティマイザは何でもってインデックス使用の判断をするかというと、
これも単純で、最終的にアクセスするデータの量、つまりコストベースなわけです。
なので、このアクセス予定のデータ量が誤っているとインデックスを使うべきかどうかの判定はコロコロ変わってしまうんですね。

で、そのデータ量の取得にはrecord_in_rang APIか、info() APIかが使われるみたいなんだけど、
細かいことはおいといて、
MyISAMだと正確な値を返すが、InnoDBだと概算値になるんです。
InnoDBでインデックスのカーディナリティを見た時にコロコロ変わるのはコレが原因です。
（テーブルの行数とかも変わりますよね）
んで、これはどうやって推計計算しているかっつぅと、
innodb_stats_sample_pages
というパラメータで指定したサンプル数だけページ（ノード）を取得して、
そこから他のページのレコードの数を推計計算するという、
いわゆるサンプリング手法で計算しているみたいです。
インデックスの量や、レコード数が増えてきたら、このサンプル数を増やす必要がありますね。
デフォルトでは8ページみたいですので、16とかにしておきます。
理論上、これでボラティリティは下がるはずです。

ということで、インデックスのカーディナリティが変に変動したり、
インデックスの使用判定がバラついたりするのであれば、
innodb_stats_sample_pagesを増やしたり、ANALYZE TABLEをしてみたりするといいかもしれません。

さて、大体以上ですが、
番外編として、断片化について。

インデックスの断片化は大きく分けて３つあります。
1. 行の断片化
一行のデータがバラバラに格納されてる状態。
これは最悪ですね。インデックスで一発で引き当てたとしてもI/Oで時間がかかるというクソパターンです。
2. 行間の断片化
id連番で連続に格納されているべきデータがバラついたりする現象です。
idが飛び飛びになったりすると起こりがちなものですね。
id:1-10をとってこい！っていう分かりやすい命令が、
ポインタベースで10個バラバラにかき集めるんじゃぁ、HDDが火を吹きますね。
3. 空き容量断片化
恐らく一番多いパターンです。
よく言われる断片化の典型パターンですね。

これもInnoDBではあまり起きないようですが、
さすがにでかいDBをずーっと運用してたら断片化はします。
1,2についての断片化具合を確かめるのは困難ですが、
3については、「実際のデータ長と、平均のデータ長✕行数の差分」で空き容量を確認することができます。
まぁこんなクエリですね。

SELECT
     `TABLE_ROWS` AS tbl_rows,
     `AVG_ROW_LENGTH` AS avg_length,
     `DATA_LENGTH` AS total_length,
     (`DATA_LENGTH` - `TABLE_ROWS` * `AVG_ROW_LENGTH`) AS flag
FROM information_schema.TABLES
WHERE TABLE_NAME = 'hogehoge'

これがあまりに太るようであれば、OPTIMIZEコマンドを流すと良いと思われます。
一番いい方法はなんと「意味のないAlter文を流す」だそうです。

ALTER TABLE hogehoge ENGINE=INNODB;

みたいな。
「えぇー。。。」って感じですが、まぁそれがよくある手法みたいですよ。

ということで、長くなりましたが、そんな感じです。
何かのご参考になれば。