一、數(shù)據(jù)庫(kù)調(diào)優(yōu)的目標(biāo)

• 1.盡可能節(jié)省系統(tǒng)資源，以便系統(tǒng)可以提供更大負(fù)荷的服務(wù)。（吞吐量更大）
• 2.合理的結(jié)構(gòu)設(shè)計(jì)和參數(shù)調(diào)整，以提高用戶操作響應(yīng)的速度。（響應(yīng)速度更快）
• 3.減少系統(tǒng)的瓶頸，提高M(jìn)ySQL數(shù)據(jù)庫(kù)整體的性能。

二、調(diào)優(yōu)時(shí)如何定位問(wèn)題？

1. 用戶的反饋★

• 用戶是我們的服務(wù)對(duì)象，因此他們的反饋是最直接的。雖然他們不會(huì)直接提出技術(shù)建議，但是有些問(wèn)題往往是用戶第一時(shí)間發(fā)現(xiàn)的。我們要重視用戶的反饋，找到和數(shù)據(jù)相關(guān)的問(wèn)題。

2. 日志分析★

• 通過(guò)查看數(shù)據(jù)庫(kù)日志和操作系統(tǒng)日志等方式找出異常情況，通過(guò)它們來(lái)定位遇到的問(wèn)題。

3. 服務(wù)器資源使用監(jiān)控

• 通過(guò)監(jiān)控服務(wù)器的CPU、內(nèi)存、I/O等使用情況，可以實(shí)時(shí)了解服務(wù)器的性能使用，與歷史情況進(jìn)行對(duì)比。

4. 數(shù)據(jù)庫(kù)內(nèi)部狀況監(jiān)控

• 在數(shù)據(jù)庫(kù)的監(jiān)控中，活動(dòng)會(huì)話（Active Session）監(jiān)控是一個(gè)重要的指標(biāo)。通過(guò)它，可以清楚地了解數(shù)據(jù)庫(kù)當(dāng)前是否處于非常繁忙的狀態(tài)，是否存在SQL堆積等。

5. 其它

• 除了活動(dòng)會(huì)話監(jiān)控以外，也可以對(duì)事務(wù)、鎖等待等進(jìn)行監(jiān)控，這些都可以幫助我們對(duì)數(shù)據(jù)庫(kù)的運(yùn)行狀態(tài)有更全面的認(rèn)識(shí)。

三、數(shù)據(jù)庫(kù)調(diào)優(yōu)步驟

第1步：選擇適合的DBMS

DBMS的選擇關(guān)系到了后面的整個(gè)設(shè)計(jì)過(guò)程，所以首先要選擇適合的DBMS，如果是確定好的系統(tǒng)直接第二步

• 如果對(duì)事務(wù)性處理以及安全性要求高的話，可以選擇商業(yè)的數(shù)據(jù)庫(kù)產(chǎn)品。這些數(shù)據(jù)庫(kù)在事務(wù)處理和查詢性能上都比較強(qiáng)，比如采用SQL Server、Oracle，單表存儲(chǔ)上億條數(shù)據(jù)是沒(méi)有問(wèn)題的。如果數(shù)據(jù)表設(shè)計(jì)得好，即使不采用分庫(kù)分表的方式，查詢效率也不差。
• 除此以外，也可以采用開源的MySQL進(jìn)行存儲(chǔ)，它有很多存儲(chǔ)引擎可以選擇，如果進(jìn)行事務(wù)處理的話可以選擇InnoDB，非事務(wù)處理可以選擇MylSAM。
• NoSQL陣營(yíng)包括鍵值型數(shù)據(jù)庫(kù)、文檔型數(shù)據(jù)庫(kù)、搜索引擎、列式存儲(chǔ)和圖形數(shù)據(jù)庫(kù)。這些數(shù)據(jù)庫(kù)的優(yōu)缺點(diǎn)和使用場(chǎng)景各有不同，比如列式存儲(chǔ)數(shù)據(jù)庫(kù)可以大幅度降低系統(tǒng)的I/O，適合于分布式文件系統(tǒng)，但如果數(shù)據(jù)需要頻繁地增刪改，那么列式存儲(chǔ)就不太適用了。

第2步：優(yōu)化表設(shè)計(jì)

RDBMS中，每個(gè)對(duì)象都可以定義為一張表，表與表之間的關(guān)系代表了對(duì)象之間的關(guān)系。如果用的是MySQL，還可以根據(jù)不同表的使用需求，選擇不同的存儲(chǔ)引擎。

• 1．表結(jié)構(gòu)要盡量遵循三范式的原則。這樣可以讓數(shù)據(jù)結(jié)構(gòu)更加清晰規(guī)范，減少冗余字段，同時(shí)也減少了在更新，插入和刪除數(shù)據(jù)時(shí)等異常情況的發(fā)生。
• 2．如果查詢應(yīng)用比較多，尤其是需要進(jìn)行多表聯(lián)查的時(shí)候，可以采用反范式進(jìn)行優(yōu)化。反范式采用空間換時(shí)間的方式，通過(guò)增加冗余字段提高查詢的效率。
• 3．表字段的數(shù)據(jù)類型選擇，關(guān)系到了查詢效率的高低以及存儲(chǔ)空間的大小。一般來(lái)說(shuō)，如果字段可以采用數(shù)值類型就不要采用字符類型;字符長(zhǎng)度要盡可能設(shè)計(jì)得短一些。針對(duì)字符類型來(lái)說(shuō)，當(dāng)確定字符長(zhǎng)度固定時(shí)，就可以采用CHAR類型；當(dāng)長(zhǎng)度不固定時(shí)，通常采用VARCHAR類型。

第3步：優(yōu)化邏輯查詢

• SQL查詢優(yōu)化，可以分為邏輯查詢優(yōu)化和物理查詢優(yōu)化。邏輯查詢優(yōu)化就是通過(guò)改變SQL語(yǔ)句的內(nèi)容讓SQL執(zhí)行效率更高效，采用的方式是對(duì)SQL語(yǔ)句進(jìn)行等價(jià)變換，對(duì)查詢進(jìn)行重寫。
• SQL的查詢重寫包括了子查詢優(yōu)化、等價(jià)謂詞重寫、視圖重寫、條件簡(jiǎn)化、連接消除和嵌套連接消除等。

第4步：優(yōu)化物理查詢

索引的創(chuàng)建和使用
索引相關(guān)問(wèn)題前邊幾章節(jié)已經(jīng)詳細(xì)剖析過(guò)，這里不在贅述。

第5步：考慮使用緩存

除了可以對(duì)SQL本身進(jìn)行優(yōu)化以外，還可以請(qǐng)外援提升查詢的效率。

• 因?yàn)閿?shù)據(jù)都存放到數(shù)據(jù)庫(kù)中，我們需要從數(shù)據(jù)庫(kù)層中取出數(shù)據(jù)放到內(nèi)存中進(jìn)行業(yè)務(wù)邏輯的操作，當(dāng)用戶量增大的時(shí)候，如果頻繁地進(jìn)行數(shù)據(jù)查詢，會(huì)消耗數(shù)據(jù)庫(kù)很多資源。如果將常用的數(shù)據(jù)直接放到內(nèi)存中，就會(huì)大幅提升查詢的效率。
• 常用的鍵值存儲(chǔ)數(shù)據(jù)庫(kù)有 Redis和Memcached，它們都可以將數(shù)據(jù)存放到內(nèi)存中。
• 從可靠性來(lái)說(shuō)，Redis支持持久化，可以讓我們的數(shù)據(jù)保存在硬盤上，不過(guò)這樣一來(lái)性能消耗也會(huì)比較大。而Memcached僅僅是內(nèi)存存儲(chǔ)，不支持持久化。
• 從支持的數(shù)據(jù)類型來(lái)說(shuō)，Redis 比 Memcached要多，它不僅支持key－value類型的數(shù)據(jù)，還支持List，Set，Hash等數(shù)據(jù)結(jié)構(gòu)。

第6步：庫(kù)級(jí)優(yōu)化

• 讀寫分離
• 如果讀和寫的業(yè)務(wù)量都很大，并且它們都在同一個(gè)數(shù)據(jù)庫(kù)服務(wù)器中進(jìn)行操作，那么數(shù)據(jù)庫(kù)的性能就會(huì)出現(xiàn)瓶頸，這時(shí)為了提升系統(tǒng)的性能，優(yōu)化用戶體驗(yàn)，我們可以采用讀寫分離的方式降低主數(shù)據(jù)庫(kù)的負(fù)載，比如用主數(shù)據(jù)庫(kù)完成寫操作，用從數(shù)據(jù)庫(kù)完成讀操作。
  
• 分庫(kù)分表
• 當(dāng)數(shù)據(jù)量級(jí)達(dá)到千萬(wàn)級(jí)以上時(shí)，有時(shí)候我們需要把一個(gè)數(shù)據(jù)庫(kù)切成多份，放到不同的數(shù)據(jù)庫(kù)服務(wù)器上，減少對(duì)單一數(shù)據(jù)庫(kù)服務(wù)器的訪問(wèn)壓力。如果使用的是MySQL，就可以使用MySQL自帶的分區(qū)表功能，也可以自己做垂直拆分（分庫(kù)）、水平拆分（分表）、垂直＋水平拆分（分庫(kù)分表）。

四、數(shù)據(jù)庫(kù)結(jié)構(gòu)優(yōu)化

1. 拆分表（冷熱分離）

• 把1個(gè)包含很多字段的表拆分成2個(gè)或者多個(gè)相對(duì)較小的表。
• 因?yàn)檫@些表中某些字段的操作頻率很高（熱數(shù)據(jù)），經(jīng)常要進(jìn)行查詢或者更新操作，而另外一些字段的使用頻率卻很低（冷數(shù)據(jù)），冷熱數(shù)據(jù)分離，可以減小表的寬度。
• 如果放在一個(gè)表里面，每次查詢都要讀取大記錄，會(huì)消耗較多的資源。
• MySQL限制每個(gè)表最多存儲(chǔ)4096列，并且每一行數(shù)據(jù)的大小不能超過(guò)65535字節(jié)。
• 表越寬，把表裝載進(jìn)內(nèi)存緩沖池時(shí)所占用的內(nèi)存也就越大，也會(huì)消耗更多的I/O。
• 冷熱數(shù)據(jù)分離的目的是：①減少磁盤I/O，保證熱數(shù)據(jù)的內(nèi)存緩存命中率。②更有效的利用緩存，避免讀入無(wú)用的冷數(shù)據(jù)。

2. 增加中間表、冗余字段

• 對(duì)于需要經(jīng)常聯(lián)合查詢的表，可以建立中間表以提高查詢效率。

• 通過(guò)建立中間表，把需要經(jīng)常聯(lián)合查詢的數(shù)據(jù)插入中間表中，然后將原來(lái)的聯(lián)合查詢改為對(duì)中間表的查詢，以此來(lái)提高查詢效率。

• 另外，表的規(guī)范化程度越高，表與表之間的關(guān)系就越多，需要連接查詢的情況也就越多。尤其在數(shù)據(jù)量大，而且需要頻繁進(jìn)行連接的時(shí)候，為了提升效率，我們也可以考慮增加冗余字段來(lái)減少連接。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-409290.html

3. 優(yōu)化字段數(shù)據(jù)類型

• 優(yōu)先選擇符合存儲(chǔ)需要的最小的數(shù)據(jù)類型。
• 列的字段越大，建立索引時(shí)所需要的空間也就越大，這樣一頁(yè)中所能存儲(chǔ)的索引節(jié)點(diǎn)的數(shù)量也就越少，在遍歷時(shí)所需要的IO次數(shù)也就越多，索引的性能也就越差。

五、單表記錄過(guò)大優(yōu)化

• 1、限定查詢范圍，使用上查詢條件，索引。
• 2、讀寫分離
  
• 3、垂直拆分，水平拆分

到了這里，關(guān)于MySQL高級(jí)第十一篇：數(shù)據(jù)庫(kù)調(diào)優(yōu)策略（定位-調(diào)優(yōu)-結(jié)構(gòu)）的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！