索引優化是資料庫效能調校的基本功，但在 Legacy PHP 系統中，它有幾個特別難搞的地方：
- 沒有 ORM——沒有
migrate指令，索引要手寫 SQL - 多租戶架構——幾十個資料庫要同步加索引
- 歷史包袱——十年來不同人加的索引，有些重複、有些缺失
- TEXT 欄位——MySQL 5.7 對 TEXT 欄位建索引有前綴長度限制
這篇記錄一次大規模索引優化的過程：一次性 23 個 ADD + 4 個 DROP，以及後續修正的踩坑經驗。
背景：為什麼突然要大規模加索引
起因是多個頁面的效能投訴。用 Claude Code 逐一分析慢查詢時，發現很多查詢的 EXPLAIN 結果都是 full table scan——不是查詢寫得差，是根本沒有對應的索引。
> 幫我分析這幾個慢查詢的 EXPLAIN 結果，找出缺少索引的地方Claude Code 讀完所有 EXPLAIN 結果後，列出了 23 個需要新增的索引和 4 個可以移除的冗餘索引。
安全的索引 Migration：safe_add_index
在多租戶架構下，你不能假設每個資料庫的 schema 都一樣。有些資料庫可能已經有某個索引（手動加過），有些可能連目標表都不存在。
Claude Code 產生的 migration SQL 用了一個安全的 wrapper：
-- 安全新增索引：表不存在不報錯、索引已存在不報錯
DELIMITER //
DROP PROCEDURE IF EXISTS safe_add_index//
CREATE PROCEDURE safe_add_index(
IN p_table VARCHAR(64),
IN p_index VARCHAR(64),
IN p_columns VARCHAR(255)
)
BEGIN
-- 1146: Table doesn't exist
-- 1061: Duplicate key name
DECLARE CONTINUE HANDLER FOR 1146 BEGIN END;
DECLARE CONTINUE HANDLER FOR 1061 BEGIN END;
SET @sql = CONCAT('ALTER TABLE `', p_table,
'` ADD INDEX `', p_index,
'` (', p_columns, ')');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END//
DELIMITER ;同樣也有 safe_drop_index：
DELIMITER //
DROP PROCEDURE IF EXISTS safe_drop_index//
CREATE PROCEDURE safe_drop_index(
IN p_table VARCHAR(64),
IN p_index VARCHAR(64)
)
BEGIN
-- 1146: Table doesn't exist
-- 1091: Can't drop index that doesn't exist
DECLARE CONTINUE HANDLER FOR 1146 BEGIN END;
DECLARE CONTINUE HANDLER FOR 1091 BEGIN END;
SET @sql = CONCAT('ALTER TABLE `', p_table,
'` DROP INDEX `', p_index, '`');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END//
DELIMITER ;這樣在多租戶環境下，同一份 SQL 可以安全地跑在所有資料庫上，不用擔心某個資料庫少了某張表或已經有某個索引。
23 個新增索引的分類
Claude Code 把索引按影響程度分成三個優先級：
A 級：直接影響使用者體驗的慢查詢
-- 排班頁面：每次載入都要掃全表
CALL safe_add_index('employer_shift', 'idx_date_empid',
'ShiftDate, EmployerID');
-- 費用建立紀錄：依收據查詢
CALL safe_add_index('feecreate_item', 'idx_receipt',
'receiptID, feeType');
-- 服務明細：按個案+日期查詢（17s → 3.4s）
CALL safe_add_index('service_detail_02', 'idx_hospno_servicedate',
'HospNo, servicedate');B 級：報表查詢效能
-- 保險申報紀錄
CALL safe_add_index('service_record_01', 'idx_hospno_admin',
'HospNo, adminID');
-- 費用明細：依項目名稱查詢
CALL safe_add_index('allfee', 'idx_parent',
'parentName, parentID');
-- 個人 I/O 紀錄
CALL safe_add_index('general_io', 'idx_person_date',
'PersonID, RecordDate');C 級：背景作業和定期報表
-- 表單提交紀錄
CALL safe_add_index('form_order_detail', 'idx_patient_form',
'patientID, formType');
-- 薪資計算用代碼對照
CALL safe_add_index('salary_code', 'idx_code_org',
'code, OrgID');4 個移除的冗餘索引
-- feecreate 的 idx_receiptID 跟 PRIMARY KEY 完全重複
CALL safe_drop_index('feecreate', 'idx_receiptID');
-- form_order 的 id UNIQUE INDEX 跟 PRIMARY KEY 重複
CALL safe_drop_index('form_order', 'id');
-- pinfolog 的 patientID 是 PK 前綴的子集
CALL safe_drop_index('pinfolog', 'patientID');
-- form_assess_17 的 Qmedicine 被新的複合索引取代
CALL safe_drop_index('form_assess_17', 'Qmedicine');Claude Code 怎麼判斷索引是冗餘的
> 列出所有表的索引，找出冗餘的（跟 PK 重複或被其他索引涵蓋的）Claude Code 比對每張表的所有索引定義，找出前綴重複的情況。例如 PK 是 (id)，又有一個 UNIQUE INDEX (id) —— 完全重複，浪費寫入效能和磁碟空間。
坑一：TEXT 欄位忘了加前綴長度
問題
Migration SQL 跑在大部分資料庫上都成功，但某幾個報了錯：
ERROR 1170 (42000): BLOB/TEXT column 'Qmedicine' used in key
specification without a key length根因
Qmedicine 欄位是 TEXT 型別。MySQL 5.7 不允許對整個 TEXT 欄位建索引，必須指定前綴長度：
-- ❌ TEXT 欄位不能直接建索引
CALL safe_add_index('form_assess_17', 'idx_med_date',
'Qmedicine, cDate');
-- ✅ 需要加前綴長度
CALL safe_add_index('form_assess_17', 'idx_med_date',
'Qmedicine(100), cDate');(100) 表示只索引前 100 個字元。這個長度需要根據實際資料分佈決定——太短會降低索引選擇性，太長會浪費空間。
同樣的坑再踩一次
修完 Qmedicine 後，另一個 issue 也踩到了同樣的問題：HospNo 在某些表中也是 TEXT 型別：
-- ❌ 又忘了
CALL safe_add_index('case_report_40', 'idx_hospno_date',
'HospNo, date');
-- ✅ 加前綴
CALL safe_add_index('case_report_40', 'idx_hospno_date',
'HospNo(20), date');教訓：在加索引之前，先確認欄位型別。VARCHAR 可以直接建索引，TEXT/BLOB 必須加前綴。
坑二：多租戶環境下的 DB 防護
問題
Migration SQL 在母版資料庫（demo-org-1，存放共用設定）上執行時，意外修改了不該改的表。
修復
在 stored procedure 中加入資料庫名稱檢查：
CREATE PROCEDURE safe_add_index(...)
BEGIN
-- 只在租戶資料庫上執行，跳過母版
IF DATABASE() NOT LIKE 'demo-org-%'
OR DATABASE() = 'demo-org-1' THEN
-- 跳過
SELECT 'Skipped: not a tenant DB' AS msg;
ELSE
-- 執行 ALTER TABLE
END IF;
END//教訓：多租戶 migration 一定要有「這個 SQL 該跑在哪些 DB」的防護。
坑三：加了索引但沒用——FORCE INDEX
問題
加完 service_detail_02 的 (HospNo, servicedate) 複合索引後，某個頁面的查詢時間從 17 秒降到 3.4 秒。但另一個類似的查詢完全沒改善。
根因
MySQL 的查詢最佳化器在某些情況下不會選擇你期望的索引。特別是當表有多個索引、且 WHERE 條件涉及多個欄位時，最佳化器可能選了一個更「通用」但更慢的索引。
修復
// ✅ 強制使用特定索引
$sql = "SELECT * FROM service_detail_02
FORCE INDEX (idx_hospno_servicedate)
WHERE HospNo = '{$hospNo}'
AND servicedate >= '{$startDate}'";FORCE INDEX 應該是最後手段，但在 Legacy 系統中，有時候它是唯一可靠的方式——因為你不確定 MySQL 最佳化器會怎麼決定，而且你沒辦法升級 MySQL 版本來獲得更好的最佳化器。
搭配 PHP 端優化的完整效果
索引只是一半。另一半是 PHP 端的查詢改法。這個案例中，Claude Code 同時做了兩件事：
移除 DATE_FORMAT 反模式
// ❌ DATE_FORMAT 讓索引失效
$sql = "WHERE date_format(servicedate, '%Y-%m-%d') >= '{$start}'";
// ✅ 直接比較，索引生效
$sql = "WHERE servicedate >= '{$start}'";批次預查取代迴圈查詢
// ❌ 迴圈內逐筆查詢服務代碼使用次數
foreach ($patients as $p) {
$sql = "SELECT code, COUNT(*) FROM service_detail_02
WHERE HospNo = '{$p['HospNo']}' GROUP BY code";
}
// ✅ 一次批次查完
$sql = "SELECT HospNo, code, COUNT(*) as cnt
FROM service_detail_02
WHERE HospNo IN ('{$hospNos}')
GROUP BY HospNo, code";
$usageMap = [];
foreach ($db->query($sql) as $row) {
$usageMap[$row['HospNo']][$row['code']] = $row['cnt'];
}加索引 + 改 PHP 查詢的綜合效果：審核作業頁面從「打不開」到「3 秒內載入」。
用 Claude Code 做索引分析的工作流
Step 1：收集慢查詢
> 找出這個模組中所有 SQL 查詢，用 EXPLAIN 分析哪些是 full table scanStep 2：請 Claude Code 建議索引
> 根據這些 EXPLAIN 結果，建議需要新增的索引。
注意 TEXT 欄位需要前綴長度，注意不要建冗餘索引。Step 3：產生安全的 migration SQL
> 用 safe_add_index 的方式產生 migration SQL，
加入多租戶 DB 防護，按優先級排序。Step 4：找出可以移除的索引
> 列出所有表的索引定義，找出跟 PK 重複或被新索引取代的。索引優化清單
| 檢查項目 | 說明 |
|---|---|
| EXPLAIN 是否 full table scan | type: ALL 就是全表掃描 |
| TEXT/BLOB 欄位是否加前綴 | column(N) 語法 |
| 是否有冗餘索引 | PK 前綴重複、被其他索引涵蓋 |
| WHERE 中是否有函式包欄位 | DATE_FORMAT、YEAR()、LOWER() 等 |
| 複合索引的欄位順序 | 最常用的篩選條件放前面 |
| Migration 是否有 DB 防護 | 多租戶環境必要 |
| Migration 是否冪等 | safe_add_index 模式 |
結語
索引優化這件事，單獨看每一個都不難——就是看 EXPLAIN、加索引。難的是在一個十年的系統中，找出所有該加沒加、該刪沒刪的索引。
Claude Code 的價值在於它可以一次讀完整個模組的所有 SQL，交叉比對表結構和查詢模式，產出一份完整的索引建議。人工做這件事需要幾天，Claude Code 幾分鐘就列完了。
而且它會記住 TEXT 前綴長度的規則——雖然我已經踩過兩次坑了。