精华内容
下载资源
问答
  • 性能分析来入手分析,定位导致 SQL 执行慢的原因。前面已经更新了总结核心的主要三点如何使用慢查询日志查找执行慢SQL 语句?如何使用 EXPLAIN 查看 SQL 执行计划?如何使用 SHOW PROFILING 分析 SQL 执行步骤中...

    492d61f857657c6a0c624f49394b4406.png

    但实际上 SQL 执行起来可能还是很慢,那么到底从哪里定位 SQL 查询慢的问题呢?是索引设计的问题?服务器参数配置的问题?还是需要增加缓存的问题呢?性能分析来入手分析,定位导致 SQL 执行慢的原因。

    前面已经更新了总结核心的主要三点

    • 如何使用慢查询日志查找执行慢的 SQL 语句?
    • 如何使用 EXPLAIN 查看 SQL 执行计划?
    • 如何使用 SHOW PROFILING 分析 SQL 执行步骤中的每一步的执行时间?

    那讲了这这么多数据库服务器的优化分析的步骤是怎样的?中间有哪些需要注意的地方?本篇主要是针对这一个话题的总结和概括。

    数据库服务器的优化步骤

    当我们遇到数据库调优问题的时候,该如何思考呢?我把思考的流程整理成了下面这张图。

    整个流程划分成了观察(Show status)和行动(Action)两个部分。字母 S 的部分代表观察(会使用相应的分析工具),字母 A 代表的部分是行动(对应分析可以采取的行动)

    067bdd96a48fafc60ecb9588f2582e0a.png

    通过观察了解数据库整体的运行状态,通过性能分析工具可以让我们了解执行慢的 SQL 都有哪些,查看具体的 SQL 执行计划,甚至是 SQL 执行中的每一步的成本代价,这样才能定位问题所在,找到了问题,再采取相应的行动

    详细解释一下这张图

    首先在 S1 部分,我们需要观察服务器的状态是否存在周期性的波动。如果存在周期性波动,有可能是周期性节点的原因,比如双十一、促销活动等。这样的话,我们可以通过 A1 这一步骤解决,也就是加缓存,或者更改缓存失效策略

    如果缓存策略没有解决,或者不是周期性波动的原因,我们就需要进一步分析查询延迟和卡顿的原因。接下来进入 S2 这一步,我们需要开启慢查询。慢查询可以帮我们定位执行慢的 SQL 语句。我们可以通过设置long_query_time参数定义“慢”的阈值,如果 SQL 执行时间超过了long_query_time,则会认为是慢查询。当收集上来这些慢查询之后,我们就可以通过分析工具对慢查询日志进行分析

    在 S3 这一步骤中,我们就知道了执行慢的 SQL 语句,这样就可以针对性地用 EXPLAIN 查看对应 SQL 语句的执行计划,或者使用 SHOW PROFILE 查看 SQL 中每一个步骤的时间成本。这样我们就可以了解 SQL 查询慢是因为执行时间长,还是等待时间长

    如果是 SQL 等待时间长,我们进入 A2 步骤。在这一步骤中,我们可以调优服务器的参数,比如适当增加数据库缓冲池等。如果是 SQL 执行时间长,就进入 A3 步骤,这一步中我们需要考虑是索引设计的问题?还是查询关联的数据表过多?还是因为数据表的字段设计问题导致了这一现象。然后在这些维度上进行对应的调整

    如果 A2 和 A3 都不能解决问题,我们需要考虑数据库自身的 SQL 查询性能是否已经达到了瓶颈,如果确认没有达到性能瓶颈,就需要重新检查,重复以上的步骤。如果已经达到了性能瓶颈,进入 A4 阶段,需要考虑增加服务器,采用读写分离的架构,或者考虑对数据库分库分表,比如垂直分库、垂直分表和水平分表等

    以上就是数据库调优的流程思路。当我们发现执行 SQL 时存在不规则延迟或卡顿的时候,就可以采用分析工具帮我们定位有问题的 SQL,这三种分析工具你可以理解是 SQL 调优的三个步骤:慢查询、EXPLAIN 和 SHOW PROFILE

    总结

    结合前面三篇的分步解读分析

    • 如何使用慢查询日志查找执行慢的 SQL 语句?
    • 如何使用 EXPLAIN 查看 SQL 执行计划?
    • 如何使用 SHOW PROFILING 分析 SQL 执行步骤中的每一步的执行时间?

    从步骤上看,我们需要先进行观察和分析,分析工具的使用在日常工作中还是很重要的。今天只介绍了常用的三种分析工具,实际上可以使用的分析工具还有很多。

    这里总结一下文章里提到的三种分析工具。我们可以通过慢查询日志定位执行慢的 SQL,然后通过 EXPLAIN 分析该 SQL 语句是否使用到了索引,以及具体的数据表访问方式是怎样的。我们也可以使用 SHOW PROFILE 进一步了解 SQL 每一步的执行时间,包括 I/O 和 CPU 等资源的使用情况

    6dd8fbe9a8c6a67310951ff9f724facb.png

    【公众号:码农架构 】专注于系统架构、高可用、高性能、高并发类技术分享

    展开全文
  • 我们都知道查询优化器,知道在查询优化器中会经历逻辑查询优化和物理查询优化。...性能分析来入手分析,定位导致 SQL 执行慢的原因。 前面已经更新了总结核心的主要三点 如何使用慢查询日志查找执行慢SQL 语..

    我们都知道查询优化器,知道在查询优化器中会经历逻辑查询优化和物理查询优化。需要注意的是,查询优化器只能在已经确定的情况下(SQL 语句、索引设计、缓冲池大小、查询优化器参数等已知的情况)决定最优的查询执行计划

     

    但实际上 SQL 执行起来可能还是很慢,那么到底从哪里定位 SQL 查询慢的问题呢?是索引设计的问题?服务器参数配置的问题?还是需要增加缓存的问题呢?性能分析来入手分析,定位导致 SQL 执行慢的原因。

     

    前面已经更新了总结核心的主要三点

    1. 如何使用慢查询日志查找执行慢的 SQL 语句?

    2. 如何使用 EXPLAIN 查看 SQL 执行计划?

    3. 如何使用 SHOW PROFILING 分析 SQL 执行步骤中的每一步的执行时间?

    那讲了这这么多数据库服务器的优化分析的步骤是怎样的?中间有哪些需要注意的地方?本篇主要是针对这一个话题的总结和概括。

    数据库服务器的优化步骤

    当我们遇到数据库调优问题的时候,该如何思考呢?我把思考的流程整理成了下面这张图。

    整个流程划分成了观察(Show status)和行动(Action)两个部分。字母 S 的部分代表观察(会使用相应的分析工具),字母 A 代表的部分是行动(对应分析可以采取的行动)

    通过观察了解数据库整体的运行状态,通过性能分析工具可以让我们了解执行慢的 SQL 都有哪些,查看具体的 SQL 执行计划,甚至是 SQL 执行中的每一步的成本代价,这样才能定位问题所在,找到了问题,再采取相应的行动

     

    详细解释一下这张图

    首先在 S1 部分,我们需要观察服务器的状态是否存在周期性的波动。如果存在周期性波动,有可能是周期性节点的原因,比如双十一、促销活动等。这样的话,我们可以通过 A1 这一步骤解决,也就是加缓存,或者更改缓存失效策略

     

    如果缓存策略没有解决,或者不是周期性波动的原因,我们就需要进一步分析查询延迟和卡顿的原因。接下来进入 S2 这一步,我们需要开启慢查询。慢查询可以帮我们定位执行慢的 SQL 语句。我们可以通过设置long_query_time参数定义“慢”的阈值,如果 SQL 执行时间超过了long_query_time,则会认为是慢查询。当收集上来这些慢查询之后,我们就可以通过分析工具对慢查询日志进行分析

     

    在 S3 这一步骤中,我们就知道了执行慢的 SQL 语句,这样就可以针对性地用 EXPLAIN 查看对应 SQL 语句的执行计划,或者使用 SHOW PROFILE 查看 SQL 中每一个步骤的时间成本。这样我们就可以了解 SQL 查询慢是因为执行时间长,还是等待时间长

     

    如果是 SQL 等待时间长,我们进入 A2 步骤。在这一步骤中,我们可以调优服务器的参数,比如适当增加数据库缓冲池等。如果是 SQL 执行时间长,就进入 A3 步骤,这一步中我们需要考虑是索引设计的问题?还是查询关联的数据表过多?还是因为数据表的字段设计问题导致了这一现象。然后在这些维度上进行对应的调整

     

    如果 A2 和 A3 都不能解决问题,我们需要考虑数据库自身的 SQL 查询性能是否已经达到了瓶颈,如果确认没有达到性能瓶颈,就需要重新检查,重复以上的步骤。如果已经达到了性能瓶颈,进入 A4 阶段,需要考虑增加服务器,采用读写分离的架构,或者考虑对数据库分库分表,比如垂直分库、垂直分表和水平分表等

     

    以上就是数据库调优的流程思路。当我们发现执行 SQL 时存在不规则延迟或卡顿的时候,就可以采用分析工具帮我们定位有问题的 SQL,这三种分析工具你可以理解是 SQL 调优的三个步骤:慢查询、EXPLAIN 和 SHOW PROFILE

    总结

    结合前面三篇的分步解读分析

    从步骤上看,我们需要先进行观察和分析,分析工具的使用在日常工作中还是很重要的。今天只介绍了常用的三种分析工具,实际上可以使用的分析工具还有很多。

     

    这里总结一下文章里提到的三种分析工具。我们可以通过慢查询日志定位执行慢的 SQL,然后通过 EXPLAIN 分析该 SQL 语句是否使用到了索引,以及具体的数据表访问方式是怎样的。我们也可以使用 SHOW PROFILE 进一步了解 SQL 每一步的执行时间,包括 I/O 和 CPU 等资源的使用情况

    展开全文
  • SQL优化对于一枚程序员来说是至关重要的,并且大部分面试中,都会问道有关sql优化的一 些问题,这里将带着大家学会如何分析sql执行效率,首先要想优化一条sql语句,前提是我们要能够定位到查询的sql语句,并对其...

    前言

    SQL优化对于一枚程序员来说是至关重要的,并且大部分面试中,都会问道有关sql优化的一 些问题,这里将带着大家学会如何分析sql执行效率,首先要想优化一条sql语句,前提是我们要能够定位到查询慢的sql语句,并对其进行分析,找到慢查询的圆心,然后进行优化。

    定位慢 SQL

    大家在工作中测试的时候偶尔会碰到查询结果,超过一定时间才返回,这时我们就应该考虑是不是慢查询导致的,接下来让我们看看如果定位慢sql语句

    一 通过慢查询日志

    如果需要定位到慢查询,一般的方法是通过慢查询日志来查询的,MySQL 的慢查询日志它会记录在 MySQL 中响应时间超过参数 long_query_time(单位秒,默认值 10)设置的值并且扫描记录数不小于min_examined_row_limit(默认值位0)的语句,所以它会帮我们找出查询慢的sql语句。

    默认情况下,慢查询日志中并不会记录管理语句,可通过设置参数 log_slow_admin_statements = on 让管理语句中的慢查询也会记录到慢查询的日志中。

    默认情况下,也不会记录查询时间不超过 long_query_time 但是不使用索引的语句,可通过配置 log_queries_not_using_indexes = on 让不使用索引的 SQL 都被记录到慢查询日志中(即使查询时间没超过 long_query_time 配置的值)。

    通常我们开启慢查询日志一般分为4给步骤:
    第一步 开启慢查询日志
    首先开启慢查询日志,由参数 slow_query_log 决定是否开启,在 MySQL 的命令行下输入下面的命令:

    mysql> set global slow_query_log = on;
    
    Query OK, 0 rows affected (0.00 sec)
    

    第二步 设置慢查询阀值

    mysql> set global long_query_time = 1;
    
    Query OK, 0 rows affected (0.00 sec)
    

    MySQL 中 long_query_time 的值如何确定呢?

    线上业务一般建议把 long_query_time 设置为 1 秒,如果某个业务的 MySQL 要求比较高的 QPS,可设置慢查询为 0.1
    秒。发现慢查询及时优化或者提醒开发改写。

    一般测试环境建议 long_query_time 设置的阀值比生产环境的小,比如生产环境是 1 秒,则测试环境建议配置成 0.5
    秒。便于在测试环境及时发现一些效率低的 SQL。

    甚至某些重要业务测试环境 long_query_time 可以设置为
    0,以便记录所有语句。并留意慢查询日志的输出,上线前的功能测试完成后,分析慢查询日志每类语句的输出,重点关注
    Rows_examined(语句执行期间从存储引擎读取的行数),提前优化。

    第三步 确定慢查询日志文件路径
    慢查询日志的路径默认是 MySQL 的数据目录

    mysql> show global variables like "datadir";
    
    +---------------+------------------------+
    | Variable_name | Value                  |
    +---------------+------------------------+
    | datadir       | /data/mysql/data/3306/ |
    +---------------+------------------------+
    
    1 row in set (0.00 sec)
    

    第四步 确定慢查询日志的文件名

    mysql> show global variables like "slow_query_log_file";
    
    +---------------------+----------------+
    | Variable_name       | Value          |
    +---------------------+----------------+
    | slow_query_log_file | mysql-slow.log |
    +---------------------+----------------+
    
    1 row in set (0.00 sec)
    

    根据上面的查询结果,可以直接查看 /data/mysql/data/3306/mysql-slow.log 文件获取已经执行完的慢查询

    [root@mysqltest ~]# tail -n5 /data/mysql/data/3306/mysql-slow.log
    
    Time: 2019-05-21T09:15:06.255554+08:00
    
    User@Host: root[root] @ localhost []  Id: 8591152
    
    Query_time: 10.000260  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
    
    SET timestamp=1558401306;
    select sleep(10);
    

    这里对上方的执行结果详细描述一下:

    tail -n5:只查看慢查询文件的最后 5
    Time:慢查询发生的时间
    User@Host:客户端用户和 IP
    Query_time:查询时间
    Lock_time:等待表锁的时间
    Rows_sent:语句返回的行数
    Rows_examined:语句执行期间从存储引擎读取的行数

    小伙伴们可以使用 pt-query-digest 或者 mysqldumpslow 等工具对慢查询日志进行分析

    二 通过 show processlist
    有时慢查询正在执行,已经导致数据库负载偏高了,而由于慢查询还没执行完,因此慢查询日志还看不到任何语句。此时可以使用 show processlist 命令判断正在执行的慢查询。show processlist 显示哪些线程正在运行。如果有 PROCESS 权限,则可以看到所有线程。否则,只能看到当前会话的线程。

    如果不使用 FULL 关键字,在 info 字段中只显示每个语句的前 100 个字符,如果想看语句的全部内容可以使用 full 修饰(show full processlist)。

    mysql> show processlist\G`
    
    `......`
    
    `*************************** 10. row ***************************`
    
         `Id: 7651833`
    
       `User: one`
    
       `Host: 192.168.1.251:52154`
    
         `db: ops`
    
    `Command: Query`
    
       `Time: 3`
    
      `State: User sleep`
    
       `Info: select sleep(10)`
    
    `......`
    
    `10 rows in set (0.00 sec)`
    

    这里对上面结果解释一下:

    Time:表示执行时间
    Info:表示 SQL 语句
    我们这里可以通过它的执行时间(Time)来判断是否是慢 SQL。

    上面讲了如果定位到慢查询,接下来,就让我们看看怎么分析定位到的慢查询了

    使用 explain 分析慢查询

    我们可以通过 explain、show profile 和 trace 等诊断工具来分析慢查询。

    Explain 可以获取 MySQL 中 SQL
    语句的执行计划,比如语句是否使用了关联查询、是否使用了索引、扫描行数等。可以帮我们选择更好地索引和写出更优的 SQL.使用方法:在查询语句前面加上 explain 运行就可以了。

    为了便于理解,先创建两张测试表,建表及数据写入语句如下:

    CREATE DATABASE muke;           /* 创建测试使用的database,名为muke */
    use muke;                       /* 使用muke这个database */
    drop table if exists t1;        /* 如果表t1存在则删除表t1 */
    
    CREATE TABLE `t1` (             /* 创建表t1 */
      `id` int(11) NOT NULL auto_increment,
      `a` int(11) DEFAULT NULL,
      `b` int(11) DEFAULT NULL,
      `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
      `update_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
      PRIMARY KEY (`id`),
      KEY `idx_a` (`a`),
      KEY `idx_b` (`b`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;	
    
    drop procedure if exists insert_t1; /* 如果存在存储过程insert_t1,则删除 */
    delimiter ;;
    create procedure insert_t1()        /* 创建存储过程insert_t1 */
    begin
      declare i int;                    /* 声明变量i */
      set i=1;                          /* 设置i的初始值为1 */
      while(i<=1000)do                  /* 对满足i<=1000的值进行while循环 */
        insert into t1(a,b) values(i, i); /* 写入表t1中a、b两个字段,值都为i当前的值 */
        set i=i+1;                      /* 将i加1 */
      end while;
    end;;
    delimiter ;                 /* 创建批量写入1000条数据到表t1的存储过程insert_t1 */
    call insert_t1();           /* 运行存储过程insert_t1 */
    
    drop table if exists t2;    /* 如果表t2存在则删除表t2 */
    create table t2 like t1;    /* 创建表t2,表结构与t1一致 */
    insert into t2 select * from t1;   /* 将表t1的数据导入到t2 */
    

    下面尝试使用 explain 分析一条 SQL

    mysql> explain select * from t1 where b=100;
    

    在这里插入图片描述
    列名 解释
    id 查询编号
    select_type 查询类型:显示本行是简单还是复杂查询
    table 涉及到的表
    partitions 匹配的分区:查询将匹配记录所在的分区。仅当使用 partition 关键字时才显示该列。对 于非分区表,该值为 NULL。
    type 本次查询的表连接类型
    possible_keys 可能选择的索引
    key 实际选择的索引
    key_len 被选择的索引长度:一般用于判断联合索引有多少列被选择了
    ref 与索引比较的列
    rows 预计需要扫描的行数,对 InnoDB 来说,这个值是估值,并不一定准确
    filtered 按条件筛选的行的百分比
    Extra 附加信息
    其中 explain 各列都有各种不同的值,这里介绍几个比较重要列常包含的值:包含 select_typ、type 和 Extra。

    select_type

    SIMPLE 简单查询 (不使用关联查询或子查询)
    PRIMARY 如果包含关联查询或者子查询,则最外层的查询部分标记为 primary
    UNION 联合查询中第二个及后面的查询
    DEPENDENT UNION 满足依赖外部的关联查询中第二个及以后的查询
    UNION RESULT 联合查询的结果
    SUBQUERY 子查询中的第一个查询
    DEPENDENT SUBQUERY 子查询中的第一个查询,并且依赖外部查询
    DERIVED 用到派生表的查询
    MATERIALIZED 被物化的子查询
    UNCACHEABLE SUBQUERY 一个子查询的结果不能被缓存,必须重新评估外层查询的每一行
    UNCACHEABLE UNION 关联查询第二个或后面的语句属于不可缓存的子查询

    type

    system 查询对象表只有一行数据,且只能用于 MyISAM 和 Memory 引擎的表,这是最好的情况
    const 基于主键或唯一索引查询,最多返回一条结果
    eq_ref 表连接时基于主键或非 NULL 的唯一索引完成扫描
    ref 基于普通索引的等值查询,或者表间等值连接
    fulltext 全文检索
    ref_or_null 表连接类型是 ref,但进行扫描的索引列中可能包含 NULL 值
    index_merge 利用多个索引
    unique_subquery 子查询中使用唯一索引
    index_subquery 子查询中使用普通索引
    range 利用索引进行范围查询
    index 全索引扫描
    ALL 全表扫描
    上表的这些情况,查询性能从上到下依次是最好到最差.

    Extra

    Using filesort 将用外部排序而不是索引排序,数据较小时从内存排序,否则需要在磁盘完成排序 explain select * from t1 order by create_time;
    Using temporary 需要创建一个临时表来存储结构,通常发生对没有索引的列进行 GROUP BY 时 explain select * from t1 group by create_time;
    Using index 使用覆盖索引 explain select a from t1 where a=111;
    Using where 使用 where 语句来处理结果 explain select * from t1 where create_time=‘2019-06-18 14:38:24’;
    Impossible WHERE 对 where 子句判断的结果总是 false 而不能选择任何数据 explain select * from t1 where 1<0;
    Using join buffer (Block Nested Loop) 关联查询中,被驱动表的关联字段没索引 explain select * from t1 straight_join t2 on (t1.create_time=t2.create_time);
    Using index condition 先条件过滤索引,再查数据 explain select * from t1 where a >900 and a like “%9”;
    Select tables optimized away 使用某些聚合函数(比如 max、min)来访问存在索引的某个字段是 explain select max(a) from t1;

    总结

    本文主要讲到如何定位慢 SQL:

    一种方法是查看慢查询日志
    另一种方法是 show process 查看正在执行的 SQL
    再讲到通过 explain 分析慢 SQL,explain 会返回很多字段,其中 select_type、type、key、rows、Extra 是重点关注项。

    展开全文
  • 这篇接着上篇继续介绍show profile 和 trace 分析sql的方法 一 show profile 分析查询 有时需要确定 SQL 到底在哪个环节,此时 explain 可能不好确定。在 MySQL 数据库中,通过 profile,能够更清楚地了解 SQL ...

    前言

    这篇接着上篇继续介绍show profile 和 trace 分析sql的方法

    一 show profile 分析慢查询

    有时需要确定 SQL 到底慢在哪个环节,此时 explain 可能不好确定。在 MySQL 数据库中,通过 profile,能够更清楚地了解 SQL 执行过程的资源使用情况,能让我们知道到底慢在哪个环节。

    可以通过配置参数 profiling = 1 来启用 SQL 分析。该参数可以在全局和 session
    级别来设置。对于全局级别则作用于整个MySQL 实例,而 session 级别仅影响当前 session 。该参数开启后,后续执行的 SQL
    语句都将记录其资源开销,如 IO、上下文切换、CPU、Memory等等。根据这些开销进一步分析当前 SQL 从而进行优化与调整。

    下面我们来讲一下如何使用 profile 分析慢查询,大致步骤是:确定这个 MySQL 版本是否支持 profile;确定 profile 是否关闭;开启 profile;执行 SQL;查看执行完 SQL 的 query id;通过 query id 查看 SQL 的每个状态及耗时时间。

    确定是否支持 profile

    mysql> select @@have_profiling;
    
    +------------------+
    | @@have_profiling |
    +------------------+
    | YES              |
    +------------------+
    
    1 row in set, 1 warning (0.00 sec)
    

    从上面结果中可以看出是YES,表示支持profile的。

    查看 profiling 是否关闭的

    mysql> select @@profiling;
    
    +-------------+
    | @@profiling |
    +-------------+
    |           0 |
    +-------------+
    
    1 row in set, 1 warning (0.00 sec)
    

    结果显示为 0,表示 profiling 参数状态是关闭的。

    通过 set 开启 profile

    mysql> set profiling=1;
    
    Query OK, 0 rows affected, 1 warning (0.00 sec)
    

    Tips:set 时没加 global,只对当前 session 有效。

    执行 SQL 语句

    mysql> select * from t1 where b=1000;
    

    确定 SQL 的 query id
    通过 show profiles 语句确定执行过的 SQL 的 query id:

    mysql> show profiles;
    +----------+------------+-------------------------------+
    | Query_ID | Duration   | Query                         |
    +----------+------------+-------------------------------+
    |        1 | 0.00063825 | select * from t1 where b=1000 |
    +----------+------------+-------------------------------+
    1 row in set, 1 warning (0.00 sec)
    

    查询 SQL 执行详情
    通过 show profile for query 可看到执行过的 SQL 每个状态和消耗时间:

    mysql> show profile for query 1;
    +----------------------+----------+
    | Status               | Duration |
    +----------------------+----------+
    | starting             | 0.000115 |
    | checking permissions | 0.000013 |
    | Opening tables       | 0.000027 |
    | init                 | 0.000035 |
    | System lock          | 0.000017 |
    | optimizing           | 0.000016 |
    | statistics           | 0.000025 |
    | preparing            | 0.000020 |
    | executing            | 0.000006 |
    | Sending data         | 0.000294 |
    | end                  | 0.000009 |
    | query end            | 0.000012 |
    | closing tables       | 0.000011 |
    | freeing items        | 0.000024 |
    | cleaning up          | 0.000016 |
    +----------------------+----------+
    15 rows in set, 1 warning (0.00 sec)
    

    通过以上结果,可以确定 SQL 执行过程具体在哪个过程耗时比较久,从而更好地进行 SQL 优化与调整。

    trace 分析 SQL 优化器

    从前面学到了 explain 可以查看 SQL 执行计划,但是无法知道它为什么做这个决策,如果想确定多种索引方案之间是如何选择的或者排序时选择的是哪种排序模式,有什么好的办法吗?

    从 MySQL 5.6 开始,可以使用 trace 查看优化器如何选择执行计划。

    通过trace,能够进一步了解为什么优化器选择A执行计划而不是选择B执行计划,或者知道某个排序使用的排序模式,帮助我们更好地理解优化器行为。

    如果需要使用,先开启 trace,设置格式为 JSON,再执行需要分析的 SQL,最后查看 trace 分析结果(在 information_schema.OPTIMIZER_TRACE 中)。

    开启该功能,会对 MySQL 性能有所影响,因此只建议分析问题时临时开启。

    下面一起来看下 trace 的使用方法。使用讲解 explain 时创建的表t1做实验。

    首先构造如下 SQL (表示取出表 t1 中 a 的值大于 900 并且 b 的值大于 910 的数据,然后按照 a 字段排序):

    select * from t1 where a >900 and b > 910 order  by a;
    

    我们首先用 explain 分析下执行计划:
    在这里插入图片描述
    通过上面执行计划中 key 这个字段可以看出,该语句使用的是 b 字段的索引 idx_b。实际表 t1 中,a、b 两个字段都有索引,为什么条件中有这两个索引字段却偏偏选了 b 字段的索引呢?这时就可以使用 trace 进行分析。大致步骤如下:

    mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on;
    /* optimizer_trace="enabled=on" 表示开启 trace;end_markers_in_json=on 表示 JSON 输出开启结束标记 */
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from t1 where a >900 and b > 910 order  by a;
    +------+------+------+
    | id   | a    | b    |
    +------+------+------+
    |    1 |    1 |    1 |
    |    2 |    2 |    2 |
    
    ......
    
    | 1000 | 1000 | 1000 |
    +------+------+------+
    1000 rows in set (0.00 sec)
    
    mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE\G
    *************************** 1. row ***************************
    QUERY: select * from t1 where a >900 and b > 910 order  by a    --SQL语句
    TRACE: {
      "steps": [
        {
          "join_preparation": {				--SQL准备阶段
            "select#": 1,
            "steps": [
              {
                "expanded_query": "/* select#1 */ select `t1`.`id` AS `id`,`t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`create_time` AS `create_time`,`t1`.`update_time` AS `update_time` from `t1` where ((`t1`.`a` > 900) and (`t1`.`b` > 910)) order by `t1`.`a`"
              }
            ] /* steps */
          } /* join_preparation */
        },
        {
          "join_optimization": {			--SQL优化阶段
            "select#": 1,
            "steps": [
              {
                "condition_processing": {    --条件处理
                  "condition": "WHERE",
                  "original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))",        --原始条件
                  "steps": [
                    {
                      "transformation": "equality_propagation",
                      "resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))" 		--等值传递转换
                    },
                    {
                      "transformation": "constant_propagation",
                      "resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"       --常量传递转换
                    },
                    {
                      "transformation": "trivial_condition_removal",
                      "resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"        --去除没有的条件后的结构
                    }
                  ] /* steps */
                } /* condition_processing */
              },
              {
                "substitute_generated_columns": {
                } /* substitute_generated_columns */   --替换虚拟生成列
              },
              {
                "table_dependencies": [		--表依赖详情
                  {
                    "table": "`t1`",
                    "row_may_be_null": false,
                    "map_bit": 0,
                    "depends_on_map_bits": [
                    ] /* depends_on_map_bits */
                  }
                ] /* table_dependencies */
              },
              {
                "ref_optimizer_key_uses": [
                ] /* ref_optimizer_key_uses */
              },
              {
                "rows_estimation": [	--预估表的访问成本
                  {
                    "table": "`t1`",
                    "range_analysis": {
                      "table_scan": {
                        "rows": 1000,       --扫描行数
                        "cost": 207.1       --成本
                      } /* table_scan */,
                      "potential_range_indexes": [    --分析可能使用的索引
                        {
                          "index": "PRIMARY",
                          "usable": false,       --为false,说明主键索引不可用
                          "cause": "not_applicable"
                        },
                        {
                          "index": "idx_a",      --可能使用索引idx_a
                          "usable": true,
                          "key_parts": [
                            "a",
                            "id"
                          ] /* key_parts */
                        },
                        {
                          "index": "idx_b",      --可能使用索引idx_b
                          "usable": true,
                          "key_parts": [
                            "b",
                            "id"
                          ] /* key_parts */
                        }
                      ] /* potential_range_indexes */,
                      "setup_range_conditions": [
                      ] /* setup_range_conditions */,
                      "group_index_range": {
                        "chosen": false,
                        "cause": "not_group_by_or_distinct"
                      } /* group_index_range */,
                      "analyzing_range_alternatives": { --分析各索引的成本
                        "range_scan_alternatives": [
                          {
                            "index": "idx_a",	--使用索引idx_a的成本
                            "ranges": [
                              "900 < a"			--使用索引idx_a的范围
                            ] /* ranges */,
                            "index_dives_for_eq_ranges": true, --是否使用index dive(详细描述请看下方的知识扩展)
                            "rowid_ordered": false, --使用该索引获取的记录是否按照主键排序
                            "using_mrr": false,  	--是否使用mrr
                            "index_only": false,    --是否使用覆盖索引
                            "rows": 100,            --使用该索引获取的记录数
                            "cost": 121.01,         --使用该索引的成本
                            "chosen": true          --可能选择该索引
                          },
                          {
                            "index": "idx_b",       --使用索引idx_b的成本
                            "ranges": [
                              "910 < b"
                            ] /* ranges */,
                            "index_dives_for_eq_ranges": true,
                            "rowid_ordered": false,
                            "using_mrr": false,
                            "index_only": false,
                            "rows": 90,
                            "cost": 109.01,
                            "chosen": true             --也可能选择该索引
                          }
                        ] /* range_scan_alternatives */,
                        "analyzing_roworder_intersect": { --分析使用索引合并的成本
                          "usable": false,
                          "cause": "too_few_roworder_scans"
                        } /* analyzing_roworder_intersect */
                      } /* analyzing_range_alternatives */,
                      "chosen_range_access_summary": {  --确认最优方法
                        "range_access_plan": {
                          "type": "range_scan",
                          "index": "idx_b",
                          "rows": 90,
                          "ranges": [
                            "910 < b"
                          ] /* ranges */
                        } /* range_access_plan */,
                        "rows_for_plan": 90,
                        "cost_for_plan": 109.01,
                        "chosen": true
                      } /* chosen_range_access_summary */
                    } /* range_analysis */
                  }
                ] /* rows_estimation */
              },
              {
                "considered_execution_plans": [  --考虑的执行计划
                  {
                    "plan_prefix": [
                    ] /* plan_prefix */,
                    "table": "`t1`",
                    "best_access_path": {          --最优的访问路径
                      "considered_access_paths": [ --决定的访问路径
                        {
                          "rows_to_scan": 90,      --扫描的行数
                          "access_type": "range",  --访问类型:为range
                          "range_details": {
                            "used_index": "idx_b"  --使用的索引为:idx_b
                          } /* range_details */,
                          "resulting_rows": 90,    --结果行数
                          "cost": 127.01,          --成本
                          "chosen": true,		   --确定选择
                          "use_tmp_table": true
                        }
                      ] /* considered_access_paths */
                    } /* best_access_path */,
                    "condition_filtering_pct": 100,
                    "rows_for_plan": 90,
                    "cost_for_plan": 127.01,
                    "sort_cost": 90,
                    "new_cost_for_plan": 217.01,
                    "chosen": true
                  }
                ] /* considered_execution_plans */
              },
              {
                "attaching_conditions_to_tables": {  --尝试添加一些其他的查询条件
                  "original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))",
                  "attached_conditions_computation": [
                  ] /* attached_conditions_computation */,
                  "attached_conditions_summary": [
                    {
                      "table": "`t1`",
                      "attached": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"
                    }
                  ] /* attached_conditions_summary */
                } /* attaching_conditions_to_tables */
              },
              {
                "clause_processing": {
                  "clause": "ORDER BY",
                  "original_clause": "`t1`.`a`",
                  "items": [
                    {
                      "item": "`t1`.`a`"
                    }
                  ] /* items */,
                  "resulting_clause_is_simple": true,
                  "resulting_clause": "`t1`.`a`"
                } /* clause_processing */
              },
              {
                "reconsidering_access_paths_for_index_ordering": {
                  "clause": "ORDER BY",
                  "index_order_summary": {
                    "table": "`t1`",
                    "index_provides_order": false,
                    "order_direction": "undefined",
                    "index": "idx_b",
                    "plan_changed": false
                  } /* index_order_summary */
                } /* reconsidering_access_paths_for_index_ordering */
              },
              {
                "refine_plan": [          --改进的执行计划
                  {
                    "table": "`t1`",
                    "pushed_index_condition": "(`t1`.`b` > 910)",
                    "table_condition_attached": "(`t1`.`a` > 900)"
                  }
                ] /* refine_plan */
              }
            ] /* steps */
          } /* join_optimization */
        },
        {
          "join_execution": {             --SQL执行阶段
            "select#": 1,
            "steps": [
              {
                "filesort_information": [
                  {
                    "direction": "asc",
                    "table": "`t1`",
                    "field": "a"
                  }
                ] /* filesort_information */,
                "filesort_priority_queue_optimization": {
                  "usable": false,             --未使用优先队列优化排序
                  "cause": "not applicable (no LIMIT)"     --未使用优先队列排序的原因是没有limit
                } /* filesort_priority_queue_optimization */,
                "filesort_execution": [
                ] /* filesort_execution */,
                "filesort_summary": {           --排序详情
                  "rows": 90,
                  "examined_rows": 90,          --参与排序的行数
                  "number_of_tmp_files": 0,     --排序过程中使用的临时文件数
                  "sort_buffer_size": 115056,
                  "sort_mode": "<sort_key, additional_fields>"   --排序模式(详解请看下方知识扩展)
                } /* filesort_summary */
              }
            ] /* steps */
          } /* join_execution */
        }
      ] /* steps */
    }
    MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0	--该字段表示分析过程丢弃的文本字节大小,本例为0,说明没丢弃任何文本
              INSUFFICIENT_PRIVILEGES: 0    --查看trace的权限是否不足,0表示有权限查看trace详情
    1 row in set (0.00 sec)
    ------------------------------------------------
    ------------------------------------------------
    
    
    mysql> set session optimizer_trace="enabled=off";
    /* 及时关闭trace */
    

    TRACE 字段中整个文本大致分为三个过程。

    准备阶段:对应文本中的 join_preparation
    优化阶段:对应文本中的 join_optimization
    执行阶段:对应文本中的 join_execution
    使用时,重点关注优化阶段和执行阶段。

    由此例可以看出:

    1.在 trace 结果的 analyzing_range_alternatives 这一项可以看到:使用索引 idx_a 的成本为 121.01,使用索引 idx_b 的成本为 109.01,显然使用索引 idx_b 的成本要低些,因此优化器选择了 idx_b 索引;

    2.在 trace 结果的 filesort_summary 这一项可以看到:排序模式为<sort_key, additional_fields>,表示使用的是单路排序,即一次性取出满足条件行的所有字段,然后在 sort buffer 中进行排序。

    知识点一:MySQL 常见排序模式:
    < sort_key, rowid >双路排序(又叫回表排序模式):是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID,然后在 sort buffer 中进行排序,排序完后需要再次取回其它需要的字段;
    < sort_key, additional_fields >单路排序:是一次性取出满足条件行的所有字段,然后在sort buffer中进行排序;
    < sort_key, packed_additional_fields >打包数据排序模式:将 char 和 varchar 字段存到 sort buffer 中时,更加紧缩。
    三种排序模式比较:
    第二种模式相对第一种模式,避免了二次回表,可以理解为用空间换时间。由于 sort buffer 有限,如果需要查询的数据比较大的话,会增加磁盘排序时间,效率可能比第一种方式更低。
    MySQL 提供了一个参数:max_length_for_sort_data,当“排序的键值对大小” > max_length_for_sort_data 时,MySQL 认为磁盘外部排序的 IO 效率不如回表的效率,会选择第一种排序模式;否则,会选择第二种模式。
    第三种模式主要解决变长字符数据存储空间浪费的问题。
    知识点二:优化器在估计符合条件的行数时有两个选择:
    index diver:dive 到 index 中利用索引完成元组数的估算;特点是速度慢,但可以得到精确的值;
    index statistics:使用索引的统计数值,进行估算;特点是速度快,但是值不一定准确。

    总结

    explain:获取 MySQL 中 SQL 语句的执行计划,比如语句是否使用了关联查询、是否使用了索引、扫描行数等;
    profile:可以清楚了解到SQL到底慢在哪个环节;
    trace:查看优化器如何选择执行计划,获取每个可能的索引选择的代价。

    展开全文
  • 但实际上 SQL 执行起来可能还是很,那么到底从哪里定位 SQL 查询的问题呢?是索引设计的问题?服务器参数配置的问题?还是需要增加缓存的问题呢?今天我们就从性能分析来入手,定位导致 ...
  • 需要注意的是,查询优化器只能在已经确定的情况下(SQL 语句、索引设计、缓冲池大小、查询优化器参数等已知的情况)决定最优的查询执行计划但实际上 SQL 执行起来可能还是很,那么到底从哪里定位 SQL 查询的问题呢...
  • 【0】如何分析mysql中sql执行的问题 步骤1、观察,至少跑一天,看看生产的sql情况; 步骤2、开启查询日志,设置阈值,比如超过5秒钟就是sql, 并将它抓取出来; 步骤3、explain+sql分析; 步骤4、...
  • 性能分析来入手分析,定位导致 SQL 执行慢的原因。前面已经更新了总结核心的主要三点那讲了这这么多数据库服务器的优化分析的步骤是怎样的?中间有哪些需要注意的地方?本篇主要是针对这一个话题的总结和概括。...
  • 如何在MySQL中查找效率SQL语句呢?这可能是困扰很多人的一个问题,MySQL通过查询日志定位那些执行效率较低的SQL 语句,用--log-slow-queries[=file_name]选项启动时,mysqld 会写一个包含所有执行时间超过long...
  • 一、导致SQL执行慢的原因 1、硬件问题。如网络速度慢,内存不足,I/O吞吐量小,磁盘空间满了等。 2、没有索引或者索引失效。(一般在互联网公司,DBA会在半夜把表锁了,重新建立一遍索引,因为当你删除某个数据的...
  • 内容摘要:开启查询日志捕获SQL使用explain分析慢SQL使用show profile查询SQL执行细节常见的SQL语句优化一、开启查询日志捕获SQL① 查询mysql是否开启日志捕获:SHOW VARIABLES LIKE '%slow_query_log%';...
  • MySQL中如何查看“查询”,如何分析执行SQL的效率?
  • 如何分析Mysql慢SQL

    2019-10-05 02:42:43
    使用show profile查询SQL执行细节 常见的SQL语句优化 一、开启查询日志捕获SQL ① 查询mysql是否开启日志捕获:SHOW VARIABLES LIKE '%slow_query_log%'; 如果还没开启的话,开启:SET G...
  • Explain命令在解决数据库性能上是第一推荐使用命令大部分的性能问题可以通过此命令来简单的解决Explain可以用来查看SQL语句的执行效果,可以帮助选择更好的索引和优化查询语句,写出更好的优化语句。使用方法:...
  • 内容摘要:开启查询日志捕获SQL使用explain分析慢SQL使用show profile查询SQL执行细节常见的SQL语句优化一、开启查询日志捕获SQL① 查询mysql是否开启日志捕获:SHOWVARIABLESLIKE'%slow_query_log%';...
  • MySQL提供了查询日志记录功能,用于记录执行时间超过参数long_query_time阈值的SQL执行日志,同时可以自定义配置、关闭、开启查询日志,用以帮助MySQL使用者更好的分析慢查询SQL,便于开展SQL性能优化工作。...
  • 在工作中,我们用于捕捉性能问题最常用的就是打开查询,定位执行效率差的SQL,那么当我们定位到一个SQL以后还不算完事,我们还需要知道该SQL执行计划,比如是全表扫描,还是索引扫描,这些都需要通过EXPLAIN去...
  • mysql命令行或者一些可视化工具在sql执行时间的精度比较低,尤其是命令行只显示到10ms,所以需要打开mysql的执行时间监听set profiling = 1;然后使用show profiles;命令就可查看sql的执行时间。例如:mysql> show...
  • 一、MySQL数据库有几个配置选项可以帮助我们及时捕获低效SQL语句1,slow_query_log这个参数设置为ON,可以捕获执行时间超过一定数值的SQL语句。2,long_query_time当SQL语句执行时间超过此数值时,就会被记录到日志...
  • Mysql如何发现执行速度较Sql,并分析、优化(日志+explain+索引)
  • 我们干开发面试工作的时候,发现对数据库的面试比重很大。...我如何分析SQL执行效率。不要慌,我们已准备了一千万条数据。接下来看看如何将这一千万条数据快速导入到数据库中。1. 创建表和导入一...
  • 作者|马听老师来源 |慕课专栏《一线数据库工程师带你深入理解 MySQL》本文首先会一起讨论一下 SQL 优化,然后分享如何定位查询和如何分析 SQl 执行效率,并用...
  • 如何分析SQL语句

    2019-05-28 17:03:07
    很多时候,我们不太清楚自己写的SQL语句好还是不好,往往数据量一大,程序运行变。其实在SQL/PLUS里可以很清晰的分析SQL语句的执行计划,它可以提醒我们来创建索引或改变SQL语句的写法。 先在sys用户下运行...
  • MySQL慢SQL优化-如何分析性能瓶颈

    千次阅读 2017-07-26 17:26:11
    mysql命令行或者一些可视化工具在sql执行时间的精度比较低,尤其是命令行只显示到10ms,所以需要打开mysql的执行时间监听 set profiling = 1;然后使用show profiles;命令就可查看sql的执行时间。例如:mysql&gt;...
  • 内容摘要:开启查询日志捕获SQL使用explain分析慢SQL使用show profile查询SQL执行细节常见的SQL语句优化一、开启查询日志捕获SQL① 查询mysql是否开启日志捕获:SHOWVARIABLESLIKE'%slow_query_log%';...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 445
精华内容 178
关键字:

如何分析sql执行慢