MySQL: ページングの最適化

インタビューの質問、MySQL テーブルに大量のデータがある場合にページングを行う方法。 。 。 。当時はデータ量が多い場合にテーブルに分割できることだけは知っていましたが、テーブルを分割せずにどうすればいいのかわかりませんでした。 。 。 。悲しいことに、エージェントに要求したのは、ほんの数個のデータと、それを完全に保持するための単純な制限とオフセット (フェイス カバー) のみです。 。 。

多くのアプリケーションは、最新または最も人気のあるレコードのみを表示する傾向がありますが、古いレコードにもアクセスできるようにするには、ページング ナビゲーション バーが必要です。ただし、MySQL を介してページングをより適切に実装する方法は常に頭の痛い問題です。既製のソリューションはありませんが、データベースの基礎となるレイヤーを理解すると、ページ分割されたクエリを最適化するのに役立ちます。

パフォーマンスが低い、よく使用されるクエリを見てみましょう。

りー

このクエリには 0.00 秒かかります。では、このクエリの何が問題なのでしょうか?実際、このクエリ ステートメントとパラメータには問題はありません。これは、以下のテーブルの主キーを使用し、15 レコードのみを読み取るためです。

りー

本当の問題は、次のように、オフセット (ページング オフセット) が非常に大きい場合です。 レコードが 200 万行ある場合、上記のクエリには 0.22 秒かかります。EXPLAIN を通じて SQL 実行プランを表示すると、SQL が 100015 行を取得しましたが、最終的に必要なのは 15 行だけであることがわかります。ページング オフセットが大きいと使用されるデータが増加し、MySQL は最終的には使用されない大量のデータをメモリにロードします。ほとんどの Web サイト ユーザーがデータの最初の数ページにしかアクセスしないと仮定しても、ページ オフセットが大きい少数のリクエストがシステム全体に損害を与える可能性があります。 Facebook もこれを認識していますが、1 秒あたりにより多くのリクエストを処理するためにデータベースを最適化するのではなく、リクエストの応答時間の変動を減らすことに重点を置いています。

ページング リクエストの場合、レコードの総数という非常に重要な情報がもう 1 つあります。次のクエリを通じてレコードの合計数を簡単に取得できます。

りー

ただし、ストレージ エンジンとして InnoDB を使用する場合、上記の SQL には 9.28 秒かかります。間違った最適化は、SQL_CALC_FOUND_ROWS を使用することです。SQL_CALC_FOUND_ROWS は、ページング クエリ中に事前に条件を満たすレコードの数を準備し、select FOUND_ROWS() を実行するだけでレコードの合計数を取得します。ただし、ほとんどの場合、クエリ ステートメントが短くてもパフォーマンスが向上するわけではありません。残念ながら、このページング クエリ方法は多くの主流フレームワークで使用されています。このステートメントのクエリ パフォーマンスを見てみましょう。

りー

このステートメントには 20.02 秒かかり、前のステートメントの 2 倍の長さになります。ページングに SQL_CALC_FOUND_ROWS を使用するのは非常に悪い考えであることがわかりました。

最適化する方法を見てみましょう。この記事は 2 つの部分に分かれており、最初の部分ではレコードの総数を取得する方法が説明され、もう 1 つの部分では実際のレコードを取得する方法が説明されています。

行数を効率的に計算する

使用するエンジンが MyISAM の場合は、COUNT(*) を直接実行して行数を取得できます。同様に、ヒープ テーブルでは、行番号もテーブルのメタ情報に格納されます。ただし、エンジンが InnoDB の場合、InnoDB はテーブル内の特定の行数を保存しないため、状況はさらに複雑になります。

行数をキャッシュし、デーモン プロセスを通じて定期的に更新するか、一部のユーザー操作によってキャッシュが無効になった場合は、次のステートメントを実行します:

SELECT *
FROM city
ORDER BY id DESC
LIMIT 0, 15

レコードを取得

次に、この記事の最も重要な部分を入力し、ページネーションに表示するレコードを取得します。前述したように、オフセットが大きいとパフォーマンスに影響するため、クエリ ステートメントを書き直す必要があります。デモンストレーションのために、新しいテーブル「ニュース」を作成し、それを話題性順に並べ替え (最新リリースが一番上にあります)、高パフォーマンスのページングを実装します。簡単にするために、最新のニュース リリースの ID も最大であると仮定します。

りー

より効率的な方法は、ユーザーが最後に表示したニュース ID に基づく方法です。次のページをクエリするステートメントは次のとおりです。現在のページに表示されている最後の ID を渡す必要があります。

りー

前のページをクエリするステートメントは似ていますが、現在のページの最初の ID を渡す必要があることと、順序を逆にする必要がある点が異なります。

りー

上記のクエリ方法は、単純なページングに適しています。つまり、特定のページ ナビゲーションは表示されず、「前のページ」と「次のページ」のみが表示されます。たとえば、ブログのフッターには「前のページ」と「次のページ」が表示されます。 " ボタン。しかし、実際のページ ナビゲーションを実現することがまだ難しい場合は、別の方法を考えてみましょう。

りー

上記のステートメントにより、ページング ボタンごとにオフセットに対応する ID を計算できます。このアプローチには別の利点もあります。新しい記事が Web サイトに公開されていると仮定すると、すべての記事の位置が 1 つ後ろに移動するため、ユーザーが記事を公開するときにページを変更すると、記事が 2 回表示されることになります。各ボタンのオフセットIDを固定すればこの問題は解決します。 Mark Callaghan も同様のブログを公開しており、インデックスと 2 つの位置変数を組み合わせて使用​​していますが、基本的な考え方は同じです。

　　如果表中的记录很少被删除、修改，还可以将记录对应的页码存储到表中，并在该列上创建合适的索引。采用这种方式，当新增一个记录的时候，需要执行下面的查询重新生成对应的页号。

SET p:= 0;
UPDATE news SET page=CEIL((p:= p + 1) / $perpage) ORDER BY id DESC;

　　当然，也可以新增一个专用于分页的表，可以用个后台程序来维护。

UPDATE pagination T
JOIN (
   SELECT id, CEIL((p:= p + 1) / $perpage) page
   FROM news
   ORDER BY id
)C
ON C.id = T.id
SET T.page = C.page;

　　现在想获取任意一页的元素就很简单了：

SELECT *
FROM news A
JOIN pagination B ON A.id=B.ID
WHERE page=$offset;

　　还有另外一种与上种方法比较相似的方法来做分页，这种方式比较试用于数据集相对小，并且没有可用的索引的情况下—比如处理搜索结果时。在一个普通的服务器上执行下面的查询，当有2M条记录时，要耗费2sec左右。这种方式比较简单，创建一个用来存储所有Id的临时表即可(这也是最耗费性能的地方)。

CREATE TEMPORARY TABLE _tmp (KEY SORT(random))
SELECT id, FLOOR(RAND() * 0x8000000) random
FROM city;

ALTER TABLE _tmp ADD OFFSET INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, DROP INDEX SORT, ORDER BY random;

　　接下来就可以向下面一样执行分页查询了。

SELECT *
FROM _tmp
WHERE OFFSET >= $offset
ORDER BY OFFSET
LIMIT $perpage;

　　简单来说，对于分页的优化就是。。。避免数据量大时扫描过多的记录。

　　博客比较长，所以翻译的有些粗糙。。。，之后会在好好检查一遍的。在自己做测试时，有些查询时间与作者有点不一致，不过作者这篇博客是写于2011年的，so~不要在意具体数据，领会精神吧~~

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