原創(chuàng)2017-07-04楊奇龍DBAplus社群
作者介紹
楊奇龍聋呢，前阿里數(shù)據(jù)庫團(tuán)隊(duì)資深DBA，主要負(fù)責(zé)淘寶業(yè)務(wù)線颠区，經(jīng)歷多次雙十一削锰，有海量業(yè)務(wù)訪問DB架構(gòu)設(shè)計(jì)經(jīng)驗(yàn)。目前就職于有贊科技瓦呼，負(fù)責(zé)數(shù)據(jù)庫運(yùn)維工作喂窟，熟悉MySQL性能優(yōu)化测暗，故障診斷央串，性能壓測(cè)。
一碗啄、簡(jiǎn)介
最近在壓測(cè)新的存儲(chǔ)质和，正好把工作過程中積累的對(duì)高性能MySQL相關(guān)的知識(shí)體系構(gòu)建起來，做成思維導(dǎo)圖的方式稚字∷撬蓿總結(jié)乃一家之言，有不妥之處胆描，望給位讀者朋友指正瘫想。
二、思維導(dǎo)圖
構(gòu)建高性能MySQL系統(tǒng)涵蓋從單機(jī)昌讲、硬件国夜、OS、文件系統(tǒng)短绸、內(nèi)存到MySQL 本身的配置车吹，以及schema 設(shè)計(jì)筹裕、索引設(shè)計(jì) ，再到數(shù)據(jù)庫架構(gòu)上的水平和垂直拓展窄驹。
三朝卒、內(nèi)容展示
硬件
（1）CPU
CPU親和性：
確保每個(gè)io都被其發(fā)起的CPU處理
echo 2 > /sys/block//queue/rq_affinity
選擇最大性能模式，避免節(jié)能模式導(dǎo)致性能不足
關(guān)閉NUMA乐埠，降低swap概率
numactl --interleave=all
（2）RAID卡
選擇FORCE WB讀寫策略
選擇合適的充放電策略
高IO抗斤，推薦RAID10
空間需求大則RAID5
操作系統(tǒng)
（1）IO調(diào)度策略
SSD/PCIE SSD推薦noop，其它推薦deadline
echo noop > /sys/block//queue/scheduler
（2）禁用塊設(shè)備輪轉(zhuǎn)模式
echo 0 > /sys/block//queue/rotational
（3）內(nèi)存
vm.swappiness=0
內(nèi)存最大性能模式
文件系統(tǒng)
確保4K對(duì)?饮戳，如果使用全盤一個(gè)分區(qū)豪治，例如mkfs.ext4 /dev/dfa也可以使用xfs 構(gòu)建文件系統(tǒng)。
禁止atime扯罐、diratime
mount -o noatime -o nodiratime
開啟trim
mount -o discard
關(guān)閉barrier
mount -o barrier=0
/dev/sdc1 /data ext4 defaults,noatime,nodiratime,nobarrier 0 0
MySQL
（1）配置優(yōu)化
IO相關(guān)參數(shù)
innodb_flush_method = O_DIRECT
innodb_read_io_threads = 16
innodb_write_io_threads = 16
innodb_io_capacity = 3000（PCIE卡建議更高）
innodb_flush_neighbors=0
InnoDB存儲(chǔ)引擎在刷新一個(gè)臟頁時(shí)负拟，會(huì)檢測(cè)該頁所在區(qū)(extent)的所有頁，如果是臟頁歹河，那么一起刷新掩浙。這樣做的好處是通過AIO可以將多個(gè)IO寫操作合并為一個(gè)IO操作。對(duì)于傳統(tǒng)機(jī)械硬盤建議使用秸歧，而對(duì)于固態(tài)硬盤可以關(guān)閉
innodb_flush_log_at_trx_commit
redo 的刷盤策略
sync_binlog
binlog 的刷盤策略
innodb_log_buffer_size
建議8-16M厨姚，有高TPS（比如大于6k）的可以提高到32M，系統(tǒng)tps越高設(shè)置可以設(shè)置的越大
推薦文章 www.cnblogs.com/conanwang/p/5849437.html
內(nèi)存分配
策略：
jemalloc是BSD的提供的內(nèi)存分配管理
tcmalloc是google的內(nèi)存分配管理模塊
ptmalloc是glibc的內(nèi)存分配管理
malloc-lib= /usr/lib64/libjemalloc.so.1
系統(tǒng)資源：
malloc-lib= /usr/lib64/libjemalloc.so.1
back_log：大于max_connections
thread_stack=192
并發(fā)控制：
使用thread_pool
thread_cache_size
（2）schema優(yōu)化
索引優(yōu)化
目標(biāo)：利用最小的索引成本找到最需要的行記錄键菱。
原則：
最左前綴原則：MySQL會(huì)一直向右匹配直到遇到范圍查詢(>谬墙、<、between经备、like)就停止匹配拭抬，比如a=1 and b=2 and c>3 and d=4 如果建立(a,b,c,d)順序的索引，d是用不到索引的侵蒙，如果建立(a,b,d,c)的索引則都可以用到造虎，a,b,d的順序可以任意調(diào)整
避免重復(fù)索引：idx_abc多列索引,相當(dāng)于創(chuàng)建了(a)單列索引，(a,b)組合索引以及(a,b,c)組合索引纷闺。不在索引列使用函數(shù) 如 max(id)> 10 ,id+1>3 等
盡量選擇區(qū)分度高的列作為前綴索引：區(qū)分度的公式是count(distinct col)/count(*)算凿，表示字段不重復(fù)的比例，比例越大我們掃描的記錄數(shù)越少
推薦文章：
MySQL索引原理及慢查詢優(yōu)化
http://tech.meituan.com/mysql-index.html
MySQL索引實(shí)踐
http://blog.coderland.net/mysql/2015/08/26/MySQL%E7%B4%A2%E5%BC%95%E5%AE%9E%E8%B7%B5/
由淺入深探究 MySQL索引結(jié)構(gòu)原理犁功、性能分析與優(yōu)化
http://blog.jobbole.com/87107/
SQL開發(fā)優(yōu)化
不使用存儲(chǔ)過程氓轰、觸發(fā)器，自定義函數(shù)
不使用全文索引
不使用分區(qū)表
針對(duì)OTLP業(yè)務(wù)盡量避免使用多表join和子查詢
不使用*,SELECT使用具體的列名：在發(fā)生列的增/刪時(shí)浸卦，發(fā)生列名修改時(shí)署鸡，最大限度避免程序邏輯中沒有修改導(dǎo)致的BUG，IN的元素個(gè)數(shù)300-500
避免使用大事務(wù)，使用短小的事務(wù)：減少鎖等待和競(jìng)爭(zhēng)
禁止使用%前綴模糊查詢 where like ‘%xxx’
禁止使用子查詢储玫，遇到使用子查詢的情況侍筛，盡量使用join代替
遇到分頁查詢，使用延遲關(guān)聯(lián)解決：分頁如果有大offset撒穷，可以先取Id匣椰，然后用主鍵id關(guān)聯(lián)表會(huì)提高效率
禁止并發(fā)執(zhí)行count(*)，并發(fā)導(dǎo)致CPU飆高
禁止使?order by rand()
不使用負(fù)向查詢端礼，如 not in/like禽笑，使用in反向代替
不要一次更新大量（大于30000條）數(shù)據(jù)，批量更新/刪除
SQL中使用到OR的改寫為用 IN() （or的效率沒有in的效率高）
數(shù)據(jù)庫架構(gòu)
單實(shí)例無法解決空間和性能需求時(shí)考慮拆分
垂直拆分
水平拆分
引入緩存系統(tǒng)
四蛤奥、說明
IO相關(guān)的優(yōu)化可能還不完整佳镜，以后會(huì)逐步完善。
關(guān)于數(shù)據(jù)庫系統(tǒng)水平和垂直拆分是一個(gè)比較大的命題凡桥，這里略過蟀伸，每個(gè)公司的業(yè)務(wù)規(guī)模不一樣，選取的拆分策略也有所不同缅刽。
五啊掏、下載方式
點(diǎn)擊文末【閱讀原文】或登錄云盤http://pan.baidu.com/s/1dFpI4t7，可下載高清版思維導(dǎo)圖衰猛。
也歡迎大家提供自己的想法迟蜜，一起來完善這張高性能MySQL系統(tǒng)思維導(dǎo)圖，可直接在本文微信評(píng)論區(qū)留言或發(fā)送郵件至：editor@dbaplus.cn啡省。
