性能测试之mysql数据库如何调优?_普通网友的博客-程序员秘密_mysql如何调优

技术标签: 经验分享  面试  python  自动化测试  软件测试  单元测试  程序人生  Python  

 一、Mysql性能指标及问题分析和定位

1、我们在监控图表中关注的性能指标大概有这么几个:CPU、内存、连接数、io读写时间、io操作时间、慢查询、系统平均负载以及memoryOver

2、介绍下Grafana模板中各性能指标的意思

这个是Mysql数据库的连接数

这个图标表示了慢查询

上图就是Mysql数据库的缓存区,展示了最大缓存以及已使用缓存等数据

3、性能分析

一般在产生Mysql瓶颈的时候往往伴随着的是CPU使用率急速上升,需要top看一下是哪个线程占据了大量的CPU资源,如果发现Mysql进程占用较高,那么基本可以判断是Mysql数据库出现了问题。

接下来就是对问题具体的分析和定位。

对于数据库的操作基本上就是大量的查询,会导致数据库出现性能问题。对有问题的场景使用Jmeter模拟场景进行并发,并观察Grafana的图表。

Mysql的几个问题基本上就是:

(1)缓存区较小,大量查询导致了缓存区溢出,使用io进行读写,众所周知,io的读写速度远远比内存读写速度要慢得多。

(2)sql语句问题,导致mysql数据库出现瓶颈的查询语句类型很多,最后会给大家列举一些。

那么怎么定位到这些问题呢?

(1)在负载测试中,通过Grafana图表观察Memory Over这个图表,如果发现占用基本占满所分配给Mysql数据库缓存区的内存,然后IO读写时间非常长,读写频率非常高,那基本上是可以判断是缓存区较小导致的问题。(这个问题已经很少出现了)

(2)判断慢查询:在mysql数据库的配置文件中找到

重启Mysql数据库

在Grafana图表中如果看到慢查询的时间超过1s时,基本判断为存在慢查询。

登入数据库运行命令

select * from mysql.slow_log;#查看慢查询表数据

运行完这条命令后,可以查看到所有超过1s的查询语句,这个时候复制这条语句到查询输入框中,选中右键点击解释。

type列,连接类型。一个好的sql语句至少要达到range级别。杜绝出现all级别
key列,使用到的索引名。如果没有选择索引,值是NULL。可以采取强制索引方式

key_len列,索引长度
rows列,扫描行数。该值是个预估值
extra列,详细说明。注意常见的不太友好的值有:Using filesort, Using temporary

 二、sql语句调优

1、SQL语句中IN包含的值不应过多

MySQL对于IN做了相应的优化,即将IN中的常量全部存储在一个数组里面,而且这个数组是排好序的。但是如果数值较多,产生的消耗也是比较大的。

再例如:select id from t where num in(1,2,3) 对于连续的数值,能用 between 就不要用 in 了;再或者使用连接来替换。

2、SELECT语句务必指明字段名称

SELECT *增加很多不必要的消耗(cpu、io、内存、网络带宽);增加了使用覆盖索引的可能性;

当表结构发生改变时,前断也需要更新。所以要求直接在select后面接上字段名。

3、当只需要一条数据的时候,使用limit 1。这是为了使EXPLAIN中type列达到const类型

4、如果排序字段没有用到索引,就尽量少排序

5、如果限制条件中其他字段没有索引,尽量少用or,or两边的字段中,如果有一个不是索引字段,而其他条件也不是索引字段,会造成该查询不走索引的情况。

很多时候使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果

6、区分in和exists, not in和not exists

select * from 表A where id in (select id from 表B)
上面sql语句相当于select * from 表A where exists(select * from 表B where 表B.id=表A.id)
区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询。所以IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。

关于not in和not exists,推荐使用not exists,不仅仅是效率问题,not in可能存在逻辑问题。如何高效的写出一个替代not exists的sql语句?

原sql语句

select colname … from A表 where a.id not in (select b.id from B表)

高效的sql语句
select colname … from A表 Left join B表 on where a.id = b.id where b.id is null

7、分段查询

在一些用户选择页面中,可能一些用户选择的时间范围过大,造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序,分段进行查询,循环遍历,将结果合并处理进行展示

8、避免在 where 子句中对字段进行 null 值判断

对于null的判断会导致引擎放弃使用索引而进行全表扫描。

9、不建议使用%前缀模糊查询

例如LIKE “%name”或者LIKE “%name%”,这种查询会导致索引失效而进行全表扫描。但是可以使用LIKE “name%”。

最后附上一些SQL优化建议:

1、SQL语句不要写的太复杂。一个SQL语句要尽量简单,不要嵌套太多层。

2、使用like的时候要注意是否会导致全表扫

3、尽量避免使用!=或<>操作符,在where语句中使用!=或<>,引擎将放弃使用索引而进行全表扫描。

4、尽量避免使用 or 来连接条件,在 where 子句中使用 or 来连接条件,引擎将放弃使用索引而进行全表扫描。

5、尽量避免使用in和not in,在 where 子句中使用 in和not in,引擎将放弃使用索引而进行全表扫描。

6、尽量避免使用表达式、函数等操作作为查询条件

7、尽量避免大事务操作,提高系统并发能力。

8、任何地方都不要使用 select * from t ,用具体的字段列表代替“*”,不要返回用不到的任何字段。

9、尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。

10、索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率

11、并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引

感谢每一个认真阅读我文章的人,看着粉丝一路的上涨和关注,礼尚往来总是要有的,如果下面这些资料你用得到的话可以直接拿走:群号 798478386 ( 备注CSDN555 )  

版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/sadmin___/article/details/123928288

智能推荐

169v 条目不存在_铜催化的频哪醇硼烷对1,3烯炔的不对称硼氢化反应获得手性烯丙基硼酸酯..._weixin_39914752的博客-程序员秘密

作者报告了由Cu(OAc)和(S,S)-Ph-BPE生成的催化剂对1,3-炔烃的铜催化对映选择性硼氢化。各种各样的烷基和芳基取代的1,3-烯炔与频哪醇硼烷进行这种不对称硼氢化反应,以高收率和高至优异的对映选择性(高达99%ee)产生相应的烯丙基。这种不对称转变可耐受各种反应性基团,例如氯,溴,三氟甲基醚,甲硅烷氧基,羧酸酯和亚氨基官能团。有机硼酸酯化合物因其无毒,高稳定性和高官能团相容性...

conda环境 python包手动安装_如何在conda中从一个文件安装多个包而不创建新环境?..._低僧的博客-程序员秘密

我从yml文件包中提取了这些文件,这些文件包是我当前环境丢失的。如何在当前环境中安装这些程序?在channels:- defaultsdependencies:- appdirs=1.4.3=py36h28b3542_0- asn1crypto=0.24.0=py36_0- attrs=18.2.0=py36h28b3542_0- blas=1.0=mkl- cffi=1.11.5=py36h61...

oracle时间类型转换的一点个人技巧_驴哥爱冰月的博客-程序员秘密

1、存入的数据是date类型(2010/10/10 10:10:10)转换成14位VARCHAR2 TO_CHAR(TIME,'YYYYMMDDHH24MISS')2、存入的数据是VARCHAR2类型(20101010101010)转换成date类型就TO_DATE(TIME,'YYYY/MM/DD HH24:MI:SS')3、字符型转成timestamp代码如下: to_tim...

Guava之controller中使用缓存cache_weixin_34205076的博客-程序员秘密

为什么80%的码农都做不了架构师?&gt;&gt;&gt; ...

现代软件工程 第十三章 【软件测试】 练习与讨论_weixin_34018202的博客-程序员秘密

13.5.2  有错不改果冻: 微软的产品经过这么多版本的不断完善,应该是把所有问题都搞定,“止于至善”了吧?阿超: 那也不一定,在非常有名的电子表格软件Excel中,就有这样一个Bug:Excel 的日期计算功能认为1900年是一个闰年,这是不对的,但是它愣是一直没有改正这个错误。众人: 真的?为什么屡教不改呢?阿超: 故事是这样的,当时这类电子表格软件的市场领头羊是Lotus ...

UE5引擎开发软件功能记录-吸附功能的实现20220412_Rock01v的博客-程序员秘密

UE5开发软件功能实现记录-吸附功能的初级实现20220412

随便推点

原生JS模拟Bootstrap中的折叠(Collapse)插件_weixin_34242509的博客-程序员秘密

以前实习的时候因为赶时间直接用bootstrap的插件collapse.js做了个折叠菜单, 对于一个原生控来说还是更倾向于自己写一个, 毕竟为了个折叠菜单引入jq和bootstrap有点太臃肿了。 于是又到了考验山寨能力的时候了-_-# 。原版collapse.js的效果其实也不难,主要是在开合的过程中添加了css3的过渡效果。以下是原...

强化学习之Q-Learning(附代码)_鬼道2022的博客-程序员秘密_qlearning代码

QQQ-Learning\mathrm{Learning}Learning原理介绍  QQQ-Learning\mathrm{Learning}Learning是强化学习的算法之一,Q\mathrm{Q}Q-Learning\mathrm{Learning}Learning的主要目的就是学习状态动作价值函数的Q(s,a)Q(s,a)Q(s,a),其中Q(s,a)Q(s,a)Q(s,a)表示的是在给定当前状态sss和采取动作aaa之后能够获得的收益期望。QQQ-Learning\mathrm{Learnin

Windows定时器编程_weixin_30608503的博客-程序员秘密

一般时控函数   vc程序员都会利用windows的wm—timer消息映射来进行简单的时间控制:1.调用函数settimer()设置定时间隔,如settimer(0,200,null)即为设置200毫秒的时间间隔;2.在应用程序中增加定时响应函数ontimer(),并在该函数中添加响应的处理语句,用来完成时间到时的操作。这种定时方法是非常简单的,但其定时功能如同sleep()...

VS2005 VC++ 代码优化问题_nooning的博客-程序员秘密

今天应该是个不错的日子,股市大跌,然之我的代码也与我有了分歧。X->y=GetProcAddress(GetModuleHandle(_T("Kernel32")), "LoadLibraryA");DEBUG Version :[没问题]10001AE8  push        offset __pDefaultRawDllMain+0C4h (10003218h) 10001AED  p

java中重写、重载、多态几个概念区别分析_萌萌的小学生的博客-程序员秘密

最近发现这几个概念有点混淆不清了。特意小结了一下,来帮助理清思路。        重写也称作覆盖,是指在继承关系中,子类并不想完全继承父类方法,而是想做自己的一些修改,可以让子类重写父类方法,使该方法和父类具有相同的名称和参数,而方法体可以和父类不同,重写是父类和子类多态性的体现。要注意的是子类函数的访问修饰权限不能少于父类的。        class Animal{ public

树莓派科学小实验4B--08_倾斜传感器、干簧管、震动传感器_掉光头发的土豆的博客-程序员秘密_树莓派小实验

系列文章目录树莓派科学小实验001 点亮第一盏LED灯002 点亮LED灯组003_开关控制LED灯004_获取温湿度005_跑马灯(ws2812b))006_数字温度传感器(PCF8591,LM393)007_继电器008_倾斜传感器、干簧管、震动传感器文章目录系列文章目录前言一、实验元件二、代码1.引入库2. 管脚定义3. 实现代码前言 倾斜传感器、干簧管、震动传感器 这一类型传感器是一样的数字传感器,可以直接接在树莓派的GPIO端口上来获取高低电平值。提示:以下是本

推荐文章

热门文章

相关标签