摘要：分頁正反偏移數據庫自帶的和的限制條件為我們創建了分頁的查詢方式，但是如果利用不對，性能會出現千倍萬倍差異。這兩條是為查詢最后一頁的翻頁查詢用的。緩存和不精準數據量達到一定程度的時候，用戶根本就不關心精準的總數沒人關心差幾個。

軟件開發中，常用要用到分頁、計算總數，數據量超過千萬、上億的時候，往往count 的需要超過 1s 的執行時間，甚至 3-5s，對于一個追求性能的前沿團隊來說，這個不能忍啊！

mysql 會對所有符合的條件做一次掃描。

select count(*) from table_a where a = "%d" ...

如果 a=%d 的數據有 1000W 條，那么數據庫就會掃描一次 1000W 條數據庫。如果不帶查詢條件，那這種全表掃描將更可怕。

count(expr) 為統計 expr 不為空的記錄

count(*) 它會計算總行數，不管你字段是否有值都會列入計算范圍。

coount(0),count(1) 沒有差別，它會計算總行數

mysql> explain extended select count(*) from user;
...
1 row in set, 1 warning (0.34 sec)

mysql> show warnings;
+-------+------+--------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------+
| Note | 1003 | select count(0) AS `count(*)` from `user` |

mysql> select count(*) from login_log
 -> ;
+----------+
| count(*) |
+----------+
| 2513 |
+----------+
1 rows in set (0.00 sec)

mysql> select count(logoutTime) from login_log;
+-------------------+
| count(logoutTime) |
+-------------------+
| 308 |
+-------------------+
1 rows in set (0.00 sec)

MyISAM 引擎很容易獲得總行數的統計，查詢速度變得更快。因為 MyISAM 存儲引擎已經存儲了表的總行數。
MyISAM 會為每張表維護一個 row count 的計數器，每次新增加一行，這個計數器就加 1。但是如果有查詢條件，那么 MyISAM 也 game over 了，MyISAM 引擎不支持條件緩存。

On MyISAM, doing a query that does SELECT COUNT(*) FROM {some_table}, is very fast, since MyISAM keeps the information in the index

受到 MySIAM DB 的啟發，我們可以手動維護總數緩存在表的索引中了。

如果 ID 連續，且基本不會斷開。直接取最大值 ID

如果表中存在連續的數字列并設為索引，那么通過頁碼即可計算出此字段的范圍，直接作范圍查詢即可：

start = (page-1)*pagesize+1 
end = page*pagesize 
select * from table where id >start and id <=end 

涉及到總數操作，專門維護一個總數。新增一個用戶，總數值加 1, 需要總數的時候直接拿這個總數, 比如分頁時。如果有多個條件，那么就需要維護多個總數列。該方案的擴展性更好，隨著用戶表數量增大, 水平切分用戶表，要獲取用戶總數，直接查詢這個總數表即可。

數據庫自帶的 skip 和 limit 的限制條件為我們創建了分頁的查詢方式，但是如果利用不對，性能會出現千倍萬倍差異。
簡單一點描述：limit 100000,20 的意思掃描滿足條件的 100020 行，扔掉前面的 100000 行，返回最后的 20 行，問題就在這里。如果我反向查詢 oder by xx desc limit 0,20，那么我只要索引 20 條數據。

mysql> select count(*) from elastic_task_log_copy;
+----------+
| count(*) |
+----------+
| 1705162 |
+----------+
1 rows in set (2.31 sec)

正向偏移查詢。超級浪費的查詢，需要先 skip 大量的符合條件的查詢。

mysql> select id from elastic_task_log_copy order by id asc limit 1705152,10;
+---------+
| id |
+---------+
| 1705157 |
| 1705158 |
| 1705159 |
| 1705160 |
| 1705161 |
| 1705162 |
| 1705163 |
| 1705164 |
| 1705165 |
| 1705166 |
+---------+
10 rows in set (2.97 sec)

反向偏移查詢。同樣的查詢結果，千差萬別的結果。

mysql> select id from elastic_task_log_copy order by id desc limit 0,10;
+---------+
| id |
+---------+
| 1705166 |
| 1705165 |
| 1705164 |
| 1705163 |
| 1705162 |
| 1705161 |
| 1705160 |
| 1705159 |
| 1705158 |
| 1705157 |
+---------+
10 rows in set (0.01 sec)

這兩條 sql 是為查詢最后一頁的翻頁 sql 查詢用的。由于一次翻頁往往只需要查詢較小的數據，如 10 條，但需要向后掃描大量的數據，也就是越往后的翻頁查詢，掃描的數據量會越多，查詢的速度也就越來越慢。

由于查詢的數據量大小是固定的，如果查詢速度不受翻頁的頁數影響，或者影響最低，那么這樣是最佳的效果了（查詢最后最幾頁的速度和開始幾頁的速度一致）。

在翻頁的時候，往往需要對其中的某個字段做排序（這個字段在索引中），升序排序。那么可不可以利用索引的有序性 來解決上面遇到的問題。

比如有 10000 條數據需要做分頁，那么前 5000 條做 asc 排序，后 5000 條 desc 排序，在 limit startnum，pagesize 參數中作出相應的調整。

但是這無疑給應用程序帶來復雜，這條 sql 是用于論壇回復帖子的 sql，往往用戶在看帖子的時候，一般都是查看前幾頁和最后幾頁，那么在翻頁的時候最后幾頁的翻頁查詢采用 desc 的方式來實現翻頁，這樣就可以較好的提高性能。

如果你知道上一頁和下一頁的臨界值，那么翻頁查詢也是信手拈來了，直接就告訴了數據庫我的起始查詢在哪，也就沒有什么性能問題了。我更愿意稱這個東西為游標 (Cursor)。
如果做下拉刷新，那么就直接避免掉分頁的問題了。根據上一頁的最后一個值去請求新數據。

mysql> select id from elastic_task_log_copy where id >= 1699999 limit 10;
+---------+
| id |
+---------+
| 1699999 |
| 1700000 |
| 1700001 |
| 1700002 |
| 1700003 |
| 1700004 |
| 1700005 |
| 1700006 |
| 1700007 |
| 1700008 |
+---------+
10 rows in set (0.01 sec)

數據量達到一定程度的時候，用戶根本就不關心精準的總數, 沒人關心差幾個。看看知乎、微博、微信訂閱號，不精準的統計到處都是。

如果每次點擊分頁的時候都進行一次 count 操作，那速度肯定不會快到哪里去。他們一般也是采用計數器的辦法。每次新增加一個粉絲，就把值加 1，直接在用戶信息存儲一個總數，一段時間后重新查詢一次，更新該緩存。這樣分頁的時候直接拿這個總數進行分頁，顯示的時候直接顯示模糊之就行。

那為什么微信公眾號的閱讀量只有 10W+ 這個量級呢？100W+ 級去哪了！

mysql 的數據查詢, 大小字段要分開, 這個還是有必要的, 除非一點就是你查詢的都是索引內容而不是表內容, 比如只查詢 id 等等

查詢速度和索引有很大關系也就是索引的大小直接影響你的查詢效果, 但是查詢條件一定要建立索引, 這點上注意的是索引字段不能太多，太多索引文件就會很大那樣搜索只能變慢,

查詢指定的記錄最好通過 Id 進行 in 查詢來獲得真實的數據. 其實不是最好而是必須，也就是你應該先查詢出復合的 ID 列表, 通過 in 查詢來獲得數據

mysql 千萬級別數據肯定是沒問題的, 畢竟現在的流向 web2.0 網站大部分是 mysql 的

合理分表也是必須的, 主要涉及橫向分表與縱向分表, 如把大小字段分開, 或者每 100 萬條記錄在一張表中等等, 像上面的這個表可以考慮通過 uid 的范圍分表, 或者通過只建立索引表, 去掉相對大的字段來處理.

count() 時間比較長, 但是本身是可以緩存在數據庫中或者緩存在程序中的, 因為我們當時使用在后臺所以第一頁比較慢但是后面比較理想

SELECT id 相對 SELECT 差距還是比較大的, 可以通過上面的方法來使用 SELECT id + SELECT ... IN 查詢來提高性能

必要的索引是必須的, 還是要盡量返回 5%-20% 的結果級別其中小于 5% 最理想;

mysql 分頁的前面幾頁速度很快, 越向后性能越差, 可以考慮只帶上一頁, 下一頁不帶頁面跳轉的方法, 呵呵這個比較垃圾但是也算是個方案, 只要在前后多查一條就能解決了. 比如 100,10 你就差 99,12 呵呵，這樣看看前后是否有結果.

前臺還是要通過其他手段來處理, 比如 lucene/Solr+mysql 結合返回翻頁結果集, 或者上面的分表

總數可能是存在內存中, 這樣分頁計算的時候速度很快。累加操作的時候將內存中的值加 1。總數這個值要持久化，還是要存到磁盤上的，也就是數據庫中 (可以是關系型數據庫，也可以是 mongdb 這樣的數據庫很適合存儲計數)。把總數放在內存中，只是避免頻繁的磁盤 i/0 操作 (操作數據庫就要涉及到磁盤讀寫)。

如果你還有更好的建議，請在評論里面告訴我吧。