您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 索引與視圖完整實戰指南：建立、類型、優化與視圖操作

編輯：MySQL 資料庫相關知識

前言

索引是資料庫效能的核心，視圖則是簡化複雜查詢、增強安全性的利器。本文以實際 student 表為例，系統演示索引建立語法、類型選擇（B-Tree/Hash）、複合/唯一/全文/空間索引、索引維護，以及視圖的建立、修改、更新限制與刪除。

一、環境準備：範例表結構與資料

CREATE TABLE `student` (
  `stu_id` INT NOT NULL,
  `stu_name` VARCHAR(20) NOT NULL,
  `stu_tel` INT NOT NULL,
  `stu_score` INT NOT NULL,
  PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `student` VALUES
(1, 'a', 151, 60),
(2, 'b', 152, 61),
(3, 'c', 153, 62),
(4, 'd', 154, 63);

二、索引完整語法與類型

通用建立語法

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
[USING {BTREE | HASH}]
ON table_name (col_name [(length)] [ASC | DESC], ...);

UNIQUE：值唯一，允許單一 NULL。
FULLTEXT：全文搜尋（MyISAM 5.6+、InnoDB 5.6.4+）。
SPATIAL：空間資料（需 GIS 擴充、MyISAM/InnoDB 5.7+）。
USING BTREE：預設，支援範圍、排序、前綴匹配。
USING HASH：僅支援等值查詢（=, <=>），記憶體表預設，InnoDB 自適應雜湊索引內部使用。
ASC/DESC：目前 MySQL 忽略，未來可能支援降序索引優化。

1. 基本單欄索引（非唯一，預設 BTREE）

CREATE INDEX idx_stu_id ON student(stu_id);
-- 等同於
CREATE INDEX idx_stu_id USING BTREE ON student(stu_id ASC);

2. 複合唯一索引（指定 HASH）

CREATE UNIQUE INDEX uk_stu_id_name USING HASH ON student(stu_id, stu_name);

注意：HASH 索引不支援範圍查詢與排序，複合索引遵循最左前綴原則。

3. 透過 ALTER TABLE 新增索引

ALTER TABLE student ADD UNIQUE INDEX uk_stu_tel USING HASH (stu_tel);

4. 刪除索引

DROP INDEX idx_stu_id ON student;
-- 或
ALTER TABLE student DROP INDEX idx_stu_id;

5. 建表時同步定義索引

CREATE TABLE student_v2 (
  stu_id INT PRIMARY KEY,
  stu_name VARCHAR(5) NOT NULL,
  stu_tel INT(5) UNIQUE,
  stu_score INT(2),
  INDEX idx_stu_id (stu_id)  -- 非主鍵索引
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

三、索引類型深度解析與選擇指南

索引類型	適用場景	優點	限制
B-Tree (預設)	幾乎所有場景：等值、範圍、排序、前綴 LIKE 'abc%'	通用、支援最左前綴、覆蓋索引、ORDER BY 優化	寫入維護成本、大欄位佔空間
Hash	純等值查詢 (=, <=>)、記憶體表、自適應雜湊	查詢 O(1)、極快	不支援範圍/排序/前綴、全表掃描無法使用、衝突處理
Fulltext	自然語言/布林全文搜尋	內建分詞、相關性排序	中文需 nggram parser、5.6+ InnoDB 支援、維護重
Spatial (R-Tree)	GIS 幾何資料（點、線、面）	空間關係查詢 (MBRContains 等)	需幾何欄位、MyISAM/InnoDB 5.7+、專業領域

四、索引優化黃金法則

最左前綴原則：複合索引 (a,b,c) 可支援 a、a,b、a,b,c 查詢，不可跳過 a 直接用 b。
覆蓋索引：SELECT a,b FROM t WHERE c=1 → 索引 (c,a,b) 避免回表。
區分度優先：高基數欄位（如 ID、手機）放前面，低基數（性別、狀態）放後或不建索引。
避免冗餘/重複索引：(a,b,c) 存在時，(a,b) 為冗餘；主鍵即索引，勿重複建立。
前綴索引：長字串 VARCHAR(200) 用 col(10) 節省空間，SELECT COUNT(DISTINCT LEFT(col,10))/COUNT(*) 評估區分度。
索引失效常見原因：函數運算 WHERE YEAR(date)=2023、隱式轉型 WHERE phone=12345678901 (phone 是字串)、!=/<>/IS NULL/OR 破壞最左前綴、LIKE '%abc' 導模糊。

五、視圖完整操作指南

1. 建立視圖

CREATE [OR REPLACE] VIEW view_name [(col_alias, ...)]
AS SELECT ... 
[WITH [CASCADED | LOCAL] CHECK OPTION];

OR REPLACE：視圖存在時覆蓋，避免先 DROP。
col_alias：重新命名輸出欄位。
WITH CHECK OPTION：防止透過視圖 INSERT/UPDATE 違背視圖定義的 WHERE 條件。
- CASCADED（預設）：檢查本視圖及所有底層視圖條件。
- LOCAL：僅檢查本視圖條件。

2. 範例：建立、重命名欄位、替換

-- 基本建立
CREATE VIEW v_student AS SELECT * FROM student;

-- 指定別名
CREATE OR REPLACE VIEW v_student (id, name, tel, score)
AS SELECT * FROM student;

-- 帶條件與檢查選項
CREATE VIEW v_high_score AS
SELECT * FROM student WHERE stu_score > 60
WITH CASCADED CHECK OPTION;

3. 查詢視圖

SELECT * FROM v_student;
SELECT * FROM v_high_score;  -- 僅返回分數>60 的行

4. 透過視圖修改資料（可更新視圖條件）

視圖可更新需滿足：

單一基礎表（無 JOIN、UNION、子查詢、聚合、DISTINCT、GROUP BY、HAVING）。
SELECT 清單無運算式/函數，皆為直接欄位引用。
無 WITH CHECK OPTION 衝突。

UPDATE v_student SET stu_score = 100 WHERE stu_id = 1;  -- 成功同步到 student 表
INSERT INTO v_student (stu_id, stu_name, stu_tel, stu_score) VALUES (5, 'e', 155, 64); -- 成功

5. 刪除視圖

DROP VIEW [IF EXISTS] v_student, v_high_score;

六、視圖進階應用場景

場景	視圖優勢	範例
權限控管	僅授予視圖 SELECT 權限，隱藏敏感欄位（薪資、身分證）	`GRANT SELECT ON v_emp_public TO hr_user;`
簡化複雜查詢	封裝多表 JOIN、子查詢、業務邏輯，應用端如查單表	`CREATE VIEW v_order_detail AS SELECT o.id, u.name, p.title... FROM orders o JOIN users u ... JOIN products p ...`
向後相容	表結構變更時，視圖保持舊介面，應用程式無需修改	重命名欄位、拆分表後視圖 UNION ALL 合併
資料遮罩	即時脫敏（手機中間四碼、Email 部分）	`SELECT id, CONCAT(LEFT(phone,3),'****',RIGHT(phone,4)) AS phone FROM users`

七、總結

索引是讀效能的加速器，視圖是業務邏輯的封裝層。掌握 B-Tree 通用性、Hash 等值極速、Fulltext 全文搜尋、Spatial 空間查詢 四大索引類型選擇，遵循 最左前綴、覆蓋索引、高區分度優先、避免冗餘 四大原則，能解決 90% 的慢查詢。視圖善用 OR REPLACE、CHECK OPTION、欄位別名，構建安全、簡潔、可維護的資料存取介面。記住：索引是雙面刃，寫入付出代價換取讀取極速；視圖不儲存資料，每次查詢即時展開，複雜視圖需警惕效能。