不止SQL優化!數據庫還有哪些優化大法?
前言
當有人問你如何對數據庫進行優化時,很多人第一反應想到的就是SQL優化,如何創建索引,如何改寫SQL,他們把數據庫優化與SQL優化劃上了等號。
當然這不能算是完全錯誤的回答,只不過思考的角度稍微片面了些,太“程序員思維”化了,沒有站在更高層次來思考回答。那今天我們就將視角拔高,站在架構的角度來聊聊這一問題,數據庫優化可以從哪些維度入手?
正如上圖所示,數據庫優化可以從架構優化,硬件優化,DB優化,SQL優化四個維度入手。
此上而下,位置越靠前優化越明顯,對數據庫的性能提升越高。我們常說的SQL優化反而是對性能提高最小的優化。
接下來我們再看看每種優化該如何實施。
一、架構優化
一般來說在高并發的場景下對架構層進行優化其效果最為明顯,常見的優化手段有:分布式緩存,讀寫分離,分庫分表等,每種優化手段又適用于不同的應用場景。
1、分布式緩存
有句老話說的好,性能不夠,緩存來湊。當需要在架構層進行優化時我們第一時間就會想到緩存這個神器,在應用與數據庫之間增加一個緩存服務,如Redis或Memcache。
當接收到查詢請求后,我們先查詢緩存,判斷緩存中是否有數據,有數據就直接返回給應用,如若沒有再查詢數據庫,并加載到緩存中,這樣就大大減少了對數據庫的訪問次數,自然而然也提高了數據庫性能。
不過需要注意的是,引入分布式緩存后系統需要考慮如何應對緩存穿透、緩存擊穿和緩存雪崩的問題。
簡單理解一下 緩存穿透、緩存擊穿 和 緩存雪崩
- 緩存穿透:它是指當用戶在查詢一條數據的時候,而此時數據庫和緩存都沒有關于這條數據的任何記錄。這條數據在緩存中沒找到就會向數據庫請求獲取數據。它拿不到數據時,是會一直查詢數據庫,這樣會對數據庫的訪問造成很大的壓力。
- 緩存擊穿:一個熱點key剛好在某個時間點失效了,但是這時候突然來了大量對這個key的并發訪問請求,導致大并發請求直接穿透緩存直達數據庫,瞬間對數據庫的訪問壓力增大。
- 緩存雪崩:某一個時間段內,緩存集中過期失效,如果這個時間段內有大量請求,而查詢數據量巨大,所有的請求都會達到存儲層,存儲層的調用量會暴增,引起數據庫壓力過大甚至宕機。
2、讀寫分離
一主多從,讀寫分離,主動同步,是一種常見的數據庫架構優化手段。
一般來說當你的應用是讀多寫少,數據庫扛不住讀壓力的時候,采用讀寫分離,通過增加從庫數量可以線性提升系統讀性能。
主庫,提供數據庫寫服務;從庫,提供數據庫讀能力;主從之間,通過binlog同步數據。
當準備實施讀寫分離時,為了保證高可用,需要實現故障的自動轉移,主從架構會有潛在主從不一致性問題。
3、水平切分
水平切分,也是一種常見的數據庫架構優化手段。
當你的應用業務數據量很大,單庫容量成為性能瓶頸后,采用水平切分,可以降低數據庫單庫容量,提升數據庫寫性能。
當準備實施水平切分時,需要結合實際業務選取合理的分片鍵(sharding-key),有時候為了解決非分片鍵查詢問題還需要將數據寫到單獨的查詢組件,如ElasticSearch。
4、架構優化小結
- 讀寫分離主要是用于解決 “數據庫讀性能問題”
- 水平切分主要是用于解決“數據庫數據量大的問題”
- 分布式緩存架構可能比讀寫分離更適用于高并發、大數據量大場景。
二、硬件優化
我們使用數據庫,不管是讀操作還是寫操作,最終都是要訪問磁盤,所以說磁盤的性能決定了數據庫的性能。一塊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、達夢為例,看看每種數據庫的參數該如何配置。
1、Oracle
2、MySQL
3、POSTGRES
4、達夢數據庫
四、SQL優化
SQL優化很容易理解,就是通過給查詢字段添加索引或者改寫SQL提高其執行效率,一般而言,SQL編寫有以下幾個通用的技巧:
1)合理使用索引
索引少了查詢慢;索引多了占用空間大,執行增刪改語句的時候需要動態維護索引,影響性能 選擇率高(重復值少)且被where頻繁引用需要建立B樹索引;一般join列需要建立索引;復雜文檔類型查詢采用全文索引效率更好;索引的建立要在查詢和DML性能之間取得平衡;復合索引創建時要注意基于非前導列查詢的情況
2)使用UNION ALL替代UNION
UNION ALL的執行效率比UNION高,UNION執行時需要排重;UNION需要對數據進行排序
3)避免select * 寫法
執行SQL時優化器需要將 * 轉成具體的列;每次查詢都要回表,不能走覆蓋索引。
4)JOIN字段建議建立索引
- 一般JOIN字段都提前加上索引
5)避免復雜SQL語句
- 提升可閱讀性;避免慢查詢的概率;可以轉換成多個短查詢,用業務端處理
6)避免where 1=1寫法
7)避免order by rand()類似寫法
- RAND()導致數據列被多次掃描
1、執行計劃
要想優化SQL必須要會看執行計劃,執行計劃會告訴你哪些地方效率低,哪里可以需要優化。我們以MYSQL為例,來認識一下執行計劃。
通過explain sql 可以查看執行計劃,如:
2、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)原執行計劃
真是糟糕的執行計劃。(全表掃描,沒有索引;臨時表;排序)
①初步優化思路:
- 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;
⑦再次查看執行時間
⑧再次查看執行計劃
至此,我們的優化過程結束,結果非常完美。
3、SQL優化小結
這里給大家總結一下SQL優化的套路:
- 查看執行計劃 explain sql
- 如果有告警信息,查看告警信息 show warnings;
- 查看SQL涉及的表結構和索引信息
- 根據執行計劃,思考可能的優化點
- 按照可能的優化點執行表結構變更、增加索引、SQL改寫等操作
- 查看優化后的執行時間和執行計劃
- 如果優化效果不明顯,重復第四步操作
小結
我們今天分別從架構優化、硬件優化、DB優化、SQL優化四個角度探討了如何實施優化,提升數據庫性能。但是大家還是要記住一句話,數據庫系統沒有銀彈, 要讓適合的系統,做合適的事情。