正如上圖所示，資料庫優化可以從架構優化，硬體優化，DB優化，SQL優化四個次元入手，此上而下，位置越靠前優化越明顯，對資料庫的性能提升越高。我們常說的SQL優化反而是對性能提高最小的優化。
接下來我們再看看每種優化該如何實施。
架構優化
一般來說在高并發的場景下對架構層進行優化其效果最為明顯，常見的優化手段有：分布式緩存，讀寫分離，分庫分表等，每種優化手段又适用于不同的應用場景。
分布式緩存
有句老話說的好，性能不夠，緩存來湊。當需要在架構層進行優化時我們第一時間就會想到緩存這個神器，在應用與資料庫之間增加一個緩存服務，如Redis或Memcache。
當接收到查詢請求後，我們先查詢緩存，判斷緩存中是否有資料，有資料就直接傳回給應用，如若沒有再查詢資料庫，并加載到緩存中，這樣就大大減少了對資料庫的通路次數，自然而然也提高了資料庫性能。
不過需要注意的是，引入分布式緩存後系統需要考慮如何應對緩存穿透、緩存擊穿和緩存雪崩的問題。
簡單了解一下 緩存穿透、緩存擊穿 和 緩存雪崩
緩存穿透：它是指當使用者在查詢一條資料的時候，而此時資料庫和緩存都沒有關于這條資料的任何記錄。這條資料在緩存中沒找到就會向資料庫請求擷取資料。它拿不到資料時，是會一直查詢資料庫，這樣會對資料庫的通路造成很大的壓力。
緩存擊穿：一個熱點key剛好在某個時間點失效了，但是這時候突然來了大量對這個key的并發通路請求，導緻大并發請求直接穿透緩存直達資料庫，瞬間對資料庫的通路壓力增大。
緩存雪崩：某一個時間段内，緩存集中過期失效，如果這個時間段内有大量請求，而查詢資料量巨大，所有的請求都會達到存儲層，存儲層的調用量會暴增，引起資料庫壓力過大甚至當機。
讀寫分離
一主多從，讀寫分離，主動同步，是一種常見的資料庫架構優化手段。
一般來說當你的應用是讀多寫少，資料庫扛不住讀壓力的時候，采用讀寫分離，通過增加從庫數量可以線性提升系統讀性能。
主庫，提供資料庫寫服務；從庫，提供資料庫讀能力；主從之間，通過binlog同步資料。
當準備實施讀寫分離時，為了保證高可用，需要實作故障的自動轉移，主從架構會有潛在主從不一緻性問題。
水準切分
水準切分，也是一種常見的資料庫架構優化手段。
當你的應用業務資料量很大，單庫容量成為性能瓶頸後，采用水準切分，可以降低資料庫單庫容量，提升資料庫寫性能。
當準備實施水準切分時，需要結合實際業務選取合理的分片鍵（sharding-key），有時候為了解決非分片鍵查詢問題還需要将資料寫到單獨的查詢元件，如ElasticSearch。
架構優化小結
讀寫分離主要是用于解決 “資料庫讀性能問題”
水準切分主要是用于解決“資料庫資料量大的問題”
分布式緩存架構可能比讀寫分離更适用于高并發、大資料量大場景。
硬體優化
我們使用資料庫，不管是讀操作還是寫操作，最終都是要通路磁盤，是以說磁盤的性能決定了資料庫的性能。一塊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
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。
- 原資料庫表結構
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`)
); - 待優化的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； - 原表資料量：
- 原執行時間
0.21s，執行速度還挺快
- 原執行計劃
真是糟糕的執行計劃。(全表掃描，沒有索引；臨時表；排序)
初步優化思路：
SQL中 where條件字段類型要跟表結構一緻，表中user_id 為varchar(50)類型，實際SQL用的int類型，存在隐式轉換，也未添加索引。将b和c表user_id 字段改成int類型。
因存在b表和c表關聯，将b和c表user_id建立索引
因存在a表和b表關聯，将a和b表seller_name字段建立索引
利用複合索引消除臨時表和排序
初步優化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;
• 1 再次檢視執行時間
再次檢視執行計劃
至此，我們的優化過程結束，結果非常完美。
SQL優化小結
這裡給大家總結一下SQL優化的套路：
檢視執行計劃 explain sql
如果有告警資訊，檢視告警資訊 show warnings;
檢視SQL涉及的表結構和索引資訊
根據執行計劃，思考可能的優化點
按照可能的優化點執行表結構變更、增加索引、SQL改寫等操作
檢視優化後的執行時間和執行計劃
如果優化效果不明顯，重複第四步操作
小結
我們今天分别從架構優化、硬體優化、DB優化、SQL優化四個角度探讨了如何實施優化，提升資料庫性能。但是大家還是要記住一句話，資料庫系統沒有銀彈， 要讓适合的系統，做合适的事情。最後，我是飄渺Jam，一名寫代碼的架構師，做架構的程式員，期待您的轉發與關注，當然也可以添加我的個人微信 jianzh5，咱們一起聊技術！