《一張思維導(dǎo)圖學(xué)會(huì)如何構(gòu)建高性能MySQL系統(tǒng)！》要點(diǎn)：
本文介紹了一張思維導(dǎo)圖學(xué)會(huì)如何構(gòu)建高性能MySQL系統(tǒng)！，希望對(duì)您有用。如果有疑問，可以聯(lián)系我們。

作者介紹

楊奇龍,前阿里數(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è)

一、簡介

最近在壓測(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/<block device>/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/<block device>/queue/scheduler

(2)禁用塊設(shè)備輪轉(zhuǎn)模式

echo 0 > /sys/block/<block device>/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=0InnoDB存儲(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_commitredo 的刷盤策略
• sync_binlogbinlog 的刷盤策略
• 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ù)：減少鎖等待和競爭
• 禁止使用%前綴模糊查詢 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)

四、說明

1. IO相關(guān)的優(yōu)化可能還不完整,以后會(huì)逐步完善.
2. 關(guān)于數(shù)據(jù)庫系統(tǒng)水平和垂直拆分是一個(gè)比較大的命題,這里略過,每個(gè)公司的業(yè)務(wù)規(guī)模不一樣,選取的拆分策略也有所不同.

文章來自微信公眾號(hào)：DBAplus社群

轉(zhuǎn)載請(qǐng)注明本頁網(wǎng)址：
http://www.fzlkiss.com/jiaocheng/2363.html