れさくのブログ

れさくのブログ

*

MySQLをチューニング1:DB構造を見直す

      2015/03/25

mysqlデータベースが重いからMySQLをチューニングするに書いた四天王の最初のヤツ「DB構造を見直す」と戦います。

見直すということは、悪い箇所を見つけて直すということです。
ということは、「何を以て悪いとするか」という根拠が必要です。
直感とかダメです。

DBが重い、遅いということは、slow_queryなんてキーワードが俄然大注目ですよ。
遅いクエリですから。

そこでこんな作戦を立てましょう。

    1:slow_queryを記録するログを設定する
    2:実際の画面を操作してクエリを発生させる
    3:ログを見てslow_queryを抽出する
    4:その悪いクエリを解析する

手順
現状の設定確認
mysql -uroot
mysql> show variables;

1秒以上かかっているクエリをログ出力する設定をする。
mysql> SET GLOBAL slow_query_log=ON;
mysql> SET GLOBAL long_query_time=1;

そのログは
/usr/local/var/mysql/PC039-slow.log
にあるらしい。(変更できなかった)

設定変更確認

*エディタビジュアルでは正常に表示されるけど本番では崩れるWordPressのバグ。プラグインとかあるのかな?

ブラウザ叩くよ
vi /usr/local/var/mysql/PC039-slow.log
こんなログが取れました。
select count(*) as aggregate from station_workers where station_workers.station_id = '43';

クエリ解析します。
EXPLAIN <SQL文>\G
これを実行すると、使えるインデックス(possible_keys)と使ったインデックス(key)が出るので、無駄なインデックスが分かります。

レッツ実行。
EXPLAIN select count(*) as aggregate from station_workers where station_workers.station_id = '43';\G

するとこんな結果が出ました。
ちなみに中で使われているテーブル名やカラム名はフィクションです。

クエリの中でstation_idというカラムをselectしてるのにpossible_keysがNULLですね。
つまりstation_idにindexが必要ということになります。

その逆のケースを見て見ましょう。

train_idにindexが貼られていますが、これはクエリの中で使われていません。
つまりこれは不要なindexということになります。

ちなみに大昔、とあるところで今回のindex最適化をしたら、
3分以上かかっていた処理が6秒にまで改善されたことがあります。

さて、このようなDBの構造を見直してもなお重い場合、PHPに書かれているクエリを見直す必要があります。
というか、「DBの構造の見直し」と「クエリ見直し」は並行してやりましょう。
大丈夫、真の勇者なら四天王を二人くらいまとめて倒せます。(知らんけど

 - MySQL

adpc

adpc

Message

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

次のHTML タグと属性が使えます: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

  関連記事

mysql
dumpした大容量sqlファイルでDBを構築-IT版本当にあった怖い話(レベル8)

途中までは、よくある話。 「dumpしたsqlファイル送るからそっちのローカルに …

slaveのレプリケーションが止まったら時間操作系のスタンドで直すことができる

MySQLがあって、masterのDBとslaveのDBがあって、masterか …

mysql
データベースが重いからMySQLをチューニングする

むかーしむかし、あるところにMySQLがありました。 おじいさんは(炎上案件の) …

mysql
コマンドラインでMySQLを操作するまとめ(復習)

phpMyAdminなどで運用中心にやっているとコマンドラインを忘れがち。 なの …

mysql
MySQLをチューニング3:MySQLの設定見直す

データベースが重いからMySQLをチューニングするに書いた四天王の三人目「MyS …

【ファントムシャットダウン】slaveのレプリケーションが止まる原因【エラー1053】

これまでのあらすじ slaveのレプリケーションが止まったらザ・ワールドであるこ …

aws
AWSの最適化とか設定を検討しながら作業メモがてら意識の高い雑談

AWSが非常に優れたベストプラクティスでサステイナブルなソリューションでありアク …