如何構(gòu)建高性能MySQL系統(tǒng)

1、 如何構(gòu)建高性能MySQL系統(tǒng)DBAplus社群 微信號(hào) dbaplus功能介紹 圍繞數(shù)據(jù)庫、大數(shù)據(jù)、PaaS云，頂級(jí)大咖、技術(shù)干貨，運(yùn)營幾個(gè)月受眾過十萬！成為運(yùn)維圈最專注圍繞“數(shù)據(jù)”的學(xué)習(xí)交流和專業(yè)社群！歡迎投稿，加入探討。思維導(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）CPUCPU親和性：確保每個(gè)io都被其發(fā)起的CPU處理echo 2 /sys/block/queue/rq_affinity選擇最大性能模式，避免節(jié)能模式導(dǎo)致性能不足關(guān)閉NUMA，降低s

2、wap概率numactl -interleave=all（2）RAID卡選擇FORCE WB讀寫策略選擇合適的充放電策略高IO，推薦RAID10空間需求大則RAID5操作系統(tǒng)（1）IO調(diào)度策略SSD/PCIE SSD推薦noop，其它推薦deadlineecho 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對，如果使用全盤一個(gè)分區(qū)，例如mkfs.ext4 /dev/dfa也可以使用xfs 構(gòu)建文件系統(tǒng)。禁止atim

3、e、diratimemount -o noatime -o nodiratime開啟trimmount -o discard關(guān)閉barriermount -o barrier=0/dev/sdc1 /data ext4 defaults,noatime,nodiratime,nobarrier 0 0MySQL（1）配置優(yōu)化IO相關(guān)參數(shù)innodb_flush_method = O_DIRECTinnodb_read_io_threads = 16innodb_write_io_threads = 16innodb_io_capacity = 3000（PCIE卡建議更高）innodb_flu

4、sh_neighbors=0InnoDB存儲(chǔ)引擎在刷新一個(gè)臟頁時(shí)，會(huì)檢測該頁所在區(qū)(extent)的所有頁，如果是臟頁，那么一起刷新。這樣做的好處是通過AIO可以將多個(gè)IO寫操作合并為一個(gè)IO操作。對于傳統(tǒng)機(jī)械硬盤建議使用，而對于固態(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è)置的越大推薦文章 /conanwang/p/5849437.html內(nèi)存分配策略：jemalloc

5、是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.1back_log：大于max_connectionsthread_stack=192并發(fā)控制：使用thread_poolthread_cache_size（2）schema優(yōu)化索引優(yōu)化目標(biāo)：利用最小的索引成本找到最需要的行記錄。原則：最左前綴原則：MySQL會(huì)一直向右匹配直到遇到范圍查詢(、3 and d=4 如果

6、建立(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+13 等盡量選擇區(qū)分度高的列作為前綴索引：區(qū)分度的公式是count(distinct col)/count(*)，表示字段不重復(fù)的比例，比例越大我們掃描的記錄數(shù)越少推薦文章：MySQL索引原理及慢查詢優(yōu)化/mysql-index.htmlMySQL索引實(shí)踐/mysql/2015/08/26/MySQL%E7

7、%B4%A2%E5%BC%95%E5%AE%9E%E8%B7%B5/由淺入深探究 MySQL索引結(jié)構(gòu)原理、性能分析與優(yōu)化/87107/SQL開發(fā)優(yōu)化不使用存儲(chǔ)過程、觸發(fā)器，自定義函數(shù)不使用全文索引不使用分區(qū)表針對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的效率高）