MySQL での最適化の制限

"

いよいよ 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 に変更します

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

#オフセット 10000 クエリ

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

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

Offset 340w query

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

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

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

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

分析ステートメントの説明

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

#ソート後のクエリ時間

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

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

Xu ソート ステートメント

この時点でわかることは、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>

MySQL での最適化の制限

从上图可以看到查询时间从0.8s优化到了0.4s，但是这样的效果还是不尽人意。

于是只能更换一下思路再进行优化。

MySQL での最適化の制限

既然优化最大偏移量这条路有点坎坷，能不能从其它方面进行入手。

估计有很多同学已经知道咔咔将要抛出什么话题了。

没错，就是使用where > id  然后使用limit。

先来测试一波结果，在写具体实现方案。

MySQL での最適化の制限

根据上图可以看到这种方式是十分可行的，分页在300W条数据以后的查询时间也基本忽略不计。

那么这种方案要怎么实现呢！

五、方案落地

其实这个方案真的很简单，只需要简单的转换一下思路即可。

いよいよ変更を行います

クライアントが初めてデータを取得するとき、offset パラメーターとlimit パラメーターは通常どおりに渡されます。

初めて返されるデータは、クライアントから渡されたオフセットと制限を使用して取得されます。

最初のデータが正常に返されたとき。

クライアントが 2 回目にデータを取得すると、パラメーターが変更され、オフセットや制限を行うことができなくなります。

このとき渡すパラメータは、最初に取得した最後のデータのidです。

この時のパラメータはlast_idとlimitです。

バックグラウンドで last_id を取得した後、SQL ステートメントで where 条件を使用できます

ここで示されている状況は、データがフラッシュバック中であるということです。 last_id より大きい、つまり Can。

次に、カカが事例を使って直接的かつ明確に説明します。

実践事例

以下は実際にpageとlimitを初めて使用してデータを取得する事例です。

返されたデータの最後の部分の ID は 3499984

##初めてのデータの取得

At今回は 2 番目のレコードを取得する場合、offset と limit を使用する代わりに、last_id とlimit を渡す必要があります。

以下に示すように

このとき、IDが最後のデータの最後のIDより小さいことを条件として、where条件を使用してデータを直接フィルタリングします。

#2 番目のデータの取得

時間比較

今、最後のデータを取得するとします

最適化前

#最適化前

#最適化後は、クエリ時間の変化が明確にわかります

最適化後のクエリ

6. まとめ 制限の最適化の概要を簡単に説明します。一言で言えば。

データ量が多い場合、オフセットが大きいほどクエリ時間が長くなるため、ページングにオフセットと制限を使用することはできません。
もちろん、すべてのページングが不可能であるとは言えませんが、データが数千、数万個しかない場合は問題なく、気軽に使用できます。
実装計画は Kaka の上のものです。初回は offset と limit を使用してデータを取得し、2 回目は where 条件を使用して最後の ID までのデータを取得します。最初のデータ。

「

学習の継続、ブログの継続、共有の継続は、Kaka がこの業界で働き始めて以来、常に堅持してきた信念です。巨大なインターネットにおけるカカの成功を願っています。この記事があなたに少しでも役立つことを願っています。私はカカです。また次回お会いしましょう。

”

以上がMySQL での最適化の制限の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。