前言

當有人問你如何對數(shù)據(jù)庫進行優(yōu)化時，很多人第一反應(yīng)想到的就是SQL優(yōu)化，如何創(chuàng)建索引，如何改寫SQL，他們把數(shù)據(jù)庫優(yōu)化與SQL優(yōu)化劃上了等號。

當然這不能算是完全錯誤的回答，只不過思考的角度稍微片面了些，太“程序員思維”化了，沒有站在更高層次來思考回答。那今天我們就將視角拔高，站在架構(gòu)的角度來聊聊這一問題，數(shù)據(jù)庫優(yōu)化可以從哪些維度入手?

正如上圖所示，數(shù)據(jù)庫優(yōu)化可以從架構(gòu)優(yōu)化，硬件優(yōu)化，DB優(yōu)化，SQL優(yōu)化四個維度入手。

此上而下，位置越靠前優(yōu)化越明顯，對數(shù)據(jù)庫的性能提升越高。我們常說的SQL優(yōu)化反而是對性能提高最小的優(yōu)化。

接下來我們再看看每種優(yōu)化該如何實施。

一、架構(gòu)優(yōu)化

一般來說在高并發(fā)的場景下對架構(gòu)層進行優(yōu)化其效果最為明顯，常見的優(yōu)化手段有：分布式緩存，讀寫分離，分庫分表等，每種優(yōu)化手段又適用于不同的應(yīng)用場景。

1、分布式緩存

有句老話說的好，性能不夠，緩存來湊。當需要在架構(gòu)層進行優(yōu)化時我們第一時間就會想到緩存這個神器，在應(yīng)用與數(shù)據(jù)庫之間增加一個緩存服務(wù)，如Redis或Memcache。

當接收到查詢請求后，我們先查詢緩存，判斷緩存中是否有數(shù)據(jù)，有數(shù)據(jù)就直接返回給應(yīng)用，如若沒有再查詢數(shù)據(jù)庫，并加載到緩存中，這樣就大大減少了對數(shù)據(jù)庫的訪問次數(shù)，自然而然也提高了數(shù)據(jù)庫性能。

不過需要注意的是，引入分布式緩存后系統(tǒng)需要考慮如何應(yīng)對緩存穿透、緩存擊穿和緩存雪崩的問題。

簡單理解一下 緩存穿透、緩存擊穿 和 緩存雪崩

• 緩存穿透：它是指當用戶在查詢一條數(shù)據(jù)的時候，而此時數(shù)據(jù)庫和緩存都沒有關(guān)于這條數(shù)據(jù)的任何記錄。這條數(shù)據(jù)在緩存中沒找到就會向數(shù)據(jù)庫請求獲取數(shù)據(jù)。它拿不到數(shù)據(jù)時，是會一直查詢數(shù)據(jù)庫，這樣會對數(shù)據(jù)庫的訪問造成很大的壓力。
• 緩存擊穿：一個熱點key剛好在某個時間點失效了，但是這時候突然來了大量對這個key的并發(fā)訪問請求，導(dǎo)致大并發(fā)請求直接穿透緩存直達數(shù)據(jù)庫，瞬間對數(shù)據(jù)庫的訪問壓力增大。
• 緩存雪崩：某一個時間段內(nèi)，緩存集中過期失效，如果這個時間段內(nèi)有大量請求，而查詢數(shù)據(jù)量巨大，所有的請求都會達到存儲層，存儲層的調(diào)用量會暴增，引起數(shù)據(jù)庫壓力過大甚至宕機。

2、讀寫分離

一主多從，讀寫分離，主動同步，是一種常見的數(shù)據(jù)庫架構(gòu)優(yōu)化手段。

一般來說當你的應(yīng)用是讀多寫少，數(shù)據(jù)庫扛不住讀壓力的時候，采用讀寫分離，通過增加從庫數(shù)量可以線性提升系統(tǒng)讀性能。

主庫，提供數(shù)據(jù)庫寫服務(wù);從庫，提供數(shù)據(jù)庫讀能力;主從之間，通過binlog同步數(shù)據(jù)。

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

3、水平切分

水平切分，也是一種常見的數(shù)據(jù)庫架構(gòu)優(yōu)化手段。

當你的應(yīng)用業(yè)務(wù)數(shù)據(jù)量很大，單庫容量成為性能瓶頸后，采用水平切分，可以降低數(shù)據(jù)庫單庫容量，提升數(shù)據(jù)庫寫性能。

當準備實施水平切分時，需要結(jié)合實際業(yè)務(wù)選取合理的分片鍵(sharding-key)，有時候為了解決非分片鍵查詢問題還需要將數(shù)據(jù)寫到單獨的查詢組件，如ElasticSearch。

4、架構(gòu)優(yōu)化小結(jié)

• 讀寫分離主要是用于解決 “數(shù)據(jù)庫讀性能問題”
• 水平切分主要是用于解決“數(shù)據(jù)庫數(shù)據(jù)量大的問題”
• 分布式緩存架構(gòu)可能比讀寫分離更適用于高并發(fā)、大數(shù)據(jù)量大場景。

二、硬件優(yōu)化

我們使用數(shù)據(jù)庫，不管是讀操作還是寫操作，最終都是要訪問磁盤，所以說磁盤的性能決定了數(shù)據(jù)庫的性能。一塊PCIE固態(tài)硬盤的性能是普通機械硬盤的幾十倍不止。這里我們可以從吞吐率、IOPS兩個維度看一下機械硬盤、普通固態(tài)硬盤、PCIE固態(tài)硬盤之間的性能指標。

• 吞吐率：單位時間內(nèi)讀寫的數(shù)據(jù)量
• 機械硬盤：約100MB/s ～ 200MB/s
• 普通固態(tài)硬盤：200MB/s ～ 500MB/s
• PCIE固態(tài)硬盤：900MB/s ～ 3GB/s
• IOPS：每秒IO操作的次數(shù)
• 機械硬盤：100 ～200
• 普通固態(tài)硬盤：30000 ～ 50000
• PCIE固態(tài)硬盤：數(shù)十萬

通過上面的數(shù)據(jù)可以很直觀的看到不同規(guī)格的硬盤之間的性能差距非常大，當然性能更好的硬盤價格會更貴，在資金充足并且迫切需要提升數(shù)據(jù)庫性能時，嘗試更換一下數(shù)據(jù)庫的硬盤不失為一個非常好的舉措，你之前遇到SQL執(zhí)行緩慢問題在你更換硬盤后很可能將不再是問題。

三、DB優(yōu)化

SQL執(zhí)行慢有時候不一定完全是SQL問題，手動安裝一臺數(shù)據(jù)庫而不做任何參數(shù)調(diào)整，再怎么優(yōu)化SQL都無法讓其性能最大化。要讓一臺數(shù)據(jù)庫實例完全發(fā)揮其性能，首先我們就得先優(yōu)化數(shù)據(jù)庫的實例參數(shù)。

數(shù)據(jù)庫實例參數(shù)優(yōu)化遵循三句口訣：日志不能小、緩存足夠大、連接要夠用。

數(shù)據(jù)庫事務(wù)提交后需要將事務(wù)對數(shù)據(jù)頁的修改刷( fsync)到磁盤上，才能保證數(shù)據(jù)的持久性。這個刷盤，是一個隨機寫，性能較低，如果每次事務(wù)提交都要刷盤，會極大影響數(shù)據(jù)庫的性能。數(shù)據(jù)庫在架構(gòu)設(shè)計中都會采用如下兩個優(yōu)化手法：

• 先將事務(wù)寫到日志文件RedoLog(WAL)，將隨機寫優(yōu)化成順序?qū)?/li>
• 加一層緩存結(jié)構(gòu)Buffer，將單次寫優(yōu)化成順序?qū)?/li>

所以日志跟緩存對數(shù)據(jù)庫實例尤其重要。而連接如果不夠用，數(shù)據(jù)庫會直接拋出異常，系統(tǒng)無法訪問。

接下來我們以O(shè)racle、MySQL(InnoDB)、POSTGRES、達夢為例，看看每種數(shù)據(jù)庫的參數(shù)該如何配置。

1、Oracle

2、MySQL

3、POSTGRES

4、達夢數(shù)據(jù)庫

四、SQL優(yōu)化

SQL優(yōu)化很容易理解，就是通過給查詢字段添加索引或者改寫SQL提高其執(zhí)行效率，一般而言，SQL編寫有以下幾個通用的技巧：

1)合理使用索引

索引少了查詢慢;索引多了占用空間大，執(zhí)行增刪改語句的時候需要動態(tài)維護索引，影響性能 選擇率高(重復(fù)值少)且被where頻繁引用需要建立B樹索引;一般join列需要建立索引;復(fù)雜文檔類型查詢采用全文索引效率更好;索引的建立要在查詢和DML性能之間取得平衡;復(fù)合索引創(chuàng)建時要注意基于非前導(dǎo)列查詢的情況

2)使用UNION ALL替代UNION

UNION ALL的執(zhí)行效率比UNION高，UNION執(zhí)行時需要排重;UNION需要對數(shù)據(jù)進行排序

3)避免select * 寫法

執(zhí)行SQL時優(yōu)化器需要將 * 轉(zhuǎn)成具體的列;每次查詢都要回表，不能走覆蓋索引。

4)JOIN字段建議建立索引

• 一般JOIN字段都提前加上索引

5)避免復(fù)雜SQL語句

• 提升可閱讀性;避免慢查詢的概率;可以轉(zhuǎn)換成多個短查詢，用業(yè)務(wù)端處理

6)避免where 1=1寫法

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

• RAND()導(dǎo)致數(shù)據(jù)列被多次掃描

1、執(zhí)行計劃

要想優(yōu)化SQL必須要會看執(zhí)行計劃，執(zhí)行計劃會告訴你哪些地方效率低，哪里可以需要優(yōu)化。我們以MYSQL為例，來認識一下執(zhí)行計劃。

通過explain sql 可以查看執(zhí)行計劃，如：

2、SQL優(yōu)化實戰(zhàn)

這里為大家準備了一套SQL優(yōu)化的綜合實戰(zhàn)，一步一步帶你走一遍完整SQL優(yōu)化的過程。

在執(zhí)行優(yōu)化之前我們需要先認識一下原始表及待優(yōu)化的SQL。

1)原數(shù)據(jù)庫表結(jié)構(gòu)

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)待優(yōu)化的SQL(查詢當前用戶在當前時間前后10個小時的訂單情況，并根據(jù)訂單創(chuàng)建時間升序排列)

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)原表數(shù)據(jù)量：

4)原執(zhí)行時間

0.21s，執(zhí)行速度還挺快

5)原執(zhí)行計劃

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

①初步優(yōu)化思路：

• SQL中 where條件字段類型要跟表結(jié)構(gòu)一致，表中user_id 為varchar(50)類型，實際SQL用的int類型，存在隱式轉(zhuǎn)換，也未添加索引。將b和c表user_id 字段改成int類型。
• 因存在b表和c表關(guān)聯(lián)，將b和c表user_id創(chuàng)建索引
• 因存在a表和b表關(guān)聯(lián)，將a和b表seller_name字段創(chuàng)建索引
• 利用復(fù)合索引消除臨時表和排序

②初步優(yōu)化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`); 

③查看優(yōu)化后的執(zhí)行時間

通過執(zhí)行計劃可以看到，執(zhí)行時間從0.21s優(yōu)化成了0.01s，執(zhí)行時間近乎縮短20倍。

④查看優(yōu)化后的執(zhí)行計劃

執(zhí)行計劃顯示從全表掃描優(yōu)化成了走索引，rows減少，但是此時出現(xiàn)了2個告警。

⑤通過show warning語句 查看告警信息

提示gmt_crteate 的格式不對，mysql進行了隱式轉(zhuǎn)換導(dǎo)致不能使用索引。

⑥繼續(xù)優(yōu)化，修改gmtc-create的格式

alter table a modify "gmt_create" datetime DEFAULT NULL; 

⑦再次查看執(zhí)行時間

⑧再次查看執(zhí)行計劃

至此，我們的優(yōu)化過程結(jié)束，結(jié)果非常完美。

3、SQL優(yōu)化小結(jié)

這里給大家總結(jié)一下SQL優(yōu)化的套路：

• 查看執(zhí)行計劃 explain sql
• 如果有告警信息，查看告警信息 show warnings;
• 查看SQL涉及的表結(jié)構(gòu)和索引信息
• 根據(jù)執(zhí)行計劃，思考可能的優(yōu)化點
• 按照可能的優(yōu)化點執(zhí)行表結(jié)構(gòu)變更、增加索引、SQL改寫等操作
• 查看優(yōu)化后的執(zhí)行時間和執(zhí)行計劃
• 如果優(yōu)化效果不明顯，重復(fù)第四步操作

小結(jié)

我們今天分別從架構(gòu)優(yōu)化、硬件優(yōu)化、DB優(yōu)化、SQL優(yōu)化四個角度探討了如何實施優(yōu)化，提升數(shù)據(jù)庫性能。但是大家還是要記住一句話，數(shù)據(jù)庫系統(tǒng)沒有銀彈， 要讓適合的系統(tǒng)，做合適的事情。