MySQL索引添加与删除方法实际示例

好的，下面为你整理一份 MySQL 索引的添加与删除方法的实际示例，涵盖 单列索引、组合索引、唯一索引、全文索引 等常见场景，并附上查看索引、注意事项与性能建议，方便直接套用到项目中。

1. 查看现有索引

在操作之前，可以先查看表的索引情况：

-- 查看某表的所有索引（两种方法）
SHOW INDEX FROM table_name;

-- 或者使用 INFORMATION_SCHEMA
SELECT * 
FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_SCHEMA = 'your_database' AND TABLE_NAME = 'table_name';

2. 添加索引的方法与示例

2.1 普通索引（INDEX）

适用于加速普通查询条件过滤。

-- 方式1：CREATE INDEX
CREATE INDEX idx_column ON table_name(column_name);

-- 示例：为用户表的 email 字段添加普通索引
CREATE INDEX idx_email ON users(email);

-- 方式2：ALTER TABLE ADD INDEX
ALTER TABLE table_name ADD INDEX index_name(column_name);

-- 示例
ALTER TABLE users ADD INDEX idx_username(username);

2.2 唯一索引（UNIQUE INDEX）

保证列的值唯一，可加速查询且防止重复数据。

-- CREATE UNIQUE INDEX
CREATE UNIQUE INDEX uk_email ON users(email);

-- ALTER TABLE 方式
ALTER TABLE users ADD UNIQUE INDEX uk_phone(phone);

2.3 组合索引（多列索引）

适用于多条件查询，遵循最左前缀匹配原则。

-- 创建组合索引
CREATE INDEX idx_name_age ON users(last_name, first_name, age);

-- ALTER TABLE 方式
ALTER TABLE users ADD INDEX idx_status_created(status, created_at);

示例场景：经常查询 WHERE status = 'active' AND created_at > '2024-01-01'，组合索引可大幅提升性能。

2.4 主键索引（PRIMARY KEY）

每个表只能有一个主键索引，通常是表的唯一标识。

-- 建表时直接指定
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL
);

-- 为已有表添加主键（需确保列无 NULL 且无重复值）
ALTER TABLE users ADD PRIMARY KEY (id);

2.5 全文索引（FULLTEXT INDEX）

适用于 MATCH ... AGAINST 全文搜索（仅 MyISAM 和 InnoDB 支持，InnoDB 从 MySQL 5.6 开始支持）。

-- 创建全文索引
CREATE FULLTEXT INDEX ft_content ON articles(content);

-- ALTER TABLE 方式
ALTER TABLE articles ADD FULLTEXT ft_title_content(title, content);

使用示例：

SELECT * FROM articles 
WHERE MATCH(content) AGAINST('database optimization' IN NATURAL LANGUAGE MODE);

2.6 前缀索引（对某列前 N 个字符建立索引）

适用于长字符串（如 TEXT/BLOB），节省空间。

-- 只对 name 列的前 10 个字符建立索引
CREATE INDEX idx_name_prefix ON products(name(10));

3. 删除索引的方法与示例

3.1 DROP INDEX

DROP INDEX index_name ON table_name;

-- 示例：删除 users 表的 idx_email 索引
DROP INDEX idx_email ON users;

3.2 ALTER TABLE DROP INDEX

ALTER TABLE table_name DROP INDEX index_name;

-- 示例
ALTER TABLE users DROP INDEX uk_phone;

3.3 删除主键索引

ALTER TABLE table_name DROP PRIMARY KEY;

⚠️ 注意：如果主键列是 AUTO_INCREMENT，要先去掉该属性才能删除主键。

4. 实际综合案例

假设有一张订单表 orders：

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    product_id INT NOT NULL,
    status ENUM('pending', 'shipped', 'completed', 'canceled') NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

业务查询场景：

按 user_id 查询订单；
按 status + created_at 筛选近期订单；
保证 order_id 唯一（已有主键）；
可能需要按 product_id 快速查找。

索引设计与执行 SQL：

-- 1. 为 user_id 添加普通索引
CREATE INDEX idx_user_id ON orders(user_id);

-- 2. 为 status 和 created_at 添加组合索引（查询常用条件）
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 3. product_id 查询优化
CREATE INDEX idx_product_id ON orders(product_id);

-- 4. 保证业务逻辑唯一性（如果有业务唯一约束，如 user_id+product_id 不允许重复下单）
CREATE UNIQUE INDEX uk_user_product ON orders(user_id, product_id);

查看索引：

SHOW INDEX FROM orders;

删除某个不再需要的索引（例如发现 idx_product_id 使用率很低）：

DROP INDEX idx_product_id ON orders;

5. 注意事项与性能建议

索引不是越多越好
- 每次 INSERT/UPDATE/DELETE 都会维护索引，过多会降低写入性能。
- 只为常用于 WHERE、JOIN、ORDER BY、GROUP BY的列建索引。
组合索引顺序
- 将选择性高（唯一值多）且经常单独作为查询条件的列放在左侧。
- 遵循最左前缀原则：查询必须从索引最左列开始匹配才能利用索引。
定期维护
- 使用 ANALYZE TABLE 更新索引统计信息。
- 监控慢查询日志，分析哪些索引缺失或冗余。
前缀索引权衡
- 节省空间，但可能降低选择性，需评估区分度。
删除前确认
- 删除索引前，用 EXPLAIN 检查查询是否仍走索引，避免性能骤降。