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
問題解答
回答1:不要使用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
這樣你可以發現響應速度超快。不過有個問題是無法前往指定頁數,不過從我目前的實際情況來看,這個沒有影響,有個搜索功能就可以彌補
回答2:優化分頁的問題其實就是offset的問題,尤其是偏移量大的時候。mysql會掃描大量不需要的行然后拋棄,只取limit的數量。所以一般最好不要用offset。解決方法有1.可以先使用索引覆蓋掃描,而不是查詢所有的列,然后做關聯操作返回相關的列。這個方法可以叫做“延遲關聯”2.可以把limit查詢轉換成已知位置的查詢,變成between XXX and XXX 。3.可以記錄上次查詢的數據的位置,下一次查詢直接從該位置開始掃描,樓上就是用的這種辦法。
鑒于問題好像只查詢id一個字段。1方法用不到,2,3可以考慮。
相關文章:
1. javascript - vuejs+elementui 購物車價格計算,點擊加減號修改數量總價都不會改變,但是計算執行了2. css右浮動字的順序顛倒了3. php - 請問大批量數據處理,如何分割?4. MySQL主鍵沖突時的更新操作和替換操作在功能上有什么差別(如圖)5. javascript - 我是做web前端的,公司最近有一個項目關于數據統計的!6. ios - 類似微博首頁,一張圖的時候是如何確定圖大小的?7. javascript - 如何使用loadash對[object,object,object]形式的數組進行比較8. javascript - vue過渡效果 css過渡 類名的先后順序9. html5和Flash對抗是什么情況?10. 數據庫 - Mysql的存儲過程真的是個坑!求助下面的存儲過程哪里錯啦,實在是找不到哪里的問題了。
