本篇文章給大家主要講的是關(guān)于Mysql參數(shù)優(yōu)化的詳細(xì)步驟的內(nèi)容，感興趣的話就一起來看看這篇文章吧，相信看完Mysql參數(shù)優(yōu)化的詳細(xì)步驟對(duì)大家多少有點(diǎn)參考價(jià)值吧。
連接優(yōu)化
back_log=500 （默認(rèn)50）
MySql的連接達(dá)到max_connections時(shí)，新來的請(qǐng)求將會(huì)被存在堆棧中，以等待某一連接釋放資源，該堆棧的數(shù)量即back_log，如果等待連接的數(shù)量超過back_log，將不被授予連接資源。將會(huì)報(bào)：unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | NULL 的待連接進(jìn)程時(shí).back_log值不能超過TCP/IP連接的偵聽隊(duì)列的大小。若超過則無效，查看當(dāng)前系統(tǒng)的TCP/IP連接的偵聽隊(duì)列的大小命令：cat /proc/sys/net/ipv4/tcp_max_syn_backlog目前系統(tǒng)為1024。對(duì)于Linux系統(tǒng)推薦設(shè)置為小于512的整數(shù)。
wait_timeout=1800 （默認(rèn)8小時(shí)，單位秒）
MySQL客戶端的數(shù)據(jù)庫(kù)連接閑置大時(shí)間值；有大量sleep長(zhǎng)時(shí)間占用內(nèi)存和連接數(shù)，則需要修改此項(xiàng)。
max_connections=3000 （默認(rèn)151）
MySql的大連接數(shù)，如果云服務(wù)器的并發(fā)連接請(qǐng)求量比較大，建議調(diào)高此值，連接數(shù)越多占用內(nèi)存越多
max_connect_errors = 10000000
盡量設(shè)大，防止錯(cuò)誤鏈接數(shù)過多導(dǎo)致無法寫入數(shù)據(jù)
CPU優(yōu)化
thread_concurrency=16 （默認(rèn)8）
正確設(shè)置可以充分利用多核CPU，thread_concurrency應(yīng)設(shè)為CPU核數(shù)的2倍
網(wǎng)絡(luò)優(yōu)化
skip-name-resolve （默認(rèn)被注釋）
禁止MySQL對(duì)外部連接進(jìn)行DNS解析，使用這一選項(xiàng)可以避免MySQL進(jìn)行DNS解析的時(shí)間。但需要注意，如果開啟該選項(xiàng)，則所有遠(yuǎn)程主機(jī)連接授權(quán)都要使用IP地址方式，否則MySQL將無法正常處理連接請(qǐng)求！
IO優(yōu)化
key_buffer_size=400M （MyISAM）
用于索引塊的緩沖區(qū)大小，對(duì)MyISAM影響較大的參數(shù)。key_reads / key_read_requests在0.1之下比較好
key_cache_miss_rate = Key_reads / Key_read_requests * 100% show status like 'key_read%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Key_read_requests | 28535 | | Key_reads | 269 | +-------------------+-------+ 以上key_cache_miss_rate大于0.1%，則需要增大參數(shù)。key_cache_miss_rate在0.1%以下都很好(每1000個(gè)請(qǐng)求有一個(gè)直接讀硬盤)，如果key_cache_miss_rate在0.01%以下的話，key_buffer_size分配的過多，可以適當(dāng)減少。 mysql> show global status like 'key_blocks_u%'; +---------------------------+ | Variable_name | Value | +---------------------------+ | Key_blocks_unused | 0 | | Key_blocks_used | 413543| +---------------------------+ Key_blocks_unused表示未使用的緩存簇(blocks)數(shù)，Key_blocks_used表示曾經(jīng)用到的大的blocks數(shù)，比如這臺(tái)服務(wù)器，所有的緩存都用到了，要么增加key_buffer_size，要么就是過渡索引了，把緩存占滿了。比較理想的設(shè)置： Key_blocks_used / (Key_blocks_unused + Key_blocks_used) * 100% ≈ 80%
innodb_buffer_pool_size = 2048M （默認(rèn)128M，InnoDB）
用于緩存數(shù)據(jù)塊和索引鍵。對(duì)InnoDB表性能影響大的一個(gè)參數(shù)，增加這個(gè)參數(shù)的大小，可以有效的減少 InnoDB 類型的表的磁盤 I/O 。
SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages_%'; +----------------------------------+--------+ | Variable_name | Value | +----------------------------------+--------+ | Innodb_buffer_pool_pages_data | 129037 | | Innodb_buffer_pool_pages_dirty | 362 | | Innodb_buffer_pool_pages_flushed | 9998 | | Innodb_buffer_pool_pages_free | 0 | !!!!!!!! | Innodb_buffer_pool_pages_misc | 2035 | | Innodb_buffer_pool_pages_total | 131072 | +----------------------------------+--------+ 6 rows in set (0.00 sec) Innodb_buffer_pool_pages_free 為 0，則說明 buffer pool 已經(jīng)被用光，需要增大innodb_buffer_pool_size 建議使用 所有內(nèi)存的 75% （在剩余內(nèi)存能夠保證系統(tǒng)和其它服務(wù)正常運(yùn)行的情況下）
innodb_additional_mem_pool_size=100M （默認(rèn)8M）
設(shè)置了InnoDB存儲(chǔ)引擎用來存放數(shù)據(jù)字典信息以及一些內(nèi)部數(shù)據(jù)結(jié)構(gòu)的內(nèi)存空間大小，所以當(dāng)我們一個(gè)MySQL Instance中的數(shù)據(jù)庫(kù)對(duì)象非常多的時(shí)候，是需要適當(dāng)調(diào)整該參數(shù)的大小以確保所有數(shù)據(jù)都能存放在內(nèi)存中提高訪問效率的。如果設(shè)置太小日志會(huì)出現(xiàn)warning
2G內(nèi)存推薦20M，32G內(nèi)存推薦100M
innodb_log_buffer_size=20M （默認(rèn)8M）
InnoDB存儲(chǔ)引擎的事務(wù)日志所使用的緩沖區(qū)。類似于Binlog Buffer，InnoDB在寫事務(wù)日志的時(shí)候，為了提高性能，也是先將信息寫入Innofb Log Buffer中。理想8M，不建議超過32MB，將此緩存段增大可以減少數(shù)據(jù)庫(kù)寫數(shù)據(jù)文件次數(shù)。
innodb_flush_log_trx_commit=0 （默認(rèn)1）
0 log buffer每秒就會(huì)被刷寫日志文件到磁盤，提交事務(wù)的時(shí)候不做任何操作
1 在每次事務(wù)提交的時(shí)候?qū)og buffer 中的數(shù)據(jù)都會(huì)寫入到log file，同時(shí)也會(huì)觸發(fā)文件系統(tǒng)到磁盤的同步;
2 事務(wù)提交會(huì)觸發(fā)log buffer到log file的刷新，但并不會(huì)觸發(fā)磁盤文件系統(tǒng)到磁盤的同步。此外，每秒會(huì)有一次文件系統(tǒng)到磁盤同步操作。
設(shè)為1當(dāng)然是最安全的，但性能頁(yè)是最差的（相對(duì)其他兩個(gè)參數(shù)而言，但不是不能接受）。如果對(duì)數(shù)據(jù)一致性和完整性要求不高，完全可以設(shè)為2,如果只最求性能，例如高并發(fā)寫的日志云服務(wù)器，設(shè)為0來獲得更高性能
innodb_log_file_size = 128M （默認(rèn)8M）
此配置項(xiàng)作用設(shè)定innodb 數(shù)據(jù)庫(kù)引擎UNDO日志的大?。粡亩鴾p少數(shù)據(jù)庫(kù)checkpoint操作。
query_cache_size = 128M （默認(rèn)32M）
緩存MySQL中的ResultSet，也就是一條SQL語(yǔ)句執(zhí)行的結(jié)果集，所以僅僅只能針對(duì)select語(yǔ)句
show status like 'Qcache_%'; | Qcache_hits | 1892463 | | Qcache_inserts | 35627 | 命中率98.17%=1892463/(1892463 +35627 )*100
thread_cache_size = 128 （默認(rèn)8）
,當(dāng)斷開連接時(shí)如果緩存中還有空間,那么客戶端的線程將被放到緩存中,如果線程重新被請(qǐng)求，那么請(qǐng)求將從緩存中讀取,如果緩存中是空的或者是新的請(qǐng)求，那么這個(gè)線程將被重新創(chuàng)建,
mysql> show status like 'thread%'; +——————-+——-+ | Variable_name | Value | +——————-+——-+ | Threads_cached | 0 | <—當(dāng)前被緩存的空閑線程的數(shù)量 | Threads_connected | 1 | <—正在使用（處于連接狀態(tài)）的線程 | Threads_created | 1498 | <—服務(wù)啟動(dòng)以來，創(chuàng)建了多少個(gè)線程 | Threads_running | 1 | <—正在忙的線程（正在查詢數(shù)據(jù)，傳輸數(shù)據(jù)等等操作） +——————-+——-+ 查看開機(jī)起來數(shù)據(jù)庫(kù)被連接了多少次？ mysql> show status like '%connection%'; +———————-+——-+ | Variable_name | Value | +———————-+——-+ | Connections | 1504 | –>服務(wù)啟動(dòng)以來，歷史連接數(shù) | Max_used_connections | 2 | +———————-+——-+ 通過連接線程池的命中率來判斷設(shè)置值是否合適？命中率超過90%以上,設(shè)定合理。 (Connections - Threads_created) / Connections * 100 % 物理內(nèi)存 1G —> 8；2G —> 16； 3G —> 32； >3G —> 64
innodb_autoextend_increment=128 （默認(rèn)8M）
此配置項(xiàng)作用主要是當(dāng)tablespace 空間已經(jīng)滿了后，需要MySQL系統(tǒng)需要自動(dòng)擴(kuò)展多少空間，每次tablespace 擴(kuò)展都會(huì)讓各個(gè)SQL 處于等待狀態(tài)。增加自動(dòng)擴(kuò)展Size可以減少tablespace自動(dòng)擴(kuò)展次數(shù)
表結(jié)構(gòu)優(yōu)化
innodb_file_per_table = on (5.6之前默認(rèn)off)
這項(xiàng)設(shè)置告知InnoDB是否需要將所有表的數(shù)據(jù)和索引存放在共享表空間里（innodb_file_per_table = OFF） 或者為每張表的數(shù)據(jù)單獨(dú)放在一個(gè).ibd文件（innodb_file_per_table = ON）。每張表一個(gè)文件允許你在drop、truncate或者rebuild表時(shí)回收磁盤空間。這對(duì)于一些高級(jí)特性也是有必要的，比如數(shù)據(jù)壓縮。但是它不會(huì)帶來任何性能收益。你不想讓每張表一個(gè)文件的主要場(chǎng)景是：有非常多的表（比如10k+）。
MySQL 5.6中，這個(gè)屬性默認(rèn)值是ON，因此大部分情況下你什么都不需要做。對(duì)于之前的版本你必需在加載數(shù)據(jù)之前將這個(gè)屬性設(shè)置為ON，因?yàn)樗粚?duì)新創(chuàng)建的表有影響。
以上關(guān)于Mysql參數(shù)優(yōu)化的詳細(xì)步驟詳細(xì)內(nèi)容，對(duì)大家有幫助嗎？如果想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。
另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國(guó)服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡(jiǎn)單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢(shì)，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場(chǎng)景需求。
重慶分公司
重慶分公司
