本文共 1788 字，大约阅读时间需要 5 分钟。

MySQL索引类型及应用指南

1. 主键索引

创建

主键索引是数据库表中最常用的索引类型之一。创建主键索引可以通过以下两种方式：

例如：

CREATE TABLE t1 (  id INT PRIMARY KEY,  name VARCHAR(255));

或者：

ALTER TABLE t1 ADD PRIMARY KEY (id);

查看

可以通过SHOW INDEX或DESCRIBE命令查看索引信息：

SHOW INDEX FROM t1;

删除

删除主键索引只需使用ALTER TABLE命令：

ALTER TABLE t1 DROP PRIMARY KEY;

2. 唯一索引

创建

唯一索引的特点是：

• 索引值必须唯一（不允许重复）
• 允许存储NULL值，且NULL值可以重复

例如：

CREATE TABLE t2 (  id INT,  unique_col VARCHAR(255) UNIQUE);

删除

删除唯一索引的命令与主键索引类似：

ALTER TABLE t2 DROP INDEX unique_col;

3. 普通索引

创建

普通索引允许索引列有重复值，但在实际应用中，建议避免为重复性高的列或唯一性较差的列创建普通索引。创建普通索引的命令为：

ALTER TABLE t2 ADD INDEX name_index(name);

删除

删除普通索引的命令：

ALTER TABLE t2 DROP INDEX name_index;

4. 复合索引

创建

复合索引由多个列组成，常用于高效查询。可以在建表时创建，也可以在建表后添加。

例如：

CREATE TABLE t3 (  id INT,  name VARCHAR(255),  created_date DATE,  PRIMARY KEY (id, name, created_date));

删除

删除复合索引的命令：

ALTER TABLE t3 DROP INDEX name_add_index;

5. 全文索引

创建

全文索引主要用于对文本或类似数据进行快速检索。仅适用于MyISAM引擎，并且目前全文索引只支持英文内容。

例如：

CREATE TABLE articles (  id INT,  title VARCHAR(255),  fulltext TEXT,  PRIMARY KEY (id),  FULLTEXT idx_fulltext (title, fulltext));

应用

在使用全文索引时，查询条件必须使用MATCH和AGAINST：

SELECT * FROM articles WHERE MATCH(title, fulltext) AGAINST('查询关键词');

删除

删除全文索引的命令：

ALTER TABLE articles DROP INDEX title;

6. 索引应用注意事项

1. 索引适用场景

• 频繁出现在WHERE、ORDER BY等语句中的字段：适合建立索引
• 唯一性较差的字段：不适合单独创建索引
• 更新频率高的字段：不适合频繁更新的字段建立索引
• 不在WHERE语句中出现的字段：不适合建立索引

2. 索引效率

• 复合索引：只要查询条件中包含最左边的字段，索引就会生效，建议将最短的列放在最左边。
• 模糊查询：LIKE 'xyz%'会使用索引，而LIKE '%xyz%'不会。如果需要支持模糊查询，建议使用全文索引。
• 条件语句：
  • OR：OR前后必须都为索引
  • AND：AND前后至少有一个为索引
• 全表扫描：如果Mysql估计使用全表扫描比索引更快，则不使用索引
• 字符串类型字段：在WHERE条件中，字段值必须加上引号，否则不会使用索引

3. 确认索引是否生效

可以使用EXPLAIN命令进行测试：

EXPLAIN SELECT * FROM t1 WHERE id = 1;

7. 索引删除注意事项

如果数据来源于其他计算机，索引可能无效。解决方法是：

通过以上方法，可以有效提升数据库查询性能，减少锁竞争和全表扫描的次数。

转载地址：http://axbfk.baihongyu.com/