目錄1、背景2、count(*)為什么性能差3、如何優(yōu)化count(*)性能3.1、增加Redis緩存3.2、加二級緩存3.3、多線程執(zhí)行3.4、減少join的表3.5、改成ClickHouse4、count的各種用法性能對比1、背景

使用的數(shù)據(jù)庫是MySQL8，使用的存儲引擎是Innodb。

通常情況下，分頁接口一般會查詢兩次數(shù)據(jù)庫，第一次是獲取具體數(shù)據(jù)，第二次是獲取總的記錄行數(shù)，然后把結(jié)果整合之后，再返回。

查詢具體數(shù)據(jù)的sql，比如是這樣的：

select id, name from user limit 1, 20;

它沒有性能問題。

但另外一條使用count(*)查詢總記錄行數(shù)的sql，例如：

select count(*) from user;

卻存在性能差的問題。

為什么會出現(xiàn)這種情況呢？

2、count(*)為什么性能差

在MySQL中，count(*)的作用是統(tǒng)計表中記錄的總行數(shù)。

而count(*)的性能跟存儲引擎有直接關(guān)系，并非所有的存儲引擎，count(*)的性能都很差。

在MySQL中使用最多的存儲引擎是：innodb和myisam。

在myisam中會把總行數(shù)保存到磁盤上，使用count(*)時，只需要返回那個數(shù)據(jù)即可，無需額外的計算，所以執(zhí)行效率很高。

而innodb則不同，由于它支持事務(wù)，有MVCC（即多版本并發(fā)控制）的存在，在同一個時間點的不同事務(wù)中，同一條查詢sql，返回的記錄行數(shù)可能是不確定的。

在innodb使用count(*)時，需要從存儲引擎中一行行的讀出數(shù)據(jù)，然后累加起來，所以執(zhí)行效率很低。

如果表中數(shù)據(jù)量小還好，一旦表中數(shù)據(jù)量很大，innodb存儲引擎使用count(*)統(tǒng)計數(shù)據(jù)時，性能就會很差。

3、如何優(yōu)化count(*)性能

從上面得知，既然count(*)存在性能問題，那么該如何優(yōu)化呢？

可以從以下幾個方面著手。

3.1、增加Redis緩存

對于簡單的count(*)，比如：統(tǒng)計瀏覽總次數(shù)或者瀏覽總?cè)藬?shù)，可以直接將接口使用Redis緩存起來，沒必要實時統(tǒng)計。

當用戶打開指定頁面時，在緩存中每次都設(shè)置成count = count+1即可。

用戶第一次訪問頁面時，Redis中的count值設(shè)置成1。用戶以后每訪問一次頁面，都讓count加1，最后重新設(shè)置到Redis中（Redis內(nèi)存占用）。

這樣在需要展示數(shù)量的地方，從Redis中查出count值返回即可。

該場景無需從數(shù)據(jù)埋點表中使用count(*)實時統(tǒng)計數(shù)據(jù)，性能將會得到極大的提升。

不過在高并發(fā)的情況下，可能會存在緩存和數(shù)據(jù)庫的數(shù)據(jù)不一致的問題。

但對于統(tǒng)計瀏覽總次數(shù)或者瀏覽總?cè)藬?shù)這種業(yè)務(wù)場景，對數(shù)據(jù)的準確性要求并不高，容忍數(shù)據(jù)不一致的情況存在。

3.2、加二級緩存

對于有些業(yè)務(wù)場景，新增數(shù)據(jù)很少，大部分是統(tǒng)計數(shù)量操作，而且查詢條件很多。這時候使用傳統(tǒng)的count(*)實時統(tǒng)計數(shù)據(jù)，性能肯定不會好。

假如在頁面中可以通過id、name、狀態(tài)、時間、來源等，一個或多個條件，統(tǒng)計品牌數(shù)量。

這種情況下用戶的組合條件比較多，增加聯(lián)合索引也沒用，用戶可以選擇其中一個或者多個查詢條件，有時候聯(lián)合索引也會失效，只能盡量滿足用戶使用頻率最高的條件增加索引。

也就是有些組合條件可以走索引，有些組合條件沒法走索引，這些沒法走索引的場景，該如何優(yōu)化呢？

答：使用二級緩存。

二級緩存其實就是內(nèi)存緩存。

可以使用caffine或者guava實現(xiàn)二級緩存的功能。

目前Spring Boot已經(jīng)集成了caffine，使用起來非常方便。

只需在需要增加二級緩存的查詢方法中，使用@Cacheable注解即可。

@Cacheable(value = 'brand', , keyGenerator = 'cacheKeyGenerator') ? public BrandModel getBrand(Condition condition) { ? ? ? return getBrandByCondition(condition); ? }

然后自定義cacheKeyGenerator，用于指定緩存的key。

public class CacheKeyGenerator implements KeyGenerator { ? ?@Override ? public Object generate(Object target, Method method, Object... params) { ? ? ? ?return target.getClass().getSimpleName() + UNDERLINE ? ? ? ? ? ? ? ?+ method.getName() + ',' ? ? ? ? ? ? ? ?+ StringUtils.arrayToDelimitedString(params, ','); ? ?}}

這個key是由各個條件組合而成。

這樣通過某個條件組合查詢出品牌的數(shù)據(jù)之后，會把結(jié)果緩存到內(nèi)存中，設(shè)置過期時間為5分鐘。

后面用戶在5分鐘內(nèi)，使用相同的條件，重新查詢數(shù)據(jù)時，可以直接從二級緩存中查出數(shù)據(jù)，直接返回了。

這樣能夠極大的提示count(*)的查詢效率。

但是如果使用二級緩存，可能存在不同的服務(wù)器上，數(shù)據(jù)不一樣的情況。需要根據(jù)實際業(yè)務(wù)場景來選擇，沒法適用于所有業(yè)務(wù)場景。

3.3、多線程執(zhí)行

不知道你有沒有做過這樣的需求：統(tǒng)計有效訂單有多少，無效訂單有多少。

這種情況一般需要寫兩條sql，統(tǒng)計有效訂單的sql如下：

select count(*) from order where status = 1;

統(tǒng)計無效訂單的sql如下：

select count(*) from order where status = 0;

但如果在一個接口中，同步執(zhí)行這兩條sql效率會非常低。

這時候，可以改成成一條sql：

select count(*), status from ordergroup by status;

使用group by關(guān)鍵字分組統(tǒng)計相同status的數(shù)量，只會產(chǎn)生兩條記錄，一條記錄是有效訂單數(shù)量，另外一條記錄是無效訂單數(shù)量。

但有個問題：status字段只有1和0兩個值，重復(fù)度很高，區(qū)分度非常低，不能走索引，會全表掃描，效率也不高。

還有其他的解決方案不？

答：使用多線程處理。

可以使用CompleteFuture使用兩個線程異步調(diào)用統(tǒng)計有效訂單的sql和統(tǒng)計無效訂單的sql，最后匯總數(shù)據(jù)，這樣能夠提升查詢接口的性能。

3.4、減少join的表

大部分的情況下，使用count(*)是為了實時統(tǒng)計總數(shù)量的。

但如果表本身的數(shù)據(jù)量不多，但join的表太多，也可能會影響count(*)的效率。

比如在查詢商品信息時，需要根據(jù)商品名稱、單位、品牌、分類等信息查詢數(shù)據(jù)。

這時候?qū)懸粭lsql可以查出想要的數(shù)據(jù)，比如下面這樣的：

select count(*)from product pinner join unit u on p.unit_id = u.idinner join brand b on p.brand_id = b.idinner join category c on p.category_id = c.idwhere p.name = '后端碼匠' and u.id=123 and b.id = 124 and c.id=125;

使用product表去join了unit、brand和category這三張表。

其實這些查詢條件，在product表中都能查詢出數(shù)據(jù)，沒必要join額外的表。

可以把sql改成這樣：

select count(*)from productwhere name = '后端碼匠' and unit_id = 123 and brand_id = 124 and category_id = 125;

在count(*)時只查product單表即可，去掉多余的表join，讓查詢效率可以提升不少。

3.5、改成ClickHouse

有些時候，join的表實在太多，沒法去掉多余的join，該怎么辦呢？

比如上面的例子中，查詢商品信息時，需要根據(jù)商品名稱、單位名稱、品牌名稱、分類名稱等信息查詢數(shù)據(jù)。

這時候根據(jù)product單表是沒法查詢出數(shù)據(jù)的，必須要去join：unit、brand和category這三張表，這時候該如何優(yōu)化呢？

答：可以將數(shù)據(jù)保存到ClickHouse。

ClickHouse是基于列存儲的數(shù)據(jù)庫，不支持事務(wù)，查詢性能非常高，號稱查詢十幾億的數(shù)據(jù)，能夠秒級返回。

為了避免對業(yè)務(wù)代碼的嵌入性，可以使用Canal監(jiān)聽MySQL的binlog日志。當product表有數(shù)據(jù)新增時，需要同時查詢出單位、品牌和分類的數(shù)據(jù)，生成一個新的結(jié)果集，保存到ClickHouse當中。

查詢數(shù)據(jù)時，從ClickHouse當中查詢，這樣使用count(*)的查詢效率能夠提升N倍。

需要特別提醒一下：使用ClickHouse時，新增數(shù)據(jù)不要太頻繁，盡量批量插入數(shù)據(jù)。

其實如果查詢條件非常多，使用ClickHouse也不是特別合適，這時候可以改成ElasticSearch，不過它跟MySQL一樣，存在深分頁問題。

4、count的各種用法性能對比

既然說到count(*)，就不能不說一下count家族的其他成員，比如：count(1)、count(id)、count(普通索引列)、count(未加索引列)。

那么它們有什么區(qū)別呢？

count(*) ：它會獲取所有行的數(shù)據(jù)，不做任何處理，行數(shù)加1。count(1)：它會獲取所有行的數(shù)據(jù)，每行固定值1，也是行數(shù)加1。count(id)：id代表主鍵，它需要從所有行的數(shù)據(jù)中解析出id字段，其中id肯定都不為NULL，行數(shù)加1。count(普通索引列)：它需要從所有行的數(shù)據(jù)中解析出普通索引列，然后判斷是否為NULL，如果不是NULL，則行數(shù)+1。count(未加索引列)：它會全表掃描獲取所有數(shù)據(jù)，解析中未加索引列，然后判斷是否為NULL，如果不是NULL，則行數(shù)+1。

由此，最后count的性能從高到低是：

count(*) ≈ count(1) > count(id) > count(普通索引列) > count(未加索引列)

所以，其實count(*)是最快的。

別跟select * 搞混了。

到此這篇關(guān)于MySQL中count()查詢的性能梳理的文章就介紹到這了,更多相關(guān)MySQL count()內(nèi)容請搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！