MySQLの初回クエリの照会パフォーマンスを改善したい

Question

MySQLの初回クエリが遅い理由と改善方法がわかる方がいれば教示いただけますでしょうか。

下記、例に挙げるテーブルとSQLは一例で、他のテーブルでも同じことが発生しているのですが、SQL_NO_CACHEでクエリキャッシュを使わないようにしても、2回目以降が劇的に早くなります。
同じテーブルの照会でも検索条件（WHERE句）を変えると、また初回は遅くなり、2回目以降は早くなるという具合ですが、WHEREの内容は毎回変わり、初回が0.1sec程度のテーブルなら気にならないのですが、データが多くなるなどで1～2sec以上かかってくると問題がでてきて、どうにか初回から早くしたい、というのが要件なのですが、どのように対応したらよいのでしょうか。

＜tagテーブル＞
tag_id int
tag varchar(32)

＜tagmapテーブル＞
id int
tag_id int

のようなテーブルを作って、idに対してタグ付けを行おうとしています。
tagテーブル：100万レコード
tagmapテーブル：10億レコード
でテストデータを作成して、下記SQLを発行してみます。

#'タグ10001'の1回目
SELECT SQL_NO_CACHE id
FROM tagmap tm, tag t
WHERE tm.tag_id = t.tag_id
AND t.tag ='タグ10001';

10000 rows in set (1.79 sec)

#'タグ10001'の2回目
SELECT SQL_NO_CACHE id
FROM tagmap tm, tag t
WHERE tm.tag_id = t.tag_id
AND t.tag ='タグ10001';

10000 rows in set (0.01 sec)

#'タグ50001'の1回目
SELECT SQL_NO_CACHE id
FROM tagmap tm, tag t
WHERE tm.tag_id = t.tag_id
AND t.tag ='タグ50001';

10000 rows in set (1.63 sec)

#'タグ50001'の2回目
SELECT SQL_NO_CACHE id
FROM tagmap tm, tag t
WHERE tm.tag_id = t.tag_id
AND t.tag ='タグ50001';

10000 rows in set (0.01 sec)

のような状況です。

MySQLは5.6です。
物理的なデータファイルも同じものを参照しているはずなので、ディスクのキャッシュなどでもないかと考えているのですが、(再起動後などに)事前に何かクエリを発行してキャッシュを持ったり、バージョンアップなども含めて解決ができればと考えています。

Answer 1

2回目が早いのは1回目の結果を利用しているからです。
DBMSも、遅いならまだしも、早くて文句を言われる筋合いはないですし。
※追記
8.9.3.2 クエリーキャッシュ SELECT オプション
そもそもSQL_NO_CACHEの意図を取り違えています。
SQL_NO_CACHEはキャッシュの更新をしないという意味で、クエリーキャッシュを無効にするには、query_cache_size システム変数を 0 に設定することです。

解決すべきは1回目に遅い事で、これはチューニングするしかありません。

先ずは、実行計画の内容を確認しましょう。

実行結果からはインデックスは最低あるような気もしますので、以下のようなSQLに変更すると改善されるかもしれません。

-- 1
SELECT id FROM tagmap
where tag_id in (select tag_id from tag where tag ='タグ10001')

-- 2
SELECT mp.id 
FROM tagmap mp inner join tag tg 
     on mp.tag_id=tg.tag_id 
where tg.tag ='タグ10001'

質問では1万件ですが、実際にどの程度に絞り込まれるかによりますが、件数次第では必要なインデックスも変わってくると思います。

Comments

[yoshi123]

> 解決すべきは1回目に遅い事で、これはチューニングするしかありません。

まさにそうですね。

SELECT tag_id FROM tag WHERE tag='タグ100000';
が0.00secで返ってきて、

SELECT id FROM tagmap WHERE tag_id=xxxxx;
が1～2secなので、ここを改善できればと思います。
が、10億レコードのパフォーマンスとしてはいい方なのかもしれませんね。

> DBMSも、遅いならまだしも、早くて文句を言われる筋合いはないですし。
本番ではまずその通りなのですが、テストではけっこう厄介な問題と考えています。

1回目が通常の(本番同等の)パフォーマンスだとしたときに、テストで毎回WHERE句を変えないといけないという手間が発生し、また、たまたま他のテストで使ったキャッシュを使った誤った結果をパフォーマンスと判断してしまう懸念があります。
ですので1回目を改善したく、正確にパフォーマンスを見ていくのに、キャッシュを使ってほしくない、という状況です。

一方で、最終的な要件としては、本番が早いこと、なので、1回目の結果が妥当だとすれば、全てのパターンの照会結果をキャッシュに載せる、という案も考えており、その手順(再起動時の対応など)と必要なメモリ量も合わせて検討しています。

こちらは別件になりそうなので、別途質問を投稿させていただこうと思います。
もしお知恵があれば、お貸しいただけると助かります。

[yoshi123]

https://teratail.com/questions/173773
一応こちらに投稿しております。
お時間がありましたら目を通していただければ幸いです。
ありがとうございました。

[sazi]

select での SQL_NO_CACHEは意図した使い方ではありません。
回答に追記したので確認して下さい。

[sazi]

実行計画はどうなっているのでしょう？
テーブルスペースを割り当てている資源はどのようなものですか？（HDD？SSD？読み書きの性能は？）

Answer 2

初回の照会結果が現環境で妥当として、クラスター構成とパーティションを検討していくことになりました。
いろいろとアドバイスいただきスッキリしました。ありがとうございました。

Answer 3

no cache効かないって記事もありますね。
https://code.i-harness.com/ja/q/65b987

ということで２回目はcacheされたレコードが返ってきているかと思われます。

そもそもですが、
10億レコードのテーブルを対象にして
１万件２秒であればそこそこいいパフォーマスだと思います。

どのようなデータかによりますが、
10億レコードも積ませようとする設計に問題があると考えます。

どうしても10億レコード必要であれば、パーティショニングするなど、
物理設計時に別の方法を考えるべきです。

Comments

[yoshi123]

> no cache効かないって記事もありますね。

なるほど、そうなんですね。
テスト時には不便ですね、、、

今回のデータは、付与する対象のid(記事、のようなものです)が1000万レコード、各記事に自動で100タグ程度振られる想定で、タグマップを10億、としてテストを行っています。

タグのカテゴリーなどでタグマップをパーティションでN分割した場合、タグ→記事の検索(今の1～2sec)はN分の1になりそうですが、記事→付与されているタグ抽出、では全パーティションを見に行くことになるので、逆に相当に遅くなってしまいそうです。

同じレコードで2つのパーティションテーブルを作って、
タグ→記事用のテーブル(タグでパーティション分割とindex)と、
記事→タグ用のテーブル(記事でパーティション分割とindex)の2つを持つ、などもアリですかね。

同じ10億レコード×2テーブルなのでメンテナンスでトラブルが起こりそうですが、、、

もし同じレコードを重複して持つなら、記事側は記事テーブルにタグカラムを作って、"/"区切りなどで保持するのが現実的かもしれませんね。

ありがとうございました。

[sazi]

>szk.さん
引用されている記事は和訳でもあるし、原文をみるとno cacheを誤解して使用しているのが分かります。