本文目录导读：
在PHP项目中，避免新增索引时锁表影响，核心思路是使用数据库支持的在线DDL（数据定义语言）操作，不同的数据库（MySQL、PostgreSQL等）实现方式不同，以下以最常用的 MySQL 为例详细说明：
核心原则：使用 Online DDL
MySQL 5.6+ 的 ALGORITHM 和 LOCK 子句
-- 方式1：最安全，允许并发DML ALTER TABLE your_table ADD INDEX idx_column (column_name), ALGORITHM=INPLACE, LOCK=NONE; -- 方式2：简写，MySQL会自动选择最优方式 ALTER TABLE your_table ADD INDEX idx_column (column_name);
不同版本支持情况
| MySQL版本 | 可用算法 | 是否锁表 |
|---|---|---|
| 5及以下 | 仅COPY | 是（全程锁表） |
| 6 | INPLACE（部分场景） | 部分支持 |
| 7+ | INPLACE + INSTANT | 大部分场景无锁 |
| 0+ | INSTANT（新增列等） | 完全无锁 |
具体实现方案
方案1：PHP代码中执行 Online DDL
<?php
// 使用PDO执行无锁DDL
try {
$pdo = new PDO('mysql:host=localhost;dbname=your_db', 'user', 'pass');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// 关键：使用 ALGORITHM=INPLACE, LOCK=NONE
$sql = "ALTER TABLE orders
ADD INDEX idx_user_id (user_id),
ALGORITHM=INPLACE,
LOCK=NONE";
$pdo->exec($sql);
echo "索引创建成功（无锁）";
} catch (PDOException $e) {
echo "执行失败: " . $e->getMessage();
}
方案2：使用工具避免锁表（大表推荐）
对于超大型表（千万级+），即使Online DDL也可能有短暂影响,推荐：
a. pt-online-schema-change（Percona Toolkit）
# 命令行执行（可通过PHP exec调用） pt-online-schema-change \ --alter "ADD INDEX idx_email (email)" \ D=your_db,t=users \ --execute \ --chunk-size=500 \ --max-lag=5
b. gh-ost（GitHub开源）
gh-ost \ --host=localhost \ --database=your_db \ --table=orders \ --alter="ADD INDEX idx_status (status)" \ --execute
方案3：分阶段低峰期执行（最稳妥）
<?php
// 在业务代码中检测当前连接是否繁忙
class SafeIndexCreator {
private $pdo;
public function __construct($pdo) {
$this->pdo = $pdo;
}
public function tryAddIndex($table, $indexName, $columns) {
// 检查当前数据库负载
$load = $this->getCurrentLoad();
if ($load > 70) {
// 负载过高，推迟执行
$this->scheduleForLater($table, $indexName, $columns);
return false;
}
try {
$sql = "ALTER TABLE `{$table}`
ADD INDEX `{$indexName}` ({$columns}),
ALGORITHM=INPLACE,
LOCK=NONE";
$this->pdo->exec($sql);
return true;
} catch (Exception $e) {
// 如果失败（如不支持INPLACE），自动降级
$this->fallbackToLowPeak($table, $indexName, $columns);
}
}
private function getCurrentLoad() {
$stmt = $this->pdo->query("SHOW GLOBAL STATUS LIKE 'Threads_running'");
return (int)$stmt->fetchColumn(1);
}
}
不同数据库的适配
PostgreSQL（完美无锁）
// Postgres的CREATE INDEX CONCURRENTLY天生无锁
$pdo->exec("CREATE INDEX CONCURRENTLY idx_email ON users(email)");
MariaDB（与MySQL类似）
// MariaDB 10.3+ 支持
$pdo->exec("ALTER TABLE users ADD INDEX idx_email (email), ALGORITHM=INPLACE, LOCK=NONE");
注意事项与最佳实践
必须避免的操作
// ❌ 错误：会锁表
$pdo->exec("ALTER TABLE users ADD INDEX idx_name (name)");
// ❌ 错误：在事务中执行DDL
$pdo->beginTransaction();
$pdo->exec("ALTER TABLE ..."); // 很多数据库事务中DDL会隐式提交
$pdo->commit();
监控DDL进度
-- MySQL 5.7+ 查看DDL进度 SELECT * FROM information_schema.INNODB_ALTER_TABLE_PROGRESS; -- 或使用SHOW PROCESSLIST查看 SHOW PROCESSLIST;
处理特殊情况
// 字段有外键约束时，需要特殊处理
try {
$pdo->exec("ALTER TABLE orders
ADD INDEX idx_user_id (user_id),
ALGORITHM=INPLACE,
LOCK=NONE");
} catch (PDOException $e) {
// 如果失败，可能是外键限制，尝试：
$pdo->exec("SET FOREIGN_KEY_CHECKS=0");
// 重新执行DDL
$pdo->exec("ALTER TABLE orders ...");
$pdo->exec("SET FOREIGN_KEY_CHECKS=1");
}
生产环境执行策略
class ProductionIndexMigration {
public function safeAddIndex() {
// 1. 检查MySQL版本
$version = $this->pdo->query("SELECT VERSION()")->fetchColumn();
// 2. 根据版本选择策略
if (version_compare($version, '5.6.0', '>=')) {
$this->onlineDDL();
} else {
// 老版本：使用工具或低峰期
$this->useToolOrScheduled();
}
// 3. 验证索引已创建
$this->verifyIndex();
}
}
监控与回滚方案
监控脚本
// 监控DDL执行状态
while (true) {
$status = $pdo->query("
SELECT STATE, PROGRESS
FROM information_schema.INNODB_ALTER_TABLE_PROGRESS
")->fetch(PDO::FETCH_ASSOC);
if ($status['STATE'] == 'DONE') break;
echo "Progress: {$status['PROGRESS']}%\n";
sleep(5);
}
紧急回滚
-- 如果DDL正在执行且不想等待
KILL QUERY {process_id};
-- 使用pt-online-schema-change时
pt-online-schema-change --stop
选择策略
| 表大小 | 推荐方案 | 复杂度 |
|---|---|---|
| < 100万行 | 直接Online DDL (ALGORITHM=INPLACE) | 低 |
| 100万-1000万 | Online DDL + 低峰期 | 中 |
| > 1000万 | pt-osc / gh-ost 工具 | 高 |
| 任何大小，完全无锁 | PostgreSQL (CREATE INDEX CONCURRENTLY) | 低 |
最重要的经验：永远不要在业务高峰期执行DDL操作；在生产环境执行前,先在测试环境模拟相同的数据量和负载验证。
