1.show status 语句解析
show status 语句支持的参数
参数值 | 参数说明 |
---|---|
connections | 连接mysql服务器的次数 |
uptime | MySQL服务器启动后连续工作的时间 |
slow_queries | 慢查询的次数 |
com_insert | 插入数据的次数批量插入多条数据的时候,只增加1 |
com_delete | 删除数据的次数,每次增加1 |
com_update | 修改数据的次数,每次增加1 |
com_select | 查询数据的次数,一次查询操作增加1 |
Innodb_rows_read | 查询数据的时候返回的数据行数 |
Innodb_rows_inserted | 插入数据的时候返回的记录数 |
Innodb_rows_updated | 修改数据的时候返回的记录数 |
Innodb_rows_deleted | 删除数据的时候返回的记录数 |
实例:使用show status 语句查看连接MySQL服务器的次数
show status like 'connections'
查看MySQL服务器启动后连续工作的时间
show status like ‘uptime’
2.explain语句的解析
MySQL中支持使用explain语句来获取执行查询语句的信息,只需要将explain关键字添加到查询语句的前面即可,此时MySQL不会真的去查询数据,而是根据explain模拟优化器执行sql语句,并输出sql语句在MySQL中的执行信息。
语法格式:
explain select select_expr
解析:
expla:分析查询语句的关键字
select:执行查询语句的关键字
select_expr:查询语句的查询选项
实例:
使用explain语句分析查询t_goods数据表中的数据
explain select goods.name ,goods.price
->from goods left join user
->on goods.user_id=user.id \G
具体的参数:请参考相关资料
注意:在分析查询语句的时候,desc的作用和explain的作用完全相同。
3.show profile语句解析
MySQL从5.0.37版本开始支持show profiles语句和show profile语句,可以通过如下的语句查看MySQL是否支持profile。
select @@have_profiling;
如果是关闭的可以通过如下的命令来开启:
set session profiling = 1;
关闭的命令:
set session profiling = 0;
小知识点:myisam存储引擎的数据表存在数据表元数据的存储信息,对myisam数据表执行count()的查询时不会消耗太多的资源,而InnoDB存储引擎的数据不存在数据表元数据的存储信息,执行count()查询就会消耗一定的资源。
a.分析InnoDB数据表
查看数据表中的记录条数
select count(*) from tab_01;
查看完毕后,使用show profiles 语句查看sql语句信息。
show profiles;
查询记录条数的sql语句的query_ID,这里假设为3。接下来,通过show profile for query 语句查看sql语句执行过程中所在线程的具体信息。
show profile for query 3;
如果需要查看sql语句消耗的block io资源使用如下的命令:
show profile block io for query 3;
b.分析myisam数据表
查询数据表中的数据的记录的条数:
select count(*) from tab_01;
使用show profiles 语句查看sql语句的执行情况:
show frofiles;
查询记录条数的sql语句的query_ID,这里假设为3。接下来,通过show profile for query 语句查看sql语句执行过程中所在线程的具体信息。
show profile for query 3;
4.pt-query-digest分析查询
pt-query-digest是percona工具包的一部分,可以对数据查询进行分析。使用pt-query-digest分析查询之前,需要先安装percona工具包。
首先安装percona工具包(在centOS 6.8):
sudo yum install https://www.percona.com/downloads/percona-release/0.1-4/percona-release-0.1-4.noarch.rpm
sudo yum list | grep percona
sudo yum install percona-toolkit --nogpgcheck
pt-query-digest支持对慢查询日志,通用查询日志,二进制日志,进程列表和tcp转储等进行分析。
a.分析慢查询日志
假设慢查询日志的位置为/home/logs/mysql/mysql-slow.log,则可以使用如下的语句将分析结果写入到mysql-slow-digest.log文件中。
sudo pt-query-digest /home/logs/mysql/mysql-slow.log >mysql-slow-digest.log
此时,mysql-slow-digest.log文件中包含查询的校验和,平均时间,百分比时间和执行次数等信息。
b.分析通用查询日志
在pt-query-digest命令后传递–type genlog 参数可以分析MySQL的通用查询日志,格式如下:
sudo pt-query-digest --type genlog /home/logs/mysql/mysql-slow.log >mysql-slow-digest.log
注意:使用sudo pt-query-digest 命令分析通用查询日志不会统计查询次数
c.分析二进制日志
使用pt-query-digest 命令分析二进制日志的时候,需要先使用mysqlbinlog工具将二进制日志转换成文本文件,格式如下:
sudo mysqlbinlog /home/logs/mysql/mysql-bin >mysql-bin-degist
接下来使用pt-query-digest命令分析mysql-bin-degist文件,格式如下:
sudo pt-query-digest --type binlog mysql-degist > mysql-bin-result
mysql-bin-result文件中存储了最终的分析结果
d.分析进程列表
pt-query-digest 命令支持从进程列表中读取查询信息,格式如下:
sudo pt-query-digest --processlist h=localhost --iterations --run-time 1m -uroot -p123456
其中,run-time参数指定每次运行的时间
e.分析TCP转储
pt-query-digest命令支持分析从tcpdump发送过来的tcp流量数据。首先,使用tcpdump抓取端口3306的tcp流量信息,如下:
sudo tcpdump -s 65535 -x -nn -q -tttt -i any -c 1024 port 3306 > mysql-tcp.log
接下来使用pt-query-digest分析mysql-tcp.log文件中的数据。
sudo pt-query-digest --type tcpdump mysql-tcp.log > mysql-tcp-result.log
其中,mysql-tcp-result.log文件存放了最终的分析结果
5.优化子查询
执行子查询时,MySQL需要生成一张临时表来存放子查询语句的结果数据。外层查询语句从临时表中查询数据时待所有数据查询完毕后,MySQL再将生成的临时表删除,因此子查询的效率不高。
在MySQL中有两种查询方式可以代替子查询:一种方式是使用join连接查询来代替子查询,因为使用join连接语句查询数据的时候不需要建立临时表,如果为join连接语句创建合适的索引,查询效率会更加的高效;另一种方式是使用公用表表达式来代替子查询。