MySQLをチューニング１：DB構造を見直す
2015/03/25
データベースが重いからMySQLをチューニングするに書いた四天王の最初のヤツ「DB構造を見直す」と戦います。
見直すということは、悪い箇所を見つけて直すということです。
ということは、「何を以て悪いとするか」という根拠が必要です。
直感とかダメです。
DBが重い、遅いということは、slow_queryなんてキーワードが俄然大注目ですよ。
遅いクエリですから。
そこでこんな作戦を立てましょう。
-
１：slow_queryを記録するログを設定する
２：実際の画面を操作してクエリを発生させる
３：ログを見てslow_queryを抽出する
４：その悪いクエリを解析する
手順
現状の設定確認
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
にあるらしい。（変更できなかった）
設定変更確認
|
1 2 3 4 5 6 7 |
mysql> show variables; +------------------+-----------+ | Variable_name | Value | +------------------+-----------+ | slow_query_log | ON | | long_query_time | 1.000000 | +------------------+-----------+ |
＊エディタビジュアルでは正常に表示されるけど本番では崩れる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
するとこんな結果が出ました。
ちなみに中で使われているテーブル名やカラム名はフィクションです。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
*************************** 1. row *************************** id: 1 select_type: SIMPLE table: station_workers type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 86 Extra: Using where 1 row in set (0.00 sec) |
クエリの中でstation_idというカラムをselectしてるのにpossible_keysがNULLですね。
つまりstation_idにindexが必要ということになります。
その逆のケースを見て見ましょう。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
*************************** 1. row *************************** id: 1 select_type: PRIMARY table: station_rec type: ref possible_keys: PRIMARY,station_number, station_id,train_id key: station_id key_len: 5 ref: const rows: 18144 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: train_tbl type: ref possible_keys: PRIMARY, station_number, train_id key: station_number key_len: 4 ref: data_entry_prod.job_documents_44.row_number rows: 2 Extra: NULL |
train_idにindexが貼られていますが、これはクエリの中で使われていません。
つまりこれは不要なindexということになります。
ちなみに大昔、とあるところで今回のindex最適化をしたら、
3分以上かかっていた処理が6秒にまで改善されたことがあります。
さて、このようなDBの構造を見直してもなお重い場合、PHPに書かれているクエリを見直す必要があります。
というか、「DBの構造の見直し」と「クエリ見直し」は並行してやりましょう。
大丈夫、真の勇者なら四天王を二人くらいまとめて倒せます。（知らんけど
adpc
adpc
関連記事
-
-
【ファントムシャットダウン】slaveのレプリケーションが止まる原因【エラー1053】
これまでのあらすじ slaveのレプリケーションが止まったらザ・ワールドであるこ …
-
-
dumpした大容量sqlファイルでDBを構築－IT版本当にあった怖い話（レベル8）
途中までは、よくある話。 「dumpしたsqlファイル送るからそっちのローカルに …
-
-
コマンドラインでMySQLを操作するまとめ（復習）
phpMyAdminなどで運用中心にやっているとコマンドラインを忘れがち。 なの …
-
-
AWSの最適化とか設定を検討しながら作業メモがてら意識の高い雑談
AWSが非常に優れたベストプラクティスでサステイナブルなソリューションでありアク …
-
-
slaveのレプリケーションが止まったら時間操作系のスタンドで直すことができる
MySQLがあって、masterのDBとslaveのDBがあって、masterか …
-
-
MySQLをチューニング３：MySQLの設定見直す
データベースが重いからMySQLをチューニングするに書いた四天王の三人目「MyS …
-
-
データベースが重いからMySQLをチューニングする
むかーしむかし、あるところにMySQLがありました。 おじいさんは（炎上案件の） …