监控mysql业务数据分析_Mysql数据库监控分析

Mysql是目前的关系型数据库中，使用频率较高的数据库之一，其监控手段的多样性得益于社区的高度活跃，我们今天就来聊一下Mysql数据库的监控分析手段，Mysql的常用监控分析方法：常规慢SQL日志获取、PT工具、Monyog工具(商用)，我们重点介绍下常规方法慢SQL以及商用工具Monyog。

一、慢SQL是Mysql数据库的标志性监控方法，主要的步骤如下：

1、找到消耗较高的SQL语句(慢SQL、消耗CPU高的)

1)打开慢SQL开关

slow_query_log开关参数是否打开

如果为OFF状态，set global slow_query_log=0;设置打开

2)查看long_query_time设置慢SQL阈值

修改long_query_time为合理阈值，set global long_query_time = 0.1;

3)查看慢SQL日志所在路径

4)通过慢sql分析工具mysqldumpslow格式化分析慢SQL日志

mysqldumpslow–s t -t 3 /mysqldata/citicsql/data/data/DRMS501-slow.log查询出最慢的三个sql语句

mysqldumpslow–s c –t 10 查询出使用最多的10个sql语句；

2、如果没有慢SQL，但是数据库CPU消耗仍然很高，需要抓取目前使用的SQL进行细化分析，可以查看当前执行的SQL语句

1)通过show processlist；查看实时线程执行sql信息。

2)将慢SQL阈值设置的足够小，打印全量的SQL语句。

3、针对出现慢SQL或者消耗高CPU的SQL进行具体分析

1)通过执行计划分析，执行计划命令为explain SQL语句；

2)查看对应SQL使用到的数据表建的索引信息，show index from tablename

3)根据执行计划，优化SQL增加索引后，重新运行执行计划进行对比

注：执行计划的主要信息项说明：

列

含义

Id

一组数字，表示查询中执行select子句或操作表的顺序，id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行。

Select_type

表示查询中每个select子句的类型(简单or复杂)

SIMPLE:简单选择，不用union或子查询

PRIMARY:最外层的选择

UNION：union中的第二个或后面的select语句

DEPENDENT UNION：union中第二个或后面的select

SUBQUERY：在SELECT或WHERE列表中包含了子查询，该子查询被标记为SUBQUERY，子查询第一个

DERIVED：在FROM列表中包含的子查询被标记为DERIVED

UNION RESULT：从UNION表获取结果的SELECT被标记为UNION RESULT

Table

查询的表名

Partitions

匹配的分区，需指定关键字PARTITIONS

Type

访问类型，如全表、索引等

All：全表扫描

Index：全表扫描，与ALL区别为index只遍历索引树

Range:索引范围扫描，常见于between、等查询

Ref:非唯一行索引扫描，返回匹配某个单独值得所有行

Eq_ref：唯一索引等值查询，常见于主键或唯一索引扫描

Const\system：对查询某部分进行优化，并转换成一个常量时，使用这些类型访问。

NULL:优化过程中分解语句，执行时甚至不用访问表或索引

Possible_keys

指出Mysql能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用

Key

显示Mysql在查询中实际使用的索引，若没有使用索引，显示为NULL，

Key_len

表示索引中使用的字节数，key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

Ref

哪些列或常量被用于查找索引列上的值

Rows

表示Mysql根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。

Filtered

查询条件过滤表行数的百分比。

Extra

额外信息

Distinct:一旦MYSQL找到了与行相联合匹配的行，就不再搜索了

Not exists: MYSQL优化了LEFT JOIN，一旦它找到了匹配LEFT JOIN标准的行，就不再搜索了

Range checked for each Record(index map:#):没有找到理想的索引，因此对于从前面表中来的每一个行组合，MYSQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一

Using filesort: 看到这个的时候，查询就需要优化了。MYSQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行

Using index: 列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候

Using temporary 看到这个的时候，查询需要优化了。这里，MYSQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

Using where 使用了WHERE从句来限制行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型ALL或index，这就会发生，或者是查询有问题不同连接类型的解释(按照效率高低的顺序排序)

二、MONYOG工具介绍

MonYog是比较全面的监控Mysql数据库的工具，从快照到实时监控到参数获取一应俱全，下面主要介绍常用的功能。

1、Dashboard：图形化监控大屏包括了连接数、吞吐量、缓存命中、事务等监控、CPU、内存、IO等

2、OverView：全局监控当前数据库下的SQL的运行情况，包括SQL语句、执行次数、总耗时和平均单个SQL耗时

3、RealTime：快照抓取能够抓取当前时间段内的SQL的执行情况，能够查看完整SQL耗时和对应的执行计划信息

4、ServerConfig：全量数据库的参数配置信息

详细的MONYOG使用手册等信息，请参考：https://webyog.com/product/monyog/

小提示：

1、所有的关系型数据库的监控点其实都一样，区别在于监控手段方法，因此要了解其本质。

2、数据库的慢SQL只是其问题分析方向之一，没有慢SQL不代表没有性能问题，要进行精细化分析。

3、数据库的的慢SQL分析，不只是索引影响，生产和线下压测的差异性的关键因素还有数据量、数据分布、数据模型、参数配置等。

4、数据库的设计要遵循设计规范，按照标准规范设计才能将性能问题扼杀在摇篮中。