質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.09%

事前に抽出条件を想定してクエリキャッシュをメモリに載せておく方法

解決済

回答 3

投稿

  • 評価
  • クリップ 1
  • VIEW 649

yoshi123

score 15

10億レコードのテーブルからの単純な抽出を検討しています。

<tagmapテーブル>
id int
tag_id int

idが振られている記事(1000万レコード)に対して1記事あたり100個のタグ付けを行っている想定のテーブルです。
タグ数(tag_id)は100万程度です。

SELECT id FROM tagmap WHERE tag_id=xxxxx;

の検索結果が、

初回
10000 rows in set (1.85 sec)

2回目
10000 rows in set (0.00 sec)

という具合で、キャッシュを使っていると思われる2回目が劇的に改善します。

WHERE句を変えると、やはり初回は1~2sec、2回目は0.00~0.01secで返ってきます。

初回のパフォーマンスのこれ以上のチューニングが難しく(パーティションなどが使いづらい)、何とか事前計算などでユーザーには最初から2回目のパフォーマンスを提供したいのですが、再起動時などに全ての抽出クエリのキャッシュをメモリに載せて、それを維持しておくことは可能でしょうか。
手順や必要なメモリ量の見積もりがわかる方がいれば教示いただけますでしょうか。

抽出パターンは単純なtag_idとして100万通りです。
(AND検索などはプログラムで絞り込む、または1~2secかけて抽出で構いません)

tagmapテーブルはファイルを見たところ約1GBのディスク量です。

サーバーのメモリは128GBなどは用意できるので、再起動時などに全てメモリに載せられれば、と考えています。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • maisumakun

    2019/02/11 11:12

    テーブルのインデックスはどのようになっていますか?(正しくかければ、1回目でも1秒もかからないかと思います)

    キャンセル

  • yoshi123

    2019/02/11 12:20

    id,tag_idの複合主キー、別途、tag_id→idでindexをつけています。

    show create table tagmap;
    +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table |
    +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | tagmap | CREATE TABLE `tagmap` (
    `id` bigint(20) NOT NULL,
    `tag_id` bigint(20) NOT NULL,
    PRIMARY KEY (`id`,`tag_id`),
    KEY `index_tag` (`tag_id`,`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
    +--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

    explain select * from tagmap where tag_id=10000;
    +----+-------------+--------+------+---------------+-----------+---------+-------+-------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+-------------+--------+------+---------------+-----------+---------+-------+-------+-------------+
    | 1 | SIMPLE | tagmap | ref | index_tag | index_tag | 8 | const | 10218 | Using index |
    +----+-------------+--------+------+---------------+-----------+---------+-------+-------+-------------+

    のような状況です。

    キャンセル

回答 3

+1

8.9.3 MySQL クエリーキャッシュ
上記は確認されていますか?

クエリーキャッシュは、あまり頻繁に変更されないテーブルがあり、それに対してサーバーが多くの同一のクエリーを受け取る環境で役立つことがあります。

上記のこの要件を満たしたものですか?10億件を想定したテーブルがそうであるようには思えません。
また、仮に10億件のデータを全てキャッシュに格納すれば、2回目の性能が出せるとは言い切れません。
逆に遅くなる場合もあります。

パフォーマンスのこれ以上のチューニングが難しく(パーティションなどが使いづらい)

出来ない理由は何でしょう?

バッチ処理でもないのに10億件中から1万件取り出す想定は何なのでしょう?

キャッシュ以前に、10億件の持ち方と取り出す単位など設計の見直しだと感じます。
キャッシュすることに意味が無い理由については、以下です。
8.9.3.1 クエリーキャッシュの動作

テーブルが変更された場合、そのテーブルを使用するキャッシュされたすべてのクエリーが無効になり、キャッシュから削除されます。これには、変更されたテーブルにマップされた MERGE テーブルを使用するクエリーも含まれます。テーブルは、INSERT、UPDATE、DELETE、TRUNCATE TABLE、ALTER TABLE、DROP TABLE、または DROP DATABASE などの多くの種類のステートメントによって変更できます。

頻繁に更新されるテーブルのキャッシュは意味がありません。

追記

tagmapの指すテーブルにtagmapの代わりにJson型でタグを持たせる方法だと、検索対象の件数自体を減らせるので効果があるかもしれません。
json型でもindexが貼りたい!
但し、扱えるのは5.7以降です。

それから、全文検索で検索対象として1万件想定していないなら、一定件数でページングするなどの仕組みでのSQLで検証すべきです。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/02/11 12:15

    なるほど、クエリーキャッシュがバイトごとの正確なマッチングでクエリーを同一判定してキャッシュを使うということだと、100万クエリーのテキストマッチングになるということですね。
    これではメモリがあってもそちらのオーバーヘッドの方が大きくなりそうですね、、、

    すみません、クエリーキャッシュのところはマニュアルを見ていませんでした。


    想定というのは1000万記事に対する全文検索です。
    一方で、各記事に対しては、付与されているタグを表示したいというものです。

    他の抽出条件があったり、分析に使っていることもあって、できればテキスト部分のデータもMySQLで一元的に扱いたい、という要件があります。

    MATCH~AGAINSTでテキスト部分の全文検索だと10~20secかかり現実的に実用に使うのは難しいため、タグ化しているものはタグでヒットさせることで、1~2秒にはできそう、というところが現状で、でももう1桁パフォーマンスを上げたい、と考えています。
    (タグ化されていないワードは仕方なくMATCH~AGAINSTで)

    検索ワードはAND検索になりますので、下記のようなSQLを想定しています(例えば3語で検索の場合)。

    SELECT tm.id
    FROM tagmap tm, tag t
    WHERE tm.tag_id = t.tag_id
    AND (t.tag IN ('タグ1', 'タグ2', 'タグ3'))
    GROUP BY tm.id
    HAVING COUNT( tm.id )=3;

    なので1万件を取り出すというよりは、基本的には複合ワードで10件だったり数百件に絞り込まれる想定です。
    それ以上はLIMITをかけるので、「10000件ヒットして全て取り出す」ということではありません。

    テーブルの更新は数も多いので日次バッチで考えています。


    タグをカテゴリーや付与件数などで付番してパーティションをtag_idで分けて10億レコードを分割する案も含めて検討していますが、、idから「記事に付与されているタグ」を抽出する時に全パーティションから抽出することになるので、逆にパフォーマンス悪化が懸念されます。

    ただ、パーティションでマッピングの抽出が改善できることは想定できるので、データは重複で持つことになるのでメンテナンス性との検討になりますが、記事テーブルに「付与タグ」カラムを設けて、"/"区切りなどでtag_id(s)を持たせる、というのが現実解なのかなと考えています。

    もう少しいくつか方法を試していきたいと思います。

    キャンセル

  • 2019/02/11 12:56 編集

    json型などについて追記しました。
    あり得ないことを検証しても、単にハードルを上げているだけですから。

    キャンセル

  • 2019/02/11 13:41

    json型は初めて知りました。
    これはタグ以外にも適用できそうです。
    全文検索もver5.7以降の方が早いようなのでバージョンアップ前提で試してみたいと思います。
    ありがとうございます。

    キャンセル

checkベストアンサー

0

キャッシュだけってのは多分簡単にはできません。そこで、キャッシュを持ち越す方法としては、
1.OSごと仮想化して仮想化環境の機能を使って再起動前の状態で続行する。

これだとMySQLの再起動時には意味がないので、
2.MySQL再起動時に想定したクエリを事前に発行する。

単に早ければいいなら、
3.もっと速い環境に変更する。(MEMORY ストレージエンジン,MySQL Clusterなど)

全て併用可能です。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/02/11 12:25

    用途からすると(基本的に永続データのため)、ストレージエンジンがメモリというのはナンセンスですが、マスターを持っておいて、メモリにレプリカのようなものを展開できると早いかもしれませんね。
    Clusterと合わせて検討してみます。

    ありがとうございます。

    キャンセル

  • 2019/02/14 23:00

    皆様からアドバイスいただき、今回は現実的な対応としてクラスター構成とパーティションを検討していくことになりました。
    HA構成はハードルが高いかと思っていたのですが、調べてみるとDBを4,5台持って交代でメンテナンスをするならクラスターで組んでしまっても運用できそうなので、検証していこうと思います。
    ありがとうございました。

    キャンセル

0

件数が増えてくるとカスタムでチューニングしない限り
実用に耐えないと思います。
それでもいくつか効果があるものとして以下試す価値があるかもしれません

  • パーティショニング
  • triggerで抽出条件単位で二重登録

ちなみにsetとfind_in_setの組み合わせで試してみましたがあまり
効率的にインデックスは使用されていなかったです

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/02/14 22:54

    ありがとうございます。
    皆様からアドバイスいただき、今回は現実的な対応としてクラスター構成とパーティションを検討していくことになりました。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.09%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる