MySQL 分頁查詢的優(yōu)化技巧
在有分頁查詢的應用中,包括 LIMIT 和 OFFSET 的查詢十分常見,而且?guī)缀趺總€都會有一個 ORDER BY 子句。如果使用索引排序的話將對性能優(yōu)化十分有幫助,否則服務端需要做很多文件排序。
一個高頻的問題是 offset 的值過大。如果查詢類似 LIMIT 10000, 20,將會產(chǎn)生10020行,并將之前的10000行丟棄,這樣的代價很高。假設所有的頁使用相同的頻次訪問,這樣的查詢將平均掃描一半數(shù)據(jù)表。為了優(yōu)化他們,你可以在分頁視圖中限制最多可訪問的頁數(shù),或者讓大便宜的查詢更有效。
一個改善性能簡單的技巧是在覆蓋索引上進行查詢操作而不是整行數(shù)據(jù)。你可以將結果與完整的行做一次聯(lián)合然后再獲取額外需要的列。這樣的效率會更高,例如下面的查詢:
SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;
如果數(shù)據(jù)表很大的話,則可以按下面的方式進行優(yōu)化:
SELECT film.film_id, film.descriptionFROM sakila.filmINNER JOIN ( SELECT film_id FROM sakila.film ORDER BY title LIMIT 50, 5) ) as lim USING(film_id);
這種“推斷聯(lián)合查詢”能夠有效工作是因為它使用了索引減少了服務端盡可能少地訪問數(shù)據(jù)行去檢查數(shù)據(jù)。一旦復核要求的行查到了,將他們與對應的數(shù)據(jù)表的行進行聯(lián)合查詢以獲取對應行的其他列。
有些時候也可以將 limit 轉換為固定位置的查詢,這種方式可以對索引進行范圍掃描完成。例如,如果你預先計算一個固定位置的列 稱之為 position,可以重寫查詢?nèi)缦拢?/p>
SELECT film_id, description FROM sakila.filmWHERE position BETWEEN 50 AND 54 ORDER BY position;
排序的數(shù)據(jù)也可以使用類似的方式解決,但是通常會被 GROUP BY操作影響。大部分情況下需要提前計算和存儲排序值。
LIMIT 和 OFFSET 真正的問題是在OFFSET,這意味著服務端會把很多數(shù)據(jù)行丟棄。如果使用一個有序書簽來記錄下次獲取行的位置的話,則可以從上次的位置開始訪問接下來的數(shù)據(jù)。例如,如果你需要對出租記錄進行分頁,從最新的出租記錄開始往回查詢,則可以依賴于記錄的主鍵是一直增加的,因此可以對第一頁數(shù)據(jù)這樣查詢:
SELECT * FROM sakila.rentalORDER BY rental_id DESC LIMIT 20;
這個查詢返回16049到16030之間的數(shù)據(jù)。接下來的查詢可以從之前結束位置開始:
SELECT * FROM sakila.rentalWHERE rental_id < 16030 ORDER BY rental_id DESC LIMIT 20;
這個技巧不管你從多遠的偏移值開始查詢都是很有效的。
其他的一些技巧包括使用預先計算的統(tǒng)計值,或者通過聯(lián)合冗余了主鍵和排序列的數(shù)據(jù)表進行查詢,這兩種方式都是通過空間換取時間的方式提高查詢效率。
以上就是MySQL 分頁查詢的優(yōu)化技巧的詳細內(nèi)容,更多關于MySQL 分頁查詢的優(yōu)化的資料請關注好吧啦網(wǎng)其它相關文章!
相關文章:
1. 詳解mysql的備份與恢復2. sql server 2005中的表分區(qū)3. sql server添加唯一性約束的方法步驟4. 使用DB2look 重新創(chuàng)建優(yōu)化器訪問計劃(3)(1)5. 利用sql server 2005數(shù)據(jù)庫郵件發(fā)送電子郵件6. MySQL之高可用集群部署及故障切換實現(xiàn)7. Centos7 下mysql重新啟動MariaDB篇8. MariaDB的安裝與配置教程9. 有時Oracle不用索引來查找數(shù)據(jù)的原因10. MySQL 編碼utf8 與 utf8mb4 utf8mb4_unicode_ci 與 utf8mb4_general_ci
