后端接口性能优化分析-数据库优化(上)

简介: 后端接口性能优化分析-数据库优化

常见思路


15.数据库优化


索引


提到接口优化,相信最直接的就是会想到添加索引。没错,添加索引就是成本最小的优化,而且一般优化效果都很不错。


索引优化这块的话,一般从这几个维度去思考:


  • 你的SQL加索引了没?
  • 你的索引是否真的生效?
  • 你的索引建立是否合理?


SQL没加索引


我们开发的时候,容易疏忽而忘记给SQL添加索引。所以我们在写完SQL的时候,就顺手查看一下 explain执行计划。

explain select * from user_info where userId like '%123';

你也可以通过命令show create table ,整张表的索引情况。

show create table user_info;

如果某个表忘记添加某个索引,可以通过alter table add index命令添加索引

alter table user_info add index idx_name (name);

一般就是:SQLwhere条件的字段,或者是order by 、group by后面的字段需需要添加索引。


索引不生效


查看索引使用情况


在sql前面加上explain关键字,就能够看到它的执行计划,通过执行计划,我们可以清楚的看到表和索引执行的情况,索引有没有执行、索引执行顺序和索引的类型等。


explain详解


用一条简单的sql看看使用explain关键字的效果:

explain select * from test1;

id列


该列的值是select查询中的序号,比如:1、2、3、4等,它决定了表的执行顺序。


某条sql的执行计划中一般会出现三种情况:


  • id相同
  • id不同
  • id相同和不同都有 那么这三种情况表的执行顺序是怎么样的呢?


id相同:


执行sql如下:

explain select * from test1 t1 inner join test1 t2 on t1.id=t2.id

结果:

图中我们看到执行结果中的两条数据id都是1,是相同的。


这种情况表的执行顺序是怎么样的呢?


答案:从上到下执行,先执行表t1,再执行表t2。


id不同:


执行sql如下:

explain select * from test1 t1 where t1.id = (select id from  test1 t2 where  t2.id=2);

结果:

我们看到执行结果中两条数据的id不同,第一条数据是1,第二条数据是2。


这种情况表的执行顺序是怎么样的呢?


答案:序号大的先执行,这里会从下到上执行,先执行表t2,再执行表t1。


id相同和不同都有:


执行sql如下:

explain
select t1.* from test1 t1
inner join (select max(id) mid from test1 group by id) t2
on t1.id=t2.mid

结果:

我们看到执行结果中三条数据,前面两条数据的的id相同,第三条数据的id跟前面的不同。


这种情况表的执行顺序又是怎么样的呢?


答案:先执行序号大的,先从下而上执行。遇到序号相同时,再从上而下执行。所以这个列子中表的顺序顺序是:test1、t1


select_type列


常用的其实就是下面几个:

下面看看这些SELECT类型具体是怎么出现的:


SIMPLE


执行sql如下:

explain select * from test1;

结果:

它只在简单SELECT查询中出现,不包含子查询和UNION,这种类型比较直观就不多说了。


PRIMARY 和 SUBQUERY

explain select * from test1 t1 where t1.id = (select id from  test1 t2 where  t2.id=2);

结果:

我们看到这条嵌套查询的sql中,最外层的t1表是PRIMARY类型,而最里面的子查询t2表是SUBQUERY类型。


DERIVED


执行sql如下:

explain
select t1.* from test1 t1
inner join (select max(id) mid from test1 group by id) t2
on t1.id=t2.mid

结果:

最后一条记录就是衍生表,它一般是FROM列表中包含的子查询,这里是sql中的分组子查询。


UNION 和 UNION RESULT


执行sql如下:

explain
select * from test1
union
select* from test2

结果:

test2表是UNION关键字之后的查询,所以被标记为UNION,test1是最主要的表,被标记为PRIMARY。而表示id=1和id=2的表union,其结果被标记为UNION RESULT。


UNION 和 UNION RESULT一般会成对出现。


此外,回答上面的问题:id列的值允许为空吗?


如果仔细看上面那张图,会发现id列是可以允许为空的,并且是在SELECT类型为: UNION RESULT的时候。


table列


该列的值表示输出行所引用的表的名称,比如前面的:test1、test2等。


但也可以是以下值之一:


:具有和id值的行的M并集N。 :用于与该行的派生表结果id的值N。派生表可能来自(例如)FROM子句中的子查询 。 :子查询的结果,其id值为N


partitions列


该列的值表示查询将从中匹配记录的分区


type列


该列的值表示连接类型,是查看索引执行情况的一个重要指标。


执行结果从最好到最坏的的顺序是从上到下。


我们需要重点掌握的是下面几种类型:

system > const > eq_ref > ref > range > index > ALL


在演示之前,先说明一下test2表中只有一条数据:

并且code字段上面建了一个普通索引:

下面逐一看看常见的几个连接类型是怎么出现的:


system


这种类型要求数据库表中只有一条数据,是const类型的一个特例,一般情况下是不会出现的。


const


通过一次索引就能找到数据,一般用于主键或唯一索引作为条件的查询sql中,执行sql如下:

explain select * from test2 where id=1;

结果:

eq_ref


常用于主键或唯一索引扫描。执行sql如下:

explain select * from test2 t1 inner join test2 t2 on t1.id=t2.id;

结果:

此时,有人可能感到不解,const和eq_ref都是对主键或唯一索引的扫描,有什么区别?

答:const只索引一次,而eq_ref主键和主键匹配,由于表中有多条数据,一般情况下要索引多次,才能全部匹配上。


ref


常用于非主键和唯一索引扫描。执行sql如下:

explain select * from test2 where code = '001';

结果:

range


常用于范围查询,比如:between … and 或 In 等操作,执行sql如下:

explain select * from test2 where id between 1 and 2;

结果:

index


全索引扫描。执行sql如下:

explain select code from test2;

结果:

ALL


全表扫描。执行sql如下:

explain select *  from test2;

结果:


possible_keys列

explain select * from test1 t1 where t1.id = (select id from  test1 t2 where  t2.id=2);

该列表示可能的索引选择。


key列


该列表示实际用到的索引。


可能会出现possible_keys列为NULL,但是key不为NULL的情况。


演示之前,先看看test1表结构:

test1表中数据:

使用的索引:

code和name字段使用了联合索引。

explain select code  from test1;

结果:

这条sql预计没有使用索引,但是实际上使用了全索引扫描方式的索引。


key_len列


该列表示使用索引的长度。上面的key列可以看出有没有使用索引,key_len列则可以更进一步看出索引使用是否充分。不出意外的话,它是最重要的列。

有个关键的问题浮出水面:key_len是如何计算的?


决定key_len值的三个因素:


  1. 字符集
  2. 长度
  3. 是否为空


常用的字符编码占用字节数量如下:

目前我的数据库字符编码格式用的:UTF8占3个字节。


mysql常用字段占用字节数:

此外,如果字段类型允许为空则加1个字节。


上图中的 184是怎么算的?


184 = 30 * 3 + 2 + 30 * 3 + 2


再把test1表的code字段类型改成char,并且改成允许为空:

执行sql如下:

explain select code  from test1;

结果:

怎么算的? 183 = 30 * 3 + 1 + 30 * 3 + 2


还有一个问题:为什么这列表示索引使用是否充分呢,还有使用不充分的情况?


执行sql如下:

explain select code  from test1 where code='001';

结果:

上图中使用了联合索引:idx_code_name,如果索引全匹配key_len应该是183,但实际上却是92,这就说明没有使用所有的索引,索引使用不充分。


当"EXPLAIN"语句中的"key_len"显示使用索引不充分时,意味着查询在执行时没有充分利用表的索引,可能会导致以下后果:


  • 查询性能下降:由于没有充分利用索引,查询执行时需要扫描更多的数据行,因此查询的性能可能会降低。(查询性能下降:索引的主要作用是加速查询操作,当查询没有充分利用索引时,数据库需要扫描更多的数据行来获取所需的数据。这将导致查询的执行时间增加,性能下降。对于复杂的查询或者大数据量的表来说,性能下降可能会非常明显。在极端情况下,如果查询始终无法充分利用索引,可能会导致系统陷入繁忙状态,无法及时响应其他查询和事务。)
  • 系统资源浪费:当查询没有充分利用索引时,可能会导致更多的系统资源被占用,如CPU、内存和磁盘I/O等,从而影响其他查询和事务的执行。(系统资源浪费:当查询没有充分利用索引时,数据库可能需要使用更多的系统资源来完成查询操作。这可能导致CPU、内存和磁盘I/O等资源的使用率上升,从而影响其他查询和事务的执行。例如,如果CPU使用率过高,可能会导致其他查询和事务的执行被阻塞,从而降低系统的并发处理能力。同样,如果内存使用率过高,可能会引发内存泄漏或者OOM(内存溢出)等异常。)
  • 增加数据库负载:如果长时间存在索引不充分的情况,可能会导致数据库的负载增加,进一步影响数据库的稳定性和可用性。(增加数据库负载:如果长时间存在索引不充分的情况,可能会导致数据库的负载增加。在高负载的情况下,数据库可能需要处理更多的查询和事务,从而影响数据库的稳定性和可用性。此外,高负载还可能导致数据库的响应时间变长,进一步影响用户体验和业务处理速度。在极端情况下,过高的负载可能会导致数据库崩溃,从而导致数据丢失和系统崩溃。)
  • 影响业务处理:由于查询性能下降,可能会导致业务处理速度变慢,影响用户体验,甚至可能导致业务系统崩溃。因此,当"EXPLAIN"语句中的"key_len"显示使用索引不充分时,应尽快采取措施优化查询和索引,以提高查询性能、降低系统资源消耗,并保障数据库的稳定性和可用性。(影响业务处理:由于查询性能下降,可能会导致业务处理速度变慢,影响用户体验。例如,在电子商务网站上,如果商品搜索查询的性能下降,可能会导致用户无法快速找到所需的商品,从而降低购物体验。在某些情况下,如果查询性能下降严重,可能会导致业务系统崩溃,从而导致数据丢失和系统崩溃。因此,对于关键业务系统来说,确保查询充分利用索引非常重要。)


ref列


该列表示索引命中的列或者常量。


执行sql如下:

explain select *  from test1 t1 inner join test1 t2 on t1.id=t2.id where t1.code='001';

结果:

我们看到表t1命中的索引是const(常量),而t2命中的索引是列sue库的t1表的id字段。


rows列


该列表示MySQL认为执行查询必须检查的行数。


对于InnoDB表,此数字是估计值,可能并不总是准确的。


Extra列


该字段包含有关MySQL如何解析查询的其他信息,这列还是挺重要的,但是里面包含的值太多,就不一一介绍了,只列举几个常见的。


Impossible WHERE


表示WHERE后面的条件一直都是false

explain select code  from test1 where 'a' = 'b';

Using filesort


表示按文件排序,一般是在指定的排序和索引排序不一致的情况才会出现。

explain select code  from test1 order by name desc; 

这里建立的是code和name的联合索引,顺序是code在前,name在后,这里直接按name降序,跟之前联合索引的顺序不一样。


Using index


表示是否用了覆盖索引,说白了它表示是否所有获取的列都走了索引。


Using temporary


表示是否使用了临时表,一般多见于order by 和 group by语句。

explain select name  from test1 group by name;

Using where


表示使用了where条件过滤。


索引优化的过程


先用慢查询日志定位具体需要优化的sql


使用explain执行计划查看索引使用情况


重点关注:


  • key(查看有没有使用索引)
  • key_len(查看索引使用是否充分)
  • type(查看索引类型)
  • Extra(查看附加信息:排序、临时表、where条件为false等)


一般情况下根据这4列就能找到索引问题。


以一个慢sql工单举例,大概是这样的 “select xxx from tabel where type = 1”。


咦,type字段明明有索引啊,为啥是慢sql呢?


通过执行explain,发现实际上数据库执行了全表扫描,从而被系统判定为慢sql。所以这就要分析有索引的情况,索引为什么失效了。


不满足最左匹配


假如表中有个组合索引,idx_start_org_code_start_province_id_trans_type,它的索引顺序如下:


start_org_code,

start_province_id,

trans_type


当我们从第二个索引字段开始查询时就不会走索引:

因为不满足索引的 最左匹配原则,该原则只适用于 1 12 123 这种查询情况。


后端接口性能优化分析-数据库优化(中):https://developer.aliyun.com/article/1413678

目录
打赏
0
0
0
0
8
分享
相关文章
后端开发中的性能优化策略
本文将探讨几种常见的后端性能优化策略,包括代码层面的优化、数据库查询优化、缓存机制的应用以及负载均衡的实现。通过这些方法,开发者可以显著提升系统的响应速度和处理能力,从而提供更好的用户体验。
94 6
Vue.js应用结合Redis数据库:实践与优化
将Vue.js应用与Redis结合,可以实现高效的数据管理和快速响应的用户体验。通过合理的实践步骤和优化策略,可以充分发挥两者的优势,提高应用的性能和可靠性。希望本文能为您在实际开发中提供有价值的参考。
56 11
Java后端开发-使用springboot进行Mybatis连接数据库步骤
本文介绍了使用Java和IDEA进行数据库操作的详细步骤,涵盖从数据库准备到测试类编写及运行的全过程。主要内容包括: 1. **数据库准备**:创建数据库和表。 2. **查询数据库**:验证数据库是否可用。 3. **IDEA代码配置**:构建实体类并配置数据库连接。 4. **测试类编写**:编写并运行测试类以确保一切正常。
57 2
记录一次后端接口抖动的排查过程
某天下午,测试新功能时发现页面接口偶尔变慢,平均十次调用中有三到四次出现3秒以上的延迟。排查了接口、数据库和服务器资源后未发现问题,最终锁定为K8s节点的负载均衡策略导致。测试环境仅有一个公网IP,SLB轮询四个Pod(其中三个为空),造成周期性延迟。移除空Pod后问题解决。
54 5
使用访问指导(SQL Access Advisor)优化数据库业务负载
本文介绍了Oracle的SQL访问指导(SQL Access Advisor)的应用场景及其使用方法。访问指导通过分析给定的工作负载,提供索引、物化视图和分区等方面的优化建议,帮助DBA提升数据库性能。具体步骤包括创建访问指导任务、创建工作负载、连接工作负载至访问指导、设置任务参数、运行访问指导、查看和应用优化建议。访问指导不仅针对单条SQL语句,还能综合考虑多条SQL语句的优化效果,为DBA提供全面的决策支持。
98 11
【深入了解MySQL】优化查询性能与数据库设计的深度总结
本文详细介绍了MySQL查询优化和数据库设计技巧,涵盖基础优化、高级技巧及性能监控。
268 0
婚恋交友系统平台 相亲交友平台系统 婚恋交友系统APP 婚恋系统源码 婚恋交友平台开发流程 婚恋交友系统架构设计 婚恋交友系统前端/后端开发 婚恋交友系统匹配推荐算法优化
婚恋交友系统平台通过线上互动帮助单身男女找到合适伴侣,提供用户注册、个人资料填写、匹配推荐、实时聊天、社区互动等功能。开发流程包括需求分析、技术选型、系统架构设计、功能实现、测试优化和上线运维。匹配推荐算法优化是核心,通过用户行为数据分析和机器学习提高匹配准确性。
180 3
【AI系统】AI 编译器后端优化
AI编译器采用多层架构,首先通过前端优化将不同框架的模型转化为统一的Graph IR并进行计算图级别的优化,如图算融合、内存优化等。接着,通过后端优化,将优化后的计算图转换为TensorIR,针对单个算子进行具体实现优化,包括循环优化、算子融合等,以适应不同的硬件架构,最终生成高效执行的机器代码。后端优化是提升算子性能的关键步骤,涉及复杂的优化策略和技术。
78 3
后端性能优化:从理论到实践
在数字化时代,后端服务的性能直接影响着用户体验和业务效率。本文将深入探讨后端性能优化的重要性,分析常见的性能瓶颈,并提出一系列切实可行的优化策略。我们将从代码层面、数据库管理、缓存机制以及系统架构设计等多个维度出发,结合具体案例,详细阐述如何通过技术手段提升后端服务的响应速度和处理能力。此外,文章还将介绍一些先进的监控工具和方法,帮助开发者及时发现并解决性能问题。无论是初创公司还是大型企业,本文提供的策略都有助于构建更加高效、稳定的后端服务体系。
94 3
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句
gbase 8a 数据库 SQL合并类优化——不同数据统计周期合并为一条SQL语句

热门文章

最新文章

AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等