活动中台体系慢 SQL 管理实践

作者：vivo 互联网服务器团队- Zhang Mengtao

 
活动中台体系作为中台项目非常注重体系性能和用户体验，数据库体系性能问题会对应用程序的性能和用户体验产生负面影响。慢查询可能导致应用程序响应变慢、请求堆积、体系负载增加等问题，甚至引发体系崩溃或不可用的环境，因此，必要在数据库体系中针对实验迟钝的SQL查询举行优化和改进。本文重要先容活动中台体系针对慢SQL问题的实践管理案例。
 
一、慢 SQL 的寄义

1.1 慢 SQL 的寄义

慢SQL是指实验时间较长的SQL查询或操纵。真实的慢 SQL 通常会陪同着大量的行扫描、临时文件排序大概频繁的磁盘 flush ，直接影响就是磁盘 IO 升高，让正常的 SQL 变成了慢 SQL ，大面积实验超时。
 
大家不要被慢查询这个名字误导，以为慢查询日志只会记录 select 语句，着实也会记录实验时间超过了long_query_time设定的阈值的 insert、update 等 DML 语句。
 
1.2 慢 SQL 的危害

从业务的角度来看：慢 SQL 会导致产品用户体验差，会减低用户对产品的好感度。
 
从数据库的角度来看：慢 SQL 会影响数据库的性能，每个 SQL 实验都必要消耗一定的 I/O 资源。假设总资源是100，有一条慢 SQL 占用了30的资源共计1分钟。那么在这1分钟时间内，其他 SQL 能够分配的资源总量就是70，如此循环，当资源分配完的时间，全部新的 SQL 实验将会排队等待。
 
二、慢SQL是怎么产生的？

【缺乏索引】：如果在查询中涉及到的列没有适当的索引，数据库体系可能必要实验全表扫描来找到匹配的行，从而导致查询变慢。
 
【查询条件不当】：查询条件过于复杂、使用了不必要的 JOIN 操纵、存在子查询等，都可能导致查询性能下降。
 
【数据量过大】：当数据表中的数据量非常庞大时，即使有索引，查询也可能变得迟钝。
 
【锁等待】：如果查询必要访问被其他变乱锁定的资源，就会导致查询阻塞，实验时间变长。
 
【硬件资源不足】：数据库服务器的硬件资源（如 CPU、内存、磁盘）不足以支撑查询的实验，也会导致查询变慢。
 
【不符合的数据库设计】：数据库表的设计不公道，如过分范式化、冗余数据等，会导致查询性能下降。
 
【统计信息不准确】：数据库的统计信息不准确会导致查询优化器做出错误的实验计划，影响查询性能。
 
三、慢 SQL 管理实践

3.1 问题分析和解决方案

通过监控平台发现，项目的慢 SQL 数量较多，天天可能会产生几千甚至上万的慢 SQL，这对体系性能和用户体验都有不小的影响，因此，优化慢 SQL 问题值得被重视。我们从以下几个方面举行排查和分析：
 

 
1.数据量

我们都知道，同样的 SQL 语句，对于差别数据量的库表，查询效率也不一样，当数据量达到万万级甚至上亿，平凡的查询语句实验时间可能也会超过一秒，进而出现慢 SQL 问题。
 
经过排查，发现不少库表的数据量已经达到万万，个别分表的数据量已经达到一亿多，几年前的历史数据仍然生存，必要举行人工清理。
 
针对数据量对 SQL 实验带来的影响，我们可以从三个方面解决：
 
（1）清理数据

最直接的方式就是将无效数据清理，很多几年前的数据，几乎没有存储的价值，可以直接提交删除语句举行清理掉，当然，我们在删除时要注意线上影响，避免一次性删除太多数据，容易造成线上数据库效率受到影响，对于单个分表可以接纳分批删除，每一批只删除一个时间段；对于多个分表可以按照分表去删除，只管减小对线上环境的影响。
 
以活动中台体系的答题活动为例，随着答题活动几年的运行，数据库已经有大量的用户数据，现在线上数据量很多到了万万级，个别到了上亿的数据量，这对线上访问影响很大。
 
所以我们举行手动清理数据，现在线上一共10张分表，思量到对线上用户业务的影响，我们通过两方面减小影响：
 
一是分五次举行清理，每次只清理2~3张分表；
二是将删除语句转化为根据主键删除，大大提升 SQL 语句实验的效率。
 
终极清理掉大量的活动数据，我们接纳的删除计谋是将一年从前的历史数据举行删除，这部分数据已经不会用到，这个时间范围清理掉了大量的无效数据。
（2）分库分表

手动清理数据虽然能解决问题，但是治标不治本，而且对于一些特别的活动，可能留下的数据量仍然很大，这个时间如果库表的数据量仍然较大，且不能举行删除，就要思量分库分表是否充足，可能之前出于业务思量没有举行分表大概分表数量较少，这个时间就要思量举行更多的分表，以提升数据库访问的效率。这里如果可以的话最好设置分表计谋，创建配置项，机动扩充实表，避免直接发版。
 
活动中台体系会有一张单独的路由表，记录分表路由，便于快速查询分表。
 
同时可以把路由相干配置直接创建在配置中央，无须发版就可以快速扩分表。可以根据数据量巨细创建10、20、50、100张分表。
 
（3）大数据量查询移步ES数据库

当然，对于一些响应要求比较高的业务需求，MySQL 数据库的性能可能无法达到要求，这个时间可以思量将数据存储在 ElasticSearch 数据库大概缓存当中。
 
2.SQL 语句

数据库的数据量是重要的影响因素，但是 SQL 语句本身更会影响实验的效率，规范的 SQL 语句是避免慢 SQL 的前提。根据下图可以看出，SQL 的实验顺序为：

• 起首实验 from、join 来确定表之间的毗连关系，得到初步的数据。
  
• 然后使用 where 关键字后面的条件对符合条件的语句举行筛选。
  
• from & join&where：用于确定要查询的表的范围，涉及到哪些表。
  

 
那么根据这个实验顺序，我们来看下 SQL 实验过程中常见的问题有哪些?
 
（1）查询字段

我们在项目中查询最常用的是返回整个 DO 层对象，但着实很多时间我们只必要其中几个字段甚至一个字段，这个时间查询整个对象是很不划算的，比如下面这个例子，该语句在数据库管理器中的实验结果如下，实验时间为9269ms。

1. select * from a where id = 0;

复制代码

 
然而我们只是想查询指定活动下的单个字段，这个时间可以不必要返回其他字段，只返回必要的字段，优化后示例如下，实验时间为4104ms，显着实验时间变短了。

1. select result from a where id = 0;

复制代码

 
（2）索引问题

索引在数据库查询中是一个很重要的影响因素，走不到索引和走到索引是有很大的区别的。但是索引有弊也有利：
长处

• 提高查询语句的实验效率，减少 IO 操纵的次数
  
• 创建唯一性索引，可以保证数据库表中每一行数据的唯一性
  
• 加了索引的列会举行排序，在使用分组和排序子句举行查询时，可以显着减少查询中分组和排序的时间
  

 
缺点

• 索引必要占物理空间
  
• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
  
• 当对表中的数据举行增编削查时，索引也要动态的维护，这样就降低了数据的更新效率
  
• 所以公道的设置索引并使用索引是高效实验SQL的重要因素。
  

 
以下面这个案例来分析：

1. CREATE TABLE `table_test` (
2. `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
3. `test` varchar(128) NOT NULL DEFAULT '' COMMENT '索引测试字段',
4. ............
5. `result` varchar(128) NOT NULL DEFAULT '' COMMENT '结果测试字段',
6. PRIMARY KEY (`id`),
7. KEY `test` (`test`),
8. ) COMMENT='记录表';

复制代码

 
这是活动平台体系记录的数据表，其中【result】是唯一的，这里可以看到索引没有涉及【result】，如果我们根据这个字段去查询唯一记录，看下实验结果怎样：
 

 
耗时4286ms，这里根据建表语句可以看到有索引【KEY test (test)】，如果我们根据索引字段来查询该条记录的话，看下实验结果怎样：
 

 
可以看到只必要16ms，索引对数据库效率的提升至关重要。我们可以根据 explain 关键字来分析一下 SQL 语句，比如刚才的案例，我们来看下相应的结果：

 

 
可以看到，直接根据非索引字段【result】查询，type 字段值为【ALL】，代表遍历全表，扫描行数为3144273，如果带上索引字段【test】，type 字段值为【ref】，代表匹配到单行记录值，扫描行数为1，显然通过索引查询效率得到了极大的提升。对于索引的使用会出现以下几种常见范例：
 
ALL：全表扫描，表示 MySQL 将遍历整个表以满足查询条件。这通常是效率最低的访问范例，应只管避免。
 
index：索引全扫描，表示 MySQL 将遍历整个索引以满足查询条件，而不是遍历整个表。虽然比全表扫描效率要高，但仍然必要遍历索引的每一行。
 
range：范围扫描，表示 MySQL 使用了索引的一部分来满足查询条件，例如使用了索引的某个范围。这通常发生在有范围查询条件时，例如使用了 WHERE 子句中的 BETWEEN、>、< 等操纵符。
 
index_merge：索引合并，表示 MySQL 使用了多个索引来满足查询条件，然后将结果合并。这通常发生在查询中有多个条件，每个条件可以使用差别的索引来访问数据。
 
unique_subquery：唯一子查询，表示 MySQL 使用了子查询来获取唯一的结果，并且子查询使用了唯一索引。
 
const：常量，表示 MySQL 使用了常量表来获取结果，这通常发生在查询条件中包含了常量值。
 
ref：引用，表示 MySQL 使用了非唯一索引来扫描表，通常发生在查询中使用了单个索引列作为条件。
 
（3）联表查询

【JOIN】关键词在项目的日常查询中可能会遇到，对于一些管理台项目可能要求较低，可以作为日常开发查询语句，但是对于面向用户的项目，由于数据量较大，往往必要避免联合查询的使用。以下面这个案例来分析：

1. select * from a left join b on a.id = b.id where a.id = 0;

复制代码

 
这个SQL是联合多张表举行联合查询，还有其他查询条件，实验得出结果时间为1432ms，超过1秒则为慢 SQL，这里联合两张表，如果相干的数据量较大，则实验速度会较慢。我们可以将 SQL 拆分为两个语句实验，拆为以下两个 SQL 分步实验：

1. select * from a where id = 0;
2. select * from b where id = 0;

复制代码

 
分步获取数据库结果后再举行聚合，分步实验结果分别为728ms和744ms，联合查询拆分为简单查询可以有效减少慢 SQL，同时可以提高SQL查询的复用性。
 
（4）条件查询

 
在项目的日常开发中，SQL 语句切忌使用复杂查询，这会对数据库造成较大的压力。下面这个例子就是使用了比较复杂的条件查询：
 

1. select * from a where id in (select id from b) and time > '2024-03-29';

复制代码

 
使用查询条件的同时还嵌套了查询语句，除此之外还夹杂了联合查询，语句已经比较复杂，我们来看下查询结果：
 

 
可以看到实验时间非常迟钝，达到12648ms，这个SQL的背景是用于业务对账，虽然使用的是离线数据库，但是仍然不能忽视它的风险，天天大量的实验这种复杂 SQL，还是对业务有一定的风险影响。思量到业务价值单一，复用性不高，我们可以直接冗余一份数据到单独的表里，避免复杂查询，直接一步到位解决对账带来的慢 SQL 问题。
 
3. 整体计谋

清理完数据库的无效数据，优化完 SQL 语句本身，可能还是会出现慢 SQL 问题，这个时间我们要思量下，是否可以优化整体的数据库交互计谋，以活动中台体系的数据清理慢 SQL 为例。
 
活动中台体系创建了一个定时任务，天天凌晨实验一次，对数据库的无效历史数据举行同一清理，具体删除哪些库表、什么时间段、什么条件都由配置项机动控制，配置项示例如下：

1. "分表数量"：7,
2. "表名"：table,
3. "条件"：condition

复制代码

 
包罗分表的数量、要删除数据的表名、查询的条件信息，查询条件对应SQL语句中的【where】信息。实验流程如下图：
 

 
整体上看删除计谋很通用，条件配置机动，可以同时应对差别分表、差别查询条件等。但是线上运行发现会产生大量的慢SQL，可能天天就会产生几千条。
 
重要原因如下：

• 会出现联表查询的环境，数据量较大的表会出实际验时间超过一秒的 SQL 语句；
  
• 删除计谋中时间是重要的因素，但是时间往往不会设置为索引字段，所以很难充实使用索引；
  
• 删除计谋会扫描全部分表，但是很多分表可能全部扫描也没有必要删除的数据，会出现无效实验的环境。
  

 
针对以上计谋，我们举行了综合改进，重要措施有以下几条：

• 避免联表查询，尽可能的拆分为简单 SQL 实验；
  
• 不要从时间维度直接出发，而是从活动维度出发，如果一个活动结束时间较长，那么这个活动相干的数据自然不再必要，可以直接删除；
  
• 使用分表路由信息，从活动本身出发快速路由到相干分表，减少 SQL 语句的无效实验。
  

 
修改完的配置项如下：

1. "分表数量"：7,
2. "表名"：table,
3. "条件"：condition,"删除数量"：1,"删除计谋":1,"开始时间":"","结束时间":""

复制代码

 
包罗分表的数量、要删除数据的表名、查询条件、每次删除的数量、开始时间和结束时间，在这个时间范围内的活动都符合条件，整体上看删除计谋更为通用，条件配置更加机动。优化的删除计谋流程图如下：
 

 
从图中可以看到，我们生存原有的删除计谋，避免个别数据表没有活动信息和分表信息，只能单纯根据时间扫描删除；除此之外，对于有活动信息的数据，我们会先查询活动，根据活动去删除，如果数据表是根据活动举行分表，则直接查询路由信息，并删除指定分表的数据即可，也就是图中的第三种计谋，线上大部分的数据都会按照第三种计谋实验，如果有活动信息但是未按照活动举行分表，可能按照用户等维度举行分表，则可以根据活动信息举行删除，也可以有效避免慢 SQL 的出现。
 
3.2 管理结果

经过对慢 SQL 的专项管理，活动中台体系的慢 SQL 数量由几千个，稳定在了两位数，有效减少了慢SQL的数量，进一步提升了体系稳定性。
 

 
四、经验总结

 

• 管理慢 SQL 的根本是从源头避免慢 SQL，项目组内部必须达成高度一致，根据编码规范举行前置避免慢 SQL 的出现。
  
• 离线数据库不能成为忽视慢 SQL 问题的原因，仍然会有影响线上业务的风险，数据库实例如果是混合部署的方式，可能离线库地点的机器有其他业务的主库，而且如果从库耽误严重会影响主从故障切换。
  
• 数据库的公道设计不能依赖后期重构，一开始就要尽可能的思量充实。
  
• 慢 SQL 管理过程中出现的问题可以及时复盘，避免团队其他成员继续踩坑。
  

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。