はじめての MySQL で100万件のデータを管理する時に行ったチューニングまとめ

MySQL の勉強をせずにフレームワーク等で SQL を書かずに Web サイトを構築していました。データ数も2万件程度でしたので、そこまで困ることはありませんでしたが、今回100万弱の商品データを扱う機会ができたので、MySQL のチューニングや発行する SQL について見直す機会がありました。

この記事では MySQL を高速化するのに行った対策など勉強したものを自分用にメモしておきました。

条件式で比較するカラムにインデックスを使用して高速化

1

INDEX index_name (col_name(length))

商品コードで存在しない商品を見つけて、商品をDBに登録するという処理を行っている場合、4万件超えたころから処理に2秒以上かかるようになってきます。12万件超えた頃には10秒程度かかるようになってしまいましたが、商品コードのフィールドに対してカラムインデックスを貼ることで0.2秒に短縮することができました。

MySQL のリファレンスにも以下のように書いてありました。

MySQL の全てのカラム型にはインデックスを張ることができます。SELECT 操作のパフォーマンスの改善には、対応するカラムにインデックスを使用することが最善の方法です。

処理が遅いなと思ったときは、まず最初にインデックスの使用を検討するのが良さそうです。劇的に速くなりますね。

ワイルドカードではなくフィールド名を限定して取り出す

1

SELECT * FROM `items` WHERE 1 = 1

ワイルドカードで全てのデータを取り出すと10秒かかる処理でも、特定のフィールドを以下のように指定して取り出すことで0.1秒程度にまで短縮することができました。

1

SELECT `Item`.`id` FROM `items` AS `Item` WHERE 1 = 1

その時必要なデータを考えて、少しでも少なく取得できるのなら、それが一番良いですね。少ないデータを取り出す場合だとあまり意識する必要はありませんが、100万件にもなると慎重にデータを取り出す必要があります。

ORDER BY RAND() は処理に時間がかかる

今までランダムにコンテンツを表示する際は以下のような SQL を発行して処理していました。

1

SELECT * FROM table ORDER BY RAND()

しかし100万件のデータに対してこれをすると全文検索になるので20秒程度かかりました。そのため、乱数は別で取得してそれを使ってデータを取り出す方が効率的です。

例えば id に欠番がなく連続して並んでる場合は以下のような PHP で乱数を作ってデータにアクセスする方法があります。コードを書くのが大変なので CakePHP のコードで書きますと。

1
2
3
4
5
6
7
8
9
10
11

$random = Array();
while(true) {
	$num = mt_rand(1, $this->find('count'));
	if(!in_array($num, $random)) $random[] = $num;
	if(count($num) > 4) break;
}
$conditions = Array();
foreach($random as $num) {
	$conditions[] = Array('id' => $num);
}
$result = $this->find('all', Array('conditions' => Array('OR' => $conditions)), 'fields' => Array('Item.id')));

このような感じになりますが、id に欠番がある場合でデータが100万件程度でしたら、以下のようにして取り出すと効率的です。もしかしたらこちらの方が効率的にランダムにデータを取得できるかも。

1
2
3
4
5
6

$random = $this->find('all', Array('limit' => 5, 'fields' => Array('Item.id'), 'order' => Array('rand()')));
$conditions = Array();
foreach($random as $num) {
	$conditions[] = $num['Item']['id'];
}
$result = $this->find('all', Array('conditions' => Array('id' => $conditions), 'fields' => Array('Item.image', 'Item.name')));

このコードは先ほど話したフィールド名を id のみを指定することで処理が高速になるので、その高速な状態で ORDER BY RAND() をして乱数を取得し、改めてその乱数を使ってデータを取得する流れです。

キーワードなどによる日本語全文検索の高速化

1

SELECT * FROM `items` WHERE `name` LIKE BINARY '%キーワード%' ORDER BY `updated` desc LIMIT 10

このように私は検索を実装していましたが、これでは100万件規模になると結果を取得するのに4秒程度かかります。一般的に全文検索をする際は全文検索用のエンジンを入れるのが良いそうです。

MySQL FULLTEXT + Ngram の組み合わせ

聞いた話ですと Ngram を使った検索は簡単に導入できますが、データが多いとソートに時間がかかったりするそうです。また頻繁に update と insert する場合は遅くなってしまうことがあるとのことです。簡単な代わりにいろいろとデメリットがあるみたいです。

Apache Solr

Apache Solr がありまして、mixi はこれのラッパーである Anuenue というのを利用しているそうで個人的に良さげに感じました。Twitter で @nntsuguさんから聞いたのですが、最近社内で Solr が流行っているらしいです。結構簡単に導入できたらしいですよ。

Senna

ニコニコ大百科で使われているらしいエンジンらしいです。MySQL FULLTEXT + Ngram の記事で紹介されていたのでチェックしてみました。これを利用しているという方が見つけられなかったのでアドバイス頂けませんでしたが、こちらも選択肢のひとつにしたいです。

関連記事

コメント

  1. 堀内暢之さんのコメント

    SELECT * FROM `items` WHERE 1 = 1

    ココの部分ですが、そもそも100万件のidが一度に必要になるケースはあまりないと思います。

    自分は、絞り込みはできるだけSQL側で実装するように心がけていて、こういうのもページネーションを入れたりするようにしますね。