mysql查询优化

1.show status 语句解析
show status 语句支持的参数

参数值参数说明
connections连接mysql服务器的次数
uptimeMySQL服务器启动后连续工作的时间
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连接语句创建合适的索引,查询效率会更加的高效;另一种方式是使用公用表表达式来代替子查询。

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

原克技术

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值