企业官网建设流程全解析

1. 索引创建的传统认知与锁表现象

第一次接触MySQL索引创建时，几乎所有DBA都会被告知一个"常识"：添加索引会导致表锁，进而阻塞所有读写操作。这种认知源于早期MySQL版本的实现机制，也确实在许多生产环境中造成过严重事故。

我至今记得五年前在某电商平台处理的一次事故：当时在订单表上添加一个普通索引，直接导致高峰期服务不可用近20分钟。通过SHOW PROCESSLIST可以看到大量"Waiting for table metadata lock"状态的连接，这正是典型的锁表症状。这种经历让很多开发者形成了"加索引=锁表=业务停摆"的思维定式。

2. Online DDL的技术演进

2.1 从Copy算法到In-Place算法

MySQL 5.6版本是个重要转折点，其引入的Online DDL机制彻底改变了索引操作的锁定行为。传统Copy算法需要：

1. 创建临时表并定义新结构
2. 逐行拷贝原表数据
3. 重命名表完成替换

这个过程会持有元数据锁（MDL），导致阻塞。而In-Place算法直接在原表空间操作，仅在某些阶段需要短暂锁表。

2.2 不同索引类型的锁差异

实测发现，在MySQL 8.0.23中：

• 添加普通二级索引：仅需MDL读锁（不阻塞读写）
• 添加全文索引：需要MDL写锁（阻塞写操作）
• 修改主键索引：仍需要重建表

通过以下命令可以验证：

-- 会话1执行 ALTER TABLE orders ADD INDEX idx_amount(order_amount); -- 会话2同时执行 SELECT * FROM orders WHERE user_id=100; -- 正常执行 UPDATE orders SET status=1 WHERE id=500; -- 正常执行

3. Online DDL的实现原理

3.1 元数据锁的优化

Online DDL的核心改进在于元数据锁的获取策略：

1. 准备阶段：获取MDL读锁，解析表结构
2. 执行阶段：
   • 先升级为MDL写锁（毫秒级）
   • 修改.frm文件
   • 立即降级回MDL读锁
3. 提交阶段：再次短暂获取MDL写锁更新数据字典

这种"快照"式锁策略将阻塞时间从秒级降到毫秒级。

3.2 增量日志的应用

InnoDB内部使用临时日志记录DDL期间的DML操作：

1. 创建临时日志文件（ibd$tmp）
2. 执行DDL时，将并发DML写入日志
3. DDL完成后应用日志变更

通过SHOW ENGINE INNODB STATUS可以看到：

LOG --- Log sequence number 24444144 Log buffer assigned up to 24444144

4. 生产环境实践指南

4.1 安全操作检查清单

执行前必须确认：

1. 版本要求：MySQL ≥5.6（推荐8.0+）
2. 存储引擎：仅InnoDB支持
3. 磁盘空间：至少需要原表1.2倍空间
4. 长事务检查：

   SELECT * FROM information_schema.innodb_trx WHERE TIME_TO_SEC(TIMEDIFF(NOW(), trx_started)) > 60;

4.2 性能影响监控

推荐监控指标：

1. 线程状态：

   SHOW PROCESSLIST;

2. 锁等待：

   SELECT * FROM performance_schema.events_waits_current;

3. 进度查看（8.0+）：

   SELECT * FROM performance_schema.events_stages_current;

5. 特殊场景处理方案

5.1 大表优化策略

对于TB级表建议：

1. 使用pt-online-schema-change工具
2. 分批次执行：

   ALTER TABLE huge_table ADD INDEX idx_partial(name(20)) ALGORITHM=INPLACE, LOCK=NONE;

3. 低峰期操作+throttle参数控制

5.2 唯一索引的注意事项

创建唯一索引时：

1. 需要全表扫描验证唯一性
2. 建议先确保字段无重复：

   SELECT COUNT(*) != COUNT(DISTINCT email) FROM users;

3. 8.0.16+支持SKIP_SCAN优化

6. 版本差异对比

各版本关键改进点：

7. 常见误区解析

7.1 "Online DDL完全不会锁表"

实际上：

• 初始化阶段需要短暂MDL写锁（毫秒级）
• 空间重组时可能阻塞并发DDL
• 唯一索引校验会加大锁冲突概率

7.2 "所有索引类型都支持Online"

限制条件包括：

1. 空间索引必须使用COPY算法
2. 降序索引需要8.0+版本
3. 虚拟列索引有表达式限制

8. 最佳实践建议

1. 始终明确指定算法：

   ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE;

2. 使用PERCONA工具校验：

   pt-checksum --replicate-check

3. 备份回滚方案：

   CREATE TABLE new_table LIKE original_table;