mysql 分表分頁查詢解決思路
當業務上按照月份分表，但是前端h5需要分頁展示，小伙伴們不知有沒有遇到這個這個需求最后怎么完成的。
我這里想了一個解決思路，可能還不完善，希望能拋轉引玉。
1、入參pageNo 為頁號碼，如果為1那么就是第一頁。pageSize 可以是入參也可定死，這里定死10條。Limit 是數據偏移標記，根據入參pageNo 計算出來的，Limit=(pageNo-1)*pageSize。假設A表有41條數據符合，B表有51條數據符合，如下圖。
有幾種種情況 1.如果limit<A表41條 那么需要從A表中取數據，（1）如果Limit+pageSize<Count直接獲取數據即可（2）如果Limit+pageSize>Count,那么需要從A 表取部分數據從B表取一部分數據。
1.如果limit>A表41條 那么需要從B表取數據，如果B數據依然不足，那么重復以上的步驟。如下圖
mysql數據庫分頁
很多應用往往只展示最新或最熱門的幾條記錄，但為了舊記錄仍然可訪問，所以就需要個分頁的導航欄。然而，如何通過MySQL更好的實現分頁，始終是比較令人頭疼的問題。雖然沒有拿來就能用的解決辦法，但了解數據庫的底層或多或少有助于優化分頁查詢。
我們先從一個常用但性能很差的查詢來看一看。
SELECT *
FROM city
ORDER BY id DESC
LIMIT 0, 15
這個查詢耗時0.00c。So，這個查詢有什么問題呢？實際上，這個查詢語句和參數都沒有問題，因為它用到了下面表的主鍵，而且只讀取15條記錄。
CREATE TABLE city (
id int(10) unsigned NOT NULL AUTO_INCREMENT,
city varchar(128) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB;
真正的問題在于offt(分頁偏移量)很大的時候，像下面這樣：
SELECT *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;
上面的查詢在有2M行記錄時需要0.22c，通過EXPLAIN查看SQL的執行計劃可以發現該SQL檢索了100015行，但最后只需要15行。大的分頁偏移量會增加使用的數據，MySQL會將大量最終不會使用的數據加載到內存中。就算我們假設大部分網站的用戶只訪問前幾頁數據，但少量的大的分頁偏移量的請求也會對整個系統造成危害。Facebook意識到了這一點，但Facebook并沒有為了每秒可以處理更多的請求而去優化數據庫，而是將重心放在將請求響應時間的方差變小。
對于分頁請求，還有一個信息也很重要，就是總共的記錄數。我們可以通過下面的查詢很容易的獲取總的記錄數。
SELECT COUNT(*)
FROM city;
然而，上面的SQL在采用InnoDB為存儲引擎時需要耗費9.28c。一個不正確的優化是采用 SQL_CALC_FOUND_ROWS,SQL_CALC_FOUND_ROWS 可以在能夠在分頁查詢時事先準備好符合條件的記錄數，隨后只要執行一句 lect FOUND_ROWS(); 就能獲得總記錄數。但是在大多數情況下，查詢語句簡短并不意味著性能的提高。不幸的是，這種分頁查詢方式在許多主流框架中都有用到，下面看看這個語句的查詢性能。
SELECT SQL_CALC_FOUND_ROWS *
FROM city
ORDER BY id DESC
LIMIT 100000, 15;
這個語句耗時20.02c，是上一個的兩倍。事實證明使用 SQL_CALC_FOUND_ROWS 做分頁是很糟糕的想法。
下面來看看到底如何優化。文章分為兩部分，第一部分是如何獲取記錄的總數目，第二部分是獲取真正的記錄。
高效的計算行數
如果采用的引擎是MyISAM，可以直接執行COUNT(*)去獲取行數即可。相似的，在堆表中也會將行數存儲到表的元信息中。但如果引擎是InnoDB情況就會復雜一些，因為InnoDB不保存表的具體行數。
我們可以將行數緩存起來，然后可以通過一個守護進程定期更新或者用戶的某些操作導致緩存失效時，執行下面的語句：
SELECT COUNT(*)
FROM city
USE INDEX(PRIMARY);
獲取記錄
下面進入這篇文章最重要的部分，獲取分頁要展示的記錄。上面已經說過了，大的偏移量會影響性能，所以我們要重寫查詢語句。為了演示，我們創建一個新的表“news”，按照時事性排序(最新發布的在最前面)，實現一個高性能的分頁。為了簡單，我們就假設最新發布的新聞的Id也是最大的。
CREATE TABLE news(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(128) NOT NULL
) ENGINE=InnoDB;
一個比較高效的方式是基于用戶展示的最后一個新聞Id。查詢下一頁的語句如下，需要傳入當前頁面展示的最后一個Id。
SELECT *
FROM news WHERE id < $last_id
ORDER BY id DESC
LIMIT $perpage
查詢上一頁的語句類似，只不過需要傳入當前頁的第一個Id，并且要逆序。
SELECT *
FROM news WHERE id > $last_id
ORDER BY id ASC
LIMIT $perpage
上面的查詢方式適合實現簡易的分頁，即不顯示具體的頁數導航，只顯示“上一頁”和“下一頁”，例如博客中頁腳顯示“上一頁”，“下一頁”的按鈕。但如果要實現真正的頁面導航還是很難的,下面看看另一種方式。
SELECT id
FROM (
SELECT id, ((@cnt:= @cnt + 1) + $perpage - 1) % $perpage cnt
FROM news
JOIN (SELECT @cnt:= 0)T
WHERE id < $last_id
ORDER BY id DESC
LIMIT $perpage * $buttons
)C
WHERE cnt = 0;
通過上面的語句可以為每一個分頁的按鈕計算出一個offt對應的id。這種方法還有一個好處。假設，網站上正在發布一片新的文章，那么所有文章的位置都會往后移一位，所以如果用戶在發布文章時換頁，那么他會看見一篇文章兩次。如果固定了每個按鈕的offt Id，這個問題就迎刃而解了。Mark Callaghan發表過一篇類似的博客，利用了組合索引和兩個位置變量，但是基本思想是一致的。
如果表中的記錄很少被刪除、修改，還可以將記錄對應的頁碼存儲到表中，并在該列上創建合適的索引。采用這種方式，當新增一個記錄的時候，需要執行下面的查詢重新生成對應的頁號。
SET p:= 0;
UPDATE news SET page=CEIL((p:= p + 1) / $perpage) ORDER BY id DESC;
當然，也可以新增一個專用于分頁的表，可以用個后臺程序來維護。
UPDATE pagination T
JOIN (
SELECT id, CEIL((p:= p + 1) / $perpage) page
FROM news
ORDER BY id
)C
ON C.id = T.id
SET T.page = C.page;
現在想獲取任意一頁的元素就很簡單了：
SELECT *
FROM news A
JOIN pagination B ON A.id=B.ID
WHERE page=$offt;
還有另外一種與上種方法比較相似的方法來做分頁，這種方式比較試用于數據集相對小，并且沒有可用的索引的情況下—比如處理搜索結果時。在一個普通的服務器上執行下面的查詢，當有2M條記錄時，要耗費2c左右。這種方式比較簡單，創建一個用來存儲所有Id的臨時表即可(這也是最耗費性能的地方)。
CREATE TEMPORARY TABLE _tmp (KEY SORT(random))
SELECT id, FLOOR(RAND() * 0x8000000) random
FROM city;
ALTER TABLE _tmp ADD OFFSET INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, DROP INDEX SORT,ORDER BY random;
接下來就可以向下面一樣執行分頁查詢了。
SELECT *
FROM _tmp
WHERE OFFSET >= $offt
ORDER BY OFFSET
LIMIT $perpage;
簡單來說，對于分頁的優化就是。。。避免數據量大時掃描過多的記錄。
mysql 數據量大的表如何做分頁查詢
直接用limit start, count分頁語句， 也是我程序中用的方法：
lect * from product limit start, count
當起始頁較小時，查詢沒有性能問題，我們分別看下從10， 100， 1000， 10000開始分頁的執行時間（每頁取20條）， 如下：
lect * from product limit 10, 20 0.016秒
lect * from product limit 100, 20 0.016秒
lect * from product limit 1000, 20 0.047秒
lect * from product limit 10000, 20 0.094秒
我們已經看出隨著起始記錄的增加，時間也隨著增大， 這說明分頁語句limit跟起始頁碼是有很大關系的，那么我們把起始記錄改為40w看下（也就是記錄的一般左右） lect * from product limit 400000, 20 3.229秒
再看我們取最后一頁記錄的時間
lect * from product limit 866613, 20 37.44秒
難怪搜索引擎抓取我們頁面的時候經常會報超時，像這種分頁最大的頁碼頁顯然這種時
間是無法忍受的。
從中我們也能總結出兩件事情：
1）limit語句的查詢時間與起始記錄的位置成正比
2）mysql的limit語句是很方便，但是對記錄很多的表并不適合直接使用。
MySQL百萬級數據量分頁查詢方法及其優化建議
offt+limit方式的分頁查詢，當數據表超過100w條記錄，性能會很差。
主要原因是offt limit的分頁方式是從頭開始查詢，然后舍棄前offt個記錄，所以offt偏移量越大，查詢速度越慢。
比如: 讀第10000到10019行元素(pk是主鍵/唯一鍵).
使用order by id可以在查詢時使用主鍵索引。
但是這種方式在id為uuid的時候就會出現問題。可以使用where in的方式解決：
帶條件的查詢：
如果在分頁查詢中添加了where條件例如 type = 'a’這樣的條件，sql變成 ：
這種情況因為type沒有使用索引也會導致查詢速度變慢。但是只添加type為索引查詢速度還是很慢,是因為查詢的數據量太多了。這個時候考慮添加組合索引，組合索引的順序要where條件字段在前，id在后，如 (type,id)，因為組合索引查詢時用到了type索引，而type跟id是組合索引的關系，如果只lect id ，那么直接就可以按組合索引返回id，而不需要再進行一次查詢去返回id
使用uuid作為主鍵不僅會帶來性能上的問題，在查詢時也會遇到問題。
因為在使用lect id from table limit 10000,10 查詢id數據時，默認是對id進行排序，返回的是排序后的id結果，如果我們想按插入順序查詢結果，這樣查詢出來的結果就與我們的需求不相符。
聚集索引跟非聚集索引：聚集索引類似與新華字典的拼音，根據拼音搜索到的信息都是連續的，可以很快獲取到它前后的信息。非聚集索引類似于部首查詢，信息存放的位置可能不在一個區域。對經常使用范圍查詢的字段考慮使用聚集索引。
InnoDB中索引分為聚簇索引（主鍵索引）和非聚簇索引（非主鍵索引），聚簇索引的葉子節點中保存的是整行記錄，而非聚簇索引的葉子節點中保存的是該行記錄的主鍵的值。
如果您的表上定義有主鍵，該主鍵索引是聚集索引。
如果你不定義為您的表的主鍵時，MySQL取第一個唯一索引（unique）而且只含非空列（NOT NULL）作為主鍵，InnoDB使用它作為聚集索引。
如果沒有這樣的列，InnoDB就自己產生一個這樣的ID值，
優先選index key_len小的索引進行count(*),盡量不使用聚簇索引
在沒有where條件的情況下，count(*)和count(常量)，如果有非聚簇索引，mysql會自動選擇非聚簇索引，因為非聚簇索引所占的空間小，如果沒有非聚簇索引會使用聚集索引。count(primary key)主鍵id為聚集索引，使用聚集索引。有where條件的情況下，是否使用索引會根據where條件判斷。
MySQL分頁的sql語言怎么寫？
1、首先我們建立一個表表的數據，這個表里有25條數據，id從1到25。（下圖是部分截圖）
2、要分頁數據，首先我們假設一頁有10條數據，我們可以用mysql的 limit關鍵字來限定返回多少條數據。并且用order by來排序數據，這里用 id來排序。所以第一頁的sql可以如圖這樣寫。
3、執行后得到的數據如圖，就是 id從1到10的前10條數據，因為我們是按id升序來排序的。
4、上面第一頁的sql是簡化的寫法，完整的寫法如圖，得到的結果和上圖的一模一樣。代碼里 limit 0, 10 的意思是從第一條數據開始，取10條數據。（注意的是第一條數據是從0開始的）
5、那么第二頁的數據，關鍵是要知道是從哪一條數據開始，可以用這個公式得到： (頁碼-1) * 每頁顯示多少條，即 （2-1) * 10 = 10, 所以sql語句如圖, limit 10, 10。
6、執行后，結果正確，得到id從11到20的10條數據。
7、同理第三頁數據的sql如圖，<br/>就是 limit 20, 10。
8、查詢的結果如圖，因為這頁只剩下5條數據了，所以只顯示5條數據。如果你有更多頁的數據，后面的數據只需要按上面的公式，得到從哪行開始，就可以寫對應的sql語句了。
使用MySQL的遞延Join連接實現高效分頁 - Aaron
在 Web 應用程序中跨大型數據集分頁記錄似乎是一個簡單的問題，但實際上很難擴展。兩種主要的分頁策略是偏移/限制和游標。
我們將首先看一下這兩種方法，然后稍作修改，可以使偏移/限制非常高效。
偏移/限制分頁
偏移/限制方法是迄今為止最常見的方法，它通過跳過一定數量的記錄（頁）并將結果限制為一頁來工作。
例如，假設您的應用程序配置為每頁顯示 15 條記錄。您的 SQL 將如下所示：
這是最常見的，因為它非常簡單，易于推理，并且幾乎每個框架都支持它。
除了易于實現之外，它還具有頁面可直接尋址的優點。例如，如果您想直接導航到第 20 頁，您可以這樣做，因為該偏移量很容易計算。
但是有一個主要的缺點，它潛伏在數據庫處理偏移量的方式中。偏移量告訴數據庫放棄從查詢中返回的前N個結果。不過數據庫仍然要從磁盤上獲取這些行。
如果你丟棄的是100條記錄，這并不重要，但如果你丟棄的是100,000條記錄，數據庫就會為了丟棄這些結果而做大量的工作。
在實踐中，這意味著第一個頁面會快速加載，之后的每一個頁面都會變得越來越慢，直到你達到一個點，網絡請求可能會直接超時。
基于游標的分頁
基于游標的分頁彌補了偏移/限制的一些不足，同時引入了一些自己的不足。
基于游標的分頁是通過存儲一些關于最后呈現給用戶的記錄的狀態，然后根據這個狀態來進行下一次查詢。
因此，它不是按順序獲取所有的記錄并丟棄前N條，而是只獲取最后一個位置N之后的記錄。
如果按ID排序，SQL可能看起來像這樣。
你可能已經看到了其中的好處。因為我們知道上次向用戶展示的ID，我們知道下一個頁面將以一個更高的ID開始。我們甚至不需要檢查ID較低的行，因為我們百分之百肯定地知道那些行不需要被顯示。
在上面的例子中，我特別說明了ID可能不是連續的，也就是說，可能有缺失的記錄。這使得我們無法計算出哪些記錄會出現在某一頁面上，你必須跟蹤之前那一頁面上的最后一條記錄是什么。
與偏移/限制分頁不同，使用游標分頁時，頁面不能直接尋址，你只能導航到 "下一頁 "或 "上一頁"。
不過光標分頁的好處是在任何數量的頁面上都很迅速。它也很適合無限滾動，在這種情況下，頁面首先不需要可以直接尋址。
Laravel文檔中有一些關于偏移量和游標之間的權衡的好的背景。
https://laravel.com/docs/pagination cursor -vs-offt-pagination
考慮到所有這些，讓我們來看看一個偏移/限制優化，可以使它的性能足以在成千上萬的頁面上使用。
使用遞延join的Offt/Limit
遞延連接（deferred join ）是一種技術，它將對要求的列的訪問推遲到應用了偏移量和限制之后。
使用這種技術，我們創建一個內部查詢，可以用特定的索引進行優化，以獲得最大的速度，然后將結果連接到同一個表，以獲取完整的行。
它看起來像這樣：
這種方法的好處可以根據你的數據集有很大的不同，但是這種方法允許數據庫盡可能少地檢查數據，以滿足用戶的意圖。
查詢中 "昂貴的 "lect *部分只在與內部查詢相匹配的15條記錄上運行。所有數據的Select都被推遲了，因此被稱為推遲join。
這種方法不太可能比傳統的偏移/限制性能差，盡管它是可能的，所以一定要在你的數據上進行測試!
Laravel實現
我們如何把這一點帶到我們最喜歡的網絡框架，如Laravel和Rails？
讓我們具體看看Laravel，因為我不知道Rails。
感謝Laravel的macroable特性，我們可以擴展Eloquent Query Builder來添加一個新的方法，叫做deferredPaginate。為了保持一致性，我們將模仿常規分頁的簽名。
我們將嘗試做盡可能少的自定義工作，并將大部分工作留給 Laravel。
這是我們要做的：
這應該為我們提供 LaravelLengthAwarePaginator 和延遲連接的所有好處！
一個Github倉庫
遞延Join和覆蓋索引
還沒有完成...
使用遞延Join的主要好處是減少了數據庫必須檢索然后丟棄的數據量。我們可以通過幫助數據庫獲得它需要的數據而更進一步，而無需獲取底層行。
這樣做的方法稱為“覆蓋索引covering index”，它是確保快速偏移/限制分頁的最終解決方案。
覆蓋索引是一個索引，在這個索引中，查詢的所有需要的字段都包含在索引本身中。當一個查詢的所有部分都能被一個索引 "覆蓋 "時，數據庫根本不需要讀取該行，它可以從索引中獲得它需要的一切。
請注意，覆蓋索引并不是以任何特殊方式創建的。它只是指一個索引滿足了一個查詢所需要的一切的情況。一個查詢上的覆蓋索引很可能不是另一個查詢上的覆蓋索引。
在接下來的幾個例子中，我們將使用這個基本的表，我把它填滿了~1000萬條記錄。
讓我們看一個僅lect索引列的簡單查詢。在這種情況下，我們將從email表中進行lect contacts。
在這種情況下，數據庫根本不需要讀取基礎行。在MySQL中，我們可以通過運行一個解釋并查看額外的列來驗證這一點：
extra: using index告訴我們，MySQL能夠只使用索引來滿足整個查詢，而不看基礎行。
如果嘗試lect name from contacts limit 10， 我們將期望MySQL必須到該行去獲取數據，因為名字name沒有被索引。這正是發生的情況，由下面的解釋顯示。
extra不再顯示 using index，所以我們沒有使用覆蓋索引。
假設你每頁有15條記錄，你的用戶想查看第1001頁，你的內部查詢最終會是這樣的。
lect id from contacts order by id limit 15 OFFSET 150000
explain結果顯示：
MySQL能夠單看索引來執行這個查詢。它不會簡單地跳過前15萬行，在使用offt是沒有辦法的，但它不需要讀取15萬行。(只有游標分頁可以讓你跳過所有的行）。
即使使用覆蓋索引和延遲連接，當你到達后面的頁面時，結果也會變慢，盡管與傳統的偏移/限制相比，它應該是最小的。使用這些方法，你可以輕易地深入到數千頁。
更好的覆蓋索引
這里的很多好處取決于擁有良好的覆蓋索引，所以讓我們稍微討論一下。一切都取決于您的數據和用戶的使用模式，但是您可以采取一些措施來確保查詢的最高命中率。
這將主要與 MySQL 對話，因為那是我有經驗的地方。其他數據庫中的情況可能會有所不同。
大多數開發人員習慣于為單列添加索引，但沒有什么能阻止您向多列添加索引。事實上，如果您的目標是為昂貴的分頁查詢創建覆蓋索引，您幾乎肯定需要一個多列索引。
當你試圖為分頁優化一個索引時，一定要把按列排序放在最后。如果你的用戶要按update_at排序，這應該是你復合索引中的最后一列。
看看下面這個包括三列的索引。
在MySQL中，復合索引是從左到右訪問的，如果一個列缺失，或者在第一個范圍條件之后，MySQL會停止使用一個索引。
MySQL 將能夠在以下場景中使用該索引：
如果你跳過is_archived，MySQL將無法訪問update_at，將不得不訴諸于沒有該索引的排序，或者根本不使用該索引，所以要確保你有相應的計劃。
主鍵始終存在
在MySQL的InnoDB中，所有的索引都附加了主鍵。這意味著（email）的索引實際上是（email，id）的索引，當涉及到覆蓋索引和延遲連接時，這是相當重要的。
查詢lect email from contacts order by id完全被email上的一個索引所覆蓋，因為InnoDB將id附加到了該索引上。
使用我們上面的綜合例子，你可以看到這有什么好處。
因為復合索引涵蓋了is_deleted, is_archived, updated_at, 和（通過InnoDB的功能）id，整個查詢可以僅由索引來滿足。
降序索引
大多數時候，用戶都在尋找 "最新的 "項目，即最近更新或創建的項目，這可以通過按update_at DESC排序來滿足。
如果你知道你的用戶主要是以降序的方式對他們的結果進行排序，那么特別將你的索引設為降序索引可能是有意義的。
MySQL 8是第一個支持降序索引的MySQL版本。
如果你在explain的Extra部分看到向后索引掃描，你也許可以配置一個更好的索引。
前向索引掃描比后向掃描快~15%，所以你要按照你認為你的用戶最常使用的順序添加索引，并為少數使用情況承擔懲罰。
太陽底下無新事
這種使用偏移/限制分頁與延遲連接和覆蓋索引的方法并不是銀彈。
僅僅是遞遲連接就可以讓你的速度得到很好的提升，但是需要花一些額外的心思來設計正確的索引以獲得最大的好處。
有一種觀點認為，遞延連接應該是框架中默認的偏移offt/限制limit方法，而任何時候覆蓋索引的出現都只是一種獎勵。我還沒有在足夠多的生產環境中測試過，所以還沒有強烈主張這樣做。
使用MySQL的遞延Join連接實現高效分頁 - Aaron
