专栏首页L宝宝聊ITMysql性能优化——慢查询分析

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)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

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

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL架构组成、物理文件组成

    MySQL经过多年的改进和完善之后,已经基本具备了所有通用数据库管理系统所需要的相关功能。

    L宝宝聊IT
  • Linux基础——rsyslog日志管理

    L宝宝聊IT
  • Mysql备份与还原——mysqldump结合binlog

    MySQL 备份一般采取全库备份加日志备份的方式,例如每天执行一次全备份,每小时执行一次二进制日志备份。这样在 MySQL 故障后可以使用全备份...

    L宝宝聊IT
  • 微信小程序“实时日志”帮你快速找到bug

    为帮助小程序开发者快捷地排查小程序漏洞、定位问题,微信官方推出了实时日志功能。从基础库2.7.1开始,开发者可通过提供的接口打印日志,日志汇聚并实时上报到小程序...

    极乐君
  • Linux中配置sudo免密钥

    今天周五,又是一个周中最美好的时候,因为明天不用上班啊,可以干自己想干的事情,想想就激动的不行。

    AsiaYe
  • win10 uwp 切换主题

    一般我们的应用都要有多种颜色,一种是正常的白天颜色,一种是晚上的黑夜颜色,还需要一种辅助的高对比颜色。这是微软建议的,一般应用都要包含的颜色。

    林德熙
  • win10 uwp 切换主题

    一般我们的应用都要有多种颜色,一种是正常的白天颜色,一种是晚上的黑夜颜色,还需要一种辅助的高对比颜色。这是微软建议的,一般应用都要包含的颜色。

    林德熙
  • Excel VBA解读(141): 自定义函数性能改进示例

    Pedro想知道怎样加速他的自定义函数,该函数需要计算35040个单元格的结果,即单元格与未知长度值列之间的最小差异。

    fanjy
  • Fiori launchpad里Enter Group name这个tile是怎么配置出来的

    版权声明:本文为博主汪子熙原创文章,未经博主允许不得转载。

    Jerry Wang
  • 高性能IO模型浅析

    服务器端编程经常需要构造高性能的IO模型,常见的IO模型有四种: (1)同步阻塞IO(Blocking IO):即传统的IO模型。 (2)同步非阻塞IO(Non...

    李海彬

扫码关注云+社区

领取腾讯云代金券