如何为大量历史购销数据设计合理的MySQL表结构？

你这个场景的业务逻辑非常清晰，按下面的结构设计完全可以满足查询性能要求：

• 基础客户表 customer，存储客户的身份、登录信息，全表仅1万条数据，无性能压力：

CREATE TABLE `customer` (
  `customer_id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '客户唯一ID',
  `login_account` varchar(32) NOT NULL COMMENT '客户登录账号',
  `customer_name` varchar(50) NOT NULL COMMENT '客户名称',
  -- 其他你需要的客户自定义字段，比如联系电话、地址等
  PRIMARY KEY (`customer_id`),
  UNIQUE KEY `idx_login_account` (`login_account`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

• 核心月度购销数据表 customer_monthly_trade，存储所有客户的月度统计数据，字段尽量精简避免冗余：

CREATE TABLE `customer_monthly_trade` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `customer_id` int unsigned NOT NULL COMMENT '关联客户ID',
  `stat_year` smallint unsigned NOT NULL COMMENT '统计年份，如2024',
  `stat_month` tinyint unsigned NOT NULL COMMENT '统计月份，取值1-12',
  `purchase_amount` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '月度采购金额',
  `sale_amount` decimal(12,2) NOT NULL DEFAULT '0.00' COMMENT '月度销售金额',
  -- 其他需要的月度统计字段可自行添加
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_cid_year_month` (`customer_id`,`stat_year`,`stat_month`),
  KEY `idx_cid_year_month_desc` (`customer_id`,`stat_year` DESC,`stat_month` DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

重点是联合索引 idx_cid_year_month_desc，客户登录后用自己的customer_id查询最近120个月的数据时，会完全走这个索引，不需要扫全表，查询延迟基本在毫秒级。每月月末批量插入1万条当月数据的操作也没有压力，批量提交的话耗时不会超过1秒。

你这个场景的数据量非常小，完全不需要担心单表的上限问题：

• 先算实际数据量：1万客户每年产生12万条数据，存10年也才120万条，就算存100年也才1200万条，这个量级对于MySQL InnoDB引擎来说属于非常轻的负载，根本碰不到性能瓶颈。
• 官方理论限制：InnoDB单表最大支持64TB存储空间，按你单条数据几十字节的大小来算，单表存几亿条都没问题；列数上限是1017列，你这张月度统计表最多加十几个统计字段，远低于上限。
  实际使用中你需要注意的不是行列上限，而是不要加冗余字段、确保查询走索引，你当前的业务规模用单表完全够用，不需要额外搞分库分表、表分区这类增加复杂度的方案，后续如果真的增长到千万级以上数据量，再考虑按年份做表分区就行。

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