tiger

2012.10.28

MySQL5.6の気になる新機能をチェック!

こんにちわ。モニプラ for Facebookを担当しています高橋です。

先日サンフランシスコでMySQLのカンファレンスが行われMySQL5.6RC版の発表がありました。
http://dev.mysql.com/tech-resources/articles/mysql-5.6-rc.html

ベースの性能がアップしているのはもちろんのこと新機能がモリモリ載っています。
今回は開発者視点で個人的に気になるMySQL5.6の機能をチェック&ちょっぴり検証してみました。

InnoDBでFULLTEXTインデックスが利用可能に

http://dev.mysql.com/doc/refman/5.6/en/innodb-table-and-index.html#innodb-fulltext-index

今までMyISAMストレージエンジンでのみ使えたFULLTEXTインデックスがInnoDBでも利用可能になりました。
InnoDBをメインで運用していてFULLTEXTインデックスを使いたいときは検索用テーブルを
MyISAMで切り出して…
というようなことをする必要がなくなりInnoDBで統一できるようになりますね。

検証してみました

こんなテーブルを作って、20万件のデータを作成しました。

まずはLIKE検索をしてみました。

結果は100回ほど試行して平均値は 0.874秒 でした。

次にMATCH AGAINSTで検索してみます。

結果は0.224秒 でした。
おー、ちゃんと効いてますねー。当たり前ですが。。
機械的にデータを作っちゃったのであまり違いが出ませんでしたが
インデックスが分散されている自然なデータであればもっと違いが出ると思います。

次にMyISAMのFULLTEXTインデックスと簡易的に比較してみました。

ストレージエンジン以外は同じ条件にして結果は0.31秒でした。
なんとInnoDBの方が約38%高速であるという結果になりました!
もっと多角的に検証すると違う結果になるかもしれませんが、全くの予想外でした。
(非常に簡易的な比較であるという点をご留意ください)

InnoDBでいくつかのALTER TABLE操作でロックが掛からないように改善

http://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl.html

今まではインデックスを張ろうとするとその最中のINSERTやUPDATEはロックがかかってしまうので
サービス提供中はパフォーマンスチューニングもできず、そのためにメンテナンスする必要がありました。
5.6からはDMLをブロックせずALTER TABLEができちゃいます。
これはかなり助かりますね。

ただし、全てのALTER TABLE操作ではなく以下に限定されるようです。
・カラムの追加、削除、リネーム
・セカンダリインデックスのCREATEとDROP
・auto incrementの値を変更
・外部キー制約の追加と削除
…etc

とはいえ、十分ですね。
運用がぐんと楽になります。

サブクエリも最適化されるようにクエリオプティマイザを改善

http://dev.mysql.com/doc/refman/5.6/en/subquery-optimization.html

以前、MySQLのサブクエリはイケてないとこの記事に書きましたが、
サブクエリもクエリオプティマイザの対象になりました。
ボトルネックになることが多かったのでチューニングが楽になりますね。

検証してみました

IN句にサブクエリを使った場合を検証してみます。
検証に使うテーブル定義はこんな感じです。
それぞれ100件、1000件のデータが入っているものとします。

person

カラム インデックス
id int(11) PRIMARY
name varchar(20)
gender varchar(1)
person_diary

カラム インデックス
id int(11) PRIMARY
title varchar(20)
content text
person_id int(11) INDEX

このSQLをEXPLAINに掛けて比較してみます。

まずは以前のバージョンのMySQLでEXPLAINを掛けてみます。

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY person_diary ALL NULL NULL NULL NULL 1101 Using where
2 DEPENDENT SUBQUERY person unique_subquery PRIMARY PRIMARY 4 func 1 Using where

残念ながら件数の多いperson_diaryテーブルが先にフルスキャンされてしまい、その後personテーブルが走査されます。

今度は5.6でEXPLAINを掛けてみます。

id
select_type
table
type
possible_keys
key
key_len
ref
rows
Extra
1 SIMPLE person ALL PRIMARY NULL NULL NULL 100 Using where
2 SIMPLE person_diary ref idx_1 idx_1 5 test.p.id 1 NULL

SQLの意図通り件数の少ないpersonテーブルを先に検索しています!
その後インデックスを使ってperson_diaryテーブルを効率よく検索しています。
クエリオプティマイザが進化しているのが分かりますね。

最後に

今回取り上げた3点はMySQLで開発・運用していて前々から気になっていた箇所でした。
実用的な機能・改善が多く着実に進化させてきたなーという印象です。

今回は開発者視点でチェックしましたがMySQL5.6には他にもたくさんの改善点、新機能が盛り込まれています。
気になる方はぜひぜひこちらでチェックしてみてください。
http://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html

アライドアーキテクツではエンジニアを随時募集しております。
興味ある方はぜひ。採用サイトはこちらです。