MySQL 查询分析

如今数据库的操作越来越成为整个应用的性能瓶颈了。对于数据库性能,并不仅仅是 DBA 关注的事情,也是开发和测试人员需要关注的事情。在设计数据库表结构时,在操作数据库时(特别是查询 sql 语句),都需要我们考虑数据库的性能问题。本文主要由一个案例引发对 MySQL 性能问题的思考,主要讲述 MySQL 慢查询和 explain 工具这两个定位 MySQL 性能瓶颈的方法。索引和查询优化部分后续将放在《MySQL 索引及查询优化总结》中。

一个低效查询引发的思考

上次在做银行对账,上传对账单后,出现对账超时的情况。查看日志发现,最后一条日志记录停在了对 c2c_zwdb.t_file_count 的查询 sql 上。使用 show processlist 命令来查看当前 SQL 的执行情况,如下:

由上图可知,原来是发生锁表了 waiting for table level lock。

引发锁表的 sql 语句就是上图中 status 为 updating 的语句为:

update c2c_zwdb.t_file_count set Fcount=Fcount 1 where FFileName='1001_招商银行 (1).txt' and Ftype=2

该条 update 语句还未执行完,给表 c2c_zwdb.t_file_count 加的写锁还没释放,又执行 select 读操作,select 语句会等待表级锁,导致阻塞而使银行对账超时。

为什么这条 update 语句执行了如此久还没执行完呢?这个语句不够高效,当在数据量很大的情况下,执行效率更慢。

定位 MySQL 性能瓶颈的方法很多,主要为这两种:慢查询与 explain 命令。

一 慢查询

慢查询,顾名思义,就是查询超过指定时间 long_query_time 的 SQL 语句查询称为"慢查询"。 慢查询帮我们找到执行慢的 SQL,方便我们对这些 SQL 进行优化。

慢查询开启方法

long_query_time 是用来定义慢于多少秒的才算"慢查询"。查询 long_query_time 的值如下:

我们可以将其设置设置 long_query_time=2,如下。

开启慢查询的方法,一是可以通过在配置文件 my.cnf 或 my.ini 中设置配置参数,二是可以通过命令行设置变量来即时启动慢查询日志,个人比较喜欢第二种即时性的。由下图可知,记录慢查询日志已开启,slow_query_log=ON。

slow_query_log 是否打开记录慢查询日志

slow_query_log_file 日志存放位置

MySQLdumpslow命令

接下来看看慢查询日志的格式是怎么样。例如,在 MySQL 中运行 select sleep(3);

打开慢查询日志文件 MySQL-slow.log 的信息格式如下,说明这条 sql 语句执行用时 5.000183s,锁了 0s,查询返回 1 行,一共查了 0 行。

随着 MySQL 数据库服务器运行时间的增加,可能会有越来越多的 SQL 查询被记录到了慢查询日志文件中,这时要分析慢查询日志就显得不是很容易了。MySQL 提供的 MySQLdumpslow 命令,可以很好地解决这个问题。

MySQLdumpslow 的主要功能是统计不同慢 sql 的:

  • 执行次数(count)
  • 执行最长时间(time)
  • 累计总耗费时间(time)
  • 等待锁的时间(lock)
  • 发送给客户端的行总数(rows)
  • 扫描的行总数(rows)

进入 MySQL/bin 目录,输入 MySQLdumpslow -help 或--help 可以看到这个工具的参数。

-s,是表示按照何种方式排序,c、t、l、r 分别是按照执行次数、执行时间、等待锁时间、返回的记录数来排序,ac、at、al、ar 表示相应的平均值;

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

比如,执行./MySQLdumpslow -s c -t 5/data/zftMySQLData/MySQL-slow.log,得到执行次数最多的前 5 个查询,如下图所示。

执行./MySQLdumpslow -s r -t 10 /data/zftMySQLData/MySQL-slow.log,得到返回记录数最多的前 10 个查询。

使用 MySQLdumpslow 命令可以非常明确的得到各种我们需要的查询语句,对 MySQL 查询语句的监控、分析、优化是 MySQL 优化的第一步,也是非常重要的一步。

二 explain 分析查询

在分析查询性能时,EXPLAIN 关键字同样很管用。EXPLAIN 关键字一般放在 SELECT 查询语句的前面,使用 EXPLAIN 关键字可以模拟优化器执行 SQL 查询语句,从而知道 MySQL 是如何处理 SQL 语句的。这可以帮助分析查询语句效率低下的原因或是表结构的性能瓶颈。通过 explain 命令可以得到:

– 表的读取顺序

– 数据读取操作的操作类型

– 哪些索引可以使用

– 哪些索引被实际使用

– 表之间的引用

– 每张表有多少行被优化器查询

Explain的用法

Explain tablename 或

Explain [EXTENDED] SELECT select_options

前者可以得出一个表的字段结构等等,后者主要是给出相关的一些索引信息,本文要讲述的重点是后者。

首先看看 explain 的输出参数:

这些参数中,各个参数的含义如下,

Id:本次 select 的标识符。在查询中每个 select 都有一个顺序的数值。

Select_type:select 类型,主要是区别普通查询和联合查询、子查询之类的复杂查询。主要有这几种:

  • SIMPLE:这个是简单的 sql 查询,不使用 UNION 或者子查询。
  • PRIMARY:子查询中最外层的 select。
  • UNION:UNION 中的第二个或后面的 SELECT 语句。
  • DEPENDENT UNION:UNION 中的第二个或后面的 SELECT 语句,取决于外面的查询。
  • UNION RESULT:UNION 的结果。
  • SUBQUERY:子查询中的第一个 SELECT。
  • DEPENDENT SUBQUERY:子查询中的第一个 SELECT,取决于外面的查询。
  • DERIVED:派生表的 SELECT(FROM 子句的子查询)。

Table:输出行所引用的表。 

Type:联合查询所使用的类型。

type 显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说,得保证查询至少达到 range 级别,最好能达到 ref。

possible_keys:指出 MySQL 能使用哪个索引在该表中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验 WHERE 子句,看是否引用某些字段,或者检查字段不是适合索引。

Key:显示 MySQL 实际决定使用的键。如果没有索引被选择,键是 NULL。

key_len:显示 MySQL 决定使用的键长度。如果键是 NULL,长度就是 NULL。文档提示特别注意这个值可以得出一个多重主键里 MySQL 实际使用了哪一部分。

Ref:显示哪个字段或常数与 key 一起被使用。

Rows:这个数表示 MySQL 要遍历多少数据才能找到,在 innodb 上是不准确的。 Extra:如果是 Only index,这意味着信息只用索引树中的信息检索出的,这比扫描整个表要快。

如果是 where used,就是使用上了 where 限制。

如果是 impossible where 表示用不着 where,一般就是没查出来啥。

如果此信息显示 Using filesort 或者 Using temporary 的话会很吃力,WHERE 和 ORDER BY 的索引经常无法兼顾,如果按照 WHERE 来确定索引,那么在 ORDER BY 时,就必然会引起 Using filesort,这就要看是先过滤再排序划算,还是先排序再过滤划算。

现在我们再用 explain 来看看前面案例的 sql 执行情况。首先,先看看 t_file_count 的表结构如下,该表的索引是 FId。

未执行完的 sql 语句是 update c2c_zwdb.t_file_count set Fcount=Fcount 1 where FFileName='1001_招商银行 (1).txt' and Ftype=2

将其转换为 select 语句,select count(*) from c2c_zwdb.t_file_count where FFileName='1001_招商银行 (1).txt' and Ftype=2。执行explain命令如下:

由上图可见,type=all,key=NULL,该 sql 未使用索引,是一个效率非常低的全表扫描,在数据量很大的情况下,性能情况可想而知。

上述讲的几种方法是用来定位 MySQL 的性能瓶颈,那定位出性能问题后,接下来就是对这些低效的 sql 语句进行优化。问题是怎么优化?根据什么原理来优化?这涉及到索引问题,这一部分将会后续放在《MySQL 索引及查询优化总结》中讲述,本文主要讲述定位 MySQL 性能瓶颈的两种方法:慢查询和 MySQLdumpslow 工具,explain 命令。

原创声明,本文系作者授权云+社区发表,未经许可,不得转载。

如有侵权,请联系 yunjia_community@tencent.com 删除。

编辑于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Aloys的开发之路

Oracle系统表整理+常用SQL语句收集

-- DBA/ALL/USER/V_$/GV_$/SESSION/INDEX开头的绝大部分都是视图 -- DBA_TABLES意为DBA拥有的或可以访问的所有...

19310
来自专栏禅林阆苑

mysql 命令完全总结 【原创】

mysql 命令完全总结 Write By CS逍遥剑仙 我的主页: www.csxiaoyao.com GitHub: github.com...

32211
来自专栏IT探索

mysql 事务

a) mysql_autocommit(0); 如果程序在此处coredump,请检查是否connect db

501
来自专栏维C果糖

史上最简单的 MySQL 教程(二十八)「外键(上)」

外键:foreign key,外面的键,即不在自己表中的键。如果一张表中有一个非主键的字段指向另外一张表的主键,那么将该字段称之为外键。每张表中,可以有多个外键...

3465
来自专栏Django Scrapy

mysql相关命令

安装 在ubuntu 14.04 x64下 1 安装 # 安装服务端 (需要中途设置root密码) sudo apt-get install mysql-ser...

3167
来自专栏Java架构

一线互联网公司是怎么处理mysql事务以及隔离级别的?

MySQL 事务主要用于处理操作量大,复杂度高的数据。比如说,在人员管理系统中,你删除一个人员,你即需要删除人员的基本资料,也要删除和该人员相关的信息,如信箱,...

622
来自专栏社区的朋友们

MySQL 入门常用命令大全(上)

作为一个 MySQL 的初学者,在短短的几个月中接触了一下,记录了一下工作中用到的 SQL 语句以及未来可能会用到的 MySQL 知识点,作为日后的参考手册。因...

7611
来自专栏李智的专栏

Mysql学习(基本指令、语句)

  1) 数值   int //int(3)与长度无关,不够3位前面补0,默认看不见     float   2) 字符串 ...

492
来自专栏沃趣科技

mysqldump与innobackupex备份过程你知多少(二)

1.2.3. 使用WITH CONSISTENT SNAPSHOT子句的作用 START TRANSACTION语句使用WITH CONSISTENT SNA...

3556
来自专栏csxiaoyao

mysql 命令完全总结

4147

扫码关注云+社区