轉(zhuǎn)載：史上更全的 MySQL 高性能優(yōu)化實戰(zhàn)總結(jié)！
1 前言
2 優(yōu)化的哲學(xué)
3 優(yōu)化思路
3.1 優(yōu)化什么
3.2 優(yōu)化的范圍有哪些
3.3 優(yōu)化維度
4 優(yōu)化工具有啥役纹？
4.1 數(shù)據(jù)庫層面
4.2 數(shù)據(jù)庫層面問題解決思路
4.3 系統(tǒng)層面
4.4 系統(tǒng)層面問題解決辦法
5 基礎(chǔ)優(yōu)化
5.1 優(yōu)化思路
5.2 硬件優(yōu)化
5.3 服務(wù)器硬件優(yōu)化
5.4 系統(tǒng)優(yōu)化
5.5 系統(tǒng)參數(shù)調(diào)整
5.6 應(yīng)用優(yōu)化
6 數(shù)據(jù)庫優(yōu)化
6.1 數(shù)據(jù)庫參數(shù)優(yōu)化
6.2 存儲引擎層（innodb基礎(chǔ)優(yōu)化參數(shù)）
1 前言
在進(jìn)行MySQL的優(yōu)化之前必須要了解的就是MySQL的查詢過程偶摔，很多的查詢優(yōu)化工作實際上就是遵循一些原則讓MySQL的優(yōu)化器能夠按照預(yù)想的合理方式運行而已。
MySQL 優(yōu)化實施方案
圖 - MySQL查詢過程
2 優(yōu)化的哲學(xué)
優(yōu)化有風(fēng)險促脉，涉足需謹(jǐn)慎
2.1 優(yōu)化可能帶來的問題
優(yōu)化不總是對一個單純的環(huán)境進(jìn)行辰斋，還很可能是一個復(fù)雜的已投產(chǎn)的系統(tǒng)。
優(yōu)化手段本來就有很大的風(fēng)險瘸味，只不過你沒能力意識到和預(yù)見到宫仗！
任何的技術(shù)可以解決一個問題，但必然存在帶來一個問題的風(fēng)險旁仿！
對于優(yōu)化來說解決問題而帶來的問題,控制在可接受的范圍內(nèi)才是有成果藕夫。
保持現(xiàn)狀或出現(xiàn)更差的情況都是失敗枯冈！
2.2 優(yōu)化的需求
穩(wěn)定性和業(yè)務(wù)可持續(xù)性,通常比性能更重要毅贮！
優(yōu)化不可避免涉及到變更，變更就有風(fēng)險尘奏！
優(yōu)化使性能變好滩褥，維持和變差是等概率事件！
切記優(yōu)化,應(yīng)該是各部門協(xié)同炫加，共同參與的工作瑰煎，任何單一部門都不能對數(shù)據(jù)庫進(jìn)行優(yōu)化！
所以優(yōu)化工作,是由業(yè)務(wù)需要驅(qū)使的Ｋ仔ⅰ＞频椤！
2.3 優(yōu)化由誰參與
在進(jìn)行數(shù)據(jù)庫優(yōu)化時赋铝，應(yīng)由數(shù)據(jù)庫管理員插勤、業(yè)務(wù)部門代表、應(yīng)用程序架構(gòu)師、應(yīng)用程序設(shè)計人員饮六、應(yīng)用程序開發(fā)人員其垄、硬件及系統(tǒng)管理員、存儲管理員等卤橄，業(yè)務(wù)相關(guān)人員共同參與绿满。
3 優(yōu)化思路
3.1 優(yōu)化什么
在數(shù)據(jù)庫優(yōu)化上有兩個主要方面：即安全與性能。
安全 ---> 數(shù)據(jù)可持續(xù)性
性能 ---> 數(shù)據(jù)的高性能訪問
3.2 優(yōu)化的范圍有哪些
存儲窟扑、主機和操作系統(tǒng)方面:
主機架構(gòu)穩(wěn)定性
I/O規(guī)劃及配置
Swap交換分區(qū)
OS內(nèi)核參數(shù)和網(wǎng)絡(luò)問題
應(yīng)用程序方面:
應(yīng)用程序穩(wěn)定性
SQL語句性能
串行訪問資源
性能欠佳會話管理
這個應(yīng)用適不適合用MySQL
數(shù)據(jù)庫優(yōu)化方面:
內(nèi)存
數(shù)據(jù)庫結(jié)構(gòu)(物理&邏輯)
實例配置
說明：不管是在喇颁，設(shè)計系統(tǒng)，定位問題還是優(yōu)化嚎货，都可以按照這個順序執(zhí)行橘霎。
3.3 優(yōu)化維度
數(shù)據(jù)庫優(yōu)化維度有四個:
硬件、系統(tǒng)配置殖属、數(shù)據(jù)庫表結(jié)構(gòu)姐叁、SQL及索引
MySQL 優(yōu)化實施方案
優(yōu)化選擇
優(yōu)化成本:硬件>系統(tǒng)配置>數(shù)據(jù)庫表結(jié)構(gòu)>SQL及索引
優(yōu)化效果:硬件<系統(tǒng)配置<數(shù)據(jù)庫表結(jié)構(gòu)
4 優(yōu)化工具有啥？
4.1 數(shù)據(jù)庫層面
檢查問題常用工具
mysql
msyqladmin?????????????????????????????????mysql客戶端洗显，可進(jìn)行管理操作
mysqlshow??????????????????????????????????功能強大的查看shell命令
show[SESSION|GLOBAL]variables查看數(shù)據(jù)庫參數(shù)信息
SHOW[SESSION|GLOBAL]STATUS查看數(shù)據(jù)庫的狀態(tài)信息
information_schema?????????????????????????獲取元數(shù)據(jù)的方法
SHOWENGINEINNODBSTATUSInnodb引擎的所有狀態(tài)
SHOWPROCESSLIST查看當(dāng)前所有連接session狀態(tài)
explain獲取查詢語句的執(zhí)行計劃
showindex查看表的索引信息
slow-log記錄慢查詢語句
mysqldumpslow??????????????????????????????分析slowlog文件的
不常用但好用的工具
zabbix監(jiān)控主機外潜、系統(tǒng)、數(shù)據(jù)庫（部署zabbix監(jiān)控平臺）
pt-query-digest?????????分析慢日志
mysqlslap???????????????分析慢日志
sysbench????????????????壓力測試工具
mysql?profiling?????????統(tǒng)計數(shù)據(jù)庫整體狀態(tài)工具
Performance?Schema??????mysql性能狀態(tài)統(tǒng)計的數(shù)據(jù)
workbench???????????????管理挠唆、備份处窥、監(jiān)控、分析玄组、優(yōu)化工具（比較費資源）
關(guān)于zabbix參考：http://www.cnblogs.com/clsn/p/7885990.html
4.2 數(shù)據(jù)庫層面問題解決思路
一般應(yīng)急調(diào)優(yōu)的思路：
針對突然的業(yè)務(wù)辦理卡頓滔驾，無法進(jìn)行正常的業(yè)務(wù)處理！需要立馬解決的場景俄讹！
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 函數(shù) 結(jié)果集>30;
show index from table;
3颅悉、通過執(zhí)行計劃判斷沽瞭，索引問題（有沒有迁匠、合不合理）或者語句本身問題
4剩瓶、show status like '%lock%'; # 查詢鎖狀態(tài)
kill SESSION_ID; # 殺掉有問題的session
常規(guī)調(diào)優(yōu)思路：
針對業(yè)務(wù)周期性的卡頓，例如在每天10-11點業(yè)務(wù)特別慢城丧，但是還能夠使用延曙，過了這段時間就好了。
1亡哄、查看slowlog枝缔，分析slowlog，分析出查詢慢的語句。
2愿卸、按照一定優(yōu)先級灵临，進(jìn)行一個一個的排查所有慢語句。
3趴荸、分析top sql儒溉，進(jìn)行explain調(diào)試，查看語句執(zhí)行時間发钝。
4顿涣、調(diào)整索引或語句本身。
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命令說明：
Procs：r顯示有多少進(jìn)程正在等待CPU時間惧财。b顯示處于不可中斷的休眠的進(jìn)程數(shù)量。在等待I/O
Memory：swpd顯示被交換到磁盤的數(shù)據(jù)塊的數(shù)量扭仁。未被使用的數(shù)據(jù)塊垮衷，用戶緩沖數(shù)據(jù)塊，用于操作系統(tǒng)的數(shù)據(jù)塊的數(shù)量
Swap：操作系統(tǒng)每秒從磁盤上交換到內(nèi)存和從內(nèi)存交換到磁盤的數(shù)據(jù)塊的數(shù)量乖坠。s1和s0最好是0
Io：每秒從設(shè)備中讀入b1的寫入到設(shè)備b0的數(shù)據(jù)塊的數(shù)量搀突。反映了磁盤I/O
System：顯示了每秒發(fā)生中斷的數(shù)量(in)和上下文交換(cs)的數(shù)量
Cpu：顯示用于運行用戶代碼，系統(tǒng)代碼熊泵，空閑仰迁，等待I/O的CPU時間
iostat命令說明
實例命令：??iostat?-dk15
iostat?-d?-k?-x5（查看設(shè)備使用率（%util）和響應(yīng)時間（await））
tps：該設(shè)備每秒的傳輸次數(shù)⊥绶郑“一次傳輸”意思是“一次I/O請求”徐许。多個邏輯請求可能會被合并為“一次I/O請求”。
iops?：硬件出廠的時候卒蘸，廠家定義的一個每秒最大的IO次數(shù)
"一次傳輸"請求的大小是未知的雌隅。
kB_read/s：每秒從設(shè)備（drive?expressed）讀取的數(shù)據(jù)量；
KB_wrtn/s：每秒向設(shè)備（drive?expressed）寫入的數(shù)據(jù)量；
kB_read：讀取的總數(shù)據(jù)量恰起；
kB_wrtn：寫入的總數(shù)量數(shù)據(jù)量修械；這些單位都為Kilobytes。
4.4 系統(tǒng)層面問題解決辦法
你認(rèn)為到底負(fù)載高好检盼，還是低好呢祠肥？
在實際的生產(chǎn)中，一般認(rèn)為 cpu只要不超過90%都沒什么問題 梯皿。
當(dāng)然不排除下面這些特殊情況：
問題一：cpu負(fù)載高仇箱，IO負(fù)載低
內(nèi)存不夠
磁盤性能差
SQL問題 ------>去數(shù)據(jù)庫層，進(jìn)一步排查sql問題
IO出問題了（磁盤到臨界了东羹、raid設(shè)計不好剂桥、raid降級、鎖属提、在單位時間內(nèi)tps過高）
tps過高: 大量的小數(shù)據(jù)IO权逗、大量的全表掃描
問題二：IO負(fù)載高，cpu負(fù)載低
大量小的IO?寫操作：
autocommit 冤议，產(chǎn)生大量小IO
IO/PS,磁盤的一個定值斟薇，硬件出廠的時候，廠家定義的一個每秒最大的IO次數(shù)恕酸。
大量大的IO?寫操作
SQL問題的幾率比較大
問題三：IO和cpu負(fù)載都很高
硬件不夠了或sql存在問題
5 基礎(chǔ)優(yōu)化
5.1 優(yōu)化思路
定位問題點吮吸
硬件 --> 系統(tǒng) --> 應(yīng)用 --> 數(shù)據(jù)庫 --> 架構(gòu)（高可用堪滨、讀寫分離、分庫分表）
處理方向
明確優(yōu)化目標(biāo)蕊温、性能和安全的折中袱箱、防患未然
5.2 硬件優(yōu)化
主機方面：
根據(jù)數(shù)據(jù)庫類型，主機CPU選擇义矛、內(nèi)存容量選擇发笔、磁盤選擇
平衡內(nèi)存和磁盤資源
隨機的I/O和順序的I/O
主機 RAID卡的BBU(Battery Backup Unit)關(guān)閉
cpu的選擇：
cpu的兩個關(guān)鍵因素：核數(shù)、主頻
根據(jù)不同的業(yè)務(wù)類型進(jìn)行選擇：
cpu密集型：計算比較多凉翻，OLTP 主頻很高的cpu了讨、核數(shù)還要多
IO密集型：查詢比較，OLAP 核數(shù)要多制轰，主頻不一定高的
內(nèi)存的選擇：
OLAP類型數(shù)據(jù)庫前计，需要更多內(nèi)存，和數(shù)據(jù)獲取量級有關(guān)艇挨。
OLTP類型數(shù)據(jù)一般內(nèi)存是cpu核心數(shù)量的2倍到4倍残炮，沒有最佳實踐。
存儲方面：
根據(jù)存儲數(shù)據(jù)種類的不同缩滨，選擇不同的存儲設(shè)備
配置合理的RAID級別(raid5、raid10、熱備盤)
對與操作系統(tǒng)來講脉漏，不需要太特殊的選擇苞冯，最好做好冗余（raid1）（ssd、sas 侧巨、sata）
raid卡：主機raid卡選擇：
實現(xiàn)操作系統(tǒng)磁盤的冗余（raid1）
平衡內(nèi)存和磁盤資源
隨機的I/O和順序的I/O
主機 RAID卡的BBU(Battery Backup Unit)要關(guān)閉舅锄。
網(wǎng)絡(luò)設(shè)備方面：
使用流量支持更高的網(wǎng)絡(luò)設(shè)備（交換機、路由器司忱、網(wǎng)線皇忿、網(wǎng)卡、HBA卡）
注意：以上這些規(guī)劃應(yīng)該在初始設(shè)計系統(tǒng)時就應(yīng)該考慮好坦仍。
5.3 服務(wù)器硬件優(yōu)化
1鳍烁、物理狀態(tài)燈：
2、自帶管理設(shè)備：遠(yuǎn)程控制卡（FENCE設(shè)備：ipmi ilo idarc）繁扎，開關(guān)機幔荒、硬件監(jiān)控。
3梳玫、第三方的監(jiān)控軟件爹梁、設(shè)備（snmp、agent）對物理設(shè)施進(jìn)行監(jiān)控
4提澎、存儲設(shè)備：自帶的監(jiān)控平臺姚垃。EMC2（hp收購了）， 日立（hds）盼忌，IBM低端OEM hds莉炉，高端存儲是自己技術(shù)，華為存儲
5.4 系統(tǒng)優(yōu)化
Cpu：
基本不需要調(diào)整碴犬，在硬件選擇方面下功夫即可絮宁。
內(nèi)存：
基本不需要調(diào)整，在硬件選擇方面下功夫即可服协。
SWAP：
MySQL盡量避免使用swap绍昂。
阿里云的服務(wù)器中默認(rèn)swap為0
IO ：
raid、no lvm偿荷、 ext4或xfs窘游、ssd、IO調(diào)度策略
Swap調(diào)整(不使用swap分區(qū))
/proc/sys/vm/swappiness的內(nèi)容改成0（臨時）跳纳，/etc/sysctl.conf上添加vm.swappiness=0（永久）
這個參數(shù)決定了Linux是傾向于使用swap忍饰，還是傾向于釋放文件系統(tǒng)cache。在內(nèi)存緊張的情況下寺庄，數(shù)值越低越傾向于釋放文件系統(tǒng)cache艾蓝。
當(dāng)然力崇，這個參數(shù)只能減少使用swap的概率，并不能避免Linux使用swap赢织。
修改MySQL的配置參數(shù)innodb_flush_method亮靴，開啟O_DIRECT模式。
這種情況下于置，InnoDB的buffer pool會直接繞過文件系統(tǒng)cache來訪問磁盤茧吊，但是redo log依舊會使用文件系統(tǒng)cache。
值得注意的是八毯，Redo log是覆寫模式的搓侄，即使使用了文件系統(tǒng)的cache，也不會占用太多
IO調(diào)度策略
#echodeadline>/sys/block/sda/queue/scheduler???臨時修改為deadline
永久修改
vi?/boot/grub/grub.conf
更改到如下內(nèi)容:
kernel/boot/vmlinuz-2.6.18-8.el5?ro?root=LABEL=/elevator=deadline?rhgb?quiet
5.5 系統(tǒng)參數(shù)調(diào)整
Linux系統(tǒng)內(nèi)核參數(shù)優(yōu)化
vim/etc/sysctl.conf
net.ipv4.ip_local_port_range?=102465535#?用戶端口范圍
net.ipv4.tcp_max_syn_backlog?=4096
net.ipv4.tcp_fin_timeout?=30
fs.file-max=65535#?系統(tǒng)最大文件句柄话速，控制的是能打開文件最大數(shù)量??
用戶限制參數(shù)（mysql可以不設(shè)置以下配置）
vim/etc/security/limits.conf
*?soft?nproc65535
*?hard?nproc65535
*?soft?nofile65535
*?hard?nofile65535
5.6 應(yīng)用優(yōu)化
業(yè)務(wù)應(yīng)用和數(shù)據(jù)庫應(yīng)用獨立,
防火墻：iptables讶踪、selinux等其他無用服務(wù)(關(guān)閉)：
chkconfig--level23456acpidoff
chkconfig?--level23456anacronoff
chkconfig?--level23456autofsoff
chkconfig?--level23456avahi-daemonoff
chkconfig?--level23456bluetoothoff
chkconfig?--level23456cupsoff
chkconfig?--level23456firstbootoff
chkconfig?--level23456haldaemonoff
chkconfig?--level23456hplipoff
chkconfig?--level23456ip6tablesoff
chkconfig?--level23456iptablesoff
chkconfig?--level23456isdnoff
chkconfig?--level23456pcscdoff
chkconfig?--level23456sendmailoff
chkconfig?--level23456yum-updatesdoff
安裝圖形界面的服務(wù)器不要啟動圖形界面 runlevel3
另外，思考將來我們的業(yè)務(wù)是否真的需要MySQL尿孔，還是使用其他種類的數(shù)據(jù)庫俊柔。用數(shù)據(jù)庫的最高境界就是不用數(shù)據(jù)庫。
6 數(shù)據(jù)庫優(yōu)化
SQL優(yōu)化方向：
執(zhí)行計劃活合、索引雏婶、SQL改寫
架構(gòu)優(yōu)化方向：
高可用架構(gòu)、高性能架構(gòu)白指、分庫分表
6.1 數(shù)據(jù)庫參數(shù)優(yōu)化
調(diào)整：
實例整體（高級優(yōu)化留晚，擴(kuò)展）：
thread_concurrency#?并發(fā)線程數(shù)量個數(shù)
sort_buffer_size#?排序緩存
read_buffer_size#?順序讀取緩存
read_rnd_buffer_size#?隨機讀取緩存
key_buffer_size#?索引緩存
thread_cache_size#?(1G—>8,?2G—>16,?3G—>32,?>3G—>64)
連接層（基礎(chǔ)優(yōu)化）
設(shè)置合理的連接客戶和連接方式
max_connections#?最大連接數(shù)，看交易筆數(shù)設(shè)置????
max_connect_errors#?最大錯誤連接數(shù)告嘲，能大則大
connect_timeout#?連接超時
max_user_connections#?最大用戶連接數(shù)
skip-name-resolve#?跳過域名解析
wait_timeout#?等待超時
back_log#?可以在堆棧中的連接數(shù)量
SQL層（基礎(chǔ)優(yōu)化）
query_cache_size：?查詢緩存>>>??OLAP類型數(shù)據(jù)庫,需要重點加大此內(nèi)存緩存错维，
但是一般不會超過GB
對于經(jīng)常被修改的數(shù)據(jù)，緩存會立馬失效橄唬。
我們可以實用內(nèi)存數(shù)據(jù)庫（redis赋焕、memecache），替代他的功能仰楚。
6.2 存儲引擎層（innodb基礎(chǔ)優(yōu)化參數(shù)）
default-storage-engine
innodb_buffer_pool_size#?沒有固定大小隆判，50%測試值，看看情況再微調(diào)僧界。但是盡量設(shè)置不要超過物理內(nèi)存70%
innodb_file_per_table=(1,0)
innodb_flush_log_at_trx_commit=(0,1,2)#?1是最安全的侨嘀，0是性能最高，2折中
binlog_sync
Innodb_flush_method=(O_DIRECT,?fdatasync)
innodb_log_buffer_size#?100M以下
innodb_log_file_size#?100M?以下
innodb_log_files_in_group#?5個成員以下,一般2-3個夠用（iblogfile0-N）
innodb_max_dirty_pages_pct#?達(dá)到百分之75的時候刷寫?內(nèi)存臟頁到磁盤捂襟。
log_bin
max_binlog_cache_size#?可以不設(shè)置
max_binlog_size#?可以不設(shè)置
innodb_additional_mem_pool_size#小于2G內(nèi)存的機器咬腕，推薦值是20M。32G內(nèi)存以上100M
