軟件開(kāi)發(fā)中，常用要用到分頁(yè)、計(jì)算總數(shù)，數(shù)據(jù)量超過(guò)千萬(wàn)、上億的時(shí)候，往往count的需要超過(guò) 1s 的執(zhí)行時(shí)間，甚至 3-5s，對(duì)于一個(gè)追求性能的前沿團(tuán)隊(duì)來(lái)說(shuō)，這個(gè)不能忍啊！
為什么會(huì)慢？
mysql 會(huì)對(duì)所有符合的條件做一次掃描。
select count(*) from table_a where a = '%d' ...
如果 a=%d 的數(shù)據(jù)有 1000W 條，那么數(shù)據(jù)庫(kù)就會(huì)掃描一次 1000W 條數(shù)據(jù)庫(kù)。如果不帶查詢條件，那這種全表掃描將更可怕。
count(*) 和 count(1)、count(0)
count(expr) 為統(tǒng)計(jì) expr 不為空的記錄
count(*) 它會(huì)計(jì)算總行數(shù)，不管你字段是否有值都會(huì)列入計(jì)算范圍。
coount(0),count(1) 沒(méi)有差別，它會(huì)計(jì)算總行數(shù)
Example 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` |
Example 2:
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 DB
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
其他 DB 引擎
受到 MySIAM DB 的啟發(fā)，我們可以手動(dòng)維護(hù)總數(shù)緩存在表的索引中了。
1、如果 ID 連續(xù)，且基本不會(huì)斷開(kāi)。直接取最大值 ID
2、如果表中存在連續(xù)的數(shù)字列并設(shè)為索引，那么通過(guò)頁(yè)碼即可計(jì)算出此字段的范圍，直接作范圍查詢即可：
start = (page-1)*pagesize+1
end = page*pagesize
select * from table where id >start and id <=end
1、涉及到總數(shù)操作，專門維護(hù)一個(gè)總數(shù)。新增一個(gè)用戶，總數(shù)值加 1, 需要總數(shù)的時(shí)候直接拿這個(gè)總數(shù), 比如分頁(yè)時(shí)。如果有多個(gè)條件，那么就需要維護(hù)多個(gè)總數(shù)列。該方案的擴(kuò)展性更好，隨著用戶表數(shù)量增大, 水平切分用戶表，要獲取用戶總數(shù)，直接查詢這個(gè)總數(shù)表即可。
分頁(yè)正反偏移
數(shù)據(jù)庫(kù)自帶的 skip 和 limit 的限制條件為我們創(chuàng)建了分頁(yè)的查詢方式，但是如果利用不對(duì)，性能會(huì)出現(xiàn)千倍萬(wàn)倍差異。
簡(jiǎn)單一點(diǎn)描述：limit 100000,20 的意思掃描滿足條件的 100020 行，扔掉前面的 100000 行，返回最后的 20 行，問(wèn)題就在這里。如果我反向查詢 oder by xx desc limit 0,20，那么我只要索引 20 條數(shù)據(jù)。
Example 3
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é)果，千差萬(wàn)別的結(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 是為查詢最后一頁(yè)的翻頁(yè) sql 查詢用的。由于一次翻頁(yè)往往只需要查詢較小的數(shù)據(jù)，如 10 條，但需要向后掃描大量的數(shù)據(jù)，也就是越往后的翻頁(yè)查詢，掃描的數(shù)據(jù)量會(huì)越多，查詢的速度也就越來(lái)越慢。
由于查詢的數(shù)據(jù)量大小是固定的，如果查詢速度不受翻頁(yè)的頁(yè)數(shù)影響，或者影響最低，那么這樣是最佳的效果了（查詢最后最幾頁(yè)的速度和開(kāi)始幾頁(yè)的速度一致）。
在翻頁(yè)的時(shí)候，往往需要對(duì)其中的某個(gè)字段做排序（這個(gè)字段在索引中），升序排序。那么可不可以利用索引的有序性來(lái)解決上面遇到的問(wèn)題。
比如有 10000 條數(shù)據(jù)需要做分頁(yè)，那么前 5000 條做 asc 排序，后 5000 條 desc 排序，在 limit startnum，pagesize 參數(shù)中作出相應(yīng)的調(diào)整。
但是這無(wú)疑給應(yīng)用程序帶來(lái)復(fù)雜，這條 sql 是用于論壇回復(fù)帖子的 sql，往往用戶在看帖子的時(shí)候，一般都是查看前幾頁(yè)和最后幾頁(yè)，那么在翻頁(yè)的時(shí)候最后幾頁(yè)的翻頁(yè)查詢采用 desc 的方式來(lái)實(shí)現(xiàn)翻頁(yè)，這樣就可以較好的提高性能。
游標(biāo)：上一頁(yè)的最大值或者最小值
如果你知道上一頁(yè)和下一頁(yè)的臨界值，那么翻頁(yè)查詢也是信手拈來(lái)了，直接就告訴了數(shù)據(jù)庫(kù)我的起始查詢?cè)谀?，也就沒(méi)有什么性能問(wèn)題了。我更愿意稱這個(gè)東西為游標(biāo) (Cursor)。
如果做下拉刷新，那么就直接避免掉分頁(yè)的問(wèn)題了。根據(jù)上一頁(yè)的最后一個(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)
緩存和不精準(zhǔn)
數(shù)據(jù)量達(dá)到一定程度的時(shí)候，用戶根本就不關(guān)心精準(zhǔn)的總數(shù), 沒(méi)人關(guān)心差幾個(gè)。看看知乎、微博、微信訂閱號(hào)，不精準(zhǔn)的統(tǒng)計(jì)到處都是。
如果每次點(diǎn)擊分頁(yè)的時(shí)候都進(jìn)行一次 count 操作，那速度肯定不會(huì)快到哪里去。他們一般也是采用計(jì)數(shù)器的辦法。每次新增加一個(gè)粉絲，就把值加 1，直接在用戶信息存儲(chǔ)一個(gè)總數(shù)，一段時(shí)間后重新查詢一次，更新該緩存。這樣分頁(yè)的時(shí)候直接拿這個(gè)總數(shù)進(jìn)行分頁(yè)，顯示的時(shí)候直接顯示模糊之就行。
那為什么微信公眾號(hào)的閱讀量只有 10W+ 這個(gè)量級(jí)呢？100W+ 級(jí)去哪了！
其他大神的建議
1、mysql 的數(shù)據(jù)查詢, 大小字段要分開(kāi), 這個(gè)還是有必要的, 除非一點(diǎn)就是你查詢的都是索引內(nèi)容而不是表內(nèi)容, 比如只查詢 id 等等
2、查詢速度和索引有很大關(guān)系也就是索引的大小直接影響你的查詢效果, 但是查詢條件一定要建立索引, 這點(diǎn)上注意的是索引字段不能太多，太多索引文件就會(huì)很大那樣搜索只能變慢,
3、查詢指定的記錄最好通過(guò) Id 進(jìn)行 in 查詢來(lái)獲得真實(shí)的數(shù)據(jù). 其實(shí)不是最好而是必須，也就是你應(yīng)該先查詢出復(fù)合的 ID 列表, 通過(guò) in 查詢來(lái)獲得數(shù)據(jù)
4、mysql 千萬(wàn)級(jí)別數(shù)據(jù)肯定是沒(méi)問(wèn)題的, 畢竟現(xiàn)在的流向 web2.0 網(wǎng)站大部分是 mysql 的
5、合理分表也是必須的, 主要涉及橫向分表與縱向分表, 如把大小字段分開(kāi), 或者每 100 萬(wàn)條記錄在一張表中等等, 像上面的這個(gè)表可以考慮通過(guò) uid 的范圍分表, 或者通過(guò)只建立索引表, 去掉相對(duì)大的字段來(lái)處理.
6、count() 時(shí)間比較長(zhǎng), 但是本身是可以緩存在數(shù)據(jù)庫(kù)中或者緩存在程序中的, 因?yàn)槲覀儺?dāng)時(shí)使用在后臺(tái)所以第一頁(yè)比較慢但是后面比較理想
7、SELECT id 相對(duì) SELECT差距還是比較大的, 可以通過(guò)上面的方法來(lái)使用 SELECT id + SELECT… IN 查詢來(lái)提高性能
8、必要的索引是必須的, 還是要盡量返回 5%-20% 的結(jié)果級(jí)別其中小于 5% 最理想;
9、mysql 分頁(yè)的前面幾頁(yè)速度很快, 越向后性能越差, 可以考慮只帶上一頁(yè), 下一頁(yè)不帶頁(yè)面跳轉(zhuǎn)的方法, 呵呵這個(gè)比較垃圾但是也算是個(gè)方案, 只要在前后多查一條就能解決了. 比如 100,10 你就差 99,12 呵呵，這樣看看前后是否有結(jié)果.
10、前臺(tái)還是要通過(guò)其他手段來(lái)處理, 比如 lucene/Solr+mysql 結(jié)合返回翻頁(yè)結(jié)果集, 或者上面的分表
11、總數(shù)可能是存在內(nèi)存中, 這樣分頁(yè)計(jì)算的時(shí)候速度很快。累加操作的時(shí)候?qū)?nèi)存中的值加 1?？倲?shù)這個(gè)值要持久化，還是要存到磁盤上的，也就是數(shù)據(jù)庫(kù)中 (可以是關(guān)系型數(shù)據(jù)庫(kù)，也可以是 mongdb 這樣的數(shù)據(jù)庫(kù)很適合存儲(chǔ)計(jì)數(shù))。把總數(shù)放在內(nèi)存中，只是避免頻繁的磁盤 i/0 操作 (操作數(shù)據(jù)庫(kù)就要涉及到磁盤讀寫)。
