"
いよいよ MySQL の最適化を開始する必要があります。この記事ではページングの最適化について説明します。あなたに合ったソリューションが得られることを願っています。
"

まえがき

ページングの話題はすでに決まり文句になっていますが、どれだけの友人が最適化を望んでいるのか一方で、私自身のシステムに関しては、独自の個性を維持しています。

最適化には自ら率先してテストデータを取得する必要があります。テストの途中でのみ発見が得られます。 . あなたの知らないこと。

この記事では、Kaka がページングの最適化についても説明します。

1. テーブル構造

このデータベース構造は、Kaka の現在のオンラインプロジェクトのテーブルです。フィールド名を変更し、時間フィールドをキャンセルしました。

データベース構造は次のとおりです

<span style="display: block; background: url(https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;"></span><code class="hljs" style="overflow-x: auto; padding: 16px; color: #abb2bf; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; font-size: 12px; -webkit-overflow-scrolling: touch; padding-top: 15px; background: #282c34; border-radius: 5px;"><span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">CREATE</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">TABLE</span> <span class="hljs-string" style="color: #98c379; line-height: 26px;">`tp_statistics`</span> (<br/>  <span class="hljs-string" style="color: #98c379; line-height: 26px;">`ss_id`</span> <span class="hljs-built_in" style="color: #e6c07b; line-height: 26px;">int</span>(<span class="hljs-number" style="color: #d19a66; line-height: 26px;">11</span>) <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">NOT</span> <span class="hljs-literal" style="color: #56b6c2; line-height: 26px;">NULL</span> AUTO_INCREMENT,<br/>  <span class="hljs-string" style="color: #98c379; line-height: 26px;">`ss_field1`</span> <span class="hljs-built_in" style="color: #e6c07b; line-height: 26px;">decimal</span>(<span class="hljs-number" style="color: #d19a66; line-height: 26px;">11</span>,<span class="hljs-number" style="color: #d19a66; line-height: 26px;">2</span>) <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">NOT</span> <span class="hljs-literal" style="color: #56b6c2; line-height: 26px;">NULL</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">DEFAULT</span> <span class="hljs-string" style="color: #98c379; line-height: 26px;">&#39;0.00&#39;</span>,<br/>  <span class="hljs-string" style="color: #98c379; line-height: 26px;">`ss_field2`</span> <span class="hljs-built_in" style="color: #e6c07b; line-height: 26px;">decimal</span>(<span class="hljs-number" style="color: #d19a66; line-height: 26px;">11</span>,<span class="hljs-number" style="color: #d19a66; line-height: 26px;">2</span>) <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">NOT</span> <span class="hljs-literal" style="color: #56b6c2; line-height: 26px;">NULL</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">DEFAULT</span> <span class="hljs-string" style="color: #98c379; line-height: 26px;">&#39;0.00&#39;</span>,<br/>  <span class="hljs-string" style="color: #98c379; line-height: 26px;">`ss_field3`</span> <span class="hljs-built_in" style="color: #e6c07b; line-height: 26px;">decimal</span>(<span class="hljs-number" style="color: #d19a66; line-height: 26px;">11</span>,<span class="hljs-number" style="color: #d19a66; line-height: 26px;">2</span>) <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">NOT</span> <span class="hljs-literal" style="color: #56b6c2; line-height: 26px;">NULL</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">DEFAULT</span> <span class="hljs-string" style="color: #98c379; line-height: 26px;">&#39;0.00&#39;</span>,<br/>  PRIMARY <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">KEY</span> (<span class="hljs-string" style="color: #98c379; line-height: 26px;">`ss_id`</span>)<br/>) <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">ENGINE</span>=<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">InnoDB</span> AUTO_INCREMENT=<span class="hljs-number" style="color: #d19a66; line-height: 26px;">3499994</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">DEFAULT</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">CHARSET</span>=utf8 <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">COLLATE</span>=utf8mb4_general_ci ROW_FORMAT=<span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">COMPACT</span>;<br/><br/></code>

ログイン後にコピー

上記の情報によると、現在 3.5 が存在することがわかります。次に、これらの 350W レコードのクエリを最適化します。

2. クエリ効率の予備調査

まずクエリ SQL ステートメントを作成し、クエリにかかる時間を見てみましょう。クエリ。

下の図によると、クエリ時間は基本的に無視されていることがわかりますが、注目すべきは制限のオフセット値です。

したがって、オフセットを段階的に増やしてからテストする必要があります。最初にオフセットを 10000 に変更します

クエリ時間が依然として非常に理想的であることがわかります。

時間を節約するために、オフセット値を 340W に直接調整します。

この時点で、非常に明らかな変化が見られ、クエリ時間が 0.79 秒に急増しています。

このような状況が発生した場合は、必ず最適化する必要があります。キーボードを手に取り、実行してください。

3. 分析クエリに時間がかかる理由

SQL ステートメントを分析する場合に必要な知識ポイントこのツールの使い方がわからない場合は、MySQL の基本的な部分を見てください。

下の図に示すように、3 つのクエリステートメントすべてがテーブルスキャンを受けていることがわかります。

ページングがある限り、並べ替えが必要であることは誰もが知っているので、並べ替えを追加してクエリの効率を確認します。

次に、ソートされたステートメントを分析して表示します。

ここで、並べ替えが使用されている場合、データベースによってスキャンされる行数は、オフセットに必要なクエリ数を加えたものであることがわかります。

この時点でわかることは、3400000,12 を超える場合の制限のように、オフセットが非常に大きい場合であるということです。このようなクエリ。

現時点では、MySQL は 3400012 行のデータをクエリし、最後の 12 個のデータを返す必要があります。

以前にクエリされた 340W データは破棄されます。このような実行結果は、私たちが望むものではありません。

Kaka は以前、この問題の解決策はページング数を直接制限するか、オフセットが非常に大きい場合のパフォーマンスを最適化することであると述べた関連記事を目にしました。

この記事をここまで読んだあなたは、どうしてがっかりするでしょうか? それは、大きなオフセットを最適化する際のパフォーマンスの問題に違いありません。

4. 最適化

最適化といっても、インデックスの追加と他のプログラムの利用の2点だけです。このプログラムを置き換えるために。

Kaka が提供するデータテーブル構造情報は、図書館の貸出記録として完全に理解できるため、フィールドについては何も心配する必要はありません。

並べ替えの場合、このシナリオでは時間は並べ替えられませんが、主キーは並べ替えられ、テストデータの追加により時間フィールドはキャンセルされます。

接下来使用覆盖索引加inner join的方式来进行优化。

<span style="display: block; background: url(https://files.mdnice.com/point.png); height: 30px; width: 100%; background-size: 40px; background-repeat: no-repeat; background-color: #282c34; margin-bottom: -7px; border-radius: 5px; background-position: 10px 10px;"></span><code class="hljs" style="overflow-x: auto; padding: 16px; color: #abb2bf; display: -webkit-box; font-family: Operator Mono, Consolas, Monaco, Menlo, monospace; font-size: 12px; -webkit-overflow-scrolling: touch; padding-top: 15px; background: #282c34; border-radius: 5px;"><span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">select</span> ss_id,ss_field1,ss_field2,ss_field3 <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">from</span> tp_statistics <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">inner</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">join</span> ( <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">select</span> ss_id <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">from</span> tp_statistics <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">order</span> <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">by</span> ss_id <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">limit</span> <span class="hljs-number" style="color: #d19a66; line-height: 26px;">3000000</span>,<span class="hljs-number" style="color: #d19a66; line-height: 26px;">10</span>) b <span class="hljs-keyword" style="color: #c678dd; line-height: 26px;">using</span> (ss_id);<br/></code>

ログイン後にコピー