MySQL優化之連接優化範例程式碼

MySQL連線最佳化主要指客戶端連接資料庫以及資料庫為回應客戶端的請求而開啟資料表和索引的過程中涉及到的參數調整。下面我們來詳細的探討下

上文MySQL優化之快取優化這篇文章中提到了一個很重要的概念，就是show variables是用來表示系統編譯或設定在my.cnf中的變數值。而show status則稱為狀態值，顯示的是目前服務實例運行所具有的狀態訊息，是一個動態改變的值。因此常用來觀測目前MySQl的運作是否正常，如果不正常那麼依賴調整靜態參數來提高MySQL的效能。所以明白這兩個概念的不同，是後面調優的基礎。

MySQL 連線最佳化

記得有一次在公司內部連線MySQL的時候，總是連不上。找到DBA後，經過檢驗原因，是目前MySQL連線數滿了，經過調整後，解決了問題。引發連線數過多的錯誤原因通常有兩個，第一的確是有很多人在連接MySQL，造成連線數用完。第二種是max_connections值太小。

1、連線參數（show variables）

mysql> show variables like '%connect%';
+-----------------------------------------------+-----------------+
| Variable_name                 | Value      |
+-----------------------------------------------+-----------------+
| character_set_connection           | utf8      |
| collation_connection             | utf8_general_ci |
| connect_timeout                | 10       |
| disconnect_on_expired_password        | ON       |
| init_connect                 |         |
| max_connect_errors              | 100       |
| max_connections                | 151       |
| max_user_connections             | 0        |
| performance_schema_session_connect_attrs_size | 512       |
+-----------------------------------------------+-----------------+

max_connections是指MySQL服務實例能夠同時接受的的最大並發連線數。 MySQL其實支援最大連線數加一的演算法，保障當連線數用完的時候，超級管理員依然可以和服務端建立連接，進行管理。

max_user_connections設定指定帳號的最大並發連線數。

max_connect_errors 當某台非法主機惡意連線MySQL服務端，遭到的錯誤達到設定值後，MySQL會解決來自該主機的所有連線。但執行flush hosts後會清除。

2、連線狀態(show status)

有一點要注意的，變數值（show variables）是以小寫字母開頭的，而狀態值（show status）是以大寫字母開頭。這樣區分有助於記憶和分類

mysql> show status like '%connections%';
+-----------------------------------+-------+
| Variable_name           | Value |
+-----------------------------------+-------+
| Connection_errors_max_connections | 0   |
| Connections            | 197  |
| Max_used_connections       | 2   |
+-----------------------------------+-------+

Connection_errors_max_connections 當MySQL的最大並發數大於系統變數（show variables）中max_connections的最大並發數，因此而被拒絕的次數，將會記錄在這個變數裡。如果Connection_error_max_connections值比較大，則表示目前系統並發比較高，要考慮調大max_connections的值。

Connections表示MySQL從啟動至今，成功建立連線的連線數，這個值是不斷累積的。

Max_used_connections表示MySQL從啟動至今，同一時刻並發的連線數，取得是最大值。如果這個值大於 max_connections則表示系統經常處於高並發的狀態，應該考慮調大最大並發連線數。

3、連接線程參數（thread variabls and status）

mysql> show variables like 'thread%';
+--------------------+---------------------------+
| Variable_name   | Value           |
+--------------------+---------------------------+
| thread_cache_size | 9             |
| thread_concurrency | 10            |
| thread_handling  | one-thread-per-connection |
| thread_stack    | 262144          |
+--------------------+---------------------------+

thread_cache_size 設定連接線程緩存的數目。這個快取相當於MySQL執行緒的快取池（thread cache pool）,將空閒的連線執行緒放入連線池中快取起來，而非立即銷毀。當有新的連接請求時，如果連接池中有空閒的連接，則直接使用。否則要重新建立線程。創建線程是一個不小的系統開銷。 MySQL的這部分線程處理和Nginx 的線程處理有異曲同工之妙，以後介紹Nginx的線程處理時，會拿來做對比。

thread_handling 預設值是： one-thread-per-connection 表示為每個連線提供或建立一個執行緒來處理請求，直到請求完畢，連線銷毀或存入快取池。當值是no-threads 時，表示在總是只提供一個執行緒來處理連接，一般是單機做測試使用的。

thread_stack stack 是堆的意思，由PHP 進程詳解這篇博客，知道進程和線程都是有唯一的ID的，進程的ID系統會維護，二線程的ID ,則由具體的線程庫區維護，當進程或者線程休眠的時候，進程的上下文信息要在內存中開闢出一塊區域，保存進程的上下文信息，以便於迅速喚醒程序。預設為MySQL的每個執行緒設定的堆疊大小為：262144/1024=256k

查看執行緒狀態資訊

mysql> show status like 'Thread%';
+-------------------+-------+
| Variable_name   | Value |
+-------------------+-------+
| Threads_cached  | 1   |
| Threads_connected | 1   |
| Threads_created  | 2   |
| Threads_running  | 1   |
+-------------------+-------+

Thread_cached 目前執行緒池的執行緒數

Thread_connected 目前的連線數

Thread_cached: 当前连接线程创建数, 如果这个值过高，可以调整threadcachesize 也就是调整线程缓存池的大小。

Thred_runnint: 当前活跃的线程数。

连接请求堆栈

MySQL在很短的时间内，突然收到很多的连接请求时，MySQL会将不能来得及处理的连接请求保存在堆栈中，以便MySQL后续处理。back_log参数设置了堆栈的大小，可以通过如下命令查看：

mysql> show variables like 'back_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| back_log   | 80  |
+---------------+-------+

连接异常

mysql> show status like 'Aborted%';
+------------------+-------+
| Variable_name  | Value |
+------------------+-------+
| Aborted_clients | 0   |
| Aborted_connects | 219  |
+------------------+-------+

Aborted_clients MySQL 客户机被异常关闭的次数。

Aborted_connects 试图连接到MySQL服务器而失败的连接次数。

other

mysql> show status like 'Slow%';
+---------------------+-------+
| Variable_name    | Value |
+---------------------+-------+
| Slow_launch_threads | 0   |
| Slow_queries    | 0   |
+---------------------+-------+

mysql> show variables like 'slow_launch_time';
+------------------+-------+
| Variable_name  | Value |
+------------------+-------+
| slow_launch_time | 2   |
+------------------+-------+

Slow_lunch_threads 创建线程的时间过长，超过slow_launch_time的设定值，则会记录。

可以通过使用 Connection_error%来查看连接的错误状态信息：

mysql> show status like 'Connection_error%';
+-----------------------------------+-------+
| Variable_name           | Value |
+-----------------------------------+-------+
| Connection_errors_accept     | 0   |
| Connection_errors_internal    | 0   |
| Connection_errors_max_connections | 0   |
| Connection_errors_peer_address  | 0   |
| Connection_errors_select     | 0   |
| Connection_errors_tcpwrap     | 0   |
+-----------------------------------+-------+

Connection_errors_peer_address 查找MySQL客户机IP地址是发生的错误数。

以上是MySQL優化之連接優化範例程式碼的詳細內容。更多資訊請關注PHP中文網其他相關文章！