1.1 前言
MySQL對(duì)于很多Linux從業(yè)者而言乌逐，是一個(gè)非常棘手的問題，多數(shù)情況都是因?yàn)閷?duì)數(shù)據(jù)庫出現(xiàn)問題的情況和處理思路不清晰创葡。在進(jìn)行MySQL的優(yōu)化之前必須要了解的就是MySQL的查詢過程浙踢，很多的查詢優(yōu)化工作實(shí)際上就是遵循一些原則讓MySQL的優(yōu)化器能夠按照預(yù)想的合理方式運(yùn)行而已。
今天給大家體驗(yàn)MySQL的優(yōu)化實(shí)戰(zhàn)灿渴，助你高薪之路順暢洛波。
圖 - MySQL查詢過程
1.2 優(yōu)化的哲學(xué)
優(yōu)化有風(fēng)險(xiǎn)，涉足需謹(jǐn)慎
1.2.1 優(yōu)化可能帶來的問題
1.2.2 優(yōu)化的需求
1.2.3 優(yōu)化由誰參與
在進(jìn)行數(shù)據(jù)庫優(yōu)化時(shí)骚露，應(yīng)由數(shù)據(jù)庫管理員蹬挤、業(yè)務(wù)部門代表、應(yīng)用程序架構(gòu)師棘幸、應(yīng)用程序設(shè)計(jì)人員闻伶、應(yīng)用程序開發(fā)人員、硬件及系統(tǒng)管理員够话、存儲(chǔ)管理員等，業(yè)務(wù)相關(guān)人員共同參與光绕。
1.3 優(yōu)化思路
1.3.1 優(yōu)化什么
在數(shù)據(jù)庫優(yōu)化上有兩個(gè)主要方面：即安全與性能女嘲。
1.3.2 優(yōu)化的范圍有哪些
存儲(chǔ)、主機(jī)和操作系統(tǒng)方面:
應(yīng)用程序方面:
數(shù)據(jù)庫優(yōu)化方面:
說明：不管是在诞帐，設(shè)計(jì)系統(tǒng)欣尼，定位問題還是優(yōu)化，都可以按照這個(gè)順序執(zhí)行停蕉。
1.3.3 優(yōu)化維度
數(shù)據(jù)庫優(yōu)化維度有四個(gè):
硬件愕鼓、系統(tǒng)配置、數(shù)據(jù)庫表結(jié)構(gòu)慧起、SQL及索引
優(yōu)化選擇
1.4 優(yōu)化工具有啥菇晃？
1.4.1 數(shù)據(jù)庫層面
檢查問題常用工具
不常用但好用的工具
1.4.2 數(shù)據(jù)庫層面問題解決思路
一般應(yīng)急調(diào)優(yōu)的思路：
針對(duì)突然的業(yè)務(wù)辦理卡頓，無法進(jìn)行正常的業(yè)務(wù)處理蚓挤！需要立馬解決的場(chǎng)景磺送！
常規(guī)調(diào)優(yōu)思路：
針對(duì)業(yè)務(wù)周期性的卡頓驻子，例如在每天10-11點(diǎn)業(yè)務(wù)特別慢，但是還能夠使用估灿，過了這段時(shí)間就好了崇呵。
1.4.3 系統(tǒng)層面
cpu方面
vmstat、sar?top馅袁、htop域慷、nmon、mpstat
內(nèi)存
free汗销、ps?-aux?犹褒、
IO設(shè)備（磁盤、網(wǎng)絡(luò)）
iostat大溜、?ss?化漆、?netstat?、?iptraf钦奋、iftop座云、lsof、
vmstat 命令說明：
iostat命令說明
實(shí)例命令： iostat -dk 1 5
iostat -d -k -x 5 （查看設(shè)備使用率（%util）和響應(yīng)時(shí)間（await））
1.4.4 系統(tǒng)層面問題解決辦法
你認(rèn)為到底負(fù)載高好付材，還是低好呢朦拖？
在實(shí)際的生產(chǎn)中，一般認(rèn)為 cpu只要不超過90%都沒什么問題 厌衔。
當(dāng)然不排除下面這些特殊情況：
問題一：cpu負(fù)載高璧帝，IO負(fù)載低
問題二：IO負(fù)載高，cpu負(fù)載低
問題三：IO和cpu負(fù)載都很高
硬件不夠了或sql存在問題
1.5 基礎(chǔ)優(yōu)化
1.5.1 優(yōu)化思路
定位問題點(diǎn):
硬件 --> 系統(tǒng) --> 應(yīng)用 --> 數(shù)據(jù)庫 --> 架構(gòu)（高可用富寿、讀寫分離睬隶、分庫分表）
處理方向
明確優(yōu)化目標(biāo)、性能和安全的折中页徐、防患未然
1.5.2 硬件優(yōu)化
主機(jī)方面：
cpu的選擇：
內(nèi)存的選擇：
存儲(chǔ)方面：
raid卡：主機(jī)raid卡選擇：
網(wǎng)絡(luò)設(shè)備方面：
使用流量支持更高的網(wǎng)絡(luò)設(shè)備（交換機(jī)苏潜、路由器、網(wǎng)線变勇、網(wǎng)卡恤左、HBA卡）
注意：以上這些規(guī)劃應(yīng)該在初始設(shè)計(jì)系統(tǒng)時(shí)就應(yīng)該考慮好。
1.5.3 服務(wù)器硬件優(yōu)化
1.5.4 系統(tǒng)優(yōu)化
Cpu：
基本不需要調(diào)整搀绣，在硬件選擇方面下功夫即可飞袋。
內(nèi)存：
基本不需要調(diào)整，在硬件選擇方面下功夫即可链患。
SWAP：
MySQL盡量避免使用swap巧鸭。阿里云的服務(wù)器中默認(rèn)swap為0
IO ：
這個(gè)參數(shù)決定了Linux是傾向于使用swap，還是傾向于釋放文件系統(tǒng)cache麻捻。在內(nèi)存緊張的情況下蹄皱，數(shù)值越低越傾向于釋放文件系統(tǒng)cache览闰。當(dāng)然，這個(gè)參數(shù)只能減少使用swap的概率巷折，并不能避免Linux使用swap压鉴。
修改MySQL的配置參數(shù)innodb_flush_method，開啟O_DIRECT模式锻拘。這種情況下油吭，InnoDB的buffer pool會(huì)直接繞過文件系統(tǒng)cache來訪問磁盤，但是redo log依舊會(huì)使用文件系統(tǒng)cache署拟。值得注意的是婉宰，Redo log是覆寫模式的，即使使用了文件系統(tǒng)的cache推穷，也不會(huì)占用太多
IO調(diào)度策略
1.5.5 系統(tǒng)參數(shù)調(diào)整
Linux系統(tǒng)內(nèi)核參數(shù)優(yōu)化
用戶限制參數(shù)（mysql可以不設(shè)置以下配置）
1.5.6 應(yīng)用優(yōu)化
業(yè)務(wù)應(yīng)用和數(shù)據(jù)庫應(yīng)用獨(dú)立,防火墻：iptables心包、selinux等其他無用服務(wù)(關(guān)閉)：
安裝圖形界面的服務(wù)器不要啟動(dòng)圖形界面 runlevel 3,另外，思考將來我們的業(yè)務(wù)是否真的需要MySQL馒铃，還是使用其他種類的數(shù)據(jù)庫蟹腾。用數(shù)據(jù)庫的最高境界就是不用數(shù)據(jù)庫。
1.6 數(shù)據(jù)庫優(yōu)化
SQL優(yōu)化方向：
執(zhí)行計(jì)劃区宇、索引娃殖、SQL改寫
架構(gòu)優(yōu)化方向：
高可用架構(gòu)、高性能架構(gòu)议谷、分庫分表
1.6.1 數(shù)據(jù)庫參數(shù)優(yōu)化
調(diào)整：
實(shí)例整體（高級(jí)優(yōu)化炉爆，擴(kuò)展）
連接層（基礎(chǔ)優(yōu)化）
設(shè)置合理的連接客戶和連接方式
SQL層（基礎(chǔ)優(yōu)化）
query_cache_size： 查詢緩存
OLAP類型數(shù)據(jù)庫,需要重點(diǎn)加大此內(nèi)存緩存.
但是一般不會(huì)超過GB.
對(duì)于經(jīng)常被修改的數(shù)據(jù)，緩存會(huì)立馬失效卧晓。
我們可以實(shí)用內(nèi)存數(shù)據(jù)庫（redis芬首、memecache），替代他的功能逼裆。
1.6.2 存儲(chǔ)引擎層（innodb基礎(chǔ)優(yōu)化參數(shù)）
為什么某些人會(huì)一直比你優(yōu)秀郁稍，是因?yàn)樗旧砭秃軆?yōu)秀還一直在持續(xù)努力變得更優(yōu)秀，而你是不是還在滿足于現(xiàn)狀內(nèi)心在竊喜!
歡迎工作一到五年的Java工程師朋友們加入Java架構(gòu)開發(fā)：760940986
群內(nèi)提供免費(fèi)的Java架構(gòu)學(xué)習(xí)資料（里面有高可用波附、高并發(fā)、高性能及分布式昼钻、Jvm性能調(diào)優(yōu)掸屡、Spring源碼，MyBatis然评，Netty,Redis,Kafka,Mysql,Zookeeper,Tomcat,Docker,Dubbo,Nginx等多個(gè)知識(shí)點(diǎn)的架構(gòu)資料）合理利用自己每一分每一秒的時(shí)間來學(xué)習(xí)提升自己仅财，不要再用"沒有時(shí)間“來掩飾自己思想上的懶惰！趁年輕碗淌，使勁拼盏求，給未來的自己一個(gè)交代抖锥！
