MySQL索引增删查改实战：语法与操作详解

创建索引：类型选择与前缀优化

在MySQL中，创建索引是提升查询性能的关键手段。其基础语法结构如下：

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
  [USING index_type]
  ON table_name (index_col_name,...)

关键参数说明

• 索引类型（UNIQUE/FULLTEXT/SPATIAL）：括号内的关键字可选。默认情况下不指定即为普通索引。
  • UNIQUE：唯一索引，列值必须唯一。
  • FULLTEXT：全文索引，用于文本内容的快速搜索。
  • SPATIAL：空间索引，用于地理空间数据。
• 索引实现方式（USING index_type）：
  • BTREE：B+树索引，是MyISAM和InnoDB存储引擎的唯一选择，也是默认值。
  • HASH：哈希索引，仅支持MEMORY和HEAP存储引擎，默认为此类型。
• 列定义（index_col_name）：指定需要建立索引的列名。支持多列复合索引，列名间用逗号分隔。此外，对于CHAR或VARCHAR类型，可使用(length)指定前缀长度。

前缀索引的应用场景

为了平衡查询速度与存储空间，常对长字符串字段使用前缀索引。例如，为User表的username字段（类型VARCHAR(50)）仅基于前6个字符创建索引：

CREATE INDEX idx_user_username ON user (username(6));

这种方式能显著减小索引文件大小，节省磁盘空间，并可能提升INSERT操作的速度。由于前6个字符通常具有足够区分度，查询效率损失极小。需注意以下限制：

• MySQL规定前缀长度最大值为255字节。
• 对于MyISAM或InnoDB引擎，最长可达1000字节。
• 强制要求：TEXT和BLOB等大数据类型字段必须指定前缀长度才能创建索引，否则语句将执行失败。

变体语法：通过ALTER TABLE创建

除了CREATE INDEX，也可以通过修改表结构来添加索引：

ALTER TABLE table_name
ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name (index_col_name,...) [USING index_type]

注意：只有当数据表存储引擎为MyISAM、InnoDB或BDB时，才允许向包含NULL值的列或TEXT/BLOB列添加索引。

删除索引：安全移除方法

移除不再需要的索引可使用ALTER TABLE ... DROP INDEX语句。该操作会立即从表中移除指定的索引结构。

-- 删除指定表中指定名称的索引
ALTER TABLE table_name
DROP INDEX index_name;

示例：删除上述创建的idx_user_username索引：

-- 删除名称为idx_user_username的索引
ALTER TABLE user
DROP INDEX idx_user_username;

修改索引：间接实现策略

MySQL并未提供直接的“修改索引”指令（如ALTER INDEX用于改变属性）。若需更改索引的定义（如增加前缀长度、改变列顺序或替换索引类型），必须采用先删除后重建的策略。

-- 第1步：删除旧索引
ALTER TABLE user
DROP INDEX idx_user_username;

-- 第2步：以新的定义创建同名索引
CREATE INDEX idx_user_username ON user (username(8));

此过程会短暂影响表的写入性能，建议在业务低峰期执行。同时，请注意重新创建索引可能会更新统计信息，从而改变查询优化器的行为。

查看索引：确认配置状态

在使用索引优化前，建议先查看表中已有的索引情况。可以使用SHOW INDEX命令获取详细信息，包括索引名称、列顺序、非空约束及基数统计等。

-- 方式一：未指定当前库时，需注明FROM子句
SHOW INDEX FROM table_name FROM db_name;

-- 方式二：使用db_name.table_name格式
SHOW INDEX FROM db_name.table_name;

执行后返回的结果集包含Non_unique（是否唯一）、Key_name（索引名）、Seq_in_index（列顺序）、Column_name（列名）等字段，有助于判断索引是否按预期生效。

以上就是MySQL中索引基本增删查改操作的详细内容，更多关于数据库性能优化的资料请关注本站其它相关文章！