萬盛學電腦網

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

MySQL 索引操作命令全集:類型、創建、刪除、修改、查看、前綴索引、全文索引

索引是查詢性能的核心杠桿。本文系統整理 MySQL 索引六大類型、創建/刪除/修改/查看命令、前綴索引技巧、全文索引配置、復合索引最左前綴原理,並給出選型決策樹,助你精準建索引、拒絕盲目加索引。

六大索引類型速覽

類型關鍵字特點適用場景
普通索引KEY / INDEX僅加速查詢,允許重複值WHERE/JOIN/ORDER BY 高頻列
唯一索引UNIQUE值唯一,插入時自動檢查業務鍵:手機號、郵箱、身份證
主鍵索引PRIMARY KEY唯一+非空,聚簇索引(InnoDB)每張表必須有,建議無業務含義自增 ID
外鍵索引FOREIGN KEY自動創建內部索引輔助約束引用完整性強一致性場景
復合索引INDEX(a,b,c)多列組合,遵循最左前綴原則多條件組合查詢
全文索引FULLTEXT分詞倒排索引,支持自然語言搜索長文本字段模糊搜索(MyISAM/InnoDB 5.6+)

創建索引語法大全

-- 1. 獨立 CREATE INDEX
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX idx_name
    [USING BTREE|HASH]
    ON tbl_name (col_name[(length)], ...);

-- 2. ALTER TABLE 添加(同等效果)
ALTER TABLE tbl_name
ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX idx_name (col_name[(length)], ...) [USING BTREE|HASH];

-- 3. 建表時直接定義
CREATE TABLE t (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(100),
    UNIQUE KEY uk_email (email),
    INDEX idx_name (name(10))  -- 前綴索引
);

關鍵選項解析

  • USING BTREE:B+ 樹,範圍查詢、排序、最左前綴,InnoDB/MyISAM 默認
  • USING HASH:哈希索引,等值查詢 O(1),Memory/Heap 引擎默認,不支持範圍/排序
  • col_name(length):前綴索引,CHAR/VARCHAR/BLOB/TEXT 必須指定長度,VARCHAR(255) 建議 10-20 字符
  • InnoDB 前綴最大 767 字節(innodb_large_prefix=ON 可擴至 3072)

復合索引與最左前綴原則

INDEX idx_a_b_c (a, b, c)

等價於擁有:(a)、(a,b)、(a,b,c) 三個索引

可用:WHERE a=1 / WHERE a=1 AND b=2 / WHERE a=1 AND b=2 AND c=3

不可用:WHERE b=2 / WHERE c=3 / WHERE b=2 AND c=3(未包含最左列 a)

索引修改策略

MySQL 無 ALTER INDEX,標準流程:

-- 1. 刪除舊索引
ALTER TABLE user DROP INDEX idx_username;

-- 2. 創建新索引(可同名)
CREATE INDEX idx_username ON user (username(8));

⚠️ 大表 DDL 會鎖表(5.6+ Online DDL 緩解),建議:

  • 使用 pt-online-schema-change 或 gh-ost 無鎖變更
  • 低峰期執行,監控 SHOW PROCESSLIST

查看索引

-- 方式一
SHOW INDEX FROM tbl_name [FROM db_name];

-- 方式二
SHOW INDEX FROM db_name.tbl_name;

-- 關鍵輸出列
Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment

Cardinality:不重複值估算數,越接近行數選擇性越高

全文索引專題

創建

-- 建表時
CREATE TABLE article (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT (title, content)
) ENGINE=MyISAM;  -- 5.6 前僅 MyISAM 支持

-- 事後添加
ALTER TABLE article ADD FULLTEXT INDEX ft_title_content (title, content);

查詢語法

-- 自然語言模式(默認)
SELECT * FROM article
WHERE MATCH(title, content) AGAINST('search keywords');

-- 布爾模式(精確控制)
SELECT * FROM article
WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

-- 查詢擴展模式
SELECT * FROM article
WHERE MATCH(title, content) AGAINST('database' WITH QUERY EXPANSION);

關鍵配置

[mysqld]
ft_min_word_len = 3          # 最小索引詞長度(默認 4),修改後需 REPAIR TABLE 重建
ft_stopword_file = ''        # 禁用停用詞表(默認內置英文停用詞)

中文全文搜索

MySQL 內建全文索引不支持中文分詞。生產環境請使用:

  • Elasticsearch(標準方案,IK 分詞器)
  • Sphinx / Coreseek(輕量級,支援中文)
  • MySQL 8.0+ ngram parser:FULLTEXT INDEX ft_content (content) WITH PARSER ngram(按 bigram 分詞,精度一般)

索引選型決策樹

  1. 列是否在 WHERE/JOIN/ORDER BY 中高頻出現?→ 否:不建索引
  2. 區分度如何?COUNT(DISTINCT col)/COUNT(*) > 0.3 → 低:考慮組合索引或不建
  3. 是否需唯一約束?→ 是:UNIQUE
  4. 是否多列組合查詢?→ 是:INDEX(a,b) 按最左原則排序
  5. 是否長文本模糊搜索?→ 是:全文索引 / ES
  6. 字段長度大?→ 前綴索引 col(10)

索引是把雙刃劍:讀快寫慢、佔空間、維護成本。定期 ANALYZE TABLE 更新統計信息,配合 EXPLAIN 驗證執行計劃,養成「先驗證後上線」習慣。

copyright © 萬盛學電腦網 all rights reserved