PHP项目冗余索引如何识别批量删除清理

wen PHP项目 2026-06-26 159

本文目录导读：

PHP项目冗余索引如何识别批量删除清理

核心原则：先识别，再备份，后删除
第一步：使用 SQL 查询识别冗余索引
第二步：使用 PHP 脚本批量生成删除语句
第三步：手动复核与安全删除
第四步：使用 Percona Toolkit（推荐）
总结建议

在 PHP 项目中识别和批量删除冗余索引，可以遵循以下步骤，这里提供一个安全、可操作的流程，主要基于 MySQL（最常用）,但思路也适用于其他数据库。

核心原则：先识别，再备份，后删除

千万不要直接在线上库执行删除,冗余索引的判断标准通常是：

最左前缀冗余：index(a,b) 包含了 index(a),后者是冗余的。
主键与唯一索引冗余：id 是主键，且有一个唯一索引 uniq(id)，uniq(id) 是冗余的。
过长前缀冗余：index(a) 和 index(a(10)) 同时存在。
未被使用的索引：根据 performance_schema 或慢查询日志,长期未被使用的索引。

第一步：使用 SQL 查询识别冗余索引

注意：MySQL 5.7+ / 8.0 没有内置的“冗余索引列表”视图,需要我们自己组合。

脚本 1：查找完全重复或前缀重复的索引

SELECT
    a.TABLE_SCHEMA AS '数据库',
    a.TABLE_NAME AS '表名',
    a.INDEX_NAME AS '冗余索引名',
    a.COLUMN_NAME AS '冗余列',
    a.SEQ_IN_INDEX AS '列序号',
    b.INDEX_NAME AS '可被替代的索引名',
    b.COLUMN_NAME AS '替代索引列'
FROM
    information_schema.STATISTICS a
        INNER JOIN information_schema.STATISTICS b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
        AND a.TABLE_NAME = b.TABLE_NAME
        AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX   -- 同一位置的列相同
        AND a.COLUMN_NAME = b.COLUMN_NAME     -- 列名相同
        AND a.INDEX_NAME != b.INDEX_NAME      -- 不同索引
WHERE
    a.NON_UNIQUE = 1   -- 只关注非唯一索引（冗余主要发生在非唯一上）
    AND a.INDEX_NAME NOT LIKE 'PRIMARY'
    AND b.INDEX_NAME NOT LIKE 'PRIMARY'
ORDER BY
    a.TABLE_SCHEMA, a.TABLE_NAME, a.INDEX_NAME, a.SEQ_IN_INDEX;

解释：

这个查询会找到那些第一列完全相同的索引对。
index(a,b) 和 index(a) 都存在，它们的第一列都是 a,就会被标记。
注意：这个查询只能识别“第一列相同”的冗余。index(a,b) 和 index(b) 不会被识别（这是另一种冗余）。

脚本 2：更精确的“前缀包含”检测

-- 查找所有组合索引，并检查是否有更短的索引包含了它的前几列
SELECT
    r.TABLE_SCHEMA,
    r.TABLE_NAME,
    r.INDEX_NAME AS '冗余索引',
    r.COLUMN_NAME,
    r.SEQ_IN_INDEX,
    k.INDEX_NAME AS '替代索引'
FROM information_schema.STATISTICS r
JOIN information_schema.STATISTICS k
    ON r.TABLE_SCHEMA = k.TABLE_SCHEMA
    AND r.TABLE_NAME = k.TABLE_NAME
    AND r.INDEX_NAME != k.INDEX_NAME
    AND r.SEQ_IN_INDEX = k.SEQ_IN_INDEX
    AND r.COLUMN_NAME = k.COLUMN_NAME
WHERE
    r.NON_UNIQUE = 1
    AND k.NON_UNIQUE = 1
    AND r.INDEX_NAME NOT LIKE 'PRIMARY'
    AND k.INDEX_NAME NOT LIKE 'PRIMARY'
    -- 过滤：只显示组合索引 (SEQ_IN_INDEX > 1) 的子索引
    AND EXISTS (
        SELECT 1 FROM information_schema.STATISTICS sub
        WHERE sub.TABLE_SCHEMA = r.TABLE_SCHEMA
          AND sub.TABLE_NAME = r.TABLE_NAME
          AND sub.INDEX_NAME = r.INDEX_NAME
          AND sub.SEQ_IN_INDEX = 1
    )
    AND EXISTS (
        SELECT 1 FROM information_schema.STATISTICS sub2
        WHERE sub2.TABLE_SCHEMA = k.TABLE_SCHEMA
          AND sub2.TABLE_NAME = k.TABLE_NAME
          AND sub2.INDEX_NAME = k.INDEX_NAME
          AND sub2.SEQ_IN_INDEX = 1
          AND sub2.COLUMN_NAME = r.COLUMN_NAME
    )
ORDER BY r.TABLE_SCHEMA, r.TABLE_NAME, r.INDEX_NAME, r.SEQ_IN_INDEX;

第二步：使用 PHP 脚本批量生成删除语句

上面的 SQL 查询会返回冗余索引的列表，你可以用 PHP 处理查询结果，并生成 DROP INDEX 语句。

示例 PHP 脚本 (`index_cleaner.php`)

<?php
/**
 * 冗余索引批量清理生成器
 * 用法: php index_cleaner.php
 * 注意: 此脚本默认只输出 SQL，不会直接执行，如要执行，需解开最后注释。
 */
$dbHost = '127.0.0.1';
$dbUser = 'root';
$dbPass = 'your_password';
$dbName = 'your_database'; // 如果需要指定数据库，或设为 null 扫描所有
try {
    $pdo = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8mb4", $dbUser, $dbPass);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    die("数据库连接失败: " . $e->getMessage());
}
// --- 1. 查询冗余索引（使用上面的第一个 SQL 简化版）---
$sqlRedundantIndexes = "
    SELECT 
        a.TABLE_SCHEMA,
        a.TABLE_NAME,
        a.INDEX_NAME AS redundant_index,
        GROUP_CONCAT(DISTINCT a.COLUMN_NAME ORDER BY a.SEQ_IN_INDEX SEPARATOR ', ') AS redundant_columns,
        b.INDEX_NAME AS suggested_index,
        GROUP_CONCAT(DISTINCT b.COLUMN_NAME ORDER BY b.SEQ_IN_INDEX SEPARATOR ', ') AS suggested_columns
    FROM information_schema.STATISTICS a
    INNER JOIN information_schema.STATISTICS b 
        ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
        AND a.TABLE_NAME = b.TABLE_NAME
        AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX
        AND a.COLUMN_NAME = b.COLUMN_NAME
        AND a.INDEX_NAME != b.INDEX_NAME
    WHERE 
        a.NON_UNIQUE = 1
        AND a.INDEX_NAME NOT LIKE 'PRIMARY'
        AND b.INDEX_NAME NOT LIKE 'PRIMARY'
        AND a.TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema','sys')
    GROUP BY 
        a.TABLE_SCHEMA, a.TABLE_NAME, a.INDEX_NAME, b.INDEX_NAME
    HAVING 
        COUNT(*) > 1   -- 至少有两列相同，才可能是真正的冗余（非唯一索引）
    ORDER BY 
        a.TABLE_SCHEMA, a.TABLE_NAME, a.INDEX_NAME;
";
$stmt = $pdo->query($sqlRedundantIndexes);
$redundantIndexes = $stmt->fetchAll(PDO::FETCH_ASSOC);
if (empty($redundantIndexes)) {
    echo "✅ 未检测到明显的冗余索引。\n";
    exit(0);
}
// --- 2. 去重并生成 SQL ---
$sqlStatements = [];
$processedPairs = []; // 防止重复生成 (表名+索引名)
foreach ($redundantIndexes as $index) {
    $db = $index['TABLE_SCHEMA'];
    $table = $index['TABLE_NAME'];
    $redundantIndexName = $index['redundant_index'];
    $pairKey = $db . '.' . $table . '.' . $redundantIndexName;
    // 避免为同一个索引生成多次
    if (isset($processedPairs[$pairKey])) {
        continue;
    }
    $processedPairs[$pairKey] = true;
    // 生成 DROP 语句
    $dropSQL = "ALTER TABLE `{$db}`.`{$table}` DROP INDEX `{$redundantIndexName}`;";
    $sqlStatements[] = [
        'sql' => $dropSQL,
        'reason' => "在 `{$db}`.`{$table}` 中，索引 `{$redundantIndexName}` ({$index['redundant_columns']}) 被 `{$index['suggested_index']}` ({$index['suggested_columns']}) 覆盖",
    ];
}
// --- 3. 输出预览 ---
echo "===============================\n";
echo "冗余索引清理计划（预览）\n";
echo "共发现 " . count($sqlStatements) . " 个可能冗余的索引\n";
echo "===============================\n\n";
foreach ($sqlStatements as $i => $item) {
    echo "[" . ($i+1) . "] " . $item['sql'] . "\n";
    echo "    原因: " . $item['reason'] . "\n\n";
}
// --- 4. 可选：批量执行（请谨慎！）---
// echo "\n是否执行这些 SQL？(yes/no): ";
// $input = trim(fgets(STDIN));
// if (strtolower($input) === 'yes') {
//     echo "开始执行...\n";
//     $pdo->beginTransaction();
//     try {
//         foreach ($sqlStatements as $item) {
//             echo "执行: " . $item['sql'] . "\n";
//             $pdo->exec($item['sql']);
//         }
//         $pdo->commit();
//         echo "✅ 所有索引删除成功！\n";
//     } catch (Exception $e) {
//         $pdo->rollBack();
//         echo "❌ 执行失败: " . $e->getMessage() . "\n";
//     }
// } else {
//     echo "已取消执行。\n";
// }

第三步：手动复核与安全删除

导出备份：在执行任何修改前,备份表结构：

mysqldump -u root -p --no-data your_database > db_schema_backup.sql

检查执行计划：针对要删除的索引，手动执行一个依赖它的查询的 EXPLAIN,确认删除后不会全表扫描。
逐步删除：如果索引很多，不要一次性全部删除,建议分批次：
- 每次删除 3-5 个索引。
- 观察一段时间（如 1 小时）的 CPU、慢查询日志。
- 确认无异常,再继续下一批。
代码层面检查：
- 搜索项目代码中是否使用 FORCE INDEX 或 USE INDEX 强制指定索引。
- 搜索有没有隐式索引（如 ORM 框架自动生成的索引名）。

第四步：使用 Percona Toolkit（推荐）

如果允许安装工具，pt-duplicate-key-checker 是行业标准：

# 安装
wget percona.com/get/pt-duplicate-key-checker
# 或使用 docker
docker run --rm perconalab/pt-duplicate-key-checker h=localhost,u=root,p=yourpass
# 生成删除 SQL （加上 --drop 参数会自动生成 ALTER TABLE DROP INDEX 语句）
pt-duplicate-key-checker --databases your_database --drop

它将直接输出类似：

#  ALTER TABLE `your_db`.`users` DROP INDEX `idx_username`;  # 冗余于 `uniq_username`

总结建议

最安全的方法：使用 pt-duplicate-key-checker 输出报告，由人工逐条确认后执行。
不要相信 100% 自动化：数据库的索引优化有时取决于具体的查询模式，工具无法完全理解业务逻辑（唯一索引虽然冗余，但可能是为了 INSERT ... ON DUPLICATE KEY UPDATE 等特定场景）。
保留唯一/主键：永远不要删除 PRIMAY KEY 或 UNIQUE 约束索引（除非你知道自己在做什么）。
监控：删除后，继续监控 performance_schema.table_io_waits_summary_by_index_usage 以确保没有索引被意外删除后导致性能下降。

通过以上步骤，你可以安全、系统化地清理 PHP 项目中的冗余索引。

本文地址： https://www.bmjup.com/post/9030.html