MySQL 學習筆記（三）：索引 類型、創建、優化原則與空間索引

MySQL 學習筆記（三）：索引 類型、創建、優化原則與空間索引

索引本質

索引是組織數據的結構，通過 B-Tree 或哈希表存儲鍵值，實現快速定位。InnoDB/MyISAM 支持 B-Tree，MEMORY 支持 Hash。

六大索引類型

• 普通索引
• 唯一索引
• 全文索引
• 單列索引
• 多列索引（復合索引）
• 空間索引

利弊權衡

利： 大幅提升查詢速度
弊： 佔用磁盤空間、降低寫入性能（維護索引開銷）

適用創建場景

1. WHERE 條件高頻字段
2. GROUP BY 分組字段
3. 主外鍵關聯字段
4. 需唯一約束字段

不適用場景

1. 查詢極少使用字段
2. 重複值極高字段（如性別、狀態）

普通索引創建三種方式

-- 1. 建表時
CREATE TABLE t_index1(
    id INT,
    name VARCHAR(20),
    INDEX index_id(id)
);

-- 2. 既有表添加
CREATE INDEX index_name ON t_index1(name);

-- 3. ALTER TABLE
ALTER TABLE t_index1 ADD INDEX index_name(name);

驗證：EXPLAIN SELECT * FROM t_index1 WHERE id=1\G 出現 possible_keys: index_id, key: index_id 即生效。

唯一索引

在普通索引基礎上加 UNIQUE 關鍵字，三種創建方式同理。

全文索引

針對 CHAR/VARCHAR/TEXT，MySQL 3.23.23+ 支持，僅 MyISAM 引擎可用。關鍵字 FULLTEXT。

多列索引（復合索引）

CREATE TABLE t_name(
    col1 TYPE,
    col2 TYPE,
    INDEX idx_col1_col2(col1, col2)
);

最左前綴原則： 僅當查詢條件包含第一列時，復合索引才會被使用。

空間索引

針對空間數據類型（GEOMETRY, POINT, LINESTRING, POLYGON），關鍵字 SPATIAL，列必須 NOT NULL，僅 MyISAM 支持。

CREATE TABLE index6(
    id INT,
    space GEOMETRY NOT NULL,
    SPATIAL INDEX index6_sp(space)
);

刪除索引

DROP INDEX index_name ON table_name;