我们已经准备好了,你呢?

2024我们与您携手共赢,为您的企业形象保驾护航!

前言

在应用系统性能测试过程中,性能优化是一个无法回避的话题。对于测试人员来说,性能优化的第一步就是SQL语句的优化分析。因此本文主要以MySQL数据库为例,介绍常见慢查询SQL语句执行效率分析优化方法及简单实例,为致力于应用系统性能优化的从业者提供一些参考和借鉴。

mysqlsql优化工具_mysql语句优化的几种方法_mysql sql语句优化工具

1. 慢查询定位

(1)慢查询

慢查询SQL语句是数据库中执行时间超过一定阈值的SQL语句,常见阈值为500~,可根据业务需求适当调整。如果存在大量慢查询语句,会直接导致系统响应时间变长,降低用户体验。因此,慢查询的定位和优化是SQL语句优化的主要内容。

慢查询调优的第一步就是准确定位慢查询语句,数据库需要开启慢查询日志功能,然后通过工具分析日志准确定位慢查询SQL语句。

--慢查询启用状态,日志位置

显示类似`%`;

--慢查询命中时长

显示类似``;

(2)慢查询日志分析

MySql数据库的慢查询SQL语句可以借助工具进行分析;其他类型的数据库可以按照官方技术文档使用相应的工具进行慢查询日志分析。

慢查询日志分析的常用参数说明如下:

mysql语句优化的几种方法_mysql sql语句优化工具_mysqlsql优化工具

例:耗时最长、最慢的10条SQL语句(后面部分是地址)

sql -s tt 10 -g''/data/mysql/data/dcbi-3306/log/slow.log

2 SQL语句执行分析

(1)SQL执行顺序

分析SQL语句执行效率的第一步就是要了解一条SQL语句的执行顺序,从而为语句优化提供依据。一般来说,执行顺序为:

from->where->group by->聚合函数(sum,avg)->->计算公式->字段->order by->limit

(2)关键词

SQL语句执行分析可以在SQL语句前添加“”关键字,然后在数据库编辑器中执行,查看该语句具体的执行情况。

*来自其中 = 和 =

(3)SQL执行计划返回结果说明

返回结果中各列说明:您可以根据需要查询相关信息,重点关注体现查询效率的[type]、[ref]、[extra]三列,其中以[type]为主。

(4)SQL执行效率分析

该语句根据[type]列的值来判断SQL执行效率,效率从低到高依次为:

全部

mysql语句优化的几种方法_mysqlsql优化工具_mysql sql语句优化工具

(5)SQL语句执行效率对比

在进行SQL优化,通过对比两条SQL语句的执行时间来验证优化效果时,需要明确语句执行过程中的数据访问方式。根据数据库数据查询机制,如果目标数据已经存在于数据库内存中,则直接从内存中获取数据,而不是从数据库物理磁盘中获取。这种情况下,执行优化前的SQL语句时,目标数据会暂存在数据库内存中;执行优化后的SQL语句时,会直接从数据库内存中获取数据,从而导致语句执行时间失真。

为了避免上述SQL语句在验证优化效果时执行时间失真,需要在关键字后添加关键字语句,通过数据库引擎重新查询数据。这意味着查询结果在内存中显示后直接从内存中释放,而不是不从内存中读取数据。因此,如果在执行前已经查询过目标数据,导致目标数据已经在数据库内存中,该语句将无效。

使用示例如下:

从其中 = 和 =;

需要注意的是,该命令在 MySQL 5.7.20 版本中已被废弃,在 MySQL 8.0 版本后已彻底移除。普通命令是直接从数据库获取数据,而不是从数据库内存中获取数据。其他类型数据库相关机制请根据需要参考相应的官方技术文档。

3.常见SQL优化方法

(1)指数覆盖范围

SQL优化最常见的做法就是实现索引覆盖,也就是where后面的查询列和where后面的查询条件都包含索引,通过查询条件就能获取到查询列的数据。

常见的索引覆盖场景:

1)如果使用了主键索引,而后续查询列不包含主键,则无法实现索引覆盖;

2)使用非主键索引,末尾的查询列都包含非主键索引,实现索引覆盖。

3)使用非主键索引。此后的查询列都包含主键索引,实现索引覆盖。

(2)最左匹配原则

MySQL 在创建联合索引(多列索引)时,会遵循最左前缀匹配原则,即最左边的列在前,检索数据时从联合索引最左边的列开始匹配。这是因为索引最左边的列是全局有序的,其余列是局部有序但全局无序的。因此查询索引时必须满足最左匹配原则,否则索引将无效。

基于最左匹配原则,创建索引时,根据业务需要将where中最常用的列放在最左边;

最左匹配原则遇到范围查询(>,1且c=3,由于b是范围查询,所以b和c的索引无效。

例2:某个表有按顺序(a,b,c)建立的索引,其中表table中字段b的所有值都是常量02003。同样的查询有四条不同的SQL语句:

--SQL语句1

*来自表格,其中 c=''

--SQL语句2

*来自表格,其中 b='02003' 和 c=''

--SQL语句3

*来自表格,其中 a='' 和 b='02003' 和 c=''

--SQL语句4

*来自表格,其中 a='' 和 c=''

执行上述SQL语句三次所需的平均时间为:

mysql sql语句优化工具_mysql语句优化的几种方法_mysqlsql优化工具

结论:SQL语句1和2不符合最左匹配原则,导致索引失效,查询时间过长。SQL语句3和4使用了索引,查询速度更快。但是SQL语句4因为缺少索引字段b,耗时比SQL语句3长。由此可见,即使索引字段b在整个表中是一个常量,但将其纳入where后的筛选条件中,依然可以提高查询效率。

(3)索引条件下推

目的:使用组合索引检索数据时,且第一个索引不是等值索引时,尽量利用其他索引条件精确选取目标数据,减少数据返回表判断是否符合目标数据的次数,解决查询慢带来的性能问题。

方法:服务层(layer)将查询工作下推到数据库引擎()进行处理。

优点:减少表查询次数,提高查询效率,减少数据库IO资源消耗。

判断:SQL输出[extra]列结果使用了索引。

下面详细对比了使用下推和不使用下推时的数据库底层逻辑,进一步说明了索引条件下推的优势。

1)使用下推

当第一个索引非等值索引的SQL语句使用索引条件时,应用层将查询请求发送给引擎层,引擎层根据索引条件去除不满足其他索引条件的数据,将剩余满足其他索引条件的数据返回给应用层,以尽可能少的表返回结果找回相应记录。

使用条件下推时,引擎层可以直接剔除非第一个索引中不满足列的数据。

2)未使用下推

当一条SQL语句有多个索引时,数据库层将查询请求发送给引擎层进行处理,引擎层按照索引的顺序将符合请求的数据返回给应用层。

数据库层完成筛选后,再按顺序发送下一个索引检索条件,并多次重复此过程,直至满足所有查询条件。

如此多次循环,导致数据库IO资源消耗较高。

mysql语句优化的几种方法_mysqlsql优化工具_mysql sql语句优化工具

(4)小表驱动大表

根据表的结果集大小来选择驱动表,一般选用较小的表作为驱动表。

例如某系统有表和表,表的数据量分别为100万和10万,当查询两表的关联数据时,将表作为子表使用:

*来自哪里 =''并且在(来自哪里......)

如果必须使用大表作为子表,请使用关键字。

*来自哪里(来自哪里……)

(5)代替或

如果where后面的查询条件中某个字段有多个值,则使用in代替or。

*来自其中=''和(=''或='');

*来自其中=''并在('','');

(6)分组避免排序

MySQL 默认对所有 group by 字段进行排序。分组会避免排序,除非必要。

,count(*)FROM t GROUP BY ORDER BY NULL;

(7)批量插入

插入多条数据记录时,尽量避免逐条插入数据,优先考虑批量数据插入(插入50条及以上数据记录)。

--批量插入数据

INTO t(id,name)(1,'Bea'),(2,'Belle'),(3,'');

--逐条插入数据

INTO t(id,name)(1,'Bea');

INTO t(id,name)(2,'Belle');

进入t(id,name)(3,'');

4 典型索引失效案例

城市表的联合索引为(ID,),非索引列为(Name,,)

(1)where索引列表达式计算

索引失败

*来自 world.city 其中 ID+1=4000;

索引并非无效

*来自 world.city 其中 ID=4001;

(2)Where索引列使用函数

索引失败

*来自 world.city 其中 (,1,2)='nl';

索引并非无效

*来自 world.city 其中类似 'nl%';*来自 world.city 其中类似 'nl_';

(3)or 条件包含非索引列

索引失败

*来自 world.city 其中 ID=4001 或 Name='Simi ';

索引并非无效

*来自 world.city 其中 ID=4001 或 ='USA';

(4)与模糊搜索类似,在单词开头使用%

索引失败

*来自 world.city 其中像'%nld%';

索引并非无效

*来自 world.city 其中像'nld%';

(5)不满足最左匹配原则

索引失败

*来自 world.city 其中 ='USA'and =;

索引并非无效

*来自 world.city 其中 ID=4001 且 ='USA'and =;

注意:有很多情况是由于最左匹配导致索引无效。有关详细信息,请参阅最左匹配部分。

(6)索引列没有设置为NOT NULL

MySQL在执行查询的时候,会判断字段是否为NOT NULL,这个过程往往需要全表扫描,因此最好给索引添加NOT NULL约束,并且设置一个默认值,以方便索引使用,加快查询效率。

5. 重点:INTO 导致的性能问题或表锁

into 会对插入的每一行数据加共享锁(S锁,其他事务只能读)做唯一键测试,同时会对主键自增ID加意向锁();

当主键比较复杂时,检查主键是否唯一时会占用主键的插入意向锁,当其他进程也想对主键ID加插入意向锁时,就会发生冲突,导致死锁。

另外,代码中的“into”也需要特别注意。

总结

SQL语句优化分析是性能测试分析从业人员开展性能优化的第一步,也是性能优化的一项基本技能,对提升系统性能有着重要的作用和意义。在掌握性能优化基本技能的基础上,还需要结合业务需求、代码逻辑访问路径,准确评估不同优化方法的适用性,综合比较不同优化方法的工作成本,采用合理、高效的优化方法开展性能优化工作。

文章最后邀请大家加入我们的软件测试学习交流群,在这里大家可以一起讨论交流软件测试,学习软件测试技术、面试等软件测试方面的知识,了解测试行业的最新动态,帮助你快速晋升到自动化测试/测试开发岗位,稳住现有职位,向更高的薪资迈进。

终于:

1)关注+回复私信:“测试”,即可免费获得万科软件测试工程师面试指南文档一份。并且对应的视频学习教程免费分享!内容包括基础知识、Linux要领、Mysql数据库、抓包工具、接口测试工具、高级测试-编程、Web自动化测试、APP自动化测试、接口自动化测试、高级持续集成测试、测试架构开发测试框架、性能测试等。

2)关注+回复私信:“进群”邀请你加入软件测试群一起学习交流~~

二维码
扫一扫在手机端查看

本文链接:https://by928.com/1746.html     转载请注明出处和本文链接!请遵守 《网站协议》
我们凭借多年的网站建设经验,坚持以“帮助中小企业实现网络营销化”为宗旨,累计为4000多家客户提供品质建站服务,得到了客户的一致好评。如果您有网站建设、网站改版、域名注册、主机空间、手机网站建设、网站备案等方面的需求,请立即点击咨询我们或拨打咨询热线: 13761152229,我们会详细为你一一解答你心中的疑难。

项目经理在线

我们已经准备好了,你呢?

2020我们与您携手共赢,为您的企业形象保驾护航!

在线客服
联系方式

热线电话

13761152229

上班时间

周一到周五

公司电话

二维码
微信
线