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

MySQL 索引添加与删除的实际示例，涵盖常见的操作场景和注意事项。

1. 准备工作：示例表结构

首先创建一个示例表用于演示：

-- 创建示例表
CREATE TABLE employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    salary DECIMAL(10,2),
    hire_date DATE,
    city VARCHAR(50)
);

-- 插入示例数据
INSERT INTO employees (first_name, last_name, email, department_id, salary, hire_date, city) VALUES
('张', '三', 'zhangsan@example.com', 1, 50000, '2020-01-15', '北京'),
('李', '四', 'lisi@example.com', 2, 60000, '2019-03-20', '上海'),
('王', '五', 'wangwu@example.com', 1, 55000, '2021-06-10', '北京'),
('赵', '六', 'zhaoliu@example.com', 3, 70000, '2018-11-05', '广州'),
('钱', '七', 'qianqi@example.com', 2, 65000, '2020-09-30', '深圳');

2. 查看现有索引

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

-- 查看表的所有索引
SHOW INDEX FROM employees;

-- 或使用信息模式查询
SELECT 
    index_name,
    column_name,
    index_type,
    non_unique
FROM information_schema.statistics
WHERE table_schema = DATABASE() 
AND table_name = 'employees'
ORDER BY index_name, seq_in_index;

3. 添加索引的示例

3.1 添加单列索引

-- 在 last_name 列上创建普通索引
CREATE INDEX idx_last_name ON employees(last_name);

-- 在 email 列上创建唯一索引
CREATE UNIQUE INDEX idx_unique_email ON employees(email);

-- 在 hire_date 列上创建索引，并指定索引名
ALTER TABLE employees ADD INDEX idx_hire_date (hire_date);

3.2 添加多列组合索引

-- 在 department_id 和 salary 上创建复合索引
CREATE INDEX idx_dept_salary ON employees(department_id, salary);

-- 在 city 和 hire_date 上创建复合索引
ALTER TABLE employees ADD INDEX idx_city_hire (city, hire_date);

3.3 添加前缀索引（适用于文本列）

-- 对 first_name 的前5个字符创建索引
CREATE INDEX idx_first_name_prefix ON employees(first_name(5));

-- 对 email 的前10个字符创建索引
ALTER TABLE employees ADD INDEX idx_email_prefix (email(10));

3.4 添加全文索引（适用于文本搜索）

-- 在 first_name 和 last_name 上创建全文索引
CREATE FULLTEXT INDEX idx_fulltext_name ON employees(first_name, last_name);

4. 删除索引的示例

4.1 删除普通索引

-- 使用 DROP INDEX 删除索引
DROP INDEX idx_last_name ON employees;

-- 使用 ALTER TABLE 删除索引
ALTER TABLE employees DROP INDEX idx_hire_date;

4.2 删除唯一索引

-- 删除唯一索引
DROP INDEX idx_unique_email ON employees;

-- 注意：不能直接删除主键索引，需要特殊处理
-- ALTER TABLE employees DROP PRIMARY KEY; -- 需要先删除自增属性

4.3 删除复合索引

-- 删除复合索引
DROP INDEX idx_dept_salary ON employees;

5. 实际应用场景示例

场景1：优化查询性能

-- 问题：查询某个部门中薪水高于某个值的员工，性能较差
EXPLAIN SELECT * FROM employees 
WHERE department_id = 1 AND salary > 55000;

-- 解决方案：添加复合索引
CREATE INDEX idx_dept_salary_filter ON employees(department_id, salary);

-- 再次查看执行计划
EXPLAIN SELECT * FROM employees 
WHERE department_id = 1 AND salary > 55000;

场景2：优化排序操作

-- 问题：按入职日期排序查询时性能差
EXPLAIN SELECT * FROM employees ORDER BY hire_date DESC;

-- 解决方案：为排序字段添加索引
CREATE INDEX idx_hire_date_sort ON employees(hire_date);

场景3：优化 LIKE 查询

-- 问题：模糊查询性能差
EXPLAIN SELECT * FROM employees WHERE last_name LIKE '张%';

-- 解决方案：添加前缀索引（如果模糊查询通常从前缀开始）
CREATE INDEX idx_last_name_like ON employees(last_name);

6. 修改索引（MySQL没有直接修改索引的命令）

需要先删除旧索引，再创建新索引：

-- 1. 删除旧索引
DROP INDEX idx_city_hire ON employees;

-- 2. 创建新索引（改变列顺序或添加新列）
CREATE INDEX idx_hire_city_salary ON employees(hire_date, city, salary);

7. 索引管理的最佳实践

7.1 监控索引使用情况

-- 启用性能模式监控（MySQL 5.6+）
SELECT * FROM sys.schema_unused_indexes 
WHERE object_schema = DATABASE() 
AND object_name = 'employees';

-- 或使用SHOW STATUS查看索引使用统计
SHOW STATUS LIKE 'Handler_read%';

7.2 分析索引选择

-- 使用EXPLAIN分析查询执行计划
EXPLAIN FORMAT=JSON 
SELECT * FROM employees 
WHERE department_id = 1 
AND city = '北京' 
ORDER BY hire_date;

-- 使用优化器跟踪
SET optimizer_trace="enabled=on";
SELECT * FROM employees WHERE department_id = 1;
SELECT * FROM information_schema.optimizer_trace;
SET optimizer_trace="enabled=off";

7.3 索引维护语句

-- 分析表，更新索引统计信息
ANALYZE TABLE employees;

-- 重建索引（InnoDB表）
ALTER TABLE employees ENGINE=InnoDB;

-- 或使用OPTIMIZE TABLE
OPTIMIZE TABLE employees;

8. 完整示例：从创建到优化的全过程

-- 1. 创建新表
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2),
    status ENUM('pending', 'shipped', 'delivered'),
    product_category VARCHAR(50),
    INDEX idx_customer (customer_id)  -- 创建表时直接定义索引
);

-- 2. 添加常用查询所需的索引
CREATE INDEX idx_order_date ON orders(order_date);
CREATE INDEX idx_status_date ON orders(status, order_date);
CREATE INDEX idx_category_amount ON orders(product_category, total_amount);

-- 3. 发现某些索引未被使用，删除冗余索引
-- 先监控一段时间后...
DROP INDEX idx_customer ON orders;  -- 假设发现很少通过customer_id单独查询

-- 4. 优化复合索引顺序（基于查询模式）
DROP INDEX idx_status_date ON orders;
CREATE INDEX idx_date_status ON orders(order_date, status);  -- 按日期范围查询更常见

-- 5. 定期维护
ANALYZE TABLE orders;

注意事项

通过以上示例，你可以根据实际业务需求合理地添加和删除MySQL索引，以优化查询性能。