それマグで！

知識はカップより、マグでゆっくり頂きます。　takuya_1stのブログ

習慣に早くから配慮した者は、 おそらく人生の実りも大きい。
トップ > MySQLのIndexの状況を観察して、効果的なINDEXを考えたい

MySQLのIndexの状況を観察して、効果的なINDEXを考えたい

epgrec のページ表示が遅いので，SQLのログを取りたい。

まずINDEXとはなにか

データベースのインデックスとは、並び替えのこと。B-treeとかうにゃうにゃあるけど、とりあえず「前もって並び替えておく」と思っておけばいい

INDEXが無いとどうなるのか

INDEXが無い場合はプライマリキーを元に全件を探して並び替えます．時間が掛かります。
ただし全件探す場合でも

フィールドが全て固定長だったら、アクセス効率が良いので速い．
テーブルサイズがメモリサイズ以下だったら、キャッシュが効きやすかったり

でそれなりに速いです．

なんでINDEXいるの？

たぶん、JOINやるから。JOINしない＆固定長のばあい，INDEXも最低限で良い．更新が多いテーブルへのJOINやINDEXは諸刃の刃なのですね。

なんで固定長？

速いから、出来る限り固定長で数字にした方が並び替えが楽。だからマスタテーブルとリレーションで番号を繋ぐ．マスタテーブルのようにあまり変わらないものにINDEXを貼るのです．

INDEXの確認（表示）

テーブルに張られたINDEXの一覧

 show index from TABLE_NAME;

インデックスの削除

テーブルに張られたINDEXを指定して削除

drop index INDEX_NAME on TABLENAME; # show の時は from で delete は ON 。混同に注意

インデックスの作成

create index INDEX_NAME ON TABLE_NAME ( カラム名1,カラム名2,カラム名3,,,)

INDEXが使われているか見る

EXPLAIN SELECT * FROM TABLE WHERE ID > 1234;

のように SQL の先頭へ EXPLAINをつけます．

あとはEXPLAINみながら、遅いqueryがINDEX使うようになったらOK

インデックスの削除と作成を繰り返す

INDEXの作成は時間が掛かる処理なので，INDEXの実験はデータベースをコピーして別のマシンでヤル方が良いですね。

Explain するSQLはどれにするの？

EXPLAINするSQLは実行が特に遅いSQLを選びます．遅いSQLがINDEXを適切に使っているかみます．使われていないときはINDEXが効くようにします。使われてるのに遅いときは一度に取出すデータ量が多すぎるとか、INDEXが古いとか．イロイロ考えられます．

まずEXPLAINするSQLをMySQLのスロークエリlogから探します。そのためにログを取らなくてはいけません

MYSQLでSlowQueryLogをONにする

/etc/mysql/my.conf (debian)
 81
 82 # Here you can see queries with especially long duration
 83 log_slow_queries    = /var/log/mysql/mysql-slow.log
 84 long_query_time = 1
 85 #log-queries-not-using-indexes
 86 #

これでスロークエリログが取れる。

ログもっと欲しい。という欲張りな人は

 75 # Be aware that this log type is a performance killer.
 76 # As of 5.1 you can enable the log at runtime!
 77 general_log_file        = /var/log/mysql/mysql.log
 78 general_log             = 1
 79

設定の反映

sudo /etc/init.d/mysql restart # 最近は upstartの `restart mysql`を使う

やってみた結果

クエリ投げすぎ！！！EPGRECひどい
６００件程度の録画一覧出すのにどれだけ・・・
一つ一つにクエリ投げるのに反対はしないけど。

>51 Query SELECT * FROM Recorder_reserveTbl WHERE starttime < '2011-07-17 01:10:55' ORDER BY starttime DESC
>51 Query SELECT * FROM Recorder_reserveTbl WHERE id='2262'

全件取ってからもう一回全件取りに行くのはどう考えても賛成できない。

では検索が遅いのは？

mysql> explain SELECT * FROM Recorder_reserveTbl WHERE starttime < '2011-07-17 01:22:05' AND CONCAT(title,description) like '%ビギナーズ%';
+----+-------------+---------------------+------+----------------+------+---------+------+------+-------------+
| id | select_type | table               | type | possible_keys  | key  | key_len | ref  | rows | Extra       |
+----+-------------+---------------------+------+----------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | Recorder_reserveTbl | ALL  | reserve_st_idx | NULL | NULL    | NULL |  705 | Using where |
+----+-------------+---------------------+------+----------------+------+---------+------+------+-------------

検索も遅くない。

epgrec で調べてみた結果PHPが無駄query投げすぎでオーバーヘッドだと分ったとりあえずLIMIT入れて解決。