在 MySQL 中，创建索引通常有三种常见时机：
1) 在建表的同时直接创建索引；
2) 在修改已有数据表结构时新增索引；
3) 通过专门的 `CREATE INDEX` 语句为现有表建立索引。
一、MySQL 创建索引的三种方法
方法 1: 使用 `CREATE INDEX` 语句（最常用）
这种方式专门用于在已经存在的数据表上创建索引，但不能直接创建主键索引。
语法：
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX <索引名>
ON <表名> (<列名> [(<长度>)]] [ASC | DESC], ...);
参数说明： `UNIQUE`: 创建唯一索引，用于保证字段值不重复。
`FULLTEXT`: 创建全文索引，常用于文章、内容检索等文本搜索场景。
`SPATIAL`: 创建空间索引，通常用于地理位置或 GIS 数据。
`<索引名>`: 索引名称，必须在当前表中保持唯一。
`<表名>`: 需要创建索引的目标数据表。
`<列名>`: 需要建立索引的字段，可以指定多个列来创建复合索引。
`<(长度)>`: 可选参数。用于前缀索引，只对字段前 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 <表名>
如果需要添加[UNIQUE|FULLTEXT|SPATIAL]类型的索引，可使用此语句：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)
这是最常见的 MySQL 索引类型，主要用于提升查询速度，但不要求字段值唯一。
方法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` 时，根据最左前缀原则，这个复合索引通常都可能被 MySQL 查询优化器利用。
5. 创建前缀索引 (Prefix Index)
前缀索引只针对字符串字段前面的一部分内容建立索引，适合在节省存储空间的同时提升检索效率。
CREATE INDEX idx_name_prefix ON tb_stu_info (name(10)); --只索引前10个字符
三、三种创建索引方式的对比与选择
特性 CREATE INDEX CREATE TABLE ALTER TABLE
使用时机 表已存在 创建新表时 表已存在
功能 可创建各种索引（除主键） 可创建所有类型的索引 可创建所有类型的索引
可读性 高，语义明确，表示当前操作就是创建索引。 中，索引定义与表结构写在一起。 中，属于修改表结构的一部分。
常用场景 最常用，适合表上线后进行索引优化和性能提升。 适合在建表阶段就明确索引设计。 适合批量调整表结构时顺便添加索引。
