如何为AWS Aurora（MySQL）15M+行的UnitHistory表设置高性能低成本键

针对你在AWS Aurora（小型实例）上15M+行的UnitHistory表性能优化需求，结合这类产品单元追踪表的常见业务场景，我整理了一套实操性强的优化方案，都是生产环境验证过的思路：

索引是提升查询性能的关键，一定要贴合你的实际查询模式来建：

• 优先覆盖高频查询的过滤+排序字段：先梳理清楚业务上最常用的查询——比如是不是经常按CustomerId筛选某个客户的单元历史，再按ScanTime倒序查看？或者经常通过SerialNumber查询单个单元的全量轨迹？
  • 如果是前者，创建联合索引：CREATE INDEX idx_customer_scantime ON UnitHistory (CustomerId, ScanTime DESC); 这个索引能直接覆盖查询，不用回表取数据，效率极高。
  • 如果是后者，若SerialNumber是唯一值，建唯一索引：CREATE UNIQUE INDEX idx_serialnumber ON UnitHistory (SerialNumber); 若存在重复，建普通索引即可。
  • 注意避免冗余索引：比如已经有(CustomerId, ScanTime)的联合索引，就没必要单独建CustomerId的索引了，前者已经覆盖了单查CustomerId的场景。
• 前缀索引优化长字段：如果Model是较长的字符串，且查询只按前缀匹配，可建前缀索引减少存储空间：CREATE INDEX idx_model_prefix ON UnitHistory (Model(10)); 索引越小，越容易被Aurora的内存缓存，适合小型实例的资源限制。

• 精简数据类型：检查列的类型是否合理——比如CustomerId如果是整数且范围不大，没必要用bigint，改成int就能减少单条数据的大小；ScanTime和UpdateTimestamp用datetime或timestamp足够，别用更大的类型。数据越小，内存一页能存的行数越多，缓存命中率越高。
• 时间范围分区：这类历史追踪表天生适合按时间分区，比如按ScanTime或UpdateTimestamp按月分区。查询指定时间范围的数据时，Aurora只会扫描对应分区，不用遍历全表，能大幅降低IO压力。示例语句：

  ALTER TABLE UnitHistory PARTITION BY RANGE (TO_DAYS(ScanTime)) (
      PARTITION p202401 VALUES LESS THAN (TO_DAYS('2024-02-01')),
      PARTITION p202402 VALUES LESS THAN (TO_DAYS('2024-03-01')),
      -- 按需添加后续分区
      PARTITION p_current VALUES LESS THAN MAXVALUE
  );
  

• 归档冷数据：15M行里肯定有大量不常查询的冷数据（比如1年前的），可以把这些数据归档到Aurora只读副本或者导出到S3（用Aurora快照导出功能），主表只保留最近3-6个月的热数据，直接缩小主表的数据规模，提升读写性能。

• 读写分离分流查询：如果有大量报表类、历史查询类请求，把这些请求分流到Aurora只读副本上，减轻主实例的CPU和内存压力。小型实例资源有限，读写分离能有效提升整体吞吐量。
• 调整参数组适配小型实例：针对MySQL兼容的Aurora，调整几个关键参数：
  • innodb_buffer_pool_size：尽量调高到实例内存的70%-80%（比如db.t3.small有2G内存，设为1.2G左右），让更多热数据和索引缓存到内存，减少磁盘IO。
  • query_cache_type：建议关闭（设为0），Aurora的缓存机制更依赖InnoDB缓冲池，查询缓存反而会带来额外开销。
  • innodb_flush_log_at_trx_commit：如果业务能接受1秒内的少量数据丢失风险，设为2，减少磁盘刷新频率，提升写入性能。
• 监控定位瓶颈：通过AWS CloudWatch监控CPUUtilization、FreeableMemory、ReadIOPS、WriteIOPS这些指标，如果CPU持续跑满，再考虑升级实例规格——但多数情况下，前面的索引和表结构优化就能解决问题，不用急着升级。

• **避免SELECT ***：只查询业务需要的列，比如只查UnitId、ScanTime、CustomerId，就别返回所有列，减少数据传输和内存占用。
• 高效分页：避免大偏移量的分页（比如LIMIT 100000, 100），这种写法会导致全表扫描。改用键值分页：WHERE ScanTime > '2024-01-01' AND CustomerId = 123 LIMIT 100，利用索引快速定位。
• 避免字段函数操作：比如WHERE DATE(ScanTime) = '2024-01-01'会导致索引失效，改成WHERE ScanTime >= '2024-01-01' AND ScanTime < '2024-01-02'，这样就能用到ScanTime的索引。

内容的提问来源于stack exchange，提问作者user5071535