MySQL 索引核心操作：CREATE/DROP/SHOW一、索引索引是一种数据结构，例如 B-Tree，它提高了从表

一、索引

索引是一种数据结构，例如 B-Tree，它提高了从表中检索数据行的速度，但需要额外的写入和存储来维护它。

索引的本质就是为了加速查询，避免全表扫描。

索引类型

默认情况下，如果不指定索引类型，MySQL 就创建 B-Tree 索引。下面显示了基于表的存储引擎允许的索引类型：

存储引擎	允许的索引类型
InnoDB	`BTREE`
MyISAM	`BTREE`
MEMORY/HEAP	`HASH`, `BTREE`

二、 `CREATE INDEX` 语法

CREATE [UNIQUE] INDEX index_name
[USING {BTREE | HASH}]
ON table_name (column_list)
[algorithm_option | lock_option];

UNIQUE 关键字表明此索引为唯一索引。它是可选的。
index_name 是索引的名字。一个表中不应该出现两个相同名字的索引。
table_name 是表的名字。
column_list 是表中的列名。多个列名使用逗号分隔。
USING 子句指定索引的类型。可选值：BTREE，HASH。它是可选的。
algorithm_option 指定删除索引的算法.

create index实例

ALGORITHM [=] {DEFAULT | INPLACE | COPY}

ALGORITHM 子句是可选的。默认为 INSTANT。如果不支持 INSTANT，则使用 INPLACE。

使用 DEFAULT 和省略 ALGORITHM 子句效果相同。

以下是对各个算法的说明：

COPY：对原表的副本进行操作，将原表中的表数据逐行复制到新表中。不允许并发 DML。
INPLACE: 操作避免复制表数据，但可能会就地重建表。在操作的准备和执行阶段，可能会短暂地对表进行独占元数据锁定。通常，支持并发 DML。
INSTANT: 操作只修改数据字典中的元数据。在操作的执行阶段，可能会短暂地对表进行独占元数据锁定。表数据不受影响，使操作瞬间完成。允许并发 DML。

lock_option 指定删除索引的并发控制策略。它使用以下的语法：

LOCK [=] {DEFAULT | NONE | SHARED | EXCLUSIVE}

LOCK 子句是可选的。以下是对各个并发策略的说明：

DEFAULT

给定 ALGORITHM 子句（如果有）和 ALTER TABLE 操作的最大并发级别：如果支持，则允许并发读取和写入。如果不是，则允许并发读取（如果支持）。如果不是，则强制执行独占访问。
NONE

如果支持，允许并发读取和写入。否则，会发生错误。
SHARED

如果支持，允许并发读取但阻止写入。即使存储引擎支持给定 ALGORITHM 子句（如果有）和 ALTER TABLE 操作的并发写入，写入也会被阻止。如果不支持并发读取，则会发生错误。
EXCLUSIVE

强制执行独占访问。即使存储引擎支持给定 ALGORITHM 子句（如果有）和 ALTER TABLE 操作的并发读/写，也会这样做。

1.没有索引时的查询

SELECT * FROM sakila.actor WHERE first_name = 'NICK';
+----------+------------+-----------+---------------------+
| actor_id | first_name | last_name | last_update         |
+----------+------------+-----------+---------------------+
|        2 | NICK       | WAHLBERG  | 2006-02-15 04:34:33 |
|       44 | NICK       | STALLONE  | 2006-02-15 04:34:33 |
|      166 | NICK       | DEGENERES | 2006-02-15 04:34:33 |
+----------+------------+-----------+---------------------+

执行情况

EXPLAIN 显示 type: ALL，表示 MySQL 做了全表扫描（Full Table Scan）。
rows: 201 说明它扫描了表里的 201 行数据，逐行检查 first_name 是否等于 'NICK'。
就像在一本没有目录的书里找 “NICK”，只能一页一页翻。

2.创建索引

CREATE INDEX first_name ON actor(first_name);

这相当于给 first_name 列建了一本目录：

目录里按字母顺序排好了所有 first_name 的值。
每个值都指向它在表里的真实行位置。

3. 验证：索引是否生效

mysql>> SHOW INDEXES FROM actor;

当多一条 Key_name: first_name 的记录，说明索引创建成功。

4. 对比：有索引后的查询

EXPLAIN SELECT * FROM sakila.actor WHERE first_name = 'NICK';

type: ref：表示 MySQL 现在是通过索引直接定位，而不是全表扫描。
key: first_name：明确使用了我们刚建的索引。
rows: 3：只需要从索引里定位 3 行，而不是扫描 201 行。
就像现在有了目录，直接翻到 “N” 开头的页码，快速找到那 3 条记录。

三、DROP INDEX语法

1、DROP INDEX

DROP INDEX 就是用来删除表中已经存在的索引，常见场景：

建错了索引，需要修正
批量插入 / 更新大量数据前，先删索引提升速度，之后再重建
索引长期不用，占用空间且拖慢写入

DROP INDEX index_name
ON table_name
[algorithm_option | lock_option];

index_name：要删除的索引名
table_name：索引所在的表名
algorithm_option 和 lock_option 是可选的高级选项，用于控制删除时的性能和并发行为

2、高级选项：ALGORITHM（算法）

这个选项决定了删除索引时 MySQL 内部如何操作数据:

选项	说明
`COPY`	复制全表到新表，不允许并发 DML，性能差
`INPLACE`	原地修改，避免复制数据，支持并发 DML，性能好（默认）
`DEFAULT`	等同于省略 ALGORITHM，MySQL 自动选择最优算法

3、高级选项：LOCK（并发锁）

这个选项控制删除索引时，其他会话对表的访问权限：

选项	说明
`DEFAULT`	MySQL 自动选择最大并发级别，优先支持读写
`NONE`	允许并发读写，不支持则报错
`SHARED`	允许并发读，但阻止写
`EXCLUSIVE`	强制独占锁，不允许任何并发读写

4、实际例子

删除普通索引
```
DROP INDEX first_name ON actor;
```
这会删除 actor 表上名为 first_name 的索引。
验证删除结果
```
SHOW INDEXES FROM actor;
```
执行后，输出的列表中就不再包含 first_name 这个索引了。
删除主键索引

主键索引的名字固定为 PRIMARY，删除语法稍有不同：
```
DROP INDEX `PRIMARY` ON t;
```

5、底层映射

在 MySQL 内部，DROP INDEX 语句会被映射为 ALTER TABLE ... DROP INDEX ... 语句来执行，所以你也可以用 ALTER TABLE 语法来删除索引，效果是一样的。

四、SHOW INDEXES语法

SHOW INDEXES 是 MySQL 中用来查看表中所有索引信息的命令，主要用途：

检查索引是否创建成功
排查查询性能问题（如索引未被使用）
了解索引的类型、唯一性、列顺序等细节

-- 方式一：直接指定数据库和表
SHOW INDEXES FROM db_name.table_name;

-- 方式二：先切换数据库，再查看表
USE db_name;
SHOW INDEXES FROM table_name;

-- 方式三：使用 IN 关键字
SHOW INDEXES FROM table_name IN db_name;

注意：

INDEXES 可以替换为 INDEX 或 KEYS
FROM 可以替换为 IN，效果完全一样

1、关键返回列解读

SHOW INDEXES 返回的每一行，代表索引中的一列。如果一个索引包含多列，就会显示多行。下面是最重要的几列：

列名	含义	关键点
`Table`	表名	表示索引属于哪个表
`Non_unique`	是否唯一	`0` = 唯一索引（如主键、UNIQUE）；`1` = 普通索引
`Key_name`	索引名	主键索引的名字固定为 `PRIMARY`
`Seq_in_index`	列在索引中的顺序	从 1 开始，复合索引中列的顺序很重要
`Column_name`	索引列名	被索引的列
`Cardinality`	基数	索引中唯一值的估算数量，基数越高，索引越有效
`Index_type`	索引类型	常见的有 `BTREE`、`FULLTEXT` 等
`Visible`	是否可见	`YES` 表示查询优化器可以使用这个索引

2、实用技巧：过滤索引

可以用 WHERE 子句对结果进行过滤，快速找到你* 关注的索引*：

查看所有唯一索引（包括主键）：

SHOW INDEXES FROM sakila.film WHERE Non_unique = 0;

查看指定名称的索引：

SHOW INDEXES FROM sakila.film WHERE Key_name = 'idx_title';

查看复合索引的所有列：

SHOW INDEXES FROM sakila.film WHERE Key_name = 'idx_fk_language_id';

3、实际例子

-- 查看 film 表的所有索引
SHOW INDEXES FROM sakila.film;

输出结果解读：

PRIMARY：主键索引，唯一，列是 film_id
idx_title：普通索引，列是 title
idx_fk_language_id：外键索引，列是 language_id

MySQL 索引核心操作：CREATE/DROP/SHOW