mysql主键索引设计有什么讲究_mysql表结构优化

应避免默认使用BIGINT AUTO_INCREMENT作主键；因其在分库分表等场景易冲突，且冗余占用存储，优先选用业务天然唯一标识或更小整型。

主键必须是 NOT NULL 且唯一，但别默认用 BIGINT AUTO_INCREMENT 填充

MySQL 的 PRIMARY KEY 本质是唯一非空的聚簇索引（InnoDB），直接影响数据物理存储顺序和查询性能。很多人直接给每张表加一个 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY，看似省事，但会埋下几个隐患：

• AUTO_INCREMENT 在分库分表、多写节点或主从切换时容易冲突，尤其用 auto_increment_offset 和 auto_increment_increment 手动调参容易漏配
• 如果业务上天然有唯一标识（如订单号 order_no VARCHAR(32)、用户手机号 phone CHAR(11)），再额外加 id 字段，等于多存一份冗余索引，浪费磁盘和内存
• BIGINT 占 8 字节，而 INT UNSIGNED（4 字节）在记录数

建议：优先考

虑「业务有意义 + 全局唯一 + 稳定不变」的字段做主键；若无，则用 INT UNSIGNED AUTO_INCREMENT 起步，预留扩容空间，避免一上来就上 BIGINT。

联合主键要谨慎，尤其含可变长字段（VARCHAR）时

用多个字段组合做主键（如 PRIMARY KEY (user_id, trade_date)）在分区表、时间序列归档等场景有用，但容易踩坑：

• InnoDB 的二级索引叶子节点里会隐式包含主键值，联合主键越长，所有二级索引体积越大——比如 VARCHAR(64) 字段进主键，每个二级索引条目都多存 64 字节
• 主键过长会降低 innodb_page_size 的有效利用率，导致单页存更少行，加剧 I/O
• 排序、范围扫描（如 WHERE user_id = ? AND trade_date BETWEEN ? AND ?）虽能走主键，但若查询只用 user_id，就只能用最左前缀，trade_date 部分失效

实操建议：联合主键字段总数不超过 3 个；优先用整型字段；避免把 VARCHAR 或 TEXT 类型塞进主键；若只是为约束唯一性，用 UNIQUE KEY 替代联合主键更轻量。

UUID 或 Snowflake ID 做主键时，务必关闭 innodb_large_prefix 并注意插入性能

用 CHAR(32) 存标准 UUID（如 '550e8400-e29b-41d4-a716-446655440000'）或 BINARY(16) 存二进制格式，虽然解决了分布式唯一性问题，但带来新问题：

• 随机写入导致大量页分裂：UUID 值无序，新记录总插在索引中间而非末尾，InnoDB 频繁分裂页、合并页，写放大严重
• CHAR(32) 主键会让所有二级索引膨胀约 32 字节/行；改用 BINARY(16) 可减半，但需应用层做 hex ↔ binary 转换
• MySQL 5.7+ 默认开启 innodb_large_prefix，但若主键含长 VARCHAR，建表可能报错 Specified key was too long，需确认 innodb_file_format= Barracuda 且 ROW_FORMAT=DYNAMIC

ALTER TABLE orders 
  ROW_FORMAT=DYNAMIC,
  CONVERT TO CHARACTER SET utf8mb4;

若坚持用 UUID，建议配合 ORDER BY RAND() 插入优化（不现实）或改用有序 UUID（如 UUID_TO_BIN(UUID(), 1) 生成时间前置版本）。

主键变更代价极高，上线前必须用 EXPLAIN 和 INFORMATION_SCHEMA.STATISTICS 验证索引实际生效路径

修改主键（比如从 INT 改成 BIGINT，或从单列改成联合）会触发全表重建（ALGORITHM=INPLACE 在某些情况下也不支持），线上大表可能锁表数小时。更隐蔽的问题是：你以为加了主键就能加速查询，但实际执行计划未必走它。

• 用 EXPLAIN SELECT ... 检查 key 列是否显示主键名，rows 是否显著下降
• 查 INFORMATION_SCHEMA.STATISTICS 确认主键是否真被 InnoDB 当作聚簇索引（INDEX_NAME = 'PRIMARY' 且 SEQ_IN_INDEX = 1）
• 对高频更新表，主键频繁变更还会让 change buffer 失效，反而拖慢写入

真正难的不是选什么当主键，而是想清楚这张表最重的读写模式是什么——是点查多？范围扫多？写入频次多高？这些比“该不该用 UUID”更能决定主键设计成败。