數(shù)據(jù)庫優(yōu)化之四大心法

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

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

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

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

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

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

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

分布式緩存

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

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

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

“

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

緩存穿透：它是指當(dāng)用戶在查詢一條數(shù)據(jù)的時(shí)候，而此時(shí)數(shù)據(jù)庫和緩存都沒有關(guān)于這條數(shù)據(jù)的任何記錄。這條數(shù)據(jù)在緩存中沒找到就會向數(shù)據(jù)庫請求獲取數(shù)據(jù)。它拿不到數(shù)據(jù)時(shí)，是會一直查詢數(shù)據(jù)庫，這樣會對數(shù)據(jù)庫的訪問造成很大的壓力。

緩存擊穿：一個(gè)熱點(diǎn)key剛好在某個(gè)時(shí)間點(diǎn)失效了，但是這時(shí)候突然來了大量對這個(gè)key的并發(fā)訪問請求，導(dǎo)致大并發(fā)請求直接穿透緩存直達(dá)數(shù)據(jù)庫，瞬間對數(shù)據(jù)庫的訪問壓力增大。

緩存雪崩：某一個(gè)時(shí)間段內(nèi)，緩存集中過期失效，如果這個(gè)時(shí)間段內(nèi)有大量請求，而查詢數(shù)據(jù)量巨大，所有的請求都會達(dá)到存儲層，存儲層的調(diào)用量會暴增，引起數(shù)據(jù)庫壓力過大甚至宕機(jī)。

”

讀寫分離

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

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

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

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

水平切分

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

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

當(dāng)準(zhǔn)備實(shí)施水平切分時(shí)，需要結(jié)合實(shí)際業(yè)務(wù)選取合理的分片鍵（sharding-key），有時(shí)候?yàn)榱私鉀Q非分片鍵查詢問題還需要將數(shù)據(jù)寫到單獨(dú)的查詢組件，如ElasticSearch。

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

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

硬件優(yōu)化

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

吞吐率：單位時(shí)間內(nèi)讀寫的數(shù)據(jù)量

• 機(jī)械硬盤：約100MB/s ～ 200MB/s
• 普通固態(tài)硬盤：200MB/s ～ 500MB/s
• PCIE固態(tài)硬盤：900MB/s ～ 3GB/s

IOPS：每秒IO操作的次數(shù)

• 機(jī)械硬盤：100 ～200
• 普通固態(tài)硬盤：30000 ～ 50000
• PCIE固態(tài)硬盤：數(shù)十萬

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

DB優(yōu)化

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

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

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

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

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

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

Oracle

MySQL

POSTGRES

達(dá)夢數(shù)據(jù)庫

SQL優(yōu)化

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

• 合理使用索引

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

• 使用UNION ALL替代UNION

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

• 避免select * 寫法

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

• JOIN字段建議建立索引

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

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

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

• 避免where 1=1寫法

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

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

執(zhí)行計(jì)劃

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

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

SQL優(yōu)化實(shí)戰(zhàn)

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

在執(zhí)行優(yōu)化之前我們需要先認(rèn)識一下原始表及待優(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（查詢當(dāng)前用戶在當(dāng)前時(shí)間前后10個(gè)小時(shí)的訂單情況，并根據(jù)訂單創(chuàng)建時(shí)間升序排列）

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í)行時(shí)間

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

5. 原執(zhí)行計(jì)劃

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

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

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

初步優(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í)行時(shí)間

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

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

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

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

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

alter table a modify "gmt_create" datetime DEFAULT NULL;

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

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

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

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

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

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

小結(jié)

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