MYSQL進(jìn)階知識(shí)點(diǎn)有哪些

這篇文章主要介紹MYSQL進(jìn)階知識(shí)點(diǎn)有哪些，文中介紹的非常詳細(xì)，具有一定的參考價(jià)值，感興趣的小伙伴們一定要看完！

文章目錄

• 1 前言

• 
  

• 1.1 數(shù)據(jù)庫(kù)架構(gòu)

• 1.2 監(jiān)控信息

• 2 影響數(shù)據(jù)庫(kù)的因素

• 
  

• 2.6.1 什么是事務(wù)

• 2.6.2 事務(wù)的原子性（ATOMICITY）

• 2.6.3 事務(wù)的一致性（CONSISTENCY）

• 2.6.4 事務(wù)的隔離性（ISOLATION）

• 2.6.5 事務(wù)的持久性（DURABILITY）

• 2.6.7 什么是大事務(wù)

• 2.5.1 大表對(duì)查詢的影響

• 2.5.2 大表對(duì)DDL操作的影響

• 2.5.3 如何處理數(shù)據(jù)庫(kù)中的大表

• 2.1 超高的QPS和TPS

• 2.2 大量的并發(fā)和超高的CPU使用率

• 2.3 磁盤IO

• 2.4 網(wǎng)卡流量

• 2.5 大表

• 
  

• 2.6 大事務(wù)

• 
  

1 前言

服務(wù)器的壓力來(lái)很大一部分壓力來(lái)自于數(shù)據(jù)庫(kù)的性能，如果沒(méi)有穩(wěn)定的數(shù)據(jù)庫(kù)及服務(wù)器環(huán)境，那么服務(wù)器很容易出現(xiàn)一些故障甚至是宕機(jī)，造成的后果也是不可估量的， 因此數(shù)據(jù)庫(kù)的性能優(yōu)化必不可少。

1.1 數(shù)據(jù)庫(kù)架構(gòu)

一般的數(shù)據(jù)庫(kù)架構(gòu)都是一臺(tái)主服務(wù)器，下面搭載著幾個(gè)或十幾個(gè)從服務(wù)器進(jìn)行主從同步，當(dāng)主服務(wù)器宕機(jī)之后，需要程序員手動(dòng)選出一臺(tái)數(shù)據(jù)最新的從服務(wù)器接替主服務(wù)器，然后對(duì)新的主服務(wù)器進(jìn)行同步。然而有時(shí)候因?yàn)閺姆?wù)器較多，導(dǎo)致這個(gè)過(guò)程是相當(dāng)耗時(shí)的，并且在這個(gè)過(guò)程也是對(duì)網(wǎng)卡的容量的一個(gè)挑戰(zhàn)。

1.2 監(jiān)控信息

QPS & TPS：數(shù)值越高越好。
并發(fā)量：同一時(shí)間處理的請(qǐng)求的數(shù)量。
CPU使用率：越低越好。
磁盤IO：讀寫性能越高越好。
注意：一般公司在大促活動(dòng)前后，最好不要在主庫(kù)上進(jìn)行數(shù)據(jù)庫(kù)備份，或者在大型活動(dòng)前取消這類計(jì)劃，因?yàn)檫@會(huì)嚴(yán)重?fù)p耗服務(wù)器的性能。

2 影響數(shù)據(jù)庫(kù)的因素

影響數(shù)據(jù)庫(kù)的因素有很多，比如有：sql查詢速度，服務(wù)器硬件，網(wǎng)卡流量，磁盤IO等等，后面我們會(huì)細(xì)說(shuō)，下面介紹一下一些監(jiān)控信息中反饋給我們的信息，以及我們應(yīng)該如何優(yōu)化它。

2.1 超高的QPS和TPS

由于效率低下的SQL，往往會(huì)造成超高的QPS和TPS的風(fēng)險(xiǎn)。在一般的大促期間，網(wǎng)站的訪問(wèn)量會(huì)大大的提高，也會(huì)提高數(shù)據(jù)庫(kù)的QPS和TPS。
什么是QPS：每秒鐘處理的查詢量。比如我們有一個(gè)cpu的情況下，10ms可以處理一個(gè)sql，那么1s就可以處理100個(gè)sql，QPS<=100，但當(dāng)我們100ms才處理一個(gè)sql，那么我們1s鐘才能處理10個(gè)sql，QPS<=10，這兩種情況是相差很大的，因此盡量?jī)?yōu)化sql性能。

2.2 大量的并發(fā)和超高的CPU使用率

那在這種情況下會(huì)導(dǎo)致什么風(fēng)險(xiǎn)呢？
在大量的并發(fā)下，可能會(huì)導(dǎo)致數(shù)據(jù)庫(kù)連接數(shù)被占滿，這種情況下，盡量將數(shù)據(jù)庫(kù)參數(shù) max_connections設(shè)置的大一點(diǎn)（默認(rèn)值為100），如果超過(guò)了這個(gè)值的時(shí)候會(huì)出現(xiàn)報(bào)500錯(cuò)誤的情況。
在超高的CPU使用率下，會(huì)因CPU資源耗盡而出現(xiàn)宕機(jī)。

2.3 磁盤IO

數(shù)據(jù)庫(kù)的瓶頸之一就是磁盤IO，那么它會(huì)帶來(lái)一下幾點(diǎn)風(fēng)險(xiǎn)：

1. 磁盤IO性能突然下降
   這往往會(huì)發(fā)生在熱數(shù)據(jù)大于服務(wù)器可用內(nèi)存的情況下。 通常這種情況我們只能使用更快的磁盤設(shè)備來(lái)解決。

2. 其他大量消耗磁盤性能的計(jì)劃任務(wù)
   這一點(diǎn)我們上面也提到了，最好避免在大促之前在主數(shù)據(jù)庫(kù)上進(jìn)行備份，或在從服務(wù)器上進(jìn)行，調(diào)整計(jì)劃任務(wù)，做好磁盤維護(hù)。

2.4 網(wǎng)卡流量

顯而易見(jiàn)，網(wǎng)卡流量過(guò)大造成網(wǎng)卡IO被占滿的風(fēng)險(xiǎn)。
一般的網(wǎng)卡是千兆網(wǎng)卡（1000Mb/8 ≈ 100MB）
如果連接數(shù)超過(guò)了網(wǎng)卡最大容量的時(shí)候，就會(huì)出現(xiàn)的服務(wù)無(wú)法連接的情況，那么我們應(yīng)該如何避免無(wú)法連接數(shù)據(jù)庫(kù)的情況：

1. 減少?gòu)姆?wù)器的數(shù)量
   因?yàn)槊總€(gè)從服務(wù)器都要從主服務(wù)器上面復(fù)制日志，因此從服務(wù)器越多，網(wǎng)卡流量就越大。

2. 進(jìn)行分級(jí)緩存
   一定要避免前端緩存突然失效而導(dǎo)致的后端訪問(wèn)量突然變大的情況。

3. 避免使用 select *進(jìn)行查詢
   這是一種最基本的數(shù)據(jù)庫(kù)優(yōu)化的方法，查詢出沒(méi)有必要的字段也會(huì)消耗大量的網(wǎng)絡(luò)流量。

4. 分離業(yè)務(wù)網(wǎng)絡(luò)和服務(wù)器網(wǎng)絡(luò)
   這樣可以避免主從同步或網(wǎng)絡(luò)備份影響網(wǎng)絡(luò)的性能。

2.5 大表

什么樣的表可以稱之為大表？其實(shí)都是相對(duì)而言，對(duì)于不同存儲(chǔ)引擎會(huì)有不同的限制。像nosql的數(shù)據(jù)存儲(chǔ)，并沒(méi)有限制表的行數(shù)，理論上只要磁盤的容量允許，都可以進(jìn)行存儲(chǔ)。但當(dāng)一張表的行數(shù)超過(guò)千萬(wàn)行的時(shí)候，就會(huì)對(duì)數(shù)據(jù)庫(kù)的性能產(chǎn)生很大的影響。那么我們可以總結(jié)大表的特點(diǎn)：

• 記錄行數(shù)巨大，單表超過(guò)千萬(wàn)行

• 表數(shù)據(jù)文件巨大，表數(shù)據(jù)文件超過(guò)10G

但就算符合了以上的特點(diǎn)，它也可能對(duì)我們數(shù)據(jù)庫(kù)性能不會(huì)產(chǎn)生很大的影響，因此這個(gè)說(shuō)法是相對(duì)的，比如像一般數(shù)據(jù)庫(kù)的日志表，即使記錄行數(shù)很大，文件大小很大，但我們一般只對(duì)它進(jìn)行增加和查詢，不涉及大量的i修改和刪除操作，因此不會(huì)對(duì)數(shù)據(jù)庫(kù)性能產(chǎn)生很大的影響。
但當(dāng)有一天因?yàn)闃I(yè)務(wù)發(fā)生變更，需要對(duì)這張10個(gè)G的日志表進(jìn)行列增加的時(shí)候，那么這個(gè)工程量無(wú)疑是巨大的。

2.5.1 大表對(duì)查詢的影響

大表往往代表著慢查詢的產(chǎn)生，慢查詢即是指很難在一定的時(shí)間內(nèi)過(guò)濾出所需要的數(shù)據(jù)。
例如在一個(gè)上千萬(wàn)條數(shù)據(jù)的日志表上，有一個(gè)叫做訂單來(lái)源的字段，它記錄著訂單是在哪一個(gè)平臺(tái)上進(jìn)行生成的。在一開(kāi)始業(yè)務(wù)不需要的情況下，是不會(huì)對(duì)數(shù)據(jù)庫(kù)性能造成影響的，但是后面由于業(yè)務(wù)需求，需要查看這上千萬(wàn)條數(shù)據(jù)的具體來(lái)自于哪一個(gè)平臺(tái)的訂單量，這一下就產(chǎn)生了很大的問(wèn)題。
因?yàn)橛捎谶@些訂單的來(lái)源渠道只有幾個(gè)，區(qū)分度很低，所以在上千萬(wàn)的數(shù)據(jù)中查詢某一些數(shù)據(jù)的話，這會(huì)消耗大量的磁盤IO，嚴(yán)重降低了磁盤的效率。在用戶每一次查看訂單的時(shí)候，都會(huì)從數(shù)據(jù)庫(kù)查詢一次訂單的來(lái)源，這樣會(huì)產(chǎn)生大量的慢查詢。

2.5.2 大表對(duì)DDL操作的影響

大表對(duì)DDL操作的影響，這會(huì)給我們帶來(lái)什么風(fēng)險(xiǎn)？

1. 在建立索引上需要很長(zhǎng)的時(shí)間
   在MySQL的5.5版本之前，數(shù)據(jù)庫(kù)在建立索引的時(shí)候會(huì)進(jìn)行鎖表，而在5.5版本之后，雖然不會(huì)鎖表，但是由于MySQL的復(fù)制機(jī)制是在新的主機(jī)上執(zhí)行，然后才能通過(guò)日志方式發(fā)送給從機(jī)，這樣會(huì)引起長(zhǎng)時(shí)間的主從延遲，影響正常的業(yè)務(wù)。

2. 修改表結(jié)構(gòu)需要長(zhǎng)時(shí)間鎖表
   在修改表的結(jié)構(gòu)過(guò)程中進(jìn)行鎖表，會(huì)給我們?cè)斐砷L(zhǎng)時(shí)間主從延遲的風(fēng)險(xiǎn)。由于我們MySQL的主從復(fù)制機(jī)制，往往是所有的表結(jié)構(gòu)操作是在主機(jī)上先執(zhí)行完成再通過(guò)日志方式傳給從機(jī)進(jìn)行相同的操作，然后才完成表結(jié)構(gòu)的主從復(fù)制。
   假設(shè)我們修改一個(gè)表的結(jié)構(gòu)，在主服務(wù)器上修改的時(shí)間為480s，那么我們?cè)趶臄?shù)據(jù)庫(kù)上的修改時(shí)間也為480s。由于現(xiàn)在MySQL的主從復(fù)制都是使用單線程，所以一旦有大表修改，在從服務(wù)器上沒(méi)有完成相關(guān)操作之前，其他的數(shù)據(jù)修改操作都無(wú)法執(zhí)行，因此這會(huì)造成至少480s以上的主從延遲。
   同時(shí)會(huì)影響數(shù)據(jù)的正常操作，這會(huì)造成所有的插入操作被阻塞，連接數(shù)會(huì)大額提高并占滿服務(wù)器，這時(shí)就會(huì)導(dǎo)致服務(wù)器出現(xiàn)500的連接錯(cuò)誤。

2.5.3 如何處理數(shù)據(jù)庫(kù)中的大表

1. 分庫(kù)分表，把一張大表分成多個(gè)小表
   難點(diǎn)：

1. 分表主鍵的選擇

2. 分表后跨分區(qū)數(shù)據(jù)的查詢和統(tǒng)計(jì)

2. 大表的歷史數(shù)據(jù)歸檔
   作用：減少對(duì)前后端業(yè)務(wù)的影響
   難點(diǎn)：

1. 歸檔時(shí)間點(diǎn)的選擇

2. 如何進(jìn)行歸檔操作

2.6 大事務(wù)

2.6.1 什么是事務(wù)

1. 事務(wù)是數(shù)據(jù)庫(kù)系統(tǒng)區(qū)別于其它一切文件系統(tǒng)的重要特性之一。

2. 事務(wù)是一組具有原子性的SQL語(yǔ)句，或是一個(gè)獨(dú)立的工作單元。+
   因此事務(wù)需要符合以下4個(gè)特性：原子性，一致性，隔離性，持久性。

2.6.2 事務(wù)的原子性（ATOMICITY）

定義：一個(gè)事務(wù)必須被視為一個(gè)不可分割的最小工作單元，整個(gè)事務(wù)中的所有操作要么全部提交成功，要么全部失敗，對(duì)于一個(gè)事務(wù)來(lái)說(shuō)，不可能只執(zhí)行其中的一部分操作。
例子：
A要轉(zhuǎn)給B1000元，在A賬戶中取出1000元時(shí)，數(shù)據(jù)庫(kù)上A的余額減去1000，但是在加到B余額上的時(shí)候，服務(wù)器出現(xiàn)了故障，那A的1000元需要回退到A的賬戶中，保持事務(wù)原子性，要么一起成功，要么一起失敗。

2.6.3 事務(wù)的一致性（CONSISTENCY）

定義：一致性是指事務(wù)將數(shù)據(jù)庫(kù)從一種一致性狀態(tài)轉(zhuǎn)換到另外一種一致性狀態(tài)，在事務(wù)開(kāi)始之前和事務(wù)結(jié)束后數(shù)據(jù)庫(kù)中數(shù)據(jù)的完整性沒(méi)有被破壞。
例子：
銀行中A的1000塊轉(zhuǎn)給了B，A的余額為0，B的賬戶余額從0變?yōu)?000，但是從頭到尾，A+B = 1000（A的余額） + 0（B的余額） = 0（A的余額） + 1000（B的余額），也就是說(shuō)，A和B的總余額數(shù)是不變的，從頭到尾還是1000元。

2.6.4 事務(wù)的隔離性（ISOLATION）

定義：隔離性要求一個(gè)事務(wù)對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的修改，在未提交完成對(duì)于其他事務(wù)時(shí)不可見(jiàn)的。
例子：
銀行中A從余額1000元中取款500元，在取款事務(wù)還沒(méi)提交前，執(zhí)行了一個(gè)查詢A賬戶余額的事務(wù)，那查詢出來(lái)的結(jié)果還是余額1000元，因?yàn)樵谌】钍聞?wù)還沒(méi)提交之前，其他業(yè)務(wù)對(duì)它的事務(wù)過(guò)程是不可見(jiàn)的。

• SQL標(biāo)準(zhǔn)中定義的四種隔離級(jí)別

• 未提交讀 > 已提交讀 > 可重復(fù)讀 > 可串行化

• 未提交讀 < 已提交讀 < 可重復(fù)讀 < 可串行化

• 最高的隔離級(jí)別。簡(jiǎn)單來(lái)說(shuō)就是會(huì)在讀取的每一條數(shù)據(jù)上都加鎖，所以可能會(huì)導(dǎo)致大量的鎖超時(shí)和鎖占用的問(wèn)題，因此在實(shí)際業(yè)務(wù)中我們很少使用這個(gè)隔離級(jí)別。除非是嚴(yán)格要求數(shù)據(jù)一致性，并且可以接受在沒(méi)有并發(fā)的情況下，我們才會(huì)考慮使用這個(gè)隔離級(jí)別。

• 比已提交讀更高一層的級(jí)別，在可重復(fù)讀的隔離級(jí)別事務(wù)中，一個(gè)未提交的事務(wù)中查詢表中的數(shù)據(jù)，在另外一個(gè)事務(wù)中向這張表插入一條數(shù)據(jù)并提交，但當(dāng)回到剛剛未提交的事務(wù)中再查詢一次表的數(shù)據(jù)和上一次查詢到的結(jié)果是相同的，并沒(méi)有查詢到剛剛插入的那一條數(shù)據(jù)。

• 但在已提交讀的隔離級(jí)別中是可以查到剛剛的那條數(shù)據(jù)的

• 查看當(dāng)前數(shù)據(jù)庫(kù)的隔離級(jí)別語(yǔ)句：
  show variables like '%iso%'

• 修改當(dāng)前數(shù)據(jù)庫(kù)隔離級(jí)別語(yǔ)句：
  set session tx_isolation='read-committed'

• 很多的數(shù)據(jù)中默認(rèn)的隔離級(jí)別，在事務(wù)提交之后才能讀出數(shù)據(jù)，也就是事務(wù)對(duì)外不可見(jiàn)。

• 未提交的事務(wù)對(duì)外可見(jiàn)，就是我們常說(shuō)的臟讀，查詢到的數(shù)據(jù)稱之為臟數(shù)據(jù)。

• 未提交讀（READ UNCOMMITED）

• 已提交讀（READ COMMITED）

• 可重復(fù)讀（REPEATABLE READ）

• 可串行化（SERIALIZABLE）

• 隔離性：

• 并發(fā)性：

2.6.5 事務(wù)的持久性（DURABILITY）

定義：一旦事務(wù)提交，則其所做的修改就會(huì)永久保存到數(shù)據(jù)庫(kù)中。此時(shí)即使系統(tǒng)崩潰，已經(jīng)提交的修改數(shù)據(jù)也不會(huì)丟失（不包括磁盤損壞等物理因素）。
例子：
銀行中A用戶存入賬戶1000元，事務(wù)提交后，即使銀行系統(tǒng)崩潰，但在恢復(fù)回來(lái)后，除非A對(duì)余額進(jìn)行了操作，否則A賬戶中的1000元是不會(huì)變的，這就是事務(wù)的持久性。

2.6.7 什么是大事務(wù)

講了這么多，那什么是大事務(wù)？
大事務(wù)就是指運(yùn)行時(shí)間比較長(zhǎng)，操作的數(shù)據(jù)比較多的事務(wù)。舉例來(lái)說(shuō)，有一個(gè)理財(cái)產(chǎn)品每天都會(huì)統(tǒng)計(jì)每個(gè)用戶前一天的收入所得，那如果需要統(tǒng)計(jì)所有用戶的收入所得并更新到用戶余額中，這時(shí)數(shù)以億計(jì)的更新就需要數(shù)小時(shí)，如果中途出現(xiàn)的出現(xiàn)故障進(jìn)行的回滾，事務(wù)需要進(jìn)行的時(shí)間就更加不可估量，還不包括在更新過(guò)程中，會(huì)對(duì)用戶的余額進(jìn)行加鎖，造成所有用戶都無(wú)法使用余額這樣的問(wèn)題。

• 大事務(wù)會(huì)造成哪些風(fēng)險(xiǎn)：

1. 鎖定太多的數(shù)據(jù)，造成大量的阻塞和鎖超時(shí)

2. 回滾時(shí)所需的時(shí)間比較長(zhǎng)

3. 執(zhí)行時(shí)間長(zhǎng)，容易造成主從延遲

• 如何避免大事務(wù)？

1. 避免一次處理太多的數(shù)據(jù)。

2. 移出不必要的事務(wù)中的SELECT操作。

能做到以上的兩點(diǎn)基本可以避免大事務(wù)的產(chǎn)生。

以上是“MYSQL進(jìn)階知識(shí)點(diǎn)有哪些”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對(duì)大家有幫助，更多相關(guān)知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！