數據庫的操作越來越成為整個應用的性能瓶頸，這對于Web應用尤其明顯。關于數據庫的性能，這并不只是DBA需要關心的，而更是后端開發需要去關注的事情。

所以本文講解MySQL在各個方面的優化方向，方便后端開發人員在調優和問題排查過程中找到切入點。

本文配置均為參考值，實際設置要參考基準測試或現場環境。

服務器硬件的優化

提升硬件設備，例如選擇盡量高頻率的內存（頻率不能高于主板的支持）、提升網絡帶寬、使用SSD高速磁盤、提升CPU性能等。

CPU的選擇:

對于數據庫并發比較高的場景，CPU的數量比頻率重要。
對于CPU密集型場景和頻繁執行復雜SQL的場景，CPU的頻率越高越好。

MySQL數據庫配置優化

表示緩沖池字節大小。
推薦值為物理內存的50%~80%。
innodb_buffer_pool_size
用來控制redo log刷新到磁盤的策略。
innodb_flush_log_at_trx_commit=1
每提交1次事務同步寫到磁盤中，可以設置為n。
sync_binlog=1
臟頁占innodb_buffer_pool_size的比例時，觸發刷臟頁到磁盤。推薦值為25%~50%。
innodb_max_dirty_pages_pct=30
后臺進程最大IO性能指標。
默認200，如果SSD，調整為5000~20000
innodb_io_capacity=200
指定innodb共享表空間文件的大小。
innodb_data_file_path
慢查詢日志的閾值設置，單位秒。
long_qurey_time=0.3
mysql復制的形式，row為MySQL8.0的默認形式。
binlog_format=row
調高該參數則應降低interactive_timeout、wait_timeout的值。
max_connections=200
過大，實例恢復時間長；過小，造成日志切換頻繁。
innodb_log_file_size
全量日志建議關閉。
默認關閉。
general_log=0

centos系統針對mysql的參數優化

本節只提及部分重要的參數，更全面的參數優化，可以查看這本書。

內核相關參數(/etc/sysctl.conf)

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

1.增加監聽隊列上限：

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連接接收和發送緩沖區大小的默認值和最大值:

net.core.wmem_default = 87380

net.core.wmem_max = 16777216

net.core.rmem_default = 87380

net.core.rmem_max = 16777216

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

net.ipv4.tcp_keepalive_time = 120

net.ipv4.tcp_keepalive_intvl = 30

net.ipv4.tcp_keepalive_probes = 3

5.單個共享內存段的最大值：

kernel.shmmax = 4294967295

這個參數應該設置的足夠大，以便能在一個共享內存段下容納整個的Innodb緩沖池的大小。
這個值的大小對于64位linux系統，可取的最大值為(物理內存值-1)byte，建議值為大于物理內存的一半，一般取值大于Innodb緩沖池的大小即可。

6.控制換出運行時內存的相對權重：

vm.swAppiness = 0

這個參數當內存不足時會對性能產生比較明顯的影響。（設置為0，表示Linux內核虛擬內存完全被占用，才會要使用交換區。）

Linux系統內存交換區：

在Linux系統安裝時都會有一個特殊的磁盤分區，稱之為系統交換分區。

使用 free -m 命令可以看到swap就是內存交換區。

作用：當操作系統沒有足夠的內存時，就會將部分虛擬內存寫到磁盤的交換區中，這樣就會發生內存交換。

如果Linux系統上完全禁用交換分區，帶來的風險：

降低操作系統的性能
容易造成內存溢出，崩潰，或都被操作系統kill掉

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

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

* soft nofile 65535

* hard nofile 65535

*：表示對所有用戶有效

soft：表示當前系統生效的設置（soft不能大于hard ）

hard：表明系統中所能設定的最大值

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

65535：限制的數量

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

注意：這個文件的修改需要重啟系統才能生效。

磁盤調度策略

1.cfq (完全公平隊列策略，Linux2.6.18之后內核的系統默認策略)

該模式按進程創建多個隊列，各個進程發來的IO請求會被cfq以輪循方式處理，對每個IO請求都是公平的。該策略適合離散讀的應用。

2.deadline (截止時間調度策略)

deadline，包含讀和寫兩個隊列，確保在一個截止時間內服務請求（截止時間是可調整的），而默認讀期限短于寫期限。這樣就防止了寫操作因為不能被讀取而餓死的現象，deadline對數據庫類應用是最好的選擇。

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

noop只實現一個簡單的FIFO隊列，傾向餓死讀而利于寫，因此noop對于閃存設備、RAM及嵌入式系統是最好的選擇。

4.anticipatory (預料I/O調度策略)

本質上與deadline策略一樣，但在最后一次讀操作之后，要等待6ms，才能繼續進行對其它I/O請求進行調度。它會在每個6ms中插入新的I/O操作，合并寫入流，用寫入延時換取最大的寫入吞吐量。anticipatory適合于寫入較多的環境，比如文件服務器。該策略對數據庫環境表現很差。

查看調度策略的方法：

cat /sys/block/devname/queue/scheduler

修改調度策略的方法：

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

MySQL的參數配置

請閱讀筆者的CSDN博客《MySQL服務器參數配置》，點擊跳轉。如無法跳轉，請查看原文即可查看。

MySQL表結構與SQL優化

索引優化規則

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

1.使用最左前綴規則

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

索引失效示例

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

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

如果是where code like 'A%'，就可以查找code中A開頭的數據，當碰到B開頭的數據時，就可以停止查找了，因為后面的數據一定不滿足要求，這樣可以提高查詢效率。

3.不要過多創建索引

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

在一般的互聯網場景中，查詢語句的執行次數遠遠大于增刪改語句的執行次數，所以重建索引的開銷可以忽略不計。但在大數據量導入時，可以考慮先刪除索引，批量插入數據，然后添加索引。

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

4.索引長度盡量短

短索引可以節省索引空間，使查找的速度得到提升，同時內存中也可以裝載更多的索引鍵值。

太長的列，可以選擇建立前綴索引

5.索引更新不能頻繁

更新非常頻繁的數據不適宜建索引，因為維護索引的成本。

6.索引列不能參與計算

不要在索引列上做任何的操作，包括計算、函數、自動或者手動類型的轉換，這樣都會導致索引失效。

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

查詢時的優化

小表驅動大表

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

建議使用left join時，以小表關聯大表，因為使用join的話，第一張表是必須全掃描的，以少關聯多就可以減少這個掃描次數.

這里所說的表的type，指的是explain執行計劃中的結果字段。詳情點擊查看，explain的屬性詳解與提速百倍的優化示例

避免全表掃描

mysql在使用不等于(!=或者<>)的時候無法使用導致全表掃描。在查詢的時候，如果對索引使用不等于的操作將會導致索引失效，進行全表掃描

避免mysql放棄索引查詢

如果mysql估計使用全表掃描要比使用索引快，則不使用索引。（最典型的場景就是數據量少的時候）

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

需要用到什么數據就查詢什么數據，這樣可以減少網絡的傳輸和mysql的全表掃描。

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

如果查詢select的列過多，覆蓋索引的效率會大大下降，這時可以考慮放棄覆蓋索引查詢。

order by的索引生效

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

不正確的使用導致索引失效

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

for update鎖表

A, B兩個事務分別使用select ... where ... for update進行查詢時：

A事務執行查詢操作的時候，如果這個查詢結果為空，無論where條件是否是索引字段，B事務執行查詢操作時，不會被阻塞。
A事務執行查詢操作的時候，當where條件是索引字段，則B事務執行同樣的查詢時會被行加鎖阻塞；當where條件不是索引字段，則B事務執行有結果集的查詢，都會被阻塞。

for update操作一定要謹慎，之前筆者就遇到過for update產生gap鎖，導致后續請求阻塞的問題。

之后的博客單獨介紹MySQL的鎖機制，同時講解下更多死鎖的情況。

其他優化

開啟慢查詢

開啟慢查詢日志，可以讓MySQL記錄下查詢超過指定時間的語句，通過定位分析性能的瓶頸，更好的優化數據庫系統的性能。

以后單獨的博客進行詳細的講解

實時獲取有性能問題的SQL

利用information_schema數據庫的processlist表，實時查看執行時間過長的線程，定位需要優化的SQL。

例如下面的SQL的作用是查看正在執行的線程，并按Time倒排序，查看執行時間過長的線程。

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

垂直分割

“垂直分割”是一種把數據庫中的表，按列變成幾張表的方法。這樣可以降低表的復雜度和字段的數目，從而達到優化的目的。

示例一：

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

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

示例二：

有一個叫 “last_login”的字段，它會在每次用戶登錄時被更新，每次更新時會導致該表的查詢緩存被清空。

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

這樣就不會影響對用戶ID、用戶名、用戶角色(假設這幾個屬性并不頻繁修改)的不停地讀取了，因為查詢緩存會增加很多性能。

拆分執行時間長的DELETE或INSERT語句

避免在生產環境上執行會鎖表的DELETE或INSERT的操作。一定把其拆分，或者使用LIMIT條件也是一個好的方法。

拆分大SQL

下面是一個示例：

while (1) {
 //每次只做1000條
 mysql_query("DELETE FROM logs WHERE log_date <= '2009-11-01' LIMIT 1000");
 if (mysql_affected_rows() == 0) {
 // 沒得可刪了，退出！
 break;
 }
 // 每次都要休息一會兒
 usleep(50000);
}

好書推薦

高性能MySQL

MySQL優化絕不是一篇博客就能講解全面的，所以筆者隆重推薦《高性能mysql》一書，如果沒看過的話，一定要看。

高性能MySQL

圖書簡介：

《高性能MySQL(第3版)》是MySQL領域的經典之作，擁有廣泛的影響力。第3 版更新了大量的內容，不但涵蓋了最新MySQL 5.5版本的新特性，也講述了關于固態盤、高可擴展性設計和云計算環境下的數據庫相關的新內容，原有的基準測試和性能優化部分也做了大量的擴展和補充。全書共分為16章和6個附錄，內容涵蓋MySQL架構和歷史，基準測試和性能剖析，數據庫軟硬件性能優化，復制、備份和恢復，高可用與高可擴展性，以及云端的mysql和mysql相關工具等方面的內容。每一章都是相對獨立的主題，讀者可以有選擇性地單獨閱讀。

阿里巴巴JAVA開發手冊

除了本文提到的索引規則，《阿里巴巴Java開發手冊》里面有關MySQL的規范接近50條，每一條也都值得琢磨。