【MySQL基礎(chǔ)入門(mén)】01 常用配置參數(shù)及優(yōu)化分析

1.?????? 概述

Mysql數(shù)據(jù)庫(kù)使用my.cnf來(lái)進(jìn)行參數(shù)配置及優(yōu)化，my.cnf的路徑由安裝時(shí)指定。

當(dāng)Mysqld服務(wù)啟動(dòng)的時(shí)候，默認(rèn)會(huì)按一定的順序讀取配置文件的，可以通過(guò)如下命令查看該順序：

# mysql --help | grep my.cnf

order of preference, my.cnf, $MYSQL_TCP_PORT,

/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

通過(guò)上述命令可以發(fā)現(xiàn)，系統(tǒng)默認(rèn)是按

/etc/my.cnf-----/etc/mysql/my.cnf----/usr/local/mysql/my.cnf的順序讀取配置文件的，當(dāng)有多個(gè)配置文件時(shí)，mysql會(huì)以讀取到的最后一個(gè)配置文件中的參數(shù)為準(zhǔn)。

2.?????? my.cnf結(jié)構(gòu)介紹

my.cnf采用集中式的配置文件，配置文件是分塊的，以 [NAME] 獨(dú)立行開(kāi)始，到下一個(gè)[NAME] 獨(dú)立行結(jié)束屬于作用于NAME指定的程序的配置。

對(duì)于里面的具體參數(shù)，需要注意：

1.在同一塊[NAME]中每個(gè)參數(shù)可以重復(fù)出現(xiàn)，對(duì)于這種重復(fù)的參數(shù)默認(rèn)排在下面的參數(shù)覆蓋上面的參數(shù)。

2.每個(gè)[NAME]塊中的參數(shù)都是默認(rèn)參數(shù)值，只有在NAME命令不帶這些參數(shù)時(shí)才會(huì)生效。

常見(jiàn)的NAME如下：

[client]

所有mysql客戶(hù)端程序讀取的配置塊，針對(duì)的是MySQL客戶(hù)端（不包含mysql命令）。

[server]

所有服務(wù)端如mysqld會(huì)讀取的配置塊，一般不用設(shè)置，完全由[mysqld]替換。

[mysqld]

mysql服務(wù)端程序mysqld和mysqld_safe讀取的配置塊。

[mysqldump]

mysqldump命令讀取的配置文件，mysqldump用于導(dǎo)出數(shù)據(jù)庫(kù)到dump文件.

[mysql]

mysql命令這個(gè)客戶(hù)端程序的配置塊

3.?????? 常用參數(shù)介紹

MySQL的配置參數(shù)多達(dá)幾百個(gè)，但是我們的my.cnf文件不會(huì)全部都配置，當(dāng)未配置時(shí)，mysql會(huì)設(shè)置默認(rèn)值。

在此不一一分析所有配置項(xiàng)，僅針對(duì)常用的配置參數(shù)來(lái)進(jìn)行說(shuō)明。

[client]

port = 3306

socket = /var/lib/mysql/mysql.sock

[mysql]

#這個(gè)配置段設(shè)置啟動(dòng)MySQL服務(wù)的條件；在這種情況下，no-auto-rehash確保這個(gè)服務(wù)啟動(dòng)得比較快。

no-auto-rehash

[mysqld]

port = 3306

socket = /var/lib/mysql/mysql.sock

#為MySQL客戶(hù)程序與服務(wù)器之間的本地通信指定一個(gè)套接字文件(僅適用于UNIX/Linux系統(tǒng); 默認(rèn)設(shè)置一般是/var/lib/mysql/mysql.sock文件)

basedir = /usr/local/mysql

#使用給定目錄作為根目錄(安裝目錄)。

datadir = /data/mysql/data/

#從給定目錄讀取數(shù)據(jù)庫(kù)文件

open_files_limit = 10240

#MySQL打開(kāi)的文件描述符限制，默認(rèn)最小1024;當(dāng)open_files_limit沒(méi)有被配置的時(shí)候，比較max_connections*5和ulimit -n的值，哪個(gè)大用哪個(gè)，

#當(dāng)open_file_limit被配置的時(shí)候，比較open_files_limit和max_connections*5的值，哪個(gè)大用哪個(gè)

event_scheduler=1

#是否在mysql啟動(dòng)時(shí)開(kāi)啟mysql的event定時(shí)器，默認(rèn)值0

back_log = 600

#在MYSQL暫時(shí)停止響應(yīng)新請(qǐng)求之前，短時(shí)間內(nèi)的多少個(gè)請(qǐng)求可以被存在堆棧中。如果系統(tǒng)在短時(shí)間內(nèi)有很多連接，則需要增大該參數(shù)的值，該參數(shù)值指定到來(lái)的TCP/IP連接的**隊(duì)列的大小。默認(rèn)值80。

max_connecti 2000

#MySQL允許最大的進(jìn)程連接數(shù)，如果經(jīng)常出現(xiàn)Too Many Connections的錯(cuò)誤提示，則需要增大此值。默認(rèn)151

max_c 6000

#設(shè)置每個(gè)主機(jī)的連接請(qǐng)求異常中斷的最大次數(shù)，當(dāng)超過(guò)該次數(shù)，MYSQL服務(wù)器將禁止host的連接請(qǐng)求，直到mysql服務(wù)器重啟或通過(guò)flush hosts命令清空此host的相關(guān)信息。默認(rèn)100

external-locking = FALSE

#使用–skip-external-locking MySQL選項(xiàng)以避免外部鎖定。該選項(xiàng)默認(rèn)開(kāi)啟

max_allowed_packet = 32M

#設(shè)置在網(wǎng)絡(luò)傳輸中一次消息傳輸量的最大值。系統(tǒng)默認(rèn)值 為4MB，最大值是1GB，必須設(shè)置1024的倍數(shù)。

#sort_buffer_size = 8M

# Sort_Buffer_Size 是一個(gè)connection級(jí)參數(shù)，在每個(gè)connection（session）第一次需要使用這個(gè)buffer的時(shí)候，一次性分配設(shè)置的內(nèi)存。

#Sort_Buffer_Size 并不是越大越好，由于是connection級(jí)的參數(shù)，過(guò)大的設(shè)置+高并發(fā)可能會(huì)耗盡系統(tǒng)內(nèi)存資源。例如：500個(gè)連接將會(huì)消耗 500*sort_buffer_size(8M)=4G內(nèi)存

#Sort_Buffer_Size 超過(guò)2KB的時(shí)候，就會(huì)使用mmap() 而不是 malloc() 來(lái)進(jìn)行內(nèi)存分配，導(dǎo)致效率降低。 系統(tǒng)默認(rèn)2M，使用默認(rèn)值即可

#join_buffer_size = 2M

#用于表間關(guān)聯(lián)緩存的大小，和sort_buffer_size一樣，該參數(shù)對(duì)應(yīng)的分配內(nèi)存也是每個(gè)連接獨(dú)享。系統(tǒng)默認(rèn)2M，使用默認(rèn)值即可

thread_cache_size = 300

#默認(rèn)38

# 服務(wù)器線(xiàn)程緩存這個(gè)值表示可以重新利用保存在緩存中線(xiàn)程的數(shù)量,當(dāng)斷開(kāi)連接時(shí)如果緩存中還有空間,那么客戶(hù)端的線(xiàn)程將被放到緩存中,如果線(xiàn)程重新被請(qǐng)求，那么請(qǐng)求將從緩存中讀取,如果緩存中是空的或者是新的請(qǐng)求，那么這個(gè)線(xiàn)程將被重新創(chuàng)建,如果有很多新的線(xiàn)程，增加這個(gè)值可以改善系統(tǒng)性能.通過(guò)比較 Connections 和 Threads_created 狀態(tài)的變量，可以看到這個(gè)變量的作用。

設(shè)置規(guī)則如下：1GB 內(nèi)存配置為8，2GB配置為16，3GB配置為32，4GB或更高內(nèi)存，可配置更大。

thread_c 8

#系統(tǒng)默認(rèn)為10

# 設(shè)置thread_concurrency的值的正確與否, 對(duì)mysql的性能影響很大, 在多個(gè)cpu(或多核)的情況下，錯(cuò)誤設(shè)置了thread_concurrency的值, 會(huì)導(dǎo)致mysql不能充分利用多cpu(或多核), 出現(xiàn)同一時(shí)刻只能一個(gè)cpu(或核)在工作的情況。thread_concurrency應(yīng)設(shè)為CPU核數(shù)的2倍. 比如有一個(gè)雙核的CPU, 那么thread_concurrency的應(yīng)該為4; 2個(gè)雙核的cpu, thread_concurrency的值應(yīng)為8

query_cache_size = 16M

#在MyISAM引擎優(yōu)化中，這個(gè)參數(shù)也是一個(gè)重要的優(yōu)化參數(shù)。但也暴露出來(lái)一些問(wèn)題。機(jī)器的內(nèi)存越來(lái)越大，習(xí)慣性把參數(shù)分配的值越來(lái)越大。這個(gè)參數(shù)加大后也引發(fā)了一系列問(wèn)題。我們首先分析一下 query_cache_size的工作原理：一個(gè)SELECT查詢(xún)?cè)贒B中工作后，DB會(huì)把該語(yǔ)句緩存下來(lái)，當(dāng)同樣的一個(gè)SQL再次來(lái)到DB里調(diào)用時(shí)，DB在該表沒(méi)發(fā)生變化的情況下把結(jié)果從緩存中返回給Client。這里有一個(gè)關(guān)建點(diǎn)，就是DB在利用Query_cache工作時(shí)，要求該語(yǔ)句涉及的表在這段時(shí)間內(nèi)沒(méi)有發(fā)生變更。那如果該表在發(fā)生變更時(shí)，Query_cache里的數(shù)據(jù)又怎么處理呢？首先要把Query_cache和該表相關(guān)的語(yǔ)句全部置為失效，然后在寫(xiě)入更新。那么如果Query_cache非常大，該表的查詢(xún)結(jié)構(gòu)又比較多，查詢(xún)語(yǔ)句失效也慢，一個(gè)更新或是Insert就會(huì)很慢，這樣看到的就是Update或是Insert怎么這么慢了。所以在數(shù)據(jù)庫(kù)寫(xiě)入量或是更新量也比較大的系統(tǒng)，該參數(shù)不適合分配過(guò)大。而且在高并發(fā)，寫(xiě)入量大的系統(tǒng)，建議把該功能禁掉。

query_cache_limit = 4M

#指定單個(gè)查詢(xún)能夠使用的緩沖區(qū)大小，缺省為1M

query_cache_min_res_unit = 2k

#默認(rèn)是4KB，設(shè)置值大對(duì)大數(shù)據(jù)查詢(xún)有好處，但如果你的查詢(xún)都是小數(shù)據(jù)查詢(xún)，就容易造成內(nèi)存碎片和浪費(fèi)

#查詢(xún)緩存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%

#如果查詢(xún)緩存碎片率超過(guò)20%，可以用FLUSH QUERY CACHE整理緩存碎片，或者試試減小query_cache_min_res_unit，如果你的查詢(xún)都是小數(shù)據(jù)量的話(huà)。

#查詢(xún)緩存利用率 = (query_cache_size – Qcache_free_memory) / query_cache_size * 100%

#查詢(xún)緩存利用率在25%以下的話(huà)說(shuō)明query_cache_size設(shè)置的過(guò)大，可適當(dāng)減小;查詢(xún)緩存利用率在80%以上而且Qcache_lowmem_prunes> 50的話(huà)說(shuō)明query_cache_size可能有點(diǎn)小，要不就是碎片太多。

#查詢(xún)緩存命中率 = (Qcache_hits – Qcache_inserts) / Qcache_hits * 100%

#default-storage-engine = MyISAM

#default_table_type = InnoDB #開(kāi)啟失敗

#thread_stack = 192K

#設(shè)置MYSQL每個(gè)線(xiàn)程的堆棧大小，默認(rèn)值足夠大，可滿(mǎn)足普通操作。可設(shè)置范圍為128K至4GB，默認(rèn)為256KB，使用默認(rèn)觀察

transaction_isolati READ-COMMITTED

# 設(shè)定默認(rèn)的事務(wù)隔離級(jí)別.可用的級(jí)別如下:READ UNCOMMITTED-讀未提交 READ COMMITTE-讀已提交 REPEATABLE READ -可重復(fù)讀 SERIALIZABLE -串行

tmp_table_size = 256M

# tmp_table_size 的默認(rèn)大小是 32M。如果一張臨時(shí)表超出該大小，MySQL產(chǎn)生一個(gè) The table tbl_name is full 形式的錯(cuò)誤，如果你做很多高級(jí) GROUP BY 查詢(xún)，增加 tmp_table_size 值。如果超過(guò)該值，則會(huì)將臨時(shí)表寫(xiě)入磁盤(pán)。

max_heap_table_size = 256M

expire_logs_days = 7

key_buffer_size = 256M

#批定用于索引的緩沖區(qū)大小，增加它可以得到更好的索引處理性能，對(duì)于內(nèi)存在4GB左右的服務(wù)器來(lái)說(shuō)，該參數(shù)可設(shè)置為256MB或384MB。

read_buffer_size = 8M

#默認(rèn)128K

# 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)行得太慢，可以通過(guò)增加該變量值以及內(nèi)存緩沖區(qū)大小提高其性能。和sort_buffer_size一樣，該參數(shù)對(duì)應(yīng)的分配內(nèi)存也是每個(gè)連接獨(dú)享。

read_rnd_buffer_size = 32M

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

bulk_insert_buffer_size = 64M

#批量插入數(shù)據(jù)緩存大小，可以有效提高插入效率，默認(rèn)為8M

innodb_buffer_pool_size = 2048M

# 這對(duì)Innodb表來(lái)說(shuō)非常重要。Innodb相比MyISAM表對(duì)緩沖更為敏感。MyISAM可以在默認(rèn)的 key_buffer_size 設(shè)置下運(yùn)行的可以，然而Innodb在默認(rèn)的 innodb_buffer_pool_size 設(shè)置下卻跟蝸牛似的。由于Innodb把數(shù)據(jù)和索引都緩存起來(lái)，無(wú)需留給操作系統(tǒng)太多的內(nèi)存，因此如果只需要用Innodb的話(huà)則可以設(shè)置它高達(dá) 70-80% 的可用內(nèi)存。一些應(yīng)用于 key_buffer的規(guī)則有 — 如果你的數(shù)據(jù)量不大，并且不會(huì)暴增，那么無(wú)需把 innodb_buffer_pool_size 設(shè)置的太大了

innodb_data_file_path = ibdata1:10M:autoextend

#表空間文件 重要數(shù)據(jù)

#innodb_thread_c0

#參數(shù)的含義是: InnoDB內(nèi)部的并發(fā)線(xiàn)程數(shù).默認(rèn)值0，不限制

可以動(dòng)態(tài)修改

具體解析: InnoDB 試圖保持InnoDB內(nèi)部的并發(fā)操作系統(tǒng)的線(xiàn)程數(shù)少于innodb_thread_concurrency設(shè)置的值，如果innodb并發(fā)線(xiàn)程數(shù)快要到達(dá)innodb_thread_cx,其他的innodb線(xiàn)程會(huì)被設(shè)置為等待狀態(tài)，隊(duì)列的算法是FIFO；處于等待拿鎖狀態(tài)的線(xiàn)程數(shù)，不會(huì)被計(jì)算入正在執(zhí)行的并發(fā)線(xiàn)程數(shù)

innodb_flush_log_at_trx_commit = 1

#參數(shù)解釋?zhuān)?/p>

0：log buffer將每秒一次地寫(xiě)入log file中，并且log file的flush(刷到磁盤(pán))操作同時(shí)進(jìn)行。該模式下在事務(wù)提交的時(shí)候，不會(huì)主動(dòng)觸發(fā)寫(xiě)入磁盤(pán)的操作。

1：每次事務(wù)提交時(shí)MySQL都會(huì)把log buffer的數(shù)據(jù)寫(xiě)入log file，并且flush(刷到磁盤(pán))中去，該模式為系統(tǒng)默認(rèn)。

2：每次事務(wù)提交時(shí)MySQL都會(huì)把log buffer的數(shù)據(jù)寫(xiě)入log file，但是flush(刷到磁盤(pán))操作并不會(huì)同時(shí)進(jìn)行。該模式下，MySQL會(huì)每秒執(zhí)行一次 flush(刷到磁盤(pán))操作。

innodb_log_buffer_size = 16M

#此參數(shù)確定些日志文件所用的內(nèi)存大小，以M為單位，默認(rèn)8M。緩沖區(qū)更大能提高性能，但意外的故障將會(huì)丟失數(shù)據(jù).

innodb_log_file_size = 1G

#此參數(shù)確定數(shù)據(jù)日志文件的大小，默認(rèn)48M，更大的設(shè)置可以提高性能，但也會(huì)增加恢復(fù)故障數(shù)據(jù)庫(kù)所需的時(shí)間

innodb_log_files_in_group = 5

#為提高性能，MySQL可以以循環(huán)方式將日志文件寫(xiě)到多個(gè)文件。

#innodb_max_dirty_pages_pct = 90

#推薦閱讀 http://www.taobaodba.com/html/221_innodb_max_dirty_pages_pct_checkpoint.html

# Buffer_Pool中Dirty_Page所占的數(shù)量，直接影響InnoDB的關(guān)閉時(shí)間。參數(shù)innodb_max_dirty_pages_pct 可以直接控制了Dirty_Page在Buffer_Pool中所占的比率，而且幸運(yùn)的是innodb_max_dirty_pages_pct是可以動(dòng)態(tài)改變的。所以，在關(guān)閉InnoDB之前先將innodb_max_dirty_pages_pct調(diào)小，強(qiáng)制數(shù)據(jù)塊Flush一段時(shí)間，則能夠大大縮短 MySQL關(guān)閉的時(shí)間。

innodb_lock_wait_timeout = 120

#默認(rèn)為50秒

# InnoDB 有其內(nèi)置的死鎖檢測(cè)機(jī)制，能導(dǎo)致未完成的事務(wù)回滾。但是，如果結(jié)合InnoDB使用MyISAM的lock tables 語(yǔ)句或第三方事務(wù)引擎,則InnoDB無(wú)法識(shí)別死鎖。為消除這種可能性，可以將innodb_lock_wait_timeout設(shè)置為一個(gè)整數(shù)值，指示 MySQL在允許其他事務(wù)修改那些最終受事務(wù)回滾的數(shù)據(jù)之前要等待多長(zhǎng)時(shí)間(秒數(shù))

innodb_file_per_table = 0

#默認(rèn)為No

#獨(dú)享表空間（關(guān)閉）

[mysqldump]

quick

max_allowed_packet = 64M

[mysql]

no-auto-rehash

# Remove the next comment character if you are not familiar with SQL

#safe-updates

華為云

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶(hù)投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶(hù)投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。