数百万のデータに対して MySQL クエリを最適化する 4 つの方法

1. 時間が経つにつれて限界が遅くなる理由

ページングデータに制限を使用すると、最初の数ページを表示するときに速度が非常に速くなることがわかります。たとえば、制限を 200,25 にすると、瞬時に表示されます。しかし、時間が経つにつれて速度がどんどん遅くなり、特に 100 万件を超えると、非常に停止してしまいます。この背後にある原理は何ですか?まず、ページを戻したときにクエリ SQL がどのようになるかを見てみましょう。

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

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

2. 百万データシミュレーション

1. 従業員テーブルと部門テーブルを作成し、データを挿入するためのストアドプロシージャを作成します。

/*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
);
/* ランダムな文字列を生成する関数 */
区切り文字 $
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 を返します。
終了 $
デリミタ;
/*ストアド プロシージャを作成: 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になるまで
    繰り返し終了;
    専念;
終了 $
デリミタ;

/*ストアド プロシージャを作成: 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になるまで
    繰り返し終了;
    専念;
終了 $
デリミタ;

2. ストアドプロシージャを実行する

/*120件のレコードを挿入*/
insert_dept(1,120)を呼び出します。
/*500万個のデータを挿入*/
insert_emp(0,5000000)を呼び出します。

500万件のレコードを挿入すると遅くなる可能性がある

3.4 クエリメソッド

1. 通常の制限ページング

/*オフセットは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秒

先に進むほど、クエリの効率は低下します。

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

主キー 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秒

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

主キーが自動増分であるテーブルに適用可能

/*前回のページングの最後のデータの 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 個のデータのみがスキャンされるため、ページがどのように分割されても、消費される時間は基本的に同じです。