MySQL性能調(diào)優(yōu)介紹

MySQL性能調(diào)優(yōu)介紹DBA王洪權(quán)新浪微博@foreverreturn內(nèi)容概要一性能分析介紹〔簡單介紹下orazdba,oprofile,ioprofile〕二performanceschema介紹2024/3/12工具化的啟發(fā)工具化的重要性〔熟練運(yùn)用已有的工具，可以快速對數(shù)據(jù)庫進(jìn)行診斷，時(shí)刻了解你的數(shù)據(jù)庫的運(yùn)行狀況〕性能分析MySQL相關(guān)：showsession/globalstatuslike'%variables%';showfullprocesslist;showengineinnodbmutex;SHOWENGINEINNODBSTATUS;SHOWPROFILE;MySQLslowquery性能分析MySQL之外orzdba(時(shí)刻監(jiān)控innodb的各項(xiàng)指標(biāo))pt-query-digest(定位慢查詢消耗，進(jìn)行相關(guān)sql優(yōu)化)oprofile(定位cpu消耗)perftop(定位mysql內(nèi)部的熱點(diǎn))tcpdump+pt-query-digest〔捕捉異常時(shí)刻sql〕ioprofile(定位系統(tǒng)的寫入和相關(guān)fsync調(diào)用)pstack(定位異常時(shí)刻系統(tǒng)的瓶頸)pt-pmp(對pstack擴(kuò)展)Blktrace+btt〔定位io進(jìn)入到塊層的情況〕relay-fetch(加速備庫預(yù)熱，解決主從延時(shí))性能分析其他mytopinnotopmysqltuner.pldba-slow-picker.pl〔診斷異常時(shí)刻SQL)

2024/3/12mysqltuner.pl給mysql做個(gè)簡單報(bào)告2024/3/12mysqltuner.pl給mysql做個(gè)簡單報(bào)告2024/3/12orzdba時(shí)刻了解你的數(shù)據(jù)庫orzdba時(shí)刻了解你的數(shù)據(jù)庫2024/3/12oprofile介紹==oprofile是什么==oprofile也是一個(gè)開源的profiling工具，它使用硬件調(diào)試存放器來統(tǒng)計(jì)信息，進(jìn)行profiling的開銷比較小，而且可以對內(nèi)核進(jìn)行profiling。Oprofile是一個(gè)全局的抽樣統(tǒng)計(jì)工具Oprofile是一種細(xì)粒度的工具，可以為指令集或者為函數(shù)、系統(tǒng)調(diào)用或中斷處理例程收集采樣。Oprofile通過取樣來工作。使用收集到的評測數(shù)據(jù)，用戶可以很容易地找出性能問題。2024/3/12Oprofile系統(tǒng)工作流圖2024/3/12oprofile使用場景cpu無端占用高？應(yīng)用程序響應(yīng)慢？苦于沒有分析的工具？oprofile利用cpu硬件層面提供的性能計(jì)數(shù)器(performancecounter)，通過計(jì)數(shù)采樣，幫助我們從進(jìn)程、函數(shù)、代碼層面找出占用cpu的"罪魁禍?zhǔn)?。2024/3/12oprofile使用#加載oprofile內(nèi)核模塊opcontrol--init

#我們對內(nèi)核的取樣沒興趣opcontrol--setup--no-vmlinux#在開始收集采樣數(shù)據(jù)前回憶下我們的設(shè)置opcontrol--status#去除上一次采樣到的數(shù)據(jù)opcontrol--reset

#啟動oprofiled守護(hù)程序,從內(nèi)核中拉出采樣數(shù)據(jù)opcontrol--start

#運(yùn)行我們的程序

#收集采樣數(shù)據(jù)opcontrol--dump#關(guān)閉守護(hù)程序,同時(shí)準(zhǔn)備好采樣的數(shù)據(jù)opcontrol--shutdown2024/3/12oprofile獲取采樣信息

#系統(tǒng)級別的opreport--long-filenames#模塊級別的opreportimage:foo-l#源碼級別的opannotateimage:foo-s2024/3/12oprofile使用opcontrol--deinitmodprobeoprofiletimer=1$dmesg|grepoprofile|tail-n1(oprofile:usingtimerinterrupt.)opcontrol--resetpcontrol--separate=lib--no-vmlinux--start--image=/home/mysql_user/mysqlhome/bin/mysqldopcontrol--dumpopcontrol--shutdownopreport-l/home/mysql_user/mysqlhome/bin/mysqld2024/3/12oprofile診斷你的cpu異常2024/3/12ioprofile查看IO情況的利器2024/3/12ioprofile查看IO情況的利器2024/3/12ioprofile透過進(jìn)程發(fā)現(xiàn)寫入狀況2024/3/12ioprofile產(chǎn)看IO情況的利器2024/3/12ioprofile產(chǎn)看IO情況的利器PERFORMANCE_SCHEMA簡介PerformanceSchema的功能，類似于Oracle數(shù)據(jù)庫提供的豐富的系統(tǒng)表，用于將數(shù)據(jù)庫內(nèi)部的運(yùn)行情況展示出來。包括：Mutex，RWLock，IO等等。performanceschema可以使得DBA更加容易的定位系統(tǒng)瓶頸performanceschema也使得mysql的性能更加可測量化Performanceschema介紹?MySQL5.5

FileI/O,Mutexes,RWLocksetc?MySQL5.6

FileI/O TableI/O QueryDigest Statement TablelocksMySQL5.7memory,replication2024/3/12MySQL5.6PSMySQL5.6默認(rèn)開啟了performance_schema，可以動態(tài)的禁用.●FileI/O(查看最熱的文件和事件)●TableI/O〔查看最熱的表的情況〕●QueryDigest(定位熱點(diǎn)查詢和表〕●Statement〔定位熱點(diǎn)查詢和表〕●Tablelocks〔定位熱點(diǎn)表鎖〕Performance_schema配置myfperformance_schema_instrument=‘%=on’performance_schema_consumer_events_stages_current=ONperformance_schema_consumer_events_stages_history=ONperformance_schema_consumer_events_stages_history_long=ONperformance_schema_consumer_events_statements_current=ONperformance_schema_consumer_events_statements_history=ONperformance_schema_consumer_events_statements_history_long=ONperformance_schema_consumer_events_waits_current=ONperformance_schema_consumer_events_waits_history=ONperformance_schema_consumer_events_waits_history_long=ONperformance_schema_consumer_global_instrumentation=ONperformance_schema_consumer_thread_instrumentation=ONperformance_schema_consumer_statements_digest=ON默認(rèn)啟動開啟了performance_schema,可以通過DML動態(tài)啟用相關(guān)的instrument和comsumerUPDATEperformance_schema.setup_instrumentsSETENABLED='YES',TIMED='YES';UPDATEperformance_schema.setup_consumersSETENABLED='YES';2024/3/12查看performance_schema變量SHOWGLOBALVARIABLESLIKE'performance\_schema%';+++|Variable_name|Value|+++|performance_schema|ON||performance_schema_accounts_size|100||performance_schema_digests_size|10000||performance_schema_events_stages_history_long_size|10000||performance_schema_events_stages_history_size|10||performance_schema_events_statements_history_long_size|10000||performance_schema_events_statements_history_size|10||performance_schema_events_waits_history_long_size|10000||performance_schema_events_waits_history_size|10||performance_schema_hosts_size|100||performance_schema_max_cond_classes|80||performance_schema_max_cond_instances|6948||performance_schema_max_file_classes|50||performance_schema_max_file_handles|32768||performance_schema_max_file_instances|7856|+++2024/3/12查看performance_schema占用內(nèi)存大小SHOWENGINEPERFORMANCE_SCHEMASTATUS;++++|Type|Name|Status|++++|performance_schema|events_waits_current.size|184||performance_schema|events_waits_current.count|12600||performance_schema|events_waits_history.size|184||performance_schema|events_waits_history.count|21000||performance_schema|events_waits_history.memory|3864000||performance_schema|events_waits_history_long.size|184||performance_schema|events_waits_history_long.count|10000||performance_schema|events_waits_history_long.memory|1840000……………………………|performance_schema|performance_schema.memory|700101608|++++|performance_schema.memory代表占用內(nèi)存大小700M左右2024/3/12performance_schema

狀態(tài)變量SHOWSTATUSLIKE"%perf%";+++|Variable_name|Value|+++|Performance_schema_accounts_lost|0||Performance_schema_cond_classes_lost|0||Performance_schema_cond_instances_lost|0||Performance_schema_digest_lost|0||Performance_schema_file_classes_lost|0||Performance_schema_file_handles_lost|0||Performance_schema_file_instances_lost|0||Performance_schema_hosts_lost|0||Performance_schema_locker_lost|0||Performance_schema_memory_classes_lost|0||Performance_schema_mutex_classes_lost|0||Performance_schema_mutex_instances_lost|0||Performance_schema_nested_statement_lost|0||Performance_schema_program_lost|0||Performance_schema_rwlock_classes_lost|0||Performance_schema_rwlock_instances_lost|0||Performance_schema_session_connect_attrs_lost|0||Performance_schema_socket_classes_lost|0||Performance_schema_socket_instances_lost|0||Performance_schema_stage_classes_lost|0||Performance_schema_statement_classes_lost|0||Performance_schema_table_handles_lost|0||Performance_schema_table_instances_lost|0||Performance_schema_thread_classes_lost|0||Performance_schema_thread_instances_lost|0||Performance_schema_users_lost|0|+++26rowsinset(0.00sec)顯示由于內(nèi)存限制導(dǎo)致某些統(tǒng)計(jì)信息沒有計(jì)入PS中PerformanceSchemasetuptableshowtableslike'%setup%';++|Tables_in_performance_schema(%setup%)|++|setup_actors||setup_consumers||setup_instruments||setup_objects||setup_timers|++5rowsinset(0.00sec)SELECT*FROMsetup_actors;++++|HOST|USER|ROLE|++++|%|%|%|++++1rowinset(0.00sec)2024/3/12默認(rèn)情況下，監(jiān)控的表對象排除mysql/PS/IS庫的對象，其中IS庫下的表，不管是否開啟，都不會去監(jiān)控Threads表selectTHREAD_ID,NAME,TYPE,INSTRUMENTEDfromthreads;+++++|THREAD_ID|NAME|TYPE|INSTRUMENTED|+++++|1|thread/sql/main|BACKGROUND|YES||2|thread/innodb/io_ibuf_thread|BACKGROUND|YES||3|thread/innodb/io_read_thread|BACKGROUND|YES||4|thread/innodb/io_read_thread|BACKGROUND|YES||5|thread/innodb/io_log_thread|BACKGROUND|YES||6|thread/innodb/io_read_thread|BACKGROUND|YES||7|thread/innodb/io_read_thread|BACKGROUND|YES||8|thread/innodb/io_read_thread|BACKGROUND|YES||9|thread/innodb/io_read_thread|BACKGROUND|YES||10|thread/innodb/io_read_thread|BACKGROUND|YES||11|thread/innodb/io_read_thread|BACKGROUND|YES||12|thread/innodb/io_write_thread|BACKGROUND|YES||13|thread/innodb/io_write_thread|BACKGROUND|YES||14|thread/innodb/io_write_thread|BACKGROUND|YES||15|thread/innodb/io_write_thread|BACKGROUND|YES||16|thread/innodb/io_write_thread|BACKGROUND|YES||17|thread/innodb/io_write_thread|BACKGROUND|YES||18|thread/innodb/io_write_thread|BACKGROUND|YES||19|thread/innodb/io_write_thread|BACKGROUND|YES||21|thread/innodb/srv_lock_timeout_thread|BACKGROUND|YES||22|thread/innodb/srv_error_monitor_thread|BACKGROUND|YES||23|thread/innodb/srv_monitor_thread|BACKGROUND|YES||24|thread/innodb/srv_master_thread|BACKGROUND|YES||25|thread/innodb/srv_purge_thread|BACKGROUND|YES||26|thread/innodb/page_cleaner_thread|BACKGROUND|YES||27|thread/sql/signal_handler|BACKGROUND|YES||28|thread/sql/one_connection|FOREGROUND|YES||29|thread/sql/one_connection|FOREGROUND|YES||30|thread/sql/one_connection|FOREGROUND|YES||31|thread/sql/one_connection|FOREGROUND|YES||32|thread/sql/one_connection|FOREGROUND|YES|+++++31rowsinset(0.00sec)instrumentselect*fromsetup_instrumentslimit10;++++|NAME|ENABLED|TIMED|++++|wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc|YES|YES||wait/synch/mutex/sql/LOCK_des_key_file|YES|YES||wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit|YES|YES||wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit_queue|YES|YES||wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_done|YES|YES||wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_flush_queue|YES|YES||wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_index|YES|YES||wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_log|YES|YES||wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_binlog_end_pos|YES|YES||wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_sync|YES|YES|++++10rowsinset(0.01sec)updatesetup_instrumentssetENABLED='NO',TIMED='NO'wherenamelike'%mutex%';QueryOK,0rowsaffected(0.00sec)Rowsmatched:156Changed:0Warnings:02024/3/12instrumentinstrument樹形結(jié)構(gòu)，主要包括idle/wait/stage/statement，下層包括例如sync,io，再往下層可能為以劃分成mutex/cond/rwlock，最后為具體對象或者模塊。wait/synch/mutex/innodb/trx_mutex●idelsocket的空閑信息，事件記錄在socket_instances表中●stage命名規(guī)那么為stage/code_area/stage_namecode_area值為sql/mysys，stage_name表示執(zhí)行語句過程中的各個(gè)階段●stage命名規(guī)那么為statement/sql或者comsql下為具體的sql類型，com下為效勞器的一些命令相關(guān)〕●wait最為關(guān)注的局部，包括文件的操作時(shí)間，socket事件，表的io的時(shí)間統(tǒng)計(jì)，主要包含wait/io，wait/lock，wait/synch〔mutex(wait/synch/mutex)〕、讀寫鎖(wait/synch/rwlock〕consumer

select*fromsetup_consumers;+++|NAME|ENABLED|+++|events_stages_current|NO||events_stages_history|NO||events_stages_history_long|NO||events_statements_current|YES||events_statements_history|NO||events_statements_history_long|NO||events_waits_current|NO||events_waits_history|NO||events_waits_history_long|NO||global_instrumentation|YES||thread_instrumentation|YES||statements_digest|YES|+++2024/3/12其中高級別的consumer決定是否去檢查低級別的consumer2024/3/12Forfilesshowtableslike'%file%';++|Tables_in_performance_schema(%file%)|++|file_instances||file_summary_by_event_name||file_summary_by_instance|++3rowsinset(0.00sec)2024/3/12查詢讀寫top5的等待selectEVENT_NAME,COUNT_READ'Reads',COUNT_WRITEWrites,COUNT_MISCMisc,(COUNT_MISC+COUNT_WRITE+COUNT_READ)asSUM_IOfromfile_summary_by_event_nameorderby5desclimit5;++++++|EVENT_NAME|Reads|Writes|Misc|SUM_IO|++++++|wait/io/file/sql/binlog|31307|31308|0|62615||wait/io/file/innodb/innodb_data_file|2985|18661|1797|23443||wait/io/file/innodb/innodb_log_file|0|167|167|334||wait/io/file/sql/slow_log|0|57|0|57||wait/io/file/archive/FRM|0|0|0|0|++++++5rowsinset(0.00sec)2024/3/12查詢讀寫top5的fileselectFILE_NAMEFILE,COUNT_READ'Reads',COUNT_WRITEWrites,COUNT_MISCMisc,(COUNT_MISC+COUNT_WRITE+COUNT_READ)asSUM_IOfromfile_summary_by_instanceorderbySUM_IOdesclimit5;++++++|FILE|Reads|Writes|Misc|SUM_IO|++++++|/data/mysql_57/mysql-bin.000043|23042|23042|0|46084||/data/mysql_57/sbtest/sbtest4.ibd|2174|5847|255|8276||/data/mysql_57/ibdata1|0|523|347|870||/data/mysql_57/ib_logfile1|0|83|83|166||/data/mysql_57/localhost-slow.log|0|50|0|50|++++++5rowsinset(0.00sec)2024/3/12FortableI/OandLockWait相關(guān)showtableslike'%table%';++|Tables_in_performance_schema(%table%)|++|table_io_waits_summary_by_index_usage||table_io_waits_summary_by_table||table_lock_waits_summary_by_table|++3rowsinset(0.00sec)2024/3/12統(tǒng)計(jì)在表上鎖的top5selectobject_schema,object_name,count_star,sum_timer_waitfromtable_lock_waits_summary_by_tableorderbycount_stardesclimit5;+++++|object_schema|object_name|count_star|sum_timer_wait|+++++|sbtest|sbtest2|25994516|24486751926030||sbtest|sbtest1|25990180|24051565063080||sbtest|sbtest3|25980844|24401812260030||ps_helper|t3|8|13488045|+++++統(tǒng)計(jì)發(fā)生tablelock消耗時(shí)間最高的表selectOBJECT_NAME'TABLE',SUM_TIMER_READReadTM,SUM_TIMER_WRITEWriteTM,SUM_TIMER_WAITWaitTMfromtable_lock_waits_summary_by_tableorderbyWaitTMdesclimit5;+++++|TABLE|ReadTM|WriteTM|WaitTM|+++++|t3|0|13488045|13488045|+++++5rowsinset(0.01sec)2024/3/12table_io_waits_summary_by_table相關(guān)selectobject_name,count_starfromtable_io_waits_summary_by_tableorderbycount_star

desclimit5;+++|object_name|count_star|+++|sbtest1|138902806||sbtest2|138838630||sbtest3|138800938||sbtest4|138638893||t3|4|+++5rowsinset(0.01sec)2024/3/12table_io_waits_summary_by_tableselectobject_name,COUNT_STAR'ALL',COUNT_READ'Read',COUNT_WRITE'Write',COUNT_FETCH'Fetch',COUNT_INSERT'Insert',COUNT_UPDATE'Update',COUNT_DELETE'DeLete'fromtable_io_waits_summary_by_tableorderby2desclimit5;+++++++++|object_name|ALL|Read|Write|Fetch|Insert|Update|DeLete|+++++++++|sbtest1|139483816|136401303|3082513|136401303|770628|1541257|770628||sbtest2|139422240|136341095|3081145|136341095|770286|1540573|770286||sbtest3|139400772|136320090|3080682|136320090|770171|1540340|770171||sbtest4|139221894|136145170|3076724|136145170|769181|1538362|769181||t3|4|0|4|0|4|0|0|+++++++++5rowsinset(0.01sec)2024/3/12table_io_waits_summary_by_index_usage相關(guān)selectobject_name,index_name,count_starfromtable_io_waits_summary_by_index_usageorderby3desclimit5;++++|object_name|index_name|count_star|++++|sbtest1|PRIMARY|139836868||sbtest2|PRIMARY|139788897||sbtest3|PRIMARY|139768021||sbtest4|PRIMARY|139566011||sbtest1|NULL|776869|++++5rowsinset(0.02sec)2024/3/12STATEMENT相關(guān)的表showtableslike'%statement%';++|Tables_in_performance_schema(%statement%)|++|events_statements_current||events_statements_history||events_statements_history_long||events_statements_summary_by_account_by_event_name||events_statements_summary_by_digest||events_statements_summary_by_host_by_event_name||events_statements_summary_by_program||events_statements_summary_by_thread_by_event_name||events_statements_summary_by_user_by_event_name||events_statements_summary_global_by_event_name|++10rowsinset(0.00sec)events_statements_summary_by_digest為相同類型的SQL的一個(gè)聚合events_statements_current,events_statements_history，events_statements_history_long這三個(gè)表中記錄了具體的SQL，而非聚合的結(jié)果。2024/3/12events_statements_summary_by_digestselectLEFT(DIGEST_TEXT,40)'QUERY',digest"SQLMD5",SUM_TIMER_WAIT,COUNT_STARfromevents_statements_summary_by_digestorderby3desclimit5;+++++|QUERY|SQLMD5|SUM_TIMER_WAIT|COUNT_STAR|+++++|SELECTcFROMsbtest1WHEREid=?|125fdbb0acedfb306b0d00bdf1d7731f|2872295511000000|7929410||SELECTcFROMsbtest3WHEREid=?|dc1eb8a7c369e8cfd527ca328919c309|2705968173000000|7924812||SELECTDISTINCTROWcFROMsbtest1WHERE|65b685f605a2acf99f054bdfe773b2b3|996040945000000|793168|+++++5rowsinset(0.01sec)

2024/3/12events_statements_summary_by_digestselect*fromevents_statements_summary_by_digestwhereDIGEST='125fdbb0acedfb306b0d00bdf1d7731f'\G***************************1.row***************************SCHEMA_NAME:sbtestDIGEST:125fdbb0acedfb306b0d00bdf1d7731fDIGEST_TEXT:SELECTcFROMsbtest1WHEREid=?

COUNT_STAR:7956690

SUM_TIMER_WAIT:2879882788000000MIN_TIMER_WAIT:56000000AVG_TIMER_WAIT:361944000MAX_TIMER_WAIT:2283464000000

SUM_LOCK_TIME:467653283000000SUM_ROWS_AFFECTED:0

SUM_ROWS_SENT:7958257SUM_ROWS_EXAMINED:7958474SUM_CREATED_TMP_DISK_TABLES:0SUM_CREATED_TMP_TABLES:0SUM_SELECT_FULL_JOIN:0SUM_SELECT_FULL_RANGE_JOIN:0SUM_SELECT_RANGE:0SUM_SELECT_RANGE_CHECK:0SUM_SELECT_SCAN:0SUM_SORT_MERGE_PASSES:0SUM_SORT_RANGE:0SUM_SORT_ROWS:0SUM_SORT_SCAN:0SUM_NO_INDEX_USED:0SUM_NO_GOOD_INDEX_USED:0

FIRST_SEEN:2013-10-1817:43:09LAST_SEEN:2013-10-1909:48:311rowinset(0.01sec)2024/3/12當(dāng)記錄超過performance_schema_digests_size大小時(shí)，SCHEMA_NAME，DIGEST，DIGEST_TEXT會為NULL，當(dāng)counter明顯很高時(shí)，這種情況是需要調(diào)整適時(shí)調(diào)整performance_schema_digests_size大小DIEGEST_TEXT列的長度為1024，超過了就以字符串“…”代替eventshowtableslike'%event_wait%';event_waits_currentevent_waits_historyevent_waits_history_longevent_waits_summary_by_event_nameTOPeventTOPeventPerformanceschema擴(kuò)展ps_helper〔AgreattoolbyMarkLeith〕一系列的視圖和存儲過程，把information_Schema中的局部表和performanceschema中的關(guān)聯(lián)表起來了安裝：gitclones://github/MarkLeith/dbahelper.gitdbahelpercddbahelpermysql-uuser-p<ps_helper_<version>.sql目前有for5.5,5.6,5.7ps_tool〔JesperKrogh〕2024/3/12ps_helper存儲過程函數(shù)CALLdisable_current_thread();CALLenable_current_thread();calldisable_background_threads();callenable_background_threads();callcurrently_enabled(TRUE,TRUE);callonly_enable('%');callonly_enable(NULL);callonly_enable('wait/io/%');format_statement()format_path()format_time()format_bytes()慢查詢診斷PerformanceSchema –events_statements_summary_by_digestps_helper –statement_analysis –statements_with_full_table_scans –statements_with_runtimes_in_95th_percentile –statements_with_sorting –statements_with_temp_tables2024/3/12statement_analysisselect*fromstatement_analysislimit3;++++++++++++++++++|query|full_scan|exec_count|err_count|warn_count|total_latency|max_latency|avg_latency|lock_latency|rows_sent|rows_sent_avg|rows_scanned|tmp_tables|tmp_disk_tables|rows_sorted|sort_merge_passes|digest|++++++++++++++++++|SELECT`t`.*,`tt`.*,`t.....)ORDERBY`t`.`name`ASC||21679|0|0|43.86s|146.05ms|2.02ms|3.31s|274475|13|1372375|21679|21679|274475|0|8b68304a8eb97c2d076cff62a21167d2||SELECTSQL_CALC_FOUND_ROWS`wp...`post_date`DESCLIMIT?,...||4810|0|0|41.85s|22.71ms|8.70ms|774.85ms|46201|10|5387435|0|0|0|0|490fa20bc36de6c43dca95aeef7d83d0||SELECT`option_name`,`option...options`WHERE`autoload`=?|*|19968|0|0|34.50s|38.19ms|1.73ms|2.25s|5111808|256|5910528|0|0|0|0|b7a64987e6573a883cf0fa3fb17e5739|++++++++++++++++++3rowsinset(0.00sec)全表掃描top10SELECT*FROMstatements_with_full_table_scansLIMIT10;+++++++++|query|

exec_count|no_index_used_count|no_good_index_used_count|

no_index_used_pct

|rows_sent|rows_examined|digest|+++++++++|SELECT`option_name`,`option...options`WHERE`autoload`=?|17085

|17085|0|100|4373760|5057160|b7a64987e6573a883cf0fa3fb17e5739||SELECTfid,tid,SUBJECT,a...DESC,datelineDESCLIMIT?|402|402|0|100|0|5095089|1447705fdb9e6c28e29dfb5a428585f2||SELECTf.fid,f.fup,f....Yf.type,f.displayorder|400|400|0|100|52000|158400|709247de7b7f78df14308fa07a5dd7ec||SELECTstyleid,NAMEFROMcdb_stylesWHEREavailable=?|241|241|0|100|241|3133|ddf7bbd08d6ca8f98f42fa73609e13cd||SHOWTABLESLIKE?|51|51|0|100|51|51|aac8cc51f1ff864d4b6d4493fc588cce||SELECTuidFROMcdb_memberfieldsmfWHEREnickname=?|30|30|0|100|315010|429244|b57defaff24a4a1a31178cacff0a50f6||SELECTm.*,mf.*,u.gr...r.postshigherDESCLIMIT?|30|30|0|100|30|244|4420b303e7e7b549f46126bbfd1d5b52||SELECTnextrunFROMcdb_crons...>?ORDERBYnextrunLIMIT?|24|24|0|100|24|336|890a5fe0b530690038dd4bf59b386003|+++++++++10rowsinset(0.01sec)2024/3/12查看具體sqlSCHEMA_NAME:uedcwpDIGEST:b7a64987e6573a883cf0fa3fb17e5739DIGEST_TEXT:SELECT`option_name`,`option_value`FROM`wp_options`WHERE`autoload`=?

COUNT_STAR:17118SUM_TIMER_WAIT:29704802064000MIN_TIMER_WAIT:875949000AVG_TIMER_WAIT:1735296000MAX_TIMER_WAIT:38191066000SUM_ERRORS:0SUM_WARNINGS:0SUM_ROWS_AFFECTED:0SUM_ROWS_SENT:4382208SUM_ROWS_EXAMINED:5066928SUM_CREATED_TMP_DISK_TABLES:0SUM_CREATED_TMP_TABLES:0SUM_SELECT_FULL_JOIN:0SUM_SELECT_FULL_RANGE_JOIN:0SUM_SELECT_RANGE:0SUM_SELECT_RANGE_CHECK:0SUM_SELECT_SCAN:17118SUM_SORT_MERGE_PASSES:0SUM_SORT_RANGE:0SUM_SORT_ROWS:0SUM_SORT_SCAN:0SUM_NO_INDEX_USED:17118SUM_NO_GOOD_INDEX_USED:0

FIRST_SEEN:2013-10-1522:04:44LAST_SEEN:2013-10-1722:41:461rowinset(0.01sec)SELECT*FROMperformance_schema.events_statements_summary_by_digestwhereDIGEST='b7a64987e6573a883cf0fa3fb17e5739'\G;***************************1.row***************************查看具體執(zhí)行方案mysql>SELECTCURRENT_SCHEMA,SQL_TEXTFROMperformance_schema.events_statements_history_longwhereDIGEST='b7a64987e6573a883cf0fa3fb17e5739'limit1;+++|CURRENT_SCHEMA|SQL_TEXT|+++|uedcwp|SELECToption_name,option_valueFROMwp_optionsWHEREautoload='yes'|+++1rowinset(0.00sec)mysql>EXPLAINSELECToption_name,option_valueFROMuedcwp.wp_optionsWHEREautoloa