2022mysql運(yùn)維操作手冊(cè)

第第頁Mysql運(yùn)維操作手冊(cè)2022目錄TOC\o"1-3"\h\z\u1、簡(jiǎn)介 71.1目的 71.2適用范圍 71.3引用文件 71.4特別聲明 72、操作系統(tǒng)參數(shù) 72.1內(nèi)核參數(shù)sysconf 72.1.1somaxconn 72.1.2tcp_max_syn_backlog 72.1.3netdev_max_backlog 72.1.4tcp_fin_timeout 82.1.5tcp_tw_reuse 82.1.6tcp_tw_recycle 82.1.7網(wǎng)絡(luò)相關(guān) 82.1.8心跳檢測(cè) 82.1.9內(nèi)存頁分配 82.1.10交換內(nèi)存 92.2資源限制 92.2.1參考樣例 92.2.2其它配置 93、常用參數(shù) 93.1三種方式 93.1.1全局方式 93.1.2會(huì)話方式 103.1.3配置方式 103.2connection相關(guān) 103.2.1max_connections 103.2.2max_user_connections 103.2.3Threads 103.2.4back_log 113.2.5超時(shí)設(shè)置 113.2.6查看連接 113.3查詢緩存 123.3.1表緩存 123.3.2查詢緩存狀態(tài) 133.3.3設(shè)置緩存參數(shù) 143.4innodb相關(guān) 153.4.1innodb_buffer_pool_% 153.4.2其它參數(shù) 163.5日志優(yōu)化 183.5.1binlog日志 183.5.2事務(wù)區(qū)日志 193.5.3復(fù)制優(yōu)化 193.6事務(wù)隔離 203.6.1READUNCOMMITTED 213.6.2READCOMMITTED 213.6.3REPEATABLEREAD 213.6.4SERIALIZABLE 213.6.5綜合建議 223.7會(huì)話參數(shù) 223.7.1sort_buffer_size 223.8其它參數(shù) 224、常用性能監(jiān)控 224.1查詢總視圖 224.2查詢連接 234.2.1列表查詢 234.2.2查詢長(zhǎng)時(shí)間連接 244.3查詢慢SQL 244.3.1開啟慢查詢 244.3.2統(tǒng)計(jì)排名 254.4查詢分析 254.4.1開啟查詢 254.5查看各表統(tǒng)計(jì)信息 254.6分析表執(zhí)行計(jì)劃 264.7查詢當(dāng)前進(jìn)程 264.7.1查詢進(jìn)程 264.7.2主要狀態(tài) 264.8狀態(tài)查詢 284.9鎖監(jiān)控查詢 294.9.1查詢鎖表情況 294.9.2查詢當(dāng)前事務(wù)鎖 294.9.3查詢當(dāng)前鎖表 294.9.4查詢被鎖事務(wù) 294.9.5查詢到鎖源 304.9.6查詢鎖源sql語句 314.9.7快速方法 314.10performance_schema 334.10.1開啟說明 334.10.2表分類說明 334.10.3簡(jiǎn)單使用 344.10.4系統(tǒng)變量 344.10.5啟動(dòng)選項(xiàng) 354.10.6重要配置表 364.10.7常用監(jiān)控 394.11Profiler(診斷) 434.11.1開啟 434.11.2查看最近 444.11.3顯示明細(xì) 444.11.4顯示cpu|block 455、內(nèi)存評(píng)估 465.1內(nèi)存參數(shù) 465.2mysql內(nèi)存公式 475.3mysql內(nèi)存計(jì)算 475.4mysql磁盤參數(shù)優(yōu)化 486、參考文章 48簡(jiǎn)介目的本文檔為xxx信息有限公司針mysql運(yùn)維操作手冊(cè)。編寫此操作手冊(cè)的目的在于幫助現(xiàn)研發(fā)、測(cè)試和實(shí)施人員安裝部署系統(tǒng)，提高工作效率。適用范圍實(shí)施人員、項(xiàng)目經(jīng)理、項(xiàng)目成員。引用文件無特別聲明操作系統(tǒng)參數(shù)內(nèi)核參數(shù)sysconfsomaxconn#系統(tǒng)允許同時(shí)發(fā)起的TCP連接數(shù)。在許多的主流操作系統(tǒng)上這個(gè)值都默認(rèn)是128。net.core.somaxconn=65535tcp_max_syn_backlog#系統(tǒng)允許的半連接(SYN)同步包上限，顯然tcp_max_syn_backlog>=somaxconnnet.ipv4.tcp_max_syn_backlog=65536netdev_max_backlog#每個(gè)網(wǎng)絡(luò)端口接收數(shù)據(jù)包的速率比內(nèi)核處理這些包的速率快時(shí)，允許送到隊(duì)列的數(shù)據(jù)包的最大數(shù)目dev_max_backlog=65536tcp_fin_timeout#這個(gè)參數(shù)是用來設(shè)置保持在FIN_WAIT_2狀態(tài)的時(shí)間。tcp4次揮手，正常的處理流程就是在FIN_WAIT_2情況下接收到FIN進(jìn)入到TIME_WAIT的情況，tcp_fin_timeout參數(shù)對(duì)處于TIME_WAIT狀態(tài)的時(shí)間沒有任何影響。但是如果這個(gè)參數(shù)設(shè)的比較小，會(huì)縮短從FIN_WAIT_2到TIME_WAIT的時(shí)間，從而使連接更早地進(jìn)入TIME_WAIT狀態(tài)。狀態(tài)開始的早，等待相同的時(shí)間，結(jié)束的也早，客觀上也加速了TIME_WAIT狀態(tài)套接字的清理速度。net.ipv4.tcp_fin_timeout=10tcp_tw_reuse#TIME-WAIT套接字是否允許重用于新的TCP連接,了解網(wǎng)絡(luò)建議開啟=1,提高連接速度,net.ipv4.tcp_tw_reuse=1tcp_tw_recycle##表示開啟TCP連接中TIME-WAITsockets的快速回收，默認(rèn)為0，表示關(guān)閉。net.ipv4.tcp_tw_recycle=0網(wǎng)絡(luò)相關(guān)#發(fā)送與接收數(shù)據(jù)的緩存值net.core.wmem_default=87380

net.core.wmem_max=16777216

net.core.rmem_default=87380

net.core.rmem_max=16777216心跳檢測(cè)#長(zhǎng)連接的心跳包機(jī)制#設(shè)置心跳包開始時(shí)機(jī)為,發(fā)送數(shù)據(jù)包后半小時(shí)開始心跳檢查net.ipv4.tcp_keepalive_time=1800#心跳包發(fā)送間隔時(shí)間10秒net.ipv4.tcp_keepalive_intvl=10#超過3次沒有應(yīng)答,則認(rèn)為連接無效,將其丟棄net.ipv4.tcp_keepalive_probes=3內(nèi)存頁分配#kernel.shmall=_PHYS_PAGES/2#參考物理內(nèi)存頁的一半,主機(jī)32G內(nèi)存，參考如下kernel.shmall=4194304#kernel.shmmax=kernel.shmall*PAGE_SIZE#主機(jī)32G內(nèi)存，參考如下kernel.shmmaxernel.shmmni=4096#kernel.shmmax必須大于INNODB_POOL_SIZE與QUERY_CACHE及其他MySQL占用內(nèi)存總和，#建議總內(nèi)存的60%左右kernel.shmmax=2147483648交換內(nèi)存vm.swappiness=1vm.dirty_background_bytes=536870912資源限制參考樣例vim

/etc/security/limits.conf*softnofile524288*hardnofile524288*softnprocunlimited*hardnprocunlimited*softmemlockunlimited*hardmemlockunlimited*softstackunlimited*hardstackunlimited*softcoreunlimited*hardcoreunlimited其它配置cat/etc/security/limits.d/20-nproc.conf常用參數(shù)三種方式全局方式showVARIABLESlike'%connection%'#setGlobal在Mysql服務(wù)器運(yùn)行過程中會(huì)一直生效,直到mysql關(guān)閉#值得注意的是:部分參數(shù)在setglobal并不會(huì)立即生效,需要重新建立連接后才有效setGLOBALmax_connections=200;會(huì)話方式#setsession代表在當(dāng)前會(huì)話(窗口/連接)才有效,關(guān)閉會(huì)話后自動(dòng)失效#參數(shù)設(shè)置的優(yōu)先級(jí)session>global>配置文件setxxx=xxx;session可以去掉，默認(rèn)就是會(huì)話方式配置方式在f中設(shè)置connection相關(guān)max_connections#max_connections代表數(shù)據(jù)庫同時(shí)允許的最大允許連接數(shù)#連接有兩種常見狀態(tài):sleep/query#sleep代表連接處于閑置狀態(tài)#query代表連接正處于處理任務(wù)的狀態(tài)#sleep+query連接的總量不能超過max_connections的設(shè)置值#否則會(huì)出現(xiàn)經(jīng)典錯(cuò)誤:"ERROR1040：Toomanyconnetcions"showVARIABLESlike'max_connections';setglobalmax_connections=300;max_user_connections每個(gè)用戶允許的最大連接數(shù)Threadsshowstatuslike'Threads%';#Threads_connected代表當(dāng)前已經(jīng)有多少連接(Sleep+Query)#Threads_created代表歷史總共創(chuàng)建過多少個(gè)數(shù)據(jù)庫連接#Threads_running代表有幾個(gè)連接正處于"工作"狀態(tài),也是目前的并發(fā)數(shù)#Threads_cached共緩存過多少連接.如果我們?cè)贛ySQL服務(wù)器配置文件中設(shè)置了thread_cache_size，當(dāng)客戶端斷開之后，服務(wù)器處理此客戶的線程將會(huì)緩存起來以響應(yīng)下一個(gè)客戶而不是銷毀(前提是緩存數(shù)未達(dá)上限)。setglobalthread_cache_size=80;#MySQL歷史運(yùn)行過程中最大連接數(shù)的數(shù)量及時(shí)點(diǎn)showstatuslike'Max_used_connections%';根據(jù)Connections和Threads_created這兩個(gè)系統(tǒng)狀態(tài)值，我們還可以計(jì)算出系統(tǒng)新建連接連接的ThreadCache命中率，也就是通過ThreadCache池中取得連接線程的次數(shù)與系統(tǒng)接收的總連接次數(shù)的比率，如下：Threads_Cache_Hit=(Connections-Threads_created)/Connections*100%back_log#back_log設(shè)置保存多少數(shù)據(jù)庫請(qǐng)求到堆棧(緩沖區(qū))中.#也就是說，如果MySql的連接數(shù)達(dá)到max_connections時(shí)，新來的請(qǐng)求將會(huì)被存在堆棧中，以等待某一連接釋放資源，該堆棧的數(shù)量即back_log，如果等待連接的數(shù)量超過back_log，將不被授予連接資源。將會(huì)報(bào)：unauthenticateduser|xxx.xxx.xxx.xxx|NULL|Connect|NULL|login|NULL的待連接進(jìn)程時(shí).showVARIABLESLIKE'back_log'超時(shí)設(shè)置#wait_timeout和interactive_timeout#這兩個(gè)參數(shù)都是至超過一段時(shí)間后,數(shù)據(jù)庫連接自動(dòng)關(guān)閉(默認(rèn)28800秒,即8小時(shí))#interactive_timeout針對(duì)交互式連接，wait_timeout針對(duì)非交互式連接。#說得直白一點(diǎn)，通過mysql客戶端連接數(shù)據(jù)庫是交互式連接，通過jdbc連接數(shù)據(jù)庫是非交互式連接。showVARIABLESLIKE'wait_timeout';showVARIABLESLIKE'interactive_timeout';查看連接#查看當(dāng)前數(shù)據(jù)庫連接詳細(xì)狀況showprocesslist;查詢緩存表緩存table_definition_cache全局參數(shù)，默認(rèn)值為-1，最小是為400，描述的是.frm文件在定義換成里面存儲(chǔ)的總量，如果你創(chuàng)建一個(gè)比較大的值，會(huì)加快你打開表的速度。這個(gè)表定義緩存會(huì)占據(jù)一些空間，它不同于常規(guī)的表緩存，它不會(huì)用文件描述符。它的值，最小400，之后一般依據(jù)下面的簡(jiǎn)單計(jì)算來定：400+(table_open_cache/2)table_open_cache這也是全局參數(shù)，默認(rèn)是2000，最小值1最大值524288，這是mysql服務(wù)器當(dāng)前所有線程打開的表的數(shù)據(jù)量，增加這個(gè)值會(huì)增加mysqld需要的文件描述符的數(shù)量?？梢酝ㄟ^檢查Opened_tables狀態(tài)變量來檢查是否需要增加表緩存。FLUSHTABLES命令會(huì)強(qiáng)迫所有的表去關(guān)閉然后重新打開。但是table_open_cache一般是可以調(diào)整增加的，但是不能超過shell文件的上限，就是-ulimit-a的那個(gè)值table_open_cache_instances全局參數(shù)，默認(rèn)16，最小1，最大64，打開的表緩存實(shí)例的數(shù)量。為了通過減少會(huì)話間的爭(zhēng)用來提高可伸縮性，可以將打開的表緩存劃分為幾個(gè)大小為table_open_cache/table_open_cache_instances的較小緩存實(shí)例。一個(gè)會(huì)話只需要鎖定一個(gè)實(shí)例就可以訪問DML語句從下面這個(gè)圖我們可以看出表緩存需要設(shè)置的大小，一般命中率太低，我們建議適當(dāng)增加該值查詢緩存狀態(tài)showstatuslike'Qcache%';#Qcache_free_memory:QueryCache中目前剩余的內(nèi)存大小。通過這個(gè)參數(shù)我們可以較為準(zhǔn)確的觀察當(dāng)前系統(tǒng)中的QueryCache內(nèi)存大小是否足夠，是需要增多還是過多了。#Qcache_lowmen_prunes：多少條Query因?yàn)閮?nèi)存不足而被清除出QueryCache，通過Qcache_lowmem_prunes和Qcache_free_memory相互結(jié)合，能夠更清楚的了解到我們系統(tǒng)中QueryCache的內(nèi)存大小是否真的足夠，是否非常頻繁的出現(xiàn)因?yàn)閮?nèi)存不足而有Query被換出。這個(gè)數(shù)字最好是長(zhǎng)時(shí)間來看，如果這個(gè)數(shù)字在不斷增長(zhǎng)，就表示可能碎片化非常嚴(yán)重，或者內(nèi)存很少。#Qcache_total_blocks：當(dāng)前QueryCache中block的數(shù)量#Qcache_free_blocks：緩存中相鄰內(nèi)存塊的個(gè)數(shù)。如果該值顯示過大，則說明QueryCache中的內(nèi)存碎片較多了。#查詢緩存碎片率：Qcache_free_block/Qcache_total_block*100%#如果查詢緩存碎片率超過20%，可以用flushquerycache整理緩存碎片#Block默認(rèn)是4KB，設(shè)置值大對(duì)大數(shù)據(jù)查詢有好處，但是如果你查詢的都是小數(shù)據(jù)查詢，就容易造成內(nèi)存碎片和浪費(fèi)。#Qcache_hits：表示有多少次命中緩存。我們主要可以通過該值來驗(yàn)證我們的查詢能緩存的效果。數(shù)字越大緩存效果越理想。#Qcache_inserts：表示多少次未命中而插入，意思是新來的SQL請(qǐng)求在緩存中未找到，不得不執(zhí)行查詢處理，執(zhí)行查詢處理后把結(jié)果insert帶查詢緩存中。這樣的情況次數(shù)越多，表示查詢緩存應(yīng)用到的比較少，效果也就不理想。#Qcache_queries_in_cache:當(dāng)前緩存中緩存的查詢數(shù)量。#Qcache_not_cached未進(jìn)入查詢緩存的select個(gè)數(shù)設(shè)置緩存參數(shù)showglobalvariableslike'query_cache_%';#query_cache_size：查詢緩存大?。ㄗⅲ篞C存儲(chǔ)的單位最小是1024byte，所以如果你設(shè)定的一個(gè)不是1024的倍數(shù)的值。這個(gè)值會(huì)被四舍五入到最接近當(dāng)前值的等于1024的倍數(shù)的值。）#query_cache_limit：超出此大小的查詢將不被緩存#1mb,超過1mb的結(jié)果將不緩存showVARIABLESlike'query_cache_limit';setglobalquery_cache_limit=10485760;#query_cache_type：緩存類型，決定緩存什么樣子的查詢，注意這個(gè)值不能隨便設(shè)置必須設(shè)置為數(shù)字，可選值以及說明如下：0：OFF相當(dāng)于禁用了1：ON將緩存所有結(jié)果，除非你的select語句使用了SQL_NO_CACHE禁用了查詢緩存2：DENAND則只緩存select語句中通過SQL_CACHE指定需要緩存的查詢。#5.7中默認(rèn)禁用QC,需要在my.ini中進(jìn)行開啟showVARIABLESlike'query_cache_type';setglobalquery_cache_type=1;#query_cache_min_res_unit：緩存塊的最小大小，query_cache_min_res_unit的配置是一柄雙刃劍，默認(rèn)是4KB，設(shè)置值大對(duì)大數(shù)據(jù)查詢有好處，但是如果你查詢的都是小數(shù)據(jù)查詢，就容易造成內(nèi)存碎片和浪費(fèi)。#每個(gè)查詢占用緩存的平均值=（query_cache_size-Qcache_free_memory）/Qcache_queries_in_cache#查詢緩存利用率：（query_cache_size-Qcache_free_memory)/query_cache_size*100%查詢緩存利用率在25%以下的話說明query_cache_size設(shè)置過大，可以適當(dāng)減小：查詢緩存利用率在80%以上而且Qcache_lowmem_prunes>50的話說明query_cache_size可能有點(diǎn)小，要不就是碎片太多#查詢緩存命中率：Qcache_hits/(Qcache_hits+Qcache_inserts)*100% innodb相關(guān)innodb_buffer_pool_%showstatuslike'Innodb_buffer_pool_%';innodb_buffer_pool_size#134217728=128M(默認(rèn)值)innodb_buffer_pool_size參數(shù)用來設(shè)置Innodb最主要的Buffer(Innodb_Buffer_Pool)的大小，也就是緩存用戶表及索引數(shù)據(jù)的最主要緩存空間，對(duì)Innodb整體性能影響也最大，在條件允許的情況下，我們盡可能將它設(shè)大Innodb_buffer_pool_pages_dataInnodb_buffer_pool_pages_data#Innodb已使用的緩存"頁P(yáng)age"數(shù)量Innodb_buffer_pool_pages_totalInnodb全部緩存頁數(shù)量Innodb_buffer_pool_pages_free剩余緩存數(shù)量#頁面使用率計(jì)算公式result=Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%val>95%則考慮增大innodb_buffer_pool_size，建議使用物理內(nèi)存的75%val<95%則考慮減小innodb_buffer_pool_size，建議設(shè)置為：Innodb_buffer_pool_pages_data*Innodb_page_size*1.05/(1024*1024*1024)Innodb_buffer_pool_pages_miscinnodbbufferpool緩存池中當(dāng)前已經(jīng)被用作管理用途或hashindex而不能用作為普通數(shù)據(jù)頁的數(shù)目。單位是pageInnodb_buffer_pool_pages_dirtyinnodbbufferpool緩存池中臟頁的數(shù)目。單位是pageInnodb_buffer_pool_pages_flushedinnodbbufferpool緩存池中刷新頁請(qǐng)求的數(shù)目。單位是page。Innodb_buffer_pool_read_requests發(fā)生讀的總次數(shù)，也可以理解成邏輯讀Innodb_buffer_pool_reads發(fā)生從磁盤讀的次數(shù)InnodbBufferPool的Read命中率計(jì)算：(Innodb_buffer_pool_read_requests-Innodb_buffer_pool_reads)/Innodb_buffer_pool_read_requests*100%Innodb_buffer_pool_read_ahead后端預(yù)讀線程讀取到innodbbufferpool的頁的數(shù)目。單位是page。Innodb_buffer_pool_read_ahead_evicted預(yù)讀的頁數(shù)，但是沒有被讀取就從緩沖池中被替換的頁的數(shù)量，一般用來判斷預(yù)讀的效率。其它參數(shù)innodb_thread_concurrency#innodb_thread_concurrency#設(shè)置innodb線程的并發(fā)數(shù)，默認(rèn)值為0表示不被限制，若要設(shè)置則與服務(wù)器的CPU核心數(shù)相同或是CPU的核心數(shù)的2倍。showglobalVARIABLESlike'innodb_thread_concurrency';innodb_write_io_threadsinnodb_read_io_threadsinnodb_purge_threads#innodb_purge_threads當(dāng)事務(wù)成功提交后，事務(wù)所關(guān)聯(lián)的undolog已經(jīng)不再需要，故需要使用回收線程去回收，回收線程的數(shù)量默認(rèn)為1個(gè)，建議修改為cpu核數(shù)innodb_purge_threads=8innodb_flush_log_at_trx_commit#innodb_flush_log_at_trx_commit#在事務(wù)控制中,存在"事務(wù)區(qū)"來保證事務(wù)完整性,在事務(wù)提交以后,這些事務(wù)區(qū)的數(shù)據(jù)會(huì)寫入到硬盤上,同時(shí)事務(wù)操作日志(log)也需要向硬盤中寫入.這個(gè)參數(shù)就是用來控制何時(shí)寫日志數(shù)據(jù)的.#0：logbuffer將每秒一次地寫入logfile中，并且logfile的flush(刷到磁盤)操作同時(shí)進(jìn)行。該模式下在事務(wù)提交的時(shí)候，不會(huì)主動(dòng)觸發(fā)寫入磁盤的操作。#1：每次事務(wù)提交時(shí)MySQL都會(huì)把logbuffer的數(shù)據(jù)寫入logfile，并且flush(刷到磁盤)中去，該模式為系統(tǒng)默認(rèn)。#2：每次事務(wù)提交時(shí)MySQL都會(huì)把logbuffer的數(shù)據(jù)寫入logfile，但是flush(刷到磁盤)操作并不會(huì)同時(shí)進(jìn)行。該模式下，MySQL會(huì)每秒執(zhí)行一次flush(刷到磁盤)操作。#三者比較#當(dāng)設(shè)置為0，該模式速度最快，但不太安全，mysqld進(jìn)程的崩潰會(huì)導(dǎo)致上一秒鐘所有事務(wù)數(shù)據(jù)的丟失。#當(dāng)設(shè)置為1，該模式是最安全的，但也是最慢的一種方式。在mysqld服務(wù)崩潰或者服務(wù)器主機(jī)crash的情況下，binarylog只有可能丟失最多一個(gè)語句或者一個(gè)事務(wù)。。#當(dāng)設(shè)置為2，該模式速度較快，也比0安全，只有在操作系統(tǒng)崩潰或者系統(tǒng)斷電的情況下，上一秒鐘所有事務(wù)數(shù)據(jù)才可能丟失。#實(shí)際測(cè)試發(fā)現(xiàn)，該值對(duì)插入數(shù)據(jù)的速度影響非常大，設(shè)置為2時(shí)插入10000條記錄只需要兩秒，設(shè)置為0時(shí)只需要一秒，設(shè)置為1時(shí)，則需要229秒。因此，MySQL手冊(cè)也建議盡量將插入操作合并成一個(gè)事務(wù)，這樣可以大幅度提高速度。innodb_lock_wait_timeout#innodb_lock_wait_timeout鎖等待超時(shí)innodb_lock_wait_timeout=30sinnodb_file_per_table#innodb_file_per_table=1#設(shè)置獨(dú)立表空間文件xxx.ibdinnodb_doublewrite#innodb_doublewrite雙寫操作#同一份數(shù)據(jù)寫入兩次,保證數(shù)據(jù)存在一個(gè)副本,預(yù)防數(shù)據(jù)因?yàn)榻橘|(zhì)問題產(chǎn)生丟失showglobalVARIABLESlike'innodb_doublewrite';日志優(yōu)化binlog日志showvariableslike'%binlog%';binlog_cache_size“binlog_cache_size"：在事務(wù)過程中容納二進(jìn)制日志SQL語句的緩存大小。二進(jìn)制日志緩存是服務(wù)器支持事務(wù)存儲(chǔ)引擎并且服務(wù)器啟用了二進(jìn)制日志(—log-bin選項(xiàng))的前提下為每個(gè)客戶端分配的內(nèi)存，注意，是每個(gè)Client都可以分配設(shè)置大小的binlogcache空間。如果讀者朋友的系統(tǒng)中經(jīng)常會(huì)出現(xiàn)多語句事務(wù)的話，可以嘗試增加該值的大小，以獲得更有的性能。當(dāng)然，我們可以通過MySQL的以下兩個(gè)狀態(tài)變量來判斷當(dāng)前的binlog_cache_size的狀況：Binlog_cache_use和Binlog_cache_disk_use。max_binlog_cache_size“max_binlog_cache_size”：和"binlog_cache_size"相對(duì)應(yīng)，但是所代表的是binlog能夠使用的最大cache內(nèi)存大小。當(dāng)我們執(zhí)行多語句事務(wù)的時(shí)候，max_binlog_cache_size如果不夠大的話，系統(tǒng)可能會(huì)報(bào)出“Multi-statementtransactionrequiredmorethan'max_binlog_cache_size'bytesofstorage”的錯(cuò)誤。max_binlog_size“max_binlog_size”：Binlog日志最大值，一般來說設(shè)置為512M或者1G，但不能超過1G。該大小并不能非常嚴(yán)格控制Binlog大小，尤其是當(dāng)?shù)竭_(dá)Binlog比較靠近尾部而又遇到一個(gè)較大事務(wù)的時(shí)候，系統(tǒng)為了保證事務(wù)的完整性，不可能做切換日志的動(dòng)作，只能將該事務(wù)的所有SQL都記錄進(jìn)入當(dāng)前日志，直到該事務(wù)結(jié)束。這一點(diǎn)和Oracle的Redo日志有點(diǎn)不一樣，因?yàn)镺racle的Redo日志所記錄的是數(shù)據(jù)文件的物理位置的變化，而且里面同時(shí)記錄了Redo和Undo相關(guān)的信息，所以同一個(gè)事務(wù)是否在一個(gè)日志中對(duì)Oracle來說并不關(guān)鍵。而MySQL在Binlog中所記錄的是數(shù)據(jù)庫邏輯變化信息，MySQL稱之為Event，實(shí)際上就是帶來數(shù)據(jù)庫變化的DML之類的Query語句。sync_binlog“sync_binlog”：這個(gè)參數(shù)是對(duì)于MySQL系統(tǒng)來說是至關(guān)重要的，他不僅影響到Binlog對(duì)MySQL所帶來的性能損耗，而且還影響到MySQL中數(shù)據(jù)的完整性。對(duì)于“sync_binlog”參數(shù)的各種設(shè)置的說明如下：●sync_binlog=0，當(dāng)事務(wù)提交之后，MySQL不做fsync之類的磁盤同步指令刷新binlog_cache中的信息到磁盤，而讓Filesystem自行決定什么時(shí)候來做同步，或者cache滿了之后才同步到磁盤?！駍ync_binlog=n，當(dāng)每進(jìn)行n次事務(wù)提交之后，MySQL將進(jìn)行一次fsync之類的磁盤同步指令來將binlog_cache中的數(shù)據(jù)強(qiáng)制寫入磁盤。事務(wù)區(qū)日志我們可以通過如下查詢來檢測(cè)mysql中的buffer寫情況showstatuslike'innodb_log%';該值往往與innodb_flush_log_at_trx_commit設(shè)置有關(guān)，通常我們認(rèn)為物理寫越少越好復(fù)制優(yōu)化MySQL中Binlog的產(chǎn)生量是沒辦法改變的，只要我們的Query改變了數(shù)據(jù)庫中的數(shù)據(jù)，那么就必須將該Query所對(duì)應(yīng)的Event記錄到Binlog中。那我們是不是就沒有辦法優(yōu)化復(fù)制了呢？當(dāng)然不是，在MySQL復(fù)制環(huán)境中，實(shí)際上是是有8個(gè)參數(shù)可以讓我們控制需要復(fù)制或者需要忽略而不進(jìn)行復(fù)制的DB或者Table的，分別為：●Binlog_Do_DB：設(shè)定哪些數(shù)據(jù)庫（Schema）需要記錄Binlog；●Binlog_Ignore_DB：設(shè)定哪些數(shù)據(jù)庫（Schema）不要記錄Binlog；●Replicate_Do_DB：設(shè)定需要復(fù)制的數(shù)據(jù)庫（Schema），多個(gè)DB用逗號(hào)（“,”）分隔；●Replicate_Ignore_DB：設(shè)定可以忽略的數(shù)據(jù)庫（Schema）；●Replicate_Do_Table：設(shè)定需要復(fù)制的Table；●Replicate_Ignore_Table：設(shè)定可以忽略的Table；●Replicate_Wild_Do_Table：功能同Replicate_Do_Table，但可以帶通配符來進(jìn)行設(shè)置；●Replicate_Wild_Ignore_Table：功能同Replicate_Ignore_Table，可帶通配符設(shè)置；實(shí)際上，上面這八個(gè)參數(shù)中的前面兩個(gè)是設(shè)置在Master端的，而后面六個(gè)參數(shù)則是設(shè)置在Slave端的。雖然前面兩個(gè)參數(shù)和后面六個(gè)參數(shù)在功能上并沒有非常直接的關(guān)系，但是對(duì)于優(yōu)化MySQL的Replication來說都可以啟到相似的功能。當(dāng)然也有一定的區(qū)別，其主要區(qū)別如下：●如果在Master端設(shè)置前面兩個(gè)參數(shù)，不僅僅會(huì)讓Master端的Binlog記錄所帶來的IO量減少，還會(huì)讓Master端的IO線程就可以減少Binlog的讀取量，傳遞給Slave端的IO線程的Binlog量自然就會(huì)較少。這樣做的好處是可以減少網(wǎng)絡(luò)IO，減少Slave端IO線程的IO量，減少Slave端的SQL線程的工作量，從而最大幅度的優(yōu)化復(fù)制性能。當(dāng)然，在Master端設(shè)置也存在一定的弊端，因?yàn)镸ySQL的判斷是否需要復(fù)制某個(gè)Event不是根據(jù)產(chǎn)生該Event的Query所更改的數(shù)據(jù)所在的DB，而是根據(jù)執(zhí)行Query時(shí)刻所在的默認(rèn)Schema，也就是我們登錄時(shí)候指定的DB或者運(yùn)行“USEDATABASE”中所指定的DB。只有當(dāng)前默認(rèn)DB和配置中所設(shè)定的DB完全吻合的時(shí)候IO線程才會(huì)將該Event讀取給Slave的IO線程。所以如果在系統(tǒng)中出現(xiàn)在默認(rèn)DB和設(shè)定需要復(fù)制的DB不一樣的情況下改變了需要復(fù)制的DB中某個(gè)Table的數(shù)據(jù)的時(shí)候，該Event是不會(huì)被復(fù)制到Slave中去的，這樣就會(huì)造成Slave端的數(shù)據(jù)和Master的數(shù)據(jù)不一致的情況出現(xiàn)。同樣，如果在默認(rèn)Schema下更改了不需要復(fù)制的Schema中的數(shù)據(jù)，則會(huì)被復(fù)制到Slave端，當(dāng)Slave端并沒有該Schema的時(shí)候，則會(huì)造成復(fù)制出錯(cuò)而停止；●而如果是在Slave端設(shè)置后面的六個(gè)參數(shù)，在性能優(yōu)化方面可能比在Master端要稍微遜色一點(diǎn)，因?yàn)椴还苁切枰€是不需要復(fù)制的Event都被會(huì)被IO線程讀取到Slave端，這樣不僅僅增加了網(wǎng)絡(luò)IO量，也給Slave端的IO線程增加了RelayLog的寫入量。但是仍然可以減少Slave的SQL線程在Slave端的日志應(yīng)用量。雖然性能方面稍有遜色，但是在Slave端設(shè)置復(fù)制過濾機(jī)制，可以保證不會(huì)出現(xiàn)因?yàn)槟J(rèn)Schema的問題而造成Slave和Master數(shù)據(jù)不一致或者復(fù)制出錯(cuò)的問題。事務(wù)隔離READUNCOMMITTED常被成為DirtyReads（臟讀），可以說是事務(wù)上的最低隔離級(jí)別：在普通的非鎖定模式下SELECT的執(zhí)行使我們看到的數(shù)據(jù)可能并不是查詢發(fā)起時(shí)間點(diǎn)的數(shù)據(jù)，因而在這個(gè)隔離度下是非ConsistentReads（一致性讀）；READCOMMITTED這個(gè)事務(wù)隔離級(jí)別有些類似Oracle數(shù)據(jù)庫默認(rèn)的隔離級(jí)。屬于語句級(jí)別的隔離，如通過SELECT...FORUPDATE和SELECT...LOCKINSHAREMODE來執(zhí)行的請(qǐng)求僅僅鎖定索引記錄，而不鎖定之前的間隙，因而允許在鎖定的記錄后自由地插入新記錄。當(dāng)然，這與Innodb的鎖定實(shí)現(xiàn)機(jī)制有關(guān)。如果我們的Query可以很準(zhǔn)確的通過索引定位到需要鎖定的記錄，則僅僅只需要鎖定相關(guān)的索引記錄，而不需要鎖定該索引之前的間隙。但如果我們的Query通過索引檢索的時(shí)候無法通過索引準(zhǔn)確定位到需要鎖定的記錄，或者是一個(gè)基于范圍的查詢，InnoDB就必須設(shè)置next-key或gaplocks來阻塞其它用戶對(duì)范圍內(nèi)的空隙插入。ConsistentReads的實(shí)現(xiàn)機(jī)制與Oracle基本類似：每一個(gè)ConsistentRead，甚至是同一個(gè)事務(wù)中的，均設(shè)置并作為它自己的最新快照。這一隔離級(jí)別下，不會(huì)出現(xiàn)DirtyRead，但是可能出現(xiàn)Non-RepeatableReads(不可重復(fù)讀)和PhantomReads（幻讀）。REPEATABLEREADREPEATABLEREAD隔離級(jí)別是InnoDB默認(rèn)的事務(wù)隔離級(jí)。SELECT...FORUPDATE,SELECT...LOCKINSHAREMODE,UPDATE,和DELETE，這些以唯一條件搜索唯一索引的，只鎖定所找到的索引記錄，而不鎖定該索引之前的間隙。否則這些操作將使用next-key鎖定，以next-key和gaplocks鎖定找到的索引范圍，并阻塞其它用戶的新建插入。在ConsistentReads中，與前一個(gè)隔離級(jí)相比這是一個(gè)重要的差別：在這一級(jí)中，同一事務(wù)中所有的ConsistentReads均讀取第一次讀取時(shí)已確定的快照。這個(gè)約定就意味著如果在同一事務(wù)中發(fā)出幾個(gè)無格式(plain)的SELECTs，這些SELECT的相互關(guān)系是一致的。在REPEATABLEREAD隔離級(jí)別下，不會(huì)出現(xiàn)DirtyReads，也不會(huì)出現(xiàn)Non-RepeatableReads，但是仍然存在PhantomReads的可能性。SERIALIZABLESERIALIZABLE隔離級(jí)別是標(biāo)準(zhǔn)事務(wù)隔離級(jí)別中的最高級(jí)別。設(shè)置為SERIALIZABLE隔離級(jí)別之后，在事務(wù)中的任何時(shí)候所看到的數(shù)據(jù)都是事務(wù)啟動(dòng)時(shí)刻的狀態(tài)，不論在這期間有沒有其他事務(wù)已經(jīng)修改了某些數(shù)據(jù)并提交。所以，SERIALIZABLE事務(wù)隔離級(jí)別下，PhantomReads也不會(huì)出現(xiàn)。綜合建議以上四種事務(wù)隔離級(jí)別實(shí)際上就是ANSI/ISOSQL92標(biāo)準(zhǔn)所定義的四種隔離級(jí)別，Innodb全部都為我們實(shí)現(xiàn)了。對(duì)于高并發(fā)應(yīng)用來說，為了盡可能保證數(shù)據(jù)的一致性，避免并發(fā)可能帶來的數(shù)據(jù)不一致問題，自然是事務(wù)隔離級(jí)別越高越好。但是，對(duì)于Innodb來說，所使用的事務(wù)隔離級(jí)別越高，實(shí)現(xiàn)復(fù)雜度自然就會(huì)更高，所需要做的事情也會(huì)更多，整體性能也就會(huì)更差。所以，我們需要分析自己應(yīng)用系統(tǒng)的邏輯，選擇可以接受的最低事務(wù)隔離級(jí)別。以在保證數(shù)據(jù)安全一致性的同時(shí)達(dá)到最高的性能。雖然Innodb存儲(chǔ)引擎默認(rèn)的事務(wù)隔離級(jí)別是REPEATABLEREAD，但實(shí)際上在我們大部分的應(yīng)用場(chǎng)景下，都只需要READCOMMITED的事務(wù)隔離級(jí)別就可以滿足需求了。會(huì)話參數(shù)以下為64G內(nèi)存時(shí)，連接參數(shù)設(shè)置參考read_buffer_size=16Mread_rnd_buffer_size=32Msort_buffer_size=32Mjoin_buffer_size=128Mtmp_table_size=64Msort_buffer_size#sort_buffer_size每個(gè)需要排序的線程分配該大小的一個(gè)緩沖區(qū)。增加這值加速ORDERBY或GROUPBY操作sort_buffer_size是一個(gè)connection級(jí)的參數(shù)，在每個(gè)connection（session）第一次需要使用這個(gè)buffer的時(shí)候，一次性分配設(shè)置的內(nèi)存。sort_buffer_size：并不是越大越好，由于是connection級(jí)的參數(shù)，過大的設(shè)置+高并發(fā)可能會(huì)耗盡系統(tǒng)的內(nèi)存資源。例如：500個(gè)連接將會(huì)消耗500*sort_buffer_size(2M)=1G 其它參數(shù)參考后面的樣例提供常用性能監(jiān)控查詢總視圖showglobalstatuslike'Com_select%';showglobalstatuslike'Com_insert%';showglobalstatuslike'Com_update%';showglobalstatuslike'Com_delete%';showglobalstatuslike'Connections%';showglobalstatuslike'Uptime%';showglobalstatuslike'Slow_queries%';通過如上參數(shù)我們可以了解到當(dāng)前數(shù)據(jù)庫主要是以插入更新為主還是查找為主，以及各個(gè)SQL語句執(zhí)行的比例，知道這些情況后，可以對(duì)當(dāng)前數(shù)據(jù)庫設(shè)計(jì)做一個(gè)大概的了解，方便后續(xù)對(duì)數(shù)據(jù)進(jìn)行一個(gè)優(yōu)化查詢連接列表查詢select*from`performance_schema`.hosts也可以通過當(dāng)前進(jìn)程查詢：select SUBSTRING_INDEX(host,':',1)ashostip, count(*)numfrom information_cesslistgroupby hostip;查詢長(zhǎng)時(shí)間連接select *from information_cesslistwhere Command!='Sleep'orderby Timedesc;查詢慢SQL開啟慢查詢showvariableslike'slow_query%';showvariableslike'long_query_time';該命令主要查看系統(tǒng)慢查詢的資料，什么是慢查詢，慢查詢就是mysql可以設(shè)置一個(gè)時(shí)間數(shù)，當(dāng)一個(gè)sql語句執(zhí)行時(shí)間超過這個(gè)時(shí)間段，則認(rèn)為該條sql語句是慢查詢語句，然后我們可以針對(duì)該sql進(jìn)行優(yōu)化。setglobalslow_query_log='ON';setglobalslow_query_log_file='/project/slow.log';setgloballong_query_time=1;注意：以上命令只對(duì)新連接生效統(tǒng)計(jì)排名mysqldumpslow-st-t100/home/teledb/data/data_8801/mysql_data8801/logs/73_8801/slow_query_2021_12_30_00_00_03.log參數(shù)說明：-s:排序方式按鎖的時(shí)間l、返回的記錄數(shù)r、查詢的時(shí)間t、記錄的次數(shù)c,倒序的話可以加r-t:查詢前多少條記錄-g:支持正則表達(dá)式,以及忽略大小寫在這里順便說下explain吧explain用來分析mysql查詢結(jié)構(gòu)的主要關(guān)注四個(gè)參數(shù)值：type、key、rows、extras訪問類型type:al最差,ref,eq_ref居中,null最好all->index->range->ref->eq_ref->const或system->null有無使用索引key:key為空沒有使用索引找到所需記錄要讀取的行數(shù)：rows,rows值越小越好查詢分析開啟查詢showvariableslike'general_log';showvariableslike'general_log_file';showvariableslike'log_output';setglobalgeneral_log=on;setglobalgeneral_log_file='tmp/general.lg';setgloballog_output='table';setgloballog_output='file';查看各表統(tǒng)計(jì)信息SELECT table_name, table_rowsFROM information_schema.tablesawhere a.TABLE_SCHEMA='custdb_ah_1'orderby table_rowsdesc分析表執(zhí)行計(jì)劃explainselect*fromcustomer;查詢當(dāng)前進(jìn)程查詢進(jìn)程showfullprocesslist主要狀態(tài)這個(gè)命令中最關(guān)鍵的就是state列，mysql列出的狀態(tài)主要有以下幾種：Checkingtable正在檢查數(shù)據(jù)表（這是自動(dòng)的）。Closingtables正在將表中修改的數(shù)據(jù)刷新到磁盤中，同時(shí)正在關(guān)閉已經(jīng)用完的表。這是一個(gè)很快的操作，如果不是這樣的話，就應(yīng)該確認(rèn)磁盤空間是否已經(jīng)滿了或者磁盤是否正處于重負(fù)中。ConnectOut復(fù)制從服務(wù)器正在連接主服務(wù)器。Copyingtotmptableondisk由于臨時(shí)結(jié)果集大于tmp_table_size，正在將臨時(shí)表從內(nèi)存存儲(chǔ)轉(zhuǎn)為磁盤存儲(chǔ)以此節(jié)省內(nèi)存。Creatingtmptable正在創(chuàng)建臨時(shí)表以存放部分查詢結(jié)果。deletingfrommaintable服務(wù)器正在執(zhí)行多表刪除中的第一部分，剛刪除第一個(gè)表。deletingfromreferencetables服務(wù)器正在執(zhí)行多表刪除中的第二部分，正在刪除其他表的記錄。Flushingtables正在執(zhí)行FLUSHTABLES，等待其他線程關(guān)閉數(shù)據(jù)表。Killed發(fā)送了一個(gè)kill請(qǐng)求給某線程，那么這個(gè)線程將會(huì)檢查kill標(biāo)志位，同時(shí)會(huì)放棄下一個(gè)kill請(qǐng)求。MySQL會(huì)在每次的主循環(huán)中檢查kill標(biāo)志位，不過有些情況下該線程可能會(huì)過一小段才能死掉。如果該線程程被其他線程鎖住了，那么kill請(qǐng)求會(huì)在鎖釋放時(shí)馬上生效。Locked被其他查詢鎖住了。Sendingdata正在處理SELECT查詢的記錄，同時(shí)正在把結(jié)果發(fā)送給客戶端。Sortingforgroup正在為GROUPBY做排序。Sortingfororder正在為ORDERBY做排序。Openingtables這個(gè)過程應(yīng)該會(huì)很快，除非受到其他因素的干擾。例如，在執(zhí)ALTERTABLE或LOCKTABLE語句行完以前，數(shù)據(jù)表無法被其他線程打開。正嘗試打開一個(gè)表。Removingduplicates正在執(zhí)行一個(gè)SELECTDISTINCT方式的查詢，但是MySQL無法在前一個(gè)階段優(yōu)化掉那些重復(fù)的記錄。因此，MySQL需要再次去掉重復(fù)的記錄，然后再把結(jié)果發(fā)送給客戶端。Reopentable獲得了對(duì)一個(gè)表的鎖，但是必須在表結(jié)構(gòu)修改之后才能獲得這個(gè)鎖。已經(jīng)釋放鎖，關(guān)閉數(shù)據(jù)表，正嘗試重新打開數(shù)據(jù)表。Repairbysorting修復(fù)指令正在排序以創(chuàng)建索引。Repairwithkeycache修復(fù)指令正在利用索引緩存一個(gè)一個(gè)地創(chuàng)建新索引。它會(huì)比Repairbysorting慢些。Searchingrowsforupdate正在講符合條件的記錄找出來以備更新。它必須在UPDATE要修改相關(guān)的記錄之前就完成了。Sleeping正在等待客戶端發(fā)送新請(qǐng)求.Systemlock正在等待取得一個(gè)外部的系統(tǒng)鎖。如果當(dāng)前沒有運(yùn)行多個(gè)mysqld服務(wù)器同時(shí)請(qǐng)求同一個(gè)表，那么可以通過增加--skip-external-locking參數(shù)來禁止外部系統(tǒng)鎖。UpgradinglockINSERTDELAYED正在嘗試取得一個(gè)鎖表以插入新記錄。Updating正在搜索匹配的記錄，并且修改它們。UserLock正在等待GET_LOCK()。Waitingfortables該線程得到通知，數(shù)據(jù)表結(jié)構(gòu)已經(jīng)被修改了，需要重新打開數(shù)據(jù)表以取得新的結(jié)構(gòu)。然后，為了能的重新打開數(shù)據(jù)表，必須等到所有其他線程關(guān)閉這個(gè)表。以下幾種情況下會(huì)產(chǎn)生這個(gè)通知：FLUSHTABLEStbl_name,ALTERTABLE,RENAMETABLE,REPAIRTABLE,ANALYZETABLE,或OPTIMIZETABLE。waitingforhandlerinsertINSERTDELAYED已經(jīng)處理完了所有待處理的插入操作，正在等待新的請(qǐng)求。狀態(tài)查詢showstatus下面我們看幾個(gè)常用的帶選項(xiàng)的命令查詢當(dāng)前MySQL本次啟動(dòng)后的運(yùn)行統(tǒng)計(jì)時(shí)間showstatuslike'uptime';查看本次MySQL啟動(dòng)后執(zhí)行的select語句的次數(shù)showstatuslike'com_select';查看本次MySQL啟動(dòng)后執(zhí)行insert語句的次數(shù)show[global]statuslike'com_insert';查看本次MySQL啟動(dòng)后執(zhí)行update語句的次數(shù)show[global]statuslike'com_update';查看本次MySQL啟動(dòng)后執(zhí)行delete語句的次數(shù)show[global]statuslike'com_delete';查看MySQL服務(wù)器的線程信息showstatuslike'Thread_%';查看試圖連接到MySQL(不管是否連接成功)的連接數(shù)showstatuslike'connections';查看線程緩存內(nèi)的線程的數(shù)量showstatuslike'threads_cached';查看立即獲得的表的鎖的次數(shù)showstatuslike'table_locks_immediate';查看不能立即獲得的表的鎖的次數(shù)。如果該值較高，并且有性能問題，你應(yīng)首先優(yōu)化查詢，然后拆分表或使用復(fù)制showstatuslike'table_locks_waited';查看查詢時(shí)間超過long_query_time秒的查詢的個(gè)數(shù)showstatuslike'slow_queries';鎖監(jiān)控查詢查詢鎖表情況showstatuslike'Table_locks_%';Table_locks_immediate指的是能夠立即獲得表級(jí)鎖的次數(shù)Table_locks_waited指的是不能立即獲取表級(jí)鎖而需要等待的次數(shù)，如果數(shù)量大，說明鎖等待多，有鎖爭(zhēng)用情況查詢當(dāng)前事務(wù)鎖查詢當(dāng)前鎖表showOPENTABLESwhereIn_use>0;查詢被鎖事務(wù)SELECT*FROMinformation_schema.INNODB_TRXWHEREtrx_state='LOCKWAIT';查詢到鎖源select c.*from information_schema.INNODB_LOCK_WAITSa, information_schema.innodb_trxb,information_schema.`PROCESSLIST`cwhere a.blocking_trx_id=b.trx_id andb.trx_mysql_thread_id=c.id anda.requesting_trx_id=21734679查詢鎖源sql語句SELECT*FROMperformance_schema.`events_statements_current`WHEREthread_idin(selectm.THREAD_IDfrom`performance_schema`.threadsmwherem.PROCESSLIST_ID=24);SELECT*FROMperformance_schema.`events_statements_history`WHEREthread_idin(selectm.THREAD_IDfrom`performance_schema`.threadsmwherem.PROCESSLIST_ID=24);快速方法查看事務(wù)鎖SHOWSTATUSLIKE'innodb_row_lock%';快速查詢鎖select r.trx_isolation_level, r.trx_idwaiting_trx_id, r.trx_mysql_thread_idwaiting_trx_thread, r.trx_statewaiting_trx_state, lr.lock_modewaiting_trx_lock_mode, lr.lock_typewaiting_trx_lock_type, lr.lock_tablewaiting_trx_lock_table, lr.lock_indexwaiting_trx_lock_index, r.trx_querywaiting_trx_query, b.trx_idblocking_trx_id, b.trx_mysql_thread_idblocking_trx_thread, b.trx_stateblocking_trx_state, lb.lock_modeblocking_trx_lock_mode, lb.lock_typeblocking_trx_lock_type, lb.lock_tableblocking_trx_lock_table, lb.lock_indexblocking_trx_lock_index, b.trx_queryblocking_queryfrom information_schema.innodb_lock_waitsw innerjoininformation_schema.innodb_trxbonb.trx_id=w.blocking_trx_id innerjoininformation_schema.innodb_trxronr.trx_id=w.requesting_trx_id innerjoininformation_schema.innodb_lockslbonlb.lock_trx_id=w.blocking_trx_id innerjoininformation_schema.innodb_lockslronlr.lock_trx_id=w.requesting_trx_idperformance_schema開啟說明--查看performance_schema的屬性，ON表示開啟SHOWVARIABLESLIKE'performance_schema';如果要關(guān)閉性能模式，不能用命令關(guān)閉，需要修改配置文件mysql.iniperformance_schema=ON表分類說明-語句事件記錄表，這些表記錄了語句事件信息，當(dāng)前語句事件表events_statements_current、歷史語句事件表events_statements_history和長(zhǎng)語句歷史事件表events_statements_history_long、以及聚合后的摘要表summary，其中，summary表還可以根據(jù)帳號(hào)(account)，主機(jī)(host)，程序(program)，線程(thread)，用戶(user)和全局(global)再進(jìn)行細(xì)分)showtableslike'%statement%';--等待事件記錄表，與語句事件類型的相關(guān)記錄表類似：showtableslike'%wait%';--階段事件記錄表，記錄語句執(zhí)行的階段事件的表showtableslike'%stage%';--事務(wù)事件記錄表，記錄事務(wù)相關(guān)的事件的表showtableslike'%transaction%';--監(jiān)控文件系統(tǒng)層調(diào)用的表showtableslike'%file%';--監(jiān)視內(nèi)存使用的表showtableslike'%memory%';--動(dòng)態(tài)對(duì)performance_schema進(jìn)行配置的配置表showtableslike'%setup%';簡(jiǎn)單使用數(shù)據(jù)信息采集和保存性能數(shù)據(jù)項(xiàng)目都是需要設(shè)置的，默認(rèn)不會(huì)全部打開。列1：打開等待事件的采集器配置項(xiàng)開關(guān)，需要修改setup_instruments配置表中對(duì)應(yīng)的采集器配置項(xiàng)UPDATEsetup_instrumentsSETENABLED='YES',TIMED='YES'wherenamelike'wait%';列2：打開等待事件的保存表配置開關(guān)，修改setup_consumers配置表中對(duì)應(yīng)的配置項(xiàng)UPDATEsetup_consumersSETENABLED='YES'wherenamelike'%wait%';當(dāng)配置完成之后可以查看當(dāng)前server正在做什么，可以通過查詢events_waits_current表來得知，該表中每個(gè)線程只包含一行數(shù)據(jù)，用于顯示每個(gè)線程的最新監(jiān)視事件系統(tǒng)變量showvariableslike'%performance_schema%';--重要的屬性解釋performance_schema=ON/*控制performance_schema功能的開關(guān)，要使用MySQL的performance_schema，需要在mysqld啟動(dòng)時(shí)啟用，以啟用事件收集功能該參數(shù)在5.7.x之前支持performance_schema的版本中默認(rèn)關(guān)閉，5.7.x版本開始默認(rèn)開啟注意：如果mysqld在初始化performance_schema時(shí)發(fā)現(xiàn)無法分配任何相關(guān)的內(nèi)部緩沖區(qū)，則performance_schema將自動(dòng)禁用，并將performance_schema設(shè)置為OFF*/performance_schema_digests_size=10000/*控制events_statements_summary_by_digest表中的最大行數(shù)。如果產(chǎn)生的語句摘要信息超過此最大值，便無法繼續(xù)存入該表，此時(shí)performance_schema會(huì)增加狀態(tài)變量*/performance_schema_events_statements_history_long_size=10000/*控制events_statements_history_long表中的最大行數(shù)，該參數(shù)控制所有會(huì)話在events_statements_history_long表中能夠存放的總事件記錄數(shù)，超過這個(gè)限制之后，最早的記錄將被覆蓋全局變量，只讀變量，整型值，5.6.3版本引入*5.6.x版本中，5.6.5及其之前的版本默認(rèn)為10000，5.6.6及其之后的版本默認(rèn)值為-1，通常情況下，自動(dòng)計(jì)算的值都是10000*5.7.x版本中，默認(rèn)值為-1，通常情況下，自動(dòng)計(jì)算的值都是10000*/performance_schema_events_statements_history_size=10/*控制events_statements_history表中單個(gè)線程（會(huì)話）的最大行數(shù)，該參數(shù)控制單個(gè)會(huì)話在events_statements_history表中能夠存放的事件記錄數(shù)，超過這個(gè)限制之后，單個(gè)會(huì)話最早的記錄將被覆蓋全局變量，只讀變量，整型值，5.6.3版本引入*5.6.x版本中，5.6.5及其之前的版本默認(rèn)為10，5.6.6及其之后的版本默認(rèn)值為-1，通常情況下，自動(dòng)計(jì)算的值都是10*5.7.x版本中，默認(rèn)值為-1，通常情況下，自動(dòng)計(jì)算的值都是10除了statement(語句)事件之外，wait(等待)事件、state(階段)事件、transaction(事務(wù))事件，他們與statement事件一樣都有三個(gè)參數(shù)分別進(jìn)行存儲(chǔ)限制配置，有興趣的同學(xué)自行研究，這里不再贅述*/performance_schema_max_digest_length=1024/*用于控制標(biāo)準(zhǔn)化形式的SQL語句文本在存入performance_schema時(shí)的限制長(zhǎng)度，該變量與max_digest_length變量相關(guān)(max_digest_length變量含義請(qǐng)自行查閱相關(guān)資料)全局變量，只讀變量，默認(rèn)值1024字節(jié)，整型值，取值范圍0~1048576*/performance_schema_max_sql_text_length=1024/*控制存入events_statements_current，events_statements_history和events_statements_history_long語句事件表中的SQL_TEXT列的最大SQL長(zhǎng)度字節(jié)數(shù)。超出系統(tǒng)變量performance_schema_max_sql_text_length的部分將被丟棄，不會(huì)記錄，一般情況下不需要調(diào)整該參數(shù)，除非被截?cái)嗟牟糠峙c其他SQL比起來有很大差異全局變量，只讀變量，整型值，默認(rèn)值為1024字節(jié)，取值范圍為0~1048576，5.7.6版本引入降低系統(tǒng)變量performance_schema_max_sql_text_length值可以減少內(nèi)存使用，但如果匯總的SQL中，被截?cái)嗖糠钟休^大差異，會(huì)導(dǎo)致沒有辦法再對(duì)這些有較大差異的SQL進(jìn)行區(qū)分。增加該系統(tǒng)變量值會(huì)增加內(nèi)存使用，但對(duì)于匯總SQL來講可以更精準(zhǔn)地區(qū)分不同的部分。*/啟動(dòng)選項(xiàng)performance_schema_consumer_events_statements_current=TRUE是否在mysqlserver啟動(dòng)時(shí)就開啟events_statements_current表的記錄功能(該表記錄當(dāng)前的語句事件信息)，啟動(dòng)之后也可以在setup_consumers表中使用UPDATE語句進(jìn)行動(dòng)態(tài)更新setup_consumers配置表中的events_statements_current配置項(xiàng)，默認(rèn)值為TRUEperformance_schema_consumer_events_statements_history=TRUE與performance_schema_consumer_events_statements_current選項(xiàng)類似，但該選項(xiàng)是用于配置是否記錄語句事件短歷史信息，默認(rèn)為TRUEperformance_schema_consumer_events_stages_history_long=FALSE與performance_schema_consumer_events_statements_current選項(xiàng)類似，但該選項(xiàng)是用于配置是否記錄語句事件長(zhǎng)歷史信息，默認(rèn)為FALSE除了statement(語句)事件之外，還支持：wait(等待)事件、state(階段)事件、transaction(事務(wù))事件，他們與statement事件一樣都有三個(gè)啟動(dòng)項(xiàng)分別進(jìn)行配置，但這些等待事件默認(rèn)未啟用，如果需要在MySQLServer啟動(dòng)時(shí)一同啟動(dòng)，則通常需要寫進(jìn)f配置文件中performance_schema_consumer_global_instrumentation=TRUE是否在MySQLServer啟動(dòng)時(shí)就開啟全局表（如：mutex_instances、rwlock_instances、cond_instances、file_instances、users、hostsaccounts、socket_summary_by_event_name、file_summary_by_instance等大部分的全局對(duì)象計(jì)數(shù)統(tǒng)計(jì)和事件匯總統(tǒng)計(jì)信息表）的記錄功能，啟動(dòng)之后也可以在setup_consumers表中使用UPDATE語句進(jìn)行動(dòng)態(tài)更新全局配置項(xiàng)默認(rèn)值為TRUEperformance_schema_consumer_statements_digest=TRUE是否在MySQLServer啟動(dòng)時(shí)就開啟events_statements_summary_by_digest表的記錄功能，啟動(dòng)之后也可以在setup_consumers表中使用UPDATE語句進(jìn)行動(dòng)態(tài)更新digest配置項(xiàng)默認(rèn)值為TRUEperformance_schema_consumer_thread_instrumentation=TRUE是否在MySQLServer啟動(dòng)時(shí)就開啟events_xxx_summary_by_yyy_by_event_name表的記錄功能，啟動(dòng)之后也可以在setup_consumers表中使用UPDATE語句進(jìn)行動(dòng)態(tài)更新線程配置項(xiàng)默認(rèn)值為TRUEperformance_schema_instrument[=name]是否在MySQLServer啟動(dòng)時(shí)就啟用某些采集器，由于instruments配置項(xiàng)多達(dá)數(shù)千個(gè)，所以該配置項(xiàng)支持key-value模式，還支持%號(hào)進(jìn)行通配等，如下:重要配置表performance_timers/*performance_timers表中記錄了server中有哪些可用的事件計(jì)時(shí)器字段解釋： timer_name:表示可用計(jì)時(shí)器名稱，CYCLE是基于CPU周期計(jì)數(shù)器的定時(shí)器 timer_frequency:表示每秒鐘對(duì)應(yīng)的計(jì)時(shí)器單位的數(shù)量,CYCLE計(jì)時(shí)器的換算值與CPU的頻率相關(guān)、 timer_resolution:計(jì)時(shí)器精度值，表示在每個(gè)計(jì)時(shí)器被調(diào)用時(shí)額外增加的值 timer_overhead:表示在使用定時(shí)器獲取事件時(shí)開銷的最小周期值*/select*fromperformance_timers;setup_timers/*setup_timers表中記錄當(dāng)前使用的事件計(jì)時(shí)器信息字段解釋： name:計(jì)時(shí)器類型，對(duì)應(yīng)某個(gè)事件類別 timer_name:計(jì)時(shí)器類型名稱*/select*fromsetup_timers;setup_consumers/*setup_consumers表中列出了consumers可配置列表項(xiàng)字段解釋： NAME：consumers配置名稱 ENABLED：consumers是否啟用，有效值為YES或NO，此列可以使用UPDATE語句修改。*/select*fromsetup_consumers;setup_instruments/*setup_instruments表列出了instruments列表配置項(xiàng)，即代表了哪些事件支持被收集：字段解釋： NAME：instruments名稱，instruments名稱可能具有多個(gè)部分并形成層次結(jié)構(gòu) ENABLED：instrumetns是否啟用，有效值為YES或NO，此列可以使用UPDATE語句修改。如果設(shè)置為NO，則這個(gè)instruments不會(huì)被執(zhí)行，不會(huì)產(chǎn)生任何的事件信息 TIMED：instruments是否收集時(shí)間信息，有效值為YES或NO，此列可以使用UPDATE語句修改，如果設(shè)置為NO，則這個(gè)instruments不會(huì)收集時(shí)間信息*/SELECT*FROMsetup_instruments;setup_actor/*setup_actors表的初始內(nèi)容是匹配任何用戶和主機(jī)，因此對(duì)于所有前臺(tái)線程，默認(rèn)情況下啟用監(jiān)視和歷史事件收集功能字段解釋： HOST：與grant語句類似的主機(jī)名，一個(gè)具體的字符串名字，或使用“％”表示“任何主機(jī)” USER：一個(gè)具體的字符串名稱，或使用“％”表示“任何用戶” ROLE：當(dāng)前未使用，MySQL8.0中才啟用角色功能 ENABLED：是否啟用與HOST，USER，ROLE匹配的前臺(tái)線程的監(jiān)控功能，有效值為：YES或NO HISTORY：是否啟用與HOST，USER，ROLE匹配的前臺(tái)線程的歷史事件記錄功能，有效值為：YES或NO*/SELE