欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

5招帶你輕松優(yōu)化MySQL count(*)查詢性能

 更新時間:2022年11月16日 09:27:28   作者:蘇三說技術  
最近在公司優(yōu)化了幾個慢查詢接口的性能,總結了一些心得體會拿出來跟大家一起分享一下,文中的示例代碼講解詳細,希望對大家會有所幫助

前言

最近我在公司優(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中使用最多的存儲引擎是:innodbmyisam

在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)聽Mysqlbinlog日志。當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 中 blob 和 text 數據類型詳解

    MySQL 中 blob 和 text 數據類型詳解

    本文主要介紹了MySQL中blob和text數據類型詳解,文中通過示例代碼介紹的非常詳細,具有一定的參考價值,感興趣的小伙伴們可以參考一下
    2022-02-02
  • 使用mysql中遇到的幾個問題

    使用mysql中遇到的幾個問題

    首先mysql不是可視化的,可以通過命令行進行操作,包括創(chuàng)建數據庫、表、添加數據等等。那豈不是很不方便了嗎?
    2009-07-07
  • windows 下忘記mysql root密碼的更改方法

    windows 下忘記mysql root密碼的更改方法

    mysql數據庫忘記了root密碼是件很痛苦的事,本文介紹如何解決windows環(huán)境下mysql服務器忘記root密碼的解決方法,需要的朋友可以參考下
    2016-10-10
  • CentOS7環(huán)境下安裝MySQL5.5數據庫

    CentOS7環(huán)境下安裝MySQL5.5數據庫

    大家好,本篇文章主要講的是CentOS7環(huán)境下安裝MySQL5.5數據庫,感興趣的同學趕快來看一看吧,對你有幫助的話記得收藏一下,方便下次瀏覽
    2021-12-12
  • MySQL手動注冊binlog文件造成主從異常的原因

    MySQL手動注冊binlog文件造成主從異常的原因

    這篇文章主要介紹了MySQL手動注冊binlog文件造成主從異常的原因,幫助大家更好的理解和使用MySQL數據庫,感興趣的朋友可以了解下
    2020-10-10
  • MySQL如何確定VARCHAR大小問題

    MySQL如何確定VARCHAR大小問題

    本文主要介紹了MySQL如何確定VARCHAR大小問題,文中通過示例代碼介紹的非常詳細,具有一定的參考價值,感興趣的小伙伴們可以參考一下
    2022-02-02
  • MySQL 查詢結果取交集的實現方法

    MySQL 查詢結果取交集的實現方法

    本文將詳細介紹MySQL中如何實現以SQL查詢返回的結果集取交集的實現方法,需要的朋友可以參考
    2012-11-11
  • MySQL語句之條件語句IFNULL和COALESCE的區(qū)別說明

    MySQL語句之條件語句IFNULL和COALESCE的區(qū)別說明

    這篇文章主要介紹了MySQL語句之條件語句IFNULL和COALESCE的區(qū)別說明,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教
    2024-04-04
  • MySQL索引失效的幾種情況小結

    MySQL索引失效的幾種情況小結

    本文主要介紹了MySQL索引失效的幾種情況小結,文中通過示例代碼介紹的非常詳細,對大家的學習或者工作具有一定的參考學習價值,需要的朋友們下面隨著小編來一起學習學習吧
    2023-03-03
  • MySQL 4.1/5.0/5.1/5.5/5.6各版本的主要區(qū)別整理

    MySQL 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

最新評論