mysql优化探索（2）查看整个mysql的命令执行状态、如何定位慢sql语句（sql优化）

一.查看整个mysql的命令执行状态

show [session | global] status 命令：

可以查看对mysql操作命令的执行次数，session选项是指本次会话对命令的执行次数，global选项是指全部会话对命令的执行次数。

例如show global status like 'Com_%';这条命令会显示以下对以下命令的执行次数：

Com_select:执行SELECT语句的操作次数，一次查询会增加1.

Com_insert:执行INSERT语句的操作次数，一次执行增加1.

Com_update:执行UPDATE操作的次数。

Com_delete:执行DELETE操作的次数。

Com_commit:事物的提交次数

Com_rollback:事物的回滚次数

再例如show global status like 'Innodb_%';这条命令会显示以下对以下命令的执行次数：

Innodb_rows_read:Innodb存储引擎下SELECT的操作次数。

Innodb_rows_insterted:Innodb存储引擎下INSERT的操作次数

Innodb_rows_updated:Innodb存储引擎下UPDATE操作次数

Innodb_rows_deleted:Innodb存储引擎下DELETED的操作次数

再例如show global status like 'Connections';是试图连接mysql的的连接次数

再例如show global status like 'Uptime';是mysql服务器的运行时间

再例如show global status like 'Slow_queries';是慢查询的次数

通过以上的这些显示，我们可以了解到，应用是已查询为主，还是以添加更新为主。知道事物的执行次数和回滚次数，例如如果事物的混滚次数特别多，说明应用程序有可能有bug，慢查询执行的次数太多的话，说明应用程序性能堪忧。

二、怎么定位慢查询语句

定位慢查询语句有两种方法，一种是启用和设置慢查询日志，第二种方法是用show processlist或者show full processlist命令在应用执行sql语句时捕捉慢查询sql，由于当sql执行完毕时才被写入慢查询日志，所以当出现应用性能问题时不一定能定位到真正的问题。但是show processlist或者show full processlis命令会实时反应定位应用出现性能问题时的慢sql。

第一种方法，开启慢查询日志

1.查询慢查询时间 

SHOW VARIABLES LIKE "long_query_time"

2.查询慢查询日志文件地址

SHOW VARIABLES LIKE "%slow%

3.开启慢查询功能

编辑/etc/my.cnf在[mysqld]下添加以下三行：

#是否开启慢查询日志，1为开启

slow_query_log  = 1

#sql请求时间超过多少秒被视为慢查询这里可以根据自己需要配置
long_query_time = 0.00001

#慢查询日志文件位置
slow_query_log_file  = /data/mysql/mysql-slow.log

配置好后重启mysql：

service mysqld start

测试：

进入mysql 执行select  sleep(1);然后查看慢查询日志

cat /data/mysql/mysql-slow.log

如上图显示第一行时记录时间，第二行时用户信息，第三行时执行语句每个步骤都用了多少时间，第四行是语句实体。

第二种方法使用show processlist或者show full processlis确定慢sql

查询显示如下图：

以下是对各个字段的解释：

id:用户登陆mysql时系统分配的connection_id

user:显示当前用户，如果不是root这个命令就只显示用户权限范围的sql语句

host:显示这个语句是从哪个ip和端口发起的，可以用来追踪问题sql的用户

db:显示进程连接的是哪个数据库

command:显示当前连接的执行命令，一般值为sleep休眠状态，query查询状态，connect连接状态。

time:显示这个状态的持续时间单位是秒

state:显示使用当前连接的sql语句的状态，这个字段我们一定要重视。state描述的是语句执行中的某一个状态。一个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成。

info:显示这个sql语句，是判断有问题的sql语句的重要依据，一般记录的是线程执行的sql语句。默认显示语句的前100字符，如果想看全部语句要用show full processlist命令。