您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 重複索引檢測與清理：pt-duplicate-key-checker 實戰指南

編輯：MySQL 綜合教學

MySQL 重複索引檢測與清理：pt-duplicate-key-checker 實戰指南

MySQL 允許在相同欄位上重複建立索引 (Oracle 不允許)，導致寫入效能下降、空間浪費、優化器混淆。Percona Toolkit `pt-duplicate-key-checker` 可自動掃描並產生清理 DDL。

安裝 Percona Toolkit

# RHEL/CentOS
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
yum install percona-toolkit -y

# Ubuntu/Debian
wget https://repo.percona.com/apt/percona-release_latest.generic_all.deb
dpkg -i percona-release_latest.generic_all.deb
apt-get update
apt-get install percona-toolkit -y

執行檢測

pt-duplicate-key-checker \
  --host=10.10.11.208 \
  --user=root \
  --password=root \
  --port=3316 \
  --charset=gbk \
  --databases=test  # 可選，指定資料庫

輸出解讀與清理範例

########################################################################
# test.app_chat_message
########################################################################
# idx_app_chat_message_to_user_from_user is a left-prefix of Index 3
# Key definitions:
# KEY `idx_app_chat_message_to_user_from_user` (`to_user`,`from_user`),
# KEY `Index 3` (`to_user`,`from_user`,`message_id`),
# To remove this duplicate index, execute:
ALTER TABLE `test`.`app_chat_message` DROP INDEX `idx_app_chat_message_to_user_from_user`;

# Index 2 is a left-prefix of Index 3
# KEY `Index 2` (`to_user`,`from_user`),
# KEY `Index 3` (`to_user`,`from_user`,`message_id`),
ALTER TABLE `test`.`app_chat_message` DROP INDEX `Index 2`;

########################################################################
# test.pub_user
########################################################################
# phone is a duplicate of phone_idx
# KEY `phone` (`phone`), KEY `phone_idx` (`phone`)
ALTER TABLE `test`.`pub_user` DROP INDEX `phone`;

# email is a duplicate of email_idx
ALTER TABLE `test`.`pub_user` DROP INDEX `email`;

# account is a duplicate of account_idx
ALTER TABLE `test`.`pub_user` DROP INDEX `account`;

重複索引類型判定邏輯

類型	定義	範例	處理
完全重複	欄位順序、數量完全相同	KEY `a`(c1), KEY `b`(c1)	刪除任一
左前綴重複	索引 A 欄位是索引 B 的左側前綴	KEY `a`(c1,c2), KEY `b`(c1,c2,c3)	刪除較短索引 A
主鍵/唯一鍵重複	主鍵/唯一鍵包含於普通索引	PRIMARY KEY(c1), KEY `a`(c1)	刪除普通索引

執行清理前檢查清單

備份：`mysqldump --single-transaction db tbl > backup.sql`
確認業務無隱性依賴：某些 ORM/框架可能指定索引名 (Hint)
低峰期執行：`DROP INDEX` 會重建表 (MDL 鎖、IO 消耗)
大表用 pt-online-schema-change：零停機重建

大表零停機清理範例

pt-online-schema-change \
  --alter "DROP INDEX \`idx_dup\`" \
  D=test,t=app_chat_message \
  --execute \
  --max-load Threads_running=50 \
  --critical-load Threads_running=100 \
  --chunk-time 0.5

定期掃描腳本化

#!/bin/bash
# 每月掃描一次
pt-duplicate-key-checker \
  --user=root --password=xxx --host=127.0.0.1 \
  --no-summary \
  --tables-regex='^(?!mysql\.)' \
  > /var/log/dup_idx_$(date +%F).log 2>&1

# 郵件通知有輸出時
if [ -s /var/log/dup_idx_$(date +%F).log ]; then
  mail -s "MySQL 重複索引檢測報告" dba@company.com < /var/log/dup_idx_$(date +%F).log
fi

統計摘要解讀

# Size Duplicate Indexes 20358902596  -- 重複索引佔用空間 ~20GB
# Total Duplicate Indexes 17           -- 重複索引總數
# Total Indexes 664                    -- 總索引數

此例中 2.5% 的索引為重複，佔用 20GB 空間，清理後可顯著降低寫入放大與備份時間。