简单调查Mysql性能消耗

我们可以通过Mysql自带的一些工具来进行简单的分析一些任务的性能。

前言

在使用Mysql的过程中，可能会遇到一些性能问题，整体性能突然变差了或者某个查询语句执行速度很慢，这时候可能就需要我们去调查下，由于什么原因引起的。

搭建测试环境

准备数据

我们先用sysbench创建一张有100W条记录的表

sysbench --test=oltp --oltp-table-size=1000000 --mysql-db=test --mysql-user=root prepare

表结构和部分表数据

表结构

表结构

表数据

表数据

Mysql自带的一些查询工具

Mysql自带了一些工具，可以用来测量任务的执行性能，下面会简单的介绍一些工具的用法。

单条查询性能调查-SHOW PROFILE

1.我们可以在mysql通过下面的命令，将profile功能打开

    set profiling =1;

2.然后执行相应的一些操作，这些记录的一些数据就会记录到一张临时表中，例如我们执行下面的语句:

    select pad from sbtest where pad like 'rrrr';

    select pad from sbtest where pad like 'rrrr' limit 0,10;

3.不同的查询有会不同的queryId和查询时间，我们可以用过下面语句进行查看

    show profiles;

效果图
4.使用show profiles获得数据比较简单，我们还可以对单条查询进行剖析

    show profile for query 1;

效果图
5.show profile语句无法根据时间进行排序，我们可以通过下面的语句进行排序，这样就能比较方便查看时间主要消耗哪里

    set @query_id=1;
    select 
    state,
    sum(duration) as total_r,
    round(100 * sum(duration) / (select sum(duration) from information_schema.profiling where query_id=@query_id),2) as pct_r,
    count(*) as calls,
    sum(duration) / count(*) as "r/call"
    from information_schema.profiling
    where query_id=@query_id
    group by state
    order by total_r desc;

效果图

通过explain获取sql语句执行的一些关键信息,例如是否有用到索引，涉及行数等

分别执行下面两条语句

explain select * from sbtest limit 0, 10;

explain select * from sbtest where id=100;

效果图

通过上图，我们清楚两条语句，一条有用到索引并且涉及行数为1，另一条没有用到索引，并且几乎涉及全表数据

查看服务器状态

show global status

通过show global status我们可以看到当前服务器的状态，例如连接线程数量，正在运行的线程数

效果图

查看服务器线程状态

show processlist

通过show processlist我们可以看到当前所有线程的状态，通过状态值，我们可以当前线程是否有异常

效果图

慢查询日志

我们可以通过慢查询日志，来查看一些慢查询的信息，我们也可以通过将慢查询的时间设置为0，来获取所有的查询的信息

查看慢查询日志的是否打开以及日志文件位置

show variables like 'slow_query%';

效果图

打开慢查询日志

set global slow_query_log='ON';

设置慢查询时间

set long_query_time=0;

效果图

查看慢查询日志

我们可以直接打开慢查询日志文件来查看相应的信息

效果图

结尾

这些工具都是Mysql自带的，所以当出现问题的时候，可以直接使用。基于这些工具我们也可以开发出其他功能更加丰富工具，来对数据库进行实时监控以及报警。