个人中心
gpt4 book ai didi

【经验】几种数据库优化技巧

转载 作者：撒哈拉 更新时间：2024-12-21 18:45:52 57 4
gpt4 key购买 nike

1.分表思想

对于查询操作来说，表中数据越少，查询速度通常越快。因此，优化的方向就是将不相关的数据分离到其他表中.

案例 1：活跃数据与历史数据分表 。

如果系统的大部分业务操作集中在“活跃”数据上，可以考虑将数据划分为活跃数据表和历史数据表:

  • 活跃数据表：包含当前常用的数据，数据量较少，但读写操作频繁。
  • 历史数据表：存储较少查询的数据，数据量大，但查询频率低。

通过分表，能有效提高查询效率，减少活跃数据表的负担.

案例 2：基于用户ID分表 。

当系统涉及大量用户的私有数据时，可以根据用户ID进行分表。通过哈希算法将用户数据均匀分布到多个表中，例如分成10张表，按用户ID进行哈希分配。这样可以保证每个表中的数据量相对均衡，提高查询性能.

2. 监控慢查询

Mysql CPU占用高，通常是慢查询导致的。可以通过记录慢查询日志发现哪些查询语句不符合预期，导致了系统性能下降.

1.修改mysql的配置文件，开启慢查询日志。（建议在测试环境中开启并进行压测） 。

[mysqld]
slow_query_log = 1              # 开启慢查询日志
slow_query_log_file = /path/to/your/slow_query.log  # 慢查询日志文件路径
long_query_time = 1            # 设置慢查询的阈值，单位为秒（这里是 1 秒）

2.通过分析慢查询日志，定位性能瓶颈，进行优化.

3. 监控SQL执行数量

有时候慢查询日志不多，但是mysql的CPU占用却很高。那有可能是大量的快查询堆积出来的。通过检查统计数据，可以判断有哪些查询是不符合预期的.

1.清空统计数据 。

TRUNCATE TABLE performance_schema.events_statements_summary_by_digest;

2.压力测试结束后查看统计数据 。

SELECT * FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 100;

案例：JPA的@NotFound(action = NotFoundAction.IGNORE)导致懒加载数据被提前加载 。

使用JPA通常使用懒加载来关联数据，但是如果用了上面的注解，就会导致懒加载失效，数据被提前加载，产生很多额外的select by id的SQL查询.

4.一致的修改顺序，防止死锁

假设业务操作①和业务操作②，都需要修改A，B，C。那么不一致的修改顺序，就可能导致死锁.

死锁示例:

  • 操作①：修改 A → 修改 B → 修改 C。
  • 操作②：修改 B → 修改 C → 修改 A。

若同时执行这两个操作，就可能发生死锁。操作①持有 A 的锁，等待 B 的锁；操作②持有 B 的锁，等待 A 的锁，最终导致死锁.

建议：定义统一的实体修改顺序 。

为了避免死锁，建议在系统中定义一个实体的修改顺序规则，并确保所有业务操作都遵循该规则。例如:

  • 在所有操作中，始终按照 A -> B -> C 的顺序修改这些实体。
  • 通过约定的顺序，确保即使多个操作并发执行，也不会产生互相等待锁的情况，从而避免死锁。

5. 查询优化器未必靠谱，强制使用索引

Mysql有查询优化器，但是查询优化器有时候不靠谱。比如某个字段有索引，查询条件也用到了。但是它不用这个索引，导致全表扫描.

此时，可以考虑使用 FORCE INDEX 来强制优化器选择某个索引.

验证方式：可以导入测试数据，通过执行 EXPLAIN [SQL]，你可以查看优化器是否正确选择了索引.

6. 批次入库思想

批量插入数据是提高效率的常见做法。但批量操作未必适合所有场景。特别是当多个数据项并非来自同一个请求或操作时，可以考虑批次管理策略.

策略:

  • 每次批次大小达到100条时进行入库。
  • 设置时间限制，例如1秒或2秒，无论批次是否满100条，时间到也要执行入库。

案例：Kafka的生产消费都采用批次处理 。

建议：合理设计批次大小和时间限制 。

7. 有限数据思想（Limit）

在处理定时任务或周期性查询时，限制查询的数据量是一个非常好的优化策略。例如，每次最多查询100条数据，防止一次查询返回大量数据，导致内存占用过高.

建议：在定期任务中，最好加上数据量的限制，避免一次性加载过多数据。如果数据量较大，可以考虑分页查询或分批处理.

8. 注意查询范围

在涉及时间范围的查询时，务必谨慎处理查询的时间范围。如果查询条件不明确，可能会导致全表扫描。例如，查询过期数据时，使用 expireTime < currentTime 作为条件，如果没有加上特定的时间区间，查询范围可能会过于广泛.

案例：定时任务，可以记录上一次处理的最后一个条目的时间，作为下一次查询的时间起点.

建议：在进行时间范围查询时，明确设置查询的区间，避免查询条件过于宽泛，导致性能问题.

9. 复合索引

复合索引可以显著提高查询效率，特别是在查询涉及多个字段时。它通过将多个字段组合成一个索引来加速查询。不过，需要注意的是，复合索引遵循最左前缀原则，即查询条件必须按照索引字段的顺序提供，且必须从第一个索引字段开始。如果查询条件没有从复合索引的最左边字段开始，复合索引将无法生效.

复合索引 vs 独立索引 。

假设每次查询都涉及到两个字段。如果为每个字段创建独立的索引，MySQL将需要分别扫描这两个索引树，并进行合并操作。而使用复合索引时，只需扫描一个索引树，大大提高了查询效率.

注意 。

复合索引和独立索引并不冲突。如果复合索引 IndexABC 包含字段 (A, B, C)，但某些查询只需要 C 字段时，复合索引就无法生效。这时，应为 C 字段单独创建一个索引，以提高查询效率.

 10.覆盖索引

覆盖索引（Covering Index）指的是索引中的所有字段都能够满足查询的需求，也就是说，查询所需要的所有数据都可以从索引本身获得，而无需回到原始数据表去查询.

建议：当字段组合，例如（A,B,C）查询频率非常高的时候，可以考虑覆盖索引.

最后此篇关于【经验】几种数据库优化技巧的文章就讲到这里了,如果你想了解更多关于【经验】几种数据库优化技巧的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。

57 4 0
文章推荐： 概率论沉思录：初等假设检验
文章推荐： 开启Word、Excel、PPT时速度很慢的一种解决方法
文章推荐： 一个.NET开源、易于使用的屏幕录制工具
文章推荐： 强化学习算法中的log_det_jacobian——概率分布的仿射变换（Bijector）
撒哈拉
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com