如何设计MySQL表结构实现FIT运动数据高速SELECT查询

问题1：查询速度优先场景下的表结构推荐 #

优先选择核心固定字段宽表+JSON扩展字段的混合宽表设计，完全匹配查询速度第一、存储体积第二的优先级要求，具体选型依据如下：

• 你当前采用的「单文件表+每参数独立表」设计是典型的EAV（实体-属性-值）反模式，天生不适合多维度时序查询场景：跨参数分析需要关联N张表，关联逻辑稍不注意就会产生笛卡尔积，参数越多join层数越多，查询性能会指数级下降，哪怕做全字段索引也达不到宽表的查询效率。
• 纯宽表的频繁改表、空值顾虑完全可以通过设计规避：
  • FIT协议的核心高频采样字段是固定的，包括时间戳、经纬度、海拔、心率、速度、踏频、功率、温度等，这类字段直接建为固定列，覆盖90%以上的常规分析需求，查询性能拉满。
  • 零散新增的低频外设参数，不需要每次执行ALTER TABLE加字段，统一存入ext_params JSON类型字段即可；MySQL 5.7+版本对JSON字段的查询、索引支持已经非常成熟，低频参数查询完全能满足性能要求，且接入新参数不需要改表结构。
  • 大表性能焦虑没有必要：InnoDB引擎下，单表只要主键设计合理、过滤字段加对索引，千万级行规模的查询延迟依然可以稳定在毫秒级；后续数据量持续增长后，按file_id或者采样时间做范围分区即可，性能不会出现明显下跌。
  • 真遇到高频使用的新增参数，MySQL 5.6之后支持Online DDL，加列操作不会长时间锁表，对线上查询的影响极小，操作成本远低于多表join的查询成本。
• 实测数据已经验证了宽表的性能优势：同过滤条件下宽表查询仅需0.5秒，多表join耗时30秒还返回错误结果，性能差距达到60倍。

问题2：现有LEFT JOIN语句的逻辑错误 #

你的关联语句存在根本性的逻辑错误，这是查询慢、返回结果行数异常爆炸的核心原因，和索引无关：

• 当前join条件仅写了x.file_id = y.file_id，等价于把同一个file_id下两张参数表的所有行做全量笛卡尔积。你提到file_id=999下单表有1964行数据，1964*1964≈385万行，和你实际返回的3857269行结果完全吻合，本质是返回了两个参数所有值的两两组合，根本不是你想要的同时序参数匹配结果。
• 正确的关联逻辑必须加上时序对齐条件，因为FIT文件的参数值是和秒级时间戳/采样计数器一一绑定的，修正后的SQL示例：

SELECT * FROM tbl_parameter_xy as x
LEFT JOIN tbl_parameter_yz as y
ON x.file_id = y.file_id
AND x.timestamp = y.timestamp -- 之前缺失的核心关联条件，按采样时间对齐同一点位的参数
WHERE x.file_id = 999

• 为什么加了file_id索引反而没提升：索引只能解决数据查找的效率问题，解决不了关联逻辑错误导致的笛卡尔积问题，反而多了索引树遍历的开销，自然不会变快。
• 哪怕修正了关联逻辑，多参数表的设计性能依然远差于宽表：如果需要同时查询10个参数，就要连续join9次，每次join都要做索引查找、数据匹配，遇到不同外设采样率不一致的情况（比如心率1秒1采、功率0.5秒1采），还要写复杂的时间对齐逻辑，开发和查询成本都极高。

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