SQL數據量很大,分頁查詢很慢,怎麼破解?(SQL分頁查詢 提升 效率/速度 虛擬表/子查詢)
為了對下面列舉的一些優化進行測試,下面針對已有的一張表進行說明。
▲表名:order_history
▲描述:某個業務的訂單歷史表
▲主要字段:unsigned int id,tinyint(4) int type
▲字段情況:該表一共37個字段,不包含text等大型數據,最大為varchar(500),id字段為索引,且為遞增。
▲數據量:5709294
▲MySQL版本:5.7.16 線下找一張百萬級的測試表可不容易,如果需要自己測試的話,可以寫shell腳本什麼的插入數據進行測試。以下的sql 所有語句執行的環境沒有發生改變,下面是基本測試結果:
select count(*) from orders_history; #返回結果:5709294 #三次查詢時間分別為: #8903 ms #8323 ms #8401 ms
SELECT * FROM table LIMIT [ offset ,] rows | rows OFFSET offset
#LIMIT 子句可以被用於指定SELECT 語句返回的記錄數。需注意以下幾點:
#第一個參數指定第一個返回記錄行的偏移量,注意從0開始
#第二個參數指定返回記錄行的最大數目
#如果只給定一個參數:它表示返回最大的記錄行數目
#第二個參數為-1 表示檢索從某一個偏移量到記錄集的結束所有的記錄行
#初始記錄行的偏移量是0(而不是1)
select * from orders_history where type = 8 order by id limit 10000 , 10 ; #三次查詢時間分別為: #3040 ms #3063 ms #3018 ms
最後實驗
select * from orders_history where type = 8 limit 100000 , 1 ; select id from orders_history where type = 8 limit 100000 , 1 ; select * from orders_history where type = 8 and id >=( select id from orders_history where type = 8 limit 100000 , 1 ) limit 100 ; select * from orders_history where type = 8 limit 100000 , 100 ; #第1條語句:3674ms #第2條語句:1315ms #第3條語句:1327ms #第4條語句:3710ms