精品网站设计,网站建设可行性研究报告,接收新网站如何做诊断,wordpress为用户添加积分分析慢 SQL
如何定位慢 SQL 呢#xff1f;
可以通过 slow log 来查看慢SQL#xff0c;默认的情况下#xff0c;MySQL 数据库是不开启慢查询日志#xff08;slow query log#xff09;。所以我们需要手动把它打开。
查看下慢查询日志配置#xff0c;我们可以使用 show …分析慢 SQL
如何定位慢 SQL 呢
可以通过 slow log 来查看慢SQL默认的情况下MySQL 数据库是不开启慢查询日志slow query log。所以我们需要手动把它打开。
查看下慢查询日志配置我们可以使用 show variables like slow_query_log% 命令如下 slow query log表示慢查询开启的状态。slow_query_log_file表示慢查询日志存放的位置。
还可以使用 show variables like long_query_time 命令查看超过多少时间才记录到慢查询日志如下 long_query_time表示查询超过多少秒才记录到慢查询日志。
可以通过慢查询日志定位那些执行效率较低的 SQL 语句重点关注分析。 explain 查看分析 SQL 的执行计划
当定位出查询效率低的 SQL 后可以使用 explain 查看 SQL 的执行计划。
当 explain 与 SQL 一起使用时MySQL 将显示来自优化器的有关语句执行计划的信息。即 MySQL 解释了它将如何处理该语句包括有关如何连接表以及以何种顺序连接表等信息。
一条简单SQL使用了explain的效果如下 一般来说我们需要重点关注id、type、key、rows、filtered、extra。 profile 分析执行耗时
explain 只是看到 SQL 的预估执行计划如果要了解SQL真正的执行线程状态及消耗的时间需要使用 profiling。开启 profiling 参数后后续执行的SQL语句都会记录其资源开销包括IO、上下文切换、CPU、内存等等我们可以根据这些开销进一步分析当前慢 SQL 的瓶颈再进一步进行优化。
profiling 默认是关闭的我们可以使用 show variables like %profil% 查看是否开启如下 可以使用 set profilingON 开启。开启后可以运行几条 SQL然后使用 show profiles 查看一下 show profiles会显示最近发给服务器的多条语句条数由变量profiling_history_size定义默认是 15。如果我们需要看单独某条 SQL 的分析可以show profile查看最近一条 SQL 的分析。也可以使用show profile for query id其中 id 就是 show profiles 中的 QUERY_ID查看具体一条的 SQL 语句分析。 除了查看 profile 还可以查看 cpu 和 io如上图。 Optimizer Trace 分析详情
profile 只能查看到 SQL 的执行耗时但是无法看到 SQL 真正执行的过程信息即不知道 MySQL 优化器是如何选择执行计划的。这时候还可以使用 Optimizer Trace它可以跟踪执行语句的解析优化执行的全过程。
可以使用 set optimizer_traceenabledon 打开开关接着执行要跟踪的 SQL最后执行 select * from information_schema.optimizer_trace 跟踪如下 大家可以查看分析其执行树会包括三个阶段
join_preparation准备阶段join_optimization分析阶段join_execution执行阶段
由于 trace 中的内容太长就不贴出来了。 采取措施
多数慢 SQL 都跟索引有关比如不加索引、索引不生效、不合理等这时候我们可以优化索引。优化 SQL 语句比如一些 in 元素过多问题分批深分页问题基于上一次数据过滤等进行时间分段查询。SQl 若不能继续优化时可以改用 ES 的方式或者数仓。如果单表数据量过大导致慢查询则可以考虑分库分表。如果数据库在刷脏页导致慢查询考虑是否可以优化一些参数。如果存量数据量太大考虑是否可以让部分数据归档。
