mysql server配置如何優化

小編給大家分享一下mysql server配置如何優化，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

在這之前，我們需要掌握以下方法：

查看MySql狀態及變量的方法：
Mysql> show status ——顯示狀態信息（擴展show status like XXX）
Mysql> show variables ——顯示系統變量（擴展show variables like XXX）
Mysql> show innodb status ——顯示InnoDB存儲引擎的狀態
Shell> admin variables -u username -p password——顯示系統變量
Shell> mysqladmin extended-status -u username -p password——顯示狀態信息
查看狀態變量及幫助：
Shell> mysqld --verbose --help [|more #逐行顯示]

首先，讓我們看看有關請求連接的變量：
為了能適應更多數據庫應用用戶，MySql提供了連接（客戶端）變量，以對不同性質的用戶群體提供不同的解決方案，筆者就max_connections，back_log做了一些細結，如下：

max_connections是指MySql的最大連接數，如果服務器的并發連接請求量比較大，建議調高此值，以增加并行連接數量，當然這建立在機器能支撐的情況下，因為如果連接數越多，介于MySql會為每個連接提供連接緩沖區，就會開銷越多的內存，所以要適當調整該值，不能盲目提高設值。可以過conn%通配符查看當前狀態的連接數量，以定奪該值的大小。

back_log是要求MySQL能有的連接數量。當主要MySQL線程在一個很短時間內得到非常多的連接請求，這就起作用，然后主線程花些時間(盡管很短)檢查連接并且啟動一個新線程。back_log值指出在MySQL暫時停止回答新請求之前的短時間內多少個請求可以被存在堆棧中。如果期望在一個短時間內有很多連接，你需要增加它。也就是說，如果MySql的連接數據達到max_connections時，新來的請求將會被存在堆棧中，以等待某一連接釋放資源，該堆棧的數量即back_log，如果等待連接的數量超過back_log，將不被授予連接資源。另外，這值（back_log）限于您的操作系統對到來的TCP/IP連接的偵聽隊列的大小。你的操作系統在這個隊列大小上有它自己的限制（可以檢查你的OS文檔找出這個變量的最大值），試圖設定back_log高于你的操作系統的限制將是無效的。

優化了MySql的連接后屬性后，我們需要看看緩沖區變量：

使用MySql數據庫存儲大量數據（或使用復雜查詢）時，我們應該考慮MySql的內存配置。如果配置MySQL服務器使用太少的內存會導致性能不是最優的；如果配置了太多的內存則會導致崩潰，無法執行查詢或者導致交換操作嚴重變慢。在現在的32位平臺下，仍有可能把所有的地址空間都用完，因此需要審視。

計算內存使用的秘訣公式就能相對地解決這一部分問題。不過，如今這個公式已經很復雜了，更重要的是，通過它計算得到的值只是“理論可能”并不是真正消耗的值。事實上，有8GB內存的常規服務器經常能運行到最大的理論值（100GB甚至更高）。此外，你輕易不會使用到“超額因素”（它實際上依賴于應用以及配置）。一些應用可能需要理論內存的10%而有些僅需1%。
那么，我們可以做什么呢？

來看看那些在啟動時就需要分配并且總是存在的全局緩沖吧！

全局緩沖：
key_buffer_size, innodb_buffer_pool_size, innodb_additional_mem_pool_size，innodb_log_buffer_size, query_cache_size

注：如果你大量地使用MyISAM表，那么你也可以增加操作系統的緩存空間使得MySQL也能用得著。把這些也都加到操作系統和應用程序所需的內存值之中，可能需要增加32MB甚至更多的內存給MySQL服務器代碼以及各種不同的小靜態緩沖。這些就是你需要考慮的在MySQL服務器啟動時所需的內存。其他剩下的內存用于連接。

key_buffer_size決定索引處理的速度，尤其是索引讀的速度。一般我們設為16M，通過檢查狀態值Key_read_requests和Key_reads，可以知道key_buffer_size設置是否合理。比例key_reads / key_read_requests應該盡可能的低，至少是1:100，1:1000更好（上述狀態值可以使用key_read%獲得用來顯示狀態數據）。key_buffer_size只對MyISAM表起作用。即使你不使用MyISAM表，但是內部的臨時磁盤表是MyISAM表，也要使用該值。可以使用檢查狀態值created_tmp_disk_tables得知詳情。

innodb_buffer_pool_size對于InnoDB表來說，作用就相當于key_buffer_size對于MyISAM表的作用一樣。InnoDB使用該參數指定大小的內存來緩沖數據和索引。對于單獨的MySQL數據庫服務器，最大可以把該值設置成物理內存的80%。

innodb_additional_mem_pool_size指定InnoDB用來存儲數據字典和其他內部數據結構的內存池大小。缺省值是1M。通常不用太大，只要夠用就行，應該與表結構的復雜度有關系。如果不夠用，MySQL會在錯誤日志中寫入一條警告信息。

innodb_log_buffer_size指定InnoDB用來存儲日志數據的緩存大小，如果您的表操作中包含大量并發事務（或大規模事務），并且在事務提交前要求記錄日志文件，請盡量調高此項值，以提高日志效率。

query_cache_size是MySql的查詢緩沖大小。（從4.0.1開始，MySQL提供了查詢緩沖機制）使用查詢緩沖，MySQL將SELECT語句和查詢結果存放在緩沖區中，今后對于同樣的SELECT語句（區分大小寫），將直接從緩沖區中讀取結果。根據MySQL用戶手冊，使用查詢緩沖最多可以達到238%的效率。通過檢查狀態值’Qcache_%’，可以知道query_cache_size設置是否合理：如果Qcache_lowmem_prunes的值非常大，則表明經常出現緩沖不夠的情況，如果Qcache_hits的值也非常大，則表明查詢緩沖使用非常頻繁，此時需要增加緩沖大小；如果Qcache_hits的值不大，則表明你的查詢重復率很低，這種情況下使用查詢緩沖反而會影響效率，那么可以考慮不用查詢緩沖。此外，在SELECT語句中加入SQL_NO_CACHE可以明確表示不使用查詢緩沖。

除了全局緩沖，MySql還會為每個連接發放連接緩沖。

連接緩沖：
每個連接到MySQL服務器的線程都需要有自己的緩沖。大概需要立刻分配256K，甚至在線程空閑時，它們使用默認的線程堆棧，網絡緩存等。事務開始之后，則需要增加更多的空間。運行較小的查詢可能僅給指定的線程增加少量的內存消耗，然而如果對數據表做復雜的操作例如掃描、排序或者需要臨時表，則需分配大約read_buffer_size，sort_buffer_size，read_rnd_buffer_size，tmp_table_size大小的內存空間。不過它們只是在需要的時候才分配，并且在那些操作做完之后就釋放了。有的是立刻分配成單獨的組塊。tmp_table_size 可能高達MySQL所能分配給這個操作的最大內存空間了。注意，這里需要考慮的不只有一點 —— 可能會分配多個同一種類型的緩存，例如用來處理子查詢。一些特殊的查詢的內存使用量可能更大——如果在MyISAM表上做成批的插入時需要分配 bulk_insert_buffer_size 大小的內存；執行 ALTER TABLE， OPTIMIZE TABLE， REPAIR TABLE 命令時需要分配 myisam_sort_buffer_size 大小的內存。

read_buffer_size是MySql讀入緩沖區大小。對表進行順序掃描的請求將分配一個讀入緩沖區，MySql會為它分配一段內存緩沖區。read_buffer_size變量控制這一緩沖區的大小。如果對表的順序掃描請求非常頻繁，并且你認為頻繁掃描進行得太慢，可以通過增加該變量值以及內存緩沖區大小提高其性能。

sort_buffer_size是MySql執行排序使用的緩沖大小。如果想要增加ORDER BY的速度，首先看是否可以讓MySQL使用索引而不是額外的排序階段。如果不能，可以嘗試增加sort_buffer_size變量的大小。

read_rnd_buffer_size是MySql的隨機讀緩沖區大小。當按任意順序讀取行時(例如，按照排序順序)，將分配一個隨機讀緩存區。進行排序查詢時，MySql會首先掃描一遍該緩沖，以避免磁盤搜索，提高查詢速度，如果需要排序大量數據，可適當調高該值。但MySql會為每個客戶連接發放該緩沖空間，所以應盡量適當設置該值，以避免內存開銷過大。

tmp_table_size是MySql的heap（堆積）表緩沖大小。所有聯合在一個DML指令內完成，并且大多數聯合甚至可以不用臨時表即可以完成。大多數臨時表是基于內存的(HEAP)表。具有大的記錄長度的臨時表 (所有列的長度的和)或包含BLOB列的表存儲在硬盤上。如果某個內部heap（堆積）表大小超過tmp_table_size，MySQL可以根據需要自動將內存中的heap表改為基于硬盤的MyISAM表。還可以通過設置tmp_table_size選項來增加臨時表的大小。也就是說，如果調高該值，MySql同時將增加heap表的大小，可達到提高聯接查詢速度的效果。

當我們設置好了緩沖區大小之后，再來看看：

table_cache所有線程打開的表的數目，增大該值可以增加mysqld需要的文件描述符的數量。每當MySQL訪問一個表時，如果在表緩沖區中還有空間，該表就被打開并放入其中，這樣可以更快地訪問表內容。通過檢查峰值時間的狀態值’Open_tables’和’Opened_tables’，可以決定是否需要增加table_cache的值。如果你發現open_tables等于table_cache，并且opened_tables在不斷增長，那么你就需要增加table_cache的值了（上述狀態值可以使用’Open%tables’獲得）。注意，不能盲目地把table_cache設置成很大的值。如果設置得太高，可能會造成文件描述符不足，從而造成性能不穩定或者連接失敗。

做了以上方面的調優設置之后，MySql應該基本能滿足您需求

以上是“mysql server配置如何優化”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！