共計(jì) 4531 個字符，預(yù)計(jì)需要花費(fèi) 12 分鐘才能閱讀完成。

本篇文章給大家分享的是有關(guān) sql 的優(yōu)化建議有哪些呢，丸趣 TV 小編覺得挺實(shí)用的，因此分享給大家學(xué)習(xí)，希望大家閱讀完這篇文章后可以有所收獲，話不多說，跟著丸趣 TV 小編一起來看看吧。

1、硬件層相關(guān)優(yōu)化

1.1、CPU 相關(guān)

在服務(wù)器的 BIOS 設(shè)置中，可調(diào)整下面的幾個配置，目的是發(fā)揮 CPU 最大性能，或者避免經(jīng)典的 NUMA 問題：

1、選擇 Performance Per Watt Optimized(DAPC) 模式，發(fā)揮 CPU 最大性能，跑 DB 這種通常需要高運(yùn)算量的服務(wù)就不要考慮節(jié)電了；

2、關(guān)閉 C1E 和 C States 等選項(xiàng)，目的也是為了提升 CPU 效率；

3、Memory Frequency（內(nèi)存頻率）選擇 Maximum Performance（最佳性能）；

4、內(nèi)存設(shè)置菜單中，啟用 Node Interleaving，避免 NUMA 問題；

1.2、磁盤 I / O 相關(guān)

下面幾個是按照 IOPS 性能提升的幅度排序，對于磁盤 I / O 可優(yōu)化的一些措施：

1、使用 SSD 或者 PCIe SSD 設(shè)備，至少獲得數(shù)百倍甚至萬倍的 IOPS 提升；

2、購置陣列卡同時配備 CACHE 及 BBU 模塊，可明顯提升 IOPS（主要是指機(jī)械盤，SSD 或 PCIe SSD 除外。同時需要定期檢查 CACHE 及 BBU 模塊的健康狀況，確保意外時不至于丟失數(shù)據(jù)）；

3、有陣列卡時，設(shè)置陣列寫策略為 WB，甚至 FORCE WB（若有雙電保護(hù)，或?qū)?shù)據(jù)安全性要求不是特別高的話），嚴(yán)禁使用 WT 策略。并且閉陣列預(yù)讀策略，基本上是雞肋，用處不大；

4、盡可能選用 RAID-10，而非 RAID-5；

5、使用機(jī)械盤的話，盡可能選擇高轉(zhuǎn)速的，例如選用 15KRPM，而不是 7.2KRPM 的盤，不差幾個錢的；

2、系統(tǒng)層相關(guān)優(yōu)化

2.1、文件系統(tǒng)層優(yōu)化

在文件系統(tǒng)層，下面幾個措施可明顯提升 IOPS 性能：

1、使用 deadline/noop 這兩種 I / O 調(diào)度器，千萬別用 cfq（它不適合跑 DB 類服務(wù)）；

2、使用 xfs 文件系統(tǒng)，千萬別用 ext3；ext4 勉強(qiáng)可用，但業(yè)務(wù)量很大的話，則一定要用 xfs；

3、文件系統(tǒng) mount 參數(shù)中增加：noatime, nodiratime, nobarrier 幾個選項(xiàng)（nobarrier 是 xfs 文件系統(tǒng)特有的）；

2.2、其他內(nèi)核參數(shù)優(yōu)化

針對關(guān)鍵內(nèi)核參數(shù)設(shè)定合適的值，目的是為了減少 swap 的傾向，并且讓內(nèi)存和磁盤 I / O 不會出現(xiàn)大幅波動，導(dǎo)致瞬間波峰負(fù)載：

1、將 vm.swappiness 設(shè)置為 5 -10 左右即可，甚至設(shè)置為 0（RHEL 7 以上則慎重設(shè)置為 0，除非你允許 OOM kill 發(fā)生），以降低使用 SWAP 的機(jī)會；

2、將 vm.dirty_background_ratio 設(shè)置為 5 -10，將 vm.dirty_ratio 設(shè)置為它的兩倍左右，以確保能持續(xù)將臟數(shù)據(jù)刷新到磁盤，避免瞬間 I / O 寫，產(chǎn)生嚴(yán)重等待（和 MySQL 中的 innodb_max_dirty_pages_pct 類似）；

3、將 net.ipv4.tcp_tw_recycle、net.ipv4.tcp_tw_reuse 都設(shè)置為 1，減少 TIME_WAIT，提高 TCP 效率；

4、至于網(wǎng)傳的 read_ahead_kb、nr_requests 這兩個參數(shù)，我經(jīng)過測試后，發(fā)現(xiàn)對讀寫混合為主的 OLTP 環(huán)境影響并不大（應(yīng)該是對讀敏感的場景更有效果），不過沒準(zhǔn)是我測試方法有問題，可自行斟酌是否調(diào)整；

3、MySQL 層相關(guān)優(yōu)化

3.1、關(guān)于版本選擇

官方版本我們稱為 ORACLE MySQL，這個沒什么好說的，相信絕大多數(shù)人會選擇它。

我個人強(qiáng)烈建議選擇 Percona 分支版本，它是一個相對比較成熟的、優(yōu)秀的 MySQL 分支版本，在性能提升、可靠性、管理型方面做了不少改善。它和官方 ORACLE MySQL 版本基本完全兼容，并且性能大約有 20% 以上的提升，因此我優(yōu)先推薦它，我自己也從 2008 年一直以它為主。

另一個重要的分支版本是 MariaDB，說 MariaDB 是分支版本其實(shí)已經(jīng)不太合適了，因?yàn)樗哪繕?biāo)是取代 ORACLE MySQL。它主要在原來的 MySQL Server 層做了大量的源碼級改進(jìn)，也是一個非常可靠的、優(yōu)秀的分支版本。但也由此產(chǎn)生了以 GTID 為代表的和官方版本無法兼容的新特性（MySQL 5.7 開始，也支持 GTID 模式在線動態(tài)開啟或關(guān)閉了），也考慮到絕大多數(shù)人還是會跟著官方版本走，因此沒優(yōu)先推薦 MariaDB。

3.2、關(guān)于最重要的參數(shù)選項(xiàng)調(diào)整建議

建議調(diào)整下面幾個關(guān)鍵參數(shù)以獲得較好的性能（可使用本站提供的 my.cnf 生成器生成配置文件模板）：

1、選擇 Percona 或 MariaDB 版本的話，強(qiáng)烈建議啟用 thread pool 特性，可使得在高并發(fā)的情況下，性能不會發(fā)生大幅下降。此外，還有 extra_port 功能，非常實(shí)用，關(guān)鍵時刻能救命的。還有另外一個重要特色是 QUERY_RESPONSE_TIME 功能，也能使我們對整體的 SQL 響應(yīng)時間分布有直觀感受；

2、設(shè)置 default-storage-engine=InnoDB，也就是默認(rèn)采用 InnoDB 引擎，強(qiáng)烈建議不要再使用 MyISAM 引擎了，InnoDB 引擎絕對可以滿足 99% 以上的業(yè)務(wù)場景；

3、調(diào)整 innodb_buffer_pool_size 大小，如果是單實(shí)例且絕大多數(shù)是 InnoDB 引擎表的話，可考慮設(shè)置為物理內(nèi)存的 50% ~ 70% 左右；

4、根據(jù)實(shí)際需要設(shè)置 innodb_flush_log_at_trx_commit、sync_binlog 的值。如果要求數(shù)據(jù)不能丟失，那么兩個都設(shè)為 1。如果允許丟失一點(diǎn)數(shù)據(jù)，則可分別設(shè)為 2 和 10。而如果完全不用 care 數(shù)據(jù)是否丟失的話（例如在 slave 上，反正大不了重做一次），則可都設(shè)為 0。這三種設(shè)置值導(dǎo)致數(shù)據(jù)庫的性能受到影響程度分別是：高、中、低，也就是第一個會另數(shù)據(jù)庫最慢，最后一個則相反；

5、設(shè)置 innodb_file_per_table = 1，使用獨(dú)立表空間，我實(shí)在是想不出來用共享表空間有什么好處了；

6、設(shè)置 innodb_data_file_path = ibdata1:1G:autoextend，千萬不要用默認(rèn)的 10M，否則在有高并發(fā)事務(wù)時，會受到不小的影響；

7、設(shè)置 innodb_log_file_size=256M，設(shè)置 innodb_log_files_in_group=2，基本可滿足 90% 以上的場景；

8、設(shè)置 long_query_time = 1，而在 5.5 版本以上，已經(jīng)可以設(shè)置為小于 1 了，建議設(shè)置為 0.05（50 毫秒），記錄那些執(zhí)行較慢的 SQL，用于后續(xù)的分析排查；

9、根據(jù)業(yè)務(wù)實(shí)際需要，適當(dāng)調(diào)整 max_connection（最大連接數(shù)）、max_connection_error（最大錯誤數(shù)，建議設(shè)置為 10 萬以上，而 open_files_limit、innodb_open_files、table_open_cache、table_definition_cache 這幾個參數(shù)則可設(shè)為約 10 倍于 max_connection 的大小；

10、常見的誤區(qū)是把 tmp_table_size 和 max_heap_table_size 設(shè)置的比較大，曾經(jīng)見過設(shè)置為 1G 的，這 2 個選項(xiàng)是每個連接會話都會分配的，因此不要設(shè)置過大，否則容易導(dǎo)致 OOM 發(fā)生；其他的一些連接會話級選項(xiàng)例如：sort_buffer_size、join_buffer_size、read_buffer_size、read_rnd_buffer_size 等，也需要注意不能設(shè)置過大；

11、由于已經(jīng)建議不再使用 MyISAM 引擎了，因此可以把 key_buffer_size 設(shè)置為 32M 左右，并且強(qiáng)烈建議關(guān)閉 query cache 功能；

3.3、關(guān)于 Schema 設(shè)計(jì)規(guī)范及 SQL 使用建議

下面列舉了幾個常見有助于提升 MySQL 效率的 Schema 設(shè)計(jì)規(guī)范及 SQL 使用建議：

1、所有的 InnoDB 表都設(shè)計(jì)一個無業(yè)務(wù)用途的自增列做主鍵，對于絕大多數(shù)場景都是如此，真正純只讀用 InnoDB 表的并不多，真如此的話還不如用 TokuDB 來得劃算；

2、字段長度滿足需求前提下，盡可能選擇長度小的。此外，字段屬性盡量都加上 NOT NULL 約束，可一定程度提高性能；

3、盡可能不使用 TEXT/BLOB 類型，確實(shí)需要的話，建議拆分到子表中，不要和主表放在一起，避免 SELECT * 的時候讀性能太差。

4、讀取數(shù)據(jù)時，只選取所需要的列，不要每次都 SELECT *，避免產(chǎn)生嚴(yán)重的隨機(jī)讀問題，尤其是讀到一些 TEXT/BLOB 列；

5、對一個 VARCHAR(N) 列創(chuàng)建索引時，通常取其 50%（甚至更小）左右長度創(chuàng)建前綴索引就足以滿足 80% 以上的查詢需求了，沒必要創(chuàng)建整列的全長度索引；

6、通常情況下，子查詢的性能比較差，建議改造成 JOIN 寫法；

7、多表聯(lián)接查詢時，關(guān)聯(lián)字段類型盡量一致，并且都要有索引；

8、多表連接查詢時，把結(jié)果集小的表（注意，這里是指過濾后的結(jié)果集，不一定是全表數(shù)據(jù)量小的）作為驅(qū)動表；

9、多表聯(lián)接并且有排序時，排序字段必須是驅(qū)動表里的，否則排序列無法用到索引；

10、多用復(fù)合索引，少用多個獨(dú)立索引，尤其是一些基數(shù)（Cardinality）太小（比如說，該列的唯一值總數(shù)少于 255）的列就不要創(chuàng)建獨(dú)立索引了；

11、類似分頁功能的 SQL，建議先用主鍵關(guān)聯(lián)，然后返回結(jié)果集，效率會高很多；

3.4、其他建議

關(guān)于 MySQL 的管理維護(hù)的其他建議有：

1、通常地，單表物理大小不超過 10GB，單表行數(shù)不超過 1 億條，行平均長度不超過 8KB，如果機(jī)器性能足夠，這些數(shù)據(jù)量 MySQL 是完全能處理的過來的，不用擔(dān)心性能問題，這么建議主要是考慮 ONLINE DDL 的代價(jià)較高；

2、不用太擔(dān)心 mysqld 進(jìn)程占用太多內(nèi)存，只要不發(fā)生 OOM kill 和用到大量的 SWAP 都還好；

3、在以往，單機(jī)上跑多實(shí)例的目的是能最大化利用計(jì)算資源，如果單實(shí)例已經(jīng)能耗盡大部分計(jì)算資源的話，就沒必要再跑多實(shí)例了；

4、定期使用 pt-duplicate-key-checker 檢查并刪除重復(fù)的索引。定期使用 pt-index-usage 工具檢查并刪除使用頻率很低的索引；

5、定期采集 slow query log，用 pt-query-digest 工具進(jìn)行分析，可結(jié)合 Anemometer 系統(tǒng)進(jìn)行 slow query 管理以便分析 slow query 并進(jìn)行后續(xù)優(yōu)化工作；

6、可使用 pt-kill 殺掉超長時間的 SQL 請求，Percona 版本中有個選項(xiàng) innodb_kill_idle_transaction 也可實(shí)現(xiàn)該功能；

7、使用 pt-online-schema-change 來完成大表的 ONLINE DDL 需求；

8、定期使用 pt-table-checksum、pt-table-sync 來檢查并修復(fù) mysql 主從復(fù)制的數(shù)據(jù)差異；

這次的優(yōu)化參考，大部分情況下我都介紹了適用的場景，如果你的應(yīng)用場景和本文描述的不太一樣，那么建議根據(jù)實(shí)際情況進(jìn)行調(diào)整，而不是生搬硬套。歡迎質(zhì)疑拍磚，但拒絕不經(jīng)過大腦的習(xí)慣性抵制。

以上就是 sql 的優(yōu)化建議有哪些呢，丸趣 TV 小編相信有部分知識點(diǎn)可能是我們?nèi)粘９ぷ鲿姷交蛴玫降摹ＯＭ隳芡ㄟ^這篇文章學(xué)到更多知識。更多詳情敬請關(guān)注丸趣 TV 行業(yè)資訊頻道。