萬盛學電腦網

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

MySQL 建立與刪除索引完整語法指南:ALTER TABLE、CREATE INDEX、DROP INDEX 實戰

建立索引三種方式

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 更新統計資訊,優化器才能正確選擇索引。
copyright © 萬盛學電腦網 all rights reserved