MySQL 索引(Index)操作详解：添加、修改和删除

MySQL 索引(Index)操作详解：添加、修改和删除

索引是MySQL中用于加速查询的重要数据结构。合理使用索引可以显著提高查询性能，但不当使用也可能带来负面影响。下面详细介绍MySQL中索引的添加、修改和删除操作。

一、索引的基本概念

索引是一种特殊的数据库结构，它类似于书籍的目录，可以帮助数据库系统快速定位数据。MySQL支持多种索引类型：

• 普通索引（INDEX）
• 唯一索引（UNIQUE INDEX）
• 主键索引（PRIMARY KEY）
• 全文索引（FULLTEXT INDEX）
• 空间索引（SPATIAL INDEX）
• 组合索引（复合索引）

二、添加索引

1. 创建表时添加索引

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),  -- 主键索引
    UNIQUE INDEX idx_email (email),  -- 唯一索引
    INDEX idx_username (username)  -- 普通索引
);

2. 使用ALTER TABLE添加索引

-- 添加普通索引
ALTER TABLE users ADD INDEX idx_username (username);

-- 添加唯一索引
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

-- 添加主键索引(如果表没有主键)
ALTER TABLE users ADD PRIMARY KEY (id);

-- 添加全文索引(仅适用于MyISAM和InnoDB，且仅适用于CHAR, VARCHAR, TEXT列)
ALTER TABLE articles ADD FULLTEXT INDEX idx_content (content);

-- 添加组合索引
ALTER TABLE users ADD INDEX idx_name_email (username, email);

3. 使用CREATE INDEX语句添加索引

-- 创建普通索引
CREATE INDEX idx_username ON users(username);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 创建组合索引
CREATE INDEX idx_name_email ON users(username, email);

三、修改索引

MySQL没有直接的"修改索引"命令，通常需要先删除旧索引再创建新索引。

1. 修改索引名称

-- 先删除旧索引
ALTER TABLE users DROP INDEX idx_old_name;
-- 再创建新索引
ALTER TABLE users ADD INDEX idx_new_name (username);

2. 修改索引列

-- 先删除旧索引
DROP INDEX idx_name ON users;
-- 再创建包含新列的索引
CREATE INDEX idx_name ON users(username, email);

四、删除索引

1. 使用DROP INDEX语句

-- 删除普通索引或唯一索引
DROP INDEX idx_username ON users;

-- 删除全文索引
DROP INDEX idx_content ON articles;

2. 使用ALTER TABLE语句

-- 删除普通索引
ALTER TABLE users DROP INDEX idx_username;

-- 删除主键索引
ALTER TABLE users DROP PRIMARY KEY;

五、查看索引信息

-- 查看表的索引信息
SHOW INDEX FROM users;

-- 使用信息模式查看索引
SELECT * FROM information_schema.STATISTICS 
WHERE TABLE_NAME = 'users';

六、索引使用的最佳实践

1. 选择合适的列建立索引：

   • 常用于WHERE子句的列
   • 常用于JOIN操作的列
   • 常用于ORDER BY或GROUP BY的列

2. 避免过度索引：

   • 每个索引都会占用存储空间
   • 索引会降低INSERT、UPDATE和DELETE的性能

3. 组合索引的列顺序：

   • 将选择性高的列放在前面
   • 遵循最左前缀原则

4. 避免在索引列上使用函数：

   -- 不推荐，无法使用索引
   SELECT * FROM users WHERE YEAR(created_at) = 2023;
   
   -- 推荐，可以使用索引
   SELECT * FROM users WHERE created_at BETWEEN '2023-01-01' AND '2023-12-31';
   

5. 定期分析索引使用情况：

   -- 查看未使用的索引
   SELECT * FROM sys.schema_unused_indexes;
   

通过合理使用索引，可以显著提高MySQL数据库的查询性能，但需要根据实际业务场景和数据特点进行优化。