21條MySQL優化建議（經歷總結）。本站提示廣大學習愛好者：（21條MySQL優化建議（經歷總結））文章只能為提供參考，不一定能成為您想要的結果。以下是21條MySQL優化建議（經歷總結）正文
明天一個同伙向我征詢怎樣去優化 MySQL，我按著思想整頓了一下，年夜概粗的可以分為21個偏向。 還有一些細節器械（table cache, 表設計，索引設計，法式端緩存之類的）先不列了，對一個體系，早期能把上面做完也是一個不錯的體系。
1. 要確保有足夠的內存
數據庫可以或許高效的運轉，最關建的身分須要內存足更年夜了，能緩存住數據，更新也能夠在內存先完成。但分歧的營業對內存須要強度紛歧樣，一推舉內存要占到數據的15-25%的比例，特殊的熱的數據，內存根本要到達數據庫的80%年夜小。
2. 須要更多更快的CPU
MySQL 5.6可以應用到64個核，而MySQL每一個query只能運轉在一個CPU上，所以請求更多的CPU，更快的CPU會更有益於並發。
3. 要選擇適合的操作體系
在官方建議估量最推舉的是Solaris, 從現實臨盆中看CentOS, REHL都是不錯的選擇，推舉應用CentOS, REHL 版本為6今後的，固然Oracle Linux也是一個不錯的選擇。固然從MySQL 5.5後對Windows做了優化，但也不推舉在高並發情況中應用windows.
4. 公道的優化體系的參數
更改文件句柄 ulimit –n 默許1024 太小
過程數限制 ulimit –u 分歧版本紛歧樣
禁失落NUMA numctl –interleave=all
5. 選擇適合的內存分派算法
默許的內存分派就是c的malloc 如今也湧現很多優化的內存分派算法:
jemalloc and tcmalloc
從MySQL 5.5後支撐聲明內存儲辦法。
[mysqld_safe]
malloc-lib = tcmalloc
或是直接指到so文件
[mysqld_safe]
malloc-lib=/usr/local/lib/libtcmalloc_minimal.so
6. 應用更快的存儲裝備ssd或是固態卡
存儲介質非常影響MySQL的隨機讀取，寫入更新速度。新一代存儲裝備固態ssd及固態卡的湧現也讓MySQL 年夜放異彩，也是淘寶在去IOE中干出了一個英俊仗。
7. 選擇優越的文件體系
推舉XFS, Ext4,假如還在應用ext2,ext3的同窗請盡快進級別。 推舉XFS，這個也是往後一段時光Linux會支撐一個文件體系。
文件體系激烈推舉: XFS
8. 優化掛載文件體系的參數
掛載XFS參數：
(rw, noatime,nodiratime,nobarrier)
掛載ext4參數：
ext4 (rw,noatime,nodiratime,nobarrier,data=ordered)
假如應用SSD或是固態盤須要斟酌：
• innodb_page_size = 4K
• Innodb_flush_neighbors = 0
9. 選擇合適的IO調劑
正常請下請應用deadline 默許是noop
echo dealine >/sys/block/{DEV-NAME}/queue/scheduler
10. 選擇適合的Raid卡Cache戰略
請應用帶電的Raid，啟用WriteBack， 關於加快redo log ,binary log, data file都有利益。
11. 禁用Query Cache
Query Cache在Innodb中有點雞肋，Innodb的數據自己可以在Innodb buffer pool中緩存，Query Cache屬於成果集緩存，假如開啟Query Cache更新寫入都要去檢討query cache反而增長了寫入的開支。
在MySQL 5.6中Query cache是被禁失落了。
12. 應用Thread Pool
如今一個數據對應5個以上App場景比擬，但MySQL有個特征跟著銜接增多的情形下機能反而降低，所以關於銜接跨越200的今後場景請斟酌應用thread pool. 這是一個巨大的創造。
13. 公道調劑內存
13.1 削減銜接的內存分派
銜接可以用thread_cache_size緩存，不雅查屬於比擬屬不如thread pool給力。數據庫在連上分派的內存以下：
max_used_connections * (
read_buffer_size +
read_rnd_buffer_size +
join_buffer_size +
sort_buffer_size +
binlog_cache_size +
thread_stack +
2 * net_buffer_length …
)
13.2 使較年夜的buffer pool
要把60-80%的內存分給innodb_buffer_pool_size. 這個不要跨越數據年夜小了，別的也不要分派跨越80%否則會應用到swap.
14. 公道選擇LOG刷新機制
Redo Logs:
– innodb_flush_log_at_trx_commit = 1 // 最平安
– innodb_flush_log_at_trx_commit = 2 // 較好機能
– innodb_flush_log_at_trx_commit = 0 // 最好的情能
binlog :
binlog_sync = 1 須要group commit支撐，假如沒這個功效可以斟酌binlog_sync=0來取得較佳機能。
數據文件：
innodb_flush_method = O_DIRECT
15. 請應用Innodb表
可以應用更多資本，在線alter操作有所進步。 今朝也支撐非中文的full text， 同時支撐Memcache API拜訪。今朝也是MySQL最優良的一個引擎。
假如你還在MyISAM請斟酌疾速轉換。
16. 設置較年夜的Redo log
之前Percona 5.5和官方MySQL 5.5比拼機能時，勝出的一個Tips就是分派了跨越4G的Redo log ,而官方MySQL5.5 redo log不克不及跨越4G. 從 MySQL 5.6後可以跨越4G了，平日建Redo log加起來要跨越500M。 可以經由過程不雅查redo log發生量，分派Redo log年夜於一小時的量便可。
17. 優化磁盤的IO
innodb_io_capactiy 在sas 15000轉的下設置裝備擺設800便可以了，在ssd上面設置裝備擺設2000以上。
在MySQL 5.6:
innodb_lru_scan_depth = innodb_io_capacity / innodb_buffer_pool_instances
innodb_io_capacity_max = min(2000, 2 * innodb_io_capacity)
18. 應用自力表空間
今朝來看新的特征都是自力表空間支撐:
truncate table 表空間收受接管
表空間傳輸
較好的去優化碎片等治理機能的增長，
全體下去看應用自力表空間是沒用的。
19. 設置裝備擺設公道的並發
innodb_thread_concurrency =並發這個參數在Innodb中變更也是最頻仍的一個參數。分歧的版本，有能夠分歧的小版本也有更改。普通推舉：
在應用thread pool 的情形下：
innodb_thread_concurrency = 0 便可以了。
假如在沒有thread pool的情形下：
5.5 推舉：innodb_thread_concurrency =16 – 32
5.6 推舉innodb_thread_concurrency = 36
20. 優化事務隔離級別
默許是 Repeatable read
推舉應用Read committed binlog格局應用mixed或是Row
較低的隔離級別 = 較好的機能
21. 重視監控
任情況離不開監控，假如少了監控，有能夠就會墮入瞽者摸象。 推舉zabbix+mpm構建監控。
