文章目录MySQL表不能没有主键原因、危害与主从架构问题前言一、InnoDB 的索引组织表IOT二、没有主键时 InnoDB 的处理方式1. 选择第一个 UNIQUE NOT NULL 的索引2. 如果没有合适的唯一索引自动生成隐藏的 _rowid三、没有显式主键的危害危害一无法使用覆盖索引优化危害二二级索引膨胀危害三数据物理排列无序产生大量随机 I/O危害四无法高效执行 REPLACE INTO 和 INSERT ... ON DUPLICATE KEY UPDATE危害五ORM 框架和工具链的兼容性问题四、主从复制架构中的严重问题问题一Row-Based ReplicationRBR性能严重下降问题二从库回放 UPDATE/DELETE 时全表扫描并行复制MTS对无主键表的回放是否有作用问题三数据不一致风险增大问题四并行复制MTS效率降低问题五半同步复制和组复制的风险五、最佳实践1. 每张表必须有显式主键1.1 MySQL 8.0.26 安全网自动生成不可见主键2. 推荐使用自增整数作为主键3. 分布式场景的主键方案4. 检查现有表是否缺少主键六、总结MySQL表不能没有主键原因、危害与主从架构问题前言在 MySQL 的建表规范中“每张表必须有主键几乎是所有大厂开发规范中的第一条铁律。但很多人只知道要加主键”却不理解背后的深层原因。本文从 InnoDB 存储引擎的底层原理出发详细说明为什么 MySQL 表不能没有主键缺少主键会带来哪些危害以及在主从复制架构中可能引发的严重问题。一、InnoDB 的索引组织表IOT理解主键的重要性首先要理解 InnoDB 的表组织方式。InnoDB 采用索引组织表Index Organized Table的模式即表中的数据按照主键的顺序存储在 B 树的叶子节点中。这张以主键为索引的 B 树被称为聚簇索引Clustered Index。聚簇索引主键 B 树 ┌─────────────────────────────────────────┐ │ 非叶子节点 │ │ (存储主键值 指针) │ ├─────────────────────────────────────────┤ │ 叶子节点存储完整的行数据 │ │ 按主键顺序物理排列 │ └─────────────────────────────────────────┘主键是数据在磁盘上物理排列的依据。如果没有主键InnoDB 就无法确定数据的物理存储顺序这是所有问题的根源。二、没有主键时 InnoDB 的处理方式如果建表时没有指定主键InnoDB 会按以下顺序尝试选择一个替代方案1. 选择第一个 UNIQUE NOT NULL 的索引CREATETABLEt1(idBIGINTNOTNULLUNIQUE,nameVARCHAR(50));-- InnoDB 会使用 id 作为聚簇索引键2. 如果没有合适的唯一索引自动生成隐藏的 _rowidInnoDB 会自动创建一个 6 字节48 位的隐藏列DB_ROW_ID作为聚簇索引的键。CREATETABLEt2(nameVARCHAR(50),ageINT);-- 没有任何索引InnoDB 自动生成 DB_ROW_ID-- 可通过 SELECT _rowid FROM t2 查看部分版本支持这个隐藏的_rowid就是大多数问题的根源。需要特别注意所有没有主键的表共享同一个全局自增计数器dict_sys-row_id高并发写入时存在 mutex 锁竞争成为性能瓶颈。三、没有显式主键的危害危害一无法使用覆盖索引优化没有主键时所有二级索引的叶子节点存储的是_rowid而非业务字段查询无法通过二级索引直接获取数据必须回表。-- 有主键的情况CREATETABLEorders(order_idBIGINTPRIMARYKEY,user_idBIGINT,amountDECIMAL(10,2),INDEXidx_user_id(user_id));-- SELECT order_id, user_id FROM orders WHERE user_id 100-- 可以通过 idx_user_id 的叶子节点直接拿到 order_id主键无需回表-- 没有主键的情况CREATETABLEorders_no_pk(user_idBIGINT,amountDECIMAL(10,2),INDEXidx_user_id(user_id));-- 同样的查询必须回表到聚簇索引_rowid才能拿到完整行数据危害二二级索引膨胀InnoDB 的二级索引叶子节点存储的是主键值。主键越长二级索引占用的空间越大。如果主键是BIGINT8 字节每个二级索引条目额外存储 8 字节如果没有主键使用_rowid6 字节看起来更小——但这意味着你完全失去了对索引大小的主动控制更严重的是如果你错误地选择了一个长字段如VARCHAR(255)的 UUID作为主键所有二级索引都会被严重膨胀。-- 反例用 UUID 做主键CREATETABLEbad_example(idCHAR(36)PRIMARYKEY,-- 36 字节nameVARCHAR(50),INDEXidx_name(name));-- idx_name 的每个条目都要额外存储 36 字节的主键值-- 相比 BIGINT 主键索引膨胀 4.5 倍危害三数据物理排列无序产生大量随机 I/O没有主键时数据按_rowid的生成顺序存储而非业务逻辑顺序。这意味着范围查询无法利用物理相邻性产生大量随机磁盘 I/O批量插入时可能频繁触发页分裂Page Split数据碎片化严重OPTIMIZE TABLE也无法根治-- 有主键且自增顺序插入磁盘 I/O 友好CREATETABLElogs(idBIGINTAUTO_INCREMENTPRIMARYKEY,log_timeDATETIME,messageTEXT);-- 没有主键插入顺序不可控碎片化严重CREATETABLElogs_no_pk(log_timeDATETIME,messageTEXT);危害四无法高效执行REPLACE INTO和INSERT ... ON DUPLICATE KEY UPDATE这两个语句依赖主键或唯一索引来判断重复。没有主键时行为不可预期。危害五ORM 框架和工具链的兼容性问题绝大多数 ORM 框架Hibernate、MyBatis-Plus、Django ORM都要求表有主键才能正常工作。没有主键会导致无法使用save()/update()等便捷方法代码生成工具无法生成实体类数据库迁移工具报错此外常用的运维工具也依赖主键pt-table-checksum/pt-table-syncPercona Toolkit强制要求表有主键pt-online-schema-change在线 DDL 工具依赖主键进行数据拷贝mysqldump逻辑备份在无主键时效率降低四、主从复制架构中的严重问题没有主键在主从复制场景下会引发一系列严重问题这是生产环境中最大的隐患。问题一Row-Based ReplicationRBR性能严重下降MySQL 推荐使用 Row-Based Replicationbinlog_formatROW。在 RBR 模式下UPDATE 和 DELETE 操作需要在 binlog 中记录被修改行的前后镜像用于在从库上定位和执行相同的行。有主键时binlog 只需记录主键值即可定位行数据量小效率高。-- 有主键binlog 记录-- UPDATE t SET namenew WHERE pk123-- 只需记录主键值 123没有主键时binlog 必须记录所有列的值来定位行因为没有其他方式唯一标识一行。-- 没有主键binlog 记录-- UPDATE t SET namenew-- WHERE col1xxx AND col2yyy AND col3zzz ...-- 必须记录所有列的前镜像和后镜像这会导致binlog 体积暴增每个 DML 操作的 binlog 量可能是有主键情况下的 2-10 倍网络带宽消耗增大主从之间的 binlog 传输量大幅增加从库回放延迟从库 SQL 线程需要解析更多数据复制延迟增大问题二从库回放 UPDATE/DELETE 时全表扫描在从库上执行UPDATE或DELETE时如果没有主键从库无法精确定位要修改的行可能退化为全表扫描。-- 主库执行UPDATEusersSETstatus1WHEREemailtestexample.com;-- 如果 users 表有主键从库通过 binlog 中的主键值直接定位行-- 如果 users 表没有主键从库需要扫描整张表来找到匹配的行-- 大表场景下这可能导致从库延迟数分钟甚至数小时可以通过slave_rows_search_algorithms参数控制从库搜索行的方式算法说明INDEX_SCAN使用索引查找有主键时首选TABLE_SCAN全表扫描最慢无主键时的兜底HASH_SCAN哈希查找无主键时比全表扫描快-- 查看当前设置SHOWVARIABLESLIKEslave_rows_search_algorithms;-- 设置为优先使用索引其次哈希SETGLOBALslave_rows_search_algorithmsINDEX_SCAN,HASH_SCAN;HASH_SCAN可以显著改善无主键表在从库的回放性能但仍不如有主键的情况。并行复制MTS对无主键表的回放是否有作用有作用但效果有限且存在上限。MySQL 5.7 的并行复制Multi-Threaded Slave工作在事务级别——多个 worker 线程可以同时执行不同的事务。但无主键表的性能瓶颈在于每个 DML 操作本身的全表扫描开销这是并行复制无法消除的。具体分析场景并行复制是否有帮助说明多个事务操作不同表有帮助不同表的事务可以并行执行互不影响多个事务操作同一张无主键表帮助有限全表扫描本身耗时长并行执行多个全表扫描反而可能加剧磁盘 I/O 争用多个事务操作同一行无帮助行级冲突导致必须串行执行并行度降为 1-- 假设从库有两个 worker 线程同时收到两个事务-- 事务1UPDATE no_pk_table SET col1 WHERE id100 -- 需要全表扫描-- 事务2UPDATE no_pk_table SET col2 WHERE id200 -- 需要全表扫描-- 有主键两个事务通过主键定位行各自 O(logN)可以快速并行完成-- 无主键两个事务各自触发全表扫描磁盘 I/O 互相竞争并行收益被抵消结论并行复制解决的是事务间的并行度问题而无主键的瓶颈是单个事务的执行效率问题。两者是不同维度不能互相替代。先加主键再谈并行复制。问题三数据不一致风险增大没有主键时主从之间的数据一致性更难保证场景一重复行问题-- 没有唯一约束的表CREATETABLElogs_no_pk(log_timeDATETIME,messageVARCHAR(200));-- 插入两条完全相同的行INSERTINTOlogs_no_pkVALUES(2026-06-07 10:00:00,test);INSERTINTOlogs_no_pkVALUES(2026-06-07 10:00:00,test);-- 在主库上 DELETE 其中一条-- 从库无法区分要删除哪一条可能删错行导致主从数据不一致场景二GTID 模式下的冲突在 GTID 复制模式下每个事务都有全局唯一的 GTID。如果没有主键事务在从库上重放时可能因为无法精确定位行而产生冲突导致复制中断。问题四并行复制MTS效率降低MySQL 5.7 支持基于逻辑时钟的并行复制slave_parallel_typeLOGICAL_CLOCK。并行复制的核心是判断事务之间是否存在冲突而冲突判断依赖于行的唯一标识。没有主键时无法高效判断两个事务是否操作同一行并行复制的并发度降低从库回放速度下降在高写入场景下从库延迟会显著增大问题五半同步复制和组复制的风险在半同步复制Semi-Sync Replication和组复制Group Replication中数据一致性要求更高半同步复制主库等待至少一个从库确认收到 binlog 才返回。没有主键时binlog 体积增大确认延迟增大主库写入性能下降。组复制组内成员之间通过行的唯一标识来检测冲突。没有主键时冲突检测效率降低可能导致事务回滚率上升。五、最佳实践1. 每张表必须有显式主键CREATETABLEexample(idBIGINTNOTNULLAUTO_INCREMENTPRIMARYKEY,-- 业务字段created_atDATETIMEDEFAULTCURRENT_TIMESTAMP);1.1 MySQL 8.0.26 安全网自动生成不可见主键MySQL 8.0.26 引入了sql_generate_invisible_primary_key参数。开启后对于没有主键的表MySQL 会自动创建一个不可见的主键列my_row_id作为兜底措施。-- 查看当前设置SHOWVARIABLESLIKEsql_generate_invisible_primary_key;-- 开启建议在无法强制所有表加主键的场景下开启SETGLOBALsql_generate_invisible_primary_keyON;注意这只是安全网不应替代显式主键。自动生成的不可见主键无法被应用层使用且列名固定为my_row_id。2. 推荐使用自增整数作为主键主键类型优点缺点适用场景AUTO_INCREMENT BIGINT顺序插入、索引紧凑、范围查询友好分布式环境需要额外方案单库场景首选雪花算法 ID分布式唯一、趋势递增非严格自增可能有少量乱序分布式场景UUID全局唯一、无需协调36字节、完全随机、索引膨胀严重不推荐做主键3. 分布式场景的主键方案-- 方案一使用 BIGINT 自增 步长-- 节点1auto_increment_offset1, auto_increment_increment3-- 节点2auto_increment_offset2, auto_increment_increment3-- 方案二使用雪花算法生成的 BIGINT ID推荐CREATETABLEdistributed_table(idBIGINTNOTNULLPRIMARYKEY,-- 由应用层雪花算法生成-- 业务字段);-- 方案三使用 MySQL 8.0 的窗口函数生成行号仅做参考不推荐4. 检查现有表是否缺少主键-- 查找没有主键的表SELECTt.TABLE_SCHEMA,t.TABLE_NAMEFROMinformation_schema.TABLEStLEFTJOINinformation_schema.TABLE_CONSTRAINTS tcONt.TABLE_SCHEMAtc.TABLE_SCHEMAANDt.TABLE_NAMEtc.TABLE_NAMEANDtc.CONSTRAINT_TYPEPRIMARY KEYWHEREtc.CONSTRAINT_NAMEISNULLANDt.TABLE_SCHEMANOTIN(mysql,information_schema,performance_schema,sys)ANDt.TABLE_TYPEBASE TABLE;六、总结维度有主键无主键数据定位精确定位O(logN)回退为_rowid不可控索引大小可控主键越小越好膨胀或不可控物理存储顺序排列I/O 友好无序碎片化严重binlog 体积小仅记录主键大记录所有列主从延迟低高可能数小时数据一致性强行唯一标识弱重复行风险并行复制高效效率降低ORM 兼容完全兼容大量问题一句话总结主键不只是一个约束它是 InnoDB 存储引擎的核心机制。没有主键的表就像没有地址的房子——快递员复制线程永远找不到门。
