2019-05-14に更新

MySQLの負荷を軽くするための方法や設計の例を色々書いてみる

読了目安:15分

昔1年ほどアクセスが多く下手なことをするとすぐ重くなってしまうソーシャルゲームの開発&運用を行ったことがあります。MySQLの運用方法についてはそこで色々と叩きこまれたためその後の開発にも役立っていますが、せっかくですので色々と思いつく軽量化の方法を色々と例をあげつつ書いてみたいと思います。

前提

最新のMySQLの挙動を全て正確に把握しているわけではありません。そのため「いや、ここはこういうやり方でも大丈夫なはずだけど…」という話も出てくると思います。ただ、とりあえず深く考えたり知ったりしていなくてもできる極端な方法をあげていくため、そのあたりの細かい部分は考慮していません。何かあればコメントで補足していただいたり、別途最新の仕様に基づいたより良い使い方ができる記事などを書いていただけると助かります。

シンプルなSQLしか使わない

基本的に、下記のようなシンプルなSQLのみを使います。

SELECT columns FROM table WHERE column = value ORDER BY order_column

インデックスを効かせる

columnやorder_columnはインデックスが効くものだけを使います。EXPLAINで適宜確認しましょう。

ただしデータが1000以下程度のテーブルであればそもそもインデックスを使ってくれなかったりするため、適当で良いです。ただ後のちデータが増える可能性があるテーブルはその時に調整などが必要になると面倒ですので、予め考えておいて作り直しがなるべく必要ではなくなるように作っておいたほうが良いとは思います。

便利な機能は使わない

下記のものは基本的には使いません。

  • OR
  • JOIN
  • UNION
  • サブクエリ
  • 関数

等々。こういうものを使っていくとインデックスが効かなくなったりする場合がありますし、検索も各データ毎に更に検索を行う、というN+1問題的なものが発生し始めるので負荷が発生する原因となる可能性がでてきます。

でも使えないとこまるんだけど…、という場合が出てくると思いますが、それは設計でカバーすることで実現していきます。引き続き色々と例をあげてみます。

わざわざデータを冗長にする

例えばフレンドについて考えてみます。Twitterのようなフォロー、フォロワーのような双方で独立した関係であれば問題はありませんが、「フレンド」というお互いのつながりのみで成り立つ場合の話です。たとえば下記のようなfriendsテーブルがあるとします。

カラム
user_id int
friend_id int

これでAさんとBさんをフレンドとして繋げる場合、user_idにAさんのID、friend_idにBさんのIDを入れるとします。この場合、「Aさんのフレンド一覧」を検索する場合、下記のSQLが必要になります。

SELECT * FROM friends WHERE user_id = AさんのID OR friend_id = AさんのID

ORを使っているのでインデックスも効かなくなりますし悲惨です。Twitterのフォロー・フォロワーの関係と同じように、フレンドになった場合は1レコードではなく双方用の2レコードを入れることで、シンプルなSELECT文だけでフレンド一覧を取得できるようになります。

SELECT * FROM friends WHERE user_id = AさんのID

わざわざテーブルの構造を冗長にする

例えば記事にタグを付けられるように下記のようなbelongsToMany的な構成の3つのテーブルが存在するとします。

posts - post_tag - tags

post_tagはこんな感じです。

カラム
post_id int
tag_id int

ここで、「あるタグがついている記事を検索する」という機能をつけたい場合、これは簡単です。

SELECT * FROM post_tag WHERE tag_id = 検索したいタグID

これでpost_idが分かるので、表示する10件程度だけpostsを取得すれば完了です。(LaravelのEager Loadingだとこれも勝手にやってくれます)

では「あるユーザーが記事につけた全てのタグを取得」となるとどうでしょう。下記のSQLでしょうか?

SELECT DISTINCT
    post_tag.tag_id
FROM
    post_tag
    LEFT JOIN
        posts
    ON  posts.id = post_tag.post_id
WHERE
    posts.user_id = ユーザーID

個人的にリレーションしたテーブルだけでWHEREを作ったSQLは危険だと思っています。インデックスも効きませんし、内部的には全データの関連データを取得しないと絞り込みができません。

この場合、post_idとtag_idしかないpost_tagにuser_idも加え、保存時にuser_idも入れておくことでシンプルなSQLにすることができます。

カラム
post_id int
tag_id int
user_id int
SELECT * FROM post_tag WHERE user_id = ユーザーID

SQLを分けてシンプルにする

上記の話と少しかぶりますが、例えばユーザー名で記事を検索したい場合。JOINして検索するのではなく、まずユーザー名がヒットするユーザーのIDを先に取得しておきます。次にそれを使ってuser_id IN (既に取得したユーザーID一覧)で記事の検索は行います。

クエリ数は増えますがJOIN先の検索が不要になるため状況によっては大幅な軽量化ができる場合があります。

表示時には複雑な検索をしない

どうしても複雑なSQLでランキング作成や集計処理を行わなければならない場合があると思います。この場合、表示する際に集計するのではなく、予め定期的なバッチ処理で集計してキャッシュに保存しておき、表示する際にはそれを表示するだけにすることで負荷をなくすことができます。

生SQLを見る

ORMを使っているとよく分からず作ってしまう場合もありますので、生SQLを時々見るようにしましょう。大体のフレームワークが開発時にはログを垂れ流してくれていると思います。

Laravel等はDebugbarがあり画面上でログを見れたりします。SQLの件数等も表示されていたりするため、誤ってループ内で更にSQLを実行してしまっている時なども気づきやすいです。

特にリレーションのSQL等はフレームワークによって異なっていたりするため、確認せずに進めているととんでもないSQLが発行されていることなどもあります。ちょこちょこ確認し、気になったものはEXPLAINでチェックするようにしましょう。

やっても問題ないことはある

色々書きましたが、実際にはやっても問題ないことはあると思います。バージョンによって可能なことは変わっていくでしょうし、MySQL以外だと最適化されているものもると思います。ただ、「あれ、どうだったっけ?」みたいなパターンが増えてくると思うため、まずは思考停止でなるべく怪しいことはしないようにし、どうしても必要な場合はちゃんと調査&検証を行って問題ないことがはっきりしたらやっていくと良いと思います。

まとめ

色々と書きましたがそんなに多くはなかったですね…。

何にしろこのように、手法や設計を変えることで負荷を与えなくても同じ動作を行えることというのはいくつもありますので、負荷的にあやしい処理をつくってしまいそうな場合にはまず設計を見直して軽量化できないかを考えてみると良いと思います。

ちいさなシステムの場合はさほど問題ないかもしれませんが、大規模な場合はそれだけでサーバーを数台減らせて何十万円と節約できたのに、という場合も多々あると思います。メリットが「ほんのちょっと」というレベルではない場合もありますので、その都度色々と考えてみてください。

この他にも色々と改善のアイデアはその都度出てくると思いますので、とにかくその都度DBちゃんの気持ちになって、どれだけたくさんのデータを見なきゃいけないかをその都度考えてみましょう。DBちゃんは現実に存在するのです…。無理に酷使してはいけません…。

まとめ2

もっと色々知りたい方は、大規模ゲームについてや、大規模サービスについてのDB運用方法について書かれたスライドが数多く存在します。僕はもう見つけられないのですが是非探してみてください。単にクエリやスキーマの調整だけでなく、DB自体の分け方や様々なテクニックが色々なところで紹介されています。


だら@Crieit開発者

Crieitの開発者です。 主にLAMPで開発しているWebエンジニアです(在宅)。大体10年程。 記事でわかりにくいところがあればDMで質問していただくか、案件発注してください。 業務依頼、同業種の方からのコンタクトなどお気軽にご連絡ください。 業務経験有:PHP, MySQL, Laravel5, CakePHP3, JavaScript, RoR 趣味:Elixir, Phoenix, Node, Nuxt, Express, Vue等色々

Crieitは個人で開発中です。 興味がある方は是非記事の投稿をお願いします! どんな軽い内容でも嬉しいです。
なぜCrieitを作ろうと思ったか

また、「こんな記事が読みたいけど見つからない!」という方は是非記事投稿リクエストボードへ!

こじんまりと作業ログやメモ、進捗を書き残しておきたい方はボード機能をご利用ください!

ボードとは?

関連記事

コメント