mysqlページング文の最適化
3628 ワード
詳細
データテーブル構造のテスト
テーブルcustomerとorderが2つありますtest,customerIdのプライマリキーはorder_testの外部キー
Customerテーブルには1億以上のデータがありますorder_testには9000 wのデータがある.
次に、上記の2つのテーブルについて一般的な文を最適化します.
limitページング最適化方法
explainの使い方の詳細は、次のとおりです.http://zcf9916.iteye.com/blog/2409731
1.customerテーブルのcustomerIdでソートされた500000～500200番目のデータを調べるとします.
普通の書き方はこうです
SELECT customerId FROM customer LIMIT 500000,200;実行時間は0.108秒
この文は上位500,200件のデータを検出し、上位5,000件のデータを破棄するので速度が遅い
explain+sqlを実行するとtype=allが表示され、全テーブルスキャンが行われます
id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------+---------------+------+---------+------+--------+----------+-------+
| 1 | SIMPLE | customer | ALL | NULL | NULL | NULL | NULL | 99808800 | 100.00 | |
limitの後ろに数字が1つしかない場合、limit 200はデータベースが200本しかスキャンできないので、ここから手に入れることができます.
1.idがインクリメントされ、データが削除されていないと仮定すると、
プライマリ・キーまたは一意のインデックスを作成し、インデックスを使用します(ページごとに10個あると仮定します).
---文スタイル:mysqlでは、SELECT*FROM customer WHERE customerId>500000 LIMIT 200、実行時間0.0032秒
---シーンに適応する:データ量が多い場合(メタグループ数が万)に適用する---原因:インデックススキャン、速度が速い.
explain+sqlを実行するとtype=range,possible_が表示されます.keys=primary説明プライマリ・キー・インデックスが使用されています
(ps:range:指定された範囲のローのみを取得し、インデックスを使用してローを選択します.)
id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------+---------------+------+---------+------+--------+----------+-------+
| 1 | SIMPLE | customer | range | PRIMARY | PRIMARY | 4 | NULL | 49808800 | 100.00 |
2.idがインクリメントされ、データが削除すると仮定し、第499800-4999999のデータが削除されたと仮定すると、実際のid範囲は50020-500400であるため、customerId>500000によって単純に位置決めことはできない.
---文スタイル:SELECT*FROM customer WHERE customerId>=
(SELECT customerId FROM customer order by customerId LIMIT 500000,1) ORDER BY customerId LIMIT 200
id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------+---------------+------+---------+------+--------+----------+-------+
| 1 | PRIMARY | customer | range | PRIMARY | PRIMARY | 4 | NULL | 49808800 | 100.00 | using where
| 2 | SUBQUERY | customer | index | | PRIMARY | 4 | NULL | 500001 | 100.00 | using index
PS:limit offsetの場合、数量.offset値が大きすぎるとクエリ時間が長くなるので、offsetが一定量を超える場合は処理しないほうがいいです.
PS:実験で発見されました.SELECT customerId FROM customer order by customerId LIMIT 0,20;SELECT customerId FROM customer LIMIT 0,20;cutomerはインクリメンタルですが、orderを付けないとインデックスのポインタ順にアクセスするので、必ずしも順番に来るとは限りません.
データテーブル構造のテスト
テーブルcustomerとorderが2つありますtest,customerIdのプライマリキーはorder_testの外部キー
CREATE TABLE `customer` (
`customerId` int(11) NOT NULL AUTO_INCREMENT,
`phone` varchar(11) NOT NULL COMMENT ' ',
`password` varchar(20) NOT NULL COMMENT ' ',
PRIMARY KEY (`customerId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `order_test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`orderNo` char(17) COLLATE utf8_bin NOT NULL,
`customerId` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `111_idx` (`customerId`),
CONSTRAINT `111` FOREIGN KEY (`customerId`) REFERENCES `customer` (`customerId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=100880001 DEFAULT CHARSET=utf8 COLLATE=utf8_binCustomerテーブルには1億以上のデータがありますorder_testには9000 wのデータがある.
次に、上記の2つのテーブルについて一般的な文を最適化します.
limitページング最適化方法
explainの使い方の詳細は、次のとおりです.http://zcf9916.iteye.com/blog/2409731
1.customerテーブルのcustomerIdでソートされた500000～500200番目のデータを調べるとします.
普通の書き方はこうです
SELECT customerId FROM customer LIMIT 500000,200;実行時間は0.108秒
この文は上位500,200件のデータを検出し、上位5,000件のデータを破棄するので速度が遅い
explain+sqlを実行するとtype=allが表示され、全テーブルスキャンが行われます
id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------+---------------+------+---------+------+--------+----------+-------+
| 1 | SIMPLE | customer | ALL | NULL | NULL | NULL | NULL | 99808800 | 100.00 | |
limitの後ろに数字が1つしかない場合、limit 200はデータベースが200本しかスキャンできないので、ここから手に入れることができます.
1.idがインクリメントされ、データが削除されていないと仮定すると、
プライマリ・キーまたは一意のインデックスを作成し、インデックスを使用します(ページごとに10個あると仮定します).
---文スタイル:mysqlでは、SELECT*FROM customer WHERE customerId>500000 LIMIT 200、実行時間0.0032秒
---シーンに適応する:データ量が多い場合(メタグループ数が万)に適用する---原因:インデックススキャン、速度が速い.
explain+sqlを実行するとtype=range,possible_が表示されます.keys=primary説明プライマリ・キー・インデックスが使用されています
(ps:range:指定された範囲のローのみを取得し、インデックスを使用してローを選択します.)
id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------+---------------+------+---------+------+--------+----------+-------+
| 1 | SIMPLE | customer | range | PRIMARY | PRIMARY | 4 | NULL | 49808800 | 100.00 |
2.idがインクリメントされ、データが削除すると仮定し、第499800-4999999のデータが削除されたと仮定すると、実際のid範囲は50020-500400であるため、customerId>500000によって単純に位置決めことはできない.
---文スタイル:SELECT*FROM customer WHERE customerId>=
(SELECT customerId FROM customer order by customerId LIMIT 500000,1) ORDER BY customerId LIMIT 200
id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------+---------------+------+---------+------+--------+----------+-------+
| 1 | PRIMARY | customer | range | PRIMARY | PRIMARY | 4 | NULL | 49808800 | 100.00 | using where
| 2 | SUBQUERY | customer | index | | PRIMARY | 4 | NULL | 500001 | 100.00 | using index
PS:limit offsetの場合、数量.offset値が大きすぎるとクエリ時間が長くなるので、offsetが一定量を超える場合は処理しないほうがいいです.
PS:実験で発見されました.SELECT customerId FROM customer order by customerId LIMIT 0,20;SELECT customerId FROM customer LIMIT 0,20;cutomerはインクリメンタルですが、orderを付けないとインデックスのポインタ順にアクセスするので、必ずしも順番に来るとは限りません.