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

1、 如何構(gòu)建高性能MySQL系統(tǒng)DBAplus社群 微信號(hào) dbaplus功能介紹 圍繞數(shù)據(jù)庫(kù)、大數(shù)據(jù)、PaaS云，頂級(jí)大咖、技術(shù)干貨，運(yùn)營(yíng)幾個(gè)月受眾過(guò)十萬(wàn)！成為運(yùn)維圈最專(zhuān)注圍繞“數(shù)據(jù)”的學(xué)習(xí)交流和專(zhuān)業(yè)社群！歡迎投稿，加入探討。思維導(dǎo)圖構(gòu)建高性能MySQL系統(tǒng)涵蓋從單機(jī)、硬件、OS、文件系統(tǒng)、內(nèi)存到MySQL 本身的配置，以及schema 設(shè)計(jì)、索引設(shè)計(jì) ，再到數(shù)據(jù)庫(kù)架構(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讀寫(xiě)策略選擇合適的充放電策略高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對(duì)，如果使用全盤(pán)一個(gè)分區(qū)，例如mkfs.ext4 /dev/dfa也可以使用xfs 構(gòu)建文件系統(tǒng)。禁止atim

3、e、diratimemount -o noatime -o nodiratime開(kāi)啟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è)臟頁(yè)時(shí)，會(huì)檢測(cè)該頁(yè)所在區(qū)(extent)的所有頁(yè)，如果是臟頁(yè)，那么一起刷新。這樣做的好處是通過(guò)AIO可以將多個(gè)IO寫(xiě)操作合并為一個(gè)IO操作。對(duì)于傳統(tǒng)機(jī)械硬盤(pán)建議使用，而對(duì)于固態(tài)硬盤(pán)可以關(guān)閉innodb_flush_log_at_trx_commitredo 的刷盤(pán)策略sync_binlogbinlog 的刷盤(pán)策略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ì)一直向右匹配直到遇到范圍查詢(xún)(、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索引原理及慢查詢(xún)優(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開(kāi)發(fā)優(yōu)化不使用存儲(chǔ)過(guò)程、觸發(fā)器，自定義函數(shù)不使用全文索引不使用分區(qū)表針對(duì)OTLP業(yè)務(wù)盡量避免使用多表join和子查詢(xún)不使用*,SELECT使用具體的列名：在發(fā)生列的增/刪時(shí)，發(fā)生列名修改時(shí)，最大限度避免程序邏輯中沒(méi)有修改導(dǎo)致的BUG，IN的元素個(gè)數(shù)300-500避免使用大事務(wù)，使用短小的事務(wù)：減少鎖等待和競(jìng)爭(zhēng)禁止使用%前綴模糊查詢(xún) where like %xxx禁止使用子查詢(xún)，遇到使用子查詢(xún)的情況，盡量使用join代替遇到分頁(yè)查詢(xún)，使用延遲關(guān)聯(lián)解決：分頁(yè)如果有大offset，可以先取Id，然后用主鍵id關(guān)聯(lián)表會(huì)提高效率禁止并發(fā)執(zhí)行count(*)，并發(fā)導(dǎo)致CPU飆高禁止使order by rand()不使用負(fù)向查詢(xún)，如 not in/like，使用in反向代替不要一次更新大量（大于30000條）數(shù)據(jù)，批量更新/刪除SQL中使用到OR的改寫(xiě)為用 IN() （or的效率沒(méi)有in的效率高）