我們常說的資料庫最佳化，可以從哪些維度入手？

當有人問你如何對資料庫進行最佳化時，很多人第一反應想到的就是SQL最佳化，如何建立索引，如何改寫SQL，他們把資料庫最佳化與SQL最佳化劃上了等號。

當然這不能算是完全錯誤的回答，只不過思考的角度稍微片面了些，太“程式設計師思維”化了，沒有站在更高層次來思考回答。那今天我們就將視角拔高，站在架構的角度來聊聊這一問題，資料庫最佳化可以從哪些維度入手？

正如上圖所示，資料庫最佳化可以從架構最佳化，硬體最佳化，DB最佳化，SQL最佳化四個維度入手。

此上而下，位置越靠前最佳化越明顯，對資料庫的效能提升越高。我們常說的SQL最佳化反而是對效能提高最小的最佳化。

接下來我們再看看每種最佳化該如何實施。

架構最佳化

一般來說在高併發的場景下對架構層進行最佳化其效果最為明顯，常見的最佳化手段有：分散式快取，讀寫分離，分庫分表等，每種最佳化手段又適用於不同的應用場景。

分散式快取

有句老話說的好，效能不夠，快取來湊。當需要在架構層進行最佳化時我們第一時間就會想到快取這個神器，在應用與資料庫之間增加一個快取服務，如Redis或Memcache。

當接收到查詢請求後，我們先查詢快取，判斷快取中是否有資料，有資料就直接返回給應用，如若沒有再查詢資料庫，並載入到快取中，這樣就大大減少了對資料庫的訪問次數，自然而然也提高了資料庫效能。

不過需要注意的是，引入分散式快取後系統需要考慮如何應對快取穿透、快取擊穿和快取雪崩的問題。

“

簡單理解一下 快取穿透、快取擊穿 和 快取雪崩

快取穿透：它是指當使用者在查詢一條資料的時候，而此時資料庫和快取都沒有關於這條資料的任何記錄。這條資料在快取中沒找到就會向資料庫請求獲取資料。它拿不到資料時，是會一直查詢資料庫，這樣會對資料庫的訪問造成很大的壓力。

快取擊穿：一個熱點key剛好在某個時間點失效了，但是這時候突然來了大量對這個key的併發訪問請求，導致大併發請求直接穿透快取直達資料庫，瞬間對資料庫的訪問壓力增大。

快取雪崩：某一個時間段內，快取集中過期失效，如果這個時間段內有大量請求，而查詢資料量巨大，所有的請求都會達到儲存層，儲存層的呼叫量會暴增，引起資料庫壓力過大甚至當機。

”

讀寫分離

一主多從，讀寫分離，主動同步，是一種常見的資料庫架構最佳化手段。

一般來說當你的應用是讀多寫少，資料庫扛不住讀壓力的時候，採用讀寫分離，透過增加從庫數量可以線性提升系統讀效能。

主庫，提供資料庫寫服務；從庫，提供資料庫讀能力；主從之間，透過binlog同步資料。

當準備實施讀寫分離時，為了保證高可用，需要實現故障的自動轉移，主從架構會有潛在主從不一致性問題。

水平切分

水平切分，也是一種常見的資料庫架構最佳化手段。

當你的應用業務資料量很大，單庫容量成為效能瓶頸後，採用水平切分，可以降低資料庫單庫容量，提升資料庫寫效能。

當準備實施水平切分時，需要結合實際業務選取合理的分片鍵（sharding-key），有時候為了解決非分片鍵查詢問題還需要將資料寫到單獨的查詢元件，如ElasticSearch。

架構最佳化小結

1. 讀寫分離主要是用於解決 “資料庫讀效能問題”
2. 水平切分主要是用於解決“資料庫資料量大的問題”
3. 分散式快取架構可能比讀寫分離更適用於高併發、大資料量大場景。

硬體最佳化

我們使用資料庫，不管是讀操作還是寫操作，最終都是要訪問磁碟，所以說磁碟的效能決定了資料庫的效能。一塊PCIE固態硬碟的效能是普通機械硬碟的幾十倍不止。這裡我們可以從吞吐率、IOPS兩個維度看一下機械硬碟、普通固態硬碟、PCIE固態硬碟之間的效能指標。

吞吐率：單位時間內讀寫的資料量

• 機械硬碟：約100MB/s ～ 200MB/s
• 普通固態硬碟：200MB/s ～ 500MB/s
• PCIE固態硬碟：900MB/s ～ 3GB/s

IOPS：每秒IO操作的次數

• 機械硬碟：100 ～200
• 普通固態硬碟：30000 ～ 50000
• PCIE固態硬碟：數十萬

透過上面的資料可以很直觀的看到不同規格的硬碟之間的效能差距非常大，當然效能更好的硬碟價格會更貴，在資金充足並且迫切需要提升資料庫效能時，嘗試更換一下資料庫的硬碟不失為一個非常好的舉措，你之前遇到SQL執行緩慢問題在你更換硬碟後很可能將不再是問題。

DB最佳化

SQL執行慢有時候不一定完全是SQL問題，手動安裝一臺資料庫而不做任何引數調整，再怎麼最佳化SQL都無法讓其效能最大化。要讓一臺資料庫例項完全發揮其效能，首先我們就得先最佳化資料庫的例項引數。

資料庫例項引數最佳化遵循三句口訣：日誌不能小、快取足夠大、連線要夠用。

資料庫事務提交後需要將事務對資料頁的修改刷（ fsync）到磁碟上，才能保證資料的永續性。這個刷盤，是一個隨機寫，效能較低，如果每次事務提交都要刷盤，會極大影響資料庫的效能。資料庫在架構設計中都會採用如下兩個最佳化手法：

• 先將事務寫到日誌檔案RedoLog（WAL），將隨機寫最佳化成順序寫
• 加一層快取結構Buffer，將單次寫最佳化成順序寫

所以日誌跟快取對資料庫例項尤其重要。而連線如果不夠用，資料庫會直接丟擲異常，系統無法訪問。

接下來我們以Oracle、MySQL（InnoDB）、POSTGRES、達夢為例，看看每種資料庫的引數該如何配置。

Oracle

MySQL

POSTGRES

達夢資料庫

SQL最佳化

SQL最佳化很容易理解，就是透過給查詢欄位新增索引或者改寫SQL提高其執行效率，一般而言，SQL編寫有以下幾個通用的技巧：

• 合理使用索引

索引少了查詢慢；索引多了佔用空間大，執行增刪改語句的時候需要動態維護索引，影響效能 選擇率高（重複值少）且被where頻繁引用需要建立B樹索引；一般join列需要建立索引；複雜文件型別查詢採用全文索引效率更好；索引的建立要在查詢和DML效能之間取得平衡；複合索引建立時要注意基於非前導列查詢的情況

• 使用UNION ALL替代UNION

UNION ALL的執行效率比UNION高，UNION執行時需要排重；UNION需要對資料進行排序

• 避免select * 寫法

執行SQL時最佳化器需要將 * 轉成具體的列；每次查詢都要回表，不能走覆蓋索引。

• JOIN欄位建議建立索引

一般JOIN欄位都提前加上索引

• 避免複雜SQL語句

提升可閱讀性；避免慢查詢的機率；可以轉換成多個短查詢，用業務端處理

• 避免where 1=1寫法

• 避免order by rand()類似寫法

RAND()導致資料列被多次掃描

執行計劃

要想最佳化SQL必須要會看執行計劃，執行計劃會告訴你哪些地方效率低，哪裡可以需要最佳化。我們以MYSQL為例，來認識一下執行計劃。

透過explain sql 可以檢視執行計劃，如：

SQL最佳化實戰

這裡為大家準備了一套SQL最佳化的綜合實戰，一步一步帶你走一遍完整SQL最佳化的過程。

在執行最佳化之前我們需要先認識一下原始表及待最佳化的SQL。

1. 原資料庫表結構

CREATE TABLE `a`
(
    `id`          int(11) NOT NULL AUTO_INCREMENT,
    `seller_id`   bigint(20)                                       DEFAULT NULL,
    `seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
    `gmt_create`  varchar(30)                                      DEFAULT NULL,
    PRIMARY KEY (`id`)
);
CREATE TABLE `b`
(
    `id`          int(11) NOT NULL AUTO_INCREMENT,
    `seller_name` varchar(100) DEFAULT NULL,
    `user_id`     varchar(50)  DEFAULT NULL,
    `user_name`   varchar(100) DEFAULT NULL,
    `sales`       bigint(20)   DEFAULT NULL,
    `gmt_create`  varchar(30)  DEFAULT NULL,
    PRIMARY KEY (`id`)
);
CREATE TABLE `c`
(
    `id`         int(11) NOT NULL AUTO_INCREMENT,
    `user_id`    varchar(50)  DEFAULT NULL,
    `order_id`   varchar(100) DEFAULT NULL,
    `state`      bigint(20)   DEFAULT NULL,
    `gmt_create` varchar(30)  DEFAULT NULL,
    PRIMARY KEY (`id`)
);

1. 待最佳化的SQL（查詢當前使用者在當前時間前後10個小時的訂單情況，並根據訂單建立時間升序排列）

select a.seller_id,
       a.seller_name,
       b.user_name,
       c.state
from a,
     b,
     c
where a.seller_name = b.seller_name
  and b.user_id = c.user_id
  and c.user_id = 17
  and a.gmt_create
    BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE)
    AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)
order by a.gmt_create；

1. 原表資料量：

1. 原執行時間

0.21s，執行速度還挺快

1. 原執行計劃

真是糟糕的執行計劃。(全表掃描，沒有索引；臨時表；排序)

初步最佳化思路：

1. SQL中 where條件欄位型別要跟表結構一致，表中user_id 為varchar(50)型別，實際SQL用的int型別，存在隱式轉換，也未新增索引。將b和c表user_id 欄位改成int型別。
2. 因存在b表和c表關聯，將b和c表user_id建立索引
3. 因存在a表和b表關聯，將a和b表seller_name欄位建立索引
4. 利用複合索引消除臨時表和排序

初步最佳化SQL

alter table b modify `user_id` int(10) DEFAULT NULL;
alter table c modify `user_id` int(10) DEFAULT NULL;
alter table c add index `idx_user_id`(`user_id`);
alter table b add index `idx_user_id_sell_name`(`user_id`,`seller_name`);
alter table a add index `idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);

檢視最佳化後的執行時間

透過執行計劃可以看到，執行時間從0.21s最佳化成了0.01s，執行時間近乎縮短20倍。

檢視最佳化後的執行計劃

透過show warning語句 檢視告警資訊

提示gmt_crteate 的格式不對，mysql進行了隱式轉換導致不能使用索引。

繼續最佳化，修改gmtc-create的格式

alter table a modify "gmt_create" datetime DEFAULT NULL;

再次檢視執行時間

再次檢視執行計劃

至此，我們的最佳化過程結束，結果非常完美。

SQL最佳化小結

這裡給大家總結一下SQL最佳化的套路：

1. 檢視執行計劃 explain sql
2. 如果有告警資訊，檢視告警資訊 show warnings;
3. 檢視SQL涉及的表結構和索引資訊
4. 根據執行計劃，思考可能的最佳化點
5. 按照可能的最佳化點執行表結構變更、增加索引、SQL改寫等操作
6. 檢視最佳化後的執行時間和執行計劃
7. 如果最佳化效果不明顯，重複第四步操作

小結

我們今天分別從架構最佳化、硬體最佳化、DB最佳化、SQL最佳化四個角度探討了如何實施最佳化，提升資料庫效能。但是大家還是要記住一句話，資料庫系統沒有銀彈， 要讓適合的系統，做合適的事情。