1. MySQL大數據量分頁查詢方法及其優化
使用子查詢優化大數據量分頁查詢
這種方式的做法是先定位偏移位置的id，然後再往後查詢，適用於id遞增的情況。
使用id限定優化大數據量分頁查詢
使用這種方式需要先假設數據表的id是連續遞增的，我們根據查詢的頁數和查詢的記錄數可以算出查詢的id的范圍，可以使用 id between and 來查詢：
當然了，也可以使用in的方式來進行查詢，這種方式經常用在多表關聯的情況下，使用其他表查詢的id集合來進行查詢：
但是使用這種in查詢方式的時候要注意的是，某些MySQL版本並不支持在in子句中使用limit子句。
參考 sql優化之大數據量分頁查詢（mysql） - yanggb - 博客園 (cnblogs.com)
2. 如果在資料庫中有大數據量，而我們用分頁存儲過程，怎麼樣才能效率高
--------------------------------
--關於分頁儲存的效率問題
--5個存儲過程都是採用不同的方式
--------------------------------
------------------------------------------
--利用select top 和select not in進行分頁--
------------------------------------------
create procere proc_paged_with_notin --利用select top and select not in
(
@pageIndex int, --頁索引
@pageSize int --每頁記錄數
)
as
begin
set nocount on;
declare @timediff datetime --耗時
declare @sql nvarchar(500)
select @timediff=Getdate()
set @sql='select top '+str(@pageSize)+' * from tb_TestTable where(ID not in(select top '+str(@pageSize*@pageIndex)+' id from tb_TestTable order by ID ASC)) order by ID'
execute(@sql) --因select top後不支技直接接參數，所以寫成了字元串@sql
select datediff(ms,@timediff,GetDate()) as 耗時
set nocount off;
endexec proc_paged_with_notin 10000,10
--------------------------------------
--利用select top 和 select max(列鍵)--
--------------------------------------
create procere proc_paged_with_selectMax --利用select top and select max(列)
(
@pageIndex int, --頁索引
@pageSize int --頁記錄數
)
as
begin
set nocount on;
declare @timediff datetime
declare @sql nvarchar(500)
select @timediff=Getdate()
set @sql='select top '+str(@pageSize)+' * From tb_TestTable where(ID>(select max(id) From (select top '+str(@pageSize*@pageIndex)+' id From tb_TestTable order by ID) as TempTable)) order by ID'
execute(@sql)
select datediff(ms,@timediff,GetDate()) as 耗時
set nocount off;
end--------------------------------------------------------
--利用select top和中間變數--此方法因網上有人說效果最佳--
--------------------------------------------------------
create procere proc_paged_with_Midvar --利用ID>最大ID值和中間變數
(
@pageIndex int,
@pageSize int
)
as
declare @count int
declare @ID int
declare @timediff datetime
declare @sql nvarchar(500)
begin
set nocount on;
select @count=0,@ID=0,@timediff=getdate()
select @count=@count+1,@ID=case when @count<=@pageSize*@pageIndex then ID else @ID end from tb_testTable order by id
set @sql='select top '+str(@pageSize)+' * from tb_testTable where ID>'+str(@ID)
execute(@sql)
select datediff(ms,@timediff,getdate()) as 耗時
set nocount off;
end
---------------------------------------------------------------------------------------
--利用Row_number() 此方法為SQL server 2005中新的方法,利用Row_number()給數據行加上索引--
---------------------------------------------------------------------------------------
create procere proc_paged_with_Rownumber --利用SQL 2005中的Row_number()
(
@pageIndex int,
@pageSize int
)
as
declare @timediff datetime
begin
set nocount on;
select @timediff=getdate()
select * from (select *,Row_number() over(order by ID asc) as IDRank from tb_testTable) as IDWithRowNumber where IDRank>@pageSize*@pageIndex and IDRank<@pageSize*(@pageIndex+1)
select datediff(ms,@timediff,getdate()) as 耗時
set nocount off;
end
--------------------------
--利用臨時表及Row_number--
--------------------------
create procere proc_CTE --利用臨時表及Row_number
(
@pageIndex int, --頁索引
@pageSize int --頁記錄數
)
as
set nocount on;
declare @ctestr nvarchar(400)
declare @strSql nvarchar(400)
declare @datediff datetime
begin
select @datediff=GetDate()
set @ctestr='with Table_CTE as
(select ceiling((Row_number() over(order by ID ASC))/'+str(@pageSize)+') as page_num,* from tb_TestTable)';
set @strSql=@ctestr+' select * From Table_CTE where page_num='+str(@pageIndex)
end
begin
execute sp_executesql @strSql
select datediff(ms,@datediff,GetDate())
set nocount off;
end
我們分別在每頁10條數據的情況下在第2頁，第1000頁，第10000頁，第100000頁，第199999頁進行測試，耗時單位：ms 每頁測試5次取其平均值 存過第2頁耗時第1000頁耗時第10000頁耗時第100000頁耗時第199999頁耗時效率排行1用not in0ms16ms47ms475ms953ms32用select max5ms16ms35ms325ms623ms13中間變數_number0ms0ms34ms365ms710ms24臨時表780ms796ms798ms780ms805ms4正好我正在研究這個問題 給大家分享
3. 大數據量實時統計排序分頁查詢 優化總結
大數據量實時統計排序分頁查詢 (並發數較小時) 的瓶頸不是函數（count，sum等）執行，
不是having, 也不是order by，甚至不是表join, 導致慢的原因就在於「數據量太大本身」
就是將表劃分為M份相互獨立的部分,可以是分表，也可以是不分表但冗餘一個取模結果欄位
實際結果是不分表比分表更加靈活，只需稍加配置，就可以動態切分大表，隨意更改M的大小。
將1條慢sql（大於30秒）拆分成為N條查詢速度巨快的sql(單條sql執行時間控制在20毫秒以內)
然後再web應用中以適當的線程數去並發查詢這些執行時間快的N條小sql再匯總結果
第一步查詢中去並發執行這N條小sql, 只取排序欄位和標識欄位，其他欄位一律丟棄
匯總結果後定位出當前頁面要顯示的pageNum條數據，再進行第二步查詢，取出頁面上需要展示的所有欄位
PS：這一點是至關重要的，其他幾點都可以不看，這點是最關鍵的。慢慢解釋一下：
a) 第一種方式是把資料庫中所有記錄（只取排序欄位和標識欄位並且不做任何sum，count having order by等操作）
全部拉到web應用中，在web應用中完成所有的計算
b) 第二種方式是把資料庫中所有記錄做sum count having等操作之後的所有行數拉到web應用中，在web應用中完成剩餘計算
c) 第三種方式是把資料庫中所有記錄做sum count having order by等操作之後把limit後的數據拉到web應用中，
在web應用中對limit後的數據再計算
顯然，第一種方式 資料庫什麼活都不做只取數據 是不可行的。以lg_order_count_seller為例，1500萬行，
如果只算id, seller_id和order_count 這三個bigint類型，至少需要拉8*3*1500 0000 = 360000000=340M,
拉到內存中之後存儲需要8*4*15000000= 460M,這還不算List是的2的n次方這個特點和計算排序等的內存開銷，
不僅資料庫與web應用機器IO扛不住，就是應用自身恐怕也要OOM了。
第二種方式，所有記錄做sum count having等操作之後,由於是group by seller_id的，總得數據量變為100萬（就是賣家總數），
這樣子一來，共需要拉8*3*100 0000 = 23M,拉到內存之後，需要8*4*100 0000 = 30M, 再算上List是的2的n次方這個特點和
計算排序等的內存開銷也不會超過100M, IO的時間和內存開銷勉強可以考慮接受。
第三種方式，所有記錄做sum count having order by等操作之後把limit後的數據拉到web應用中，因為做了limit，所以，
數據量很小了，無論是IO還是內存開銷都已經很小了。可以忽略。
綜合以上三種，第三種方式適用於頁面的前n頁和後n頁，因為這個limit的數據量隨著頁數的增大而增大，
當大到每個切分後的小表的數據量時就轉為第二種方式了。
第二種方式適用於頁面的第[n+1, totaoPageNum-n]頁。
切分成N條小sql後並行執行時排序不穩定性的解決辦法
① 問題描述：
優化之前，還是是一條大慢sql查詢時，由於資料庫排序是穩定排序，
所以當兩條記錄排序欄位值相同時他們在頁面上的頁碼位置是固定的。
優化之後，當並行執行這N條小sql時，由於無法控制這些小sql的先後執行順序，
導致在web應用中當兩條記錄的排序欄位值相同時在頁面上的頁碼位置是隨機的。
② 解決辦法：
除了拉標識欄位(seller_id)和排序欄位(order_count_sum)之外，再取一個unique（id）的欄位，當兩條記錄的排序欄位值相同時，再用這個unique的欄位（在賣家監控中這個欄位是id）進行第二次排序.這樣就解決了排序不穩定的問題。
③ 也許，看到這里會有疑問，為什麼不用seller_id？seller_id也是唯一， 這樣子不是少取id這個欄位，減少IO了？
seller_id雖然也是唯一，可以輔助排序，但是不要忘記資料庫的排序規則是：
如果兩列的值相等，那麼序號在前的排在前面，這里的序號就是主鍵(自動生成，autoincrement),
如果用seller_id的話還是不能保證排序的穩定性，只能用主鍵id.
優先載入頁面上的主要元素，然後再去非同步載入次要元素，
反應在賣家監控頁面中，查數據和查頁頁碼的sql語句基本相同，是在競爭同一資源，
所以，需要做一個策略，優先把資源讓給查數，數據查完之後再去查頁碼。
限流
由於多線程取數據並沒有從本質上提高資料庫性能，所以必須針對大數據量實時統計排序分頁查詢做限流
我這里打個比方：食堂有6個窗口，物流團隊吃飯要買6個菜，平均每買1個菜需要1分鍾的時間，
如果派我一個人去一個窗口買的話需要6分鍾的時間
假如派6個人分別去6個窗口買這6個菜，只需要1分鍾的時間
但是，如果除了物流團隊，再來其他5個團隊呢，也就是說6個團隊每個團隊買6個菜共買36個菜，
這樣子有的團隊先買完，有的團隊後買完，但平均時間還是6分鍾。本質上沒有變化。
所以，對於特定的查詢條件，必須進行限流。讓每分鍾至多有6個團隊買菜，這樣子能使得情況變得不至於太糟糕。
從根本上改變現狀
這一點從目前來看只能是展望了，比如mysql資料庫換更為強大的oracle資料庫，
或更換InnoDb引擎為其他，或更換SATA硬碟為SSD 。。。。。。
從實踐效果來看，優化後的效果是很明顯的。
相同的查詢條件，原來一個頁面查詢時間由於超過60秒超時了，根據1-6點建議優化之後，查詢時間變為2秒至3.5秒之間。
4. sql怎麼控制檢索出的最大數據量，數量太大機器受不了，有能分批檢索的命令嗎
用分頁查詢演算法來實現。給你一個我寫的通用分頁存儲過程，將這個存儲過程創建在你的SQL資料庫上，調用該過程即可實現分頁查詢：
/*
通用存儲過程
只要傳入頁碼，每頁大小，查詢的sql語句，排序方式（不需要order）即可
*/
CREATE Procere [sp_common_cuspage3]
(
@PageNo int, --當前查詢頁碼
@PageSize int, --每頁數量
@sql nvarchar(2000), --查詢的SQL語句
@order nvarchar(200), --排序方式，例如：ResID desc
@totalcount int out --返回當前查詢SQL的符合總條數
)
AS
Begin
declare @querysql nvarchar(2000)
declare @countsql nvarchar(2000)
declare @begin int
declare @end int
declare @totalPage int
--查詢符合條件的條目數
set @countsql = N'select @count = count(*) from ('+@sql + N')G0'
exec sp_executesql @countsql, N' @count int output ', @totalcount output
--計算總頁碼及糾正當前頁碼
set @totalPage = (@totalcount-1)/@PageSize +1
IF(@PageNo > @totalPage ) set @PageNo = @totalPage
--計算起止位置
set @begin = @PageSize * (@PageNo-1)
set @end = @PageSize * @PageNo +1
--組合出SQL進行查詢
set @querysql = N'select * from ('
set @querysql = @querysql + N' SELECT Row_Number() OVER(ORDER BY ' + @order + N' ) as RowID ,G0.* FROM ('
set @querysql = @querysql + @sql
set @querysql = @querysql + N' ) G0 ) G1'
set @querysql = @querysql + N' Where G1.RowID >' + CAST(@begin as nvarchar) + N' AND G1.RowID<'+CAST(@end as nvarchar)
--print @querysql
exec(@querysql)
End