mysql優化索引、配置，及慢查詢講解

下文內容主要給大家帶來 mysql優化索引、配置，及慢查詢講解，這里所講到的知識，與書籍略有不同，都是億速云專業技術人員在與用戶接觸過程中，總結出來的，具有一定的經驗分享價值，希望給廣大讀者帶來幫助。           

s首先基本的思路

1）性能瓶頸定位

使用show命令、

慢查詢日志、

explain分析查詢、

profiling分析查詢、

2）索引及查詢優化

3）配置優化

MySQL數據庫常見的兩個瓶頸cpu、i/o：

CPU主要在飽和的時候發生在數據裝入內存或磁盤上讀取數據的時候

i/o發生在裝入數據遠大于內存容量的時候，如果應用分布在網絡上，那么查詢量相當大的網絡瓶頸，我們可以通過mpstat、iostat、vmstat、sar等命令查看系統的性能狀態

例如：mpstat  3  3 {表示每3秒輸出三次}

另外除了云服務器硬件的性能瓶頸，對于mysql系統本身，我們可以使用工具來優化數據庫的性能；通常有三種：

使用索引、   使用explain分析查詢、   調整mysql的內部配置

1：查詢與索引優化分析；

在優化mysql時，通常需要對數據庫進行分析，常見的分析有慢查詢日志，explain分析查詢，profiling分析以及show命令查詢系統狀態及系統變量。

show命令

可以通過show命令查看mysql狀態以及變量，找到系統的瓶頸；

查看mysql服務器配置信息 、

查看mysql服務器運行的各種狀態、

顯示系統變量：=====>> mysqladmin variables -u username -ppassword

顯示系統狀態：======>> mysqladmin exlended -stautus -u username -ppassword

另外可以通過：

慢查詢日志

開啟慢查詢日志；在配置文件中/etc/my.cnf中添加三個參數；

slow_query_log=1 [1表示開啟、0表示關閉]

slow_query_log_file=/usr/local/mysql/data/slow-query.log 慢查旬日志存放位置

long-query_time=1 表示查詢超過1秒的時間記錄

在my.cnf中添加log-queries-not-using-indexes參數，表示向慢查詢日志中記錄下沒有使用索引的查詢

慢查詢日志也可以在命令行中開啟：

只不過在命令行中的屬于臨時生效，而在主配置文件中屬于永久生效

查看慢查詢的設置信息：

查看超時的時間限制

另外我們還可以同過查看慢查詢日志查看執行效率低的sql語句

可以看到剛才指定慢查詢文件的路徑這條命令的執行結果時間超過了0.01秒，所以也同樣被記錄了下來。

如果慢查詢日志當中的內容很多的話，可以使用mysqldmpslow對日志文件進行分類匯總，

具體使用方式可以通過mysqldmpslow --help查看使用的參數

如果有慢查詢的語句，那么該如何優化呢

一：對數據entertime列進行創建索引

二：優化這個sql查詢語句

使用mysqldumpslow命令可以非常明確得到各種我們需要的查詢語句；對mysql查詢語句的監控、分析、優化是mysql非常重要的一部分。

explain 分析查詢：

使用explain關鍵字可以模擬優化器執行sql查詢語句；從而知道mysql是如何處理sql語句的。可以分析查詢語句或表結構的性能瓶頸

EXPLAIN字段：

Table：顯示這一行的數據是關于哪張表的

type：這是最重要的字段之一，顯示查詢使用了何種類型。從最好到最差的連接類型為system、const、eq_reg、ref、range、index和ALL

possible_keys：顯示可能應用在這張表中的索引。如果為空，沒有可能的索引。

key：實際使用的索引。如果為NULL，則沒有使用索引。

key_len：使用的索引的長度。在不損失精確性的情況下，長度越短越好

ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數

rows：MySQL認為必須檢索的用來返回請求數據的行數

Extra：關于MYSQL如何解析查詢的額外信息

從上面的explain模擬優化器執行sql語句來看是沒有使用索引查詢的，而是全表掃描

從上面的explain模擬優化器執行sql語句看來沒有使用索引查詢，而是全表掃描

優化方法：

顯示結果說明該查詢語句使用了index_stuname索引查詢數據而非全表掃描。

profiling分析查詢：

通過慢日志查詢可以知道那些sql語句執行效率低，通過explain可以知道sql語句的具體執行情況；索引等，可以通過profiling命令得到更準確的sql執行消耗系統資源的信息。

profiling默認是關閉的，可通過查看的方式：

或者通過

打開profiling的功能：如下圖

接下來測試要執行的sql語句

status:是profile里的狀態，duration：是status狀態下的耗時。因此我們關注的就是那個狀態最耗時，這些狀態中那些可以優化。

當然也可以查看更多的信息如CPU等等

SHOW PROFILE [type [, type] ... ]  [FOR QUERY n]

type:
       ALL:顯示所有的開銷信息
      BLOCK IO:顯示塊IO相關開銷
      CPU:顯示用戶CPU時間、系統CPU時間
      IPC:顯示發送和接收相關開銷信息
       PAGE FAULTS:顯示頁面錯誤相關開銷信息
      SWAPS:顯示交換次數相關開銷的信息

測試完成之以后，記得要關閉調試功能，以免影響數據庫的正常使用：

mysql> set profiling=0;

2：配置優化：

mysql參數優化對不同的網站，及其線量，訪問量、帖子數量、網絡情況、以及硬件設備，都有關系，優化不可能一次性完成，需要不斷的觀察以及調試，才能達到最佳效果。

對性能影響比較大的分為鏈接請求的變量和緩沖區變量

1)連接請求的變量

max_connections

mysql的最大連接數；如果服務器的并發請求量比較大，建議調高此值，以增加并行連接數量，當然這是建立在服務器能夠支撐的情況之下，如果連接數越大，mysql回味每個連接提供連接緩沖區，這樣內存的開銷會提高。所以要適當的調整該值。不能盲目的提高。

但是如果數值過小的話會出現ERROR 1040：Too many connections的錯誤，可以通過以下的命令查看連接數啊

mysql>show variables like ‘max_connections’ 最大連接數

mysql>show  status like ‘max_used_connections’ 響應的連接數

max_used_connections / max_connections * 100% （理想值≈ 85%） 

max_used_connections/ max_connections * 100% {理想值=85%}

那么如何設置max_xonnections？

修改/etc/my.cnf文件，在【mysqld】下面添加如下內容，。如設置最大連接數為1024

max_connections=1024

之后重啟mysql服務

2：back_log

mysql能暫存的連接數量。當主要mysql線程在一個很短的時間內得到非常對的連接請求；它就會起作用，當mysql；的鏈接數達到max_connections是，新的請求將會被存放在堆棧當中。等待某一鏈接，釋放資源，該堆棧的數量即back_log，如果鏈接數量超過back_log，將不被授予鏈接資源

back_log值指出在mysql暫時停止回答新請求之前的短時間內段時間內有多少個請求可以被存在堆棧中，如果期望在一個短時間內與很多鏈接，你需要增加它

何設置back_log?

修改/etc/my.cnf文件，在[mysqld]下面添加如下內容，如設置最大連接數為1024

back_log = 數值

重啟mysql服務

3. wait_timeout和interactive_timeout

wait_timeout -- 指的是MySQL在關閉一個非交互的連接之前所要等待的秒數

interactive_time -- 指的是mysql在關閉一個交互的連接之前所要等待的秒數，比如我們在終端上進入mysql管理，使用的即使交互的連接，這時候，如果沒有操作的時間超過了interactive_time設置的時間就會自動斷開。默認數值是28800，可調優為7200。

對性能的影響：

wait_timeout：

（1）如果設置大小，那么連接關閉的很快，從而使一些持久的連接不起作用

（2）如果設置太大，容易造成連接打開時間過長，在show processlist時，能看到太多的sleep狀態的連接，從而造成too many connections錯誤

（3）一般希望wait_timeout盡可能地低

interactive_timeout的設置將要對你的web application沒有多大的影響

查看wait_timeout和interactive_timeout

mysql> show variables like '%wait_timeout%';

mysql> show variables like '%interactive_timeout%';

如何設置wait_timeout和interactive_timeout?

修改/etc/my.cnf文件，在[mysqld]下面添加如下內容

wait_timeout=100
interactive_timeout=100

重啟MySQL Server進入后，查看設置已經生效。

2）綬沖區變量

全局緩沖：

4.key_buffer_size

key_buffer_size指定索引緩沖區的大小，它決定索引處理的速度，尤其是索引讀的速度。通過檢查狀態值Key_read_requests和Key_reads，可以知道key_buffer_size設置是否合理。比例key_reads / key_read_requests應該盡可能的低，至少是1:100，1:1000更好（上述狀態值可以使用SHOW STATUS LIKE ‘key_read%’獲得）。

一共有6個索引讀取請求，有3個請求在內存中沒有找到直接從硬盤讀取索引，計算索引未命中緩存的概率： 
key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100% =50% 

key_buffer_size只對MyISAM表起作用。即使你不使用MyISAM表，但是內部的臨時磁盤表是MyISAM表，也要使用該值。可以使用檢查狀態值created_tmp_disk_tables得知詳情。

 如何調整key_buffer_size

默認配置數值是8388608(8M)，主機有4GB內存，可以調優值為268435456(256MB)

修改/etc/my.cnf文件，在[mysqld]下面添加如下內容

key_buffer_size=268435456或key_buffer_size=256M

重啟MySQL Server進入后，查看設置已經生效。

5.  query_cache_size(查詢緩存簡稱QC)

使用查詢緩沖，MySQL將查詢結果存放在緩沖區中，今后對于同樣的SELECT語句（區分大小寫），將直接從緩沖區中讀取結果。

一個SQL查詢如果以select開頭，那么MySQL服務器將嘗試對其使用查詢緩存。

注：兩個SQL語句，只要相差哪怕是一個字符（例如大小寫不一樣；多一個空格等），那么這兩個SQL將使用不同的一個CACHE。

通過檢查狀態值’Qcache%’，可以知道query_cache_size設置是否合理（上述狀態值可以使用SHOW STATUS LIKE ‘Qcache%’獲得）。

Qcache_free_blocks：緩存中相鄰內存塊的個數。如果該值顯示較大，則說明Query Cache 中的內存碎片較多了，FLUSH QUERY CACHE會對緩存中的碎片進行整理，從而得到一個空閑塊。 

注：當一個表被更新之后，和它相關的cache blocks將被free。但是這個block依然可能存在隊列中，除非是在隊列的尾部。可以用FLUSH QUERY CACHE語句來清空free blocks
Qcache_free_memory：Query Cache 中目前剩余的內存大小。通過這個參數我們可以較為準確的觀察出當前系統中的Query Cache 內存大小是否足夠，是需要增加還是過多了。
Qcache_hits：表示有多少次命中緩存。我們主要可以通過該值來驗證我們的查詢緩存的效果。數字越大，緩存效果越理想。
Qcache_inserts：表示多少次未命中然后插入，意思是新來的SQL請求在緩存中未找到，不得不執行查詢處理，執行查詢處理后把結果insert到查詢緩存中。這樣的情況的次數越多，表示查詢緩存應用到的比較少，效果也就不理想。當然系統剛啟動后，查詢緩存是空的，這很正常。
Qcache_lowmem_prunes：多少條Query 因為內存不足而被清除出Query Cache。通過“Qcache_lowmem_prunes”和“Qcache_free_memory”相互結合，能夠更清楚的了解到我們系統中Query Cache 的內存大小是否真的足夠，是否非常頻繁的出現因為內存不足而有Query 被換出。這個數字最好長時間來看；如果這個數字在不斷增長，就表示可能碎片非常嚴重，或者內存很少。（上面的free_blocks和free_memory可以告訴您屬于哪種情況） 
Qcache_not_cached：不適合進行緩存的查詢的數量，通常是由于這些查詢不是 SELECT 語句或者用了now()之類的函數。 
Qcache_queries_in_cache：當前Query Cache 中cache 的Query 數量； 
Qcache_total_blocks：當前Query Cache 中的block 數量；。 

我們再查詢一下服務器關于query_cache的配置：

上圖可以看出query_cache_type為ON表示緩存任何查詢

各字段的解釋：
query_cache_limit：超過此大小的查詢將不緩存 
query_cache_min_res_unit：緩存塊的最小大小 ，query_cache_min_res_unit的配置是一柄”雙刃劍”，默認是4KB，設置值大對大數據查詢有好處，但如果你的查詢都是小數據查詢，就容易造成內存碎片和浪費。
query_cache_size：查詢緩存大小 (注：QC存儲的最小單位是1024 byte，所以如果你設定了query_cache_type：緩存類型，決定緩存什么樣的查詢，注意這個值不能隨便設置，必須設置為數字，可選項目以及說明如下：

如果設置為0，那么可以說，你的緩存根本就沒有用，相當于禁用了。

如果設置為1，將會緩存所有的結果，除非你的select語句使用SQL_NO_CACHE禁用了查詢緩存。

如果設置為2，則只緩存在select語句中通過SQL_CACHE指定需要緩存的查詢。

修改/etc/my.cnf,配置完后的部分文件如下：

query_cache_size=256M

保存文件，重新啟動MYSQL服務，然后通過如下查詢來驗證開啟了：

query_cache_wlock_invalidate：當有其他客戶端正在對MyISAM表進行寫操作時，如果查詢在query cache中，是否返回cache結果還是等寫操作完成再讀表獲取結果。  

查詢緩存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100% 
如果查詢緩存碎片率超過20%，可以用FLUSH QUERY CACHE整理緩存碎片，或者試試減小query_cache_min_res_unit，如果你的查詢都是小數據量的話。 
查詢緩存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100% 
查詢緩存利用率在25%以下的話說明query_cache_size設置的過大，可適當減小；查詢緩存利用率在80％以上而且Qcache_lowmem_prunes > 50的話說明query_cache_size可能有點小，要不就是碎片太多。
查詢緩存命中率 = Qcache_hits/(Qcache_hits +Qcache_inserts) * 100% 

Query Cache 的限制

a) 所有子查詢中的外部查詢SQL 不能被Cache；
b) 在Procedure，Function 以及Trigger 中的Query 不能被Cache；
c) 包含其他很多每次執行可能得到不一樣結果的函數的Query不能被Cache。
鑒于上面的這些限制，在使用Query Cache 的過程中，建議通過精確設置的方式來使用，僅僅讓合適的表的數據可以進入Query Cache，僅僅讓某些Query的查詢結果被Cache。

如何設置query_cache_size？

修改/etc/my.cnf文件，在[mysqld]下面添加如下內容

query_cache_size=256M
query_cache_type=1

重啟MySQL Server進入后，查看設置已經生效。

6. max_connect_errors是一個MySQL中與安全有關的計數器值，它負責阻止過多嘗試失敗的客戶端以防止暴力破解密碼的情況, 當超過指定次數，MYSQL服務器將禁止host的連接請求，直到mysql服務器重啟或通過flush hosts命令清空此host的相關信息。max_connect_errors的值與性能并無太大關系。

修改/etc/my.cnf文件，在[mysqld]下面添加如下內容

max_connect_errors=20

重啟MySQL Server進入后，查看設置已經生效。

         MySQL性能優化————影響性能的因素

如果將mysql服務器比作一臺跑車，那么服務器硬件就好比發動機，引擎等公具，而里面的設施皮椅就可以比作MySQL的性能優化，只有兩者兼備才算的上是一個完整的跑車；

在這里我們主要針對的是對mysql的性能進行優化屬于剛才說的里面的設施。

包括連接數，查詢緩存等

MySQL影響性能的因素：

1.商業需求的影響

2.系統架構及實現的影響

1）二進制多媒體數據

2）超大文本數據

3：sql語句使用的不恰當，以及優化前后的變化

在數據庫中最主要的優化包括cpu、內存和磁盤i/o的優化

當然這些必須根據自身公司的服務器進行判斷：比如CPU可以支持多核，內存最小64GB、以上等等

例子：為查詢緩存優化你的查詢

大多數MySQL已經開啟了查詢緩存，這是最有效提高優化的方法之一；很多相同的查詢執行多次，被放到一個緩存當中，后續得到查詢就不用操作表而直接訪問緩存當中的數據

 query_cache_size(查詢緩存簡稱QC)

注：兩個SQL語句，只要相差哪怕是一個字符（例如大小寫不一樣；多一個空格等），那么這兩個SQL將使用不同的一個CACHE。

通過檢查狀態值’Qcache%’，可以知道query_cache_size設置是否合理（上述狀態值可以使用SHOW STATUS LIKE ‘Qcache%’獲得）。

Qcache_free_blocks：緩存中相鄰內存塊的個數。如果該值顯示較大，則說明Query Cache 中的內存碎片較多了，FLUSH QUERY CACHE會對緩存中的碎片進行整理，從而得到一個空閑塊。 

注：當一個表被更新之后，和它相關的cache blocks將被free。但是這個block依然可能存在隊列中，除非是在隊列的尾部。可以用FLUSH QUERY CACHE語句來清空free blocks
我們再查詢一下服務器關于query_cache的配置：

上圖可以看出query_cache_type為off表示不緩存任何查詢

各字段的解釋：
query_cache_limit：超過此大小的查詢將不緩存 
query_cache_min_res_unit：緩存塊的最小大小 ，query_cache_min_res_unit的配置是一柄”雙刃劍”，默認是4KB，設置值大對大數據查詢有好處，但如果你的查詢都是小數據查詢，就容易造成內存碎片和浪費。
query_cache_size：查詢緩存大小 (注：QC存儲的最小單位是1024 byte，所以如果你設定了一個不是1024的倍數的值，這個值會被四舍五入到最接近當前值的等于1024的倍數的值。)
query_cache_type：緩存類型，決定緩存什么樣的查詢，注意這個值不能隨便設置，必須設置為數字，可選項目以及說明如下：

如果設置為0，那么可以說，你的緩存根本就沒有用，相當于禁用了。

如果設置為1，將會緩存所有的結果，除非你的select語句使用SQL_NO_CACHE禁用了查詢緩存。

如果設置為2，則只緩存在select語句中通過SQL_CACHE指定需要緩存的查詢。

query_cache_wlock_invalidate：當有其他客戶端正在對MyISAM表進行寫操作時，如果查詢在query cache中，是否返回cache結果還是等寫操作完成再讀表獲取結果。 

修改/etc/my.cnf,配置完后的部分文件如下：

query_cache_size=256M
query_cache_type=1

保存文件，重新啟動MYSQL服務，然后通過如下查詢來驗證是否真正開啟了：

2：explain你的select查詢

幫助你查看你的mysql是如何處理的的sql語句，分析你的查詢語句或者表結構的性能瓶頸。

另外還會告訴你使用的是什么索引；數據表是如何被搜索的和排序的

explain分析查詢

使用 EXPLAIN 關鍵字可以模擬優化器執行SQL查詢語句，從而知道MySQL是如何處理你的SQL語句的。這可以幫你分析你的查詢語句或是表結構的性能瓶頸。通過explain命令可以得到:

> explain select * from test1.tb1 where stuname='admin'\G;

profiling分析查詢

通過慢日志查詢可以知道哪些SQL語句執行效率低下，通過explain我們可以得知SQL語句的具體執行情況，索引使用等，還可以結合show命令查看執行狀態。如果覺得explain的信息不夠詳細，可以同通過profiling命令得到更準確的SQL執行消耗系統資源的信息。

profiling默認是關閉的。可以通過以下語句查看

mysql> show variables like '%profiling%';    //off表示未開啟

打開profiling功能： mysql>set profiling=1; 執行需要測試的sql 語句：

mysql> select @@profiling;

+---------------------+

| @@profiling |

+---------------------+

|           1 |

+----------------------+

執行要測試的sql語句

mysql> select * from test1.tb1 where stuname='admin' and entertime='2016-9-1';

mysql> show profiles\G;   //可以得到被執行的SQL語句的時間和ID

*************************** 1. row ***************************

Query_ID: 1

Duration: 0.00012650

   Query: select @@profiling

*************************** 2. row ***************************

Query_ID: 2

Duration: 0.00121725

   Query: select * from test1.tb1 where stuname='admin' and entertime='2016-9-1'

mysql> show profile for query 2;  //得到對應SQL語句執行的詳細信息

+----------------------+-------------------------+

| Status              | Duration |

+----------------------+-------------------------+

| starting             | 0.000230 |

| checking permissions | 0.000013 |

| Opening tables       | 0.000030 |

| init                 | 0.000087 |

| System lock          | 0.000018 |

| optimizing           | 0.000128 |

| statistics           | 0.000378 |

| preparing            | 0.000026 |

| executing            | 0.000005 |

| Sending data         | 0.000187 |

| end                  | 0.000013 |

| query end            | 0.000011 |

| closing tables       | 0.000010 |

| freeing items        | 0.000061 |

| cleaning up          | 0.000021 |

+----------------------+-------------------------+

status:是profile里的狀態，duration：是status狀態下的耗時。因此我們關注的就是那個狀態最耗時，這些狀態中那些可以優化。

當然也可以查看更多的信息如CPU等等

SHOW PROFILE [type [, type] ... ][FOR QUERY n]

type:
       ALL:顯示所有的開銷信息
      BLOCK IO:顯示塊IO相關開銷
      CPU:顯示用戶CPU時間、系統CPU時間
      IPC:顯示發送和接收相關開銷信息
       PAGE FAULTS:顯示頁面錯誤相關開銷信息
      SWAPS:顯示交換次數相關開銷的信息

測試完成之以后，記得要關閉調試功能，以免影響數據庫的正常使用：

mysql> set profiling=0;

3:為搜索字段建立索引:

索引并不一定給主鍵或是字段建立，而是給經常需要查詢的目標建立，相當于字典的目錄，提速高效能。提高查詢效率，快速定位數據 

索引分為四種：

 CREATE INDEX indexName ON tablename(column1[,column2,……])

 全文索引 

只用于MyISAM 表 對文本域進行索引。字段類型包括char、varchar、text

不過切記對于大容量的數據表，生成全文索引是一個非常消耗時間非常消耗硬盤空間的做法。

CREATE FULLTEXT INDEX indexname ON tablename(column)

全局緩沖：索引緩存的大小

.key_buffer_size

key_buffer_size指定索引緩沖區的大小，它決定索引處理的速度，尤其是索引讀的速度。通過檢查狀態值Key_read_requests和Key_reads，可以知道key_buffer_size設置是否合理。

 如何調整key_buffer_size

默認配置數值是8388608(8M)，主機有4GB內存，可以調優值為268435456(256MB)

修改/etc/my.cnf文件，在[mysqld]下面添加如下內容

key_buffer_size=268435456或key_buffer_size=256M

innodb_buffer_pool_size的作用就相當于key_buffer_size對于MyISAM表的作用一樣。InnoDB使用該參數指定大小的內存來緩沖數據和索引。

4：避免select*的使用：

如果數據庫的數據過多的話使用‘*’增加查詢的時間增大cpu和i/o的負載，全表查詢而且速度慢，應該養成查詢的時候制定某一個字段，

5：選擇正確的存儲引擎：

myisam使用與查詢大量的的應用；有時一個update字段，可能導致全表鎖定，當然在count（*）這類計算的時候是速度非常快的因為有計數器

innodb復雜的存儲引擎支持行鎖，換支持事物，不適合count（*）

6:查看慢查詢日志：

慢查詢日志開啟：

在配置文件my.cnf中在[mysqld]一行下面加入3個配置參數，并重啟mysql服務

slow_query_log = 1   //0關閉  1開啟
slow_query_log_file = /usr/local/mysql/data/slow-query.log    //慢查詢日志存放地點

long_query_time = 1                               //表示查詢超過1秒才記錄                                

在my.cnf中添加log-queries-not-using-indexes參數，表示向慢查詢日志中記錄下沒有使用索引的查詢。

慢查詢日志開啟方法二：

我們也可以通過命令行設置變量來即時啟動慢日志查詢

mysql> set global slow_query_log = on;

mysql> set long_query_time =0.01;

mysql> set global slow_query_log_file = "/usr/local/mysql/data/slow-query.log";

查看慢查詢的設置信息

mysql> show variables like '%slow_query_log%';

mysql> show variables like '%long_query_time%';

我們可以通過打開log文件查看得知哪些SQL執行效率低下

[root@localhost data]# cat slow-query.log 

7：大批量數據的限制：

如果大批量的添加數據會導致查詢效率低，還有就是數據入庫的時間長，有時候會長達幾個小時

max_allowed_packet = 32M

MySQL根據配置文件會限制Server接受的數據包大小。有時候大的插入和更新會受 max_allowed_packet 參數限制，導致寫入或者更新失敗。最大值是1GB，必須設置1024的倍數。

8：關閉交互式：

比如當dba使用交互式的界面對數據庫進行增、改、刪、查之后，忘記了退出數據庫的交互式頁面，如果有人看見在上面進行操作修改數據，會為公司造成不可估量的損失，在這里我們可以通過只配置文件調整交互式存在的時間，防止其他人員進行操作；

另外也可以釋放一個用戶的鏈接數，增大一個鏈接數量

.wait_timeout和interactive_timeout

wait_timeout -- 指的是MySQL在關閉一個非交互的連接之前所要等待的秒數

interactive_time -- 指的是mysql在關閉一個交互的連接之前所要等待的秒數，比如我們在終端上進入mysql管理，使用的即使交互的連接，這時候，如果沒有操作的時間超過了interactive_time設置的時間就會自動斷開。默認數值是28800{8小時}，可調優為7200。

9：增大用戶鏈接數：

有時候突然之間數據庫的性能變慢；鏈接客戶需要好長的時間才能的到響應，甚至有時候收不到，客戶就會不斷的進行鏈接，這樣數據庫就更加的繁忙了，最后情況嚴重的話可能導致數據庫掛機，這里需要設置最大鏈接數量

1.max_connections

MySQL的最大連接數，如果服務器的并發連接請求量比較大，建議調高此值，以增加并行連接數量，當然這建立在機器能支撐的情況下，因為如果連接數越多， MySQL會為每個連接提供連接緩沖區，就會開銷越多的內存，所以要適當調整該值，不能盲目提高設值。

mysql>show variables like ‘max_connections’ 最大連接數

mysql>show  status like ‘max_used_connections’響應的連接數

max_used_connections / max_connections * 100% （理想值≈ 85%） 

如果max_used_connections跟max_connections相同那么就是max_connections設置過低或者超過服務器負載上限了，低于10%則設置過大。

如何設置max_connections?

修改/etc/my.cnf文件，在[mysqld]下面添加如下內容，如設置最大連接數為1024

max_connections = 1024

10：MySQL的堆棧設置：2.back_log

如果當鏈接數用戶過多，而且鏈接的最大數量不夠使用的時候可以設置堆棧，類似一個房間，講過多的鏈接先存放起來，等處理完之前的鏈接之后再處理房間里的鏈接，如果等待連接的數量超過back_log，將不被授予連接資源。

back_log值指出在MySQL暫時停止回答新請求之前的短時間內有多少個請求可以被存在堆棧中。只有如果期望在一個短時間內有很多連接，你需要增加它。

當觀察你主機進程列表（mysql> show full processlist），發現大量

xxxxx | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待連接進程時，就要加大back_log 的值了或加大max_connections的值。

通過mysql> show variables like 'back_log';查看back_log的設置

如何設置back_log?

修改/etc/my.cnf文件，在[mysqld]下面添加如下內容，如設置最大連接數為1024

back_log = 數值{1024}

重啟mysql服務

對于以上關于mysql優化索引、配置，及慢查詢講解，如果大家還有更多需要了解的可以持續關注我們億速云的行業推新，如需獲取專業解答，可在官網聯系售前售后的，希望該文章可給大家帶來一定的知識更新。