MySQL聚合函數(shù)

很多時(shí)候，我們查詢數(shù)據(jù)的時(shí)候都不會(huì)把明細(xì)數(shù)據(jù)查詢出來，那樣一般意義也不大。更多的時(shí)候是根據(jù)業(yè)務(wù)需求，把數(shù)據(jù)聚合成業(yè)務(wù)能直接使用的數(shù)據(jù)。MYSQL中有5個(gè)聚合函數(shù)，如下面5個(gè)，用的最多的還是count和sum，下面分別介紹一下用法。

10年積累的成都網(wǎng)站設(shè)計(jì)、成都做網(wǎng)站經(jīng)驗(yàn)，可以快速應(yīng)對(duì)客戶對(duì)網(wǎng)站的新想法和需求。提供各種問題對(duì)應(yīng)的解決方案。讓選擇我們的客戶得到更好、更有力的網(wǎng)絡(luò)服務(wù)。我雖然不認(rèn)識(shí)你，你也不認(rèn)識(shí)我。但先網(wǎng)站設(shè)計(jì)制作后付款的網(wǎng)站建設(shè)流程，更有蕭山免費(fèi)網(wǎng)站建設(shè)讓你可以放心的選擇與我們合作。

【COUNT】

在MySQL中，COUNT()函數(shù)統(tǒng)計(jì)數(shù)據(jù)表中包含的記錄行的總數(shù)，或者根據(jù)查詢結(jié)果返回列中包含的數(shù)據(jù)行數(shù)，使用方法有以下兩種：

求order表中，查詢一共有多少條訂單數(shù)，SQL語句如下↓

【SUM】

在MySQL中，SUM()是一個(gè)求總和的函數(shù)，返回指定列值的總和。

求order表中所有產(chǎn)品銷售數(shù)量，SQL語句如下↓

【AVG】

在MySQL中，AVG()函數(shù)通過計(jì)算返回的行數(shù)和每一行數(shù)據(jù)的和，求得指定列數(shù)據(jù)的平均值。

求order表中，2021年所有產(chǎn)品的平均單價(jià)，SQL語句如下↓

【MAX/MIN】

在MySQL中，MAX()函數(shù)是用來返回指定列中的最大值。

求order表中，查詢最大的單價(jià)價(jià)格，SQL預(yù)計(jì)如下↓

在MySQL中，MIN()函數(shù)是用來返回指定列中的最小值。

求order表中，查詢最小的單價(jià)價(jià)格，SQL預(yù)計(jì)如下↓

【結(jié)合GROUP BY】

正常情況下，聚合函數(shù)都是搭配著GROUP BY來使用的?？梢允前词》菥酆?、產(chǎn)品聚合、時(shí)間聚合等等。下面演示每個(gè)品牌最低單價(jià)的聚合，其他幾個(gè)聚合函數(shù)使用方式一樣，SQL語句如下↓

還可以用ORDER BY排個(gè)序，求每個(gè)品牌累計(jì)銷售價(jià)格的SQL語句，從高到低排序↓

End

◆ PowerBI開場(chǎng)白

◆ Python高德地圖可視化

◆ Python不規(guī)則條形圖

Mysql某個(gè)表有近千萬數(shù)據(jù)，CRUD比較慢，如何優(yōu)化？

數(shù)據(jù)千萬級(jí)別之多，占用的存儲(chǔ)空間也比較大，可想而知它不會(huì)存儲(chǔ)在一塊連續(xù)的物理空間上，而是鏈?zhǔn)酱鎯?chǔ)在多個(gè)碎片的物理空間上。可能對(duì)于長(zhǎng)字符串的比較，就用更多的時(shí)間查找與比較，這就導(dǎo)致用更多的時(shí)間。

可以做表拆分，減少單表字段數(shù)量，優(yōu)化表結(jié)構(gòu)。

在保證主鍵有效的情況下，檢查主鍵索引的字段順序，使得查詢語句中條件的字段順序和主鍵索引的字段順序保持一致。

主要兩種拆分 垂直拆分，水平拆分。

垂直分表

也就是“大表拆小表”，基于列字段進(jìn)行的。一般是表中的字段較多，將不常用的， 數(shù)據(jù)較大，長(zhǎng)度較長(zhǎng)（比如text類型字段）的拆分到“擴(kuò)展表“。 一般是針對(duì) 那種 幾百列的大表，也避免查詢時(shí)，數(shù)據(jù)量太大造成的“跨頁(yè)”問題。

垂直分庫(kù)針對(duì)的是一個(gè)系統(tǒng)中的不同業(yè)務(wù)進(jìn)行拆分，比如用戶User一個(gè)庫(kù)，商品Product一個(gè)庫(kù)，訂單Order一個(gè)庫(kù)。 切分后，要放在多個(gè)服務(wù)器上，而不是一個(gè)服務(wù)器上。為什么？ 我們想象一下，一個(gè)購(gòu)物網(wǎng)站對(duì)外提供服務(wù)，會(huì)有用戶，商品，訂單等的CRUD。沒拆分之前， 全部都是落到單一的庫(kù)上的，這會(huì)讓數(shù)據(jù)庫(kù)的單庫(kù)處理能力成為瓶頸。按垂直分庫(kù)后，如果還是放在一個(gè)數(shù)據(jù)庫(kù)服務(wù)器上， 隨著用戶量增大，這會(huì)讓單個(gè)數(shù)據(jù)庫(kù)的處理能力成為瓶頸，還有單個(gè)服務(wù)器的磁盤空間，內(nèi)存，tps等非常吃緊。 所以我們要拆分到多個(gè)服務(wù)器上，這樣上面的問題都解決了，以后也不會(huì)面對(duì)單機(jī)資源問題。

數(shù)據(jù)庫(kù)業(yè)務(wù)層面的拆分，和服務(wù)的“治理”，“降級(jí)”機(jī)制類似，也能對(duì)不同業(yè)務(wù)的數(shù)據(jù)分別的進(jìn)行管理，維護(hù)，監(jiān)控，擴(kuò)展等。 數(shù)據(jù)庫(kù)往往最容易成為應(yīng)用系統(tǒng)的瓶頸，而數(shù)據(jù)庫(kù)本身屬于“有狀態(tài)”的，相對(duì)于Web和應(yīng)用服務(wù)器來講，是比較難實(shí)現(xiàn)“橫向擴(kuò)展”的。 數(shù)據(jù)庫(kù)的連接資源比較寶貴且單機(jī)處理能力也有限，在高并發(fā)場(chǎng)景下，垂直分庫(kù)一定程度上能夠突破IO、連接數(shù)及單機(jī)硬件資源的瓶頸。

水平分表

針對(duì)數(shù)據(jù)量巨大的單張表（比如訂單表），按照某種規(guī)則（RANGE,HASH取模等），切分到多張表里面去。 但是這些表還是在同一個(gè)庫(kù)中，所以庫(kù)級(jí)別的數(shù)據(jù)庫(kù)操作還是有IO瓶頸。不建議采用。

水平分庫(kù)分表

將單張表的數(shù)據(jù)切分到多個(gè)服務(wù)器上去，每個(gè)服務(wù)器具有相應(yīng)的庫(kù)與表，只是表中數(shù)據(jù)集合不同。 水平分庫(kù)分表能夠有效的緩解單機(jī)和單庫(kù)的性能瓶頸和壓力，突破IO、連接數(shù)、硬件資源等的瓶頸。

水平分庫(kù)分表切分規(guī)則

1. RANGE

從0到10000一個(gè)表，10001到20000一個(gè)表；

2. HASH取模

一個(gè)商場(chǎng)系統(tǒng)，一般都是將用戶，訂單作為主表，然后將和它們相關(guān)的作為附表，這樣不會(huì)造成跨庫(kù)事務(wù)之類的問題。 取用戶id，然后hash取模，分配到不同的數(shù)據(jù)庫(kù)上。

3. 地理區(qū)域

比如按照華東，華南，華北這樣來區(qū)分業(yè)務(wù)，七牛云應(yīng)該就是如此。

4. 時(shí)間

按照時(shí)間切分，就是將6個(gè)月前，甚至一年前的數(shù)據(jù)切出去放到另外的一張表，因?yàn)殡S著時(shí)間流逝，這些表的數(shù)據(jù) 被查詢的概率變小，所以沒必要和“熱數(shù)據(jù)”放在一起，這個(gè)也是“冷熱數(shù)據(jù)分離”。

分庫(kù)分表后面臨的問題

事務(wù)支持

分庫(kù)分表后，就成了分布式事務(wù)了。如果依賴數(shù)據(jù)庫(kù)本身的分布式事務(wù)管理功能去執(zhí)行事務(wù)，將付出高昂的性能代價(jià)； 如果由應(yīng)用程序去協(xié)助控制，形成程序邏輯上的事務(wù)，又會(huì)造成編程方面的負(fù)擔(dān)。

跨庫(kù)join

只要是進(jìn)行切分，跨節(jié)點(diǎn)Join的問題是不可避免的。但是良好的設(shè)計(jì)和切分卻可以減少此類情況的發(fā)生。解決這一問題的普遍做法是分兩次查詢實(shí)現(xiàn)。在第一次查詢的結(jié)果集中找出關(guān)聯(lián)數(shù)據(jù)的id,根據(jù)這些id發(fā)起第二次請(qǐng)求得到關(guān)聯(lián)數(shù)據(jù)。

跨節(jié)點(diǎn)的count,order by,group by以及聚合函數(shù)問題

這些是一類問題，因?yàn)樗鼈兌夹枰谌繑?shù)據(jù)集合進(jìn)行計(jì)算。多數(shù)的代理都不會(huì)自動(dòng)處理合并工作。解決方案：與解決跨節(jié)點(diǎn)join問題的類似，分別在各個(gè)節(jié)點(diǎn)上得到結(jié)果后在應(yīng)用程序端進(jìn)行合并。和join不同的是每個(gè)結(jié)點(diǎn)的查詢可以并行執(zhí)行，因此很多時(shí)候它的速度要比單一大表快很多。但如果結(jié)果集很大，對(duì)應(yīng)用程序內(nèi)存的消耗是一個(gè)問題。

數(shù)據(jù)遷移，容量規(guī)劃，擴(kuò)容等問題

來自淘寶綜合業(yè)務(wù)平臺(tái)團(tuán)隊(duì)，它利用對(duì)2的倍數(shù)取余具有向前兼容的特性（如對(duì)4取余得1的數(shù)對(duì)2取余也是1）來分配數(shù)據(jù)，避免了行級(jí)別的數(shù)據(jù)遷移，但是依然需要進(jìn)行表級(jí)別的遷移，同時(shí)對(duì)擴(kuò)容規(guī)模和分表數(shù)量都有限制?？偟脕碚f，這些方案都不是十分的理想，多多少少都存在一些缺點(diǎn)，這也從一個(gè)側(cè)面反映出了Sharding擴(kuò)容的難度。

ID問題

一旦數(shù)據(jù)庫(kù)被切分到多個(gè)物理結(jié)點(diǎn)上，我們將不能再依賴數(shù)據(jù)庫(kù)自身的主鍵生成機(jī)制。一方面，某個(gè)分區(qū)數(shù)據(jù)庫(kù)自生成的ID無法保證在全局上是唯一的；另一方面，應(yīng)用程序在插入數(shù)據(jù)之前需要先獲得ID,以便進(jìn)行SQL路由.

一些常見的主鍵生成策略

UUID

使用UUID作主鍵是最簡(jiǎn)單的方案，但是缺點(diǎn)也是非常明顯的。由于UUID非常的長(zhǎng)，除占用大量存儲(chǔ)空間外，最主要的問題是在索引上，在建立索引和基于索引進(jìn)行查詢時(shí)都存在性能問題。

Twitter的分布式自增ID算法Snowflake

在分布式系統(tǒng)中，需要生成全局UID的場(chǎng)合還是比較多的，twitter的snowflake解決了這種需求，實(shí)現(xiàn)也還是很簡(jiǎn)單的，除去配置信息，核心代碼就是毫秒級(jí)時(shí)間41位 機(jī)器ID 10位 毫秒內(nèi)序列12位。

跨分片的排序分頁(yè)

一般來講，分頁(yè)時(shí)需要按照指定字段進(jìn)行排序。當(dāng)排序字段就是分片字段的時(shí)候，我們通過分片規(guī)則可以比較容易定位到指定的分片，而當(dāng)排序字段非分片字段的時(shí)候，情況就會(huì)變得比較復(fù)雜了。為了最終結(jié)果的準(zhǔn)確性，我們需要在不同的分片節(jié)點(diǎn)中將數(shù)據(jù)進(jìn)行排序并返回，并將不同分片返回的結(jié)果集進(jìn)行匯總和再次排序，最后再返回給用戶。

如何提高億級(jí)別 mysql group by order by 效率

1、使用用索引

注意有些情況下不能夠使用索引來提高Order By語句的查詢性能。

這里需要注意的是，并不是任何情況下都能夠通過使用索引來提高Order Byz子句的查詢效率。如對(duì)不同的關(guān)鍵字使用這個(gè)語句、混合使用ASC模式和DESC模式、用于查詢條件的關(guān)鍵字與Order By語句中所使用的關(guān)鍵字不同、對(duì)關(guān)鍵字的非連續(xù)元素使用Order By子句、在同一條語句中使用不同的Order BY 和Group BY表達(dá)式、使用的表索引的類型不能夠按順序來保存行等情況，就無法通過使用索引來解決Order By語句的排序問題。此時(shí)就需要另想他法。如可以重新調(diào)整表結(jié)構(gòu)或者查詢語句，以滿足使用這個(gè)特性的特定條件。

通常情況下，為了避免使用Order By語句導(dǎo)致的查詢速度變慢的問題，先是需要考慮使用索引來解決問題。如果不能夠通過索引來解決問題，那么可以通過緩存在一定程度來緩解。如可以增加soft_buffer_size變量的大小、根據(jù)實(shí)際情況調(diào)整Read_buffer_size變量的大小、更改tmpdir目錄將其指向具有大量空閑空間的專用文件系統(tǒng)等等。有時(shí)候管理員可以使用這個(gè)特性將負(fù)載均勻分布到多個(gè)目錄中去。

2、使用Explain關(guān)鍵字來確認(rèn)是否可以通過索引來解決Order BY速度問題。

如可以通過使用explain select * from ad_user where is_active='Y' order by value(即在常規(guī)的查詢語句前面加上一個(gè)explain關(guān)鍵字)，用來判斷是否可以使用索引來提高查詢的效率。

判斷的方法是：如果這個(gè)查詢語句中，有一個(gè)using filesort這個(gè)字段，那么就非常的抱歉，無法通過使用索引來提高這個(gè)語句的查詢效率。反之，沒有這個(gè)字段，則說明可以通過索引來提高查詢效率。

3、分頁(yè)優(yōu)化

分頁(yè)程序原理很簡(jiǎn)單，這里就不多說了。

mysql的group by怎么優(yōu)化

在某些情況中，MySQL能夠做得更好，通過索引訪問而不用創(chuàng)建臨時(shí)表。

GROUP BY使用索引的最重要的前提條件是 所有GROUP BY列引用同一索引的屬性，并且索引按順序保存(例如，這是B-樹索引，而不是HASH索引)。

是否用索引訪問來代替臨時(shí)表的使用還取決于在查詢中使用了哪部分索引、為該部分指定的條件，以及選擇的累積函數(shù)。

有兩種方法可以通過索引優(yōu)化GROUP BY語句：

1，組合操作結(jié)合所有范圍判斷式使用(如果有)。

2，首先執(zhí)行范圍掃描，然后組合結(jié)果元組。