如何正確優(yōu)化SQL數(shù)據(jù)庫

    下文內(nèi)容主要給大家?guī)?/span>如何正確優(yōu)化SQL數(shù)據(jù)庫，所講到的知識，與書籍略有不同，都是億速云專業(yè)技術(shù)人員在與用戶接觸過程中，總結(jié)出來的，具有一定的經(jīng)驗分享價值，希望給廣大讀者帶來幫助。

     要正確的優(yōu)化SQL，我們需要快速定位能性的瓶頸點，也就是說快速找到我們SQL主要的開銷在哪里？而大多數(shù)情況性能最慢的設(shè)備會是瓶頸點，如下載時網(wǎng)絡(luò)速度可能會是瓶頸點，本地復(fù)制文件時硬盤可能會是瓶頸點，為什么這些一般的工作我們能快速確認(rèn)瓶頸點呢，因為我們對這些慢速設(shè)備的性能數(shù)據(jù)有一些基本的認(rèn)識，如網(wǎng)絡(luò)帶寬是2Mbps，硬盤是每分鐘7200轉(zhuǎn)等等。因此，為了快速找到SQL的性能瓶頸點，我們也需要了解我們計算機系統(tǒng)的硬件基本性能指標(biāo)，下圖展示的當(dāng)前主流計算機性能指標(biāo)數(shù)據(jù)

     從圖上可以看到基本上每種設(shè)備都有兩個指標(biāo)：

延時（響應(yīng)時間）：表示硬件的突發(fā)處理能力；

帶寬（吞吐量）：代表硬件持續(xù)處理能力。

      從上圖可以看出，計算機系統(tǒng)硬件性能從高到代依次為：

CPU——Cache(L1-L2-L3)——內(nèi)存——SSD硬盤——網(wǎng)絡(luò)——硬盤

     根據(jù)數(shù)據(jù)庫知識，我們可以列出每種硬件主要的工作內(nèi)容：

CPU及內(nèi)存：緩存數(shù)據(jù)訪問、比較、排序、事務(wù)檢測、SQL解析、函數(shù)或邏輯運算；

網(wǎng)絡(luò)：結(jié)果數(shù)據(jù)傳輸、SQL請求、遠程數(shù)據(jù)庫訪問（dblink）；

硬盤：數(shù)據(jù)訪問、數(shù)據(jù)寫入、日志記錄、大數(shù)據(jù)量排序、大表連接。

     根據(jù)當(dāng)前計算機硬件的基本性能指標(biāo)及其在數(shù)據(jù)庫中主要操作內(nèi)容，可以整理出如下圖所示的性能基本優(yōu)化法則：

這個優(yōu)化法則歸納為5個層次：

1、  減少數(shù)據(jù)訪問（減少磁盤訪問）

2、  返回更少數(shù)據(jù)（減少網(wǎng)絡(luò)傳輸或磁盤訪問）

3、  減少交互次數(shù)（減少網(wǎng)絡(luò)傳輸）

4、  減少云服務(wù)器CPU開銷（減少CPU及內(nèi)存開銷）

5、  利用更多資源（增加資源）

        由于每一層優(yōu)化法則都是解決其對應(yīng)硬件的性能問題，所以帶來的性能提升比例也不一樣。傳統(tǒng)數(shù)據(jù)庫系統(tǒng)設(shè)計是也是盡可能對低速設(shè)備提供優(yōu)化方法，因此針對低速設(shè)備問題的可優(yōu)化手段也更多，優(yōu)化成本也更低。我們?nèi)魏我粋€SQL的性能優(yōu)化都應(yīng)該按這個規(guī)則由上到下來診斷問題并提出解決方案，而不應(yīng)該首先想到的是增加資源解決問題。

        以下是每個優(yōu)化法則層級對應(yīng)優(yōu)化效果及成本經(jīng)驗參考：

    接下來，我們針對5種優(yōu)化法則列舉常用的優(yōu)化手段

a: 表的設(shè)計合理化(符合3NF)

b: 優(yōu)化SQL語句（索引）

c: 分表技術(shù)(水平分割、垂直分割)、分區(qū)技術(shù)

d: 讀寫[寫: update/delete/add]分離

e: 存儲過程 [模塊化編程，可以提高速度]

f: 對mysql配置優(yōu)化 [配置最大并發(fā)數(shù), 調(diào)整緩存大小 ]

g: mysql服務(wù)器硬件升級

h: 定時的去清除不需要的數(shù)據(jù),定時進行碎片整理

1、表的設(shè)計合理化(符合3NF)

1NF（第一范式）

    1NF的限定條件如下：（只要數(shù)據(jù)庫是關(guān)系型數(shù)據(jù)庫，就自動的滿足1NF）

         1. 每個列必須有一個唯一的名稱

         2. 行和列的次序無關(guān)緊要

         3. 每一列都必須有單個數(shù)據(jù)類型

         4. 不允許包含相同值的兩行

         5. 每一列都必須包含一個單值 （一個列不能保存多個數(shù)據(jù)值）

         6. 列不能包含重復(fù)的組

第一范式會存在更新、刪除和插入異常。

2NF（第二范式）

    2NF的限定條件如下：（通常我們設(shè)計一個主鍵來實現(xiàn)）

         1. 它符合第一范式

         2. 所有的非鍵值字段均依賴于所有的鍵值字段

第二范式也會存在更新、刪除和插入異常。

3NF（第三范式）

    3NF的限定條件如下：    

         1. 符合2NF

         2. 不包含傳遞相關(guān)性，（即，一個非鍵值字段的值依賴于另一個非鍵值字段的值），不含冗余數(shù)據(jù)

反3NF ：沒有冗余的數(shù)據(jù)庫未必是最好的數(shù)據(jù)庫，有時為了提高運行效率，就必須降低范式標(biāo)準(zhǔn)，適當(dāng)保留冗余數(shù)據(jù)。

具體做法：

在概念數(shù)據(jù)模型設(shè)計時遵守第三范式，降低范式標(biāo)準(zhǔn)的工作放到物理數(shù)據(jù)模型設(shè)計時考慮。降低范式就是增加字段，允許冗余。

2、優(yōu)化SQL語句

（1）迅速的定位執(zhí)行速度慢的語句 

     a 開啟慢查詢

     b 設(shè)置慢查詢時間

     c 啟用慢查詢?nèi)罩?/p>

     d 通過mysqldumoslow工具對慢日志進行分類匯總

（2）分析SQL語句

     a 通過explain分析查詢 

     b 通profiling可以得到更詳細的信息

（3）SQL語句優(yōu)化

     a 創(chuàng)建索引(主鍵索引/唯一索引/全文索引/普通索引)

     b 避免Select * (不查詢多余的列與行)

     c Where中少用NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKE、OR，它們會忽略索引，引起全表掃描

     d 用Where子句替代having子句,having只會在檢索出所有記錄之后才對結(jié)果集進行過濾

     e 使用視圖（經(jīng)常被查詢的列數(shù)據(jù)，并且這些數(shù)據(jù)不被經(jīng)常的修改，刪除）

    數(shù)據(jù)庫索引的原理非常簡單，但在復(fù)雜的表中真正能正確使用索引的人很少，即使是專業(yè)的DBA也不一定能完全做到最優(yōu)。

    索引會大大增加表記錄的DML(INSERT,UPDATE,DELETE)開銷，正確的索引可以讓性能提升100，1000倍以上，不合理的索引也可能會讓性能下降100倍，因此在一個表中創(chuàng)建什么樣的索引需要平衡各種業(yè)務(wù)需求。

    如果我們把一個表的內(nèi)容認(rèn)為是一本字典，那索引就相當(dāng)于字典的目錄，如下圖所示：

    圖中是一個字典按部首+筆劃數(shù)的目錄，相當(dāng)于給字典建了一個按部首+筆劃的組合索引。

一個表中可以建多個索引，就如一本字典可以建多個目錄一樣（按拼音、筆劃、部首等等）。

一個索引也可以由多個字段組成，稱為組合索引，如上圖就是一個按部首+筆劃的組合目錄。

我們一般在什么字段上建索引？

    這是一個非常復(fù)雜的話題，需要對業(yè)務(wù)及數(shù)據(jù)充分分析后再能得出結(jié)果。主鍵及外鍵通常都要有索引，其它需要建索引的字段應(yīng)滿足以下條件：

a 字段出現(xiàn)在查詢條件中，并且查詢條件可以使用索引；

b 語句執(zhí)行頻率高，一天會有幾千次以上；

c 通過字段條件可篩選的記錄集很小，那數(shù)據(jù)篩選比例是多少才適合？

    這個沒有固定值，需要根據(jù)表數(shù)據(jù)量來評估，以下是經(jīng)驗公式，可用于快速評估：

小表(記錄數(shù)小于10000行的表)：篩選比例<10%；

大表：(篩選返回記錄數(shù))<(表總記錄數(shù)*單條記錄長度)/10000/16

單條記錄長度≈字段平均內(nèi)容長度之和+字段數(shù)*2

如何知道SQL是否使用了正確的索引？

       簡單SQL可以根據(jù)索引使用語法規(guī)則判斷，復(fù)雜的SQL不好辦，判斷SQL的響應(yīng)時間是一種策略，但是這會受到數(shù)據(jù)量、主機負(fù)載及緩存等因素的影響，有時數(shù)據(jù)全在緩存里，可能全表訪問的時間比索引訪問時間還少。要準(zhǔn)確知道索引是否正確使用，需要到數(shù)據(jù)庫中查看SQL真實的執(zhí)行計劃，這個話題比較復(fù)雜，詳見SQL執(zhí)行計劃專題介紹。

索引對DML(INSERT,UPDATE,DELETE)附加的開銷有多少？

      這個沒有固定的比例，與每個表記錄的大小及索引字段大小密切相關(guān)，以下是一個普通表測試數(shù)據(jù)，僅供參考：

索引對于Insert性能降低56%

索引對于Update性能降低47%

索引對于Delete性能降低29%

       因此對于寫IO壓力比較大的系統(tǒng)，表的索引需要仔細評估必要性，另外索引也會占用一定的存儲空間。

       切記，性能優(yōu)化是無止境的，當(dāng)性能可以滿足需求時即可，不要過度優(yōu)化。在實際數(shù)據(jù)庫中我們不可能把每個SQL請求的字段都建在索引里，所以這種只通過索引訪問數(shù)據(jù)的方法一般只用于核心應(yīng)用，也就是那種對核心表訪問量最高且查詢字段數(shù)據(jù)量很少的查詢

3、分表技術(shù)(水平分割、垂直分割)、分區(qū)技術(shù)

為什么要分表和分區(qū)？ 

    如果遇到大表的情況下，SQL語句優(yōu)化已經(jīng)無法繼續(xù)優(yōu)化了，我們可以考慮分表和分區(qū)，目的就是減少數(shù)據(jù)庫的負(fù)擔(dān)，提高數(shù)據(jù)庫的效率，通常點來講就是提高表 的增刪改查效率。

什么是分表？

      分表是將一個大表按照一定的規(guī)則分解成多張具有獨立存儲空間的實體表，我們可以稱為子表，每個表都對應(yīng)三個文件，MYD數(shù)據(jù)文件，.MYI索引文件，.frm表結(jié)構(gòu)文件。這些子表可以分布在同一塊磁盤上，也可以在不同的機器上。app讀寫的時候根據(jù)事先定義好的規(guī)則得到對應(yīng)的子表名，然后去操作它。

什么是分區(qū)？

        分區(qū)和分表相似，都是按照規(guī)則分解表。不同在于分表將大表分解為若干個獨立的實體表，而分區(qū)是將數(shù)據(jù)分段劃分在多個位置存放，可以是同一塊磁盤也可以在不同的機器。分區(qū)后，表面上還是一張表，但數(shù)據(jù)散列到多個位置了。app讀寫的時候操作的還是大表名字，db自動去組織分區(qū)的數(shù)據(jù)。

mysql分表和分區(qū)有什么聯(lián)系呢？

（1）都能提高mysql的性能，在高并發(fā)狀態(tài)下都有一個良好的表現(xiàn)。

（2）分表和分區(qū)不矛盾，可以相互配合的，對于那些大訪問量，并且表數(shù)據(jù)比較多的表，我們可以采取分表和分區(qū)結(jié)合的方式，訪問量不大，但是表數(shù)據(jù)很多的表，我們可以采取分區(qū)的方式等。

（3）分表技術(shù)是比較麻煩的，需要手動去創(chuàng)建子表，app服務(wù)端讀寫時候需要計算子表名。采用merge好一些，但也要創(chuàng)建子表和配置子表間的union關(guān)系。

（4）表分區(qū)相對于分表，操作方便，不需要創(chuàng)建子表。

4、讀寫[寫: update/delete/add]分離

        大型網(wǎng)站為了緩解大量的并發(fā)訪問，除了在網(wǎng)站實現(xiàn)分布式負(fù)載均衡，遠遠不夠。如果還是傳統(tǒng)的數(shù)據(jù)結(jié)構(gòu)，或者只是單單靠一臺服務(wù)器扛，如此多的數(shù)據(jù)庫連接操作，數(shù)據(jù)庫必然會崩潰，數(shù)據(jù)丟失的話，后果更是不堪設(shè)想。這時候，我們會考慮如何減少數(shù)據(jù)庫的聯(lián)接，一方面采用優(yōu)秀的代碼框架，進行代碼的優(yōu)化，采用優(yōu)秀的數(shù)據(jù)緩存技術(shù)如：memcached,如果資金豐厚的話，必然會想到架設(shè)服務(wù)器群，來分擔(dān)主數(shù)據(jù)庫的壓力

        因此，一般來說都是通過主從復(fù)制（Master-Slave）的方式來同步數(shù)據(jù)，再通過讀寫分離（MySQL-Proxy，是MySQL官方提供的MySQL中間件服務(wù)）來提升數(shù)據(jù)庫的并發(fā)負(fù)載能力 這樣的方案來進行部署與實施的

實現(xiàn)方式

第一種：php程序上自己做邏輯判斷，寫php代碼的時候，自己在程序上做邏輯判讀寫匹配。select，insert、update、delete做正則匹配，根據(jù)結(jié)果選擇寫服務(wù)器（主服務(wù)器）。如果是select操作則選擇讀服務(wù)器（從服務(wù)器器） mysql_connect('讀寫的區(qū)分')

第二種：MySQL中間件，基本的原理是讓主數(shù)據(jù)庫處理寫操作（insert、update、delete），而從數(shù)據(jù)庫處理查詢操作（select）。而數(shù)據(jù)庫的一致性則通過主從復(fù)制來實現(xiàn)。所以說主從復(fù)制是讀寫分離的基礎(chǔ)。

      下面是一些常用的MySQL中間件的背景介紹

5、存儲過程 

（1）為什么需要存儲過程

     a 數(shù)據(jù)不安全，網(wǎng)絡(luò)傳送SQL代碼，容易被未授權(quán)者截獲

     b 每次提交SQL代碼都要經(jīng)過語法編譯后在執(zhí)行，影響應(yīng)用程序的運行性能

     c 網(wǎng)絡(luò)流量大，對于反復(fù)執(zhí)行的SQL代碼，在網(wǎng)絡(luò)上多次傳送，影響網(wǎng)絡(luò)傳輸量

（2）什么是存儲過程

       存儲過程是SQL語句和控制語句的預(yù)編譯集合，保存在數(shù)據(jù)庫中，可有應(yīng)用程序調(diào)用執(zhí)行，而且允許用戶聲明變量、邏輯控制語句及其他強大的編程功能。包含邏輯控制語句和數(shù)據(jù)操作語句，可以接收參數(shù)、輸出參數(shù)、返回單個或多個結(jié)果值及返回值

（3）使用存儲過程的優(yōu)點

     a 模塊化程序設(shè)計，只需創(chuàng)建一次，以后即可調(diào)用該存儲過程任意次

     b 執(zhí)行速度快，效率高

     c 減少網(wǎng)絡(luò)流量

     d 具有良好的安全性

6、對mysql配置優(yōu)化   

    下面是一些配置的優(yōu)化，具體參數(shù)的解釋就不寫了，請自行查找資料

7、mysql云服務(wù)器硬件升級

（1）磁盤 

MySQL每秒鐘都在進行大量、復(fù)雜的查詢操作，對磁盤的讀寫量可想而知。所以，通常認(rèn)為磁盤I/O是制約MySQL性能的最大因素之一

解決方案： 使用RAID-10 、磁盤陣列設(shè)備SAN 

（2）CPU  對于MySQL應(yīng)用，推薦使用S.M.P.架構(gòu)的多路對稱CPU

（3）內(nèi)存  越大越好

（4）網(wǎng)卡  至少兩個網(wǎng)卡，均為1GBE。通常我會將這兩個nics綁定在一起以提供冗余

8、定時的去清除不需要的數(shù)據(jù),定時進行碎片整理

什么是磁盤碎片？

        簡單的說,刪除數(shù)據(jù)必然會在數(shù)據(jù)文件中造成不連續(xù)的空白空間,而當(dāng)插入數(shù)據(jù)時,這些空白空間則會被利用起來.于是造成了數(shù)據(jù)的存儲位置不連續(xù),以及物理存儲順序與理論上的排序順序不同,這種是數(shù)據(jù)碎片.實際上數(shù)據(jù)碎片分為兩種,一種是單行數(shù)據(jù)碎片,另一種是多行數(shù)據(jù)碎片.前者的意思就是一行數(shù)據(jù),被分成N個片段,存儲在N個位置.后者的就是多行數(shù)據(jù)并未按照邏輯上的順序排列.

        當(dāng)有大量的刪除和插入操作時,必然會產(chǎn)生很多未使用的空白空間,這些空間就是多出來的額外空間.索引也是文件數(shù)據(jù),所以也會產(chǎn)生索引碎片,理由同上,大概就是順序紊亂的問題.Engine 不同,OPTIMIZE 的操作也不一樣的,MyISAM 因為索引和數(shù)據(jù)是分開的,所以 OPTIMIZE 可以整理數(shù)據(jù)文件,并重排索引。這樣不但會浪費空間，并且查詢速度也更慢。

解決方案：

（1）查看表碎片的方法

select ROW_FORMAT,TABLE_ROWS,DATA_LENGTH,INDEX_LENGTH,MAX_DATA_LENGTH,DATA_FREE,ENGINE from TABLES where TABLE_SCHEMA='test_db' and TABLE_NAME='table_name' limit 1;

（2）Innodb存儲引擎清理碎片方法

ALTER TABLE tablename ENGINE=InnoDB

（3）Myisam存儲引擎清理碎片方法

OPTIMIZE TABLE table_name

對于以上關(guān)于如何正確優(yōu)化SQL數(shù)據(jù)庫，如果大家還有更多需要了解的可以持續(xù)關(guān)注我們億速云的行業(yè)推新，如需獲取專業(yè)解答，可在官網(wǎng)聯(lián)系售前售后的，希望該文章可給大家?guī)硪欢ǖ闹R更新。