数十億のデータに対するMySQLページングの最適化に関する簡単な説明

背景

仕事が終わって、帰りの地下鉄に座りながら、週末の予定をどうするか考えていました。

突然、電話が鳴りました。開発クラスの仲間からの電話だとわかり、すぐに緊張しました。今週のバージョンはすでにリリースされており、この時間に電話がかかってくるということは、通常、回線に問題があることを意味します。

案の定、通信状況はオンライン データ クエリ インターフェイスが狂ったように不合理に呼び出され、この操作によってオンライン MySql クラスターの速度が直接低下しました。
さて、この問題は深刻です。地下鉄を降りて急いで家に帰り、コンピューターを起動して、同僚と一緒に Pinpoint でスロークエリログを取得しました。次のような非常に奇妙なクエリを見ました

POST ドメイン/v1.0/モジュール/メソッド?order=条件&orderType=desc&offset=1800000&limit=500

domain、module、method はすべてエイリアスで、インターフェイスのドメイン、モジュール、インスタンス メソッド名を表します。次の offset と limit は、ページング操作のオフセットとページ数を表します。つまり、学生は (1800000/500+1=3601) 番目のページに移動していることになります。ログを予備的に検索したところ、そのような通話が 8,000 件以上見つかりました。

これはすごいですね。私たちのページのページ分割されたページ数は 500 ではなく、1 ページあたり 25 です。これは間違いなく、機能ページでページを 1 つずつ手動でめくっているためではなく、データが更新されているためです (説明のために、私たちの本番環境のデータは 1 億を超えています)。ログを詳細に比較すると、多くのページング時間が重複しており、相手側はマルチスレッド呼び出しであるはずであることが判明しました。

認証トークンを分析することで、リクエストは ApiAutotest というクライアント プログラムから送信され、認証トークンを生成したアカウントは QA 学生からのものであることが基本的に判明しました。私はすぐにクラスメートに電話して連絡を取り、問題に対処しました。

分析する

実際、MySQL クエリ ステートメントの全体的な効率はまだ許容範囲内です。必要な結合テーブル クエリの最適化はすべて実行されており、必要な簡略化されたクエリ コンテンツも実行されており、キー条件フィールドと並べ替えフィールドに必要なインデックスも実行されています。問題は、クエリがページごとに実行されるため、ページが戻るほどスキャンされるデータが多くなり、速度が遅くなることです。
最初の数ページをチェックしたところ、速度が非常に速く、たとえば制限 200,25 が瞬時に表示されることがわかりました。しかし、時間が経つにつれて速度がどんどん遅くなり、特に 100 万件を超えると、非常に停止してしまいます。この背後にある原理は何ですか?まず、ページを戻したときにクエリ SQL がどのようになるかを見てみましょう。

c_name1='xxx' の場合、t_name から * を選択し、c_name2 で順序付けし、制限 2000000,25 にします。

このクエリの速度低下は、実際には制限後のオフセットが大きいことが原因です。たとえば、上記の制限 2000000,25 は、データベースが 2000025 個のデータをスキャンし、最初の 20000000 個のデータを破棄し、残りの 25 個のデータをユーザーに返すことに相当します。このアプローチは明らかに不合理です。

この問題については、「High Performance MySQL」の第 6 章「クエリ パフォーマンスの最適化」で説明されています。

ページング操作は通常、適切な order by 句とともに、limit と offset を使用して実装されます。しかし、これには共通の問題があります。オフセットが非常に大きい場合、MySQL は大量の不要な行をスキャンし、それらを破棄することになります。

データシミュレーション

さて、問題の原理を理解したので、それを解決してみましょう。データの機密性に関しては、この状況をシミュレートし、テスト用のデータをいくつか構築します。

1. 従業員テーブルと部門テーブルの2つのテーブルを作成します。

/*Department テーブル、存在する場合は削除*/
存在する場合はテーブルを削除します。
テーブル dep を作成します(
    id int unsigned 主キー auto_increment,
    depno mediumint unsigned not null デフォルト 0,
    depname varchar(20) NULLでないデフォルト "",
    メモ varchar(200) null ではない デフォルト ""
);

/*従業員テーブル、存在する場合は削除*/
emp が存在する場合はテーブルを削除します。
テーブルempを作成します(
    id int unsigned 主キー auto_increment,
    empno mediumint unsigned not null デフォルト 0,
    empname varchar(20) NULLでないデフォルト "",
    ジョブvarchar(9) NULLでないデフォルト "",
    mgr mediumint unsigned not null デフォルト 0,
    hiredate datetime が null ではありません。
    sal 小数点(7,2) は null ではありません。
    comn 10進数(7,2) は null ではありません。
    depno mediumint unsigned not null デフォルト 0
);

2. ランダムな文字列とランダムな数値を生成する2つの関数を作成する

/* ランダムな文字列を生成する関数 */
区切り文字 $
rand_stringが存在する場合はFUNCTIONを削除します。
CREATE FUNCTION rand_string(n INT) は VARCHAR(255) を返します。
始める
    chars_str VARCHAR(100) をデフォルト 'abcdefghijklmlopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ' として宣言します。
    return_str VARCHAR(255) DEFAULT '' を宣言します。
    i INT DEFAULT 0 を宣言します。
    i < n ながら
    return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1)) を設定します。
    i = i+1 を設定します。
    終了しながら;
    戻り値 return_str;
終了 $
デリミタ;


/*ランダムな部門番号を生成する関数*/
区切り文字 $
rand_numが存在する場合はFUNCTIONを削除します。
CREATE FUNCTION rand_num() は INT(5) を返します。
始める
    i INT DEFAULT 0 を宣言します。
    i = FLOOR(100+RAND()*10) を設定します。
    i を返します。
終了 $
デリミタ;

3. 500万人の従業員データをシミュレートするストアドプロシージャを作成する

/*ストアド プロシージャを作成: emp テーブルにデータを挿入します*/
区切り文字 $
存在する場合は PROCEDURE を削除します insert_emp;
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
始める
    i INT DEFAULT 0 を宣言します。
    /*set autocommit =0 自動コミットを 0 に設定し、デフォルトのコミットをオフにします*/
    自動コミットを 0 に設定します。
    繰り返す
    i = i + 1 を設定します。
    emp(empno,empname,job,mgr,hiredate,sal,comn,depno) に INSERT INTO VALUES ((START+i),rand_string(6),'SALEMAN',0001,now(),2000,400,rand_num());
    i = max_numになるまで
    繰り返し終了;
    専念;
終了 $
デリミタ;
/*500万個のデータを挿入*/
insert_emp(0,5000000)を呼び出します。

4. 120の部門データをシミュレートするストアドプロシージャを作成します。

/*ストアド プロシージャを作成: dep テーブルにデータを挿入します*/
区切り文字 $
存在する場合は PROCEDURE を削除します insert_dept;
CREATE PROCEDURE insert_dept(IN START INT(10), IN max_num INT(10))
始める
    i INT DEFAULT 0 を宣言します。
    自動コミットを 0 に設定します。
    繰り返す
    i = i+1 を設定します。
    dep( depno, depname, memo) に挿入 VALUES((START+i),rand_string(10),rand_string(8));
    i = max_numになるまで
    繰り返し終了;
    専念;
終了 $
デリミタ;
/*120件のレコードを挿入*/
insert_dept(1,120)を呼び出します。

5. キー フィールドのインデックスを作成します。データを実行した後、インデックスを作成するのに長い時間がかかりますが、データの実行は速くなります。

/*キーフィールドのインデックスを作成: 並べ替え、条件*/
emp(id) に idx_emp_id インデックスを作成します。
emp(depno) に idx_emp_depno インデックスを作成します。
dep(depno) に idx_dep_depno インデックスを作成します。

テスト

テストデータ

/*オフセットは100なので、25を取る*/
a.empno、a.empname、a.job、a.sal、b.depno、b.depname を選択
emp a から左結合 dep b を a.depno = b.depno で、order by a.id desc limit 100,25 で結合します。
/*オフセットは 4800000 なので 25 を取る*/
a.empno、a.empname、a.job、a.sal、b.depno、b.depname を選択
emp a から、左結合 dep b を a.depno = b.depno で、order by a.id desc limit 4800000,25 で結合します。

実行結果

[SQL]
a.empno、a.empname、a.job、a.sal、b.depno、b.depname を選択
emp a から左結合 dep b を a.depno = b.depno で、order by a.id desc limit 100,25 で結合します。
影響を受ける行: 0
時間: 0.001秒
[SQL]
a.empno、a.empname、a.job、a.sal、b.depno、b.depname を選択
emp a から、左結合 dep b を a.depno = b.depno で、order by a.id desc limit 4800000,25 で結合します。
影響を受ける行: 0
時間: 12.275秒

スキャンするデータが大量にあるため、当然ながら、時間がかかるというわけではありません。

解決

1. インデックスカバレッジ+サブクエリ最適化を使用する

主キー ID があり、それに基づいてインデックスを構築しているため、最初にインデックス ツリー内の開始位置の ID 値を見つけ、次に見つかった ID 値に基づいて行データをクエリできます。

/*サブクエリは 100 オフセットされた位置の ID を取得し、この位置の後の 25 を取得します*/
a.empno、a.empname、a.job、a.sal、b.depno、b.depname を選択
emp a から、a.depno = b.depno で dep b を左結合します。
ここで、a.id >= (emp から id を選択し、id 制限で 100,1 を注文)
a.id 制限 25 で順序付けします。

/*サブクエリは 4800000 オフセットの位置の ID を取得し、この位置の 25 を取得します*/
a.empno、a.empname、a.job、a.sal、b.depno、b.depname を選択
emp a から、a.depno = b.depno で dep b を左結合します。
ここで、a.id >= (emp から id を選択し、id 制限で 4800000,1 を注文)
a.id 制限 25 で順序付けします。

実行結果

実行効率は以前に比べて大幅に向上しました。
[SQL]
a.empno、a.empname、a.job、a.sal、b.depno、b.depname を選択
emp a から、a.depno = b.depno で dep b を左結合します。
ここで、a.id >= (emp から id を選択し、id 制限で 100,1 を注文)
a.id 制限 25 で順序付けします。
影響を受ける行: 0
時間: 0.106秒

[SQL]
a.empno、a.empname、a.job、a.sal、b.depno、b.depname を選択
emp a から、a.depno = b.depno で dep b を左結合します。
ここで、a.id >= (emp から id を選択し、id 制限で 4800000,1 を注文)
a.id 制限 25 で順序付けします。
影響を受ける行: 0
時間: 1.541秒

2. 開始位置を再定義する

オフセットの使用を避けるために、最後の検索結果の主キーの位置を覚えておいてください

/*前回のページングの最後のデータの ID は 100 なので、100 をスキップして 101 からテーブルをスキャンします */
a.id、a.empno、a.empname、a.job、a.sal、b.depno、b.depname を選択
emp a から、a.depno = b.depno で dep b を左結合します。
a.id > 100 の場合、a.id で順序付けし、制限を 25 にします。

/*前回のページングの最後のデータの ID は 4800000 なので、4800000 をスキップして 4800001 からテーブルをスキャンします */
a.id、a.empno、a.empname、a.job、a.sal、b.depno、b.depname を選択
emp a から、a.depno = b.depno で dep b を左結合します。
a.id > 4800000の場合
a.id 制限 25 で順序付けします。

実行結果

[SQL]
a.id、a.empno、a.empname、a.job、a.sal、b.depno、b.depname を選択
emp a から、a.depno = b.depno で dep b を左結合します。
a.id > 100 の場合、a.id で順序付けし、制限を 25 にします。
影響を受ける行: 0
時間: 0.001秒

[SQL]
a.id、a.empno、a.empname、a.job、a.sal、b.depno、b.depname を選択
emp a から、a.depno = b.depno で dep b を左結合します。
a.id > 4800000の場合
a.id 制限 25 で順序付けします。
影響を受ける行: 0
時間: 0.000秒

これが最も効率的です。条件を実行した後、25 個のデータのみがスキャンされるため、ページがどのように分割されても、消費される時間は基本的に同じです。

しかし、問題があります。これは、前のページの最後の ID を記憶できるように、一度に 1 ページずつページングする場合にのみ適しています。ユーザーがページ間を移動すると問題が発生します。たとえば、ユーザーがページ 25 の閲覧を終えてすぐにページ 35 にジャンプすると、データは不正確になります。

これは、スクロール ホイールを下に引いて、継続的に引いて読み込む Baidu 検索や Tencent News などのシナリオに適しています。この遅延読み込みにより、データが急激に取得されることがなくなります。

3. ダウングレード戦略

Alibaba DBA のクラスメートがオンラインで共有しているソリューションを見ました。制限オフセットと取得数の最大値を設定します。最大値を超えると、空のデータが返されます。
値がこれを超えると、ページングではなくデータの更新が行われると考えられるためです。確実にデータを検索する場合は、ページごとにページングするのではなく、適切な条件を入力して範囲を絞り込む必要があります。
これは私の同僚の考えとほぼ同じです。リクエスト中にオフセットが特定の値より大きい場合、最初に 4xx エラーが返されます。

まとめ

その夜、私たちはオフセットを制限するために上記の 3 番目の解決策を適用しました。特定の値を超えると、null 値が返されます。 2 日目には、最初のソリューションと 2 番目のソリューションを組み合わせて、プログラムとデータベース スクリプトをさらに最適化しました。

合理的に言えば、あらゆる機能を実行する際には極端な状況を考慮する必要があり、設計容量は極端な境界テストをカバーする必要があります。

さらに、必要な電流制限とダウングレードも考慮する必要があります。たとえば、ツールがマルチスレッドで呼び出され、その頻度が短期間に 8,000 回に達する場合、カウント サービスを使用して、ユーザーの呼び出しが頻繁すぎることを判断してフィードバックし、呼び出しを直接中断することができます。

これで、MySQL の 10 億レベルのデータ ページングの最適化に関するこの記事は終了です。MySQL の 10 億レベルのデータ ページングに関するより関連性の高いコンテンツについては、123WORDPRESS.COM で以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。