所以本文講解MySQL在各個(gè)方面的優(yōu)化方向，方便后端開發(fā)人員在調(diào)優(yōu)和問(wèn)題排查過(guò)程中找到切入點(diǎn)。

本文配置均為參考值，實(shí)際設(shè)置要參考基準(zhǔn)測(cè)試或現(xiàn)場(chǎng)環(huán)境。

服務(wù)器硬件的優(yōu)化

提升硬件設(shè)備，例如選擇盡量高頻率的內(nèi)存（頻率不能高于主板的支持）、提升網(wǎng)絡(luò)帶寬、使用SSD高速磁盤、提升CPU性能等。

CPU的選擇:

• 對(duì)于數(shù)據(jù)庫(kù)并發(fā)比較高的場(chǎng)景，CPU的數(shù)量比頻率重要。

• 對(duì)于CPU密集型場(chǎng)景和頻繁執(zhí)行復(fù)雜SQL的場(chǎng)景，CPU的頻率越高越好。

MySQL數(shù)據(jù)庫(kù)配置優(yōu)化

• 表示緩沖池字節(jié)大小。

• 推薦值為物理內(nèi)存的50%~80%。

• innodb_buffer_pool_size

• 用來(lái)控制redo log刷新到磁盤的策略。

• innodb_flush_log_at_trx_commit=1

• 每提交1次事務(wù)同步寫到磁盤中，可以設(shè)置為n。

• sync_binlog=1

• 臟頁(yè)占innodb_buffer_pool_size的比例時(shí)，觸發(fā)刷臟頁(yè)到磁盤。 推薦值為25%~50%。

• innodb_max_dirty_pages_pct=30

• 后臺(tái)進(jìn)程最大IO性能指標(biāo)。

• 默認(rèn)200，如果SSD，調(diào)整為5000~20000

• innodb_io_capacity=200

• 指定innodb共享表空間文件的大小。

• innodb_data_file_path

• 慢查詢?nèi)罩镜拈撝翟O(shè)置，單位秒。

• long_qurey_time=0.3

• mysql復(fù)制的形式，row為MySQL8.0的默認(rèn)形式。

• binlog_format=row

• 調(diào)高該參數(shù)則應(yīng)降低interactive_timeout、wait_timeout的值。

• max_connections=200

• 過(guò)大，實(shí)例恢復(fù)時(shí)間長(zhǎng)；過(guò)小，造成日志切換頻繁。

• innodb_log_file_size

• 全量日志建議關(guān)閉。

• 默認(rèn)關(guān)閉。

• general_log=0

CentOS系統(tǒng)針對(duì)mysql的參數(shù)優(yōu)化

內(nèi)核相關(guān)參數(shù)(/etc/sysctl.conf)

以下參數(shù)可以直接放到sysctl.conf文件的末尾。

1.增加監(jiān)聽隊(duì)列上限：

net.core.somaxconn = 65535

net.core.netdev_max_backlog = 65535

net.ipv4.tcp_max_syn_backlog = 65535

2.加快TCP連接的回收：

net.ipv4.tcp_fin_timeout = 10

net.ipv4.tcp_tw_reuse = 1

net.ipv4.tcp_tw_recycle = 1

3.TCP連接接收和發(fā)送緩沖區(qū)大小的默認(rèn)值和最大值:

net.core.wmem_default = 87380

net.core.wmem_max = 16777216

net.core.rmem_default = 87380

net.core.rmem_max = 16777216

4.減少失效連接所占用的TCP資源的數(shù)量，加快資源回收的效率：

net.ipv4.tcp_keepalive_time = 120

net.ipv4.tcp_keepalive_intvl = 30

net.ipv4.tcp_keepalive_probes = 3

5.單個(gè)共享內(nèi)存段的最大值：

kernel.shmmax = 4294967295

• 這個(gè)參數(shù)應(yīng)該設(shè)置的足夠大，以便能在一個(gè)共享內(nèi)存段下容納整個(gè)的Innodb緩沖池的大小。

• 這個(gè)值的大小對(duì)于64位linux系統(tǒng)，可取的最大值為(物理內(nèi)存值-1)byte，建議值為大于物理內(nèi)存的一半，一般取值大于Innodb緩沖池的大小即可。

6.控制換出運(yùn)行時(shí)內(nèi)存的相對(duì)權(quán)重：

vm.swappiness = 0

這個(gè)參數(shù)當(dāng)內(nèi)存不足時(shí)會(huì)對(duì)性能產(chǎn)生比較明顯的影響。（設(shè)置為0，表示Linux內(nèi)核虛擬內(nèi)存完全被占用，才會(huì)要使用交換區(qū)。）

Linux系統(tǒng)內(nèi)存交換區(qū)：

在Linux系統(tǒng)安裝時(shí)都會(huì)有一個(gè)特殊的磁盤分區(qū)，稱之為系統(tǒng)交換分區(qū)。

使用 free -m 命令可以看到swap就是內(nèi)存交換區(qū)。

作用：當(dāng)操作系統(tǒng)沒(méi)有足夠的內(nèi)存時(shí)，就會(huì)將部分虛擬內(nèi)存寫到磁盤的交換區(qū)中，這樣就會(huì)發(fā)生內(nèi)存交換。

如果Linux系統(tǒng)上完全禁用交換分區(qū)，帶來(lái)的風(fēng)險(xiǎn)：

1. 降低操作系統(tǒng)的性能

2. 容易造成內(nèi)存溢出，崩潰，或都被操作系統(tǒng)kill掉

增加資源限制(/etc/security/limit.conf)

打開文件數(shù)的限制(以下參數(shù)可以直接放到limit.conf文件的末尾)：

* soft nofile 65535

* hard nofile 65535

*：表示對(duì)所有用戶有效

soft：表示當(dāng)前系統(tǒng)生效的設(shè)置（soft不能大于hard ）

hard：表明系統(tǒng)中所能設(shè)定的最大值

nofile：表示所限制的資源是打開文件的最大數(shù)目

65535：限制的數(shù)量

以上兩行配置將可打開的文件數(shù)量增加到65535個(gè)，以保證可以打開足夠多的文件句柄。

注意：這個(gè)文件的修改需要重啟系統(tǒng)才能生效。

磁盤調(diào)度策略

1.cfq (完全公平隊(duì)列策略，Linux2.6.18之后內(nèi)核的系統(tǒng)默認(rèn)策略)

該模式按進(jìn)程創(chuàng)建多個(gè)隊(duì)列，各個(gè)進(jìn)程發(fā)來(lái)的IO請(qǐng)求會(huì)被cfq以輪循方式處理，對(duì)每個(gè)IO請(qǐng)求都是公平的。該策略適合離散讀的應(yīng)用。

2.deadline (截止時(shí)間調(diào)度策略)

deadline，包含讀和寫兩個(gè)隊(duì)列，確保在一個(gè)截止時(shí)間內(nèi)服務(wù)請(qǐng)求（截止時(shí)間是可調(diào)整的），而默認(rèn)讀期限短于寫期限。這樣就防止了寫操作因?yàn)椴荒鼙蛔x取而餓死的現(xiàn)象，deadline對(duì)數(shù)據(jù)庫(kù)類應(yīng)用是最好的選擇。

3.noop (電梯式調(diào)度策略)

noop只實(shí)現(xiàn)一個(gè)簡(jiǎn)單的FIFO隊(duì)列，傾向餓死讀而利于寫，因此noop對(duì)于閃存設(shè)備、RAM及嵌入式系統(tǒng)是最好的選擇。

4.anticipatory (預(yù)料I/O調(diào)度策略)

本質(zhì)上與deadline策略一樣，但在最后一次讀操作之后，要等待6ms，才能繼續(xù)進(jìn)行對(duì)其它I/O請(qǐng)求進(jìn)行調(diào)度。它會(huì)在每個(gè)6ms中插入新的I/O操作，合并寫入流，用寫入延時(shí)換取最大的寫入吞吐量。anticipatory適合于寫入較多的環(huán)境，比如文件服務(wù)器。該策略對(duì)數(shù)據(jù)庫(kù)環(huán)境表現(xiàn)很差。

查看調(diào)度策略的方法：

cat /sys/block/devname/queue/scheduler

修改調(diào)度策略的方法：

echo <schedulername> > /sys/block/devname/queue/scheduler

MySQL的參數(shù)配置

請(qǐng)閱讀筆者的CSDN博客《MySQL服務(wù)器參數(shù)配置》，點(diǎn)擊跳轉(zhuǎn)。如無(wú)法跳轉(zhuǎn)，請(qǐng)查看原文即可查看。

MySQL表結(jié)構(gòu)與SQL優(yōu)化

索引優(yōu)化規(guī)則

結(jié)合筆者的上一篇博客——《徹底搞懂MySQL的索引》，可以很容易理解索引優(yōu)化的原理。

1.使用最左前綴規(guī)則

如果使用聯(lián)合索引，要遵守最左前綴規(guī)則。即要求使用聯(lián)合索引進(jìn)行查詢，從索引的最左前列開始，不跳過(guò)索引中的列并且不能使用范圍查詢(>、<、between、like)。

索引失效示例

2.模糊查詢不能利用索引(like '%XX'或者like '%XX%')

假如索引列code的值為'AAA','AAB','BAA','BAB'，如果where code like '%AB'條件，由于條件前面是模糊的，所以不能利用索引的順序，必須逐個(gè)查找，看是否滿足條件。這樣會(huì)導(dǎo)致全索引掃描或者全表掃描。

如果是where code like 'A%'，就可以查找code中A開頭的數(shù)據(jù)，當(dāng)碰到B開頭的數(shù)據(jù)時(shí)，就可以停止查找了，因?yàn)楹竺娴臄?shù)據(jù)一定不滿足要求，這樣可以提高查詢效率。

3.不要過(guò)多創(chuàng)建索引

過(guò)多的索引會(huì)占用更多的空間，而且每次增、刪、改操作都會(huì)重建索引。

在一般的互聯(lián)網(wǎng)場(chǎng)景中，查詢語(yǔ)句的執(zhí)行次數(shù)遠(yuǎn)遠(yuǎn)大于增刪改語(yǔ)句的執(zhí)行次數(shù)，所以重建索引的開銷可以忽略不計(jì)。但在大數(shù)據(jù)量導(dǎo)入時(shí)，可以考慮先刪除索引，批量插入數(shù)據(jù)，然后添加索引。

盡量擴(kuò)展索引，比如現(xiàn)有索引(a)，現(xiàn)在又要對(duì)(a,b)進(jìn)行索引，那么只需要修改索引(a)即可，避免不必要的索引冗余。

4.索引長(zhǎng)度盡量短

短索引可以節(jié)省索引空間，使查找的速度得到提升，同時(shí)內(nèi)存中也可以裝載更多的索引鍵值。

太長(zhǎng)的列，可以選擇建立前綴索引

5.索引更新不能頻繁

更新非常頻繁的數(shù)據(jù)不適宜建索引，因?yàn)榫S護(hù)索引的成本。

6.索引列不能參與計(jì)算

不要在索引列上做任何的操作，包括計(jì)算、函數(shù)、自動(dòng)或者手動(dòng)類型的轉(zhuǎn)換，這樣都會(huì)導(dǎo)致索引失效。

比如，where from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很簡(jiǎn)單，b+樹中存的都是數(shù)據(jù)表中的字段值，但進(jìn)行檢索時(shí)，需要把所有元素都應(yīng)用函數(shù)才能比較，顯然成本太大。所以語(yǔ)句應(yīng)該寫成where create_time = unix_timestamp(’2014-05-29’)。

查詢時(shí)的優(yōu)化

小表驅(qū)動(dòng)大表

第一張表是全表索引(要以此關(guān)聯(lián)其他表)，其余表的查詢類型type為range(索引區(qū)間獲得)，也就是6 * 1 * 1，共遍歷查詢6次即可;

建議使用left join時(shí)，以小表關(guān)聯(lián)大表，因?yàn)槭褂胘oin的話，第一張表是必須全掃描的，以少關(guān)聯(lián)多就可以減少這個(gè)掃描次數(shù).

這里所說(shuō)的表的type，指的是explain執(zhí)行計(jì)劃中的結(jié)果字段。詳情點(diǎn)擊查看，explain的屬性詳解與提速百倍的優(yōu)化示例

避免全表掃描

mysql在使用不等于(!=或者<>)的時(shí)候無(wú)法使用導(dǎo)致全表掃描。在查詢的時(shí)候，如果對(duì)索引使用不等于的操作將會(huì)導(dǎo)致索引失效，進(jìn)行全表掃描

避免mysql放棄索引查詢

如果mysql估計(jì)使用全表掃描要比使用索引快，則不使用索引。（最典型的場(chǎng)景就是數(shù)據(jù)量少的時(shí)候）

使用覆蓋索引，少使用select*

需要用到什么數(shù)據(jù)就查詢什么數(shù)據(jù)，這樣可以減少網(wǎng)絡(luò)的傳輸和mysql的全表掃描。

盡量使用覆蓋索引，比如索引為name，age，address的組合索引，那么盡量覆蓋這三個(gè)字段之中的值，mysql將會(huì)直接在索引上取值（using index），并且返回值不包含不是索引的字段。

如果查詢select的列過(guò)多，覆蓋索引的效率會(huì)大大下降，這時(shí)可以考慮放棄覆蓋索引查詢。

order by的索引生效

order by排序應(yīng)該遵循最佳左前綴查詢，如果是使用多個(gè)索引字段進(jìn)行排序，那么排序的規(guī)則必須相同（同是升序或者降序），否則索引同樣會(huì)失效。

不正確的使用導(dǎo)致索引失效

如果查詢中有某個(gè)列的范圍查詢，則其右邊所有列都無(wú)法使用索引。

for update鎖表

A, B兩個(gè)事務(wù)分別使用select ... where ... for update進(jìn)行查詢時(shí)：

1. A事務(wù)執(zhí)行查詢操作的時(shí)候，如果這個(gè)查詢結(jié)果為空，無(wú)論where條件是否是索引字段，B事務(wù)執(zhí)行查詢操作時(shí)，不會(huì)被阻塞。

2. A事務(wù)執(zhí)行查詢操作的時(shí)候，當(dāng)where條件是索引字段，則B事務(wù)執(zhí)行同樣的查詢時(shí)會(huì)被行加鎖阻塞；當(dāng)where條件不是索引字段，則B事務(wù)執(zhí)行有結(jié)果集的查詢，都會(huì)被阻塞。

for update操作一定要謹(jǐn)慎，之前筆者就遇到過(guò)for update產(chǎn)生gap鎖，導(dǎo)致后續(xù)請(qǐng)求阻塞的問(wèn)題。

之后的博客單獨(dú)介紹MySQL的鎖機(jī)制，同時(shí)講解下更多死鎖的情況。

其他優(yōu)化

開啟慢查詢

開啟慢查詢?nèi)罩?，可以讓MySQL記錄下查詢超過(guò)指定時(shí)間的語(yǔ)句，通過(guò)定位分析性能的瓶頸，更好的優(yōu)化數(shù)據(jù)庫(kù)系統(tǒng)的性能。

以后單獨(dú)的博客進(jìn)行詳細(xì)的講解

實(shí)時(shí)獲取有性能問(wèn)題的SQL

利用information_schema數(shù)據(jù)庫(kù)的processlist表，實(shí)時(shí)查看執(zhí)行時(shí)間過(guò)長(zhǎng)的線程，定位需要優(yōu)化的SQL。

例如下面的SQL的作用是查看正在執(zhí)行的線程，并按Time倒排序，查看執(zhí)行時(shí)間過(guò)長(zhǎng)的線程。

select * from information_schema.processlist where Command != 'Sleep' order by Time desc;

垂直分割

“垂直分割”是一種把數(shù)據(jù)庫(kù)中的表，按列變成幾張表的方法。這樣可以降低表的復(fù)雜度和字段的數(shù)目，從而達(dá)到優(yōu)化的目的。

示例一：

在Users表中有一個(gè)字段是address，它是可選字段，并且不需要經(jīng)常讀取或是修改。

那么，就可以把它放到另外一張表中，這樣會(huì)讓原表有更好的性能。

示例二：

有一個(gè)叫 “l(fā)ast_login”的字段，它會(huì)在每次用戶登錄時(shí)被更新，每次更新時(shí)會(huì)導(dǎo)致該表的查詢緩存被清空。

所以，可以把這個(gè)字段放到另一個(gè)表中。

這樣就不會(huì)影響對(duì)用戶ID、用戶名、用戶角色(假設(shè)這幾個(gè)屬性并不頻繁修改)的不停地讀取了，因?yàn)椴樵兙彺鏁?huì)增加很多性能。

拆分執(zhí)行時(shí)間長(zhǎng)的DELETE或INSERT語(yǔ)句

避免在生產(chǎn)環(huán)境上執(zhí)行會(huì)鎖表的DELETE或INSERT的操作。一定把其拆分，或者使用LIMIT條件也是一個(gè)好的方法。

拆分大SQL

下面是一個(gè)示例：