tiger

2012.10.28

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

MySQL

こんにちわ。モニプラ 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万件のデータを作成しました。

CREATE TABLE innodb_fulltext_test (
     id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
     content TEXT,
     FULLTEXT( content )
) ENGINE = INNODB;

CREATE TABLE innodb_fulltext_test (

id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,

content TEXT,

FULLTEXT( content )

) ENGINE = INNODB;

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

SELECT COUNT(*) FROM innodb_fulltext_test WHERE content LIKE '%keyword%';

1	SELECT COUNT(*) FROM innodb_fulltext_test WHERE content LIKE '%keyword%';

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

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

SELECT COUNT(*) FROM innodb_fulltext_test WHERE MATCH(content) AGAINST ('keyword');

1	SELECT COUNT(*) FROM innodb_fulltext_test WHERE MATCH(content) AGAINST ('keyword');

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

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

CREATE TABLE myisam_fulltext_test (
     id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
     content TEXT,
     FULLTEXT( content )
) ENGINE = MYISAM;

CREATE TABLE myisam_fulltext_test (

id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,

content TEXT,

FULLTEXT( content )

) ENGINE = MYISAM;

SELECT COUNT(*) FROM myisam_fulltext_test WHERE MATCH(content) AGAINST ('keyword');

1	SELECT COUNT(*) FROM myisam_fulltext_test WHERE MATCH(content) AGAINST ('keyword');

ストレージエンジン以外は同じ条件にして結果は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に掛けて比較してみます。

SELECT
    *
FROM
    person_diary
WHERE
    person_id IN (SELECT id FROM person WHERE name LIKE '%TAKA%');

SELECT

FROM

person_diary

WHERE

person_id IN (SELECT id FROM person WHERE name LIKE '%TAKA%');

まずは以前のバージョンの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テーブルを効率よく検索しています。
クエリオプティマイザが進化しているのが分かりますね。

最後に

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

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

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

tiger

最近ではコードを書く機会がめっきり減って来てプログラマー35歳限界説に恐怖しています。このブログを口実に無理矢理新しい技術に触れていきたいと思っています。

RELATION ENTRY

tigerの記事一覧へ

▶︎

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

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

検証してみました

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

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

検証してみました

最後に

RELATION ENTRY

HOT ENTRY

RECENT