mysql的數據庫優化
當有人問你如何對數據庫進行優化時，很多人第一反應想到的就是 SQL 優化，如何創建索引，如何改寫 SQL，他們把數據庫優化與 SQL 優化劃上了等號。
當然這不能算是完全錯誤的回答，只不過思考的角度稍微片面了些，太“程序員思維”化了，沒有站在更高層次來思考回答。那今天我們就將視角拔高，站在架構的角度來聊聊這一問題，數據庫優化可以從哪些維度入手？
數據庫優化可以從架構優化，硬件優化，DB 優化，SQL 優化四個維度入手。
此上而下，位置越靠前優化越明顯，對數據庫的性能提升越高。我們常說的 SQL 優化反而是對性能提高最小的優化。
接下來我們再看看每種優化該如何實施。
一、架構優化
一般來說在高并發的場景下對架構層進行優化其效果最為明顯，java培訓常見的優化手段有：分布式緩存，讀寫分離，分庫分表等，每種優化手段又適用于不同的應用場景。
1、分布式緩存
有句老話說的好，性能不夠，緩存來湊。當需要在架構層進行優化時我們第一時間就會想到緩存這個神器，在應用與數據庫之間增加一個緩存服務，如 Redis 或 Memcache。
當接收到查詢請求后，我們先查詢緩存，判斷緩存中是否有數據，有數據就直接返回給應用，如若沒有再查詢數據庫，并加載到緩存中，這樣就大大減少了對數據庫的訪問次數，自然而然也提高了數據庫性能。
不過需要注意的是，引入分布式緩存后系統需要考慮如何應對緩存穿透、緩存擊穿和緩存雪崩的問題。
簡單理解一下 緩存穿透、緩存擊穿 和 緩存雪崩
緩存穿透：它是指當用戶在查詢一條數據的時候，而此時數據庫和緩存都沒有關于這條數據的任何記錄。這條數據在緩存中沒找到就會向數據庫請求獲取數據。它拿不到數據時，是會一直查詢數據庫，這樣會對數據庫的訪問造成很大的壓力。
緩存擊穿：一個熱點 key 剛好在某個時間點失效了，但是這時候突然來了大量對這個 key 的并發訪問請求，導致大并發請求直接穿透緩存直達數據庫，瞬間對數據庫的訪問壓力增大。
緩存雪崩：某一個時間段內，緩存集中過期失效，如果這個時間段內有大量請求，而查詢數據量巨大，所有的請求都會達到存儲層，存儲層的調用量會暴增，引起數據庫壓力過大甚至宕機。
2、讀寫分離
一主多從，讀寫分離，主動同步，是一種常見的數據庫架構優化手段。
一般來說當你的應用是讀多寫少，數據庫扛不住讀壓力的時候，采用讀寫分離，通過增加從庫數量可以線性提升系統讀性能。
主庫，提供數據庫寫服務； 從庫，提供數據庫讀能力； 主從之間，通過 binlog 同步數據。
當準備實施讀寫分離時，為了保證高可用，需要實現故障的自動轉移，主從架構會有潛在主從不一致性問題。
3、水平切分
水平切分，也是一種常見的數據庫架構優化手段。
當你的應用業務數據量很大，單庫容量成為性能瓶頸后，采用水平切分，可以降低數據庫單庫容量，提升數據庫寫性能。
當準備實施水平切分時，需要結合實際業務選取合理的分片鍵（sharding-key），有時候為了解決非分片鍵查詢問題還需要將數據寫到單獨的查詢組件，如 ElasticSearch。
4、架構優化小結
讀寫分離主要是用于解決 “數據庫讀性能問題”
水平切分主要是用于解決“數據庫數據量大的問題”
分布式緩存架構可能比讀寫分離更適用于高并發、大數據量大場景。
二、硬件優化
我們使用數據庫，不管是讀操作還是寫操作，最終都是要訪問磁盤，所以說磁盤的性能決定了數據庫的性能。一塊 PCIE 固態硬盤的性能是普通機械硬盤的幾十倍不止。北京java培訓這里我們可以從吞吐率、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()導致數據列被多次掃描
8 查看SQL的執行計劃 分析并優化SQL -- explain
