5招帶你輕松優(yōu)化MySQL count(*)查詢性能
前言
最近我在公司優(yōu)化過幾個慢查詢接口的性能,總結了一些心得體會拿出來跟大家一起分享一下,希望對你會有所幫助。
我們使用的數據庫是Mysql8
,使用的存儲引擎是Innodb
。這次優(yōu)化除了優(yōu)化索引
之外,更多的是在優(yōu)化count(*)
。
通常情況下,分頁接口一般會查詢兩次數據庫,第一次是獲取具體數據,第二次是獲取總的記錄行數,然后把結果整合之后,再返回。
查詢具體數據的sql,比如是這樣的:
select?id,name?from?user?limit?1,20;
它沒有性能問題。
但另外一條使用count(*)查詢總記錄行數的sql,例如:
select?count(*)?from?user;
卻存在性能差的問題。
為什么會出現這種情況呢?
1 count(*)為什么性能差
在Mysql中,count(*)
的作用是統(tǒng)計表中記錄的總行數。
而count(*)
的性能跟存儲引擎有直接關系,并非所有的存儲引擎,count(*)
的性能都很差。
在Mysql中使用最多的存儲引擎是:innodb
和myisam
。
在myisam中會把總行數保存到磁盤上,使用count(*)時,只需要返回那個數據即可,無需額外的計算,所以執(zhí)行效率很高。
而innodb則不同,由于它支持事務,有MVCC
(即多版本并發(fā)控制)的存在,在同一個時間點的不同事務中,同一條查詢sql,返回的記錄行數可能是不確定的。
在innodb使用count(*)時,需要從存儲引擎中一行行的讀出數據,然后累加起來,所以執(zhí)行效率很低。
如果表中數據量小還好,一旦表中數據量很大,innodb存儲引擎使用count(*)統(tǒng)計數據時,性能就會很差。
2 如何優(yōu)化count(*)性能
從上面得知,既然count(*)
存在性能問題,那么我們該如何優(yōu)化呢?
我們可以從以下幾個方面著手。
2.1 增加redis緩存
對于簡單的count(*),比如:統(tǒng)計瀏覽總次數或者瀏覽總人數,我們可以直接將接口使用redis緩存起來,沒必要實時統(tǒng)計。
當用戶打開指定頁面時,在緩存中每次都設置成count = count+1即可。
用戶第一次訪問頁面時,redis中的count值設置成1。用戶以后每訪問一次頁面,都讓count加1,最后重新設置到redis中。
這樣在需要展示數量的地方,從redis中查出count值返回即可。
該場景無需從數據埋點表中使用count(*)實時統(tǒng)計數據,性能將會得到極大的提升。
不過在高并發(fā)的情況下,可能會存在緩存和數據庫的數據不一致的問題。
但對于統(tǒng)計瀏覽總次數或者瀏覽總人數這種業(yè)務場景,對數據的準確性要求并不高,容忍數據不一致的情況存在。
2.2 加二級緩存
對于有些業(yè)務場景,新增數據很少,大部分是統(tǒng)計數量操作,而且查詢條件很多。這時候使用傳統(tǒng)的count(*)實時統(tǒng)計數據,性能肯定不會好。
假如在頁面中可以通過id、name、狀態(tài)、時間、來源等,一個或多個條件,統(tǒng)計品牌數量。
這種情況下用戶的組合條件比較多,增加聯合索引也沒用,用戶可以選擇其中一個或者多個查詢條件,有時候聯合索引也會失效,只能盡量滿足用戶使用頻率最高的條件增加索引。
也就是有些組合條件可以走索引,有些組合條件沒法走索引,這些沒法走索引的場景,該如何優(yōu)化呢?
答:使用二級緩存。
二級緩存其實就是內存緩存。
我們可以使用caffine
或者guava
實現二級緩存的功能。
目前SpringBoot
已經集成了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是由各個條件組合而成。
這樣通過某個條件組合查詢出品牌的數據之后,會把結果緩存到內存中,設置過期時間為5分鐘。
后面用戶在5分鐘內,使用相同的條件,重新查詢數據時,可以直接從二級緩存中查出數據,直接返回了。
這樣能夠極大的提示count(*)的查詢效率。
但是如果使用二級緩存,可能存在不同的服務器上,數據不一樣的情況。我們需要根據實際業(yè)務場景來選擇,沒法適用于所有業(yè)務場景。
2.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?order group?by?status;
使用group by
關鍵字分組統(tǒng)計相同status的數量,只會產生兩條記錄,一條記錄是有效訂單數量,另外一條記錄是無效訂單數量。
但有個問題:status字段只有1和0兩個值,重復度很高,區(qū)分度非常低,不能走索引,會全表掃描,效率也不高。
還有其他的解決方案不?
答:使用多線程處理。
我們可以使用CompleteFuture
使用兩個線程
異步調用統(tǒng)計有效訂單的sql和統(tǒng)計無效訂單的sql,最后匯總數據,這樣能夠提升查詢接口的性能。
2.4 減少join的表
大部分的情況下,使用count(*)是為了實時統(tǒng)計總數量的。
但如果表本身的數據量不多,但join的表太多,也可能會影響count(*)的效率。
比如在查詢商品信息時,需要根據商品名稱、單位、品牌、分類等信息查詢數據。
這時候寫一條sql可以查出想要的數據,比如下面這樣的:
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='測試商品'?and?u.id=123?and?b.id=124?and?c.id=125;
使用product表去join
了unit、brand和category這三張表。
其實這些查詢條件,在product表中都能查詢出數據,沒必要join額外的表。
我們可以把sql改成這樣:
select?count(*) from?product where?name='測試商品'?and?unit_id=123?and?brand_id=124?and?category_id=125;
在count(*)時只查product單表即可,去掉多余的表join,讓查詢效率可以提升不少。
2.5 改成ClickHouse
有些時候,join的表實在太多,沒法去掉多余的join,該怎么辦呢?
比如上面的例子中,查詢商品信息時,需要根據商品名稱、單位名稱、品牌名稱、分類名稱等信息查詢數據。
這時候根據product單表是沒法查詢出數據的,必須要去join
:unit、brand和category這三張表,這時候該如何優(yōu)化呢?
答:可以將數據保存到ClickHouse
。
ClickHouse是基于列存儲
的數據庫,不支持事務,查詢性能非常高,號稱查詢十幾億的數據,能夠秒級返回。
為了避免對業(yè)務代碼的嵌入性,可以使用Canal
監(jiān)聽Mysql
的binlog
日志。當product表有數據新增時,需要同時查詢出單位、品牌和分類的數據,生成一個新的結果集,保存到ClickHouse當中。
查詢數據時,從ClickHouse當中查詢,這樣使用count(*)的查詢效率能夠提升N倍。
需要特別提醒一下:使用ClickHouse時,新增數據不要太頻繁,盡量批量插入數據。
其實如果查詢條件非常多,使用ClickHouse也不是特別合適,這時候可以改成ElasticSearch
,不過它跟Mysql一樣,存在深分頁
問題。
3 count的各種用法性能對比
既然說到count(*),就不能不說一下count家族的其他成員,比如:count(1)、count(id)、count(普通索引列)、count(未加索引列)。
那么它們有什么區(qū)別呢?
- count(*) :它會獲取所有行的數據,不做任何處理,行數加1。
- count(1):它會獲取所有行的數據,每行固定值1,也是行數加1。
- count(id):id代表主鍵,它需要從所有行的數據中解析出id字段,其中id肯定都不為NULL,行數加1。
- count(普通索引列):它需要從所有行的數據中解析出普通索引列,然后判斷是否為NULL,如果不是NULL,則行數+1。
- count(未加索引列):它會全表掃描獲取所有數據,解析中未加索引列,然后判斷是否為NULL,如果不是NULL,則行數+1。
由此,最后count的性能從高到低是:
count(*) ≈ count(1) > count(id) > count(普通索引列) > count(未加索引列)
所以,其實count(*)
是最快的。
意不意外,驚不驚喜?
千萬別跟select *
搞混了。
到此這篇關于5招帶你輕松優(yōu)化MySQL count(*)查詢性能的文章就介紹到這了,更多相關MySQL count(*)查詢性能內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家!
相關文章
MySQL語句之條件語句IFNULL和COALESCE的區(qū)別說明
這篇文章主要介紹了MySQL語句之條件語句IFNULL和COALESCE的區(qū)別說明,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教2024-04-04MySQL 4.1/5.0/5.1/5.5/5.6各版本的主要區(qū)別整理
這篇文章主要介紹了MySQL 4.1/5.0/5.1/5.5/5.6各版本的主要區(qū)別整理,非常不錯,具有參考借鑒價值,需要的朋友可以參考下2017-08-08