2024MySQL查询优化操作指南

只查询一条数据为什么执行这同样的逻辑，不同SQL执行时间差这业务代码没有变更，SQL返回时间数据库服务器负载居高不下，要优化，这么多SQLMySQL逻辑怎么发现和定位慢MySQL是如何访问MySQL的优分析执行计怎么发现慢从当前查询中获取问题从慢日志中获取问题sysschema相关视图中获取问题查看MySQL服务器内部当前线程正在执除非具有process权限，否则只能看到自己发起的线Select*fromShow[full]processlist;不使用full则只显示info字段前100ID：连接标识。等于pefoance_cheathea表的RCESSLIST_ID字段，与CNNECTN_)函数返回值相同；如果需要k一个查询需要用到它；HOST：发出该语句的客户端主机名或者Db：该线程连接的数据库Command：该连接当前执行的命令或状态sleep（休眠），query（查询）等等Time：线程处于当前状态的时间，单Info：线程正在执行的语句或Slowquerylog记录执行时间超过long_query_time的SQL，并且至少需要检查min_examined_row_limitslow_query_log参数决定慢查询long_query_time支持微秒级TABLE,ANALYZETABLE,CHECKTABLE,CREATEINDEX,DROPINDEX,OPTIMIZETABLE,andREPAIRqueesnusnndexes参数开启后，所有(表数据大于2行)不使用索引的查询会被记录。如果这类查询较多，可以使用参数hequeesnusnndexes限制；log_throttle_queries_not_using_indexes每分钟有多少不使用索引的查询可被记录在慢查询Time：log记录的时间 User@Host：SQL执行的用户以及主机 id：连接的标识Query_time：SQL执行的时间 Lock_time：获取锁的时间Rows_sent：返回客户端的结果行数 Rows_examined：server层检查的行数Thread_id：连接的标识 Errno：SQL错误号，0表示没有错误Killed：语句终止的错误号，0表示正常终止 Bytes_received/sent：收到和发送的字节数Read_last：读取索引最后一个key的次数 Read_key：基于key读取行的请求数，较大说明使用正确的索Sort_range_count：使用范围完成的排序次数 Sort_rows：排序的行数Sort_scan_count：通过扫描表完成的排序次数；Start/End：语句开始和结束Read_next：按顺序取下一行数据的次数，索引范围查找和索引扫描时该值Read_prev：按顺序读取上一行的请求数，orderbydesc查询较优时该值较Read_first：读取索引中第一条数据的记录，该值反映全索引扫Read_rnd：按固定位置读取行的请求数，大量的回表、没有索引的连接和对结果集排序时Read_rnd_next：读取数据文件下一行的次数，大量表扫描、未创建或合理使用索引时Sort_merge_passes：排序算法合并的次数，如该值较大考虑增加sort_buffer_size的Created_tmp_disk_tables：创建内部磁盘临时Created_tmp_tables：创建内部临时使用工具分析慢查询mysqldumpslow，mysql自带慢查询-r-n只显示前n个-a不对数字和字符串进-g字符串-al平均锁等待时间at平均查询时间arc出现总l等待锁的时间r返回总t累计查询耗时pt-query-econatookt中的工具可以从普通日志、慢查询日志、二进日志以及howpocet和tcpup中对SL进行分析；默认type分析慢查询query-/data/mysql/data/node1-输；包括总的查询次数、Profile：对重要或较慢查询进Rank:Responsetime“语句”的响应时间Calls该“语句”的执R/Call每次执行的平均响应时间V/M响应时间的方差均值比（VMR）（值越大这类SQL响应时间越趋于不同）详细信息：Profile中各SQL的详细信息，默认按总ExecTime降序输pt-query-digest：提供较type：指定输入文件的类型，默认是slowlog输入是MySQL慢查询binlog输入是mysqlbinlog转换后的二进genlog输入是MySQLgeneraltcpdump分析tcpdump抓包内容，建议以-x-n-q-tttt格式化抓包输rawlog输入不是MySQL日志，而是换行分隔的SQL语Group-by：指定分类的属性，默认是order-by：指定排序方式，默认是Query_time：sum。聚合方式包括sum、min、max、Sysschema：MySQL5.7开始支持，由表、视图、存储过程和函数等一系列对象组成，本身不存储，而是将performance_schema和information_schema中的数据已更容易理解的形式组织和呈现调优和诊断。sysschema的对将性能模式数据汇总为更易于理解的形式的视图执行诸如PerformanceSchema配置和生成诊断报告之类的操作的存储过查询PerformanceSchema配置并提供格式化服务的存储由于sysschema提供的是performanceschema的另一种访问方式，要使用sysschema需要启用performanceschema；【performance_schema=ON】必须启用某些performanceschema的instruments和consumers后才能充分利用sysCALLCALLCALLCALLCALL启用上述instruments和consumers会对性能造成影响，可快速恢复默CALL通过以下SQL启用全部的instrumentsandUPDATEperformance_schema.setup_consumersSETENABLED=UPDATEperformance_schema.setup_instrumentsSETENABLED='YES',TIMED=Sysschema中包含两类对象，以x$开头的视图返回原始数据，其余视图返回的是经过转换后跟容易理Sysschema中对象有以下几主机相关，host_summary开头的Innodb相关，以innodb开头的视图（慎用IO使用情况，以IO开头的内存使用情况，以memory开头的连接与会话信息，含有processlist和session的表相关信息，以schema_table开头的索引相关信息，含有index的语句相关信息，以statement开头的用户相关信息，以user开头的时间等待相关信息，以wait开头的schema_table_statistics视图查看table的增删查改以及IOschema_tables_with_full_table_scans视图查看全表扫描情况，可作为优schema_auto_increment_columns视图查看自增主键schema_index_statistics视图查看索引的增删改查schema_redundant_indexes和schema_unused_indexes视图分别查statements_with_full_table_scans视图查看全表扫描的SQLstatement_analysis视图查看SQL汇总统计信息(数据来源statements_with_errors_or_warnings视图查看出现error和warning的statements_with_sorting和statements_with_temp_tables视图查看使用排序和临时表的statements_with_runtimes_in_95th_percentile视图查看runtime在95%的SQL，视图数据默认avg_latency我们通过视图statement_analysis查看总执行时间最长的SQL：select*fromsys.statement_analysisorderbytotal_latencydesclimit1\Gquery：抽象后的Db：语句默认数据full_scan：全表扫描的话*，否则total/max/avg/lock_latency：总时间、最大时间、平均时间、总锁等待rows_sent/rows_sent_avg：总rows_examined/rows_examined_avg：总检查行数、平均检查tmp_tables：创建临时exec_count：执err_count/warn_count：错误tmp_disk_tables：创建磁盘临时rows_sortedsort_merge_passes查看innodb_buffer_page相关视图会导致innodbbufferpool的扫描，非常影响性能，不要在生产环境访开启performanceschema采集指标信息会对性能造成一定影响，请谨慎选择；（特别是打开所有02MySQL怎么访问连接查询FileFileHeader38字页号、checksum、上下页号、LSNPageHeader56字节记录数、freespace位置等SystemRecord26字Infimum+Supremum两个虚拟UserRecordUn-rdrd存储的行记录数据，每行数据包含nxtrrd指针，指向页升序的单向链表FreeSpace页面中还没有使用的空闲空PageDirectory页面中某些记录的相对位置，方便二分查FileTrailer8字节checksum和fileheader结构中的上下文页号组成双向链。每个page中还维护了一个pagedirectoryInnodb作为索引组织表，所有数据存储在聚簇索引（primarykey）这个二级索引（secondarykey）使用索引列的大小进行记录和页的排序，叶子全表扫描ALL，依次扫描表中所有记录，将符合条件的数据加入结使用索引Const：通过主键或唯一索引定位一条记录，主键或唯一索引的等Ref：非唯一索引的等值查询；可能会匹配到多条在二级索引中连续的Range：利用索引进行范围查询；包括多个单点区间和连续的范围区Index：全索引扫描，通过遍历索引得到结特殊情况下可以在一个查询中使用多个二级索引，称为index系统变量optimizer_switch中index_merge=on，或者index_merge=off并单独打开、index_merge_sort_union、index_merge_intersectionindexmergeintersection：交集合并。当查询条件是多个二级索引等值匹配，主键可以是范围匹配indexmergeunion：并集合并。当查询条件是多个二级索引等值匹配，主键可以是范围（or）条件时indexmergesortunion：排序并集合并。当查询条件是多个二级索引的范围匹配，且从某个二级索连接查询本质上就是将各个表中的数据依次匹配后加入结果集中并返回，不带任何条件时结果集包含每一个表中所有与其他表所有记录的组合，称为笛卡尔积，连接分为内连接和外连MySQL之前默认的连接方式为嵌套循环连接（nested-loopjoin）从8.0.18开始支持对等值条件的连接查询使用hash，从8.0.20开始使用hashjoinBlockNested-LoopJoin，并且hashjoin开始支持外连对于nested-loopjoin嵌套循环连接，首先选择驱动表，通过驱动表的过滤条件得到结果集。再用结果集中每一条记录，为了优化传统的嵌套循环连接，减少被驱动表的访问次数，MySQL提供了基于块的嵌套循环连接方式（BlockNested-LoopJoin）进行优化。该方法会将驱动表过滤后的结果集以及查询需要的列存入joinbuffer这一内存结构中，再将join（Build）和探测（Probe）两个阶段；构建时MySQL选择空间较小的表，对关联字段hash计算后存入joinbuffer中。hashjoin时joinbuffer测阶段会对另一张表的关联字段hash计算后，与joinbuffer中的hashtable进行匹配。匹配成功后返回结果。当joinbuffer无法容纳整个hashtable时，会将两个表的数据分别溢出到块文件中，再对每一对块文件做hash匹配并返回结果。如果表数据较joinbuffer较少时，要注意open_files_limit的限制；优常量传递。constant_propagation，x=5andx>yx=5and等值传递。equality_propagation，x=yandy=zandx=42x=42andy=42and移除不用的条件。trivial_condition_removal，removeconditionsthatarealwaysfalseoralways表达式计算。x=5+1x=6,注意索引列不能在函数中，例如having和where子句的合并。如查询没有sum，max等聚合函数以及groupby子句，优化器将having和where常量表检测。当表中没有数据或者通过主键和唯一索引等值匹配时，优化器会首先执行constanttable查询，并将条外连接消除。当出现null-rejecting也就是被驱动表在where子句中包含不为null的条件时，外连接和内连接和相互子查询优化：某些符合条件的in子查询MySQL会优化成成semi-join的方式执IO成本：将数据从磁盘加载到内存的开销，读取一个页面的成本默认CPU成本：条件判断或排序等操作的开销，检测一条记录的成本默认成本常数存放在mysql.server_cost和mysql.engine_cost中，可以修改cost_value值后执行flushoptimizer_costs加维护当前最小成本变量，提前结束成本评系统变量optimizer_search_depth，决定参与评估的连接表个数。默认系统变量optimizer_prune_level，开启启发式规则。默认开启。开启后优化器会对各表访问预估跳过引的页面统计计算出来的。我们可以通过showtablestatusshowindex查看到表的预估的行数，索引中唯一值的数量也持久化存储的统计信息是存放在mysql库统计信息可以自动或手动的方式采集，如果系统变量nnob_tat_auto_ecac设置为N（默认值），则当表中数据变动超过10%时MySL将异步更新该表的统计信息。否则只能通过anayetae的方式手动更新。由于数据库的统计信息是按表来采集和存储的，所以也可以在建表时通过tat_auto_ecac=1指定。注意：在MySQL中执行analyzetable时，如果该表有长事务，将会阻塞其他事务对该innodb_index_stats和innodb_table_stats表中的统计数据也可以手动修改，只是修改后需执行flushtable查看执行计划及optimizertraceMySQL提供Explain工具查看某个SQL的执行计划，SQL并不会真正执行。在只读实例上无法查看写入SQL的执行Explain[format=(triditional|tree|json)]Id:操作表顺序。id从大到小执行，id相同从上到下执select_type：查询类型。（smiple/primary/union/subquery/dependentType：查询数据的具体访问方式possible_key&key：可能用到的和实际选择key_len：使用索引的长度（组合索引的一部分Ref：使用索引列做等值匹配时Row：预计扫描的行数或索引记录Filtered：扫描的行数中符合其他条件的比例。下图sbtest7主键扫描19774条记录中，预估11.11%满Extra：其他关键信息(usingwhere/usingfilesort/usingindex/using查询类型simple：不包括union或子Primary：包含union或子查询的最外union：子查询中除了primary的Unionresult：使用临时表对union结subquery：不能转换成semi-join的不相关子dependentsubquery：不能转换成semi-join的相关子Derived：采用物化的方式执行的Materialized：子查询物化后与外层进行连接数据扫描方式Const：使用主键或唯一索引等eq_ref：连接查询被驱动表通过主键或唯一索引等值Ref：非唯一索引等值range：范Index：覆盖索引扫ALL：全表扫usingfilesortusingtemptemporary：使用临时usingwhereusingindex：覆盖索引扫MySQL8.0.18开始支持Explainanalyze，Explainanalyze按format=tree格式输出，除了返回预估的成本和行SQL，在8.0.20以后可以通过killquery或CTRL-C终止运行。hashjoin用法只能由TREE格式显示，所以Explainanalyze也可以显示查询是否使用hashjoin。MySQL提供optimizertrace工具剖析SQL