1. 介紹
在資料庫的運作中，搜尋速度的優化是重要的課題。其解決方案之一就是「索引」的活用。索引是為了加速資料庫中的資料搜尋而不可或缺的功能。本文將使用 MySQL 來確認索引的方法，從基礎到應用進行說明。
本文可學到的事項
- 索引的基本機制及其種類
- MySQL 中的索引確認方法（SHOW INDEX 或 EXPLAIN 指令的具體範例）
- 索引的適當管理和維護方法
- 有關索引的常見疑問及其解決方案
索引如果適當活用，能大幅提升資料庫的效能。但是，不適當的設定或運作反而會降低整個系統的效能。透過本文，從索引的基本到應用進行掌握，並用於改善資料庫運作。
2. 索引的基本知識
索引是為了效率化資料庫搜尋而設計的機制，在資料管理中扮演非常重要的角色。本節將說明索引的基本機制、種類，以及優點與挑戰。
索引是什麼？
索引是設定在資料庫內特定欄位上的「索引」類似物。藉此，提升資料的搜尋速度。類似書籍的目錄角色，用來有效率地找出必要資訊。
例如，沒有索引的情況下，資料庫需要依序確認搜尋對象的所有資料（全表掃描）。另一方面，若設定了索引，只需循著索引結構即可迅速存取目的資料。
索引的種類
- 主鍵索引（PRIMARY KEY）
主鍵上自動設定的索引。保證各行唯一識別，每個表格僅存在一個。 - 唯一索引（UNIQUE）
保證指定欄位的值唯一。不允許相同值的資料時使用。 - 全文索引（FULLTEXT）
為了高速化文字搜尋的索引。主要在進行全文搜尋時活用。 - 複合索引
也可以組合多個欄位來建立索引。
例:name和age的兩者基於搜尋條件，設定複合索引可提升速度。
索引的優點與挑戰
優點
- 搜尋速度的提升
資料搜尋或基於特定條件的抽出會高速化。 - 查詢效率的改善
WHERE子句、JOIN、ORDER BY等的處理速度大幅改善。
挑戰
- 資料更新時效能降低
由於需要更新索引，INSERT、UPDATE、DELETE操作可能變慢。 - 儲存空間消耗
索引需要額外的儲存空間，大規模索引會壓迫磁碟容量。
3. 在 MySQL 中的索引確認方法
MySQL 中，為了確認索引的狀態，準備了幾種方法。本節將以具體範例說明三種代表性的方法：「SHOW INDEX 命令」、「INFORMATION_SCHEMA.STATISTICS 表格」和「EXPLAIN 命令」。
使用 SHOW INDEX 命令的確認方法
SHOW INDEX 命令是用來確認表格中設定的索引詳細的基本命令。
基本語法
SHOW INDEX FROM 表格名稱;執行範例
例如，確認 users 表格的索引時，如下執行。
SHOW INDEX FROM users;結果範例
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Index_type | Comment |
|---|---|---|---|---|---|---|---|---|
| users | 0 | PRIMARY | 1 | id | A | 1000 | BTREE | |
| users | 1 | idx_name | 1 | name | A | 500 | BTREE |
輸出結果的項目說明
- Key_name: 索引的名稱。
- Non_unique: 唯一性（0 表示唯一，1 表示非唯一）。
- Column_name: 索引設定的欄位名稱。
- Cardinality: 索引中唯一值的估計數。
- Index_type: 索引的種類（通常是 BTREE）。
使用 INFORMATION_SCHEMA.STATISTICS 的確認方法
INFORMATION_SCHEMA.STATISTICS 是儲存資料庫中索引資訊的系統表格。
基本語法
SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema = '資料庫名稱'
AND table_name = '表格名稱';執行範例
確認 my_database 的 users 表格相關的索引資訊時：
SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema = 'my_database'
AND table_name = 'users';結果範例（摘錄）
| TABLE_SCHEMA | TABLE_NAME | INDEX_NAME | COLUMN_NAME | INDEX_TYPE |
|---|---|---|---|---|
| my_database | users | PRIMARY | id | BTREE |
| my_database | users | idx_name | name | BTREE |
此方法在有效取得特定資料庫或跨多個表格的索引資訊時很有用。
使用 EXPLAIN 命令的確認方法
EXPLAIN 命令是用來確認 SQL 查詢的執行計劃，並分析索引如何被使用的工具。
基本語法
EXPLAIN 查詢;執行範例
確認下列查詢的執行計劃。
EXPLAIN SELECT * FROM users WHERE name = 'Alice';結果範例
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | users | ref | idx_name | idx_name | 102 | const | 1 | Using index |
輸出結果的項目說明
- key: 實際使用的索引名稱。
- possible_keys: 可使用的索引。
- rows: 預測的掃描行數。
- Extra: 索引的使用情況或附加資訊。
總結
在 MySQL 中，可以使用 SHOW INDEX、INFORMATION_SCHEMA.STATISTICS 和 EXPLAIN 命令來確認索引的狀態或在查詢中的使用情況。由於各方法有其特性，請依用途選擇適當的方法。
4. 索引的管理
MySQL 中適當管理索引對於資料庫的有效運作是不可或缺的。本節將詳細說明索引的建立、刪除以及最佳化的方法。
索引的建立
基本語法
索引是使用CREATE INDEX語句來建立的。
CREATE INDEX 索引名稱 ON 資料表名稱(欄位名稱);執行範例
例如，在users資料表的email欄位建立索引時：
CREATE INDEX idx_email ON users(email);複合索引的建立
也可以結合多個欄位來建立索引。
CREATE INDEX idx_name_email ON users(name, email);使用複合索引，可以有效化多個搜尋條件。
索引的刪除
基本語法
不再需要的索引可以使用DROP INDEX語句來刪除。
DROP INDEX 索引名稱 ON 資料表名稱;執行範例
例如，從users資料表刪除idx_email索引時：
DROP INDEX idx_email ON users;透過刪除索引，可以減少不必要的儲存空間使用，並提升資料更新時的效能。
索引的最佳化與維護
特定使用頻率低的索引
使用頻率低的索引可能會成為資料庫的負擔。使用以下查詢來確認索引的使用情況。
SELECT * FROM INFORMATION_SCHEMA.STATISTICS
WHERE table_schema = '資料庫名稱'
AND table_name = '資料表名稱';刪除冗餘索引
如果相同的欄位設定多個索引，透過刪除它們可以改善效率。
工具的使用範例
使用 Percona Toolkit 來自動偵測冗餘索引。
pt-duplicate-key-checker --host=localhost --user=root --password=yourpassword解決索引的片段化
如果索引片段化，效能會降低。在這種情況下，透過重建索引可以期待改善。
ALTER TABLE 資料表名稱 ENGINE=InnoDB;摘要
索引的管理不僅僅是建立或刪除，還需要最佳化和定期維護。透過適當的管理，可以維持資料庫的效能，並實現有效的運作。
5. 常見問題 (FAQ)
MySQL 的索引相關疑問是許多人會遇到的重點。本節中，我們彙整了關於索引的常見問題及其解答。透過閱讀這些內容，您將能更深入了解索引的機制與運作方式。
索引未被使用的理由是？
即使已設定索引，查詢仍可能未使用索引。以下顯示其主要理由與對策。
主要理由
- 查詢描述錯誤
在查詢中使用無法活用索引的語法（例如：LIKE '%keyword%'這類前方不一致的情況）。 - 資料型態不一致
查詢中指定的值資料型態與索引設定時的欄位資料型態不同時。 - 小型表格
資料庫判斷全表掃描更有效率時。
解決方案
- 使用 EXPLAIN 指令
確認執行計劃，檢查是否使用了索引。
EXPLAIN SELECT * FROM users WHERE name = 'Alice';- 最佳化查詢
修正條件式，使其能活用索引。
建立複合索引時需注意的事項是？
複合索引能加速多重條件的搜尋，但需掌握建立時的注意點。
注意點
- 欄位順序很重要
將搜尋條件中頻繁使用的欄位置於前方。例如：WHERE name = 'Alice' AND age > 25的情況下，將name置於前方。 - 範圍條件置後
包含範圍條件（例如：age > 30）時，將其置於後方。 - 避免過度複合索引
包含使用頻度低的欄位，可能導致效能下降。
索引導致效能下降的情況是？
索引大多有益，但在某些情況下可能導致效能下降。
主要原因
- 索引過度設定
建立過多不必要的索引，會在資料插入・更新時增加額外負荷。 - 碎片化的影響
索引碎片化會導致搜尋速度下降。 - 重複索引
對相同欄位建立多個索引會造成冗餘，浪費資源。
對策
- 刪除未使用的索引。
- 定期重建索引。
確認索引有效性的方法是？
要驗證索引是否有效運作，可使用以下方法。
- 利用 EXPLAIN 指令
確認執行計劃，檢查key欄位是否顯示索引名稱。 - 活用查詢效能結構描述
使用 MySQL 的效能結構描述，詳細分析索引的使用情況。 - 使用效能監控工具
活用 Percona Toolkit 等工具，診斷索引的效能。
索引的最佳數量是？
索引的數量依用途與表格性質而異，但請考慮以下重點。
重點
- 以頻繁使用的查詢為基準設計
僅建立特定查詢所需的索引。 - 更新頻度高的表格應保持最小限
為了減輕資料更新的負荷，僅保留必要的最低限度索引。
6. 總結
MySQL 的索引是大幅提升資料庫搜尋效率的重要元素。本文從索引的基本原理到應用、具體的管理方法以及常見問題的解答，系統性地說明了。
回顧文章的重點
- 索引的基本機制與種類
- 索引作為資料庫的「索引」功能，能提升搜尋效率。
- 主鍵索引、唯一索引、全文字索引、複合索引等，根據用途存在各種種類。
- MySQL 中的索引確認方法
- 使用 SHOW INDEX 或 EXPLAIN 指令，即可輕鬆確認索引的狀態或查詢的使用情況。
- 活用 INFORMATION_SCHEMA.STATISTICS 表格，即可取得更詳細的資訊。
- 索引的管理與最佳化
- 適當建立與刪除必要的索引，不僅能提升搜尋效率，同時也能減輕更新負荷。
- 刪除多餘的索引或解決碎片化也很重要。
- 有關索引的常見問題
- FAQ 區段回答了索引未被使用的理由、複合索引的注意事項等實務上的疑問。
下一步驟
- 確認目前資料庫的索引狀況
使用 SHOW INDEX 或 EXPLAIN 調查表格中設定的索引吧。 - 執行效能最佳化
找出使用頻率低的索引或多餘的索引，視需要刪除。 - 實踐適當的索引設計
以經常使用的查詢為基礎，建立或調整索引。 - 活用所學知識
以本文所學的知識為基礎，推進資料庫運用的效率化。
最後
索引的適當運用不僅能提升資料庫的效能，還能提高系統整體的效率。然而，過度設定索引或不適當的設計反而會成為降低效能的因素。以本文為參考，進一步磨練索引的運用技能，目標是穩定的資料庫運用吧。
