私は、データベースの大ファンです。 大学時代には、自分でDBMSを作りたいと思ったこともあります。 今はRDBMSとNoSQLの両方のソリューションで仕事をしていますが、その点ではとても熱中しています。 ゴールデン・ハンマーはありません。
The SQL I Love <3 というブログポストのシリーズで、私が特に面白いと思ったSQLで解決したいくつかの問題を紹介します。 解決策は、1 億以上のレコードを持つテーブルを使用してテストされています。 すべての例は MySQL を使用していますが、アイデアは PostgreSQL、Oracle、SQL Server などの他のリレーショナル データストアに適用できます。
この章では、主キーに offset を使用したページ分割を使って、大きなテーブルを効率的にスキャンすることに焦点を当てます。 これはキーセットページネーションとしても知られています。
背景
この章では、次のデータベース構造を例として使用します。 ユーザーに関する正規の例は、どんなドメインにも適合するはずです。
CREATE TABLE `users` ( `user_id` int(11) unsigned NOT NULL AUTO_INCREMENT, `external_id` varchar(32) NOT NULL, `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL, `metadata` text COLLATE utf8_unicode_ci, `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`user_id`), UNIQUE KEY `uf_uniq_external_id` (`external_id`), UNIQUE KEY `uf_uniq_name` (`name`), KEY `date_created` (`date_created`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;構造について少しコメントします。
-
external_idカラムは、他のシステムで同じユーザーへの参照を UUID 形式で格納します -
nameはFirstname Lastname -
metadataカラムに JSON ブロブ、すべての種類の非構造化データ
このテーブルは比較的大きく、約 100 000 000 レコードが格納されます。
大きなテーブルをスキャンする
問題: テーブルを走査して、各レコードを抽出し、アプリケーションのコード内で変換して、別の場所に挿入する必要があります。 この投稿では、最初の段階であるテーブルのスキャンに焦点を当てます。
Obvious and wrong solution
SELECT user_id, external_id, name, metadata, date_createdFROM users;100 000 000 レコードの私のケースでは、クエリは決して終了しません。 DBMS がクエリを終了させるだけです。 なぜでしょうか。 おそらく、テーブル全体を RAM にロードしようとしたためでしょう。 クライアントにデータを返す前にです。 もう一つの仮定 – 送信前にデータをプリロードするのに時間がかかりすぎて、クエリーがタイムアウトした。 とにかく、時間内にすべてのレコードを取得しようとする試みは失敗しました。 他の解決策を見つける必要があります。
Solution #2
ページでデータを取得することを試みることができます。 レコードは物理的または論理的なレベルでテーブルの順序が保証されていないため、DBMS 側で ORDER BY 句を使用して並べ替える必要があります。 うまくいきましたね。 10 000レコードの最初のページを尋ねたところ、わずか0.03秒で返されました。 しかし、5000ページ目ではどうでしょうか。
SELECT user_id, external_id, name, metadata, date_createdFROM usersORDER BY user_id ASCLIMIT 50 000 000, 10 000; --- 5 000th page * 10 000 page size10 000 rows in set (40.81 sec)確かに、これはとても遅いですね。 最新ページのデータを取得するのに必要な時間を見てみましょう。
SELECT user_id, external_id, name, metadata, date_createdFROM usersORDER BY user_id ASCLIMIT 99 990 000, 10 000; --- 9999th page * 10 000 page size10 000 rows in set (1 min 20.61 sec)これは正気ではありません。 しかし、バックグラウンドで実行されるソリューションの場合は問題ないでしょう。 あなたがそれをスキャンしている途中でテーブルからレコードを削除しようとすると、このアプローチのもう一つの隠された問題が明らかにすることができます。 たとえば、10ページ（100 000レコードはすでに訪問している）を終了し、100 001と110 000の間のレコードをスキャンしようとします。 しかし、レコード99 998と99 999は次のSELECT実行の前に削除されます。 この場合、次のクエリは予期しない結果を返します。
SELECT user_id, external_id, name, metadata, date_created FROM users ORDER BY user_id ASC LIMIT 100 000, 10 000; N, id, ... 1, 100 003, ... 2, 100 004, ...ご覧のように、クエリは ID 100 001 と 100 002 のレコードをスキップしました。 彼らは2つの削除操作の後、彼らは最初の100 000レコードに表示されるため、このアプローチで、アプリケーションのコードによって処理されることはありません。 したがって、データセットが変更可能な場合、この方法は信頼できません。
Solution #3 – the final one for today
このアプローチは、まだページングを使用するので前のものと非常に似ていますが、スキャンしたレコードの数に依存する代わりに、最新の訪問レコードの user_id を offset として使用するようになっています。
簡易アルゴリズム：
- テーブルから
PAGE_SIZE件のレコードを取得します。 - バッチ内の
user_idの最大戻り値を次のページのオフセットとして使用します。 - 現在の
offsetよりも高いuser_id値を持つレコードから、次のバッチを取得します。
5,000ページ目のクエリの実行。各ページには1万人のユーザーのデータが含まれます:
SELECT user_id, external_id, name, metadata, date_createdFROM usersWHERE user_id > 51 234 123 --- value of user_id for 50 000 000th recordORDER BY user_id ASCLIMIT 10 000;10 000 rows in set (0.03 sec)注意：user_idの値は連続ではなく、たとえば25 348は25 345よりすぐ後にあります。 この解決策は、将来のページからレコードが削除された場合にも機能します。この場合でも、クエリはレコードをスキップしません。
パフォーマンスの説明
さらに学習するには、50 000 000 の後に次の 10 000 レコードを取得するクエリの各バージョンについて、EXPLAIN EXTENDED の結果を調査することをお勧めします。
| 解 | 時間 | 型 | キー | 行 | フィルタ | エクストラ |
|---|---|---|---|---|---|---|
| 1.1. Obvious | Never | ALL | NULL | 100M | 100.00 | NULL |
| 2.選択的処理。 レコード数をオフセットとしたページング | 40.81 sec | index | NULL / PRIMARY | 50M | 200.00 | NULL |
| 3.レコード数によるオフセットのページング。 user_id をオフセットとして使用したキーセットのページ分割 | 0.03 sec | range | PRIMARY / PRIMARY | 50M | 100.00.00 | Using where |
ここで、1番目のソリューションは大きなテーブルでは実用的でないため、2番目と3番目のソリューションの実行プランの違いに焦点を当てましょう。 index vs range。 最初のものは、レコードを見つけるためにインデックスツリー全体がスキャンされることを意味します。 range 型は、指定された範囲内で一致する行を見つけるためにのみインデックスが使用されることを意味します。 したがって、range型はindex.
NULLとPRIMARYの比較。 このカラムは、MySQL で使用可能なキーを示しています。 ちなみに、キーカラムを見ると、最終的に両方のクエリで PRIMARY キーが使用されていることがわかります。 50 010 000 vs 50 000 000です。 この値は、結果を返す前に分析されたレコードの数を表示します。 2番目のクエリでは、値はスクロールの深さによって異なります。 例えば、9999ページ目以降の10 000レコードを取得しようとした場合、99 990 000レコードが調査されることになります。 一方、3番目のクエリの値は一定で、1ページ目のデータを読み込もうと、最後のページのデータを読み込もうと関係ない。 200.00と100.00の比較。 この列は、処理前にフィルタリングされるテーブルの割合を示しています。 値が高いほど良い。 100.00 の値は、クエリがテーブル全体を検索することを意味します。 2番目のクエリでは、値は一定ではなく、ページ番号に依存する。1ページ目を尋ねる場合、フィルタリング・カラムの値は1000000.00となる。 最後のページでは、100.00.NULL と Using where の比較。 MySQL がクエリを解決する方法についての追加情報を提供します。 PRIMARY キーでの WHERE の使用により、クエリの実行が高速化されます。結合タイプは、3 番目のクエリを高速化するためにパフォーマンスに最も貢献したクエリのパラメータであると思われます。 もう一つ重要なのは、2番目のクエリがスクロールするページ数に極端に依存していることです。
EXPLAIN コマンドの出力を理解するための詳しいガイダンスは、お使いの RDBMS の公式ドキュメントにあります。
まとめ
今回のブログ記事の主なテーマは、100 000レコードからなる大規模テーブルを主キー付きoffsetでスキャン（キーセット ページネーション）することでした。 全体として、3つの異なるアプローチをレビューし、対応するデータセットでテストしました。 また、MySQL クエリの実行計画を分析するための EXPLAIN EXTENDED コマンドの使用法も見直しました。
次の章では、データの集約とストレージの最適化に注目します。 ご期待ください！
大きなテーブルをスキャンする方法は何ですか？
解決策3のようにキーセットのページ送りを使用する他の目的を覚えていますか？
