1、目的：

通過根據(jù)服務(wù)器目前狀況刽虹，修改Mysql的系統(tǒng)參數(shù)酗捌，達(dá)到合理利用服務(wù)器現(xiàn)有資源，最大合理的提高M(jìn)ySQL性能涌哲。

2胖缤、服務(wù)器參數(shù)：

32G內(nèi)存、4個CPU,每個CPU?8核阀圾。

3哪廓、MySQL目前安裝狀況。

????MySQL目前安裝初烘，用的是MySQL默認(rèn)的最大支持配置涡真。拷貝的是my-huge.cnf.編碼已修改為UTF-8.具體修改及安裝MySQL,可以參考<<Linux系統(tǒng)上安裝MySQL?5.5>>幫助文檔肾筐。

4哆料、修改MySQL配置

打開MySQL配置文件my.cnf

vi??/etc/my.cnf

4.1?MySQL非緩存參數(shù)變量介紹及修改

4.1.1修改back_log參數(shù)值:由默認(rèn)的50修改為500.（每個連接256kb,占用：125M）

??????????back_log=500

????back_log值指出在MySQL暫時停止回答新請求之前的短時間內(nèi)多少個請求可以被存在堆棧中。也就是說吗铐，如果MySql的連接數(shù)據(jù)達(dá)到max_connections時东亦，新來的請求將會被存在堆棧中，以等待某一連接釋放資源唬渗，該堆棧的數(shù)量即back_log讥此，如果等待連接的數(shù)量超過back_log，將不被授予連接資源谣妻。將會報：unauthenticated?user?|?xxx.xxx.xxx.xxx?|?NULL?|?Connect?|?NULL?|?login?|?NULL?的待連接進(jìn)程時.

back_log值不能超過TCP/IP連接的偵聽隊列的大小萄喳。若超過則無效，查看當(dāng)前系統(tǒng)的TCP/IP連接的偵聽隊列的大小命令：cat?/proc/sys/net/ipv4/tcp_max_syn_backlog目前系統(tǒng)為1024蹋半。對于Linux系統(tǒng)推薦設(shè)置為小于512的整數(shù)他巨。

修改系統(tǒng)內(nèi)核參數(shù)，）http://www.51testing.com/html/64/n-810764.html

查看mysql?當(dāng)前系統(tǒng)默認(rèn)back_log值减江，命令：

show?variables?like?'back_log';?查看當(dāng)前數(shù)量

4.1.2修改wait_timeout參數(shù)值染突，由默認(rèn)的8小時，修改為30分鐘辈灼。(本次不用)

??????????wait_timeout=1800（單位為妙）

我對wait-timeout這個參數(shù)的理解：MySQL客戶端的數(shù)據(jù)庫連接閑置最大時間值份企。

說得比較通俗一點，就是當(dāng)你的MySQL連接閑置超過一定時間后將會被強行關(guān)閉巡莹。MySQL默認(rèn)的wait-timeout??值為8個小時司志，可以通過命令show?variables?like?'wait_timeout'查看結(jié)果值;甜紫。

設(shè)置這個值是非常有意義的，比如你的網(wǎng)站有大量的MySQL鏈接請求（每個MySQL連接都是要內(nèi)存資源開銷的?）骂远，由于你的程序的原因有大量的連接請求空閑啥事也不干囚霸，白白占用內(nèi)存資源，或者導(dǎo)致MySQL超過最大連接數(shù)從來無法新建連接導(dǎo)致“Too?many?connections”的錯誤激才。在設(shè)置之前你可以查看一下你的MYSQL的狀態(tài)（可用show?processlist)拓型，如果經(jīng)常發(fā)現(xiàn)MYSQL中有大量的Sleep進(jìn)程，則需要?修改wait-timeout值了瘸恼。

interactive_timeout：服務(wù)器關(guān)閉交互式連接前等待活動的秒數(shù)劣挫。交互式客戶端定義為在mysql_real_connect()中使用CLIENT_INTERACTIVE選項的客戶端。

wait_timeout:服務(wù)器關(guān)閉非交互連接之前等待活動的秒數(shù)东帅。在線程啟動時揣云，根據(jù)全局wait_timeout值或全局?interactive_timeout值初始化會話wait_timeout值，取決于客戶端類型(由mysql_real_connect()的連接選項CLIENT_INTERACTIVE定義).

這兩個參數(shù)必須配合使用冰啃。否則單獨設(shè)置wait_timeout無效

4.1.3修改max_connections參數(shù)值，由默認(rèn)的151刘莹，修改為3000（750M）阎毅。

????max_connections=3000

max_connections是指MySql的最大連接數(shù)，如果服務(wù)器的并發(fā)連接請求量比較大点弯，建議調(diào)高此值扇调，以增加并行連接數(shù)量，當(dāng)然這建立在機器能支撐的情況下抢肛，因為如果連接數(shù)越多狼钮，介于MySql會為每個連接提供連接緩沖區(qū)，就會開銷越多的內(nèi)存捡絮，所以要適當(dāng)調(diào)整該值熬芜，不能盲目提高設(shè)值「Ｎ龋可以過'conn%'通配符查看當(dāng)前狀態(tài)的連接數(shù)量涎拉，以定奪該值的大小。

MySQL服務(wù)器允許的最大連接數(shù)16384的圆；

查看系統(tǒng)當(dāng)前最大連接數(shù)：

show?variables?like?'max_connections';

4.1..4修改max_user_connections值鼓拧，由默認(rèn)的0，修改為800

?????max_user_connections=800

?max_user_connections是指每個數(shù)據(jù)庫用戶的最大連接

針對某一個賬號的所有客戶端并行連接到MYSQL服務(wù)的最大并行連接數(shù)越妈。簡單說是指同一個賬號能夠同時連接到mysql服務(wù)的最大連接數(shù)季俩。設(shè)置為0表示不限制。

目前默認(rèn)值為：0不受限制梅掠。

這兒順便介紹下Max_used_connections:它是指從這次mysql服務(wù)啟動到現(xiàn)在酌住，同一時刻并行連接數(shù)的最大值店归。它不是指當(dāng)前的連接情況，而是一個比較值赂韵。如果在過去某一個時刻娱节，MYSQL服務(wù)同時有1000個請求連接過來，而之后再也沒有出現(xiàn)這么大的并發(fā)請求時祭示，則Max_used_connections=1000.請注意與show?variables?里的max_user_connections的區(qū)別肄满。默認(rèn)為0表示無限大。

查看max_user_connections值

show?variables?like?'max_user_connections';

4.1.5修改thread_concurrency值质涛，由目前默認(rèn)的8稠歉，修改為64

?????thread_concurrency=64

thread_concurrency的值的正確與否,?對mysql的性能影響很大,?在多個cpu(或多核)的情況下，錯誤設(shè)置了thread_concurrency的值,?會導(dǎo)致mysql不能充分利用多cpu(或多核),?出現(xiàn)同一時刻只能一個cpu(或核)在工作的情況汇陆。

thread_concurrency應(yīng)設(shè)為CPU核數(shù)的2倍.?比如有一個雙核的CPU,?那thread_concurrency??的應(yīng)該為4;?2個雙核的cpu,?thread_concurrency的值應(yīng)為8.

比如：根據(jù)上面介紹我們目前系統(tǒng)的配置怒炸，可知道為4個CPU,每個CPU為8核，按照上面的計算規(guī)則毡代，這兒應(yīng)為:4*8*2=64

查看系統(tǒng)當(dāng)前thread_concurrency默認(rèn)配置命令：

?show?variables?like?'thread_concurrency';

4.1.6添加skip-name-resolve阅羹，默認(rèn)被注釋掉狗热，沒有該參數(shù)躯喇。

skip-name-resolve

skip-name-resolve：禁止MySQL對外部連接進(jìn)行DNS解析砚尽，使用這一選項可以消除MySQL進(jìn)行DNS解析的時間马篮。但需要注意绩鸣，如果開啟該選項命迈，則所有遠(yuǎn)程主機連接授權(quán)都要使用IP地址方式膳凝，否則MySQL將無法正常處理連接請求控妻！

4.1.7?skip-networking迂烁，默認(rèn)被注釋掉看尼。沒有該參數(shù)。（本次無用）

?skip-networking建議被注釋掉盟步，不要開啟

開啟該選項可以徹底關(guān)閉MySQL的TCP/IP連接方式藏斩，如果WEB服務(wù)器是以遠(yuǎn)程連接的方式訪問MySQL數(shù)據(jù)庫服務(wù)器則不要開啟該選項！否則將無法正常連接却盘！

4.1.8??default-storage-engine(設(shè)置MySQL的默認(rèn)存儲引擎)

default-storage-engine=?InnoDB(設(shè)置InnoDB類型灾茁，另外還可以設(shè)置MyISAM類型)

設(shè)置創(chuàng)建數(shù)據(jù)庫及表默認(rèn)存儲類型

show?table?status?like?‘tablename’顯示表的當(dāng)前存儲狀態(tài)值

查看MySQL有哪些存儲狀態(tài)及默認(rèn)存儲狀態(tài)

?show?engines;

創(chuàng)建表并指定存儲類型

CREATE?TABLE?mytable?(id?int,?title?char(20))?ENGINE?=?INNODB;

修改表存儲類型：

??Alter?table?tableName?engine?=engineName

備注：設(shè)置完后把以下幾個開啟：

#?Uncomment?the?following?if?you?are?using?InnoDB?tables

innodb_data_home_dir?=?/var/lib/mysql

#innodb_data_file_path?=?ibdata1:1024M;ibdata2:10M:autoextend（要注釋掉，否則會創(chuàng)建一個新的把原來的替換的谷炸。）

innodb_log_group_home_dir?=?/var/lib/mysql

#?You?can?set?.._buffer_pool_size?up?to?50?-?80?%

#?of?RAM?but?beware?of?setting?memory?usage?too?high

innodb_buffer_pool_size?=?1000M

innodb_additional_mem_pool_size?=?20M

#?Set?.._log_file_size?to?25?%?of?buffer?pool?size

innodb_log_file_size?=?500M

innodb_log_buffer_size?=?20M

innodb_flush_log_at_trx_commit?=?0

innodb_lock_wait_timeout?=?50

設(shè)置完后一定記得把MySQL安裝目錄地址（我們目前是默認(rèn)安裝所以地址/var/lib/mysql/）下的ib_logfile0和ib_logfile1刪除掉北专。否則重啟MySQL起動失敗。

4.2?MySQL緩存變量介紹及修改

數(shù)據(jù)庫屬于IO密集型的應(yīng)用程序旬陡，其主職責(zé)就是數(shù)據(jù)的管理及存儲工作拓颓。而我們知道，從內(nèi)存中讀取一個數(shù)據(jù)庫的時間是微秒級別描孟，而從一塊普通硬盤上讀取一個?IO是在毫秒級別驶睦，二者相差3個數(shù)量級砰左。所以，要優(yōu)化數(shù)據(jù)庫场航，首先第一步需要優(yōu)化的就是IO缠导，盡可能將磁盤IO轉(zhuǎn)化為內(nèi)存IO。本文先從MySQL數(shù)據(jù)庫?IO相關(guān)參數(shù)(緩存參數(shù))的角度來看看可以通過哪些參數(shù)進(jìn)行IO優(yōu)化

4.2.1全局緩存

啟動MySQL時就要分配并且總是存在的全局緩存溉痢。目前有：key_buffer_size(默認(rèn)值：402653184,即384M)僻造、innodb_buffer_pool_size(默認(rèn)值：134217728即：128M)、innodb_additional_mem_pool_size（默認(rèn)值：8388608即：8M）孩饼、innodb_log_buffer_size(默認(rèn)值：8388608即：8M)髓削、query_cache_size(默認(rèn)值：33554432即：32M)等五個《迫ⅲ總共：560M.

這些變量值都可以通過命令如：show?variables?like?'變量名';查看到立膛。

4.2.1.1：key_buffer_size,本系統(tǒng)目前為384M,可修改為400M

????key_buffer_size=400M

????key_buffer_size是用于索引塊的緩沖區(qū)大小，增加它可得到更好處理的索引(對所有讀和多重寫)梯码，對MyISAM(MySQL表存儲的一種類型宝泵，可以百度等查看詳情)表性能影響最大的一個參數(shù)。如果你使它太大轩娶，系統(tǒng)將開始換頁并且真的變慢了儿奶。嚴(yán)格說是它決定了數(shù)據(jù)庫索引處理的速度，尤其是索引讀的速度罢坝。對于內(nèi)存在4GB左右的服務(wù)器該參數(shù)可設(shè)置為256M或384M.

怎么才能知道key_buffer_size的設(shè)置是否合理呢，一般可以檢查狀態(tài)值Key_read_requests和Key_reads???搅窿，比例key_reads?/?key_read_requests應(yīng)該盡可能的低嘁酿，比如1:100，1:1000?男应，1:10000闹司。其值可以用以下命令查得：show?status?like?'key_read%';

比如查看系統(tǒng)當(dāng)前key_read和key_read_request值為：

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

|?Variable_name?????|?Value?|

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

|?Key_read_requests?|?28535?|

|?Key_reads?????????|?269???|

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

可知道有28535個請求，有269個請求在內(nèi)存中沒有找到直接從硬盤讀取索引.

未命中緩存的概率為：0.94%=269/28535*100%.??一般未命中概率在0.1之下比較好沐飘。目前已遠(yuǎn)遠(yuǎn)大于0.1游桩，證明效果不好。若命中率在0.01以下耐朴，則建議適當(dāng)?shù)男薷膋ey_buffer_size值借卧。

http://dbahacker.com/mysql/innodb-myisam-compare(InnoDB與MyISAM的六大區(qū)別)

http://kb.cnblogs.com/page/99810/（查看存儲引擎介紹）

MyISAM、InnoDB筛峭、MyISAM?Merge引擎铐刘、InnoDB、memory(heap)影晓、archive

4.2.1.2：innodb_buffer_pool_size(默認(rèn)128M)

innodb_buffer_pool_size=1024M(1G)

???innodb_buffer_pool_size:主要針對InnoDB表性能影響最大的一個參數(shù)镰吵。功能與Key_buffer_size一樣檩禾。InnoDB占用的內(nèi)存，除innodb_buffer_pool_size用于存儲頁面緩存數(shù)據(jù)外疤祭，另外正常情況下還有大約8%的開銷盼产，主要用在每個緩存頁幀的描述、adaptive?hash等數(shù)據(jù)結(jié)構(gòu)勺馆，如果不是安全關(guān)閉戏售，啟動時還要恢復(fù)的話，還要另開大約12%的內(nèi)存用于恢復(fù)谓传，兩者相加就有差不多21%的開銷蜈项。假設(shè)：12G的innodb_buffer_pool_size，最多的時候InnoDB就可能占用到14.5G的內(nèi)存续挟。若系統(tǒng)只有16G紧卒，而且只運行MySQL，且MySQL只用InnoDB诗祸，

那么為MySQL開12G跑芳，是最大限度地利用內(nèi)存了。

另外InnoDB和?MyISAM?存儲引擎不同直颅，?MyISAM?的?key_buffer_size?只能緩存索引鍵博个，而?innodb_buffer_pool_size?卻可以緩存數(shù)據(jù)塊和索引鍵。適當(dāng)?shù)脑黾舆@個參數(shù)的大小功偿，可以有效的減少?InnoDB?類型的表的磁盤?I/O?盆佣。

當(dāng)我們操作一個?InnoDB?表的時候，返回的所有數(shù)據(jù)或者去數(shù)據(jù)過程中用到的任何一個索引塊械荷，都會在這個內(nèi)存區(qū)域中走一遭共耍。

可以通過?(Innodb_buffer_pool_read_requests?–?Innodb_buffer_pool_reads)?/?Innodb_buffer_pool_read_requests?*?100%?計算緩存命中率，并根據(jù)命中率來調(diào)整?innodb_buffer_pool_size?參數(shù)大小進(jìn)行優(yōu)化吨瞎。值可以用以下命令查得：show?status?like?'Innodb_buffer_pool_read%';

比如查看當(dāng)前系統(tǒng)中系統(tǒng)中

|?Innodb_buffer_pool_read_requests??????|?1283826?|

|?Innodb_buffer_pool_reads??????????????|?519?????|

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

其命中率99.959%=（1283826-519）/1283826*100%??命中率越高越好痹兜。

4.2.1.3：innodb_additional_mem_pool_size(默認(rèn)8M)

??innodb_additional_mem_pool_size=20M

?????innodb_additional_mem_pool_size?設(shè)置了InnoDB存儲引擎用來存放數(shù)據(jù)字典信息以及一些內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存空間大小，所以當(dāng)我們一個MySQL?Instance中的數(shù)據(jù)庫對象非常多的時候颤诀，是需要適當(dāng)調(diào)整該參數(shù)的大小以確保所有數(shù)據(jù)都能存放在內(nèi)存中提高訪問效率的字旭。

這個參數(shù)大小是否足夠還是比較容易知道的，因為當(dāng)過小的時候崖叫，MySQL會記錄Warning信息到數(shù)據(jù)庫的error?log中遗淳，這時候你就知道該調(diào)整這個參數(shù)大小了。

查看當(dāng)前系統(tǒng)mysql的error日志??cat??/var/lib/mysql/機器名.error?發(fā)現(xiàn)有很多waring警告心傀。所以要調(diào)大為20M.

根據(jù)MySQL手冊洲脂，對于2G內(nèi)存的機器，推薦值是20M。

????32G內(nèi)存的?100M

4.2.1.4：innodb_log_buffer_size(默認(rèn)8M)

innodb_log_buffer_size=20M

????innodb_log_buffer_size??這是InnoDB存儲引擎的事務(wù)日志所使用的緩沖區(qū)恐锦。類似于Binlog?Buffer往果，InnoDB在寫事務(wù)日志的時候，為了提高性能一铅，也是先將信息寫入Innofb?Log?Buffer中陕贮，當(dāng)滿足innodb_flush_log_trx_commit參數(shù)所設(shè)置的相應(yīng)條件(或者日志緩沖區(qū)寫滿)之后，才會將日志寫到文件?(或者同步到磁盤)中潘飘“怪可以通過innodb_log_buffer_size?參數(shù)設(shè)置其可以使用的最大內(nèi)存空間。

???InnoDB?將日志寫入日志磁盤文件前的緩沖大小卜录。理想值為?1M?至?8M戈擒。大的日志緩沖允許事務(wù)運行時不需要將日志保存入磁盤而只到事務(wù)被提交(commit)。?因此艰毒，如果有大的事務(wù)處理筐高，設(shè)置大的日志緩沖可以減少磁盤I/O。?在?my.cnf中以數(shù)字格式設(shè)置丑瞧。

默認(rèn)是8MB柑土，系的如頻繁的系統(tǒng)可適當(dāng)增大至4MB～8MB。當(dāng)然如上面介紹所說绊汹，這個參數(shù)實際上還和另外的flush參數(shù)相關(guān)稽屏。一般來說不建議超過32MB

注：innodb_flush_log_trx_commit參數(shù)對InnoDB?Log的寫入性能有非常關(guān)鍵的影響,默認(rèn)值為1。該參數(shù)可以設(shè)置為0西乖，1狐榔，2，解釋如下：

0：log?buffer中的數(shù)據(jù)將以每秒一次的頻率寫入到log?file中获雕，且同時會進(jìn)行文件系統(tǒng)到磁盤的同步操作薄腻，但是每個事務(wù)的commit并不會觸發(fā)任何log?buffer?到log?file的刷新或者文件系統(tǒng)到磁盤的刷新操作;

1：在每次事務(wù)提交的時候?qū)og?buffer?中的數(shù)據(jù)都會寫入到log?file，同時也會觸發(fā)文件系統(tǒng)到磁盤的同步;

2：事務(wù)提交會觸發(fā)log?buffer到log?file的刷新典鸡，但并不會觸發(fā)磁盤文件系統(tǒng)到磁盤的同步被廓。此外坏晦，每秒會有一次文件系統(tǒng)到磁盤同步操作萝玷。

實際測試發(fā)現(xiàn)，該值對插入數(shù)據(jù)的速度影響非常大昆婿，設(shè)置為2時插入10000條記錄只需要2秒球碉，設(shè)置為0時只需要1秒，而設(shè)置為1時則需要229秒仓蛆。因此睁冬，MySQL手冊也建議盡量將插入操作合并成一個事務(wù)，這樣可以大幅提高速度。根據(jù)MySQL手冊豆拨，在存在丟失最近部分事務(wù)的危險的前提下直奋，可以把該值設(shè)為0。

4.5.1.5：query_cache_size(默認(rèn)32M)

query_cache_size=40M

?????query_cache_size:?主要用來緩存MySQL中的ResultSet施禾，也就是一條SQL語句執(zhí)行的結(jié)果集脚线，所以僅僅只能針對select語句。當(dāng)我們打開了?Query?Cache功能弥搞，MySQL在接受到一條select語句的請求后邮绿，如果該語句滿足Query?Cache的要求(未顯式說明不允許使用Query?Cache，或者已經(jīng)顯式申明需要使用Query?Cache)攀例，MySQL會直接根據(jù)預(yù)先設(shè)定好的HASH算法將接受到的select語句以字符串方式進(jìn)行hash船逮，然后到Query?Cache中直接查找是否已經(jīng)緩存。也就是說粤铭，如果已經(jīng)在緩存中挖胃，該select請求就會直接將數(shù)據(jù)返回，從而省略了后面所有的步驟(如SQL語句的解析承耿，優(yōu)化器優(yōu)化以及向存儲引擎請求數(shù)據(jù)等)冠骄，極大的提高性能。根據(jù)MySQL用戶手冊加袋，使用查詢緩沖最多可以達(dá)到238%的效率凛辣。

當(dāng)然，Query?Cache也有一個致命的缺陷职烧，那就是當(dāng)某個表的數(shù)據(jù)有任何任何變化扁誓，都會導(dǎo)致所有引用了該表的select語句在Query?Cache中的緩存數(shù)據(jù)失效。所以蚀之，當(dāng)我們的數(shù)據(jù)變化非常頻繁的情況下蝗敢，使用Query?Cache可能會得不償失

???Query?Cache的使用需要多個參數(shù)配合，其中最為關(guān)鍵的是query_cache_size和query_cache_type足删，前者設(shè)置用于緩存?ResultSet的內(nèi)存大小寿谴，后者設(shè)置在何場景下使用Query?Cache。在以往的經(jīng)驗來看失受，如果不是用來緩存基本不變的數(shù)據(jù)的MySQL數(shù)據(jù)庫讶泰，query_cache_size一般256MB是一個比較合適的大小。當(dāng)然拂到，這可以通過計算Query?Cache的命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))來進(jìn)行調(diào)整痪署。?query_cache_type可以設(shè)置為0(OFF)，1(ON)或者2(DEMOND)兄旬，分別表示完全不使用query?cache狼犯，除顯式要求不使用query?cache(使用sql_no_cache)之外的所有的select都使用query?cache，只有顯示要求才使用query?cache(使用sql_cache)。如果Qcache_lowmem_prunes的值非常大悯森，則表明經(jīng)常出現(xiàn)緩沖.?如果Qcache_hits的值也非常大宋舷，則表明查詢緩沖使用非常頻繁，此時需要增加緩沖大衅耙觥肥缔；

根據(jù)命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))進(jìn)行調(diào)整，一般不建議太大汹来，256MB可能已經(jīng)差不多了续膳，大型的配置型靜態(tài)數(shù)據(jù)可適當(dāng)調(diào)大.

可以通過命令：show?status?like?'Qcache_%';查看目前系統(tǒng)Query?catch使用大小

|?Qcache_hits?????????????|?1892463??|

|?Qcache_inserts??????????|?35627??

命中率98.17%=1892463/(1892463?+35627?)*100

4.2.2局部緩存

除了全局緩沖，MySql還會為每個連接發(fā)放連接緩沖收班。個連接到MySQL服務(wù)器的線程都需要有自己的緩沖坟岔。大概需要立刻分配256K，甚至在線程空閑時摔桦，它們使用默認(rèn)的線程堆棧社付，網(wǎng)絡(luò)緩存等。事務(wù)開始之后邻耕，則需要增加更多的空間鸥咖。運行較小的查詢可能僅給指定的線程增加少量的內(nèi)存消耗，然而如果對數(shù)據(jù)表做復(fù)雜的操作例如掃描兄世、排序或者需要臨時表啼辣，則需分配大約read_buffer_size，

sort_buffer_size御滩，read_rnd_buffer_size鸥拧，tmp_table_size?大小的內(nèi)存空間.?不過它們只是在需要的時候才分配，并且在那些操作做完之后就釋放了削解。有的是立刻分配成單獨的組塊富弦。tmp_table_size?可能高達(dá)MySQL所能分配給這個操作的最大內(nèi)存空間了

。注意氛驮，這里需要考慮的不只有一點——可能會分配多個同一種類型的緩存腕柜，例如用來處理子查詢。一些特殊的查詢的內(nèi)存使用量可能更大——如果在MyISAM表上做成批的插入

時需要分配?bulk_insert_buffer_size?大小的內(nèi)存矫废；執(zhí)行?ALTER?TABLE盏缤，?OPTIMIZE?TABLE，?REPAIR?TABLE?命令時需要分配?myisam_sort_buffer_size?大小的內(nèi)存磷脯。

4.2.2.1：read_buffer_size（默認(rèn)值：2097144即2M）

read_buffer_size=4M

???????read_buffer_size?是MySql讀入緩沖區(qū)大小蛾找。對表進(jìn)行順序掃描的請求將分配一個讀入緩沖區(qū)娩脾，MySql會為它分配一段內(nèi)存緩沖區(qū)赵誓。read_buffer_size變量控制這一

緩沖區(qū)的大小。如果對表的順序掃描請求非常頻繁，并且你認(rèn)為頻繁掃描進(jìn)行得太慢俩功，可以通過增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能.

4.2.2.2：sort_buffer_size（默認(rèn)值：2097144即2M）

sort_buffer_size=4M

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

4.2.2.3:??read_rnd_buffer_size(默認(rèn)值：8388608即8M)

read_rnd_buffer_size=8M

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

銷過大。

4.2.2.4:??tmp_table_size(默認(rèn)值：8388608?即：16M)

tmp_table_size=16M

???tmp_table_size是MySql的heap?（堆積）表緩沖大小责掏。所有聯(lián)合在一個DML指令內(nèi)完成柜砾，并且大多數(shù)聯(lián)合甚至可以不用臨時表即可以完成。大多數(shù)臨時表是基于內(nèi)

存的(HEAP)表换衬。具有大的記錄長度的臨時表?(所有列的長度的和)或包含BLOB列的表存儲在硬盤上局义。如果某個內(nèi)部heap（堆積）表大小超過tmp_table_size，MySQL可以根據(jù)需要自

動將內(nèi)存中的heap表改為基于硬盤的MyISAM表冗疮。還可以通過設(shè)置tmp_table_size選項來增加臨時表的大小萄唇。也就是說，如果調(diào)高該值术幔，MySql同時將增加heap表的大小另萤，可達(dá)到提高

聯(lián)接查詢速度的效果。

4.2.2.5：record_buffer:（默認(rèn)值：）

??record_buffer每個進(jìn)行一個順序掃描的線程為其掃描的每張表分配這個大小的一個緩沖區(qū)诅挑。如果你做很多順序掃描四敞，你可能想要增加該值。默認(rèn)數(shù)值是131072

(128K)

4.2.3其它緩存：

4.2.3.1：table_cache(默認(rèn)值：512)

TABLE_CACHE(5.1.3及以后版本又名TABLE_OPEN_CACHE)

table_cache指定表高速緩存的大小拔妥。每當(dāng)MySQL訪問一個表時忿危，如果在表緩沖區(qū)中還有空間，該表就被打開并放入其中没龙，這樣可以更快地訪問表內(nèi)容铺厨。通過檢查峰值時間的狀態(tài)值Open_tables和Opened_tables缎玫，可以決定是否需要增加table_cache的值。如果你發(fā)現(xiàn)open_tables等于table_cache解滓，并且opened_tables在不斷增長赃磨，那么你就需要增加table_cache的值了（上述狀態(tài)值可以使用SHOW?STATUS?LIKE?‘Open%tables’獲得）。注意洼裤，不能盲目地把table_cache設(shè)置成很大的值邻辉。如果設(shè)置得太高，可能會造成文件描述符不足腮鞍，從而造成性能不穩(wěn)定或者連接失敗值骇。

SHOW?STATUS?LIKE?'Open%tables';

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

|?Variable_name?|?Value?|

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

|?Open_tables???|?356???|

|?Opened_tables?|?0?????|

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

2?rows?in?set?(0.00?sec)

open_tables表示當(dāng)前打開的表緩存數(shù)，如果執(zhí)行flush?tables操作移国，則此系統(tǒng)會關(guān)閉一些當(dāng)前沒有使用的表緩存而使得此狀態(tài)值減欣卓汀；

opend_tables表示曾經(jīng)打開的表緩存數(shù)桥狡，會一直進(jìn)行累加搅裙，如果執(zhí)行flush?tables操作，值不會減小裹芝。

在mysql默認(rèn)安裝情況下部逮，table_cache的值在2G內(nèi)存以下的機器中的值默認(rèn)時256到512，如果機器有4G內(nèi)存,則默認(rèn)這個值?是2048嫂易，但這決意味著機器內(nèi)存越大兄朋，這個值應(yīng)該越大，因為table_cache加大后怜械，使得mysql對SQL響應(yīng)的速度更快了颅和，不可避免的會產(chǎn)生?更多的死鎖（dead?lock），這樣反而使得數(shù)據(jù)庫整個一套操作慢了下來缕允，嚴(yán)重影響性能峡扩。所以平時維護(hù)中還是要根據(jù)庫的實際情況去作出判斷，找到最適合你維護(hù)的庫的?table_cache值障本。

由于MySQL是多線程的機制,為了提高性能,每個線程都是獨自打開自己需要的表的文件描?述符,而不是通過共享已經(jīng)打開的.針對不同存儲引擎處理的方法當(dāng)然也不一樣

在myisam表引擎中,數(shù)據(jù)文件的描述符?(descriptor)是不共享的,但是索引文件的描述符卻是所有線程共享的.Innodb中和使用表空間類型有關(guān),假如是共享表空間那么實際就一個數(shù)?據(jù)文件,當(dāng)然占用的數(shù)據(jù)文件描述符就會比獨立表空間少.

mysql手冊上給的建議大小?是:table_cache=max_connections*n

n表示查詢語句中最大表數(shù),?還需要為臨時表和文件保留一些額外的文件描述符教届。

這個數(shù)據(jù)遭到很多質(zhì)疑,table_cache夠用就好,檢查?Opened_tables值,如果這個值很大,或增長很快那么你就得考慮加大table_cache了.

??table_cache：所有線程打開的表的數(shù)目。增大該值可以增加mysqld需要的文件描述符的數(shù)量驾霜。默認(rèn)值是64.

4.2.3.2?thread_cache_size?(服務(wù)器線程緩存)

thread_cache_size=64

默認(rèn)的thread_cache_size=8案训，但是看到好多配置的樣例里的值一般是32，64粪糙，甚至是128强霎，感覺這個參數(shù)對優(yōu)化應(yīng)該有幫助，于是查了下：

根據(jù)調(diào)查發(fā)現(xiàn)以上服務(wù)器線程緩存thread_cache_size沒有進(jìn)行設(shè)置蓉冈，或者設(shè)置過小,這個值表示可以重新利用保存在緩存中線程的數(shù)量,當(dāng)斷開連接時如果緩存中還有空間,那么客戶端的線程將被放到緩存中,如果線程重新被請求城舞，那么請求將從緩存中讀取,如果緩存中是空的或者是新的請求轩触，那么這個線程將被重新創(chuàng)建,如果有很多新的線程，增加這個值可以改善系統(tǒng)性能.通過比較?Connections?和?Threads_created?狀態(tài)的變量椿争，可以看到這個變量的作用。(–>表示要調(diào)整的值)???根據(jù)物理內(nèi)存設(shè)置規(guī)則如下：

1G?—>?8

2G?—>?16

3G?—>?32?????>3G?—>?64

mysql>?show?status?like?'thread%';

+——————-+——-+

|?Variable_name?????|?Value?|

+——————-+——-+

|?Threads_cached????|?0?????|??<—當(dāng)前被緩存的空閑線程的數(shù)量

|?Threads_connected?|?1?????|??<—正在使用（處于連接狀態(tài)）的線程

|?Threads_created???|?1498??|??<—服務(wù)啟動以來熟嫩，創(chuàng)建了多少個線程

|?Threads_running???|?1?????|??<—正在忙的線程（正在查詢數(shù)據(jù)秦踪，傳輸數(shù)據(jù)等等操作）

+——————-+——-+

查看開機起來數(shù)據(jù)庫被連接了多少次？

mysql>?show?status?like?'%connection%';

+———————-+——-+

|?Variable_name????????|?Value?|

+———————-+——-+

|?Connections??????????|?1504??|??????????–>服務(wù)啟動以來掸茅，歷史連接數(shù)

|?Max_used_connections?|?2?????|

+———————-+——-+

通過連接線程池的命中率來判斷設(shè)置值是否合適椅邓？命中率超過90%以上,設(shè)定合理。

?(Connections?-??Threads_created)?/?Connections?*?100?%

特別申明昧狮，此文章轉(zhuǎn)載自：https://www.cnblogs.com/angryprogrammer/p/6667741.html