PHP项目索引新增如何避免锁表影响

wen PHP项目 2026-06-29 230

本文目录导读：

PHP项目索引新增如何避免锁表影响

核心原则：使用 Online DDL
具体实现方案
不同数据库的适配
注意事项与最佳实践
监控与回滚方案
选择策略

在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)	低