MySQL 创建索引

创建索引主要有三种时机：

1) 建表时同时创建；

2) 修改已存在的表结构时添加；

3) 使用专门的 `CREATE INDEX` 语句为已存在的表添加。

一、三种创建索引的方法

方法 1: 使用 `CREATE INDEX` 语句 (最常用)

专门用于在已存在的表上创建索引（不能创建主键）。

语法：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX <索引名>

ON <表名> (<列名> [(<长度>)]] [ASC | DESC], ...);

参数说明：   `UNIQUE`: 创建唯一索引。

   `FULLTEXT`: 创建全文索引（用于文本搜索）。

   `SPATIAL`: 创建空间索引（用于地理数据）。

   `<索引名>`: 索引的名称，必须在表中唯一。

   `<表名>`: 要创建索引的目标表。

   `<列名>`: 要建立索引的列。可以指定多列创建复合索引。

   `<(长度)>`: 可选。用于前缀索引，只对列的前 N 个字符创建索引，能减小索引大小。常用于字符串类型的列。

   `ASC|DESC`: 可选。指定索引的排序顺序，升序或降序。默认为 `ASC`。

示例：

 在 `users` 表的 `email` 列上创建一个普通索引

CREATE INDEX idx_email ON users (email);

 在 `products` 表的 `name` 列前20个字符上创建索引

CREATE INDEX idx_name_prefix ON products (name(20));

 在 `orders` 表上创建 `user_id` 和 `order_date` 的复合索引

CREATE INDEX idx_user_order ON orders (user_id, order_date DESC);

方法 2: 使用 `CREATE TABLE` 语句

在创建新表的同时定义索引。

语法（内联在列定义中）：

CREATE TABLE table_name (
    id INT NOT NULL PRIMARY KEY,  --创建主键索引
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE,    --创建唯一约束（自动生成唯一索引）
    age INT,
    KEY idx_age (age),            --创建普通索引，索引名为 idx_age
    INDEX idx_name (name)         --创建普通索引，同上
);

语法（在语句末尾定义）： (更适合复合索引和主键)

CREATE TABLE table_name (
    id INT NOT NULL,
    name VARCHAR(100),
    email VARCHAR(100),
    age INT,
    PRIMARY KEY (id),            -- 创建主键索引
    UNIQUE KEY uk_email (email),  --创建唯一索引，命名为 uk_email
    KEY idx_age_name (age, name)  --创建复合索引，包含 age 和 name 两列
);

 方法 3: 使用 `ALTER TABLE` 语句

在修改已存在的表时添加索引。功能上与 `CREATE INDEX` 类似，但语法不同。

语法：

ALTER TABLE <表名>

ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX [<索引名>] (<列名> [(<长度>)] [ASC | DESC], ...);

 或者添加主键

ALTER TABLE <表名> ADD PRIMARY KEY (<列名>);

示例：

 为已存在的 `articles` 表添加一个普通索引

ALTER TABLE articles ADD INDEX idx_created_at (created_at);

 为 `users` 表添加唯一索引

ALTER TABLE users ADD UNIQUE INDEX uk_username (username);

 为 `posts` 表添加主键（如果原来没有）

ALTER TABLE posts ADD PRIMARY KEY (id);

二、创建不同类型的索引示例

1. 创建普通索引 (INDEX/KEY)

最常见的索引，仅用于加速查询，不强制唯一性。

方法1

CREATE INDEX idx_height ON tb_stu_info (height);

 方法2 (建表时)

CREATE TABLE tb_stu_info (
    id INT,
    name CHAR(45),
    height INT,
    INDEX (height)  --或 KEY (height)
);

 方法3

ALTER TABLE tb_stu_info ADD INDEX idx_height (height);
`SHOW CREATE TABLE` 输出： `KEY ``height`` (``height``)`

2. 创建唯一索引 (UNIQUE INDEX)

保证索引列的值必须是唯一的，可以有一个 NULL 值（除非列被定义为 `NOT NULL`）。

 方法1

CREATE UNIQUE INDEX uid_height ON tb_stu_info2 (height);

 方法2 (建表时)

CREATE TABLE tb_stu_info2 (
    id INT,
    height INT,
    UNIQUE INDEX (height) -- 或 UNIQUE KEY (height)
);

 方法3

ALTER TABLE tb_stu_info2 ADD UNIQUE INDEX uid_height (height);
`SHOW CREATE TABLE` 输出： `UNIQUE KEY ``height`` (``height``)`

 3. 创建主键索引 (PRIMARY KEY)

特殊的唯一索引，不允许有空值。每张表只能有一个主键。

 方法2 (建表时最常见)

CREATE TABLE tb_stu_info3 (
    id INT NOT NULL PRIMARY KEY, -- 内联方式
    name VARCHAR(100)
);

 或

CREATE TABLE tb_stu_info3 (
    id INT NOT NULL,
    name VARCHAR(100),
    PRIMARY KEY (id) -- 末尾方式
);

 方法3

ALTER TABLE tb_stu_info3 ADD PRIMARY KEY (id);

4. 创建复合索引 (Composite Index)

基于多个列的索引

CREATE INDEX idx_dept_age ON tb_stu_info (dept_id, age);

使用场景： 查询条件同时包含 `dept_id` 和 `age` 时，或者仅包含 `dept_id` 时，该索引都可能被用到（最左前缀原则）。

5. 创建前缀索引 (Prefix Index)

只对字符串列的前面一部分字符创建索引，节省空间。

CREATE INDEX idx_name_prefix ON tb_stu_info (name(10)); --只索引前10个字符

三、方法对比与选择

 特性  CREATE INDEX  CREATE TABLE  ALTER TABLE

使用时机 表已存在  创建新表时  表已存在

功能 可创建各种索引（除主键）  可创建所有类型的索引  可创建所有类型的索引

可读性 高。意图明确，就是创建索引。  中。索引定义与表结构混在一起。  中。是修改表结构的一部分。

常用场景 最常用。在表上线后优化性能。  建表时已知需要的索引。  在批量修改表结构时同时添加索引。