摘要：分頁正反偏移數(shù)據(jù)庫自帶的和的限制條件為我們創(chuàng)建了分頁的查詢方式，但是如果利用不對(duì)，性能會(huì)出現(xiàn)千倍萬倍差異。這兩條是為查詢最后一頁的翻頁查詢用的。緩存和不精準(zhǔn)數(shù)據(jù)量達(dá)到一定程度的時(shí)候，用戶根本就不關(guān)心精準(zhǔn)的總數(shù)沒人關(guān)心差幾個(gè)。

軟件開發(fā)中，常用要用到分頁、計(jì)算總數(shù)，數(shù)據(jù)量超過千萬、上億的時(shí)候，往往count 的需要超過 1s 的執(zhí)行時(shí)間，甚至 3-5s，對(duì)于一個(gè)追求性能的前沿團(tuán)隊(duì)來說，這個(gè)不能忍啊！

mysql 會(huì)對(duì)所有符合的條件做一次掃描。

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

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

count(expr) 為統(tǒng)計(jì) expr 不為空的記錄

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

coount(0),count(1) 沒有差別，它會(huì)計(jì)算總行數(shù)

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 引擎很容易獲得總行數(shù)的統(tǒng)計(jì)，查詢速度變得更快。因?yàn)?MyISAM 存儲(chǔ)引擎已經(jīng)存儲(chǔ)了表的總行數(shù)。
MyISAM 會(huì)為每張表維護(hù)一個(gè) row count 的計(jì)數(shù)器，每次新增加一行，這個(gè)計(jì)數(shù)器就加 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 的啟發(fā)，我們可以手動(dòng)維護(hù)總數(shù)緩存在表的索引中了。

如果 ID 連續(xù)，且基本不會(huì)斷開。直接取最大值 ID

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

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

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

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

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

正向偏移查詢。超級(jí)浪費(fèi)的查詢，需要先 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)

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

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 查詢用的。由于一次翻頁往往只需要查詢較小的數(shù)據(jù)，如 10 條，但需要向后掃描大量的數(shù)據(jù)，也就是越往后的翻頁查詢，掃描的數(shù)據(jù)量會(huì)越多，查詢的速度也就越來越慢。

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

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

比如有 10000 條數(shù)據(jù)需要做分頁，那么前 5000 條做 asc 排序，后 5000 條 desc 排序，在 limit startnum，pagesize 參數(shù)中作出相應(yīng)的調(diào)整。

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

如果你知道上一頁和下一頁的臨界值，那么翻頁查詢也是信手拈來了，直接就告訴了數(shù)據(jù)庫我的起始查詢?cè)谀模簿蜎]有什么性能問題了。我更愿意稱這個(gè)東西為游標(biāo) (Cursor)。
如果做下拉刷新，那么就直接避免掉分頁的問題了。根據(jù)上一頁的最后一個(gè)值去請(qǐng)求新數(shù)據(jù)。

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)

數(shù)據(jù)量達(dá)到一定程度的時(shí)候，用戶根本就不關(guān)心精準(zhǔn)的總數(shù), 沒人關(guān)心差幾個(gè)。看看知乎、微博、微信訂閱號(hào)，不精準(zhǔn)的統(tǒng)計(jì)到處都是。

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

那為什么微信公眾號(hào)的閱讀量只有 10W+ 這個(gè)量級(jí)呢？100W+ 級(jí)去哪了！

mysql 的數(shù)據(jù)查詢, 大小字段要分開, 這個(gè)還是有必要的, 除非一點(diǎn)就是你查詢的都是索引內(nèi)容而不是表內(nèi)容, 比如只查詢 id 等等

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

查詢指定的記錄最好通過 Id 進(jìn)行 in 查詢來獲得真實(shí)的數(shù)據(jù). 其實(shí)不是最好而是必須，也就是你應(yīng)該先查詢出復(fù)合的 ID 列表, 通過 in 查詢來獲得數(shù)據(jù)

mysql 千萬級(jí)別數(shù)據(jù)肯定是沒問題的, 畢竟現(xiàn)在的流向 web2.0 網(wǎng)站大部分是 mysql 的

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

count() 時(shí)間比較長(zhǎng), 但是本身是可以緩存在數(shù)據(jù)庫中或者緩存在程序中的, 因?yàn)槲覀儺?dāng)時(shí)使用在后臺(tái)所以第一頁比較慢但是后面比較理想

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

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

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

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

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

如果你還有更好的建議，請(qǐng)?jiān)谠u(píng)論里面告訴我吧。