mysql大資料量之limit最佳化

標籤：

背景：當資料庫裡面的資料達到幾百萬條上千萬條的時候，如果要分頁的時候（不過一般分頁不會有這麼多），如果業務要求這麼做那我們需要如何解決呢？
我用的本地一個自己生產的一張表有五百多萬的表，來進行測試，表名為big_data;
首先我們看如下幾條sql語句：
在這之前我們開啟profiling來監測sql語句執行的情況。
set profiling=1;
1.查詢從第10w條資料開始分頁10條
2.查詢從第20w條資料分頁10條
3.查詢從第30w條資料分頁10條

3.查詢從第300w條資料分頁10條

3.查詢從第500w條資料分頁10條

我們可以看出查詢從200w開始分頁的都還比較快，但從500w開始速度就變的很慢了，這個是不太讓人滿意的。

mysql> select id,my_name from big_data limit 5000000,10;

+---------+------------+

| id      | my_name    |

+---------+------------+

| 5000001 | kwCwziqhNu |

| 5000002 | NLpqMMwaJv |

| 5000003 | kskUTLXDbx |

| 5000004 | PtAvBtpubZ |

| 5000005 | whsuShiuvX |

| 5000006 | TcDLWzHNQT |

| 5000007 | qHmnEkjsmh |

| 5000008 | UQrmluqvgr |

| 5000009 | UzKeqpEbtQ |

| 5000010 | SkuvSePMpq |

+---------+------------+

10 rows in set (2.34 sec)

mysql> show profiles;

+----------+------------+--------------------------------------------------+

| Query_ID | Duration   | Query                                            |

+----------+------------+--------------------------------------------------+

|        1 | 0.02591075 | select id,my_name from big_data limit 100000,10  |

|        2 | 0.05773150 | select id,my_name from big_data limit 200000,10  |

|        3 | 0.08253525 | select id,my_name from big_data limit 300000,10  |

|        4 | 1.38455375 | select id,my_name from big_data limit 3000000,10 |

|        5 | 2.34040775 | select id,my_name from big_data limit 5000000,10 |

+----------+------------+--------------------------------------------------+

5 rows in set, 1 warning (0.00 sec)

show  profiles；

我們就如下兩種解決方案：
（1）、通過判斷id的範圍來分頁
select  id,my_sn from big_data where id>5000000 limit 10;
也得到了分頁的資料，但是我們發現如果id不是順序的，也就是如果有資料刪除過的話，那麼這樣分頁資料就會不正確，這個是有缺陷的。
（2）、通過串連查詢來分頁
我們可以先查詢500w條資料開始分頁的那10個id，然後通過串連查詢顯示資料
mysql> select b.id,b.my_name from big_data as b  inner join (select id from big_data order by id limit 4500000,10) as  tmp on tmp.id=b.id;

我們測試不同開始端點的分頁資料

 

mysql> select b.id,b.my_name from big_data as b  inner join (select id from big_data order by id limit 5000000,10) as  tmp on tmp.id=b.id;

+---------+------------+

| id      | my_name    |

+---------+------------+

| 5000001 | kwCwziqhNu |

| 5000002 | NLpqMMwaJv |

| 5000003 | kskUTLXDbx |

| 5000004 | PtAvBtpubZ |

| 5000005 | whsuShiuvX |

| 5000006 | TcDLWzHNQT |

| 5000007 | qHmnEkjsmh |

| 5000008 | UQrmluqvgr |

| 5000009 | UzKeqpEbtQ |

| 5000010 | SkuvSePMpq |

+---------+------------+

10 rows in set (2.15 sec)

 

mysql> show profiles;

+----------+------------+------------------------------------------------------------------------------------------------------------------------------------+

| Query_ID | Duration   | Query                                                                                                                              |

+----------+------------+------------------------------------------------------------------------------------------------------------------------------------+

|        1 | 0.02591075 | select id,my_name from big_data limit 100000,10                                                                                    |

|        2 | 0.05773150 | select id,my_name from big_data limit 200000,10                                                                                    |

|        3 | 0.08253525 | select id,my_name from big_data limit 300000,10                                                                                    |

|        4 | 1.38455375 | select id,my_name from big_data limit 3000000,10                                                                                   |

|        5 | 2.34040775 | select id,my_name from big_data limit 5000000,10                                                                                   |

|        6 | 0.00004200 | reset query cache                                                                                                                  |

|        7 | 0.01999275 | select b.id,b.my_name from big_data as b  inner join (select id from big_data order by id limit 100000,10) as  tmp on tmp.id=b.id  |

|        8 | 0.03888825 | select b.id,b.my_name from big_data as b  inner join (select id from big_data order by id limit 200000,10) as  tmp on tmp.id=b.id  |

|        9 | 0.37394450 | select b.id,b.my_name from big_data as b  inner join (select id from big_data order by id limit 1000000,10) as  tmp on tmp.id=b.id |

|       10 | 1.33475700 | select b.id,b.my_name from big_data as b  inner join (select id from big_data order by id limit 3000000,10) as  tmp on tmp.id=b.id |

|       11 | 2.14759000 | select b.id,b.my_name from big_data as b  inner join (select id from big_data order by id limit 5000000,10) as  tmp on tmp.id=b.id |

如果懷疑有緩衝的緣故我們可以清楚緩衝後來查詢

reset query cache;

?
show profile for query 3;//查看被記錄的第三條sql語句的執行情況
可以看出兩種方法查出來的資料都是一致的，但通過方法二的速度比之前單表查詢的速度快了一些。

分析：因為mysql分頁查詢是先把分頁之前資料都查詢出來了，然後截取後把不是分頁的資料給扔掉後得到的結果這樣，所以資料量太大了後分頁緩慢是可以理解的。
但是我們可以先把需要分頁的id查詢出來，因為id是主鍵id主鍵索引，查詢起來還是快很多的，然後根據id串連查詢對應的分頁資料，可見並不是所有的串連查詢都會比
單查詢要慢，要依情況而定。

 

mysql大資料量之limit最佳化