MySQL查询优化
只查询一条数据为什么执行这么慢？
同样的逻辑，不同SQL执行时间差这么多？
业务代码没有变更，SQL返回时间怎么突然增加了？
数据库服务器负载居高不下，要优化，这么多SQL从哪下手？
怎么发现和定位慢SQL
MySQL是如何访问数据的
MySQL的优化器
分析执行计划
tips
01 怎么发现和定位慢SQL
从当前查询中获取问题SQL
从慢日志中获取问题SQL
sys schema相关视图中获取问题SQL
查看MySQL服务器内部当前线程正在执行的操作
• 除非具有process权限，否则只能看到自己发起的线程信息
• Select * from information_Schema.processlist;
• Show [full] processlist; 不使用full则只显示info字段前100个字符
ID：连接标识。等于performance_schema.threads表的PROCESSLIST_ID字段，与CONNECTION_ID()函数返
回值相同；如果需要kill一个查询需要用到它；
•USER：执行该操作的用户；
•HOST：发出该语句的客户端主机名或者IP：PORT；
•Db：该线程连接的数据库；
•Command：该连接当前执行的命令或状态；包括 sleep（休眠），query（查询）等等
https://dev.mysql.com/doc/refman/8.0/en/thread-commands.html
•Time：线程处于当前状态的时间，单位是秒；
•State：当前线程正在执行的动作、事件和状态；一个SQL可能包含多个状态，大多数状态都是非常快速的，
如果持续数秒则需要重点关注；这是分析问题最重要的一项指标；
https://dev.mysql.com/doc/refman/8.0/en/general-thread-states.html
•Info：线程正在执行的语句或Null；
• Slow query log 记录执行时间超过long_query_time的SQL，并且至少需要检查min_examined_row_limit行
• slow_query_log参数决定慢查询日志是否打开；
• long_query_time支持微秒级精度；
• log_slow_admin_statements参数开启后，执行较慢的管理语句将会被记录在慢查询日志中，包括ALTER TABLE, ANALYZE TABLE, CHECK TABLE, CREATE INDEX, DROP INDEX, OPTIMIZE TABLE, and REPAIR TABLE；
• log_queries_not_using_indexes参数开启后，所有(表数据大于2行)不使用索引的查询会被记录。如果这类查询较多，可以使用参数log_throttle_queries_not_using_indexes限制；
• log_throttle_queries_not_using_indexes每分钟有多少不使用索引的查询可被记录在慢查询日志中；
log_slow_extra=off
log_slow_extra=on(8.0.14+)
•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：按顺序读取上一行的请求数，order by desc查询较优时该值较大；
•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 字符串过滤
• -s
• al平均锁等待时间排序
• at平均查询时间排序
• ar平均返回行数排序
• c 出现总次数排序
• l等待锁的时间排序
• r 返回总行数排序
• t 累计查询耗时排序
• 使用mysqldumpslow分别对慢查询日志按执行次数、锁定时间、返回行数和执行时间分析排序后输出
•pt-query-digest
•Percona-toolkit中的工具可以从普通日志、慢查询日志、二进制日志以及show processlist和tcpdump中对SQL进行分析；
•默认type分析慢查询日志： pt-query-digest/data/mysql/data/node1-slow.log
•输出分三大部分：
•整体概要：对当前实例各项指标进行统计和初步的分析；包括总的查询次数、SQL数量、QPS以及并发；对执行时间、锁、检查返回行数等指标进行统计；
• Profile：对重要或较慢查询进一步分析
• Rank:排名
• Response time “语句”的响应时间以及整体占比情况。
• Calls 该“语句”的执行次数。
• R/Call 每次执行的平均响应时间。
• V/M 响应时间的方差均值比（VMR） （值越大这类SQL响应时间越趋于不同）。
详细信息：Profile中各SQL的详细信息，默认按总的Exec Time降序输出
pt-query-digest：提供较丰富的参数
•type：指定输入文件的类型，默认是slowlog
•slowlog输入是MySQL慢查询日志
•binlog 输入是mysqlbinlog转换后的二进制日志
•genlog 输入是MySQL general log
•tcpdump 分析tcpdump抓包内容，建议以-x -n -q -tttt格式化抓包输出
•rawlog 输入不是MySQL日志，而是换行分隔的SQL语句；
• Group-by：指定分类的属性，默认是fingerprint
• order-by：指定排序方式，默认是Query_time：sum。聚合方式包括sum、min、max、cnt
•Sys schema：MySQL5.7开始支持，由表、视图、存储过程和函数等一系列对象组成，本身不存储数据，而是将performance_schema和information_schema中的数据已更容易理解的形式组织和呈现，可用于调优和诊断。sys schema的对象包括：
•将性能模式数据汇总为更易于理解的形式的视图。
•执行诸如Performance Schema配置和生成诊断报告之类的操作的存储过程。
•查询Performance Schema配置并提供格式化服务的存储函数。
•由于sys schema提供的是performance schema的另一种访问方式，要使用sys schema需要启用performance schema；【performance_schema=ON】
•必须启用某些performance schema的instruments和consumers后才能充分利用sys的功能；
•CALL sys.ps_setup_enable_instrument('wait');
•CALL sys.ps_setup_enable_instrument('stage');
•CALL sys.ps_setup_enable_instrument('statement');
•CALL sys.ps_setup_enable_consumer('current');
•CALL sys.ps_setup_enable_consumer(‘history_long');
•启用上述instruments和consumers会对性能造成影响，可快速恢复默认值
•CALL sys.ps_setup_reset_to_default(TRUE);
•通过以下SQL启用全部的instruments and consumers
•UPDATE performance_schema.setup_consumers SET ENABLED = 'YES';
•UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED = 'YES';
• Sys schema中包含两类对象，以x$开头的视图返回原始数据，其余视图返回的是经过转换后跟容易理解方便阅读的数据
• Sys schema中对象有以下几类
• 主机相关，host_summary开头的视图
• Innodb相关，以innodb开头的视图（慎用）
• IO使用情况，以IO开头的视图
• 内存使用情况，以memory开头的视图
• 连接与会话信息，含有processlist和session的视图
• 表相关信息，以schema_table开头的视图
• 索引相关信息，含有index的视图
• 语句相关信息，以statement开头的视图
• 用户相关信息，以user开头的视图
• 时间等待相关信息，以wait开头的视图
•schema_table_statistics视图查看table的增删查改以及IO等情况
•schema_tables_with_full_table_scans视图查看全表扫描情况，可作为优化的重点
•schema_auto_increment_columns视图查看自增主键的情况
•schema_index_statistics视图查看索引的增删改查情况
•schema_redundant_indexes和schema_unused_indexes 视图分别查看冗余索引和未被使用索引的情况
•statements_with_full_table_scans视图查看全表扫描的SQL信息
•statement_analysis视图查看SQL汇总统计信息(数据来源events_statements_summary_by_digest)
•statements_with_errors_or_warnings视图查看出现error和warning的SQL
•statements_with_sorting和statements_with_temp_tables 视图查看使用排序和临时表的SQL
•statements_with_runtimes_in_95th_percentile 视图查看runtime在95%的SQL，视图数据默认按avg_latency倒序排列
•我们通过视图statement_analysis查看总执行时间最长的SQL：select * from sys.statement_analysis order by total_latency desc limit 1\G
•query：抽象后的SQL
•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_sorted：排序次数
•sort_merge_passes：合并排序次数
•查看innodb_buffer_page相关视图会导致innodb buffer pool的扫描，非常影响性能，不要在生产环境访问；应在测试环境重现问题，并在测试环境查看；
•开启performance schema采集指标信息会对性能造成一定影响，请谨慎选择；（特别是打开所有instruments和consumers后）
02 MySQL怎么访问数据
记录存储结构
单表访问方法
连接查询原理
Innodb表空间分成多个段（segment）包括叶子节点段、非叶子节点段、回滚段等等，段是由多个区（extent）构成的。每个区是一段连续的存储空间由64个页组成。页是Innodb存储管理的基本单位，页的默认大小是16K，不同类型的数据存储在不同的页中。
Innodb中存储数据的页叫索引页（FIL_PAGE_INDEX）。如图所示，索引页可以大致分为7部分，除了存储用户记录外还包括header和trailer等数据结构。索引页由file header结构中的上下文页号组成双向链表，用户数据是按行存储在page中的，并且由next_record指针在page中组成单向链表。每个page中还维护了一个page directory结构，将排序后的行进行分组，支持二分查找快速定位数据。
Innodb作为索引组织表，所有数据存储在聚簇索引（primary key）这个B+tree的叶子节点中，使用主键的大小进行记录和页的排序。
二级索引（secondary key）使用索引列的大小进行记录和页的排序，叶子节点记录索引列和主键的值。由于内节点中索引数据需要满足唯一性，非唯一索引内节点的key还包含主键值。访问二级索引拿到主键后，还需根据主键值去聚簇索引获取完整的记录。
•全表扫描查询
•ALL，依次扫描表中所有记录，将符合条件的数据加入结果集中
•使用索引查询
•Const：通过主键或唯一索引定位一条记录，主键或唯一索引的等值查询
•Ref：非唯一索引的等值查询；可能会匹配到多条在二级索引中连续的记录
•Range：利用索引进行范围查询；包括多个单点区间和连续的范围区间
•Index：全索引扫描，通过遍历索引得到结果集
• 特殊情况下可以在一个查询中使用多个二级索引，称为index merge
• 系统变量optimizer_switch中index_merge=on，或者index_merge=off并单独打开index_merge_union、index_merge_sort_union、index_merge_intersection中的一个或多个；
• index merge intersection：交集合并。当查询条件是多个二级索引等值匹配，主键可以是范围匹配的交集（and）条件时可以使用；此场景建议用联合索引优化；
• index merge union：并集合并。当查询条件是多个二级索引等值匹配，主键可以是范围匹配的并集（or）条件时可以使用；
• index merge sort union：排序并集合并。当查询条件是多个二级索引的范围匹配，且从某个二级索引中获取的记录较少时，可能会使用这种优化操作。先将二级索引记录的主键值排序，再按union方式合并；
• 连接查询本质上就是将各个表中的数据依次匹配后加入结果集中并返回，不带任何条件时结果集包含每一个表中所有记录与其他表所有记录的组合，称为笛卡尔积，连接分为内连接和外连接。
• MySQL之前默认的连接方式为嵌套循环连接（nested-loop join）从8.0.18开始支持对等值条件的连接查询使用hash join，从8.0.20开始使用hash join 替代了 Block Nested-Loop Join，并且hash join开始支持外连接。
• 对于nested-loop join嵌套循环连接，首先选择驱动表，通过驱动表的过滤条件得到结果集。再用结果集中每一条记录，分别到被驱动表中根据连接条件查找匹配的记录。整个执行过程类似一个嵌套的循环，如果驱动表返回结果集较多，被驱动表中的数据会多次被访问，性能较差。当被驱动表关联字段存在索引时可以使用eq_ref或ref的方式优化；
• 为了优化传统的嵌套循环连接，减少被驱动表的访问次数，MySQL提供了基于块的嵌套循环连接方式（Block Nested-Loop Join）进行优化。该方法会将驱动表过滤后的结果集以及查询需要的列存入join buffer这一内存结构中，再将joinbuffer中的数据批量和被驱动表的记录根据连接条件进行匹配，减少被驱动表的IO开销；
• MySQL8.0.18开始等值连接不能使用索引优化时，将采用hash join的方式处理连接查询。hash join的执行过程分为构建（Build）和探测（Probe）两个阶段；构建时MySQL选择空间较小的表，对关联字段hash计算后存入join buffer中。hash join时join buffer是增量分配的。 在探测阶段会对另一张表的关联字段hash计算后，与join buffer中的hash table进行匹配。匹配成功后返回结果。当join buffer无法容纳整个hash table时，会将两个表的数据分别溢出到块文件中，再对每一对块文件做hash匹配并返回结果。如果表数据较大 且 join buffer较少时，要注意open_files_limit的限制；
03 优化器
基于规则的优化
基于成本的优化
统计信息如何收集
在正式执行操作前，MySQL会依据一些规则将SQL进行优化或简化也叫查询重写，尽量将SQL改写成可以高效执行的形式。主要有以下几个方面
•移除不必要的括号
•常量传递。constant_propagation，x=5 and x>y -> x=5 and y<5
•等值传递。equality_propagation， x=y and y=z and x=42 -> x=42 and y=42 and z=42
•移除不用的条件。trivial_condition_removal ，remove conditions that are always false or always true
•表达式计算。x=5+1 -> x=6 ,注意索引列不能在函数中，例如abs(x)=5
•having和where子句的合并。如查询没有sum，max等聚合函数以及group by子句，优化器将having和where子句合并。
•常量表检测。当表中没有数据或者通过主键和唯一索引等值匹配时，优化器会首先执行constant table查询，并将条件替换成常量；
•外连接消除。当出现null-rejecting也就是被驱动表在where子句中包含不为null的条件时，外连接和内连接和相互转换；
子查询优化：某些符合条件的 in 子查询MySQL会优化成成semi-join的方式执行https://dev.mysql.com/doc/refman/8.0/en/semijoins.html
•MySQL在实际执行语句前优化器会在多种执行方案总选择一个成本最低的方案执行；所谓的执行成本是由IO成本和CPU成本两个方面组成的，总成本=IO成本+CPU成本。
•IO成本：将数据从磁盘加载到内存的开销，读取一个页面的成本默认1.0
•CPU成本：条件判断或排序等操作的开销，检测一条记录的成本默认0.2
•成本常数存放在mysql.server_cost和mysql.engine_cost中，可以修改cost_value值后执行flush optimizer_costs加载
单表查询基于成本的优化过程，首先估算全表扫描侧成本，然后找出所有可能使用的索引并估算各自的成本，最终选择出成本最低的执行计划。
•对于传统的嵌套循环连接，总成本=单次访问驱动表的成本+驱动表符合条件的行数*单次访问被驱动表的成本。所以优化连接查询的主要方向，一是尽可能降低驱动表访问成本以及减少驱动表结果集大小，选择合适的驱动表和连接顺序。另外还要尽可能减少被驱动表的访问成本，被驱动表上选择合适的索引。
•如果是多表连接查询虽然执行过程类似，但是由于表较多，连接顺序的可能性也非常多。MySQL如果要穷尽所有的连接顺序开销非常大，所以优化器做了以下几点优化：
•维护当前最小成本变量，提前结束成本评估
•系统变量optimizer_search_depth，决定参与评估的连接表个数。默认62
•系统变量optimizer_prune_level，开启启发式规则。默认开启。开启后优化器会对各表访问行数的预估跳过某些方案；
优化器要能够得到这些表和索引的行数、基数等统计信息才能估算出执行计划的成本，统计信息是MySQL抽样采集表和索引的页面统计计算出来的。我们可以通过show table status 和 show index查看到表的预估的行数，索引中唯一值的数量也就是基数等信息。
• MySQL提供了两种统计信息的存储方式，持久化存储在表中或者存储在内存中。是由系统变量innodb_stats_persistent控制，默认为ON存储在表中。由于数据库的统计信息是按表来采集和存储的，所以也可以在建表时通过stats_persistent=(1|0)指定。
• 持久化存储的统计信息是存放在mysql库的innodb_index_stats和innodb_table_stats表中的。
• innodb_table_stats存储表的统计信息，包括表记录数，聚簇索引和其他索引占用的page数。
• innodb_index_stats存储索引的统计信息，包括叶子节点page数，索引总page数，索引中不重复值的数量（组合索引分别统计），采样的页面数。
•统计信息可以自动或手动的方式采集，如果系统变量innodb_stats_auto_recalc设置为ON（默认值），则当表中数据变动超过10%时MySQL将异步更新该表的统计信息。否则只能通过analyze table的方式手动更新。由于数据库的统计信息是按表来采集和存储的，所以也可以在建表时通过stats_auto_recalc=(1|0)指定。
•注意：在MySQL中执行analyze table时，如果该表有长事务，将会阻塞其他事务对该表的操作；
•innodb_index_stats和innodb_table_stats表中的统计数据也可以手动修改，只是修改后需执行flush tabletable_name重新加载。
•我们知道采样页面越多统计信息越准确，但采集和更新统计信息的开销越高。MySQL提供了参数可以修改统计信息采样页面的数量。统计信息持久化存储时，采样页面数由系统变量innodb_stats_persistent_sample_pages控制，默认30。内存存储时，采样页面数由系统变量innodb_stats_transient_sample_pages控制，默认8。
04 查看执行计划及调优
Explain
optimizer trace
优化思路
•MySQL提供Explain工具查看某个SQL的执行计划，SQL并不会真正执行。在只读实例上无法查看写入SQL的执行计划；
•Explain [format=(triditional|tree|json)] SQL
•Id:操作表顺序。id从大到小执行，id相同从上到下执行
•select_type：查询类型。（smiple/primary/union/subquery/dependent subquery/materialized）
•Type：查询数据的具体访问方式(system/const/eq_ref/ref/range/index/all)
•possible_key&key：可能用到的和实际选择的索引
•key_len：使用索引的长度（组合索引的一部分）
•Ref：使用索引列做等值匹配时，匹配的列
•Row：预计扫描的行数或索引记录数
•Filtered：扫描的行数中符合其他条件的比例。下图sbtest7主键扫描19774条记录中，预估11.11%满足其他条件
•Extra：其他关键信息(using where/using filesort/using index/using temporary)
•查询类型select_type
•simple：不包括union或子查询
•Primary：包含union或子查询的最外层查询
•union：子查询中除了primary的查询
•Union result：使用临时表对union结果去重
•subquery：不能转换成semi-join的不相关子查询
•dependent subquery：不能转换成semi-join的相关子查询
•Derived：采用物化的方式执行的查询
•Materialized：子查询物化后与外层进行连接查询
•数据扫描方式TYPE
•Const：使用主键或唯一索引等值查询
•eq_ref：连接查询被驱动表通过主键或唯一索引等值匹配
•Ref：非唯一索引等值匹配
•range：范围查询
•Index：覆盖索引扫描
•ALL：全表扫描
•Extra
•Impossible WHERE：条件肯定为false时
•using filesort：使用排序算法排序
•using temptemporary：使用临时表
•using where：需要回表
•using index：覆盖索引扫描
•MySQL8.0.18开始支持Explain analyze，Explain analyze按format=tree格式输出，除了返回预估的成本和返回行数外，还会真正的执行这个SQL并返回实例的执行时间、返回行数和循环次数；因为会真正执行SQL，在8.0.20以后可以通过kill query或 CTRL-C终止运行。hash join用法只能由TREE格式显示，所以Explain analyze也可以显示查询是否使用hash join。
•MySQL提供optimizer trace工具剖析SQL执行过程中优化器选择执行计划的过程，如果执行计划和预期不一致，可以尝试用optimizer trace分析；
•set optimizer_trace=‘enabled=on' #打开optimizer_trace
•执行SQL
•select * from information_schema.optimizer_trace; #查看上一个查询的优化过程
•optimizer trace结果主要分为join_preparation准备，join_optimization优化和join_execution执行三部分；
重点关注优化部分
05tips
•查询时只返回需要的列
•条件字段有索引时注意强制类型转换
•单表查询多个条件并集时，尽量创建组合索引，不要交给优化器做index merge
•子查询避免出现dependent subquery，对于in子查询 如果有多个其他条件的要用and连接，子查询需是单一查询且不含group by、having以及聚合参数，这样可以优化成semi-join的方式执行
•统一字符集和比较规则，避免连接查询字符集转换的问题
•由于hash join对join buffer是增量分配的，可以适当增加join_buffer_size配置值，减少溢出文件的数量
•Innodb需要有自定义主键且最好使用整型自增主键，8.0.23版本开始可以添加不可见(invisible)列，减少对业务的影响
•字段类型最好是not null且设置默认值，如果索引列中存在大量null，由于系统变量innodb_stats_method默认值是nulls_equal，MySQL会认为所有的null都是相同的值，该索引区分度不高不倾向于使用。最好修改成nulls_unequal，这样null值都不相同，优化器更倾向于使用该索引；
•删除现有索引时，可先将该索引设置成invisible，观察一段时间无异常后再drop
•某些场景，可以考虑采用虚拟列并创建索引或使用函数索引的方式，优化SQL避免全表扫描
•where 子句in()中条件较多时，需适当调整eq_range_index_dive_limit参数值；该参数默认值是200，当单点区间超过200时MySQL优化器将放弃index dive而使用索引统计数据估算成本，可能会基于不太准确的统计数据选择不合理的执行计划；
•如果业务SQL原因导致慢查询消耗大量系统资源时，可以先使用query_rewrite 插件改写SQL恢复业务
浙公网安备 33010602011771号