Mysqlページングの実装と最適化
9434 ワード
通常、ORDER BY LIMIT start,offset方式でページングクエリーを行います.たとえば、次のSQLです.
または、次のような条件なしのページングSQL:
一般に、ページングSQLの消費時間はstart値の増加に伴って急激に増加します.次の2つの異なる開始値のページングSQLの実行時間を見てみましょう.
ページ数が増えるにつれて、SQLクエリーの消費時間も数十倍に増え、明らかに科学的ではないことがわかります.今日は、このページングスキームを最適化する方法を分析します.一般的には、ページングを最適化する究極の方法は、ページングがなく、ハハハ~~~、くだらないことを言わないでください.確かに、ページングアルゴリズムをSphinx、Lucenceなどの第三者ソリューションに渡すことができ、MySQLに苦手なことをさせる必要はありません.もちろん、第三者を使うのは面倒だという友达がいます.私たちはMySQLでこのページを作りたいと思っています.どうすればいいですか.急いではいけません.ゆっくり分析して、まず次の表DDL、データ量、SQLの実行計画などの情報を調べます.
プライマリ・キー・インデックスでスキャンされたが、2番目のSQLでスキャンする必要があるレコードの数が大きすぎて、約935510件のレコードをスキャンしてから、ソート結果に基づいて10件のレコードを取得する必要があることがわかります.これは非常に遅いに違いありません.このような状況に対して、私たちの最適化の構想は比較的にはっきりしていて、2つの点があります.
これにより、サブクエリ、テーブル接続の2つの適切な書き換え方法があります.すなわち、次のようになります.
次に、この2つの最適化された新しいSQLの実行時間を比較します.
フィルタなしのページングSQLの比較を見てみましょう.
ここで,サブクエリやINNER JOINを用いて最適化を行った場合,いずれも大幅に向上していることが分かるが,この手法は小さいページングにも同様に適用され,LIMIT開始のstart位置はかなり小さくなり,SQL実行時間もかなり速くなったが,この手法を用いると,WHERE条件付きページングはそれぞれクエリ効率を向上させることができる:24.9%,156.5%,WHERE条件なしページングはそれぞれクエリ効率を向上させる:554.5%,11.7%,皆さんは自分でテスト検証を行うことができます.スケールを上げるだけでは、これらの最適化方法が様々なページングモードに適用できることを確保し、最初から使用することができます.さまざまなシーンに対応するアップグレードの割合を見てみましょう.
大きなページ、WHERE付き
大きいページ、WHEREを持っていません
大ページング平均アップスケール
小さいページ、WHERE付き
小さいページ、WHEREを持っていません
全体平均上昇率
サブクエリの最適化
28.20%
10.60%
19.40%
24.90%
554.40%
154.53%
INNER JOIN最適化
30.80%
30.20%
30.50%
156.50%
11.70%
57.30%
結論:このように見れば明らかであり,特に大きなページングの場合にはINNER JOIN方式を用いてページングアルゴリズムを最適化することを優先的に推奨する.
上記のテストのたびにmysqldインスタンスを再起動し、SQL_を追加しました.NO_CACHEは、毎回直接データファイルまたはインデックスファイルで読み取ることを保証します.データが予熱されると、クエリーの効率はある程度向上しますが、上記の対応する効率の向上割合はほぼ一致します.
SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 100, 10;
または、次のような条件なしのページングSQL:
SELECT * FROM `t1` ORDER BY id DESC LIMIT 100, 10;
一般に、ページングSQLの消費時間はstart値の増加に伴って急激に増加します.次の2つの異なる開始値のページングSQLの実行時間を見てみましょう.
[email protected]> SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 500, 10;
…
10 rows in set (0.05 sec)
[email protected]> SELECT * FROM `t1` WHERE ftype=6 ORDER BY id DESC LIMIT 935500, 10;
…
10 rows in set (2.39 sec)
ページ数が増えるにつれて、SQLクエリーの消費時間も数十倍に増え、明らかに科学的ではないことがわかります.今日は、このページングスキームを最適化する方法を分析します.一般的には、ページングを最適化する究極の方法は、ページングがなく、ハハハ~~~、くだらないことを言わないでください.確かに、ページングアルゴリズムをSphinx、Lucenceなどの第三者ソリューションに渡すことができ、MySQLに苦手なことをさせる必要はありません.もちろん、第三者を使うのは面倒だという友达がいます.私たちはMySQLでこのページを作りたいと思っています.どうすればいいですか.急いではいけません.ゆっくり分析して、まず次の表DDL、データ量、SQLの実行計画などの情報を調べます.
[email protected]> SHOW CREATE TABLE `t1`;
CREATE TABLE `t1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
...
`ftype` tinyint(3) unsigned NOT NULL,
...
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
[email protected]> select count(*) from t1;
+----------+
| count(*) |
+----------+
| 994584 |
+----------+
[email protected]> EXPLAIN SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 500, 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 510
Extra: Using where
[email protected]> EXPLAIN SELECT * FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935500, 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 935510
Extra: Using where
プライマリ・キー・インデックスでスキャンされたが、2番目のSQLでスキャンする必要があるレコードの数が大きすぎて、約935510件のレコードをスキャンしてから、ソート結果に基づいて10件のレコードを取得する必要があることがわかります.これは非常に遅いに違いありません.このような状況に対して、私たちの最適化の構想は比較的にはっきりしていて、2つの点があります.
1、 ，
2、 ， ， N
これにより、サブクエリ、テーブル接続の2つの適切な書き換え方法があります.すなわち、次のようになります.
# ， id， ， 10
# 2 ， LIMIT start ， 10， 935510，
[email protected]> EXPLAIN SELECT * FROM (SELECT * FROM `t1` WHERE id > ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) t ORDER BY id DESC\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table:
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
Extra: Using filesort
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: t1
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 973192
Extra: Using where
*************************** 3. row ***************************
id: 3
select_type: SUBQUERY
table: t1
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 935511
Extra: Using where
# INNER JOIN ，JOIN ID ， ， 10
[email protected]> EXPLAIN SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935500,10) t2 USING (id)\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table:
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 935510
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: t1
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: t2.id
rows: 1
Extra: NULL
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: t1
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 973192
Extra: Using where
次に、この2つの最適化された新しいSQLの実行時間を比較します.
[email protected]> SELECT * FROM (SELECT * FROM `t1` WHERE id > ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) T ORDER BY id DESC;
...
rows in set (1.86 sec)
# ， profiling ， SQL ：28.2%
[email protected]> SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1` WHERE ftype=1 ORDER BY id DESC LIMIT 935500,10) t2 USING (id);
...
10 rows in set (1.83 sec)
# INNER JOIN ， profiling ， SQL ：30.8%
フィルタなしのページングSQLの比較を見てみましょう.
# SQL
[email protected]> EXPLAIN SELECT * FROM `t1` ORDER BY id DESC LIMIT 935500, 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 935510
Extra: NULL
[email protected]> SELECT * FROM `t1` ORDER BY id DESC LIMIT 935500, 10;
...
10 rows in set (2.22 sec)
#
[email protected]> EXPLAIN SELECT * FROM (SELECT * FROM `t1` WHERE id > ( SELECT id FROM `t1` ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) t ORDER BY id DESC;
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table:
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 10
Extra: Using filesort
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: t1
type: ALL
possible_keys: PRIMARY
key: NULL
key_len: NULL
ref: NULL
rows: 973192
Extra: Using where
*************************** 3. row ***************************
id: 3
select_type: SUBQUERY
table: t1
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 935511
Extra: Using index
[email protected]> SELECT * FROM (SELECT * FROM `t1` WHERE id > ( SELECT id FROM `t1` ORDER BY id DESC LIMIT 935510, 1) LIMIT 10) t ORDER BY id DESC;
…
10 rows in set (2.01 sec)
# ， profiling ， SQL ：10.6%
# INNER JOIN
[email protected]> EXPLAIN SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1`ORDER BY id DESC LIMIT 935500,10) t2 USING (id)\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table:
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 935510
Extra: NULL
*************************** 2. row ***************************
id: 1
select_type: PRIMARY
table: t1
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: t1.id
rows: 1
Extra: NULL
*************************** 3. row ***************************
id: 2
select_type: DERIVED
table: t1
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 973192
Extra: Using index
[email protected]> SELECT * FROM `t1` INNER JOIN ( SELECT id FROM `t1`ORDER BY id DESC LIMIT 935500,10) t2 USING (id);
…
10 rows in set (1.70 sec)
# INNER JOIN ， profiling ， SQL ：30.2%
ここで,サブクエリやINNER JOINを用いて最適化を行った場合,いずれも大幅に向上していることが分かるが,この手法は小さいページングにも同様に適用され,LIMIT開始のstart位置はかなり小さくなり,SQL実行時間もかなり速くなったが,この手法を用いると,WHERE条件付きページングはそれぞれクエリ効率を向上させることができる:24.9%,156.5%,WHERE条件なしページングはそれぞれクエリ効率を向上させる:554.5%,11.7%,皆さんは自分でテスト検証を行うことができます.スケールを上げるだけでは、これらの最適化方法が様々なページングモードに適用できることを確保し、最初から使用することができます.さまざまなシーンに対応するアップグレードの割合を見てみましょう.
大きなページ、WHERE付き
大きいページ、WHEREを持っていません
大ページング平均アップスケール
小さいページ、WHERE付き
小さいページ、WHEREを持っていません
全体平均上昇率
サブクエリの最適化
28.20%
10.60%
19.40%
24.90%
554.40%
154.53%
INNER JOIN最適化
30.80%
30.20%
30.50%
156.50%
11.70%
57.30%
結論:このように見れば明らかであり,特に大きなページングの場合にはINNER JOIN方式を用いてページングアルゴリズムを最適化することを優先的に推奨する.
上記のテストのたびにmysqldインスタンスを再起動し、SQL_を追加しました.NO_CACHEは、毎回直接データファイルまたはインデックスファイルで読み取ることを保証します.データが予熱されると、クエリーの効率はある程度向上しますが、上記の対応する効率の向上割合はほぼ一致します.