【经验】几种数据库优化技巧
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）查询频率非常高的时候，可以考虑覆盖索引。
