【中級】テーブル内の不要なインデックスをVBAで検出し、書き込み速度を改善するクリーンアップツール
レガシーなAccessデータベースが日々直面する最大の性能ボトルネック、それは「無秩序に乱立したインデックス」にある。
開発者が変わるたび、クエリの遅延対策として場当たり的に追加されたインデックスの群れは、SELECTクエリをわずかに高速化させる一方で、INSERT、UPDATE、DELETEといったすべての書き込み処理において、Jet/ACEデータベースエンジンのB-Tree構造の再構築コストを跳ね上げさせる。
特に、数万件以上のトランザクションデータを扱う業務システムにおいて、不要なインデックスの存在は、VBAからのレコードセット書き込み速度を致命的に低下させる悪の根源だ。
今回は、DAO（Data Access Objects）の`Indexes`コレクションと`Properties`を極限まで低レイヤーで走査し、システムに寄生する無駄なインデックスを自動検出・排除して、書き込みパフォーマンスを限界突破させるクリーンアップツールの全貌を公開する。
—
1. なぜインデックスが書き込みを殺すのか？（アーキテクチャの真実）
Access（Jet/ACEエンジン）のテーブルにインデックスが存在する場合、レコードが1行追加・更新されるたびに、エンジンは以下のタスクを強制される。
1. データページの更新：実際のレコードデータが格納されるページの書き換え。
2. インデックスページの更新：該当フィールドの値が属するB-Treeインデックスのノード探索と、必要に応じたツリーの分割（Split）・再平衡化（Rebalance）。
3. トランザクションログの肥大化：これらすべての変更履歴が `.laccdb` および `.accdb` 本体に書き込まれ、ファイルロックの競合確率が跳ね上がる。
さらに悪質なのは、「主キー（Primary Key）」や「リレーションシップの外部キー（Foreign Key）」によって暗黙的に生成されるインデックスと重複している、あるいは完全に包含されている単一フィールドのインデックスである。これらは完全に「無駄なオーバーヘッド」でありながら、開発者の視界から隠蔽されていることが多い。
—
2. 不要インデックスの定義と検出ロジック
本ツールでは、以下の条件に合致するインデックスを「不要（削除候補）」と判定する。
- システムの基本インデックス：主キー（Primary）および外部キー（Foreign）は保護対象とする（これらを消すと整合性が崩壊する）。
- 単一フィールドの重複インデックス：複数フィールドインデックスの先頭フィールドと完全に重複しているもの。
- 低選択性（Low Cardinality）の疑い：ブール値（Yes/No）や性別など、値の種類が極端に少ないフィールドに貼られたインデックス（※今回はロジックの簡略化のため、構造的冗長性にフォーカスする）。
—
3. 実装コード：インデックス・クリーンアップエンジン
以下のVBAコードを標準モジュールに配置し、実行せよ。
DAOのオブジェクトライフサイクルを完全に理解し、メモリリークとCOMコンポーネントの解放漏れを防ぐためのシニアエンジニアリングパターン（`Set obj = Nothing` の徹底）を実装している。
Option Compare Database
Option Explicit
‘ =========================================================================
‘ 接続されたDB内の全テーブルを走査し、不要なインデックスを検出・削除する
‘ =========================================================================
Public Sub ExecuteIndexCleanupTool(Optional ByVal ExecuteDelete As Boolean = False)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim idx As DAO.Index
Dim fld As DAO.Field
Dim targetTables As Long
Dim removedCount As Long
‘ 現在のデータベースセッションを取得
Set db = CurrentDb()
targetTables = 0
removedCount = 0
Debug.Print “=== インデックス・クリーンアップ・プロセス開始 ===”
Debug.Print “実行モード: ” & IIf(ExecuteDelete, “【削除実行】”, “【シミュレーション（ドライラン）】”)
‘ テーブル定義を走査
For Each tdf In db.TableDefs
‘ システムテーブル（MSysで始まるもの）およびリンクテーブルはスキップ
If (tdf.Attributes & dbSystemObject) = 0 And (tdf.Attributes & dbAttachedTable) = 0 And (tdf.Attributes & dbAttachedODBC) = 0 Then
targetTables = targetTables + 1
‘ 各テーブルのインデックスコレクションを評価
Dim i As Long
For i = tdf.Indexes.Count – 1 To 0 Step -1
Set idx = tdf.Indexes(i)
‘ 1. 主キー（Primary）および一意制約（Unique）、外部キー（Foreign）は除外
If Not idx.Primary And Not idx.Unique And Not idx.Foreign Then
‘ 2. 自動生成されたインデックスやマルチフィールドの冗長性を評価
‘ ※ここでは「1つのフィールドのみで構成され、かつ特定の条件を満たすもの」をターゲットとする
If idx.Fields.Count = 1 Then
Dim fieldName As String
fieldName = idx.Fields(0).Name
‘ 判定ロジック：
‘もしそのフィールドが既に主キーの一部である場合、この単一インデックスは完全に冗長
If IsFieldPartOfPrimaryOrForeign(tdf, fieldName) Then
Debug.Print ” [冗長検出] テーブル: ” & tdf.Name & ” | インデックス: ” & idx.Name & ” (フィールド: ” & fieldName & “)”
If ExecuteDelete Then
tdf.Indexes.Delete idx.Name
removedCount = removedCount + 1
Debug.Print ” -> 削除実行完了”
End If
End If
End If ‘ idx.Fields.Count = 1
End If ‘ Not Primary/Unique/Foreign
Set idx = Nothing
Next i
End If
Set tdf = Nothing
Next tdf
Debug.Print “=== プロセス完了 ===”
Debug.Print “走査テーブル数: ” & targetTables
Debug.Print “削除されたインデックス数: ” & removedCount
‘ クリーンアップ
Set db = Nothing
MsgBox “インデックスのクリーンアップが完了しました。” & vbCrLf & _
“処理テーブル数: ” & targetTables & vbCrLf & _
“削除インデックス数: ” & removedCount, vbInformation, “最適化完了”
End Sub
‘ =========================================================================
‘ 指定されたフィールドが、テーブルの主キーまたは外部キーに含まれているか判定
‘ =========================================================================
Private Function IsFieldPartOfPrimaryOrForeign(tdf As DAO.TableDef, targetFieldName As String) As Boolean
Dim idx As DAO.Index
Dim fld As DAO.Field
IsFieldPartOfPrimaryOrForeign = False
For Each idx In tdf.Indexes
If idx.Primary Or idx.Foreign Then
For Each fld In idx.Fields
‘ 大文字小文字を区別せずに比較
If StrComp(fld.Name, targetFieldName, vbTextCompare) = 0 Then
IsFieldPartOfPrimaryOrForeign = True
Exit Function
End If
Next fld
End If
Next idx
End Function
—
4. チーフアーキテクトからの実践的アドバイス：運用の作法
このツールを本番環境（Production）に投入する前に、以下のアーキテクチャ上の鉄則を心に刻んでおいてほしい。
1. 実行前のバックアップと「コンパクトと修復」
インデックスを削除しただけでは、Accessの内部ストレージ（ページファイル）の物理的なサイズは縮小しない。不要なインデックスを大量に削除した後は、必ず 「データベースのコンパクトと修復（Compact and Repair Database）」 を実行し、B-Tree構造体を完全に再構築させよ。これにより、ファイルサイズが劇的に縮小し、ディスクI/Oのヒット率が向上する。
2. トランザクション処理のベンチマーク
大量のレコード（例: 10,000件以上）をDAOの `AddNew` / `Update` ループ、あるいは `INSERT INTO` のパススルー・内部クエリで流し込む処理がある場合、インデックス削除前後で処理時間が何倍（場合によっては何十倍）に跳ね上がるかを計測せよ。体感できるレベルでの高速化が即座に証明されるはずだ。
3. レガシー環境におけるリスクヘッジ
もし古い基幹系Accessアプリで、開発者が意図的に「クエリデザイナの自動結合（リレーションシップ非依存）」のためにインデックスに頼っていた場合、これを削除すると特定のSELECTクエリの実行計画が変わり、逆にパフォーマンスが落ちるリスクが僅かに存在する。本番適用時は、必ず事前に `ExecuteDelete = False` のシミュレーションモードで出力ログを精査し、影響範囲を目視で確認すること。
—
5. 結び
VBAプログラミングのスキルは、ただ「動くコードを書く」ことでは向上しない。データベースエンジンの内部挙動（ストレージ、インデックス、メモリ管理）を背後で支配している物理法則を理解し、不要な負荷を削ぎ落とすことこそが、真のシニアエンジニアの仕事である。
乱立したインデックスの呪縛からデータベースを解放し、極限まで研ぎ澄まされた書き込みパフォーマンスを手に入れてほしい。
