建立索引三種方式
1. ALTER TABLE(最常用,支援多索引一次建立)
-- 普通索引
ALTER TABLE tbl_name ADD INDEX idx_name (col1, col2);
-- 唯一索引
ALTER TABLE tbl_name ADD UNIQUE uk_name (col1);
-- 主鍵
ALTER TABLE tbl_name ADD PRIMARY KEY (col1);
- 單條語句可加多個:
ALTER TABLE t ADD INDEX i1(a), ADD UNIQUE i2(b), ADD PRIMARY KEY(c);
index_name 可省略,MySQL 自動以首欄位命名。
2. CREATE INDEX(獨立語句,語義清晰)
CREATE INDEX idx_name ON tbl_name (col1, col2);
CREATE UNIQUE INDEX uk_name ON tbl_name (col1);
- **不支援建立主鍵**(需用
ALTER TABLE)。
- 索引名**不可省略**。
3. 建表時同步定義(DDL 原子)
CREATE TABLE t (
id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100),
UNIQUE KEY uk_email (email),
INDEX idx_name (name)
) ENGINE=InnoDB;
刪除索引
DROP INDEX(語義明確)
DROP INDEX idx_name ON tbl_name;
ALTER TABLE 刪除(可一次刪多個)
ALTER TABLE tbl_name DROP INDEX idx_name;
ALTER TABLE tbl_name DROP PRIMARY KEY; -- 僅主鍵用此語法
- 刪除主鍵不需指定名稱(一張表只有一個 PK)。
- 若無 PK 但有 UNIQUE,
DROP PRIMARY KEY 會刪除第一個 UNIQUE 索引。
實戰操作示範
mysql> USE tpsc;
mysql> ALTER TABLE tpsc ADD INDEX shili (tpmc);
Query OK, 2 rows affected (0.08 sec)
mysql> DROP INDEX shili ON tpsc;
Query OK, 2 rows affected (0.08 sec)
索引欄位變更的連帶影響
- 刪除欄位 → 組合索引中該欄位自動移除;若組合索引所有欄位皆刪,索引整體消失。
- 修改欄位型別/長度 → 索引自動重建(可能鎖表),檢查長度是否超限(InnoDB 767/3072 bytes)。
效能與命名規範建議
- 命名規範:
idx_ 普通、uk_ 唯一、pk_ 主鍵(通常不顯式命名)。
- 組合索引遵循最左前綴原則:
INDEX idx_a_b_c (a,b,c) 支援 a、a,b、a,b,c 查詢。
- 大表加索引用
pt-online-schema-change 或 8.0+ ALGORITHM=INPLACE 避免鎖表。
- 定期
ANALYZE TABLE 更新統計資訊,優化器才能正確選擇索引。