作者 ：慘綠少年
來源：http://clsn.io/clsn/lx287.html
一、前言
MySQL 對于很多 Linux 從業者而言，是一個非常棘手的問題，多數情況都是因為對資料庫出現問題的情況和處理思路不清晰。在進行 MySQL 的優化之前必須要了解的就是 MySQL 的查詢過程，很多的查詢優化工作實際上就是遵循一些原則讓 MySQL 的優化器能夠按照預想的合理方式運作而已。
今天給大家體驗 MySQL 的優化實戰，助你高薪之路順暢！
圖 - MySQL 查詢過程
二、優化的哲學
注意：優化有風險，涉足需謹慎！
2.1、優化可能帶來的問題
- 優化不總是對一個單純的環境進行，還很可能是一個複雜的已投産的系統。
- 優化手段本來就有很大的風險，隻不過你沒能力意識到和預見到！
- 任何的技術可以解決一個問題，但必然存在帶來一個問題的風險！
- 對于優化來說解決問題而帶來的問題, 控制在可接受的範圍内才是有成果。
- 保持現狀或出現更差的情況都是失敗！
2.2、優化的需求
- 穩定性和業務可持續性, 通常比性能更重要！
- 優化不可避免涉及到變更，變更就有風險！
- 優化使性能變好，維持和變差是等機率事件！
- 切記優化, 應該是各部門協同，共同參與的工作，任何單一部門都不能對資料庫進行優化！
- 是以優化工作, 是由業務需要驅使的！！！
2.3、優化由誰參與
在進行資料庫優化時，應由資料庫管理者、業務部門代表、應用程式架構師、應用程式設計人員、應用程式開發人員、硬體及系統管理者、存儲管理者等，業務相關人員共同參與。
三、優化思路
3.1、優化什麼
在資料庫優化上有兩個主要方面：即安全與性能。
- 安全 —> 資料可持續性
- 性能 —> 資料的高性能通路
3.2、優化的範圍有哪些
存儲、主機和作業系統方面:
- 主機架構穩定性
- I/O 規劃及配置
- Swap 交換分區
- OS 核心參數和網絡問題
應用程式方面:
- 應用程式穩定性
- SQL 語句性能
- 串行通路資源
- 性能欠佳會話管理
- 這個應用适不适合用 MySQL
資料庫優化方面:
- 記憶體
- 資料庫結構 (實體 & 邏輯)
- 執行個體配置
說明：不管是在，設計系統，定位問題還是優化，都可以按照這個順序執行。
3.3、優化次元
資料庫優化次元有四個：
硬體、系統配置、資料庫表結構、SQL 及索引。
優化選擇：
- 優化成本: 硬體 > 系統配置 > 資料庫表結構 > SQL 及索引
- 優化效果: 硬體 < 系統配置 < 資料庫表結構 < SQL 及索引
四、優化工具有啥？
4.1、資料庫層面
檢查問題常用工具：
不常用但好用的工具：
4.2、資料庫層面問題解決思路
一般應急調優的思路：
針對突然的業務辦理卡頓，無法進行正常的業務處理！需要立馬解決的場景！
- 1、show processlist
-
2、explain select id ,name from stu where name=‘clsn’; # ALL id name age sex
select id,name from stu where id=2-1 函數 結果集 > 30;
show index from table;
- 3、通過執行計劃判斷，索引問題（有沒有、合不合理）或者語句本身問題
-
4、show status like ‘%lock%’; # 查詢鎖狀态
kill SESSION_ID; # 殺掉有問題的 session
正常調優思路：
針對業務周期性的卡頓，例如在每天 10-11 點業務特别慢，但是還能夠使用，過了這段時間就好了。
- 1、檢視 slowlog，分析 slowlog，分析出查詢慢的語句。
- 2、按照一定優先級，進行一個一個的排查所有慢語句。
- 3、分析 top sql，進行 explain 調試，檢視語句執行時間。
- 4、調整索引或語句本身。
4.3、系統層面
cpu 方面：
vmstat、sar top、htop、nmon、mpstat
記憶體：
free 、ps -aux 、
IO 裝置（磁盤、網絡）：
iostat 、 ss 、 netstat 、 iptraf、iftop、lsof、
vmstat 指令說明：
- Procs：r 顯示有多少程序正在等待 CPU 時間。b 顯示處于不可中斷的休眠的程序數量。在等待 I/O
- Memory：swpd 顯示被交換到磁盤的資料塊的數量。未被使用的資料塊，使用者緩沖資料塊，用于作業系統的資料塊的數量
- Swap：作業系統每秒從磁盤上交換到記憶體和從記憶體交換到磁盤的資料塊的數量。s1 和 s0 最好是 0
- Io：每秒從裝置中讀入 b1 的寫入到裝置 b0 的資料塊的數量。反映了磁盤 I/O
- System：顯示了每秒發生中斷的數量 (in) 和上下文交換 (cs) 的數量
- Cpu：顯示用于運作使用者代碼，系統代碼，空閑，等待 I/O 的 CPU 時間
iostat 指令說明
執行個體指令：iostat -dk 1 5
iostat -d -k -x 5 （檢視裝置使用率（%util）和響應時間（await））
- tps：該裝置每秒的傳輸次數。“一次傳輸” 意思是 “一次 I/O 請求”。多個邏輯請求可能會被合并為 “一次 I/O 請求”。
- iops ：硬體出廠的時候，廠家定義的一個每秒最大的 IO 次數,“一次傳輸” 請求的大小是未知的。
- kB_read/s：每秒從裝置（drive expressed）讀取的資料量；
- KB_wrtn/s：每秒向裝置（drive expressed）寫入的資料量；
- kB_read：讀取的總資料量；
- kB_wrtn：寫入的總數量資料量；這些機關都為 Kilobytes。
4.4、系統層面問題解決辦法
你認為到底負載高好，還是低好呢？
在實際的生産中，一般認為 cpu 隻要不超過 90% 都沒什麼問題 。
當然不排除下面這些特殊情況：
問題一：cpu 負載高，IO 負載低
- 記憶體不夠
- 磁盤性能差
- SQL 問題 ------> 去資料庫層，進一步排查 sql 問題
- IO 出問題了（磁盤到臨界了、raid 設計不好、raid 降級、鎖、在機關時間内 tps 過高）
- tps 過高: 大量的小資料 IO、大量的全表掃描
問題二：IO 負載高，cpu 負載低
- 大量小的 IO 寫操作：
- autocommit ，産生大量小 IO
- IO/PS, 磁盤的一個定值，硬體出廠的時候，廠家定義的一個每秒最大的 IO 次數。
- 大量大的 IO 寫操作
- SQL 問題的幾率比較大
問題三：IO 和 cpu 負載都很高
硬體不夠了或 sql 存在問題
五、基礎優化
5.1、優化思路
定位問題點:
硬體 --> 系統 --> 應用 --> 資料庫 --> 架構（高可用、讀寫分離、分庫分表）
處理方向：
明确優化目标、性能和安全的折中、防患未然
5.2、硬體優化
主機方面：
- 根據資料庫類型，主機 CPU 選擇、記憶體容量選擇、磁盤選擇
- 平衡記憶體和磁盤資源
- 随機的 I/O 和順序的 I/O
- 主機 RAID 卡的 BBU(Battery Backup Unit) 關閉
cpu 的選擇：
- cpu 的兩個關鍵因素：核數、主頻
- 根據不同的業務類型進行選擇：
- cpu 密集型：計算比較多，OLTP 主頻很高的 cpu、核數還要多
- IO 密集型：查詢比較，OLAP 核數要多，主頻不一定高的
記憶體的選擇：
- OLAP 類型資料庫，需要更多記憶體，和資料擷取量級有關。
- OLTP 類型資料一般記憶體是 cpu 核心數量的 2 倍到 4 倍，沒有最佳實踐。
存儲方面：
- 根據存儲資料種類的不同，選擇不同的儲存設備
- 配置合理的 RAID 級别 (raid5、raid10、熱備盤)
- 對與作業系統來講，不需要太特殊的選擇，最好做好備援（raid1）（ssd、sas 、sata）
raid 卡：主機 raid 卡選擇：
- 實作作業系統磁盤的備援（raid1）
- 平衡記憶體和磁盤資源
- 随機的 I/O 和順序的 I/O
- 主機 RAID 卡的 BBU(Battery Backup Unit) 要關閉。
網絡裝置方面：
使用流量支援更高的網絡裝置（交換機、路由器、網線、網卡、HBA 卡）
注意：以上這些規劃應該在初始設計系統時就應該考慮好。
5.3、伺服器硬體優化
- 1、實體狀态燈：
- 2、自帶管理裝置：遠端控制卡（FENCE 裝置：ipmi ilo idarc），開關機、硬體監控。
- 3、第三方的監控軟體、裝置（snmp、agent）對實體設施進行監控
- 4、儲存設備：自帶的監控平台。EMC2（hp 收購了）， 日立（hds），IBM 低端 OEM hds，高端存儲是自己技術，華為存儲
5.4、系統優化
Cpu：
基本不需要調整，在硬體選擇方面下功夫即可。
記憶體：
基本不需要調整，在硬體選擇方面下功夫即可。
SWAP：
MySQL 盡量避免使用 swap。阿裡雲的伺服器中預設 swap 為 0
IO ：
- raid、no lvm、 ext4 或 xfs、ssd、IO 排程政策
- Swap 調整 (不使用 swap 分區)
這個參數決定了 Linux 是傾向于使用 swap，還是傾向于釋放檔案系統 cache。在記憶體緊張的情況下，數值越低越傾向于釋放檔案系統 cache。當然，這個參數隻能減少使用 swap 的機率，并不能避免 Linux 使用 swap。
修改 MySQL 的配置參數 innodb_flush_method，開啟 O_DIRECT 模式。這種情況下，InnoDB 的 buffer pool 會直接繞過檔案系統 cache 來通路磁盤，但是 redo log 依舊會使用檔案系統 cache。值得注意的是，Redo log 是覆寫模式的，即使使用了檔案系統的 cache，也不會占用太多
IO 排程政策：
5.5、系統參數調整
Linux 系統核心參數優化：
使用者限制參數（mysql 可以不設定以下配置）：
5.6、應用優化
業務應用和資料庫應用獨立, 防火牆：iptables、selinux 等其他無用服務 (關閉)：
安裝圖形界面的伺服器不要啟動圖形界面 runlevel 3, 另外，思考将來我們的業務是否真的需要 MySQL，還是使用其他種類的資料庫。用資料庫的最高境界就是不用資料庫。
六、資料庫優化
SQL 優化方向：
執行計劃、索引、SQL 改寫
架構優化方向：
高可用架構、高性能架構、分庫分表
6.1、資料庫參數優化
調整：
執行個體整體（進階優化，擴充）
連接配接層（基礎優化）
設定合理的連接配接客戶和連接配接方式
SQL 層（基礎優化）
- query_cache_size：查詢緩存
- OLAP 類型資料庫, 需要重點加大此記憶體緩存.
- 但是一般不會超過 GB.
- 對于經常被修改的資料，緩存會立馬失效。
- 我們可以實用記憶體資料庫（redis、memecache），替代他的功能。
6.2、存儲引擎層（innodb 基礎優化參數）