共計 4546 個字符，預計需要花費 12 分鐘才能閱讀完成。
本篇文章給大家分享的是有關比較全面的 MySQL 優化是怎么樣的，丸趣 TV 小編覺得挺實用的，因此分享給大家學習，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著丸趣 TV 小編一起來看看吧。
1、硬件層相關優化
1.1、CPU 相關
在服務器的 BIOS 設置中，可調整下面的幾個配置，目的是發揮 CPU 最大性能，或者避免經典的 NUMA 問題：
1、選擇 Performance Per Watt Optimized(DAPC) 模式，發揮 CPU 最大性能，跑 DB 這種通常需要高運算量的服務就不要考慮節電了；
2、關閉 C1E 和 C States 等選項，目的也是為了提升 CPU 效率；
3、Memory Frequency（內存頻率）選擇 Maximum Performance（最佳性能）；
4、內存設置菜單中，啟用 Node Interleaving，避免 NUMA 問題；
1.2、磁盤 I / O 相關
下面幾個是按照 IOPS 性能提升的幅度排序，對于磁盤 I / O 可優化的一些措施：
1、使用 SSD 或者 PCIe SSD 設備，至少獲得數百倍甚至萬倍的 IOPS 提升；
2、購置陣列卡同時配備 CACHE 及 BBU 模塊，可明顯提升 IOPS（主要是指機械盤，SSD 或 PCIe SSD 除外。同時需要定期檢查 CACHE 及 BBU 模塊的健康狀況，確保意外時不至于丟失數據）；
3、有陣列卡時，設置陣列寫策略為 WB，甚至 FORCE WB（若有雙電保護，或對數據安全性要求不是特別高的話），嚴禁使用 WT 策略。并且閉陣列預讀策略，基本上是雞肋，用處不大；
4、盡可能選用 RAID-10，而非 RAID-5；
5、使用機械盤的話，盡可能選擇高轉速的，例如選用 15KRPM，而不是 7.2KRPM 的盤，不差幾個錢的；
2、系統層相關優化
2.1、文件系統層優化
在文件系統層，下面幾個措施可明顯提升 IOPS 性能：
1、使用 deadline/noop 這兩種 I / O 調度器，千萬別用 cfq（它不適合跑 DB 類服務）；
2、使用 xfs 文件系統，千萬別用 ext3；ext4 勉強可用，但業務量很大的話，則一定要用 xfs；
3、文件系統 mount 參數中增加：noatime, nodiratime, nobarrier 幾個選項（nobarrier 是 xfs 文件系統特有的）；
2.2、其他內核參數優化
針對關鍵內核參數設定合適的值，目的是為了減少 swap 的傾向，并且讓內存和磁盤 I / O 不會出現大幅波動，導致瞬間波峰負載：
1、將 vm.swappiness 設置為 5 -10 左右即可，甚至設置為 0（RHEL 7 以上則慎重設置為 0，除非你允許 OOM kill 發生），以降低使用 SWAP 的機會；
2、將 vm.dirty_background_ratio 設置為 5 -10，將 vm.dirty_ratio 設置為它的兩倍左右，以確保能持續將臟數據刷新到磁盤，避免瞬間 I / O 寫，產生嚴重等待（和 MySQL 中的 innodb_max_dirty_pages_pct 類似）；
3、將 net.ipv4.tcp_tw_recycle、net.ipv4.tcp_tw_reuse 都設置為 1，減少 TIME_WAIT，提高 TCP 效率；
4、至于網傳的 read_ahead_kb、nr_requests 這兩個參數，我經過測試后，發現對讀寫混合為主的 OLTP 環境影響并不大（應該是對讀敏感的場景更有效果），不過沒準是我測試方法有問題，可自行斟酌是否調整；
3、MySQL 層相關優化
3.1、關于版本選擇
官方版本我們稱為 ORACLE MySQL，這個沒什么好說的，相信絕大多數人會選擇它。
我個人強烈建議選擇 Percona 分支版本，它是一個相對比較成熟的、優秀的 MySQL 分支版本，在性能提升、可靠性、管理型方面做了不少改善。它和官方 ORACLE MySQL 版本基本完全兼容，并且性能大約有 20% 以上的提升，因此我優先推薦它，我自己也從 2008 年一直以它為主。
另一個重要的分支版本是 MariaDB，說 MariaDB 是分支版本其實已經不太合適了，因為它的目標是取代 ORACLE MySQL。它主要在原來的 MySQL Server 層做了大量的源碼級改進，也是一個非常可靠的、優秀的分支版本。但也由此產生了以 GTID 為代表的和官方版本無法兼容的新特性（MySQL 5.7 開始，也支持 GTID 模式在線動態開啟或關閉了），也考慮到絕大多數人還是會跟著官方版本走，因此沒優先推薦 MariaDB。
3.2、關于最重要的參數選項調整建議
建議調整下面幾個關鍵參數以獲得較好的性能（可使用本站提供的 my.cnf 生成器生成配置文件模板）：
1、選擇 Percona 或 MariaDB 版本的話，強烈建議啟用 thread pool 特性，可使得在高并發的情況下，性能不會發生大幅下降。此外，還有 extra_port 功能，非常實用，關鍵時刻能救命的。還有另外一個重要特色是 QUERY_RESPONSE_TIME 功能，也能使我們對整體的 SQL 響應時間分布有直觀感受；
2、設置 default-storage-engine=InnoDB，也就是默認采用 InnoDB 引擎，強烈建議不要再使用 MyISAM 引擎了，InnoDB 引擎絕對可以滿足 99% 以上的業務場景；
3、調整 innodb_buffer_pool_size 大小，如果是單實例且絕大多數是 InnoDB 引擎表的話，可考慮設置為物理內存的 50% ~ 70% 左右；
4、根據實際需要設置 innodb_flush_log_at_trx_commit、sync_binlog 的值。如果要求數據不能丟失，那么兩個都設為 1。如果允許丟失一點數據，則可分別設為 2 和 10。而如果完全不用 care 數據是否丟失的話（例如在 slave 上，反正大不了重做一次），則可都設為 0。這三種設置值導致數據庫的性能受到影響程度分別是：高、中、低，也就是第一個會另數據庫最慢，最后一個則相反；
5、設置 innodb_file_per_table = 1，使用獨立表空間，我實在是想不出來用共享表空間有什么好處了；
6、設置 innodb_data_file_path = ibdata1:1G:autoextend，千萬不要用默認的 10M，否則在有高并發事務時，會受到不小的影響；
7、設置 innodb_log_file_size=256M，設置 innodb_log_files_in_group=2，基本可滿足 90% 以上的場景；
8、設置 long_query_time = 1，而在 5.5 版本以上，已經可以設置為小于 1 了，建議設置為 0.05（50 毫秒），記錄那些執行較慢的 SQL，用于后續的分析排查；
9、根據業務實際需要，適當調整 max_connection（最大連接數）、max_connection_error（最大錯誤數，建議設置為 10 萬以上，而 open_files_limit、innodb_open_files、table_open_cache、table_definition_cache 這幾個參數則可設為約 10 倍于 max_connection 的大小；
10、常見的誤區是把 tmp_table_size 和 max_heap_table_size 設置的比較大，曾經見過設置為 1G 的，這 2 個選項是每個連接會話都會分配的，因此不要設置過大，否則容易導致 OOM 發生；其他的一些連接會話級選項例如：sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size 等，也需要注意不能設置過大；
11、由于已經建議不再使用 MyISAM 引擎了，因此可以把 key_buffer_size 設置為 32M 左右，并且強烈建議關閉 query cache 功能；
3.3、關于 Schema 設計規范及 SQL 使用建議
下面列舉了幾個常見有助于提升 MySQL 效率的 Schema 設計規范及 SQL 使用建議：
1、所有的 InnoDB 表都設計一個無業務用途的自增列做主鍵，對于絕大多數場景都是如此，真正純只讀用 InnoDB 表的并不多，真如此的話還不如用 TokuDB 來得劃算；
2、字段長度滿足需求前提下，盡可能選擇長度小的。此外，字段屬性盡量都加上 NOT NULL 約束，可一定程度提高性能；
3、盡可能不使用 TEXT/BLOB 類型，確實需要的話，建議拆分到子表中，不要和主表放在一起，避免 SELECT * 的時候讀性能太差。
4、讀取數據時，只選取所需要的列，不要每次都 SELECT *，避免產生嚴重的隨機讀問題，尤其是讀到一些 TEXT/BLOB 列；
5、對一個 VARCHAR(N) 列創建索引時，通常取其 50%（甚至更小）左右長度創建前綴索引就足以滿足 80% 以上的查詢需求了，沒必要創建整列的全長度索引；
6、通常情況下，子查詢的性能比較差，建議改造成 JOIN 寫法；
7、多表聯接查詢時，關聯字段類型盡量一致，并且都要有索引；
8、多表連接查詢時，把結果集小的表（注意，這里是指過濾后的結果集，不一定是全表數據量小的）作為驅動表；
9、多表聯接并且有排序時，排序字段必須是驅動表里的，否則排序列無法用到索引；
10、多用復合索引，少用多個獨立索引，尤其是一些基數（Cardinality）太小（比如說，該列的唯一值總數少于 255）的列就不要創建獨立索引了；
11、類似分頁功能的 SQL，建議先用主鍵關聯，然后返回結果集，效率會高很多；
3.4、其他建議
關于 MySQL 的管理維護的其他建議有：
1、通常地，單表物理大小不超過 10GB，單表行數不超過 1 億條，行平均長度不超過 8KB，如果機器性能足夠，這些數據量 MySQL 是完全能處理的過來的，不用擔心性能問題，這么建議主要是考慮 ONLINE DDL 的代價較高；
2、不用太擔心 mysqld 進程占用太多內存，只要不發生 OOM kill 和用到大量的 SWAP 都還好；
3、在以往，單機上跑多實例的目的是能最大化利用計算資源，如果單實例已經能耗盡大部分計算資源的話，就沒必要再跑多實例了；
4、定期使用 pt-duplicate-key-checker 檢查并刪除重復的索引。定期使用 pt-index-usage 工具檢查并刪除使用頻率很低的索引；
5、定期采集 slow query log，用 pt-query-digest 工具進行分析，可結合 Anemometer 系統進行 slow query 管理以便分析 slow query 并進行后續優化工作；
6、可使用 pt-kill 殺掉超長時間的 SQL 請求，Percona 版本中有個選項 innodb_kill_idle_transaction 也可實現該功能；
7、使用 pt-online-schema-change 來完成大表的 ONLINE DDL 需求；
8、定期使用 pt-table-checksum、pt-table-sync 來檢查并修復 mysql 主從復制的數據差異；
寫在最后：這次的優化參考，大部分情況下我都介紹了適用的場景，如果你的應用場景和本文描述的不太一樣，那么建議根據實際情況進行調整，而不是生搬硬套。歡迎質疑拍磚，但拒絕不經過大腦的習慣性抵制。
以上就是比較全面的 MySQL 優化是怎么樣的，丸趣 TV 小編相信有部分知識點可能是我們日常工作會見到或用到的。希望你能通過這篇文章學到更多知識。更多詳情敬請關注丸趣 TV 行業資訊頻道。
