MySQL的SQL查詢性能優(yōu)化技術(shù)

1、技術(shù)創(chuàng)新，變革未來MySQL的SQL查詢性能優(yōu)化技術(shù)議程基本準則企業(yè)版監(jiān)控配置&參數(shù)化數(shù)據(jù)檢查一致性VS性能查詢優(yōu)化支持Q&A基本準則3https:/products/enterprise/monitor.html4性能優(yōu)化指南1/2“小心”最佳實踐沒有兩個系統(tǒng)是完全相同的之前的情況可能不再成立思考 考慮你打算做什么!監(jiān)視你的系統(tǒng)MySQL Enterprise Monitor 是用來監(jiān)視MySQL的最好的工具確保部署在生產(chǎn)環(huán)境之前對更改做了充分的測試測試必須反映你的生產(chǎn)使用情況進行增量更改每次更改一處相對較小的變化性能優(yōu)化指南2/25注意你的要求一些選項需要在性能或數(shù)據(jù)安全性之間做出選擇您需

2、要什么?大多數(shù)情況下默認值即可確保所有的表都具有主鍵InnoDB通過主鍵組織數(shù)據(jù):主鍵包含在所有的輔助索引中，以便能夠找到實際的數(shù)據(jù)行= 主鍵越小輔助索引越小通常建議使用順序排列的主鍵，以避免在現(xiàn)有行之間插入數(shù)據(jù)行。https:/products/enterprise/monitor.html https:/products/enterprise/demo.html6Enterprise Monitor沒有測量就無法控制監(jiān)視提供基線通知發(fā)生的狀況對調(diào)查性能問題非常有用可以主動發(fā)現(xiàn)潛在問題確保配置警報，以便根據(jù)嚴重性級別對所有事件做出適當反應(yīng)！監(jiān)視示例查找要優(yōu)化的查詢8應(yīng)用程序指標手動執(zhí)行查詢并

3、確定查詢過慢或耗費了太多資源慢查詢?nèi)罩綧ySQL企業(yè)監(jiān)視器查詢分析器performance_schema.events_statements_summary_by_digestsys.statements_with_runtimes_in_95th_percentileMySQL Enterprise Monitor9提供對系統(tǒng)性能，可用性和運行狀 況的可見性實時MySQL性能監(jiān)控和警報可視化查詢分析查找并修復(fù)高成本的查詢操作系統(tǒng)和磁盤監(jiān)控識別安全漏洞自動發(fā)現(xiàn)MySQL服務(wù)器和復(fù)制拓撲與企業(yè)版功能集成（備份，審計， 防火墻等）MySQL Enterprise Monitor Query Ana

4、lyzer查找查詢進行優(yōu)化10配置11配置路徑12在改善MySQL5.6、5.7和8.0中的默認設(shè)置方面做了很多工作。工作仍持續(xù)進行一些重要的路徑:datadir數(shù)據(jù)默認存放的位置默認位置：非InnoDB 表的數(shù)據(jù)和索引InnoDB file-per-table、通用、系統(tǒng)和撤消日志表空間innodb_data_home_dirInnoDB系統(tǒng)表空間（ibdata1）的缺省路徑innodb_log_group_home_dirInnoDB重做日志文件的路徑innodb_undo_directoryInnoDB撤消表空間的路徑log_bin雙重功能：啟用二進制日志記錄并設(shè)置路徑/文件名前綴log

5、_error錯誤日志的位置配置容量設(shè)置13對性能至關(guān)重要的選項:innodb_buffer_pool_size 用于 InnoDB 工作負載key_buffer_size用于MyISAM工作負載/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size14配置innodb_buffer_pool_size最優(yōu)容量：主機具有多大內(nèi)存？減去操作系統(tǒng)和其他進程所需的內(nèi)存減去除InnoDB緩沖池以外的MySQL所需的內(nèi)存選擇上述容量值和“工作數(shù)據(jù)集”的容量的最小值MySQL5.7之后版本innodb_buffer_p

6、ool_size可以動態(tài)更改配置innodb_buffer_pool_instances15將緩沖池分割成多個實例，可以減少并發(fā)工作負載的爭用經(jīng)驗法則:innodb_buffer_pool_size=1G:1實例 innodb_buffer_pool_size16G:2G/實例 最大值:64需要使用實際的工作負載進行測試!需要重啟配置InnoDBRedolog16重做日志的總?cè)萘颗c下面兩個選項有關(guān):innodb_log_file_sizeinnodb_log_files_in_group總?cè)萘?innodb_log_file_size*innodb_log_files_in_group支持最大

7、的重做日志容量:MySQL5.5及更早版本：4GMySQL5.6,5.7&8.0:512G應(yīng)該足夠大以避免過多的檢查點重做日志越大，恢復(fù)速度越慢，但IO更好。通過重啟實例進行更改/doc/refman/8.0/en/innodb-redo-log.html配置InnoDBRedologMySQLEnterpriseMonitor顯示InnoDB重做日志的使用率17/mysql-8-0-2-more-flexible-undo-tablespace-management/18配置InnoDBUndolog只能在初始化數(shù)據(jù)目錄之前設(shè)置innodb_undo_tablespaces（5.7）MySQ

8、L8允許隨時創(chuàng)建/刪除UNDO表空間如果設(shè)置該參數(shù)，將在系統(tǒng)表空間（ibdata1）之外創(chuàng)建撤消日志表空間。優(yōu)點:保持系統(tǒng)表空間較小可以將撤消日志保留在更快的磁盤上在MySQL5.7中，撤消日志表空間可以被截斷每個撤銷日志表空間文件初始為10M配置其他19max_connections注意不要設(shè)置得過大，因為每個連接都需要內(nèi)存table_definition_cache確保所有表都可以在緩存中。如果希望有4000個表，請設(shè)置 table_definition_cache4000table_open_cache每個表可以多次打開table_open_cache_instances通常設(shè)置為16緩

9、沖和緩存20一些可用的緩沖區(qū)和緩存每個查詢的緩沖:join_buffer_size/read_buffer_size/read_rnd_buffer_size/ sort_buffer_size查詢緩存query_cache_size8.0中刪除.更多緩沖和緩存21啟用緩存是一件好事嗎?使用大的緩存和緩沖區(qū)更好嗎？緩沖和緩存22啟用緩存是一件好事嗎?使用大的緩存和緩沖區(qū)更好嗎？NO!Buffers and Caches Query Cache23Buffers and Caches Query Cache24Buffers and Caches Query Cache25啟用查詢緩存的好處非常

10、少由單個互斥鎖保護大多數(shù)工作負載最好禁用查詢緩存（默認設(shè)置）：query_cache_type= 0如果認為工作負載可以從查詢緩存中受益，請對其進行測試寫的越多，收益越少緩沖池中容納的數(shù)據(jù)越多，收益越少查詢越復(fù)雜，掃描范圍越大，則越受益通常，其他緩存解決方案是更好的選擇MySQL 8.0將其刪除緩沖和緩存為什么使用小的全局值很重要?26緩沖和緩存27因為 :某些緩沖區(qū)在每次使用時都會被分配滿一個查詢可能使用多個緩沖區(qū)，較大的緩沖區(qū)可能會導(dǎo)致使用大量內(nèi)存內(nèi)存分配相對昂貴例如，Linuxglibcmalloc在超過閾值（典型值為256k或512k）時會更 改內(nèi)存分配算法。較大分配的算法可能比較小分

11、配的算法慢40倍緩沖和緩存 join_buffer_size28在MySQL5.5及更早版本中：僅用于純索引掃描，范圍索引掃描和 不使用索引的連接不應(yīng)讓它大于每個匹配行的大小在MySQL5.6+中也用于BatchedKeyAccess（BKA）使用BKA的查詢可以受益于更大的連接緩沖區(qū)分配的最小容量！通常較小的全局值（新連接的默認值）最好：32k-256k根據(jù)需要在會話級別適量增加緩沖和緩存 sort_buffer_size29像join_buffer_size一樣，通常使用較小的全局值：32k-256k可以使用全局狀態(tài)變量Sort_merge_passes進行監(jiān)視在繁忙的服務(wù)器上以每秒幾次S

12、ort_merge_passes為目標根據(jù)需要在會話級適量增加數(shù)據(jù)驗證檢查Server SQL 模式30mysql SHOW GLOBAL VARIABLES LIKE sql_modeG* 1. row * Variable_name: sql_modeValue: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISIO N_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION/doc/refman/8.0/en/sql-mode

13、.html31Server SQL模式定義MySQL應(yīng)支持的SQL語法以及應(yīng)執(zhí)行的數(shù)據(jù)驗證檢查類型。在不同環(huán)境中使用MySQL以及將MySQL與其他數(shù)據(jù)庫服務(wù)器一起使用更加容易。這些模式分別應(yīng)用于不同的客戶端?？梢栽诒镜貫闀挘ㄟB接）設(shè)置模式，也可以為服務(wù)器全局設(shè)置模式Server SQL模式 最重要的模式1/232ANSI此模式更改語法和行為以更符合標準SQL。等效于REAL_AS_FLOAT, PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, ONLY_FULL_GROUP_BYSTRICT_TRANS_TABLES為事務(wù)性存儲引擎以及可能的情況下為非

14、事務(wù)性存儲引擎啟用嚴格的SQL模式。如果不能按照給定值插入事務(wù)表中，中止該語句。對于非事務(wù)表，如果該值出現(xiàn)在單行語句或多行語句的第一行中，則中止該語句。Server SQL模式 最重要的模式2/233TRADITIONAL使MySQL表現(xiàn)得像“傳統(tǒng)”SQL數(shù)據(jù)庫系統(tǒng)。當在列中插入不正確的值時，此模式的簡單描述是“給出錯誤而不是警告”。一旦發(fā)現(xiàn)錯誤，INSERT或UPDATE將中止。如果使用的是非事務(wù)性存儲引擎，這可能不是想要的結(jié)果，因為在錯誤之前進行的數(shù)據(jù)更改可能 不會回滾，從而導(dǎo)致“部分完成”更新。等效于STRICT_TRANS_TABLES，STRICT_ALL_TABLES，NO_ZER

15、O_IN_DATE， NO_ZERO_DATE，ERROR_FOR_DIVISION_BY_ZERO，NO_AUTO_CREATE_USER和 NO_ENGINE_SUBSTITUTIONServer SQL 模式警告34SQL模式和用戶定義的分區(qū)在數(shù)據(jù)創(chuàng)建并插入到分區(qū)表后，更改服務(wù)器SQL模式可能會導(dǎo)致此類表的行為發(fā) 生重大變化，并可能導(dǎo)致數(shù)據(jù)丟失或損壞。強烈建議，一旦使用用戶定義的分區(qū)創(chuàng)建表，就不要更改SQL模式。 SQL模式和復(fù)制復(fù)制分區(qū)表時，主服務(wù)器和從服務(wù)器上不同的SQL模式也會導(dǎo)致問題。為了獲得最佳結(jié)果，應(yīng)該始終在主服務(wù)器和從服務(wù)器上使用相同的服務(wù)器SQL模 式。數(shù)據(jù)一致性與性能3

16、5事務(wù)安全/性能?數(shù)據(jù)一致性與性能36innodb_flush_log_at_trx_commit數(shù)據(jù)安全性的可能值：1:理論上是最慢的，但是使用高速SSD時，其速度可能約為2和02:每秒刷新一次（5.6及更高版本中的innodb_flush_log_at_timeout） 如果操作系統(tǒng)崩潰，事務(wù)可能會丟失0:MySQL從不進行fsyncs如果操作系統(tǒng)崩潰，事務(wù)可能丟失默認值1;每次提交時刷新重做日志ACID里面D持久化的要求數(shù)據(jù)一致性與性能innodb_flush_log_at_trx_commit37如果innodb_flush_log_at_trx_commit=1太慢，又想要實現(xiàn) AC

17、ID的D該怎么辦?確保重做日志位于單獨的磁盤上不要試圖將各別的重做日志放在不同的磁盤上如果提交率非常高，應(yīng)該考慮使用SSD支持電池的磁盤緩存也使刷新變得更便宜數(shù)據(jù)一致性與性能sync_binlog38指定每次刷新二進制日志之間有多少個事務(wù)0:1:二進制進行輪換或操作系統(tǒng)決定在每個事物提交時最安全N:每N個提交默認值:MySQL5.6及更早:0MySQL5.7及以后:1MySQL5.6及以后版本的InnoDB支持組提交，sync_binlog=1的開 銷更少數(shù)據(jù)一致性與性能39sync_binlogsync_binlog!=1意味著如果主服務(wù)器崩潰，從服務(wù)器很可能必須重新構(gòu)建但sync_binl

18、og=0肯定是最佳性能，對嗎?默認:max_binlog_size=1G1G如今已經(jīng)不是很大OS可能緩沖完整的二進制日志當日志輪換時，最多1G會刷新到磁盤暫停所有其他提交，直到二進制日志輪換完成因此:sync_binlog=0可以獲得最佳的吞吐量sync_binlog=1可以提供可預(yù)測的性能查詢優(yōu)化更好的使用數(shù)據(jù)40MySQL優(yōu)化器41主要的訪問方法:表掃描索引掃描索引查找(refaccess)范圍掃描索引合并松散索引掃描42選擇訪問方法順序磁盤訪問為每張表找到最佳訪問方法：檢查有用的訪問方法估算使用該方法的成本選擇最低成本的方法基于成本選擇訪問方法SELECT SUM(o_totalpric

19、e) FROM ordersWHERE o_orderdate BETWEEN 1994-01-01 AND 1994-12-31;Table scanIO-cost: #pages in table * IO_BLOCK_READ_COSTCPU cost: #rows * ROW_EVALUATE_COSTRange scan (on secondary index)IO-cost: #rows_in_range * IO_BLOCK_READ_COSTCPU cost: #rows_in_range * ROW_EVALUATE_COST成本模型示例43SELECT SUM(o_tota

20、lprice) FROM ordersWHERE o_orderdate BETWEEN 1994-01-01 AND 1994-12-31;SELECT SUM(o_totalprice) FROM ordersWHERE o_orderdate BETWEEN 1994-01-01 AND 1994-06-30;成本模型示例44成本模型示例 Optimizer Trace45成本模型vs真實世界46衡量執(zhí)行時間強制索引掃描:SELECT SUM(o_totalprice)FROM orders FORCE INDEX (i_o_orderdate)WHERE o_orderdate BET

21、WEEN 1994-01-01 AND 1994-12-31;無法使用索引的原因47索引列作為函數(shù)的參數(shù)使用YEAR(o_orderdate) = 1997后綴檢索:name LIKE %son復(fù)合索引的第一列未被使用b = 10 索引定義為(a, b)類型不匹配my_string = 10字符集/字符排序不匹配t1 LEFT JOIN t2 ON t1.utf8_string = t2. latin1_string查詢優(yōu)化查詢分析48Analyzing Queries MEM, Query Analyzer49Analyzing Queries Query Analyzer Details5

22、0Analyzing Queries EXPLAIN51使用EXPLAIN輸出最終的查詢計劃:EXPLAIN SELECT * FROM t1 JOIN t2 ON t1.a = t2.a WHERE b 10 AND c 10;對正在運行的查詢執(zhí)行Explain(MySQL5.7+):EXPLAIN FOR CONNECTION connection_id;52 10 AND c 10;EXPLAIN FORMAT=JSON SELECT * FROM t1 WHERE bEXPLAINquery_block: select_id: 1, cost_info: query_cost: 17.

23、81,table: table_name: t1,access_type: range, 10),possible_keys: idx1,key: idx1,used_key_parts: b,key_length: 4,rows_examined_per_scan: 12,rows_produced_per_join: 3,filtered: 33.33,index_condition: (test.t1.bcost_info: read_cost: 17.01,eval_cost: 0.80,prefix_cost: 17.81,data_read_per_join: 63,attache

24、d_condition: (test.t1.c 10)Analyzing Queries Structured EXPLAINJSON 格式:EXPLAIN FORMAT=JSON SELECT .包含更多信息:使用的索引部分推入索引條件估算成本估算數(shù)據(jù)Analyzing Queries MySQL Workbench Visual EXPLAIN53/doc/refman/8.0/en/controlling-optimizer.html54查詢優(yōu)化控制查詢優(yōu)化器控制查詢優(yōu)化器55優(yōu)化器沒有按照預(yù)期進行添加索引和/或分析鍵分布:ANALYZETABLE強制使用指定的索引(IndexHints

25、):USEINDEX,FORCEINDEX,IGNOREINDEX強制指定連接順序:STRAIGHT_JOIN調(diào)整會話變量(OptimizerHints):優(yōu)化器切換標志:setoptimizer_switch=index_merge=off緩沖大小:setsort_buffer=8*1024*1024;控制查詢計劃評估optimizer_prune_level/optimizer_search_depthsetoptimizer_search_depth=10;optimizer_switch 系統(tǒng)變量允許控制優(yōu)化器的行為值:ON或OFF可以在運行時對全局和會話級別的值進行更改在服務(wù)器啟動時

26、設(shè)置默認值mysql SHOW GLOBAL VARIABLES LIKE optimizer_switchG* 1. row * Variable_name: optimizer_switchValue: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_in tersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=o n,mrr_cost_based=on,block_nested_loop=on,b

27、atched_key_access=off,materializat ion=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_m aterialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on56優(yōu)化切換OptimizationFlag NameBatched Key Accessbatched_key_accessBlock Nested-Loopblock_nested

28、_loopCondition Filteringcondition_fanout_filterEngine Condition Pushdownengine_condition_pushdownIndex Condition Pushdownindex_condition_pushdownIndex Extensionsuse_index_extensionsIndex Mergeindex_merge | index_merge_intersection | index_merge_sort_union | index_merge_unionMulti-Range Readmrr | mrr

29、_cost_basedSemi-joinsemijoin | firstmatch | loosescan | duplicateweedoutSubquery materializationmaterialization | subquery_materialization_cost_basedDerived table mergingderived_merge57優(yōu)化切換優(yōu)化策略O(shè)ptimizerHints58對語句執(zhí)行計劃提供更好的控制Hint 語法(在SELECT,UPDATE,INSERT,REPLACE,DELETE之后使用):SELECT /*+ HINT1(args) HINT

30、2(args) */ . FROM SELECT /*+ MAX_EXECUTION_TIME */ FROM e.g:Hints:BKA(tables) / NO_BKA(tables), BNL(tables) / NO_BNL(tables)MRR(table indexes) / NO_MRR(table indexes)SEMIJOIN / NO_SEMIJOIN(strategies), SUBQUERY(strategy)NO_ICP(table indexes)NO_RANGE_OPTIMIZATION(table indexes)QB_NAME(name)Finer gran

31、ularilty than optimizer_switch session variableOptimizer Hints598.0的新優(yōu)化器提示啟用/禁用視圖和派生表的合并:MERGE(derived_table)NO_MERGE(derived_table)連接順序JOIN_ORDER(tables)JOIN_PREFIX(tables)JOIN_SUFFIX(tables) JOIN_FIXED_ORDER()強制/忽略index_merge的選擇INDEX_MERGE(tableindexes)NO_INDEX_MERGE(tableindexes)考慮增加提示:使用新的語法重新實現(xiàn)索引提示臨時設(shè)置查詢期間的會話變量MySQL: 查詢重寫插件60重寫有問題的查詢，而不需要修改應(yīng)用程序添加提示修改連接順序更多.添加重寫規(guī)則:INSERT INTO query_rewrite.rewrite_rules (pattern, replacement ) VALUES(SELECT * FROM t1 WHERE a ? AND b = ?,SELECT * FROM t1 FORCE INDEX (a_idx) WHERE a ? AND b = ?);新的前/后查詢解析重寫API用戶可以寫自己的插件/blog/2016/02/25/30-mins