MySQL建索引遇1034错误求助：大表索引损坏排查及构建原理

在MySQL中开展TPCC测试时，执行以下SQL添加主键索引时触发报错：

ERROR 1034 (HY000) at line 3: Index for table 'stock' is corrupt; try to repair it

执行的SQL代码：

use tpccrunner1000;

alter table stock add constraint pk_stock
  primary key (s_w_id, s_i_id);

alter table item add constraint pk_item
  primary key (i_id);

其中stock为包含1亿行数据的大表，表体积超过35GB。尝试调整innodb_buffer_pool_size参数后问题未得到解决。需解决该报错以完成索引创建，并了解MySQL索引的构建原理。

补充信息：已提供SHOW CREATE TABLE stock结果截图、磁盘使用情况截图、表大小信息截图。

1. 修复损坏的表（针对InnoDB引擎） #

InnoDB引擎不支持REPAIR TABLE命令，需通过强制恢复+表重建的方式处理：

• 停止MySQL服务
• 在配置文件（my.cnf/my.ini）中添加：innodb_force_recovery = 1（建议从1开始尝试，数值越高风险越大，最高为6）
• 启动MySQL服务，执行ALTER TABLE stock ENGINE=InnoDB; 重建表及索引
• 修复完成后，移除innodb_force_recovery配置，重启MySQL

2. 大表创建索引的优化方案 #

针对35GB级别的大表，直接执行ALTER TABLE易引发资源耗尽或损坏问题，推荐以下方案：

• 使用Online DDL（MySQL 5.6+支持）：添加参数避免锁表，降低阻塞风险：

  ALTER TABLE stock ADD CONSTRAINT pk_stock PRIMARY KEY (s_w_id, s_i_id) ALGORITHM=INPLACE, LOCK=NONE;
  

• 分批迁移重建：
  1. 创建与stock结构一致的临时表stock_temp，并预先创建好主键索引
  2. 分批将原表数据插入临时表，例如：

     INSERT INTO stock_temp SELECT * FROM stock WHERE s_w_id BETWEEN 1 AND 1000;
     

     （根据数据分布调整分批范围，避免单次插入数据量过大）
  3. 数据迁移完成后，交换表名：

     RENAME TABLE stock TO stock_old, stock_temp TO stock;
     

• 检查磁盘空间：确保磁盘剩余空间至少为表体积的1.5倍，避免因空间不足导致索引构建失败。

3. 根源排查 #

• 查看MySQL错误日志，确认是否存在磁盘IO异常、内存不足等报错，这些可能是索引损坏的诱因
• 检查磁盘健康状态，排查是否存在坏道等硬件问题

InnoDB的主键索引为聚簇索引，构建流程如下：

1. 空间初始化：MySQL为新索引分配磁盘空间，初始化B+树的结构框架
2. 数据扫描与排序：全表扫描数据行，按照主键列（s_w_id, s_i_id）的组合值进行排序
3. B+树插入：将排序后的数据插入B+树，叶子节点存储完整的行数据（聚簇索引特性），非叶子节点存储主键值及子节点指针
4. 事务一致性保障：构建过程中通过undo日志、redo日志保证原子性与持久性，若中途中断，可通过日志回滚或恢复
5. 大表适配：大表构建索引时，InnoDB会分批处理数据，利用缓冲池缓存部分数据以减少磁盘IO，但缓冲池不足时会频繁读写磁盘，增加失败风险

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