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 删除。

编辑于

我来说两句

2 条评论
登录 后参与评论

相关文章

来自专栏java达人

mysql性能优化的几条重要建议

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事...

1976
来自专栏Java技术

告诉你38个MySQL数据库的小技巧!

培养兴趣 兴趣是最好的老师,不论学习什么知识,兴趣都可以极大地提高学习效率。当然学习MySQL 5.6也不例外。 夯实基础 计算机领域的技术非常强调基础,刚开始...

381
来自专栏积累沉淀

Python快速学习第九天--安装并操作Mysql数据库

python操作mysql数据库 Python 标准数据库接口为 Python DB-API,Python DB-API为开发人员提供了数据库应用编程接口。...

2228
来自专栏coder修行路

Go基础之--操作Mysql(一)

关于标准库database/sql database/sql是golang的标准库之一,它提供了一系列接口方法,用于访问关系数据库。它并不会提供数据库特有的方法...

3059
来自专栏高性能服务器开发

数据库进阶2 Mysql高并发优化

如果不能设计一个合理的数据库模型,不仅会增加客户端和服务器段程序的编程和维护的难度,而且将会影响系统实际运行的性能。所以,在一个系统开始实施之前,完备的数据库模...

591
来自专栏Java3y

数据库面试题(开发者必看)

数据库常见面试题(开发者篇) ? ? 这里写图片描述 什么是存储过程?有哪些优缺点? 什么是存储过程?有哪些优缺点? 存储过程就像我们编程语言中的函数一样,封装...

3705
来自专栏个人分享

Hive操作表部分总结

create table tableName(time INT,userid BIGINT,url STRING,ip STRING COMMENT 'IP A...

672
来自专栏PHP技术

MySQL性能优化的21条经验

1. 为查询缓存优化你的查询 大多数的MySQL服务器都开启了查询缓存。这是提高性最有效的方法之一,而且这是被MySQL的数据库引擎处理的。当有很多相同的查询被...

3078
来自专栏Linyb极客之路

MYSQL性能优化

今天,数据库的操作越来越成为整个应用的性能瓶颈了,这点对于Web应用尤其明显。关于数据库的性能,这并不只是DBA才需要担心的事,而这更是我们程序员需要去关注的事...

803
来自专栏JackieZheng

RabbitMQ入门-Topic模式

上篇《RabbitMQ入门-Routing直连模式》我们介绍了可以定向发送消息,并可以根据自定义规则派发消息。看起来,这个Routing模式已经算灵活的了,但是...

19610

扫码关注云+社区