2024MySQL查詢優(yōu)化操作指南

只查詢一條數(shù)據(jù)為什么執(zhí)行這同樣的邏輯，不同SQL執(zhí)行時間差這業(yè)務(wù)代碼沒有變更，SQL返回時間數(shù)據(jù)庫服務(wù)器負載居高不下，要優(yōu)化，這么多SQLMySQL邏輯怎么發(fā)現(xiàn)和定位慢MySQL是如何訪問MySQL的優(yōu)分析執(zhí)行計怎么發(fā)現(xiàn)慢從當(dāng)前查詢中獲取問題從慢日志中獲取問題sysschema相關(guān)視圖中獲取問題查看MySQL服務(wù)器內(nèi)部當(dāng)前線程正在執(zhí)除非具有process權(quán)限，否則只能看到自己發(fā)起的線Select*fromShow[full]processlist;不使用full則只顯示info字段前100ID：連接標識。等于pefoance_cheathea表的RCESSLIST_ID字段，與CNNECTN_)函數(shù)返回值相同；如果需要k一個查詢需要用到它；HOST：發(fā)出該語句的客戶端主機名或者Db：該線程連接的數(shù)據(jù)庫Command：該連接當(dāng)前執(zhí)行的命令或狀態(tài)sleep（休眠），query（查詢）等等Time：線程處于當(dāng)前狀態(tài)的時間，單Info：線程正在執(zhí)行的語句或Slowquerylog記錄執(zhí)行時間超過long_query_time的SQL，并且至少需要檢查min_examined_row_limitslow_query_log參數(shù)決定慢查詢long_query_time支持微秒級TABLE,ANALYZETABLE,CHECKTABLE,CREATEINDEX,DROPINDEX,OPTIMIZETABLE,andREPAIRqueesnusnndexes參數(shù)開啟后，所有(表數(shù)據(jù)大于2行)不使用索引的查詢會被記錄。如果這類查詢較多，可以使用參數(shù)hequeesnusnndexes限制；log_throttle_queries_not_using_indexes每分鐘有多少不使用索引的查詢可被記錄在慢查詢Time：log記錄的時間 User@Host：SQL執(zhí)行的用戶以及主機 id：連接的標識Query_time：SQL執(zhí)行的時間 Lock_time：獲取鎖的時間Rows_sent：返回客戶端的結(jié)果行數(shù) Rows_examined：server層檢查的行數(shù)Thread_id：連接的標識 Errno：SQL錯誤號，0表示沒有錯誤Killed：語句終止的錯誤號，0表示正常終止 Bytes_received/sent：收到和發(fā)送的字節(jié)數(shù)Read_last：讀取索引最后一個key的次數(shù) Read_key：基于key讀取行的請求數(shù)，較大說明使用正確的索Sort_range_count：使用范圍完成的排序次數(shù) Sort_rows：排序的行數(shù)Sort_scan_count：通過掃描表完成的排序次數(shù)；Start/End：語句開始和結(jié)束Read_next：按順序取下一行數(shù)據(jù)的次數(shù)，索引范圍查找和索引掃描時該值Read_prev：按順序讀取上一行的請求數(shù)，orderbydesc查詢較優(yōu)時該值較Read_first：讀取索引中第一條數(shù)據(jù)的記錄，該值反映全索引掃Read_rnd：按固定位置讀取行的請求數(shù)，大量的回表、沒有索引的連接和對結(jié)果集排序時Read_rnd_next：讀取數(shù)據(jù)文件下一行的次數(shù)，大量表掃描、未創(chuàng)建或合理使用索引時Sort_merge_passes：排序算法合并的次數(shù)，如該值較大考慮增加sort_buffer_size的Created_tmp_disk_tables：創(chuàng)建內(nèi)部磁盤臨時Created_tmp_tables：創(chuàng)建內(nèi)部臨時使用工具分析慢查詢mysqldumpslow，mysql自帶慢查詢-r-n只顯示前n個-a不對數(shù)字和字符串進-g字符串-al平均鎖等待時間at平均查詢時間arc出現(xiàn)總l等待鎖的時間r返回總t累計查詢耗時pt-query-econatookt中的工具可以從普通日志、慢查詢?nèi)罩尽⒍M日志以及howpocet和tcpup中對SL進行分析；默認type分析慢查詢query-/data/mysql/data/node1-輸；包括總的查詢次數(shù)、Profile：對重要或較慢查詢進Rank:Responsetime“語句”的響應(yīng)時間Calls該“語句”的執(zhí)R/Call每次執(zhí)行的平均響應(yīng)時間V/M響應(yīng)時間的方差均值比（VMR）（值越大這類SQL響應(yīng)時間越趨于不同）詳細信息：Profile中各SQL的詳細信息，默認按總ExecTime降序輸pt-query-digest：提供較type：指定輸入文件的類型，默認是slowlog輸入是MySQL慢查詢binlog輸入是mysqlbinlog轉(zhuǎn)換后的二進genlog輸入是MySQLgeneraltcpdump分析tcpdump抓包內(nèi)容，建議以-x-n-q-tttt格式化抓包輸rawlog輸入不是MySQL日志，而是換行分隔的SQL語Group-by：指定分類的屬性，默認是order-by：指定排序方式，默認是Query_time：sum。聚合方式包括sum、min、max、Sysschema：MySQL5.7開始支持，由表、視圖、存儲過程和函數(shù)等一系列對象組成，本身不存儲，而是將performance_schema和information_schema中的數(shù)據(jù)已更容易理解的形式組織和呈現(xiàn)調(diào)優(yōu)和診斷。sysschema的對將性能模式數(shù)據(jù)匯總為更易于理解的形式的視圖執(zhí)行諸如PerformanceSchema配置和生成診斷報告之類的操作的存儲過查詢PerformanceSchema配置并提供格式化服務(wù)的存儲由于sysschema提供的是performanceschema的另一種訪問方式，要使用sysschema需要啟用performanceschema；【performance_schema=ON】必須啟用某些performanceschema的instruments和consumers后才能充分利用sysCALLCALLCALLCALLCALL啟用上述instruments和consumers會對性能造成影響，可快速恢復(fù)默CALL通過以下SQL啟用全部的instrumentsandUPDATEperformance_schema.setup_consumersSETENABLED=UPDATEperformance_schema.setup_instrumentsSETENABLED='YES',TIMED=Sysschema中包含兩類對象，以x$開頭的視圖返回原始數(shù)據(jù)，其余視圖返回的是經(jīng)過轉(zhuǎn)換后跟容易理Sysschema中對象有以下幾主機相關(guān)，host_summary開頭的Innodb相關(guān)，以innodb開頭的視圖（慎用IO使用情況，以IO開頭的內(nèi)存使用情況，以memory開頭的連接與會話信息，含有processlist和session的表相關(guān)信息，以schema_table開頭的索引相關(guān)信息，含有index的語句相關(guān)信息，以statement開頭的用戶相關(guān)信息，以user開頭的時間等待相關(guān)信息，以wait開頭的schema_table_statistics視圖查看table的增刪查改以及IOschema_tables_with_full_table_scans視圖查看全表掃描情況，可作為優(yōu)schema_auto_increment_columns視圖查看自增主鍵schema_index_statistics視圖查看索引的增刪改查schema_redundant_indexes和schema_unused_indexes視圖分別查statements_with_full_table_scans視圖查看全表掃描的SQLstatement_analysis視圖查看SQL匯總統(tǒng)計信息(數(shù)據(jù)來源statements_with_errors_or_warnings視圖查看出現(xiàn)error和warning的statements_with_sorting和statements_with_temp_tables視圖查看使用排序和臨時表的statements_with_runtimes_in_95th_percentile視圖查看runtime在95%的SQL，視圖數(shù)據(jù)默認avg_latency我們通過視圖statement_analysis查看總執(zhí)行時間最長的SQL：select*fromsys.statement_analysisorderbytotal_latencydesclimit1\Gquery：抽象后的Db：語句默認數(shù)據(jù)full_scan：全表掃描的話*，否則total/max/avg/lock_latency：總時間、最大時間、平均時間、總鎖等待rows_sent/rows_sent_avg：總rows_examined/rows_examined_avg：總檢查行數(shù)、平均檢查tmp_tables：創(chuàng)建臨時exec_count：執(zhí)err_count/warn_count：錯誤tmp_disk_tables：創(chuàng)建磁盤臨時rows_sortedsort_merge_passes查看innodb_buffer_page相關(guān)視圖會導(dǎo)致innodbbufferpool的掃描，非常影響性能，不要在生產(chǎn)環(huán)境訪開啟performanceschema采集指標信息會對性能造成一定影響，請謹慎選擇；（特別是打開所有02MySQL怎么訪問連接查詢FileFileHeader38字頁號、checksum、上下頁號、LSNPageHeader56字節(jié)記錄數(shù)、freespace位置等SystemRecord26字Infimum+Supremum兩個虛擬UserRecordUn-rdrd存儲的行記錄數(shù)據(jù)，每行數(shù)據(jù)包含nxtrrd指針，指向頁升序的單向鏈表FreeSpace頁面中還沒有使用的空閑空PageDirectory頁面中某些記錄的相對位置，方便二分查FileTrailer8字節(jié)checksum和fileheader結(jié)構(gòu)中的上下文頁號組成雙向鏈。每個page中還維護了一個pagedirectoryInnodb作為索引組織表，所有數(shù)據(jù)存儲在聚簇索引（primarykey）這個二級索引（secondarykey）使用索引列的大小進行記錄和頁的排序，葉子全表掃描ALL，依次掃描表中所有記錄，將符合條件的數(shù)據(jù)加入結(jié)使用索引Const：通過主鍵或唯一索引定位一條記錄，主鍵或唯一索引的等Ref：非唯一索引的等值查詢；可能會匹配到多條在二級索引中連續(xù)的Range：利用索引進行范圍查詢；包括多個單點區(qū)間和連續(xù)的范圍區(qū)Index：全索引掃描，通過遍歷索引得到結(jié)特殊情況下可以在一個查詢中使用多個二級索引，稱為index系統(tǒng)變量optimizer_switch中index_merge=on，或者index_merge=off并單獨打開、index_merge_sort_union、index_merge_intersectionindexmergeintersection：交集合并。當(dāng)查詢條件是多個二級索引等值匹配，主鍵可以是范圍匹配indexmergeunion：并集合并。當(dāng)查詢條件是多個二級索引等值匹配，主鍵可以是范圍（or）條件時indexmergesortunion：排序并集合并。當(dāng)查詢條件是多個二級索引的范圍匹配，且從某個二級索連接查詢本質(zhì)上就是將各個表中的數(shù)據(jù)依次匹配后加入結(jié)果集中并返回，不帶任何條件時結(jié)果集包含每一個表中所有與其他表所有記錄的組合，稱為笛卡爾積，連接分為內(nèi)連接和外連MySQL之前默認的連接方式為嵌套循環(huán)連接（nested-loopjoin）從8.0.18開始支持對等值條件的連接查詢使用hash，從8.0.20開始使用hashjoinBlockNested-LoopJoin，并且hashjoin開始支持外連對于nested-loopjoin嵌套循環(huán)連接，首先選擇驅(qū)動表，通過驅(qū)動表的過濾條件得到結(jié)果集。再用結(jié)果集中每一條記錄，為了優(yōu)化傳統(tǒng)的嵌套循環(huán)連接，減少被驅(qū)動表的訪問次數(shù)，MySQL提供了基于塊的嵌套循環(huán)連接方式（BlockNested-LoopJoin）進行優(yōu)化。該方法會將驅(qū)動表過濾后的結(jié)果集以及查詢需要的列存入joinbuffer這一內(nèi)存結(jié)構(gòu)中，再將join（Build）和探測（Probe）兩個階段；構(gòu)建時MySQL選擇空間較小的表，對關(guān)聯(lián)字段hash計算后存入joinbuffer中。hashjoin時joinbuffer測階段會對另一張表的關(guān)聯(lián)字段hash計算后，與joinbuffer中的hashtable進行匹配。匹配成功后返回結(jié)果。當(dāng)joinbuffer無法容納整個hashtable時，會將兩個表的數(shù)據(jù)分別溢出到塊文件中，再對每一對塊文件做hash匹配并返回結(jié)果。如果表數(shù)據(jù)較joinbuffer較少時，要注意open_files_limit的限制；優(yōu)常量傳遞。constant_propagation，x=5andx>yx=5and等值傳遞。equality_propagation，x=yandy=zandx=42x=42andy=42and移除不用的條件。trivial_condition_removal，removeconditionsthatarealwaysfalseoralways表達式計算。x=5+1x=6,注意索引列不能在函數(shù)中，例如having和where子句的合并。如查詢沒有sum，max等聚合函數(shù)以及groupby子句，優(yōu)化器將having和where常量表檢測。當(dāng)表中沒有數(shù)據(jù)或者通過主鍵和唯一索引等值匹配時，優(yōu)化器會首先執(zhí)行constanttable查詢，并將條外連接消除。當(dāng)出現(xiàn)null-rejecting也就是被驅(qū)動表在where子句中包含不為null的條件時，外連接和內(nèi)連接和相互子查詢優(yōu)化：某些符合條件的in子查詢MySQL會優(yōu)化成成semi-join的方式執(zhí)IO成本：將數(shù)據(jù)從磁盤加載到內(nèi)存的開銷，讀取一個頁面的成本默認CPU成本：條件判斷或排序等操作的開銷，檢測一條記錄的成本默認成本常數(shù)存放在mysql.server_cost和mysql.engine_cost中，可以修改cost_value值后執(zhí)行flushoptimizer_costs加維護當(dāng)前最小成本變量，提前結(jié)束成本評系統(tǒng)變量optimizer_search_depth，決定參與評估的連接表個數(shù)。默認系統(tǒng)變量optimizer_prune_level，開啟啟發(fā)式規(guī)則。默認開啟。開啟后優(yōu)化器會對各表訪問預(yù)估跳過引的頁面統(tǒng)計計算出來的。我們可以通過showtablestatusshowindex查看到表的預(yù)估的行數(shù)，索引中唯一值的數(shù)量也持久化存儲的統(tǒng)計信息是存放在mysql庫統(tǒng)計信息可以自動或手動的方式采集，如果系統(tǒng)變量nnob_tat_auto_ecac設(shè)置為N（默認值），則當(dāng)表中數(shù)據(jù)變動超過10%時MySL將異步更新該表的統(tǒng)計信息。否則只能通過anayetae的方式手動更新。由于數(shù)據(jù)庫的統(tǒng)計信息是按表來采集和存儲的，所以也可以在建表時通過tat_auto_ecac=1指定。注意：在MySQL中執(zhí)行analyzetable時，如果該表有長事務(wù)，將會阻塞其他事務(wù)對該innodb_index_stats和innodb_table_stats表中的統(tǒng)計數(shù)據(jù)也可以手動修改，只是修改后需執(zhí)行flushtable查看執(zhí)行計劃及optimizertraceMySQL提供Explain工具查看某個SQL的執(zhí)行計劃，SQL并不會真正執(zhí)行。在只讀實例上無法查看寫入SQL的執(zhí)行Explain[format=(triditional|tree|json)]Id:操作表順序。id從大到小執(zhí)行，id相同從上到下執(zhí)select_type：查詢類型。（smiple/primary/union/subquery/dependentType：查詢數(shù)據(jù)的具體訪問方式possible_key&key：可能用到的和實際選擇key_len：使用索引的長度（組合索引的一部分Ref：使用索引列做等值匹配時Row：預(yù)計掃描的行數(shù)或索引記錄Filtered：掃描的行數(shù)中符合其他條件的比例。下圖sbtest7主鍵掃描19774條記錄中，預(yù)估11.11%滿Extra：其他關(guān)鍵信息(usingwhere/usingfilesort/usingindex/using查詢類型simple：不包括union或子Primary：包含union或子查詢的最外union：子查詢中除了primary的Unionresult：使用臨時表對union結(jié)subquery：不能轉(zhuǎn)換成semi-join的不相關(guān)子dependentsubquery：不能轉(zhuǎn)換成semi-join的相關(guān)子Derived：采用物化的方式執(zhí)行的Materialized：子查詢物化后與外層進行連接數(shù)據(jù)掃描方式Const：使用主鍵或唯一索引等eq_ref：連接查詢被驅(qū)動表通過主鍵或唯一索引等值Ref：非唯一索引等值range：范Index：覆蓋索引掃ALL：全表掃usingfilesortusingtemptemporary：使用臨時usingwhereusingindex：覆蓋索引掃MySQL8.0.18開始支持Explainanalyze，Explainanalyze按format=tree格式輸出，除了返回預(yù)估的成本和行SQL，在8.0.20以后可以通過killquery或CTRL-C終止運行。hashjoin用法只能由TREE格式顯示，所以Explainanalyze也可以顯示查詢是否使用hashjoin。MySQL提供optimizertrace工具剖析SQL