explain 查看 SQL 的执行计划

本贴最后更新于 811 天前,其中的信息可能已经时移世改

为什么要查看 SQL 的执行计划

  • 通过执行计划对 SQL 使用索引的情况分析
  • 通过慢查询日志获取有性能问题的 SQL

如何使用 explain 命令

在查询语句前加上 explain 即可,如 explain select * from t_test

  • id:执行 SELECT 语句的顺序。id 值相同时,执行顺序由上至下;id 值越大优先级越高,越先被执行
    在有子查询是用得到,因为 MySQL 优化器可以对 SQL 的执行顺序进行编排。单查询往往是 1
  • select_type:
    SIMPLE:不包含子查询或是 UNION 操作的查询
    PRIMARY:查询中如果包含任何子查询,那么最外层的查询则被标记为 PRIMARY
    SUBQUERY:SELECT 列表中的子查询
    DEPENDENT SUBQUERY:子查询中的第一个 SELECT,依赖外部结果的子查询
    UNION:Union 操作的第二个或是之后的查询的值为 union
    DEPENDENT UNION:当 UNION 做为子查询时,第二或是第二个后的查询的 select_type 值
    UNION RESULT:UNION 产生的结果集
    DERIVED:出现在 FROM 子句中的子查询
  • table:输出数据行所在的表的名称
    <unionM,N> 由 ID 为 M,N 查询 union 产生的结果集
    <derivedN>/<subqueryN> 由 ID 为 N 的查询产生的结果
  • partitions:匹配的分区
    对于分区表,显示查询的分区 ID
    对于非分区表,显示为 NULL
  • type:表的连接类型
    ALL:Full Table Scan 全表扫描,遍历全表以找到匹配的行,这是效率最差的联接方式
    index:Full Index Scan 全索引扫描,index 与 ALL 区别为 index 类型只遍历索引树
    range:索引范围扫描,常见于 between、>、< 的查询条件,只检索给定范围的行,使用一个索引选择行
    index_merge:使用了索引合并优化方法
    ref_of_null:类似于 ref 类型的查询,但是附加了对 NULL 值列的查询
    ref:非唯一索引查找,上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
    eq_ref:唯一索或主键引查找,对于每个索引键,表中只有一条记录与之匹配
    const:表中有且只有一个匹配的行时使用,如对主键或是唯一索引的查询,这是效率最高的联接方式
    system:这是 const 联接类型的一个特例,当查询的表只有一行时使用
    NULL:在优化过程中分解语句,执行时甚至不用访问表或索引
  • extra:MySQL 解决查询的详细信息
    Distinct:优化 distinct 操作,在找到第一匹配的元组后即停止找同样值的动作
    Not exists:使用 not exists 来优化查询
    Using filesort:使用额外操作进行排序,通常会出现在 order by 或 group by 查询中
    Using index:使用了覆盖索引进行查询
    Using temporary :MySQL 需要使用临时表来处理查询,常见于排序、子查询和分组查询
    Using where:需要在 MySQL 服务器层使用 WHERE 条件来过滤数据
    select tables optimized away:直接通过索引来获得数据,不用访问表
  • possible_keys:MySQL 能使用那些索引来优化查询
    查询列所涉及到的列上的索引都会被列出,但不一定会被使用
  • key:查询优化器优化查询实际所使用的索引
    如果没有可用的索引,则显示为 NULL,如查询使用了覆盖索引,则该索引仅出现在 Key 列中
  • key_len:索引中使用的字节数,可通过该列计算查询中使用的索引的长度
    key_len 显示的值为索引字段的最大可能长度,并非实际使用长度,即 key_len 是根据表定义计算而得,不是通过表内检索出的
  • ref:列与索引的比较,表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
  • rows:MySQL 通过索引统计信息,估算所需读取的行数,rows 值大小是个统计抽样结果,并不十分准确
  • filtered:返回结果的行数占需读取行数的百分比
    Filtered 列的值越大越好
    Filtered 列的值依赖说统计信息

执行计划的限制

无法展示存储过程,触发器,UDF 对查询的影响

无法使用 explain 对存储过程进行分析

早期版本的 MySQL 只支持对 SELECT 语句进行分析

捕获有问题的 SQL

  • 启动慢查询日志
    set global slow_query_log_file = /sql_log/slow_log.log;
    set global log_queries_not_using_indexes=on;   # 未使用索引的SQL记录日志
    set global long_query time=0.001;		 # 抓取执行超过多少时间的SQL,单位:秒
    set global low_query_log=on;
    
  • 分析慢查询日志
    • mysqldumpslow

      # 查看帮助信息
      $ mysqldumpslow.pl --help
      Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
      
      Parse and summarize the MySQL slow query log. Options are
      
        --verbose    verbose
        --debug      debug
        --help       write this text to standard output
      
        -v           verbose
        -d           debug
        -s ORDER     what to sort by (al, at, ar, c, l, r, t), 'at' is default
                      al: average lock time
                      ar: average rows sent
                      at: average query time
                       c: count
                       l: lock time
                       r: rows sent
                       t: query time
        -r           reverse the sort order (largest last instead of first)
        -t NUM       just show the top n queries
        -a           don't abstract all numbers to N and strings to 'S'
        -n NUM       abstract numbers with at least n digits within names
        -g PATTERN   grep: only consider stmts that include this string
        -h HOSTNAME  hostname of db server for *-slow.log filename (can be wildcard),
                     default is '*', i.e. match all
        -i NAME      name of server instance (if using mysql.server startup script)
        -l           don't subtract lock time from total time
      
    • pt-query-digest

      # 查看帮助信息
      pt-query-digest [OPTIONS] [FILES] [DSN]
      --create-review-table  当使用--review参数把分析结果输出到表中时,如果没有表就自动创建
      --create-history-table  当使用--history参数把分析结果输出到表中时,如果没有表就自动创建
      --filter  对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
      --limit    限制输出结果百分比或数量,默认值是20,即将最慢的20条语句输出,如果是50%则按总响应时间占比从大到小排序,输出到总和达到50%位置截止
      --host  mysql服务器地址
      --user  mysql用户名
      --password  mysql用户密码
      --history 将分析结果保存到表中,分析结果比较详细,下次再使用--history时,如果存在相同的语句,且查询所在的时间区间和历史表中的不同,则会记录到数据表中,可以通过查询同一CHECKSUM来比较某类型查询的历史变化
      --review 将分析结果保存到表中,这个分析只是对查询条件进行参数化,一个类型的查询一条记录,比较简单。当下次使用--review时,如果存在相同的语句分析,就不会记录到数据表中
      --output 分析结果输出类型,值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon,一般使用report,以便于阅读
      --since 从什么时间开始分析,值为字符串,可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点,也可以是简单的一个时间值:s(秒)、h(小时)、m(分钟)、d(天),如12h就表示从12小时前开始统计
      --until 截止时间,配合—since可以分析一段时间内的慢查询
      
      # 案例解读
      # 该工具执行日志分析的用户时间,系统时间,物理内存占用大小,虚拟内存占用大小
      # 343ms user time, 78ms system time, 0 rss, 0 vsz
      # 工具执行时间
      # Current date: Thu Mar 29 15:51:38 2018
      # 运行分析工具的主机名
      # Hostname: NB2015041602
      # 被分析的文件名
      # Files: /d/xampp/mysql/data/NB2015041602-slow.log
      # 语句总数量,唯一的语句数量,QPS,并发数
      # Overall: 5 total, 3 unique, 0.00 QPS, 0.05x concurrency ________________
      # 日志记录的时间范围
      # Time range: 2018-03-28 14:02:06 to 14:22:10
      # 属性               总计      最小    最大    平均    95%  标准    中等
      # Attribute          total     min     max     avg     95%  stddev  median
      # ============     ======= ======= ======= ======= ======= ======= =======
      # 语句执行时间
      # Exec time            60s     10s     17s     12s     17s      3s     11s
      # 锁占用时间
      # Lock time            1ms       0   500us   200us   490us   240us       0
      # 发送到客户端的行数
      # Rows sent             50      10      10      10      10       0      10
      # select语句扫描行数
      # Rows examine     629.99k  45.43k 146.14k 126.00k 143.37k  39.57k 143.37k
      # 查询的字符数
      # Query size         2.81k     235   1.36k  575.40   1.33k  445.36  234.30
      # String:
      # Databases    database_base
      # Hosts        localhost
      # Users        root
      # Query_time distribution
      #   1us
      #  10us
      # 100us
      #   1ms
      #  10ms
      # 100ms
      #    1s
      #  10s+  ################################################################
      # Tables
      #    SHOW TABLE STATUS FROM `database_base` LIKE 'table_list1'\G
      #    SHOW CREATE TABLE `database_base`.`table_list1`\G
      #    SHOW TABLE STATUS FROM `database_base` LIKE 'user_list'\G
      #    SHOW CREATE TABLE `database_base`.`user_list`\G
      # EXPLAIN /*!50100 PARTITIONS*/
      select SQL_CALC_FOUND_ROWS al.*, ul.Alias as userName
              FROM table_list1 al
              LEFT JOIN user_list ul ON ul.ID = al.UserId
               WHERE TRUE  AND (al.SupportCountrys LIKE '%%')
      
               limit 80, 10\G
      
      
      直接分析慢查询文件
      pt-query-digest  slow.log > slow_report.log
      
      分析最近12小时内的查询
      pt-query-digest  --since=12h  slow.log > slow_report2.log
      
      分析指定时间范围内的查询
      pt-query-digest slow.log --since '2017-01-07 09:30:00' --until '2017-01-07 10:00:00'> > slow_report3.log
      
      分析含有select语句的慢查询
      pt-query-digest --filter '$event->{fingerprint} =~ m/^select/i' slow.log> slow_report4.log
      
      针对某个用户的慢查询
      pt-query-digest --filter '($event->{user} || "") =~ m/^root/i' slow.log> slow_report5.log
      
      查询所有全表扫描或full join的慢查询
      pt-query-digest --filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' slow.log> slow_report6.log
      
      把查询保存到query_review
      pt-query-digest --user=root –password=abc123 --review  h=localhost,D=test,t=query_review--create-review-table  slow.log
      
      把查询保存到query_history
      pt-query-digest  --user=root –password=abc123 --review  h=localhost,D=test,t=query_history--create-review-table  slow.log_0001
      pt-query-digest  --user=root –password=abc123 --review  h=localhost,D=test,t=query_history--create-review-table  slow.log_0002
      
      通过tcpdump抓取的tcp协议数据,然后分析
      tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
      pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log
      
      分析biglog
      mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
      pt-query-digest  --type=binlog  mysql-bin000093.sql > slow_report10.log
      
      分析general log
      pt-query-digest  --type=genlog  localhost.log > slow_report11.log
      
  • 数据库

    据说 99% 的性能瓶颈都在数据库。

    330 引用 • 614 回帖
  • MySQL

    MySQL 是一个关系型数据库管理系统,由瑞典 MySQL AB 公司开发,目前属于 Oracle 公司。MySQL 是最流行的关系型数据库管理系统之一。

    673 引用 • 535 回帖
1 操作
Hefery 在 2022-01-08 00:57:07 更新了该帖

相关帖子

欢迎来到这里!

我们正在构建一个小众社区,大家在这里相互信任,以平等 • 自由 • 奔放的价值观进行分享交流。最终,希望大家能够找到与自己志同道合的伙伴,共同成长。

注册 关于
请输入回帖内容 ...