MySQLでサブクエリする時は一度LAST_INSERT_ID()を思い出してみると幸せになるかもしれない
すごく長いタイトルになりました(゜∀。)
MySQLネタです。
先月から3万件くらいのデータを一気にINSERTすることが多く、
いかにして速度を上げるかというのをやっていて、
この1ヶ月で5時間から20分にまで短縮に成功したのですが、
それで、実は一番速度が上がったのが、MySQLでよく知られている関数の
LAST_INSERT_ID()を使った時でした。
MySQL :: MySQL 5.1 リファレンスマニュアル (オンラインヘルプ) :: 7.11.3 情報関数
LAST_INSERT_ID()の説明はここに書いてあります。
それまでは、
mysql> insert into foods > (country, genre) > values > ('japan', 'grain'); mysql> insert into okome > (food_id, okomegroup) > values > ((select id from foods where country='japan' and genre='grain'), 'hakumai')
こんな感じで、サブクエリを使って入れた値のidを他のテーブルに入れていたのですが、
実はこれだとokomeテーブルに入れる時に毎回foodsテーブルをフルスキャンしてしまって、
大量のデータを入れる時にはとても時間がかかります。
そこで、現れるのがlast_insert_id()ちゃんです。
mysql> insert into foods > (country, genre) > values > ('japan', 'grain'); mysql> insert into okome > (food_id, okomegroup) > values > ((select last_insert_id()), 'hakumai')
あらまシンプル!
しかも処理速度も3分の1くらいになります。
しかもtransactionの中でも使うことが出来ます。
「でも、insertしてる間に他のバッチとかでsql動いたら
違うレコードから値を持ってきちゃうんじゃないの?」
と思う人がいると思いますが、実はlast_insert_id()は
同一コネクション内で使われるので、他で動いているクエリからは影響を受けません。
ここが結構ステキポイント。
ちなみに、last_insert_id()はauto_incrementが自動生成された時のみで動くので、
insert文の中でauto_increment値を設定していた時はlast_insert_id()には何も入りません。
おしまい!