如何正確優化SQL數據庫

    下文內容主要給大家帶來如何正確優化SQL數據庫，所講到的知識，與書籍略有不同，都是億速云專業技術人員在與用戶接觸過程中，總結出來的，具有一定的經驗分享價值，希望給廣大讀者帶來幫助。

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

     從圖上可以看到基本上每種設備都有兩個指標：

延時（響應時間）：表示硬件的突發處理能力；

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

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

CPU——Cache(L1-L2-L3)——內存——SSD硬盤——網絡——硬盤

     根據數據庫知識，我們可以列出每種硬件主要的工作內容：

CPU及內存：緩存數據訪問、比較、排序、事務檢測、SQL解析、函數或邏輯運算；

網絡：結果數據傳輸、SQL請求、遠程數據庫訪問（dblink）；

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

     根據當前計算機硬件的基本性能指標及其在數據庫中主要操作內容，可以整理出如下圖所示的性能基本優化法則：

這個優化法則歸納為5個層次：

1、  減少數據訪問（減少磁盤訪問）

2、  返回更少數據（減少網絡傳輸或磁盤訪問）

3、  減少交互次數（減少網絡傳輸）

4、  減少云服務器CPU開銷（減少CPU及內存開銷）

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

        由于每一層優化法則都是解決其對應硬件的性能問題，所以帶來的性能提升比例也不一樣。傳統數據庫系統設計是也是盡可能對低速設備提供優化方法，因此針對低速設備問題的可優化手段也更多，優化成本也更低。我們任何一個SQL的性能優化都應該按這個規則由上到下來診斷問題并提出解決方案，而不應該首先想到的是增加資源解決問題。

        以下是每個優化法則層級對應優化效果及成本經驗參考：

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

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

b: 優化SQL語句（索引）

c: 分表技術(水平分割、垂直分割)、分區技術

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

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

f: 對mysql配置優化 [配置最大并發數, 調整緩存大小 ]

g: mysql服務器硬件升級

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

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

1NF（第一范式）

    1NF的限定條件如下：（只要數據庫是關系型數據庫，就自動的滿足1NF）

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

         2. 行和列的次序無關緊要

         3. 每一列都必須有單個數據類型

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

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

         6. 列不能包含重復的組

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

2NF（第二范式）

    2NF的限定條件如下：（通常我們設計一個主鍵來實現）

         1. 它符合第一范式

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

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

3NF（第三范式）

    3NF的限定條件如下：    

         1. 符合2NF

         2. 不包含傳遞相關性，（即，一個非鍵值字段的值依賴于另一個非鍵值字段的值），不含冗余數據

反3NF ：沒有冗余的數據庫未必是最好的數據庫，有時為了提高運行效率，就必須降低范式標準，適當保留冗余數據。

具體做法：

在概念數據模型設計時遵守第三范式，降低范式標準的工作放到物理數據模型設計時考慮。降低范式就是增加字段，允許冗余。

2、優化SQL語句

（1）迅速的定位執行速度慢的語句 

     a 開啟慢查詢

     b 設置慢查詢時間

     c 啟用慢查詢日志

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

（2）分析SQL語句

     a 通過explain分析查詢 

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

（3）SQL語句優化

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

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

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

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

     e 使用視圖（經常被查詢的列數據，并且這些數據不被經常的修改，刪除）

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

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

    如果我們把一個表的內容認為是一本字典，那索引就相當于字典的目錄，如下圖所示：

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

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

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

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

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

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

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

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

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

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

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

單條記錄長度≈字段平均內容長度之和+字段數*2

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

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

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

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

索引對于Insert性能降低56%

索引對于Update性能降低47%

索引對于Delete性能降低29%

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

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

3、分表技術(水平分割、垂直分割)、分區技術

為什么要分表和分區？ 

    如果遇到大表的情況下，SQL語句優化已經無法繼續優化了，我們可以考慮分表和分區，目的就是減少數據庫的負擔，提高數據庫的效率，通常點來講就是提高表 的增刪改查效率。

什么是分表？

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

什么是分區？

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

mysql分表和分區有什么聯系呢？

（1）都能提高mysql的性能，在高并發狀態下都有一個良好的表現。

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

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

（4）表分區相對于分表，操作方便，不需要創建子表。

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

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

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

實現方式

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

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

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

5、存儲過程 

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

     a 數據不安全，網絡傳送SQL代碼，容易被未授權者截獲

     b 每次提交SQL代碼都要經過語法編譯后在執行，影響應用程序的運行性能

     c 網絡流量大，對于反復執行的SQL代碼，在網絡上多次傳送，影響網絡傳輸量

（2）什么是存儲過程

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

（3）使用存儲過程的優點

     a 模塊化程序設計，只需創建一次，以后即可調用該存儲過程任意次

     b 執行速度快，效率高

     c 減少網絡流量

     d 具有良好的安全性

6、對mysql配置優化   

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

7、mysql云服務器硬件升級

（1）磁盤 

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

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

（2）CPU  對于MySQL應用，推薦使用S.M.P.架構的多路對稱CPU

（3）內存  越大越好

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

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

什么是磁盤碎片？

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

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

解決方案：

（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

對于以上關于如何正確優化SQL數據庫，如果大家還有更多需要了解的可以持續關注我們億速云的行業推新，如需獲取專業解答，可在官網聯系售前售后的，希望該文章可給大家帶來一定的知識更新。