MySQL索引详解：类型、创建删除与优化原则

一、MySQL索引简介

索引是MySQL数据库为了加快数据查询速度而创建的“目录”。对于InnoDB引擎而言，索引是表空间的一部分，而非独立的物理文件。MySQL主要支持五种索引类型，每种类型针对不同的数据约束和查询需求：

• 普通索引：最基本的索引形式，无任何限制，仅用于加速查询。
• 唯一性索引：索引列的值必须唯一，允许为空值（具体取决于引擎配置）。
• 主键索引：一种特殊的唯一索引，不允许空值，且每张表只能有一个主键。
• 复合索引：在多个列上创建的索引，遵循最左前缀原则。
• 全文索引：用于大规模文本数据的模糊匹配和分词搜索。

二、MySQL五种类型索引详解

第1步：使用普通索引加速基础查询

普通索引适用于任何字段，主要用于优化特定列的检索性能。它不对数据值施加额外约束。

1. 建表时添加

在创建表结构时，直接使用 INDEX 或 KEY 关键字指定索引列。

create table exp(id int, name varchar(20), index exp_name(name));

或者使用别名写法：key exp_name(name)。若省略索引名称，MySQL将自动使用该列名作为索引名。

2. 建表后添加

利用 ALTER TABLE 语句为已有表添加索引：

alter table exp add index exp_id(id);

3. 删除与查询索引

删除索引时需明确索引名称而非列名：

alter table exp drop index exp_name;

若忘记索引名称，可通过以下命令查看表中所有索引：

show index from exp;

完成状态判断：执行 DESC exp; 查看表结构时，被索引列的 Key 列显示为 MUL，表示该列已包含普通索引。

第2步：创建唯一性索引保证数据规范

唯一性索引要求索引列的值在整个表中必须唯一，常用于身份证号、学号等具有唯一标识性的字段，避免重复录入。

1. 建表时添加

使用 UNIQUE KEY 或 UNIQUE INDEX 关键字：

create table exp (id int, name varchar(20), unique key (name));

2. 删除唯一索引

alter table exp drop index name;

完成状态判断：DESC 查看表结构时，对应列的 Key 显示为 UNI。

第3步：设置主键索引实现最高效查询

主键索引是查询速度最快的索引类型，每个表仅能有一个。其列值既不能重复也不能为空（NOT NULL）。

1. 定义主键

建表时可直接定义为 PRIMARY KEY，或在已有表中通过 ALTER 添加：

create table exp(id int, name varchar(20), primary key (id));
alter table exp add primary key (id);

2. 删除主键的注意事项

删除主键需使用专用语法，注意此处 key 不可替换为 index：

alter table exp drop primary key;

排错提示：如果删除失败，可能是因为该字段设置了 AUTO_INCREMENT（自增）属性。此时需先移除自增属性，方可删除主键索引（见下图示意）：

完成状态判断：DESC 输出中主键列的 Key 显示为 PRI。

第4步：构建复合索引优化多条件查询

当查询涉及多个列时（如根据IP和端口查找数据包），可使用复合索引。它相当于一个联合主键，各组合列均不允许为空且组合值唯一。

1. 创建与删除示例

以下展示了错误的拼写修正（原稿中的 pirmary 和 dorp 已更正为 primary 和 drop）：

create table exp (ip varchar(15), port int, primary key (ip, port));
-- 或者使用 alter 添加
-- alter table exp add primary key(ip, port);
-- 删除复合索引
-- alter table exp drop primary key;

完成状态判断：DESC 输出中，参与复合索引的每一列 Key 均显示为 PRI。

限制说明：复合索引的任何列都不能为空，否则会导致插入错误或索引失效。如果两行记录的复合列数据完全相同，也会触发报错。

第5步：配置全文索引处理文本模糊搜索

传统的 LIKE '%...%' 在大文本模糊匹配下效率极低。全文索引通过分词技术建立关键词频率索引，专为解决此问题而生。

1. 兼容性要求

• 版本限制：MySQL 3.2+ 支持基本功能；5.7+ 内置 ngram 插件以支持中文；早期版本仅限 MyISAM 引擎。
• 当前环境：MySQL 5.6+ 的 InnoDB 和 MyISAM 均支持。
• 类型限制：仅 CHAR, VARCHAR, TEXT 类型的字段可建全文索引。

2. 创建语法

create table exp (id int, content text, fulltext key (content)) engine=MyISAM;
alter table exp add fulltext index (content);
alter table exp drop index content;

3. 正确查询方式

全文索引不能使用 LIKE，必须使用 MATCH ... AGAINST 语法：

select * from exp where match(content) against ('a');

其中 content 为建有全文索引的列，'a' 为搜索关键词。

三、MySQL索引使用原则

合理使用索引需要在性能和维护成本之间取得平衡，遵循以下原则：

1. 空间换时间：索引会占用额外的磁盘存储空间，但能显著提升读取速度。
2. 读写权衡：虽然索引加快了查询，但在插入、更新和删除数据时，数据库需同步维护索引结构，这会降低写入性能。
3. 按需添加：并非越多越好。对于数据量小的表，全表扫描可能比走索引更快；只有频繁用于 WHERE 过滤、排序（ORDER BY）或分组（GROUP BY）的字段才值得建立索引。
4. 去重原则：如果某字段需要唯一性约束，应优先选择唯一索引或主键，而非普通索引，以确保数据完整性并减少冗余存储。

以上就是MySQL索引类型详解及创建优化的详细内容，更多关于MySQL索引的资料请关注本站其它相关文章！