Rails で MySQL を使うときの注意点
Rails のブログでも取り上げられていた、"Rubyisms - MySQL-dump" が面白かったので、特に興味深かった一時テーブルまわりの要約を載せてみる。要約、というか読みながら書いたメモそのまんまですが。
Rubyisms - MySQL-dump http://mysqldump.azundris.com/archives/72-Rubyisms.html
“select *” が多すぎる
ActiveRecord は、特に何も指定しないと select * で、すべてのカラムを取ってくる。SQL は動的に生成されて、どのカラムが存在するかチェックしているので、スキームが変更されても大丈夫。
でも、必要のないデータまで取ってきてしまう、という問題はあるよね。そして、何が無駄かっていうと、必要のないデータのために一時的なテーブルが使われるかもしれない、ってことなんだ（SQL を EXPLAIN してみて、“using temporary” となっていれば、そういう事態が発生しているサインだ）。
- TEXT や BLOB をもつテーブルではもっと悪くて、一時テーブルはディスク上に作られる
- これには
tmp_table_sizeやmax_heap_table_sizeの設定は関係ない
そういう場合は、SQL を二段階に分けるといい
- まず、最初の SQL でレコードの ID だけを SELECT する（
intだけなので、このときの一時テーブルはメモリ上に作られる） - 次に、その ID で必要な TEXT や BLOB を SELECT するんだ（これは ID による単純な検索なので一時テーブルは使われない）
- ActiveRecord では
find([id1, id2, ...])
では、ActiveRecord で select * を避けるにはどうするか？
find(...)の最後の引数に指定するハッシュに:select => [ fieldlist ]として指定するActiveRecord::Baseをサブクラス化して、:selectが指定されていなければ、警告を出すようにするのがいいんじゃない？
文字列型がかなり無駄かも
たとえ TEXT 型（Migration だと :text 型）を使っていなくても、Migration で :string 型を使っているなら、気をつけた方がいい。
- 普通、これは VARCHAR(255)
- エンコーディングは
utf8になっているだろう
さて、MySQL ではメモリ上の一時テーブルには VARCHAR 型がない（「補足：メモリ上の一時テーブルには VARCHAR 型がない」参照）
- 代わりに VARCHAR は CHAR に変換される
- つまり、VARCHAR(255) は CHAR(255) ってこと
- そして、
utf8では 1 文字 3 バイトとして換算されるから … :string型のカラムひとつごとに 765 バイトを占有する … なんてこった。
だから、デフォルトの utf8 ではなく、latin1 などの多バイトではないエンコーディングを使うことも考慮した方がよい。そして、:string 型には必ず :limit を指定しよう。
以上、MySQL の一時テーブルの特性とか、知らないとどうにもならん部分が多い。他にも、
- なんでテーブル名とかをバッククォートで囲んでないの？ とか、
- トランザクション内部ではクエリーキャッシュが使われない、とか
興味深い話題があります。あと、この要約が間違ってる可能性もあるので、是非原文も読むことをおすすめします。
補足：メモリ上の一時テーブルには VARCHAR 型がない
「メモリ上の一時テーブルには VARCHAR 型がない」という記述について、気になったので調べてみた。
Common Questions and Answers from Performance Tuning Webinars - Jay Pipes という記事にて、
- A temporary table is implicitly created to handle a GROUP BY or ORDER BY clause and a VARCHAR column is in the SELECT statement
- A temporary table is created explicitly which contains a VARCHAR column
In these cases, the length of the VARCHAR columns does come into play. Why? Because temporary tables in memory are actually just tables of the MEMORY storage engine. The MEMORY storage engine, for some reason, treats all VARCHAR(X) columns as CHAR(X) columns.
つまり、
- メモリ上の一時テーブルは MEMORY ストレージエンジンである
- そして、MEMORY ストレージエンジンは
VARCHAR(X)をCHAR(X)として扱う
ということのようだ。