膨大なデータに対するページド・クエリを最適化するには？

コンテキスト

一般的に大量のデータ、およびページングの必要性は、一般的なページングステートメントは、オフセット、行を制限しています。このページングデータは、影響がないときは小さいですが、データの量が大きく、大きいのオフセットと大きくなってきたら、パフォーマンスが悪化し、悪くなります。ここで実験しています：

データの準備

• MyISAM のテスト テーブル エンジンでテーブルを構築します。

 
CREATE TABLE USER (
id INT ( 20 ) NOT NULL auto_increment,
NAME VARCHAR ( 20 ) NOT NULL,
address VARCHAR ( 20 ) NOT NULL,
PRIMARY KEY ( id ) 
) ENGINE = MyISAM; 

• バッチ挿入プロシージャの作成

delimiter //
# テーブルのデータを削除する
TRUNCATE TABLE t;
# すでにsp_test_batchプロシージャを閉じ、削除し、後で再作成する
DROP PROCEDURE IF EXISTS sp_test_batch;
# numとbatchの入力を持つストアドプロシージャを作成する。numは挿入される行の総数を表し、batchは一度に挿入される行の数を表す。
CREATE PROCEDURE sp_test_batch(IN num INT,IN batch INT)
BEGIN
	SET @insert_value = '';
	# 挿入された行の総数
 SET @count = 0;
	# 
	SET @batch_count = 0;
	WHILE @count < num DO
		# スプライシングのための内部whileループ INSERT INTO t VALUES (),(),(),...VALUESの後の部分
		WHILE (@batch_count < batch AND @count < num) DO
			IF @batch_count>0
			THEN 
				SET @insert_value = concat(@insert_value,',');
			END IF;
			SET @insert_value = concat(@insert_value,"('name", @count, "','address", @count, "')");
			SET @batch_count = @batch_count+1;	
		END WHILE;
 
		SET @count = @count + @batch_count;
		# SQL文を分割して実行する
		SET @exesql = concat("insert into user(name,address) values ", @insert_value);	
		PREPARE stmt FROM @exesql;
		EXECUTE stmt;
		DEALLOCATE PREPARE stmt;
		# 変数の値をリセットする
		SET @insert_value = '';
		SET @batch_count=0;
	END WHILE;
	# データ挿入が完了したら、テーブルのレコード総数を表示する。
	SELECT COUNT(id) FROM user;
END

• 100wのデータを挿入

テストパフォーマンス

オフセットの値を変えて以下の実験を行いました：

• offset 00100

  

• offset 001000時間がかかる

  

• offset 0010000

  

• offset 0050000

  

• オフセットが等しい場合に消費される時間

  

  上の図から、オフセットの値が大きくなるにつれて、消費される時間が長くなっていることがわかります。これはたった1000wのデータですが、もし何億ものデータがあれば、この時のクエリの効率がいかに悪いか想像がつくでしょう。最適化の方法は以下の通りです。

パフォーマンスの最適化

サブクエリのページング方法：

SELECT * FROM user WHERE id >= 
(SELECT id FROM user ORDER BY id LIMIT 9000000, 1) LIMIT 10
グラフから、サブクエリは確かに2倍速いと結論づけることができる。

JOINページネーションメソッド：

SELECT * FROM user t1 INNER join
(SELECT id FROM user ORDER BY id LIMIT 9000000, 10) t2 on t2.id =t1.id

• ジョインアプローチは、サブクエリよりも若干優れたパフォーマンスを示します。

現在のページIDの最適化に依存します：

この時間のパフォーマンスが最高です。この最適化は、前のクエリの最大IDに依存する必要があります、それは直接ページングの種類である場合は、何ページが可能ではない指定することができます、それは唯一のページの後、ページの後でなければならないので、クリックします。

SELECT id FROM user where id > 9000000 ORDER BY id LIMIT 10;

究極の最適化

• 擬似カラムによるIDのページネーションは、複数のスレッドに同時にクエリを実行させることができます。
• ID範囲の取得

  

select id from(
SELECT @rownum:=@rownum+1 AS rownum, id FROM user as t1 ,(SELECT @rownum:=0) t2 order
by t1.id asc
) t3 where t3.rownum%5000=0

複数のスレッドが異なるSQLを実行します：

select * from user where id >0 and id<=5000 一番大きなIDまで行く

終了

• 彼らの無知のために、間違いがあることは避けられない、あなたが間違った場所を見つけた場合、私はそれを修正します、指摘するために私にメッセージを残すことを願っています。

• お読みいただきありがとうございます。