mysql優化 - mysql 分頁查詢優化。

問題描述

table 表中30萬記錄 id，自增主鍵， node，create_at 都有索引 但是沒有聯合索引

下面的語句查一次要8s左右， 可以預估隨著數據的繼續增加，速度會越來越慢。 最近在學習 mysql 查詢優化

也看了很多文章，教程（但是沒有系統的看mysql手冊，不好意思）

請各位朋友指導下，如何優化，如果可以 請大概講述下，怎么分析的，為什么使用xxxx方式優化就會有效。

謝謝各位。

EXPLAINSELECT `id` FROM `table` WHERE `node` = 2 ORDER BY `create_at` DESC LIMIT 12 OFFSET 69996------------------------------------------------------------------------ id: 1 select_type: SIMPLEtable: table type: refpossible_keys: node key: node key_len: 5 ref: const rows: 72278Extra: Using where; Using filesort

問題解答

不要使用OFFSET方式分頁以你的例子來說

MySQL會先查詢所有符合條件的數據，通過EXPLAIN可以發現（72278），查詢了這么多

因為OFFSET的關系，MySQL丟棄前面（69996）的記錄。查詢優化就是指在第1步的時候就讓MySQL查詢最少的數據。

我目前在用的分頁方式（數據量千萬級），依舊拿你的例子來說，假設分頁大小為10第一頁

#查詢1SELECT `id` FROM `table` WHERE `node` = 2 ORDER BY `id` ASC LIMIT 10

假設查詢1的第10條數據的id是10，第1條數據的id是1那么查詢第二頁的SQL如下

SELECT `id` FROM `table` WHERE `node` = 2 AND `id`>10 ORDER BY `id` ASCLIMIT 10

這樣你可以發現響應速度超快。不過有個問題是無法前往指定頁數，不過從我目前的實際情況來看，這個沒有影響，有個搜索功能就可以彌補

優化分頁的問題其實就是offset的問題，尤其是偏移量大的時候。mysql會掃描大量不需要的行然后拋棄，只取limit的數量。所以一般最好不要用offset。解決方法有1.可以先使用索引覆蓋掃描，而不是查詢所有的列，然后做關聯操作返回相關的列。這個方法可以叫做“延遲關聯”2.可以把limit查詢轉換成已知位置的查詢，變成between XXX and XXX 。3.可以記錄上次查詢的數據的位置，下一次查詢直接從該位置開始掃描，樓上就是用的這種辦法。

鑒于問題好像只查詢id一個字段。1方法用不到，2，3可以考慮。