SQL最適化のブログ事例

問題の背景：ブログのトップページはデータ量の増加に従って、最初は何百何千のデータで、正常に訪問します。これは開発環境です。
問題SQL:

SELECT
DISTINCT post.`ID` AS postId,post.`post_title`,post.`post_content`,post.`post_excerpt`,u.`display_name`,
(SELECT IFNULL(GROUP_CONCAT(term.name),'') FROM wp_term_relationships AS r LEFT JOIN wp_term_taxonomy AS t ON(r.term_taxonomy_id = t.term_taxonomy_id) LEFT JOIN wp_terms AS term ON(term.term_id = t.term_id)
WHERE r.object_id = post.ID AND t.taxonomy = 'category') AS categoryName,
(SELECT IFNULL(GROUP_CONCAT(term.name),'') FROM wp_term_relationships AS r LEFT JOIN wp_term_taxonomy AS t ON(r.term_taxonomy_id = t.term_taxonomy_id) LEFT JOIN wp_terms AS term ON(term.term_id = t.term_id)
WHERE r.object_id = post.ID AND t.taxonomy = 'post_tag') AS tagName,
post.`comment_count`,post.`post_status`,post.`post_date`
FROM wp_posts AS post
LEFT JOIN wp_users AS u ON(post.`post_author` = u.`ID`)
LEFT JOIN wp_term_relationships AS relation ON(relation.`object_id` = post.`ID`)
LEFT JOIN wp_term_taxonomy AS taxonomy ON(taxonomy.`term_taxonomy_id` =relation.`term_taxonomy_id`)
LEFT JOIN wp_terms AS term ON(term.`term_id` = taxonomy.`term_id`)
WHERE post.`post_type` = 'post'
AND post.`post_status` IN ('publish')
ORDER BY post.`post_date` DESC
LIMIT 0,10

このsqlをsqlyogに入れて実行すると、以下のような時間がかかります。実行：59.204 sec総数：59.239 10行（10本のデータのみを表示します。）
最適化後のSQL:

SELECT
            DISTINCT post.`ID` AS postId,post.`post_title`,post.`post_content`,post.`post_excerpt`,u.`display_name`,
            (SELECT IFNULL(GROUP_CONCAT(term.name),'') FROM wp_term_relationships AS r LEFT JOIN wp_term_taxonomy AS t ON(r.term_taxonomy_id = t.term_taxonomy_id) LEFT JOIN wp_terms AS term ON(term.term_id = t.term_id)
            WHERE r.object_id = post.ID AND t.taxonomy = 'category') AS categoryName,
            (SELECT IFNULL(GROUP_CONCAT(term.name),'') FROM wp_term_relationships AS r LEFT JOIN wp_term_taxonomy AS t ON(r.term_taxonomy_id = t.term_taxonomy_id) LEFT JOIN wp_terms AS term ON(term.term_id = t.term_id)
            WHERE r.object_id = post.ID AND t.taxonomy = 'post_tag') AS tagName,
            post.`comment_count`,post.`post_status`,post.`post_date`
            FROM ( SELECT * FROM wp_posts WHERE `post_type` = 'post'  AND `post_status` IN ('publish') ORDER BY  `post_date` DESC LIMIT 0,10 ) AS post
            LEFT JOIN wp_users AS u ON(post.`post_author` = u.`ID`)
            LEFT JOIN wp_term_relationships AS relation ON(relation.`object_id` = post.`ID`)
            LEFT JOIN wp_term_taxonomy AS taxonomy ON(taxonomy.`term_taxonomy_id` =relation.`term_taxonomy_id`)
            LEFT JOIN wp_terms AS term ON(term.`term_id` = taxonomy.`term_id`)

このsqlをsqlyogに入れて実行した結果、以下のように時間がかかりました。実行：0.056 sec総数：0.888 sec 10行（10本のデータのみを表示します。）
最適化後、直接ミリ秒レベルです。結果項目はテスト環境でカードにアクセスしませんでした。
主な変更はクエリとフィルタ条件を一番後ろからメインテーブルのサブクエリに埋め込みます。
問題SQLはなぜこんなに遅いですか？最適化されたSQLはなぜ急にミリ秒に達するのですか？
まず問題を見ます。なぜ問題SQLはこんなに遅くなりますか？問題SQL：

SELECT
          DISTINCT post.`ID` AS postId,post.`post_title`,post.`post_content`,post.`post_excerpt`,u.`display_name`,
          (SELECT IFNULL(GROUP_CONCAT(term.name),'') FROM wp_term_relationships AS r LEFT JOIN wp_term_taxonomy AS t ON(r.term_taxonomy_id = t.term_taxonomy_id) LEFT JOIN wp_terms AS term ON(term.term_id = t.term_id)
          WHERE r.object_id = post.ID AND t.taxonomy = 'category') AS categoryName,
          (SELECT IFNULL(GROUP_CONCAT(term.name),'') FROM wp_term_relationships AS r LEFT JOIN wp_term_taxonomy AS t ON(r.term_taxonomy_id = t.term_taxonomy_id) LEFT JOIN wp_terms AS term ON(term.term_id = t.term_id)
          WHERE r.object_id = post.ID AND t.taxonomy = 'post_tag') AS tagName,
          post.`comment_count`,post.`post_status`,post.`post_date`
          FROM wp_posts AS post
          LEFT JOIN wp_users AS u ON(post.`post_author` = u.`ID`)
          LEFT JOIN wp_term_relationships AS relation ON(relation.`object_id` = post.`ID`)
          LEFT JOIN wp_term_taxonomy AS taxonomy ON(taxonomy.`term_taxonomy_id` =relation.`term_taxonomy_id`)
          LEFT JOIN wp_terms AS term ON(term.`term_id` = taxonomy.`term_id`)
          WHERE post.`post_type` = 'post'
          AND post.`post_status` IN ('publish')
          ORDER BY post.`post_date` DESC
          LIMIT 0,10

プログラムは上から下まで、左から右に実行して、先にSELECTは更にLEFT JINの多くの時計、最後に更にWHEREとORDER BYとLIMIT、どのように見ても大丈夫で、しかし実際にはとても問題があります。
問題の分析？

まず、SELECT*を使わずに必要なフィールドを列挙します。

explayinのキーワードで問題SQLを見た結果、typeフィールドを中心に表スキャン方式：system>const>eq_ref>ref>range>index>allは、一番遅いのはallで、つまり全表スキャンです。

最適化後のSQLを見てください。
それらをよりよく比較するためには、どのような違いがありますか？explinがパラメータを取得する意味を理解する必要があります。
explinキーワードの意味
（1）id
MySQL QueryOptimizer選択された実行計画で照会されたシリアルナンバーは、クエリーでselectサブ句または操作表の順序を実行することを表します。ID値が大きいほど優先度が高く、優先度が高いほど先に実行されます。idは同じで、実行順序は上から下までです。
(2)select_タイプ

SIM PLE（簡単なselectクエリ（unionおよびサブクエリを使用しない）

PRIMARY（最外層のselectクエリ、2つのテーブルが存在すればクエリー、外部層のテーブル操作はPRIMARY、内部層（サブクエリ）の操作はSUBQUERY）

SUBQUERY（サブクエリで最初のSELECT（複数のサブクエリが存在する場合）は、外部層のテーブルに依存しません。fromサブフレーズに含まれるサブクエリの他に、他のところで発生するサブクエリはSUBQULYかもしれません。／DEPENDENT SUBQULY（サブクエリで最初のSELECT（複数のサブクエリが存在する場合）は、459167に依存します。
（3）テーブル
行参照の表を出力します。表示されたクエリーテーブル名は、エイリアスが使用されている場合、ここに別名が表示され、データテーブルの操作に関与していない場合は、nullと表示され、カギカッコで囲まれていると表示されると、これは臨時表であり、後のNは計画を実行するidであり、その結果はこのクエリから生じることを示しています。山括弧で囲まれている場合、これと同様に、一時テーブルでもあり、この結果はunionクエリからのidがMであり、Nの結果集であることを示しています。
(4)type
優から差までの順番は以下の通りです。system->const->eq_ref->ref->fulltext->ref_or_null->index_merge->unique_subquery->index_subquery->range->index->allは一般的に、開発者が書いたSQLの基本的な要求はeq_である。refレベル
（5）possible_keys
この表で使用できるインデックスが検索に役立ちます。使用可能なインデックスを調べたらここに並べます。空の場合は、使用可能な索引がないことを示します。
（6）key
実際にpossibleからキーは使用するインデックスを選択します。nullであればインデックスは使用されません。select_.typeはindex_であるmergeの場合、ここには二つ以上の索引があります。他のselect(u)typeここには一つしか現れません。少ない場合、MySQLは最適化不足インデックスを選択します。この場合、USE＿INDEXをSELECT文で強制的に使用したり、IGNORE＿INDEXを使って、MySQLがインデックスを無視するよう強制したりすることができます。
（7）key_len
クエリーを処理するためのインデックス長は、精度を失わない場合は、長さが短いほど良いです。単一の列の索引であれば、インデックス全体の長さを計算します。複数の列の索引であれば、検索は必ずしもすべての列に使えるとは限りません。具体的に何列の索引を使用していますか？ここで計算します。使用していない列はここで計算されません。key_lenはwhere条件で使用されるインデックス長だけを計算します。並べ替えとグループはインデックスを使用しても、ken_まで計算されません。len中です
（8）ref
インデックスを表示するどの列が使用されますか？使用する定数等値クエリーの場合は、ここでconstが表示されます。クエリーを接続すると、駆動テーブルの実行計画はここで駆動テーブルの関連フィールドが表示されます。条件が表式または関数を使用すると、条件列に内部暗黙的な変換が発生し、ここでfuncが表示されるかもしれません。
（9）ローソン
チェックしなければならないと考えられているのは、要求データのライン数を返すためのスキャンが必要な回数です。
（10）extra
この列で表示できる情報は、Using filesort、Using temporaryの2つがあるとインデックスが使えないという意味で、効率に大きな影響を与えます。できるだけそれを最適化するべきです。

distinct：select部分にdistinctキーワードを使用した

using filesort：ソート時にインデックスに使用できない場合、これが発生します。order byとgroup by文でよく見られます。既存のインデックスを利用して並べ替えることはできません。追加の並べ替えが必要です。順序付けの必要に応じて適切なインデックスを作成します。

using index：クエリーに戻る必要はなく、直接索引で照会データを取得することができます。カバーインデックスを利用して、表に戻らずに結果データを取得することができます。この結果が良いです。

using temporay：一時テーブルを使って中間結果を記憶していることを示します。

using where：エンジンが戻ってきた記録はすべて検索条件を満たしているわけではなく、server層でフィルタリングする必要があります。

explinを理解した後、この問題SQL：すなわちkey、type、rows、extraをexplinの重要なパラメータで説明します。その中のローソンを見ると、このセグメントsqlは実行開始時に直接28386回スキャンした。最適化されたSQLは10回しかスキャンされないので、この場所では遅いと分かります。これは問題SQLが遅い根本的な原因です。
最後に、最適化されたSQLはまだ冗長です。トップページとして展示されているので、実際にはこんなに多くのテーブルの関連は必要ないです。詳細を見るなら、分解してから分割して実行してもいいです。
最終トップページのSQLは以下の通りです。

SELECT
DISTINCT post.`ID` AS postId,post.`post_title`,post.`post_content`,post.`post_excerpt`,
(SELECT `display_name` FROM wp_users WHERE ID = post_author) AS display_name, 
post.`comment_count`,post.`post_status`,post.`post_date` 
FROM  wp_posts AS post
WHERE `post_type` = 'post'  AND `post_status` IN ('publish')
ORDER BY  `post_date` DESC
LIMIT 0,10

また、ファイリングクエリもこのSQLを使っています。これによって最適化が必要です。そこで、私はそれを別のDAOに分離して、性最適化を行います。
問題SQL（ファイリング、消耗8.183 sec）：

SELECT
            DISTINCT post.`ID` AS postId,post.`post_title`,post.`post_content`,post.`post_excerpt`,u.`display_name`,
            post.`comment_count`,post.`post_status`,post.`post_date`
            FROM ( SELECT * FROM wp_posts WHERE `post_type` = 'post'  AND `post_status` IN ('publish') 
AND DATE_FORMAT(`post_date`, '%Y %m ') = '2020 06 '            
            ORDER BY  `post_date` DESC LIMIT 0,10 ) AS post
            LEFT JOIN wp_users AS u ON(post.`post_author` = u.`ID`)

問題SQLは再度最適化された（今回の実行時間は55.966 sec）：

SELECT
            DISTINCT post.`ID` AS postId,post.`post_title`,post.`post_content`,post.`post_excerpt`,u.`display_name`,
            post.`comment_count`,post.`post_status`,post.`post_date`
            FROM ( SELECT * FROM wp_posts WHERE `post_type` = 'post'  AND `post_status` IN ('publish') 
AND DATE_FORMAT(`post_date`, '%Y %m ') = '2020 06 '            
            ORDER BY  `post_date` DESC LIMIT 0,10 ) AS post
            LEFT JOIN wp_users AS u ON(post.`post_author` = u.`ID`)

最終最適化版：

SELECT
           DISTINCT post.`ID` AS postId,post.`post_title`,post.`post_content`,post.`post_excerpt`,
           (SELECT `display_name` FROM wp_users WHERE ID = post_author) AS display_name,
            post.`comment_count`,post.`post_status`,post.`post_date` 
           FROM wp_posts AS post WHERE ID IN 
           (SELECT `ID` FROM wp_posts  WHERE `post_type` = 'post' AND `post_status` IN ('publish') AND DATE_FORMAT(`post_date`, '%Y %m ') =  '2020 06 '  ORDER BY `post_date` DESC)
           LIMIT 0,10

この最適化バージョンは、アーカイブを抽出してサブクエリ条件としてIDを検索し、並べ替えて取得するという考えです。IDを取得するこのSQLはミリ秒レベルで、それから外部層LIMITでも大丈夫です。
共通の法則と方法

SQLの進行と遅い原因（explinのidはsqlがどのように行われているかを知ることができる）をexplinキーワードで理解する

分割を習得し、2つに分割して書くことができる（記事の詳細を見る場合は、2つの部分に分けられ、一部は詳細を得るため、もう一部は文章に対応する分類やラベルを取得することで、sqlは基本的にequrefレベル、ミリ秒レベルとして確保できる）

サブクエリを合理的に使用する（例えば、ファイリングの部分のIDを調べ、取得したIDをwhereクエリ条件として持ち、背後の原理はキーインデックスに沿っているが、なぜメインキーインデックスが早いのかは、メインキーインデックスが通常の索引よりも速いからである）

上記の方法を例にとって、データ量が大きいページの性能問題を解決します。（ブログシステムが最後のページをクリックして、スロー問題をロードします。本質的にはSQLの原因です。最適化した後、主キーインデックスを利用します。）、最適化されたコードは以下の通りです。

SELECT DISTINCT post.`ID` AS postId,post.`post_title`,post.`post_content`,post.`post_excerpt`,
(SELECT `display_name` FROM wp_users WHERE ID = post_author) AS display_name, post.`comment_count`,post.`post_status`,post.`post_date` 
FROM wp_posts AS post JOIN (SELECT ID FROM wp_posts WHERE `post_type` = 'post' AND `post_status` IN ('publish') LIMIT 2340,10) AS post_b ON(post.ID = post_b.ID) 
WHERE `post_type` = 'post' AND `post_status` IN ('publish')  ORDER BY `post_date` DESC

FAQ
なぜSQLクエリが遅いですか？
通常は次のようにまとめることができます。

インデックスなしまたはインデックスなし

I/Oスループットが小さい

メモリ不足

ネットワーク速度が遅い

検索したデータ量が多すぎる

ロックまたはデッドロック

不要な行と列を返す

今回の例では、トップページが遅いのは、最初のSQLスキャンの行数が大きいからです。スキャンが終わったら、関連表を調べてください。
最適化された後のスキャン行数は10行だけで、またサブクエリに関連します。
両者の違いは前者が全部スキャンしてもう一度関連する再条件で、後者は直接条件によって濾過してから関連します。
サブクエリの実行過程は何ですか？
内向的に外に処理して、本文の挙げるトップページの文章の最適化の語句に対応します。
なぜSQLは単一のフィールドを調べてもページに分けずに六万本のデータを使っていますか？時間はミリ秒です。
IOの消費（入出力）のため、出力データ量が大きいとスループットが小さい（スループットはディスク、CPU、メモリに関連）。