MySQL ページング パフォーマンス最適化ガイド

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

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

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

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

CREATE TABLE city (
  id int(10) unsigned NOT NULL AUTO_INCREMENT,
  city varchar(128) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

本当の問題は、次のようにオフセット (ページング オフセット) が非常に大きい場合です。

SELECT *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;

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

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

SELECT COUNT(*)
FROM city;

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

SELECT SQL_CALC_FOUND_ROWS *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;

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

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

行数を効率的にカウントする

使用するエンジンがMyISAMの場合は、COUNT(*)を直接実行して行数を取得できます。同様に、ヒープ テーブルでは、行番号もテーブルのメタ情報に格納されます。ただし、エンジンが InnoDB の場合、InnoDB はテーブル内の特定の行数を保存しないため、状況はさらに複雑になります。
行数をキャッシュし、デーモンプロセスを通じて定期的に更新するか、一部のユーザー操作によってキャッシュが無効になった場合は、次のステートメントを実行します:

SELECT COUNT(*)
FROM city
USE INDEX(PRIMARY);

レコードを取得します

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

CREATE TABLE news(
   id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
   title VARCHAR(128) NOT NULL
) ENGINE=InnoDB;

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

SELECT *
FROM news WHERE id < $last_id
ORDER BY id DESC
LIMIT $perpage

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

SELECT *
FROM news WHERE id > $last_id
ORDER BY id ASC
LIMIT $perpage

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

SELECT id
FROM (
   SELECT id, ((@cnt:= @cnt + 1) + $perpage - 1) % $perpage cnt
   FROM news 
   JOIN (SELECT @cnt:= 0)T
   WHERE id < $last_id
   ORDER BY id DESC
   LIMIT $perpage * $buttons
)C
WHERE cnt = 0;

通过上面的语句可以为每一个分页的按钮计算出一个offset对应的id。这种方法还有一个好处。假设，网站上正在发布一片新的文章，那么所有文章的位置都会往后移一位，所以如果用户在发布文章时换页，那么他会看见一篇文章两次。如果固定了每个按钮的offset Id，这个问题就迎刃而解了。Mark Callaghan发表过一篇类似的博客，利用了组合索引和两个位置变量，但是基本思想是一致的。

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

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;

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

以上就是MySQL分页性能优化指南的内容，更多相关内容请关注PHP中文网（m.sbmmt.com）！