萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 索引創建、刪除、修改與優化總結

MySQL 索引全生命周期管理:創建、維護、優化實戰

索引是查詢性能的關鍵。本文系統整理 MySQL 索引的創建語法、類型選擇、維護操作、優化原則與數據類型建議。

索引類型與創建語法

1. 創建表時定義索引

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(100) NOT NULL,
  name VARCHAR(50),
  status TINYINT DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  
  UNIQUE INDEX uk_email (email),
  INDEX idx_status_created (status, created_at),
  FULLTEXT INDEX ft_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. ALTER TABLE 添加索引

-- 主鍵
ALTER TABLE users ADD PRIMARY KEY (id);

-- 唯一索引
ALTER TABLE users ADD UNIQUE INDEX uk_email (email);

-- 普通索引
ALTER TABLE users ADD INDEX idx_name (name);

-- 組合索引
ALTER TABLE users ADD INDEX idx_status_created (status, created_at);

-- 全文索引
ALTER TABLE users ADD FULLTEXT INDEX ft_name (name);

3. CREATE INDEX 語法

CREATE INDEX idx_name ON users (name);
CREATE UNIQUE INDEX uk_email ON users (email);
CREATE FULLTEXT INDEX ft_name ON users (name);

索引刪除與查看

-- 刪除普通/唯一/全文索引
ALTER TABLE users DROP INDEX idx_name;
-- 或
DROP INDEX idx_name ON users;

-- 刪除主鍵
ALTER TABLE users DROP PRIMARY KEY;

-- 查看索引
SHOW INDEX FROM users\G

索引選擇與設計原則

最左前綴原則

組合索引 (a, b, c) 可支持:

  • WHERE a = 1
  • WHERE a = 1 AND b = 2
  • WHERE a = 1 AND b = 2 AND c = 3
  • ORDER BY a, b, c

❌ 不支持:WHERE b = 2、WHERE c = 3、WHERE a = 1 AND c = 3 (跳過 b)

覆蓋索引

-- 查詢僅需讀取索引,無需回表
EXPLAIN SELECT id, email FROM users WHERE email = 'test@example.com';
-- Extra: Using index

數據類型選擇原則

  1. 越小越好:TINYINT < SMALLINT < INT < BIGINT;CHAR 固定長,VARCHAR 可變長
  2. 簡單優於複雜:整型 > 日期時間 > 字符串;IP 存 INT UNSIGNED (INET_ATON/INET_NTOA)
  3. 避免 NULL:NOT NULL DEFAULT '' 或 DEFAULT 0;NULL 增加統計複雜度、索引存儲額外位圖
  4. 字符集一致:關聯列字符集/校對規則必須一致,否則隱式轉換導致索引失效

索引失效常見場景

場景示例解決
隱式類型轉換WHERE varchar_col = 123WHERE varchar_col = '123'
函數/表達式作用於索引列WHERE DATE(created_at) = '2024-01-01'WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'
LIKE 以 % 開頭WHERE name LIKE '%abc'全文索引或應用層反轉存儲
OR 條件未全覆蓋索引WHERE a = 1 OR b = 2改用 UNION ALL 或組合索引
不等於 (!=, <>)WHERE status != 1全表掃描,考慮反向查詢
IS NULL/IS NOT NULLWHERE col IS NULLMySQL 5.7+ 可用索引,但選擇性需評估

索引維護與監控

碎片整理

-- 重建索引(InnoDB)
ALTER TABLE tbl_name ENGINE=InnoDB;
-- 或
OPTIMIZE TABLE tbl_name;

監控指標

  • Handler_read_key / Handler_read_rnd_next 比例:越高說明索引命中率越好
  • Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests:命中率
  • SHOW ENGINE INNODB STATUS 中 BUFFER POOL AND MEMORY 區塊

總結:索引優化清單

  1. 慢查詢日誌識別問題 SQL
  2. EXPLAIN 分析執行計劃:type=ref/range/eq_ref、key、rows、Extra
  3. 根據查詢模式設計組合索引,遵循最左前綴
  4. 控制單表索引數量(建議 ≤ 5-6 個),權衡寫入性能
  5. 定期 ANALYZE TABLE 更新統計信息
  6. 大表 DDL 使用 pt-osc/gh-ost
copyright © 萬盛學電腦網 all rights reserved