MySQL 索引與預存程序完整指南：建立、類型、優化與最佳實踐

前言

索引是查詢效能的關鍵，預存程序是業務邏輯下沉資料庫的手段。MySQL 支援 B+Tree、Hash、Fulltext、Spatial 多種索引類型，並提供完整的預存程序/函數、變數、遊標、動態 SQL 能力。本文系統整理索引分類、建立/刪除語法、覆蓋索引原理、預存程序建立流程、變數作用域、遊標使用與權限管理。

一、索引篇

1. 索引分類與儲存類型

2. 建立索引語法

-- 建表時建立
CREATE TABLE t_order (
  id BIGINT PRIMARY KEY,
  user_id BIGINT,
  status TINYINT,
  create_time DATETIME,
  INDEX idx_user_status (user_id, status),
  UNIQUE uk_order_no (order_no)
) ENGINE=InnoDB;

-- 既有表新增
ALTER TABLE t_order ADD INDEX idx_status_time (status, create_time);
CREATE INDEX idx_user_id ON t_order (user_id);
CREATE UNIQUE INDEX uk_email ON t_user (email);

-- 全文索引（InnoDB 5.6+）
ALTER TABLE t_article ADD FULLTEXT INDEX ft_content (title, body);

3. 刪除索引

DROP INDEX idx_name ON table_name;
ALTER TABLE table_name DROP INDEX idx_name;
ALTER TABLE table_name DROP PRIMARY KEY;  -- 僅主鍵可省略索引名

4. 覆蓋索引與最左前綴原則

• 覆蓋索引：SELECT 欄位全部包含在索引中，避免回表，EXPLAIN 顯示 Using index。
• 最左前綴：聯合索引 (a,b,c) 可支援 a、a,b、a,b,c 的查詢/排序/分組；b、c、b,c 無法使用。
• 索引下推 (ICP, 5.6+)：InnoDB 在索引遍歷階段直接過濾 WHERE 條件，減少回表次數。

5. 索引設計黃金法則

1. WHERE/JOIN/ORDER BY/GROUP BY 欄位優先建索引。
2. 高選擇性（基數大）欄位放在聯合索引前列。
3. 避免過度索引：單表建議 ≤ 5 個索引，每個索引 ≤ 3-4 欄位。
4. 使用 EXPLAIN 驗證 type=ref/range、key=預期索引、rows 合理。

二、預存程序篇

1. 建立預存程序

DELIMITER //
CREATE PROCEDURE sp_get_user_orders(IN p_user_id BIGINT, OUT p_total INT)
BEGIN
  DECLARE v_cnt INT DEFAULT 0;
  SELECT COUNT(*) INTO v_cnt FROM t_order WHERE user_id = p_user_id;
  SET p_total = v_cnt;
  SELECT * FROM t_order WHERE user_id = p_user_id ORDER BY create_time DESC LIMIT 20;
END //
DELIMITER ;

2. 呼叫與參數模式

CALL sp_get_user_orders(1001, @total);
SELECT @total;

• IN：輸入參數（預設）
• OUT：輸出參數
• INOUT：雙向

3. 變數作用域

4. 賦值方式

SET v_name = 'value';
SELECT col INTO v_name FROM table WHERE id=1;
SELECT COUNT(*) INTO v_cnt FROM table;

5. 遊標

DECLARE cur CURSOR FOR SELECT id, name FROM t_user WHERE status=1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur;
read_loop: LOOP
  FETCH cur INTO v_id, v_name;
  IF done THEN LEAVE read_loop; END IF;
  -- 處理邏輯
END LOOP;
CLOSE cur;

6. 查看與修改

SHOW PROCEDURE STATUS [LIKE 'pattern'];
SHOW CREATE PROCEDURE sp_name;
ALTER PROCEDURE sp_name [characteristic ...];  -- 僅能修改特性，不可改邏輯

修改邏輯需 DROP 後重建。

7. 權限與安全

• 建立需 CREATE ROUTINE 權限。
• 執行需 EXECUTE 權限。
• DEFINER vs INVOKER：預設 DEFINER（建立者權限執行），可指定 SQL SECURITY INVOKER 使用呼叫者權限，減少權限提升風險。

總結

索引是讀效能的基石，**覆蓋索引 + 最左前綴 + ICP** 是查詢優化核心三劍客。預存程序適合封裝複雜、高頻、需事務保證的業務邏輯（如庫存扣減、結算），但會增加資料庫 CPU 壓力、難以版控、除錯困難。**現代架構傾向將業務邏輯留在應用層，資料庫專注存儲與索引**。

1. 上一篇文章： MySQL 運行時間查看方法：Uptime、ps 命令與 phpMyAdmin 三種方式
2. 下一篇文章： MySQL 中文亂碼徹底解決：字符集配置、連接參數與表級修正全攻略

MySQL 綜合教學

MySQL 線程創建與線程池機制深度探秘：從源碼看 pthread_create 到服務啟動線程拓撲

MySQL 為了避免頻繁創建/銷毀線程的系統開銷，實現了線程

MySQL 數據類型速查手冊：整數、日期、字符串全解析

MySQL 數據類型速查手冊：整數、日期、字符串全解析 選對

Mac 下 MySQL 5.7 忘記 root 密碼重置圖文教程

操作步驟 系統偏好設定 → MySQL → Stop My

MySQL 模糊查詢優化實戰：LOCATE 函數替代 LIKE %...% 提升 10 倍過濾效率

MySQL 模糊查詢優化：LIKE %...% 的高性能替代