MySQL參數(shù)調(diào)整方法

這篇文章主要講解了“MySQL參數(shù)調(diào)整方法”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“MySQL參數(shù)調(diào)整方法”吧！

創(chuàng)新互聯(lián)是專業(yè)的泰寧網(wǎng)站建設(shè)公司，泰寧接單;提供成都網(wǎng)站設(shè)計(jì)、做網(wǎng)站,網(wǎng)頁(yè)設(shè)計(jì),網(wǎng)站設(shè)計(jì),建網(wǎng)站,PHP網(wǎng)站建設(shè)等專業(yè)做網(wǎng)站服務(wù);采用PHP框架,可快速的進(jìn)行泰寧網(wǎng)站開(kāi)發(fā)網(wǎng)頁(yè)制作和功能擴(kuò)展;專業(yè)做搜索引擎喜愛(ài)的網(wǎng)站,專業(yè)的做網(wǎng)站團(tuán)隊(duì),希望更多企業(yè)前來(lái)合作!

1 MySQL參數(shù)調(diào)整

1.1 tx_isolation事務(wù)參數(shù)

1.1.1 事務(wù)的特征

事務(wù)具有四個(gè)特性：原子性（Atomiocity）、一致性（Consistency）、隔離性（Isolation）和持久性（Durability），這四個(gè)特性簡(jiǎn)稱ACID特性。

原子性：事務(wù)是數(shù)據(jù)庫(kù)的邏輯工作單位，事務(wù)中包括的所有操作要么都做，要么都不做。

一致性：事務(wù)執(zhí)行的結(jié)果必須是使數(shù)據(jù)庫(kù)從一個(gè)一致性的狀態(tài)變到另外一個(gè)一致性狀態(tài)。

隔離性：一個(gè)事務(wù)的執(zhí)行不能被其他事務(wù)干擾。即一個(gè)事務(wù)內(nèi)部的操作及使用的數(shù)據(jù)對(duì)其他事務(wù)是隔離的，并發(fā)執(zhí)行的各個(gè)事務(wù)之間互相不干擾。

持久性：一個(gè)事務(wù)一旦成功提交，對(duì)數(shù)據(jù)庫(kù)中數(shù)據(jù)的修改就是持久性的。接下來(lái)其他的其他。

1.1.2 四種事務(wù)隔離級(jí)別

READ UNCOMMITED：SELECT的時(shí)候允許臟讀，即SELECT會(huì)讀取其他事務(wù)修改而還沒(méi)有提交的數(shù)據(jù)。

READ COMMITED：SELECT的時(shí)候不能重復(fù)讀，即同一個(gè)事務(wù)中兩次執(zhí)行同樣的查詢語(yǔ)句，若在第一次與第二次查詢之間時(shí)間段，其他事務(wù)又剛好修改了其查詢的數(shù)據(jù)且提交了，則兩次讀到的數(shù)據(jù)不一致。

REPEATABLE READ：SELECT的時(shí)候可以重復(fù)讀，即同一個(gè)事務(wù)中兩次執(zhí)行同樣的查詢語(yǔ)句，得到的數(shù)據(jù)始終都是一致的。

SERIALIZABLE：與可重復(fù)讀的唯一區(qū)別是，默認(rèn)把普通的SELECT語(yǔ)句改成SELECT …. LOCK IN SHARE MODE。即為查詢語(yǔ)句涉及到的數(shù)據(jù)加上共享瑣，阻塞其他事務(wù)修改真實(shí)數(shù)據(jù)。

1.1.3 隔離級(jí)別選擇

綜合考慮系統(tǒng)的應(yīng)用和性能，建議使用默認(rèn)的REPEATABLE READ級(jí)別，或者READ COMMITED級(jí)別。

1.2 innodb_buffer_pool_size

這是InnoDB最重要的設(shè)置，對(duì)InnoDB性能有決定性的影響。默認(rèn)的設(shè)置只有8M，所以默認(rèn)的數(shù)據(jù)庫(kù)設(shè)置下面InnoDB性能很差。在只有 InnoDB存儲(chǔ)引擎的數(shù)據(jù)庫(kù)服務(wù)器上面，可以設(shè)置60-80%的內(nèi)存。更精確一點(diǎn)，在內(nèi)存容量允許的情況下面設(shè)置比InnoDB tablespaces大10%的內(nèi)存大小。它用來(lái)設(shè)置用于緩存 InnoDB 索引及數(shù)據(jù)塊的內(nèi)存區(qū)域大小，類似于 MyISAM 存儲(chǔ)引擎的 key_buffer_size 參數(shù)，當(dāng)然，可能更像是 Oracle 的 db_cache_size。簡(jiǎn)單來(lái)說(shuō)，當(dāng)我們操作一個(gè) InnoDB 表的時(shí)候，返回的所有數(shù)據(jù)或者去數(shù)據(jù)過(guò)程中用到的任何一個(gè)索引塊，都會(huì)在這個(gè)內(nèi)存區(qū)域中走一遭。

和key_buffer_size 對(duì)于 MyISAM 引擎一樣，innodb_buffer_pool_size 設(shè)置了 InnoDB 存儲(chǔ)引擎需求最大的一塊內(nèi)存區(qū)域的大小，直接關(guān)系到 InnoDB存儲(chǔ)引擎的性能，所以如果我們有足夠的內(nèi)存，盡可將該參數(shù)設(shè)置到足夠打，將盡可能多的 InnoDB 的索引及數(shù)據(jù)都放入到該緩存區(qū)域中，直至全部。

我們可以通過(guò) (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 計(jì)算緩存命中率，并根據(jù)命中率來(lái)調(diào)整 innodb_buffer_pool_size 參數(shù)大小進(jìn)行優(yōu)化。

1.3 innodb_flush_log_at_trx_commit

該參數(shù)設(shè)定了事務(wù)提交時(shí)內(nèi)存中l(wèi)og信息的刷新頻率，有3個(gè)值, 默認(rèn)設(shè)置的是1，也就是同步刷新：

=0時(shí)，日志緩沖每秒一次地被寫(xiě)到日志文件，并且對(duì)日志文件做到磁盤(pán)操作的刷新。任何mysqld進(jìn)程的崩潰會(huì)刪除崩潰前最后一秒的事務(wù)；

=1時(shí)，在每個(gè)事務(wù)提交時(shí)，日志緩沖被寫(xiě)到日志文件，對(duì)日志文件做到磁盤(pán)操作的刷新。Truly ACID。速度慢；

=2時(shí)，在每個(gè)事務(wù)提交時(shí)，日志緩沖被寫(xiě)到文件，但不對(duì)日志文件做到磁盤(pán)操作的刷新。只有操作系統(tǒng)崩潰或掉電才會(huì)刪除最后一秒的事務(wù)，不然不會(huì)丟失事務(wù)。

1.4 innodb_log_buffer_size

INNODB日志緩存的大小，磁盤(pán)速度是很慢的，直接將log寫(xiě)道磁盤(pán)會(huì)影響InnoDB的性能，該參數(shù)設(shè)定了log buffer的大小，一般4M。如果有大的blob操作，可以適當(dāng)增大。

1.5 innodb_log_file_size

INNODB日志文件的大小，該參數(shù)決定了recovery speed。太大的話recovery就會(huì)比較慢，太小了影響查詢性能，一般取1GB可以兼顧性能和recovery的速度。

1.6 innodb_data_file_path

該參數(shù)指定表數(shù)據(jù)和索引存儲(chǔ)的空間，可以是一個(gè)或者多個(gè)文件。最后一個(gè)數(shù)據(jù)文件必須是自動(dòng)擴(kuò)充的，也只有最后一個(gè)文件允許自動(dòng)擴(kuò)充。這樣，當(dāng)空間用完后，自動(dòng)擴(kuò)充數(shù)據(jù)文件就會(huì)自動(dòng)增長(zhǎng)（以8MB為單位）以容納額外的數(shù)據(jù)。例如： innodb_data_file_path=/disk1 /ibdata1:900M;/disk2/ibdata2:50M:autoextend兩個(gè)數(shù)據(jù)文件放在不同的磁盤(pán)上（）。數(shù)據(jù)首先放在ibdata1 中，當(dāng)達(dá)到900M以后，數(shù)據(jù)就放在ibdata2中。一旦達(dá)到50MB，ibdata2將以8MB為單位自動(dòng)增長(zhǎng)。如果磁盤(pán)滿了，需要在另外的磁盤(pán)上面增加一個(gè)數(shù)據(jù)文件。

1.7 innodb_data_home_dir

該參數(shù)設(shè)置了放置表空間數(shù)據(jù)的目錄，默認(rèn)在mysql的數(shù)據(jù)目錄，由MySQL參數(shù)datadir指定。設(shè)置到和MySQL 安裝文件不同的分區(qū)可以提高性能?？梢耘cinnodb_data_file_path配合使用，用innodb_data_home_dir來(lái)指定共同位置，然后在通過(guò) inndo_data_file_path來(lái)指定文件名即可。

1.8 innodb_file_io_threads

此參數(shù)指定InnoDB表可用的文件I／O線程數(shù)，建議在非Windows平臺(tái)中這個(gè)參數(shù)設(shè)置為4。

1.9 innodb_flush_logs_at_trx_commit

該參數(shù)設(shè)定了事務(wù)提交時(shí)內(nèi)存中l(wèi)og信息的處理。

1) =1時(shí)，在每個(gè)事務(wù)提交時(shí)，日志緩沖被寫(xiě)到日志文件，對(duì)日志文件做到磁盤(pán)操作的刷新。Truly ACID。速度慢。默認(rèn)為此值。

2) =2時(shí)，在每個(gè)事務(wù)提交時(shí)，日志緩沖被寫(xiě)到文件，但不對(duì)日志文件做到磁盤(pán)操作的刷新。只有操作系統(tǒng)崩潰或掉電才會(huì)刪除最后一秒的事務(wù)，不然不會(huì)丟失事務(wù)。

3) =0時(shí)，日志緩沖每秒一次地被寫(xiě)到日志文件，并且對(duì)日志文件做到磁盤(pán)操作的刷新。任何mysqld進(jìn)程的崩潰會(huì)刪除崩潰前最后一秒的事務(wù)。

1.10 innodb_flush_method

影響了服務(wù)器flush數(shù)據(jù)或日志文件的方法。設(shè)置InnoDB同步IO的方式

1) Default – 使用fsync（）。

2) O_SYNC 以sync模式打開(kāi)文件，通常比較慢。

3) O_DIRECT，在Linux上使用Direct IO。跳過(guò)了操作系統(tǒng)的文件系統(tǒng)Disk Cache，讓MySQL直接讀寫(xiě)磁盤(pán)，可以顯著提高速度，特別是在RAID系統(tǒng)上。避免額外的數(shù)據(jù)復(fù)制和double buffering（mysql buffering 和OS buffering）。避免雙緩沖(double buffering)和降低swap的壓力。大多數(shù)情況下可以提高性能.但是注意如果RAID cache不夠的話,寫(xiě)IO的操作會(huì)有麻煩。

1.11 tmp_table_size

tmp_table_size，它規(guī)定了內(nèi)存臨時(shí)表的最大值，每個(gè)線程都要分配。（實(shí)際起限制作用的是tmp_table_size和max_heap_table_size的最小值。）如果內(nèi)存臨時(shí)表超出了限制，MySQL就會(huì)自動(dòng)地把它轉(zhuǎn)化為基于磁盤(pán)的MyISAM表，此參數(shù)不限制create tables創(chuàng)建的內(nèi)存表，存儲(chǔ)在指定的tmpdir目錄下，默認(rèn):

mysql> show variables like "tmpdir";

1.12 max_heap_table_size

這個(gè)變量定義了用戶可以創(chuàng)建的內(nèi)存表(memory table)的大小。這個(gè)值用來(lái)計(jì)算內(nèi)存表的最大行數(shù)值。如果內(nèi)存內(nèi)的臨時(shí)表超過(guò)該值，MySQL自動(dòng)將它轉(zhuǎn)換為硬盤(pán)上的MyISAM表。這個(gè)變量支持動(dòng)態(tài)改變，即set @max_heap_table_size=# ，但是對(duì)于已經(jīng)存在的內(nèi)存表就沒(méi)有什么用了，除非這個(gè)表被重新創(chuàng)建(create table)或者修改(alter table)或者truncate table。服務(wù)重啟也會(huì)設(shè)置已經(jīng)存在的內(nèi)存表為全局max_heap_table_size的值。這個(gè)變量和tmp_table_size一起限制了內(nèi)部?jī)?nèi)存表的大小。

1.13 long_query_time

long_query_time，對(duì)SQL執(zhí)行設(shè)定一個(gè)時(shí)間限制，單位為秒，超出這個(gè)時(shí)間，測(cè)返回錯(cuò)誤。該參數(shù)可動(dòng)態(tài)設(shè)置：set global long_query_time=1800;

1.14 join_buffer_size

如果兩個(gè)表關(guān)聯(lián)查詢，但關(guān)聯(lián)的字段又沒(méi)有索引時(shí)，使用此參數(shù)分配的內(nèi)存空間。

1.15 key_buffer_size

MyISAM表的索引塊分配了緩沖區(qū)，由所有線程共享。key_buffer_size是索引塊緩沖區(qū)的大小。鍵值緩沖區(qū)即為鍵值緩存.用key_buffer_size結(jié)合Key_blocks_unused狀態(tài)變量和緩沖區(qū)塊大小，可以確定使用的鍵值緩沖區(qū)的比例。從key_cache_block_size服務(wù)器變量可以獲得緩沖區(qū)塊大小。使用的緩沖區(qū)的比例為：

1 - ((Key_blocks_unused * key_cache_block_size) / key_buffer_size)

注意：MyISAM的數(shù)據(jù)文件讀取依賴于操作系統(tǒng)自身的IO緩存，如果有MyISAM表，要預(yù)留更多的內(nèi)存給操作系統(tǒng)。

1.16 wait_timeout

wait_timeout，設(shè)置非互交連接會(huì)話的空閑超時(shí)時(shí)間，單位為s。

1.17 interactive_timeout

interactive_timeout，設(shè)置互交連接會(huì)話的空閑超時(shí)時(shí)間，單位為s。

感謝各位的閱讀，以上就是“MySQL參數(shù)調(diào)整方法”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)MySQL參數(shù)調(diào)整方法這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是創(chuàng)新互聯(lián)，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！

文章標(biāo)題：MySQL參數(shù)調(diào)整方法
文章來(lái)源：http://muchs.cn/article30/ijcepo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供靜態(tài)網(wǎng)站、用戶體驗(yàn)、網(wǎng)站制作、網(wǎng)站策劃、品牌網(wǎng)站制作、網(wǎng)站內(nèi)鏈

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容