MySQL效能調優

對於全端而言，資料庫技能不可或缺，關係型資料庫或nosql，記憶體型資料庫或偏磁碟儲存的資料庫，對象儲存的資料庫或圖資料庫…林林總總,但是第一必備技能也應該是MySQL。從LAMP的興起，到Mariadb的出現，甚至PG的到來，熟練的MySQL技能都是大有用武之地的。

MySQL資料庫技術的各個方面也是很多，這裡只涉及必備的效能調優，推崇從下向上的效能調優，主要包括運行環境，配置參數，SQL效能，和系統架構設計調優。

運行環境調優

這裡是Linux的天下，MySQL 運行環境的調優往往和Linux的核心調優一併完成。當然了，對雲端服務RDS 也有一定的參考作用。

調整Linux預設的IO調度演算法.

IO調度器的總體目標是希望讓磁頭能夠總是往一個方向移動,移動到底了再往反方向走,這恰恰就是現實生活中的電梯模型,所以IO調度器也被叫做電梯(elevator)，而相應的算法也就被叫做電梯算法.而Linux中IO調度的電梯算法有好幾種,一個叫做as(Anticipatory),一個叫做cfq(Complete Fairness Queueing),一個叫做deadline,還有一個叫做noop(No Operation).

IO對資料庫的影響較大，linux預設的IO調度演算法為cfq,需要修改為deadline,如果是SSD或PCIe-SSD裝置,需要修改為noop,可以使用下面兩種修改方式。

1、線上動態修改,重啟失效。

echo “deadline” > /sys/block/sda/queue/scheduler

2、修改/etc/grub.conf,永久生效。

修改/etc/grub.conf設定檔,在kernel那行增加一個設定,例如:

elevator=deadline

主要關注elevator這個參數,設定核心的話需要重啟系統才能生效。

禁用numa特性

新一代架構的NUMA不適合跑數據庫,NUMA是為了內存利用率的提高,但反而可能導致一CPU的內存尚有剩餘,另外一個卻不夠用了,發生swap的問題,因此一般建議關閉或修改NUMA的調度。

numa=off

2、修改/etc/init.d/mysql或mysqld_safe腳本,設定啟動mysqld程序時的NUMA調度機制,如 numactl –interleave=all 。

修改swappiness設定

swappiness是linux的一個核心參數,用來控制物理記憶體交換出去的策略.它允許一個百分比的值,最小的為0 ,最大的為100,改值預設是60.這個設定值到底有什麼影響呢？

vm.swappiness設定為0表示盡量少使用swap,100表示​​盡量將inactive的記憶體頁交換到swap里或釋放cache。 inactive記憶體的意思是程式映射著，但是」長時間」不用的記憶體。我們可以利用vmstat查看系統內有多少inactive的記憶體。

# vmstat -a 1

這個值建議設定為1,設定方法如下,在/etc/sysctl.conf檔案中增加一行。

vm.swappiness = 1

擴大檔案描述子

這個是經常修改的參數,高並發的程式都會修改.

ulimit -n 51200

2、修改設定檔,永久生效。

在/etc/security/limits.conf配置文件中增加

* hardnofile 51200
 
* softnofile 51200

面向session的进程文件描述符的修改稍有不同，在云上的修改也略有差异，可以参见一样的“open too many files”

优化文件系统挂载参数。

对于文件系统，如无特殊要求，最好采用ext4.

文件系统挂载参数是在/etc/fstab文件中修改,重启时候生效。

noatime表示不记录访问时间,nodiratime不记录目录的访问时间。

barrier=0,表示关闭barrier功能.

barrier的主要目的是为了保证磁盘写数据的安全性,但是会降低性能。如果有BBU之类的电池备份电源保证控制卡不瞬间掉电,那么这个功能就可以放心大胆的关闭。

配置参数调优

my.cnf中的配置参数调优取决于业务，负载或硬件，在慢内存和快磁盘、高并发和写密集型负载情况下，都需要特殊的调整。

基本配置

query_cache_size

query cache是一个众所周知的瓶颈，甚至在并发并不多时也如此。 最 好是一开始就停用，设置query_cache_size = 0，并利用其他方法加速查询：优化索引、增加拷贝分散负载或者启用额外的缓存（比如memcache或redis）。如果已经启用了query cache并且还没有发现任何问题，query cache可能有用。如果想停用它，那就得小心了。

innodb_buffer_pool_size

缓冲池是数据和索引缓存的地方：这个值越大越好，这能保证你在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB(8GB内存)，20-25GB(32GB内存)，100-120GB(128GB内存)。

innodb_log_file_size

redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。从MySQL 5.5之后，崩溃恢复的性能的到了很大提升，可以同时拥有较高的写入性能和崩溃恢复性能。在MySQL 5.6里可以被提高到4GB以上。如果应用程序需要频繁的写入数据，可以一开始就把它这是成4G。

max_connections

max_connection值被设高了(例如1000或更高)之后一个主要缺陷是当服务器运行1000个或更高的活动事务时会变的没有响应。在应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题。

back_log

要求 mysql 能有的连接数量。当主要mysql线程在一个很短时间内得到非常多的连接请求，这就起作用，然后主线程花些时间检查连接并且启动一个新线程。back_log指明在mysql暂时停止回答新请求之前的短时间内多少个请求可以被存在堆栈中。只有如果期望在一个短时间内有很多连接，需要增加它，换句话说，该值对到来的tcp/ip连接的侦听队列的大小。

Innodb配置

innodb_file_per_table

這項設定告知InnoDB是否需要將所有表的資料和索引存放在共享表空間裡（innodb_file_per_table = OFF）或為每張表的資料單獨放在一個.ibd檔案（innodb_file_per_table = ON）。每張表一個檔案允許你在drop、truncate或rebuild表時回收磁碟空間。這對於一些高階特性也是有必要的，例如資料壓縮。但是它不會帶來任何性能收益。 MySQL 5.6中，這個屬性預設值是ON。

innodb_flush_log_at_trx_commit

預設值為1，表示InnoDB完全支援ACID特性。當關注點是資料安全的時候這個值是最適合的，例如在一個主節點上。但對於磁碟（讀寫）速度較慢的系統，它會帶來很大的開銷，因為每次將改變flush到redo日誌都需要額外的fsyncs。如果值為0速度就更快了，但在系統崩潰時可能丟失一些資料, 所以一遍只適用於備份節點。

innodb_flush_method

這項配置決定了資料和日誌寫入硬碟的方式。一般來說，如果你有硬體RAID控制器，並且其獨立快取採用write-back機制，並有著電池斷電保護，那麼應該設定配置為O_DIRECT；否則，大多數情況下應將其設為fdatasync（默認值）。 sysbench是一個可以幫助你決定這個選項的好工具。

innodb_log_buffer_size

這項配置決定了尚未執行的交易分配的快取。但是如果交易中包含有二進位大物件或大文字欄位的話，看Innodb_log_waits狀態變量，如果它不是0，增加innodb_log_buffer_size。

其他配置

log_bin

*​​&*如果資料庫伺服器充當主節點的備份節點，那麼開啟二進位日誌是必須的。就算只有一個伺服器，如果你想做基於時間點的資料恢復，這也是很有用的。二進制日誌一旦建立就將永久保存。如果不想讓磁碟空間耗盡，你可以用 PURGE BINARY LOGS 來清除舊文件，或設定 expire_logs_days 來指定多少天日誌將會自動清除。記錄二進位日誌不是沒有開銷的，所以如果你在一個非主節點的複製節點上不需要它的話，那麼建議關閉這個選項。

interactive_timeout

伺服器在關閉它之前在一個交互連接上等待行動的秒數。一個互動的客戶被定義為對 mysql_real_connect()使用 client_interactive 選項的客戶。 預設數值是28800，建議改為7200。

table_open_cache

MySQL每打開一個表，都會讀入一些資料到table_open_cache快取中，當MySQL在這個快取中找不到對應資訊時，才會去磁碟上讀取。假設系統有200個並發連接，則需將此參數設定為200*N(N為每個連接所需的檔案描述符數目)；當把table_open_cache設定為很大時，如果系統處理不了那麼多檔案描述符，那麼就會出現客戶端失效，連線不上。

max_allowed_pa​​cket

接受的資料包大小；增加該變數的值十分安全，這是因為僅當需要時才會分配額外記憶體。例如，只有當你發出長查詢或MySQLd必須傳回大的結果行時MySQLd才會分配更多記憶體。該變數之所以取較小預設值是一種預防措施，以捕獲客戶端和伺服器之間的錯誤訊息包，並確保不會因偶然使用大的信息包而導致內存溢出

skip_name_resolve

當客戶端連接資料庫伺服器時，且當DNS很慢時，建立連線也會很慢。因此建議在啟動伺服器時關閉skip_name_resolve選項而不進行DNS查找。

SQL 語句調優

在應用層，透過pt工具和慢查詢日誌的配合，可以輕鬆分辨出全表掃描的語句。

基本原則

• 避免全表掃描

• 建立索引

• 盡量避免向客戶端回傳大數據量，若資料量過大，應考慮相應需求是否合理

• 盡量避免大事務操作，提高系統並發能力

• 使用基於遊標的方法或臨時表方法之前，應先尋找基於集的解決方案來解決問題，基於集的方法通常更有效。盡量避免使用遊標，因為遊標的效率較差。

雕蟲小技

關於where 後的條件

• 應盡量避免在where 子句中使用!= 或<> 操作符，否則將引擎放棄使用索引而進行全表掃描。

• 應盡量避免在where 子句中使用or 來連接條件,可以考慮使用union 代替

• in 和not in 也要慎用，對於連續的數值，能用between 就不要用in，exists 代替in

  *​​&*
• 盡量避免在where 子句中對字段進行表達式操作和函數操作

關於資料類型

• 盡量使用數字型字段，若只含數值資訊的字段盡量不要設計為字元型，這會降低查詢和連接的效能，並會增加儲存開銷。

• 盡可能的使用varchar/nvarchar 代替char/nchar ，因為變長字段存儲空間小，對於查詢來說，在一個相對較小的字段內搜索效率顯然要高些。

• 最好不要給資料庫留NULL，盡可能的使用NOT NULL填充資料庫.備註、描述、評論之類的可以設定為NULL，其他的，最好不要使用NULL 。

• 任何地方都不要使用 select * from t ，用特定的字段列表代替“*”，不要返回用不到的任何字段。

關於臨時表

• 避免經常建立和刪除臨時表，以減少系統表資源的消耗。對於一次性事件， 最好使用匯出表。

• 在新臨時表時，如果一次性插入資料量很大，那麼可以使用select into 代替create table，避免造成大量log ，以提高速度；如果資料量不大，為了緩和系統表的資源，應先create table，然後insert。

• 如果使用到了臨時表，在最後將所有的臨時表明確刪除時，先 truncate table ，然後 drop table ，這樣可以避免系統表的較長時間鎖定。

關於索引

• 先應考慮在 where 及 order by 涉及的欄位上建立索引。

• 在使用索引欄位作為條件時，如果該索引是複合索引，那麼必須使用到該索引中的第一個欄位作為條件時才能保證系統使用該索引， 否則該索引將不會被使用， 並且應盡可能的讓字段順序與索引順序相一致。

• 索引並不是越多越好，索引固然可以提高相應的select 的效率，但同時也降低了insert和update 的效率，因為insert 或update 時有可能會重建索引，所以視具體情況而定。一個表的索引數最好不要超過7個，若太多則應考慮一些不常使用到的列上建的索引是否有必要.

資料庫架構調優

從底層來到了應用層，最終到架構層，然而脫離業務邏輯談架構就是耍流氓。資料庫架構同樣是依賴業務系統的，穩定且彈性地服務業務系統是關鍵。架構調優的方向有：

• 區分錶

• 業務分庫

• 主從同步與讀寫分離

• 資料緩存

• 主從熱備與HA雙活

• …..

 

 以上是MySQL效能調優的內容，更多相關內容請關注PHP中文網（www.php.cn）！