Symfony5项目Doctrine外键与索引异常修复及原因咨询

接手一个基于Symfony 5、Doctrine和MySQL 5.6的项目，执行doctrine:schema:update --dump-sql后输出大量添加外键约束的ALTER语句，示例如下：

ALTER TABLE user_show_access ADD CONSTRAINT FK_2015CD5AD0C1FC64 FOREIGN KEY (show_id) REFERENCES shows (id);
ALTER TABLE user_show_access ADD CONSTRAINT FK_2015CD5AA76ED395 FOREIGN KEY (user_id) REFERENCES users (id);
ALTER TABLE user_track_access ADD CONSTRAINT FK_CE6796BFA76ED395 FOREIGN KEY (user_id) REFERENCES users (id);
ALTER TABLE user_track_access ADD CONSTRAINT FK_CE6796BF5ED23C43 FOREIGN KEY (track_id) REFERENCES tracks (id);
ALTER TABLE tracks_video ADD CONSTRAINT FK_B620A4B1DE12AB56 FOREIGN KEY (created_by) REFERENCES users (id);
ALTER TABLE tracks_video ADD CONSTRAINT FK_B620A4B116FE72E1 FOREIGN KEY (updated_by) REFERENCES users (id)

直接在数据库执行这些语句时报错：

[2023-08-31 13:25:12] [HY000][1215] Cannot add foreign key constraint
[2023-08-31 13:25:12] [HY000][150] Create table '{xxx}/#sql-859_42977d4' with foreign key constraint failed. There is no index in the referenced table where the referenced columns appear as the first columns.

本地新建干净数据库执行这些语句完全正常，推测是前任开发手动修改过数据库导致异常。同时当前数据库查询速度极慢（最快查询也需600ms以上），推测修复外键问题能提升性能。已确认users表id是主键，附干净数据库中相关表的创建语句：

users表：

CREATE TABLE users (id CHAR(36) NOT NULL COMMENT '(DC2Type:guid)', user_avatar_id CHAR(36) DEFAULT NULL COMMENT '(DC2Type:guid)', email VARCHAR(180) NOT NULL, initialized TINYINT(1) NOT NULL, password VARCHAR(255) DEFAULT NULL, first_name VARCHAR(255) DEFAULT NULL, last_name VARCHAR(255) DEFAULT NULL, remote_id VARCHAR(255) DEFAULT NULL, facebook_id BIGINT DEFAULT NULL, avatar_url VARCHAR(250) DEFAULT NULL, birthday DATETIME DEFAULT NULL, gender VARCHAR(255) DEFAULT NULL, city VARCHAR(255) DEFAULT NULL, geo_latitude DOUBLE PRECISION DEFAULT NULL, geo_longitude DOUBLE PRECISION DEFAULT NULL, google_id VARCHAR(250) DEFAULT NULL, google_url VARCHAR(250) DEFAULT NULL, email_verified TINYINT(1) NOT NULL, news_duration INT DEFAULT NULL, you_id INT DEFAULT NULL, microsoft_id VARCHAR(255) DEFAULT NULL, podcastovatko_id INT DEFAULT NULL, is_active TINYINT(1) NOT NULL, created_at DATETIME NOT NULL, updated_at DATETIME DEFAULT NULL, UNIQUE INDEX UNIQ_1483A5E9E7927C74 (email), UNIQUE INDEX UNIQ_1483A5E99BE8FD98 (facebook_id), UNIQUE INDEX UNIQ_1483A5E976F5C865 (google_id), UNIQUE INDEX UNIQ_1483A5E986D8B6F4 (user_avatar_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

user_show_access表：

CREATE TABLE user_show_access (id CHAR(36) NOT NULL COMMENT '(DC2Type:guid)', user_id CHAR(36) DEFAULT NULL COMMENT '(DC2Type:guid)', show_id CHAR(36) DEFAULT NULL COMMENT '(DC2Type:guid)', valid_to DATETIME NOT NULL, INDEX IDX_2015CD5AA76ED395 (user_id), INDEX IDX_2015CD5AD0C1FC64 (show_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB;

待添加的约束语句：

ALTER TABLE users ADD CONSTRAINT FK_1483A5E986D8B6F4 FOREIGN KEY (user_avatar_id) REFERENCES users_avatars (id) ON DELETE CASCADE;
ALTER TABLE user_show_access ADD CONSTRAINT FK_2015CD5AA76ED395 FOREIGN KEY (user_id) REFERENCES users (id);
ALTER TABLE user_show_access ADD CONSTRAINT FK_2015CD5AD0C1FC64 FOREIGN KEY (show_id) REFERENCES shows (id);

核心问题：

1. 如何修复该问题？是否可以重置数据库外键让Doctrine自动处理？
2. 哪些操作会导致数据库进入这种异常状态？

1. 修复外键约束问题 #

方法一：逐步排查修复 #

根据报错信息，核心问题是被引用表中缺少以被引用列为首列的索引，或子表存在脏数据：

• 检查被引用表的索引：逐个验证每个外键对应的父表，比如show_id引用shows(id)，需确认shows表的id是主键（主键自带索引），或存在以id为首列的普通索引；如果缺失，手动添加：

  CREATE INDEX IDX_SHOWS_ID ON shows(id);
  

• 清理脏数据：子表中存在父表没有的关联值也会导致外键创建失败，比如清理user_show_access中无效的user_id：

  DELETE FROM user_show_access WHERE user_id NOT IN (SELECT id FROM users);
  

• 完成上述操作后，再执行Doctrine生成的ALTER语句。

方法二：重置数据库结构（需备份数据） #

如果手动排查效率低，可重置结构让Doctrine重新生成：

1. 导出当前数据库数据（仅数据，不含结构）：

   mysqldump -u [用户名] -p [数据库名] --no-create-info > backup_data.sql
   

2. 删除现有数据库，通过Doctrine创建干净结构：

   php bin/console doctrine:schema:create
   

3. 导入备份数据（需临时禁用外键检查）：

   SET FOREIGN_KEY_CHECKS=0;
   -- 执行导入命令：mysql -u [用户名] -p [数据库名] < backup_data.sql
   SET FOREIGN_KEY_CHECKS=1;
   

4. 最后执行doctrine:schema:update --force确保约束全部生成。

方法三：用Doctrine校验定位问题 #

执行命令让Doctrine检测实体映射与数据库结构的差异：

php bin/console doctrine:schema:validate

它会输出具体不匹配项（如缺失索引、字段类型不一致），按提示逐一修复即可。

2. 导致数据库异常的常见操作 #

• 手动修改数据库结构：比如直接删除被引用表的索引，或修改字段类型（如把CHAR(36)改为VARCHAR(36)，导致外键类型不兼容）；
• 跳过Doctrine直接操作数据库：未通过Doctrine迁移或schema命令，手动创建表/字段，导致实体映射与实际结构脱节；
• 手动删除外键约束：临时删除外键后未同步更新实体映射；
• 导入数据时禁用外键检查未恢复：导入脏数据后未清理无效关联，导致后续无法添加外键；
• 修改实体映射但未更新数据库：添加关联后未执行schema:update，又手动修改数据库，造成映射与结构不一致。

缺失外键对应的索引是查询缓慢的核心原因（Doctrine创建外键时会自动生成索引，若手动删除则无法利用索引加速）。修复外键约束的同时，对应的索引会被正确创建，能大幅降低关联查询的耗时。

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