MySQL ビッグデータ クエリ最適化エクスペリエンスの共有 (推奨)

本格的な MySQL 最適化!

MySQL のデータ量が少ない場合は最適化は不要です。データ量が多い場合は最適化が不可欠です。クエリが最適化されていない場合は 10 秒かかります。適切に最適化されている場合は、同じクエリに 10 ミリ秒かかります。

これは何と痛い認識でしょう！

プログラマーの用語で言うと、MySQL の最適化とは、インデックスの最適化と where 条件の最適化を意味します。

実験環境: MacBook Pro MJLQ2CH/A、MySQL 5.7、データ量: 212万以上

1つ：

 記事から*を選択
 内部結合（
 選択ID
 記事より
 どこ
  長さ(content_url) > 0 かつ
  (id = article.source_idのソースからステータスを選択)=1 かつ
  (id = article.category_idのカテゴリからステータスを選択) = 1 かつ
  ステータス = 1 かつ ID < 2164931
 スティック降順、pub_time降順で並び替え
 制限 240,15
 ) として
USING(id);

一見すると、上司は間違いなく私を殺したいと思うでしょう。なぜ自己関連付けや内部結合を行う必要があるのでしょうか? XX 階の皆さん、肉切り包丁を持ってきてください。ブロガーを殺したいんです! ！ ！

正直に言うと、朝出かけるときに頭がドアに押し付けられることはなかったので、そんなことは起きてほしくなかったのです。

1. データ量が多い場合は、大きなオフセットでページング クエリを実行する必要があります。これにより、クエリの速度が大幅に向上します。その理由は、結合サブテーブルで ID を使用してテーブル全体をカバーし、完全なテーブル スキャンを回避するためです。

私の order by を見てください (ささやく: これは単なる order by です。誰が書けるでしょうか)。この order by をフィールド desc に置き換えるか、独自のテーブルで説明して、何が起こるかを確認してください。追加 ---> filesort ! くそっ !

2. 複数の条件を持つこのような order by の場合、通常は 2 つのフィールドにそれぞれインデックスを直接追加しますが、Extra ---> filesort も使用します。別のアプローチとして、order by の後のすべての条件に結合インデックスを追加します。順序は order by の順序と一致している必要があることに注意してください。こうすれば、Extra に残されたのはどこなのかだけです。

where，(select status from source where id = article.source_id)=1 and ...これはなんと奇妙な書き方でしょう!

3. join+index方式を検討しましたが、試してみたところ、この方式とほぼ同じであることがわかりました。本番環境はこのように書かれているので、そのままにしておきましょう。また、2 つのインデックス (source_id、category_id) を保存できます。怠惰を止めることは誰にもできません。将来損失が発生した場合は、戻って最適化を続けることができます。

4. 昨晩、この点に気付きました。where 条件を満たす順序は、右から左の順に、最後の条件を最初に満たすというものです。テストのためにインデックスを削除したところ、確かに効果があり、時間が 6 秒から 4 秒に短縮されました。インデックスを最適化した後、再度テストしたところ、順序による時間消費への影響は 0.X ミリ秒とほとんど無視できることがわかりました。

二：

 記事から*を選択
 内部結合（
 SELECT id FROM article WHERE INSTR(ifnull(title,''),'战狼') > 0 かつ status != 9
 pub_time 降順で並び替え
 制限 100,10

 ) は t として扱われます (id);

うーん――また内部結合か……。

INSTR(ifnull(title,''),'Wolf Warrior') > 0 のように使用しないのはなぜでしょうか......

1. 管理プラットフォームでの検索であるため、検索エンジンで検索されていません。検索エンジンは 1 時間に 1 回しかデータを同期しないため、データが不完全です。検索する場合、管理者は必要な結果のみを気にします。たとえば、%XX% はインデックスを使用できず、効率は instr の 5 倍低くなります。また、正規表現 '.*XX*.' もテストしましたが、それでも instr よりも少し時間がかかります。つまり...

desc または explain、filesort.....pub_time にインデックスを追加して動作するかどうか確認するか、filesort...

2. この状況には別の解決策があり、 SELECT id FROM article force index(pub_time)てこのインデックスの使用を指定します。しかし、この書き方は柔軟性に欠けるので、やめましょう。 Baidu で検索したところ、ある人からアドバイスをもらいました。ステータスと pub_time の結合インデックス (pub_time_status、先頭に順序条件) を作成し、where クエリを実行するときにこのインデックスを自動的に強制します。

三つ：

status != 9 の場合に article から * を選択し、 pub_time で並べ替え、 desc limit 100000,25 を指定します。
desc または explain、または filesort..... 以前に status と pub_time の共同インデックスを作成しませんでしたか? 理由を教えてください...

まあ、私もわかりません。status と pub_time の別の結合インデックスstatus_pub_timeを作成します。今回は where 条件が前にあり、explain に filesort はありませんが、このインデックスは使用されませんpub_time_statusをフックします。理解できない

同時に、TWO の SQL についても説明しました。これは次のようになります。

これら 2 つのうちいずれか 1 つを削除しても機能しません。 いずれか 1 つを削除すると、SQL はファイル ソートを実行します。

4:

フォローから*を選択
 ここで、(((SELECT status FROM source WHERE id=follow.source_id)=1 and follow.type=1) または ((select status from topic WHERE id=follow.source_id)=1 and follow.type=2)) AND user_id=10054
 ORDER BY ソート制限 15,15;
 SELECT * from follow inner join(
 フォローからIDを選択
 ここで、(((SELECT status FROM source WHERE id=follow.source_id)=1 and follow.type=1) または ((select status from topic WHERE id=follow.source_id)=1 and follow.type=2)) AND user_id=10054
 ORDER BY ソート制限 15,15
 ) を t として使用します (id);
 (SELECT id、source_id、user_id、temporary、sort、follow_time、read_time、type from follow where (SELECT status FROM source WHERE id=follow.source_id)=1 and follow.type=1 and user_id=10054)
 すべて結合
 (SELECT id、source_id、user_id、temporary、sort、follow_time、read_time、type from follow where (select status from topic WHERE id=follow.source_id)=1 and follow.type=2 and user_id=10054) を実行します。
 ORDER BY ソート制限 15,15;

これら 3 つの SQL ステートメントを見てください。興味深いですね。

公平を期すために、インデックス user_id_sort(user_id,sort) を最適化し、where が user_id を使用してこのインデックスを強制するようにしました。

最初の文: 0.48ms

2番目の文: 0.42ms

3 番目の文: 6 ミリ秒。時間がかかる理由は、結合 (テーブルを 2 回クエリしてサブテーブルにマージする) の後、インデックスを使用して order by の並べ替えをカバーできないためです。

場合によっては、union が or よりも必ずしも高速であるとは限りません。

要約する

上記は、編集者が共有したMySQLビッグデータクエリ最適化の経験です。皆様のお役に立てれば幸いです。ご質問がある場合は、メッセージを残してください。編集者がすぐに返信します。また、123WORDPRESS.COM ウェブサイトをサポートしてくださっている皆様にも感謝申し上げます。