GROUP BYで一時テーブルが作られる時にボトルネックになるのは、
「一時テーブルの作成」だけではなく「一時テーブルに載せるデータの取得」もあるということ。
MySQLにおいてのインデックスと主キーの違いは、
やったことにも書きましたが、
「実データを取りに行く必要がある（インデックス）かない（主キー）か」という部分。
（厳密にはそれだけでは無いでしょうが・・・）
・・・ということは、
という感じで、全てが一つのインデックス内部で片付くようなインデックスを貼ったら、
実データを見に行く必要が無くなるから同じくらい速いんじゃないだろうか？
今度、時間があったら検証してみたいです。
こんな経験はそうそう無いと思うので、忘れない内にメモしておきます。
※記載されている内容は、基本的に個人的な見解に基づくものであり、間違っているかもしれません。ご了承ください。
MySQLのバージョンを確認します。
# mysql --version
バージョンは5.5.29でした。
最新安定版なので、とりあえずこのままで行こうと思いました。
途中で行き詰まった時に5.6を試してみようかと少し考えましたが、
同時実行性が求められる内容ならともかく、今回のような単発の遅いSQLが劇的に早くなるとは思えなかったので、
結局最後までこのまま行きました。
環境を壊した場合は、インスタンスを初期化してもらうとして、
データやテーブルを戻したい時の念のためmysqldumpでバックアップをとります。
# mysqldump -uroot wikipedia > /tmp/wikipedia01.dump
テスト用のスクリプトを確認します。
RESET QUERY CACHE;
というクエリが先頭にあったので、
「クエリキャッシュは使えない」ということだけとりあえず把握しました。
サーバのスペック（特に大事なメモリ容量）を確認します。
# cat /proc/meminfo
なんと、メモリ容量15GB！！dumpファイルが1GBもなかったので、
メモリは潤沢すぎるほどに載っているということがわかりました。
テーブルの中身を見たり、クエリを流すのを楽にできるように、
phpMyAdminをインストールします。
# yum install php54 php54-mysqlnd
↑依存関係でApacheも入ります。
phpMyAdminは、手元にあった3.5.1を入れました。
最新版はコチラ
詳しくは後述します。
MySQLのチューニングと聞いた瞬間、真っ先にやろうとしたのが、
・・・ですが、
mysql > ALTER TABLE テーブル名 ENGINE = MEMORY;
を実行しようとした所、「BLOBまたはTEXT型のカラムがあるため変更できません」と怒られてしまいました・・・
MyISAMにしようかとも少しだけ思いましたが、
「MySQL5.5はInnoDBが劇的に進化していて、MyISAMと同じくらいか、クエリによってはむしろ早い」
という噂を聞いたことがあったので、慣れ親しんだInnoDBのままで行くことにしました。
ストレージそのものをオンメモリ化できないということで、
設定ファイルの内容↓
[mysqld] # Settings user and group are ignored when systemd is used. # If you need to run mysqld under different user or group, # customize your systemd unit file for mysqld according to the # instructions in http://fedoraproject.org/wiki/Systemd datadir=/var/lib/mysql #tmpdir=/dev/shm socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 character-set-server=utf8 skip-name-resolve innodb_buffer_pool_size = 4G innodb_log_buffer_size = 4M innodb_additional_mem_pool_size = 128M thread_cache_size = 10 innodb_log_file_size = 256M sort_buffer_size = 512M join_buffer_size = 128M read_buffer_size = 512M read_rnd_buffer_size = 512M max_allowed_packet = 16M table_open_cache = 2048 table_cache = 2048 max_sort_length = 12 max_heap_table_size = 2G tmp_table_size = 2G [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid character-set-server=utf8
割り当て量は、メモリが盛大に乗っているサーバだったため、
だいぶ適当です。
ポイントは以下のあたりかと思います。
それにしても割り当て過ぎな面がありますが、減らす調整をしても全く速くならなかったので途中で止めました。
| skip-name-resolve | コネクションオープンが遅くなるので、DNS逆引きによる名前解決を行わない |
|---|---|
| thread_cache_size | スレッド（コネクション）をキャッシュして接続のオーバヘッドを低減させる。今回は最大でローカルとオフィシャルの同時2接続だけなので少なくて良いが、確保はしておく。 |
| innodb_buffer_pool_size | InnoDBの一番大事なバッファ。全データが確実にのる分確保する。 |
| sort_buffer_size | 後半二つのSQLが、集計項目でORDER BYしており、インデックスが効かないので、使用されるテーブルがまるごと乗るくらい確保。 |
| read_buffer_size | 最後のSQLが、関数を噛ました項目でGROUP BYしており、インデックスが効かないので、フルスキャンが発生していると思われるので使用されるテーブルがまるごと乗るくらい確保。 |
| read_rnd_buffer_size | 後半二つのSQLが、GROUP BYで時間が掛かっていたので使用されるテーブルがまるごと乗るくらい確保。 |
| max_heap_table_size tmp_table_size |
後半二つのSQLが、GROUP BYを処理するための一時テーブルの作成に時間がかかっていたので、盛大に確保。 |
この時点では既存のインデックスの一部が効いていそうな部分はとりあえずノータッチとしました。
この時点で初めて計測した所、16～18秒程度だったと思います。
内訳は、
1•2番目→0.7-8秒
3番目→0.01秒
4•5番目→7-8秒
ちなみに、
GROUP BY、ORDER BY、一時テーブルの作成に時間が掛かっていると書いていますが、
これは、MySQLのプロファイリング機能の出力結果から判断しました。
この後はこの結果を持ってチューニングしています。
mysql > set profiling = 1; mysql > SQL実行; mysql > show profile;
以上を行うと、何に何秒（マイクロ秒まで）掛かった出力されます。
目立ったのは以下の３つでした。→全ての解説はこちら
| Sorting for order | ORDER BYの時間 |
|---|---|
| Creating tmp table | 一時テーブルの作成時間＝今回ではGROUP BYの時間に含まれる |
| Sending Data | 名前的には結果を送信する時間だが、集計関数の実行も含まれる（これは、ネットで調べて今回初めて知りました） |
実行結果から、1〜3番目はそれなりに十分に早かったので、
4•5番目を中心にチューニングすることにしました。
プロファイリングの結果では、
Creating tmp tableにほとんどの時間を費やしていたので、
まず、疑ったのが「一時テーブルがディスク上に作られてしまっている」ということです。
しかしこれは間違いで、いくら手を打っても改善されず、
後になってMySQLのパフォーマンス統計情報でディスク上に一時テーブルが作られた回数（Created tmp disk tables）というのを保持しているので、確認してみたら0回になっていました…
ちなみにこの情報は、phpMyAdminで簡単に見られます。
コマンドは…忘れましたw
ここでかなりの時間をロスしてしまいました。
たしか、３時過ぎくらいまで格闘していたと思います。
ダメ元で一時データ領域をtmpfsに載せてみたりという迷走もしましたw
先にパフォーマンス統計情報を確認するべきでした…
後半二つは、他に改善方法の検討がつかなかったので、
とりあえず、前半二つを少しでも早くすることにしました。
前半二つは、Sending Dataで時間がかかっており、
「こんな結果が小さい内容でそんなはずはない」と思ってネットで調べたら、
「集計関数の集計時間も含まれる」
とあったので、さらに集計関数の高速化方法を調べた所、
「COUNT(*)はインデックスカラムの中で最もサイズの小さいものを数えるため、カラム中で最もサイズの小さいものにインデックスを貼ると少し速くなる」
とあったので、実践したら0.4秒くらいになりました。
そして、「ここから先はインデックスの最適化しか道は無い！」と思い、インデックスの最適化にとりかかります。
ひたすら実行計画を取り、
使われていないインデックスの削除
および、
使用されているインデックスの最適化
を行いました。
最適化は、複合インデックスの使われていない項目を削って、
使われると思われる項目があれば追加していく流れで行いました。
この結果、合計で9.3〜10秒程度まで速度が上がりました。
ですが、残り一時間弱。既に8秒台の方が続々と出てきており、入賞には届かない状況でした。
手詰まり感を感じつつ、より適性化できるインデックスが無いかとphpMyAdminの画面とにらめっこしていました。
phpMyAdminで、インデックスの一覧と一緒にそのインデックスにおける一意な値の数が表示されるのですが、
revisonテーブルの結合キーと主キーの一意な値の数が一緒であることに気がついたのです。
MySQLのインデックスは、クラスタインデックスという方式をとっており、
主キーと一緒に実データが格納され、主キー以外のインデックスは主キーの値を持っており、
インデックスでの検索はインデックスで参照した後に実データを取るために主キーで参照しなおすため、
主キーとインデックスでは速度に差があるということは知っていたので、
ということで変更してみました。
結果！
後半の遅いSQLが2秒台にまで大幅に高速化され、
6.225秒（ローカルでは6.11秒まで出ました）というタイムで優勝することができました。
勢いで書いたので後ほど修正が入るかもしれません…ご了承をm(_ _)m
追記：