Mysql性能优化——慢查询分析

MYSQL数据库是常见的两个瓶颈是CPU和I/O的瓶颈,CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候。磁盘I/O瓶颈发生在装入数据远大于内存容量的时候,如果应用分布在网络上,那么查询量相当大的时候瓶颈就会出现在网络上,我们可以用mpstat, iostat,sar和 vmstat来查看系统的性能状态。

除了服务器硬件的性能瓶颈,对于Mysql系统本身,我们可以使用工具来优化数据库的性能,通常有三种:使用索引,使用 EXPLAIN分析查询以及调整MYSQL的内部配置。

一、查询与索引优化分析

在优化mysql时,通常需要对数据库进行分析,常见的分析手段有慢查询日志, EXPLAIN分析查询, profiling分析以及show命令查询系统状态及系统变量,通过定位分析性能的瓶颈,才能更好的优化数据库系统的性能。

性能瓶颈定位

1、show命令

通过show命令查看mysql状态及变量,找到系统的瓶颈,

Mysql > show variables; 查看mysql服务器配置信息

Mysql > show global status; 查看mysql服务器运行的各种状态值

#mysqadmin variables -u username -ppassword ——显示系统变量

#mysqladmin extended-status -u username -ppassword ——显示状态信息

其余show命令可以参考:mysql >help show

2、慢查询日志

慢查询日志开启方法一

在配置文件my.cnf中[mysqld]下加入以下内容并重启mysql服务

Slow_query_log=1 //0关闭 1开启

Slow_query_log_file=/usr/local/mysql/data/slow-query.log //慢查询日志存放地点

Long_query_time=1 //表示查询超过1秒才记录

注:在my.cnf中添加log-queries-not-using-indexes参数,表示向慢查询日志中记录没有使用索引的查询。

慢查询日志开启方法二

通过命令行设置变量来即时启动慢查询日志

查看慢查询的设置信息

打开慢查询日志文件查看

从日志中,可以发现查询时间超过0.01秒的sql,而小于0.01秒的没有出现在此日志中。

如果慢查询日志中记录内容很多,可以使用 mysqldumpslow工具(Msql客户端安装自带)来对慢查询日志进行分类汇总。 mysqldumpslow对日志文件进行了分类汇总,显示汇总后摘要结果。(具体参数可以参考#mysqldumpslow --help)

优化上面的慢查询

二、explain分析查询

使用explain可以模拟优化器执行sql查询语句,从而知道mysql是如何处理你的sql语句的。可以帮助分析查询语句或表结构的性能结果。

通过explain查看,发现没有使用索引查询,而是全表扫描。

优化:在stuname列上创建索引

Mysql > create index index_stuname ontest1.tb1(stuname);

再次执行explain

发现查询语句使用了index_stuname索引查询而非全表查询。

三、Profiling分析查询

如果觉得explain的信息不够详细,可以通过profiling命令得到更准确的sql执行消耗系统资源的信息

查看profiling是否开启

打开profiling功能:mysql >set profiling=1;并查看是否开启

执行测试语句:

四、配置优化

下面列出了对性能优化影响较大的主要变量,分为连接请求的变量和缓冲区变量

1、连接请求变量

Max_connections //mysql的最大连接数

Back_log //mysql能暂存的连接数量

Wait_timeout //mysql在关闭一个非交互的连接之前所要等待的秒数

Wait_timeout //mysql在关闭一个交互的连接之前所要等待的秒数

2、缓冲区变量

Key_buffer_size //索引缓冲区的大小

Query_cache_size //查询缓存简称QC

Max_connect_errors //阻止过多尝试失败的客户端,防止暴力破解密码

Sort_buffer_size //每个需要进行排序的线程分配该大小的缓冲区

Max_allowed_packet=32M //限制server接受的数据包大小

Join_buffer_size=2M //用于表间关联缓存的大小

Thread_cache_size=300 //可以重新利用保存在缓存中线程的数量

3、配置InnoDB的变量

Innodb_buffer_pool_size //指定大小的内存来缓冲数据和索引

Innodb_flush_log_at_trx_commit //控制inodb将log buffer总的数据写入日志文件并flush磁盘的时间点

Innodb_thread_concurrency=0 //设置innodb线程的并发数量

Innodb_log_buffer_size //确定日志文件所用的内存大小

Innodb_log_file_size=50M //确定数据日志文件的大小

Innodb_log_files_in_group=3 //以循环方式将日志文件写到多个文件

Read_buffer_size=1M //mysql读入缓冲区大小

Read_rnd_buffer_size=16M //mysql的随机读缓存区大小

Bulk_insert_buffer_size=64M //批量插入数据缓存大小

在优化之前执行mysqlslap工具测试

相关优化参数总结:

原文发布于微信公众号 - L宝宝聊IT(gh_b0e552aa80db)

原文发表时间:2018-08-14

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏张秀云的专栏

MySQL 压缩解决方案(一)

本文描述 mysql 压缩的使用场景和解决方案,包括压缩传输协议、压缩列解决方案和压缩表解决方案。

3.2K1
来自专栏LhWorld哥陪你聊算法

Mysql篇--Linux中安装Mysql

由于Windows安装Mysql非常麻烦,所以分享一篇Linux中对MySQL的搭建,废话不多说,来,come on.

3662
来自专栏乐沙弥的世界

记一次SQL server 2005 到SQL server 2008 数据库迁移

    最近周末完成了一个从SQL server 2005升级到SQL server 2008的升级迁移,其实质是一个服务器迁移的过程。比较复杂的是这个服务器上...

1784
来自专栏运维技术迷

MySQL数据库(六):体系结构和存储引擎

一、mysql 体系结构 连接池:内存/cpu/进程数 管理工具:提供mysql数据库服务的软件自带的命令 sql接口:传递sql命令给mysqld进程 ...

3448
来自专栏杨建荣的学习笔记

一个60亿数据表改分区表+数据清理的改进思路(r7笔记第19天)

今天有个同学问我一个问题,也是一个实际的案例,我简单分析了一下,发现还是有很多可以考究的地方。仅做参考。 问题是,系统里目前有一个大表,因为历史数据的沉淀,目前...

3679
来自专栏杨建荣的学习笔记

关于视图和存储过程的权限问题探究 (r9笔记第87天)

今天在处理一个工单的时候发现了一个奇怪的现象,开发同学需要创建一个存储过程,目前的架构类似这样的形式 ? 数据库中存在一个属主用户,表,存储过程等对象...

36010
来自专栏乐沙弥的世界

Percona XtraDB Cluster Strict Mode(PXC 5.7)

在Percona XtraDB Cluster集群架构中,为了避免多主节点导致的数据异常,或者说一些不被支持的特性引发的数据不一致的情形,PXC集群可以通过配置...

1532
来自专栏大眼瞪小眼

Mysql语句的执行过程

当你希望MySQL能够以更高的性能运行查询时,最好的办法是弄清楚MySQL是如何优化和执行查询。《高性能MySQL》

962
来自专栏逸鹏说道

zabbix最新SQL注入漏洞+EXP

最近zabbix又出大事了,高危的SQL注入漏洞,影响V3.0.4以下所有版本,请小伙伴及时修复。 漏洞概述: zabbix是一个开源的企业级性能监控解决方案。...

3968
来自专栏北京马哥教育

MySQL 数据库上线后根据 status 状态优化

马哥linux运维 | 最专业的linux培训机构 ---- 网上有很多的文章教怎么配置mysql服务器,但考虑到服务器硬件配置的不同,具体应用的差别,那些文...

3246

扫码关注云+社区

领取腾讯云代金券