MySQL查询优化

背景

数据库性能优化

写在前面
原文链接为https://www.modb.pro/doc/21032 （杨明翰）

link

怎么发现和定位慢SQL

• 从当前查询中获取问题SQL
• 从慢日志中获取问题SQL (常用方式)
• sys schema相关视图中获取问题SQL

• 从当前查询中获取问题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；

• 从慢日志中获取问题SQL （工具mysqldumpslow和pt-query-digest）

show variables like '%slow%';
+---------------------------+----------------------------------------------------+
| Variable_name             | Value                                              |
+---------------------------+----------------------------------------------------+
| log_slow_admin_statements | ON                                                 |
| log_slow_slave_statements | ON                                                 |
| slow_launch_time          | 2                                                  |
| slow_query_log            | ON                                                 |
| slow_query_log_file       | /usr/local/mysql5.7.32/data/mysqlupdate01-slow.log |
+---------------------------+----------------------------------------------------+
show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.200000 |
+-----------------+----------+

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限制(5.7，8.0无)；
• log_throttle_queries_not_using_indexes每分钟有多少不使用索引的查询可被记录在慢查询日志中；

show variables like '%using%';
+----------------------------------------+-------+
| Variable_name                          | Value |
+----------------------------------------+-------+
| log_queries_not_using_indexes          | ON    |
| log_throttle_queries_not_using_indexes | 60    |
+----------------------------------------+-------+


8.0
log_slow_extra=off
log_slow_extra=on(8.0.14+)

使用工具分析慢查询日志• mysqldumpslow，mysql自带慢查询日志分析工具；
• 常用参数：
• -r 倒序排列
• -n 只显示前n个记录
• -a 不对数字和字符串进行抽象
• -g 字符串过滤

• -s  是表示按照何种方式排序
• al平均锁等待时间排序
• at平均查询时间排序
• ar平均返回行数排序
• c 出现总次数排序
• l等待锁的时间排序
• r 返回总行数排序
• t 累计查询耗时排序

-t, 是top n的意思，即为返回前面多少条的数据；
-g, 后边可以写一个正则匹配模式，大小写不敏感的；

使用mysqldumpslow分别对慢查询日志按执行次数、锁定时间、返回行数和执行时间分析排序后输出
另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现刷屏的情况。

得到返回记录集最多的10个SQL。
mysqldumpslow -s r -t 10 mysqlupdate01-slow.log | more

得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 mysqlupdate01-slow.log | more

获取访问次数最多的5个SQL语句
mysqldumpslow -s c -t 5 mysqlupdate01-slow.log | more

按照时间排的top 5个SQL语句
mysqldumpslow -s t -t 5 mysqlupdate01-slow.log | more

按照时间排序且含有'like'的top 5个SQL语句
mysqldumpslow -s t -t 3 -g "like"  mysqlupdate01-slow.log | more

还有一种方法是mysqldumpslow mysqlupdate01-slow.log | grep 过滤条件|more  

pt-query-digest

pt-query-digest• Percona-toolkit中的工具可以从普通日志、慢查询日志、二进制日志以及show processlist和tcpdump中对SQL进行分析；
• 默认type分析慢查询日志： pt-query-digest  /data/mysql/data/node1-slow.log
• 输出分三大部分：
• 整体概要：对当前实例各项指标进行统计和初步的分析；包括总的查询次数、SQL数量、QPS以及并发；对执行时间、锁、检查返回行数等指标进行统计；
(之前文章有介绍)

还有一种方式Profile

在mysql5.7之后，profile信息将逐渐被废弃，mysql推荐使用performance schema
Profile：对重要或较慢查询进一步分析
 • Rank:排名• Response time “语句”的响应时间以及整体占比情况。
 • Calls 该“语句”的执行次数。• R/Call 每次执行的平均响应时间。
 • V/M 响应时间的方差均值比（VMR） （值越大这类SQL响应时间越趋于不同）。

set profiling=1;                  //打开分析
run sql1 //运行查询
show profiles;                    //查看sql1的语句分析
show profile for query 1;        //查看sql1的具体分析
show variables like '%profili%';
SHOW profile CPU,BLOCK IO io FOR query 1; 

sys schema相关视图中获取问题SQL（推荐这种）

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后）
 

8.0.的查询

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这一内存结构中，
再将join buffer中的数据批量和被驱动表的记录根据连接条件进行匹配，减少被驱动表的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的限制

优化器

基于规则的优化
基于成本的优化
统计信息如何收集

在正式执行操作前，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.htm

子查询优化:

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 table table_name重新加载。
• 我们知道采样页面越多统计信息越准确，但采集和更新统计信息的开销越高。
MySQL提供了参数可以修改统计信息采样页面的数量。统计信息持久化存储时，
采样页面数由系统变量innodb_stats_persistent_sample_pages控制，默认30。
内存存储时，采样页面数由系统变量innodb_stats_transient_sample_pages控制，默认8。

查看执行计划及调优

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执行三部分；重点关注优化部分

sql 规范

查询时只返回需要的列
• 条件字段有索引时注意强制类型转换
• 单表查询多个条件并集时，尽量创建组合索引，不要交给优化器做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

where 子句in()中条件较多时，需适当调整eq_range_index_dive_limit参数值；该参数默认值是200，
当单点区间超过200时MySQL优化器将放弃index dive而使用索引统计数据估算成本，
可能会基于不太准确的统计数据选择不合理的执行计划；

如果业务SQL原因导致慢查询消耗大量系统资源时，可以先使用query_rewrite 插件改写SQL恢复业务

原文链接

推荐阅读(因为包含例子)https://www.modb.pro/doc/21032
link

本文说明，主要技术内容来自互联网技术大佬的分享，还有一些自我的加工(仅仅起到注释说明的作用)。如有相关疑问，请留言，将确认之后，执行侵权必删​