mysql server配置如何優(yōu)化

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

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

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

首先，讓我們看看有關(guān)請(qǐng)求連接的變量：
為了能適應(yīng)更多數(shù)據(jù)庫應(yīng)用用戶，MySql提供了連接（客戶端）變量，以對(duì)不同性質(zhì)的用戶群體提供不同的解決方案，筆者就max_connections，back_log做了一些細(xì)結(jié)，如下：

max_connections是指MySql的最大連接數(shù)，如果服務(wù)器的并發(fā)連接請(qǐng)求量比較大，建議調(diào)高此值，以增加并行連接數(shù)量，當(dāng)然這建立在機(jī)器能支撐的情況下，因?yàn)槿绻B接數(shù)越多，介于MySql會(huì)為每個(gè)連接提供連接緩沖區(qū)，就會(huì)開銷越多的內(nèi)存，所以要適當(dāng)調(diào)整該值，不能盲目提高設(shè)值?？梢赃^conn%通配符查看當(dāng)前狀態(tài)的連接數(shù)量，以定奪該值的大小。

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

優(yōu)化了MySql的連接后屬性后，我們需要看看緩沖區(qū)變量：

使用MySql數(shù)據(jù)庫存儲(chǔ)大量數(shù)據(jù)（或使用復(fù)雜查詢）時(shí)，我們應(yīng)該考慮MySql的內(nèi)存配置。如果配置MySQL服務(wù)器使用太少的內(nèi)存會(huì)導(dǎo)致性能不是最優(yōu)的；如果配置了太多的內(nèi)存則會(huì)導(dǎo)致崩潰，無法執(zhí)行查詢或者導(dǎo)致交換操作嚴(yán)重變慢。在現(xiàn)在的32位平臺(tái)下，仍有可能把所有的地址空間都用完，因此需要審視。

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

來看看那些在啟動(dòng)時(shí)就需要分配并且總是存在的全局緩沖吧！

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

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

key_buffer_size決定索引處理的速度，尤其是索引讀的速度。一般我們?cè)O(shè)為16M，通過檢查狀態(tài)值Key_read_requests和Key_reads，可以知道key_buffer_size設(shè)置是否合理。比例key_reads / key_read_requests應(yīng)該盡可能的低，至少是1:100，1:1000更好（上述狀態(tài)值可以使用key_read%獲得用來顯示狀態(tài)數(shù)據(jù)）。key_buffer_size只對(duì)MyISAM表起作用。即使你不使用MyISAM表，但是內(nèi)部的臨時(shí)磁盤表是MyISAM表，也要使用該值?？梢允褂脵z查狀態(tài)值created_tmp_disk_tables得知詳情。

innodb_buffer_pool_size對(duì)于InnoDB表來說，作用就相當(dāng)于key_buffer_size對(duì)于MyISAM表的作用一樣。InnoDB使用該參數(shù)指定大小的內(nèi)存來緩沖數(shù)據(jù)和索引。對(duì)于單獨(dú)的MySQL數(shù)據(jù)庫服務(wù)器，最大可以把該值設(shè)置成物理內(nèi)存的80%。

innodb_additional_mem_pool_size指定InnoDB用來存儲(chǔ)數(shù)據(jù)字典和其他內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存池大小。缺省值是1M。通常不用太大，只要夠用就行，應(yīng)該與表結(jié)構(gòu)的復(fù)雜度有關(guān)系。如果不夠用，MySQL會(huì)在錯(cuò)誤日志中寫入一條警告信息。

innodb_log_buffer_size指定InnoDB用來存儲(chǔ)日志數(shù)據(jù)的緩存大小，如果您的表操作中包含大量并發(fā)事務(wù)（或大規(guī)模事務(wù)），并且在事務(wù)提交前要求記錄日志文件，請(qǐng)盡量調(diào)高此項(xiàng)值，以提高日志效率。

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

除了全局緩沖，MySql還會(huì)為每個(gè)連接發(fā)放連接緩沖。

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

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

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

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

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

當(dāng)我們?cè)O(shè)置好了緩沖區(qū)大小之后，再來看看：

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

做了以上方面的調(diào)優(yōu)設(shè)置之后，MySql應(yīng)該基本能滿足您需求

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