mysqlのlimitのページングクエリの最適化について
4218 ワード
昨日、古い同僚のZ君と交流して、Z君は私に闻いて、ページが大量のデータに出会った时、私达の通常のselect*from`tablename`limit m、nはとても遅い问题があって、私はしばらくどのような最适化方法があるか分かりません.私たちはコミュニケーションからすでに知っています.mysqlクエリでlimitを使用すると、mysqlはm+nレコードをスキャンした後、前のmレコードをフィルタして最後に私たちが望んでいるnデータを返します.従って、前のsqlのパラメータmが10 w 100 w 1000 wに等しい場合、効率は直線的に低下する.
以下は技術スラグの私が度娘の中で見つけた比較的全面的に詳細な分析と最適化方法である.次のようになります.
クエリ文を使用する場合、常に最初の数行または中間の数行のデータが返されますが、この場合はどうしますか?心配しないでください.すでにこのような機能を提供しています.LIMIT句は、SELECT文に指定されたレコード数を返すように強制するために使用することができる.LIMITは1つまたは2つの数値パラメータを受け入れる.パラメータは整数定数でなければなりません.2つのパラメータが与えられた場合、1番目のパラメータは、1番目の戻りレコード行のオフセット量を指定し、2番目のパラメータは、戻りレコード行の最大数を指定します.SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
これは2つのパラメータで、1つ目はオフセット量で、2つ目は数select*from employee limit 3,7です.//4-11行select*from employee limit 3,1を返します.//4行目のパラメータselect*from employee limit 3を返します.//前の3行に戻る
以前mysqlでページングしていたlimit 100000,20という方法を使っていましたが、あなたもそうだと信じていますが、効率を高めて、ページングのコード効率をもっと高く、もっと速くするには、どうすればいいのでしょうか.
第一部:ページ分けの基本原理を見てみましょう
上のmysql文について説明します:limit 10000,20の意味は条件を満たす10020行をスキャンして、前の10000行を捨てて、最後の20行を返して、問題はここで、limit 1000000100であれば、100100行をスキャンする必要があります.高い同時アプリケーションでは、クエリーのたびに10 W行を超えるスキャンが必要で、性能は大きく割引されます.n行のみをスキャンするため、limit n性能は問題ありません.
第二部分:ヤフーの何人かのエンジニアによってEfficient Pagination Using MySQLの報告内容の拡張をもたらした:本文の中で1種のclueのやり方に言及して、ページをめくることにいくつかの手がかりを提供して、例えばSELECT*FROM message ORDER BY id DESCで、idの降順によってページを分けて、1ページ20条、現在は10ページ目で、現在のページのエントリidは最大1020で、最小は1000で、前のページ、次のページのようなジャンプのみを提供する場合(Nページ目のジャンプは提供されません)、前のページを処理するときにSQL文は次のようになります.
次のページを処理するときのSQL文は次のようになります.
何ページめくっても、クエリーごとに20行しかスキャンされません.
欠点は前のページ、次のページのリンク形式しか提供できないことですが、私たちのプロダクトマネージャは「前のページ1 2 3 3 4 5 6 7 8 9次のページ」というリンク方式が大好きです.どうすればいいですか?
もしLIMIT m,nが避けられないならば、効率を最適化するには、できるだけmを小さくするしかありません.私たちは前のclueのやり方を拡張しますか、それともSELECT*FROM message ORDER BY id DESCですか.idの降順にページを分けます.1ページ20本です.現在は10ページ目です.現在のページのエントリidは最大2519で、最小は2500です.10ページ目のSQLは次のようになります.
たとえば、9ページにジャンプするには、SQL文を次のように書くことができます.
たとえば、8ページ目にジャンプする場合、SQL文は次のように書くことができます.
たとえば、7ページ目にジャンプする場合、SQL文は次のように書くことができます.
11ページにジャンプ:
12ページにジャンプ:
13ページにジャンプ:
原理はやはり同じで、現在のページidの最大値と最小値を記録して、ジャンプページと現在のページの相対オフセットを計算して、ページが近いため、このオフセット量は大きくなくて、このようにするとm値は相対的に小さくて、スキャンの行数を大幅に減らします.実は従来のlimit m,n,相対的なオフセットはずっと第1ページであり,このようにすると後ろにひっくり返すほど効率が悪くなり,上記の方法ではこのような問題はない.
SQL文の中のASCとDESCに注意して、ASCが取り出した結果なら、表示するときは逆さまにしてください.
以上の転載は(権利侵害や過失があれば連絡してください)http://blog.chinaunix.net/uid-26602509-id-3363512.html
転載先:https://www.cnblogs.com/lishuaige/p/5072352.html
以下は技術スラグの私が度娘の中で見つけた比較的全面的に詳細な分析と最適化方法である.次のようになります.
クエリ文を使用する場合、常に最初の数行または中間の数行のデータが返されますが、この場合はどうしますか?心配しないでください.すでにこのような機能を提供しています.LIMIT句は、SELECT文に指定されたレコード数を返すように強制するために使用することができる.LIMITは1つまたは2つの数値パラメータを受け入れる.パラメータは整数定数でなければなりません.2つのパラメータが与えられた場合、1番目のパラメータは、1番目の戻りレコード行のオフセット量を指定し、2番目のパラメータは、戻りレコード行の最大数を指定します.SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset
これは2つのパラメータで、1つ目はオフセット量で、2つ目は数select*from employee limit 3,7です.//4-11行select*from employee limit 3,1を返します.//4行目のパラメータselect*from employee limit 3を返します.//前の3行に戻る
以前mysqlでページングしていたlimit 100000,20という方法を使っていましたが、あなたもそうだと信じていますが、効率を高めて、ページングのコード効率をもっと高く、もっと速くするには、どうすればいいのでしょうか.
第一部:ページ分けの基本原理を見てみましょう
mysql explain SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20
***************** 1. row **************
id: 1
select_type: SIMPLE
table: message
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 10020
Extra:
1 row in set (0.00 sec)
上のmysql文について説明します:limit 10000,20の意味は条件を満たす10020行をスキャンして、前の10000行を捨てて、最後の20行を返して、問題はここで、limit 1000000100であれば、100100行をスキャンする必要があります.高い同時アプリケーションでは、クエリーのたびに10 W行を超えるスキャンが必要で、性能は大きく割引されます.n行のみをスキャンするため、limit n性能は問題ありません.
第二部分:ヤフーの何人かのエンジニアによってEfficient Pagination Using MySQLの報告内容の拡張をもたらした:本文の中で1種のclueのやり方に言及して、ページをめくることにいくつかの手がかりを提供して、例えばSELECT*FROM message ORDER BY id DESCで、idの降順によってページを分けて、1ページ20条、現在は10ページ目で、現在のページのエントリidは最大1020で、最小は1000で、前のページ、次のページのようなジャンプのみを提供する場合(Nページ目のジャンプは提供されません)、前のページを処理するときにSQL文は次のようになります.
SELECT * FROM message WHERE id>1020 ORDER BY id ASC LIMIT 20;//
次のページを処理するときのSQL文は次のようになります.
SELECT * FROM message WHERE id<1000 ORDER BY id DESC LIMIT 20;//
何ページめくっても、クエリーごとに20行しかスキャンされません.
欠点は前のページ、次のページのリンク形式しか提供できないことですが、私たちのプロダクトマネージャは「前のページ1 2 3 3 4 5 6 7 8 9次のページ」というリンク方式が大好きです.どうすればいいですか?
もしLIMIT m,nが避けられないならば、効率を最適化するには、できるだけmを小さくするしかありません.私たちは前のclueのやり方を拡張しますか、それともSELECT*FROM message ORDER BY id DESCですか.idの降順にページを分けます.1ページ20本です.現在は10ページ目です.現在のページのエントリidは最大2519で、最小は2500です.10ページ目のSQLは次のようになります.
SELECT * FROM tb_goods_info WHERE auto_id >=2500 ORDER BY auto_id ASC LIMIT 0,20
たとえば、9ページにジャンプするには、SQL文を次のように書くことができます.
SELECT * FROM tb_goods_info WHERE auto_id <2500 ORDER BY auto_id desc LIMIT 0,20
たとえば、8ページ目にジャンプする場合、SQL文は次のように書くことができます.
SELECT * FROM tb_goods_info WHERE auto_id <2500 ORDER BY auto_id desc LIMIT 20,20たとえば、7ページ目にジャンプする場合、SQL文は次のように書くことができます.
SELECT * FROM tb_goods_info WHERE auto_id <2500 ORDER BY auto_id desc LIMIT 40,2011ページにジャンプ:
SELECT * FROM tb_goods_info WHERE auto_id >2519 ORDER BY auto_id asc LIMIT 0,20
12ページにジャンプ:
SELECT * FROM tb_goods_info WHERE auto_id >2519 ORDER BY auto_id asc LIMIT 20,20
13ページにジャンプ:
SELECT * FROM tb_goods_info WHERE auto_id >2519 ORDER BY auto_id asc LIMIT 40,20
原理はやはり同じで、現在のページidの最大値と最小値を記録して、ジャンプページと現在のページの相対オフセットを計算して、ページが近いため、このオフセット量は大きくなくて、このようにするとm値は相対的に小さくて、スキャンの行数を大幅に減らします.実は従来のlimit m,n,相対的なオフセットはずっと第1ページであり,このようにすると後ろにひっくり返すほど効率が悪くなり,上記の方法ではこのような問題はない.
SQL文の中のASCとDESCに注意して、ASCが取り出した結果なら、表示するときは逆さまにしてください.
以上の転載は(権利侵害や過失があれば連絡してください)http://blog.chinaunix.net/uid-26602509-id-3363512.html
転載先:https://www.cnblogs.com/lishuaige/p/5072352.html