看完還不會資料庫優化，你來找我！

當有人問你如何對資料庫進行優化時，很多人第一反應想到的就是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`)
);

2. 待優化的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；

3. 原表資料量：

4. 原執行時間

0.21s，執行速度還挺快

5. 原執行計劃

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

初步優化思路：

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倍，

查看優化后的執行計劃

執行計劃顯示從全表掃描優化成了走索引，rows減少，但是此時出現了2個告警，

通過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優化四個角度探討了如何實施優化，提升資料庫性能，但是大家還是要記住一句話，資料庫系統沒有銀彈， 要讓適合的系統，做合適的事情，最后，我是飄渺Jam，一名寫代碼的架構師，做架構的程式員，期待您的轉發與關注，當然也可以添加我的個人微信 jianzh5，咱們一起聊技術！