MySQL中如何实现父表单行记录与对应子表的关联

你一开始想的「每个资产单独建表、拿父表主键当子表名」是数据库设计里出了名的反模式，几千个资产的分钟级价格数据根本犯不上这么折腾，用标准的外键关联单表+合理索引，性能够用，还好维护。

• 维护成本根本兜不住：几千张表意味着后续你要加个字段、改个字段类型，得跑几千次ALTER语句，备份、排查问题、做数据迁移的时候，翻几千张表能把人耗死
• 查询灵活性极差：想查多个资产同时间的价格对比、算全市场某一分钟的均价，就得拼几千张表的UNION SQL，不仅写起来麻烦，性能拉胯还容易出bug
• InnoDB本身对表数量很敏感，表数量过千之后元数据管理会明显卡顿，属于是主动给数据库找不痛快

表结构设计 #

就建两张表，父表存资产基础信息，所有价格数据全放一张子表，用资产ID做关联键就行：
首先是资产父表：

CREATE TABLE `assets` (
  `asset_id` INT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '资产唯一ID',
  `asset_code` VARCHAR(32) NOT NULL COMMENT '资产交易编码，比如BTC_USDT、600519',
  `asset_name` VARCHAR(128) NOT NULL COMMENT '资产全称',
  `create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`asset_id`),
  UNIQUE KEY `uk_asset_code` (`asset_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

然后是价格明细表，所有资产的价格全存在这：

CREATE TABLE `asset_prices` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `asset_id` INT UNSIGNED NOT NULL COMMENT '关联assets表的asset_id',
  `price` DECIMAL(18,8) NOT NULL COMMENT '对应分钟的成交价格',
  `ts` DATETIME NOT NULL COMMENT '价格对应的分钟级时间戳',
  `volume` DECIMAL(18,4) DEFAULT NULL COMMENT '可选，存对应分钟成交量',
  PRIMARY KEY (`id`),
  -- 核心联合索引，直接覆盖单资产按时间范围查价格的最高频场景
  KEY `idx_asset_ts` (`asset_id`, `ts`),
  KEY `idx_ts` (`ts`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

算笔账：就算你有5000个资产，每分钟一条数据，一年也就50006024*365≈26亿条。真到单表性能扛不住的时候，优先做两种优化，别拆成按资产分的散表：

1. 按时间做RANGE分区，比如每个季度一个分区，查指定时间范围的数据时MySQL会自动裁剪掉无关分区，性能提升很明显
2. 按asset_id做HASH分固定数量的表，比如分32或者64张，路由规则写死分表后缀 = asset_id % 分表总数，不需要动态建表，新资产进来直接按规则路由到对应表就行，比维护几千张动态表简单100倍

Python端实现逻辑 #

根本不需要写动态拼表名的逻辑，不管用原生驱动还是ORM都非常简单，举个pymysql的原生实现例子：
批量写入价格数据的方法：

import pymysql
from datetime import datetime

def batch_save_prices(price_records: list[tuple[int, float, datetime]]):
    """
    批量写入分钟级价格
    :param price_records: 格式为 [(资产ID, 价格, 对应时间戳), ...]
    """
    conn = pymysql.connect(
        host="127.0.0.1",
        user="your_db_user",
        password="your_db_pwd",
        database="market_data",
        charset="utf8mb4"
    )
    try:
        with conn.cursor() as cur:
            insert_sql = """
                INSERT INTO asset_prices (asset_id, price, ts)
                VALUES (%s, %s, %s)
            """
            # 批量写入比单条循环插入性能高几十倍，分钟级写入完全无压力
            cur.executemany(insert_sql, price_records)
        conn.commit()
    finally:
        conn.close()

查询单个资产指定时间范围价格的方法：

def query_asset_prices(asset_id: int, start_time: datetime, end_time: datetime):
    conn = pymysql.connect(
        host="127.0.0.1",
        user="your_db_user",
        password="your_db_pwd",
        database="market_data",
        charset="utf8mb4"
    )
    try:
        with conn.cursor() as cur:
            query_sql = """
                SELECT price, ts FROM asset_prices
                WHERE asset_id = %s AND ts BETWEEN %s AND %s
                ORDER BY ts ASC
            """
            cur.execute(query_sql, (asset_id, start_time, end_time))
            return cur.fetchall()
    finally:
        conn.close()

• 写入的时候尽量攒批，比如攒个几百条再一次性调用executemany写入，不要来一条就插一次，能大幅降低磁盘IO压力
• 没必要加物理外键约束，物理外键会拖慢写入速度，只要你业务层写入的时候保证传入的asset_id都在assets表存在就行，毕竟这类行情数据都是你自己的程序写入，不会有乱插脏数据的问题
• 如果要存3年以上的历史数据，可以做冷热分离：1年以内的热数据存在MySQL供实时查询，更早的冷数据归档到低成本存储里
• 如果后续要做分表，一定固定分表总数，不要跟着资产动态建表，路由规则写死在代码里，维护成本极低

内容的提问来源于stack exchange，提问作者Full.Of.Life