前言

最近我在公司優(yōu)化過(guò)幾個(gè)慢查詢(xún)接口的性能，總結(jié)了一些心得體會(huì)拿出來(lái)跟大家一起分享一下，希望對(duì)你會(huì)有所幫助。

我們使用的數(shù)據(jù)庫(kù)是Mysql8，使用的存儲(chǔ)引擎是Innodb。這次優(yōu)化除了優(yōu)化索引之外，更多的是在優(yōu)化count(*)。

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

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

select?id,name?from?user?limit?1,20;


它沒(méi)有性能問(wèn)題。

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

select?count(*)?from?user;


卻存在性能差的問(wèn)題。

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

1 count(*)為什么性能差？

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

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

在Mysql中使用最多的存儲(chǔ)引擎是：innodb和myisam。

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

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

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

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

2 如何優(yōu)化count(*)性能？

從上面得知，既然count(*)存在性能問(wèn)題，那么我們?cè)撊绾蝺?yōu)化呢？

我們可以從以下幾個(gè)方面著手。

2.1 增加redis緩存

對(duì)于簡(jiǎn)單的count(*)，比如：統(tǒng)計(jì)瀏覽總次數(shù)或者瀏覽總?cè)藬?shù)，我們可以直接將接口使用redis緩存起來(lái)，沒(méi)必要實(shí)時(shí)統(tǒng)計(jì)。

當(dāng)用戶(hù)打開(kāi)指定頁(yè)面時(shí)，在緩存中每次都設(shè)置成count = count+1即可。

用戶(hù)第一次訪問(wèn)頁(yè)面時(shí)，redis中的count值設(shè)置成1。用戶(hù)以后每訪問(wèn)一次頁(yè)面，都讓count加1，最后重新設(shè)置到redis中。

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

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

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

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

2.2 加二級(jí)緩存

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

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

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

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

答：使用二級(jí)緩存。

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

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

目前SpringBoot已經(jīng)集成了caffine，使用起來(lái)非常方便。

只需在需要增加二級(jí)緩存的查詢(xún)方法中，使用@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,?",");
????}
}


這個(gè)key是由各個(gè)條件組合而成。

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

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

這樣能夠極大地提升count(*)的查詢(xún)效率。

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

2.3 多線程執(zhí)行

不知道你有沒(méi)有做過(guò)這樣的需求：統(tǒng)計(jì)有效訂單有多少，無(wú)效訂單有多少。

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

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


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

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


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

這時(shí)候，可以改成一條sql：

select?count(*),status?from?order
group?by?status;


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

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

還有其他的解決方案不？

答：使用多線程處理。

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

2.4 減少join的表

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

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

比如在查詢(xún)商品信息時(shí)，需要根據(jù)商品名稱(chēng)、單位、品牌、分類(lèi)等信息查詢(xún)數(shù)據(jù)。

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

select?count(*)
from?product?p
inner?join?unit?u?on?p.unit_id?=?u.id
inner?join?brand?b?on?p.brand_id?=?b.id
inner?join?category?c?on?p.category_id?=?c.id
where?p.name='測(cè)試商品'?and?u.id=123?and?b.id=124?and?c.id=125;


使用product表去joinunit、brand和category這三張表。

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

我們可以把sql改成這樣：

select?count(*)
from?product
where?name='測(cè)試商品'?and?unit_id=123?and?brand_id=124?and?category_id=125;


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

2.5 改成ClickHouse

有些時(shí)候，join的表實(shí)在太多，沒(méi)法去掉多余的join，該怎么辦呢？

比如上面的例子中，查詢(xún)商品信息時(shí)，需要根據(jù)商品名稱(chēng)、單位名稱(chēng)、品牌名稱(chēng)、分類(lèi)名稱(chēng)等信息查詢(xún)數(shù)據(jù)。

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

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

ClickHouse是基于列存儲(chǔ)的數(shù)據(jù)庫(kù)，不支持事務(wù)，查詢(xún)性能非常高，號(hào)稱(chēng)查詢(xún)十幾億的數(shù)據(jù)，能夠秒級(jí)返回。

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

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

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

其實(shí)如果查詢(xún)條件非常多，使用ClickHouse也不是特別合適，這時(shí)候可以改成ElasticSearch，不過(guò)它跟Mysql一樣，存在深分頁(yè)問(wèn)題。

3 count的各種用法性能對(duì)比

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

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

• count(*) ：它會(huì)獲取所有行的數(shù)據(jù)，不做任何處理，行數(shù)加1。

• count(1)：它會(huì)獲取所有行的數(shù)據(jù)，每行固定值1，也是行數(shù)加1。

• count(id)：id代表主鍵，它需要從所有行的數(shù)據(jù)中解析出id字段，其中id肯定都不為NULL，行數(shù)加1。

• count(普通索引列)：它需要從所有行的數(shù)據(jù)中解析出普通索引列，然后判斷是否為NULL，如果不是NULL，則行數(shù)加1。

• count(未加索引列)：它會(huì)全表掃描獲取所有數(shù)據(jù)，解析出未加索引列，然后判斷是否為NULL，如果不是NULL，則行數(shù)加1。

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

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

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

意不意外，驚不驚喜？

千萬(wàn)別跟select *?搞混了。

轉(zhuǎn)載于：https://mp.weixin.qq.com/s/y4B5Dx4Z8G_pnxgjIubsCg