Mysql--基础知识点--86--慢查询

1 慢查询监控与分析工具启用慢查询日志设置long_query_time并分析使用Performance Schema监控SQL执行细节第三方工具如Percona Toolkit、pt-query-digest等可视化监控使用PrometheusGrafana等方案2 慢查询解决方案考虑方向当面对MySQL慢查询问题时可以从以下几个方面进行系统性的分析和优化2.1 查询优化复杂查询对于非常复杂的查询尤其是包含多个子查询和联合操作的查询考虑将其拆分为多个简单的查询或者使用临时表。这样可以减少 MySQL 的查询优化器的压力并可能提高查询效率。嵌套查询嵌套子查询可以考虑改成关联查询。Mysql–基础知识点–94.1–嵌套子查询转关联查询事务避免使用长事务Mysql–基础知识点–95–为什么避免使用长事务select避免select *select 列 时使用覆盖索引的方式指的是非主键索引select * 时where的是非主键索引时会回表二次查找。count使用count * 而不是count 具体的列Mysql–基础知识点–96–count * VS count 列join在处理多表连接时选择合适的连接方式可以显著影响查询性能。尽量使用 INNER JOIN 而不是 OUTER JOIN除非必须返回没有匹配数据的记录。确定ON或者USING子句中是否有索引。limit 分页当偏移量大时使用where 索引列名偏移量group by、order by使用索引列如果不需要ORDER BY进行GROUP BY时加ORDER BY NULLMySQL不会再进行文件排序。关联查询时确保GROUP BY和ORDER BY只有一个表中的列这样MySQL才有可能使用索引对于联合索引也需要符合最左匹配原则union使用union all 而不是unionMysql–基础知识点–97–UNION ALL VS UNION联合索引需要符合最左匹配原则: https://www.cnblogs.com/l1pe1/p/16965628.htmlwhere基本时索引失效的一些场景Mysql索引失效的场景 中除了10 group by外2.2 索引优化创建索引注意些什么MySQL进阶之六索引的使用与设计原则 中的6.52.3 数据库配置优化缓冲池大小调整innodb_buffer_pool_size排序缓冲区优化sort_buffer_size等参数连接数配置适当调整max_connections和thread_cache_size日志配置平衡慢查询日志开销与监控需求2.4 架构层面优化读写分离将读请求分流到从库分库分表对大数据量表进行水平或垂直拆分缓存层引入使用Redis等缓存热点数据异步处理将非实时需求操作异步化2.5 硬件与系统层面存储优化使用SSD替代HDD内存扩容增加服务器内存减少磁盘I/OCPU资源确保有足够的CPU处理能力系统参数调整文件描述符限制、网络参数等2.6 应用层优化减少交互次数批量操作替代单条操作合理使用事务避免长事务和过大事务连接池管理合理配置和使用数据库连接池ORM框架优化检查生成的SQL语句效率。一般不考虑用的基本都是第三方包或库。3 具体排查过程2 慢查询解决方案考虑方向中的方案会在以下的排查过程中体现1 判断是不是网络、接口问题。接口问题应该从该文档2.6应用层优化考虑2 若不是网络或接口问题查看服务器性能cpu、内存、硬盘。若mysql的cpu很高则表示读写频率高若网站的访问量又不高则可能是(1)mysql参数问题(2)linux系统参数(3)mysql的sql语句查询慢的问题。2.1 mysql参数的问题采取措施(i) 调整缓存池大小 mysql8.0可以不用考虑8.0已经去掉了查询缓存 (ii)调整连接数大小。2.2 linux系统参数采取措施(i) 调整文件句柄数 (ii) 调整tcp连接TIMEWAIT状态相关参数MySQL–基础知识点–55–MySQL优化中系统内核优化部分2.3 mysql的sql语句查询慢问题采取措施(i) 使用show processlist;查询当前活动连接可以显现出谁在执行sql以及正在执行的sql执行了多长时间。(ii) 使用慢查询日志查看慢查询sql [1 查询慢查询日志三个相关参数show variables like ‘query’; 2 三个慢查询日志参数slow_query_time 慢查询时间阈值超过这个时间的查询的查询语句会被记录到慢查询日志里slow_query_log 是否打开慢查询值为off/onslow_query_log_file 慢查询日志的位置3 启用慢查询 set global slow_query_logON 4 摸拟一条慢查询 select * from edu_user where id1 and sleep(60);] 4 mysqldumpslow 分析慢查询日志访问最慢的sql、查询频次最高的sql等。(iii) 使用profiles可以查看是具体哪一步耗时一条sql 语句执行有多个步骤见MySQL–基础知识点–53–profile分析SQL语句性能消耗中的starting–cleaning up主要关注其中optimizing、executing、Sending data这三个阶段。optimizing 慢检查执行计划、更新统计信息、优化索引executing 慢优化SQL写法、添加缺失索引、避免全表扫描Sending data 慢限制返回字段、分页优化、网络调优(iv)explain分析慢查询sql执行任务通过查看 EXPLAIN 的输出你可以了解查询的性能瓶颈并据此进行优化。并且可以根据trace【MYSQL 索引分析工具——trace】的结果进行更详细的分析。例如如果你发现查询没有使用索引或者查询了过多的行那么你可能需要重新考虑索引策略或者修改查询以使其更高效。(v)根据(iv)的分析结果进行优化[1]索引优化添加合适的索引[2]查询语句优化3 若以上都没问题考虑数据量大的问题。可以从架构层考虑(i) 读写分离(ii) 应用级别的缓存(iii)分库分表分区。补充mysqldumpslow是MySQL提供的一个命令行工具主要用于分析MySQL的慢查询日志slow query log。以下是关于mysqldumpslow的详细介绍和使用方法一、基本用途分析慢查询日志从慢查询日志中提取信息并以易读的方式展示最耗时的查询以便进一步分析和优化。提高性能帮助开发人员和数据库管理员识别和优化数据库中的慢查询提高系统的性能和响应时间。二、使用方法查看慢查询日志在命令行中输入mysqldumpslow /path/to/slowquery.log以查看整个慢查询日志文件中的所有慢查询。排序选项-s参数用于指定排序方式可以按查询时间、锁等待时间、返回行数等进行排序。-s t按时间顺序从最新到最旧排序。-s at按查询时间长短排序显示查询时间最长的慢查询。-s al按锁等待时间长短排序。-s ar按返回行数排序。指定显示的记录数-t NUM参数用于指定显示多少条慢查询记录。默认情况下mysqldumpslow将显示前10条慢查询记录。过滤特定的查询使用-g PATTERN参数进行正则匹配筛选出与特定模式相关的慢查询。其他选项-v输出debug信息。-d指定输出排序规则包括多种排序方式的组合。-r输出结果反序排序默认是降序desc排序。-a不将数字抽象为N、字符抽象为S。-n NUM将超过N个数字的数值字符抽象显示。-l总时间包含锁定时间。三、注意事项在使用mysqldumpslow之前需要确保MySQL的慢查询日志功能已经开启并且慢查询日志文件存在且可读取。mysqldumpslow工具通常与MySQL数据库服务器一起安装无需单独安装。虽然mysqldumpslow提供了基本的慢查询分析功能但对于更复杂的性能问题可能需要结合其他工具和方法进行综合分析和优化。通过合理地使用mysqldumpslow工具可以有效地识别和优化MySQL数据库中的慢查询提高系统的整体性能和响应速度。EXPLAIN 是 MySQL 中的一个关键字用于分析 SQL 查询的执行计划。当你对查询性能有疑问或者想知道 MySQL 是如何执行某个查询时EXPLAIN 可以提供非常有价值的信息。使用 EXPLAIN 的基本语法是EXPLAINSELECT...FROM...WHERE...;eg:EXPLAINSELECT*FROMusersWHEREusernamejohn_doe;当你执行一个带有 EXPLAIN 的查询时MySQL 不会实际执行这个查询而是返回一个关于查询执行划的表格。这个表格包含了多个列每个列都提供了关于查询执行的不同方面的信息。想知道 MySQL 是如何执行某个查询时EXPLAIN 可以提供非常有价值的信息。以下是一些常见的 EXPLAIN 输出列及其描述想知道 MySQL 是如何执行某个查询时EXPLAIN 可以提供非常有价值的信息。id查询标识符每个 SELECT和某些子查询都会有一个唯一的 ID。select_type查询的类型例如 SIMPLE、SUBQUERY、UNION 等。table查询涉及的表。type连接类型例如 ALL、index、range、ref、eq_ref、const 等它告诉你 MySQL 如何连接表。ref 和 const 通常是好的而 ALL 则可能表示性能问题。possible_keys查询可能使用的索引。key实际使用的索引如果 MySQL 决定使用索引的话。key_len使用的索引的长度。ref哪些列或常量被用作索引查找的引用。rowsMySQL 估计需要检查的行数注意这只是一个估计值。Extra包含 MySQL 解决查询的附加信息例如使用了哪些文件排序、使用了临时表等。想知道 MySQL 是如何执行某个查询时EXPLAIN 可以提供非常有价值的信息。通过查看 EXPLAIN 的输出你可以了解查询的性能瓶颈并据此进行优化。例如如果你发现查询没有使用索引或者查询了过多的行那么你可能需要重新考虑索引策略或者修改查询以使其更高效。