《一張思維導圖學會如何構建高性能MySQL系統！》要點：
本文介紹了一張思維導圖學會如何構建高性能MySQL系統！，希望對您有用。如果有疑問，可以聯系我們。

作者介紹

楊奇龍,前阿里數據庫團隊資深DBA,主要負責淘寶業務線,經歷多次雙十一,有海量業務訪問DB架構設計經驗.目前就職于有贊科技,負責數據庫運維工作,熟悉MySQL性能優化,故障診斷,性能壓測

一、簡介

最近在壓測新的存儲,正好把工作過程中積累的對高性能MySQL相關的知識體系構建起來,做成思維導圖的方式.總結乃一家之言,有不妥之處,望給位讀者朋友指正.

二、思維導圖

構建高性能MySQL系統涵蓋從單機、硬件、OS、文件系統、內存到MySQL 本身的配置,以及schema 設計、索引設計 ,再到數據庫架構上的水平和垂直拓展.

三、內容展示

硬件

(1)CPU

• CPU親和性：

確保每個io都被其發起的CPU處理

echo 2 > /sys/block/<block device>/queue/rq_affinity

• 選擇最大性能模式,避免節能模式導致性能不足

• 關閉NUMA,降低swap概率

numactl –interleave=all

(2)RAID卡

• 選擇FORCE WB讀寫策略
• 選擇合適的充放電策略
• 高IO,推薦RAID10
• 空間需求大則RAID5

操作系統

(1)IO調度策略

SSD/PCIE SSD推薦noop,其它推薦deadline

echo noop > /sys/block/<block device>/queue/scheduler

(2)禁用塊設備輪轉模式

echo 0 > /sys/block/<block device>/queue/rotational

(3)內存

• vm.swappiness=0
• 內存最大性能模式

文件系統

確保4K對?,如果使用全盤一個分區,例如mkfs.ext4 /dev/dfa也可以使用xfs 構建文件系統.

禁止atime、diratime

mount -o noatime -o nodiratime

開啟trim

mount -o discard

關閉barrier

mount -o barrier=0

/dev/sdc1 /data ext4 defaults,noatime,nodiratime,nobarrier 0 0

MySQL

(1)配置優化

IO相關參數

• innodb_flush_method = O_DIRECT
• innodb_read_io_threads = 16
• innodb_write_io_threads = 16
• innodb_io_capacity = 3000(PCIE卡建議更高)
• innodb_flush_neighbors=0InnoDB存儲引擎在刷新一個臟頁時,會檢測該頁所在區(extent)的所有頁,如果是臟頁,那么一起刷新.這樣做的好處是通過AIO可以將多個IO寫操作合并為一個IO操作.對于傳統機械硬盤建議使用,而對于固態硬盤可以關閉
• innodb_flush_log_at_trx_commitredo 的刷盤策略
• sync_binlogbinlog 的刷盤策略
• innodb_log_buffer_size建議8-16M,有高TPS(比如大于6k)的可以提高到32M,系統tps越高設置可以設置的越大

推薦文章 www.cnblogs.com/conanwang/p/5849437.html

內存分配

• 策略：

jemalloc是BSD的提供的內存分配管理

tcmalloc是google的內存分配管理模塊

ptmalloc是glibc的內存分配管理

malloc-lib= /usr/lib64/libjemalloc.so.1

• 系統資源：

malloc-lib= /usr/lib64/libjemalloc.so.1

back_log：大于max_connections

thread_stack=192

• 并發控制：

使用thread_pool

thread_cache_size

(2)schema優化

索引優化

目標：利用最小的索引成本找到最需要的行記錄.

原則：

• 最左前綴原則：MySQL會一直向右匹配直到遇到范圍查詢(>、<、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的順序可以任意調整
• 避免重復索引：idx_abc多列索引,相當于創建了(a)單列索引,(a,b)組合索引以及(a,b,c)組合索引.不在索引列使用函數 如 max(id)> 10 ,id+1>3 等
• 盡量選擇區分度高的列作為前綴索引：區分度的公式是count(distinct col)/count(*),表示字段不重復的比例,比例越大我們掃描的記錄數越少

推薦文章：

• MySQL索引原理及慢查詢優化http://tech.meituan.com/mysql-index.html
• MySQL索引實踐http://blog.coderland.net/mysql/2015/08/26/MySQL%E7%B4%A2%E5%BC%95%E5%AE%9E%E8%B7%B5/
• 由淺入深探究 MySQL索引結構原理、性能分析與優化http://blog.jobbole.com/87107/

SQL開發優化

• 不使用存儲過程、觸發器,自定義函數
• 不使用全文索引
• 不使用分區表
• 針對OTLP業務盡量避免使用多表join和子查詢
• 不使用*,SELECT使用具體的列名：在發生列的增/刪時,發生列名修改時,最大限度避免程序邏輯中沒有修改導致的BUG,IN的元素個數300-500
• 避免使用大事務,使用短小的事務：減少鎖等待和競爭
• 禁止使用%前綴模糊查詢 where like ‘%xxx’
• 禁止使用子查詢,遇到使用子查詢的情況,盡量使用join代替
• 遇到分頁查詢,使用延遲關聯解決：分頁如果有大offset,可以先取Id,然后用主鍵id關聯表會提高效率
• 禁止并發執行count(*),并發導致CPU飆高
• 禁止使?order by rand()
• 不使用負向查詢,如 not in/like,使用in反向代替
• 不要一次更新大量(大于30000條)數據,批量更新/刪除
• SQL中使用到OR的改寫為用 IN() (or的效率沒有in的效率高)

數據庫架構

• 單實例無法解決空間和性能需求時考慮拆分
• 垂直拆分
• 水平拆分
• 引入緩存系統

四、說明

1. IO相關的優化可能還不完整,以后會逐步完善.
2. 關于數據庫系統水平和垂直拆分是一個比較大的命題,這里略過,每個公司的業務規模不一樣,選取的拆分策略也有所不同.

文章來自微信公眾號：DBAplus社群

轉載請注明本頁網址：
http://www.fzlkiss.com/jiaocheng/2363.html