MySQL创建和删除索引命令CREATE/DROP INDEX使用方法详解

索引是MySQL中提升查询性能的核心机制，通过创建合适的数据结构（如B+树、哈希表）加速数据检索。本文ZHANID工具网将系统讲解CREATE INDEX和DROP INDEX命令的语法细节、使用场景及优化实践，帮助开发者高效管理数据库索引。

一、索引基础概念

1.1 索引的本质

索引是数据库表的特殊附加结构，类似于书籍目录，通过存储字段值与行位置的映射关系，使查询引擎能快速定位数据而无需全表扫描。MySQL支持多种索引类型：

• B-Tree索引：默认类型，支持精确匹配和范围查询

• 哈希索引：仅支持等值比较（如Memory引擎）

• 全文索引：用于文本内容的模糊搜索

• 空间索引：针对地理空间数据类型

1.2 索引的代价

• 存储开销：每个索引占用额外磁盘空间（约表大小的10%-30%）

• 写入性能下降：INSERT/UPDATE/DELETE操作需同步更新索引

• 维护成本：复杂索引可能导致查询优化器选择低效执行计划

二、CREATE INDEX命令详解

2.1 基本语法结构

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name 
ON table_name (column1 [(length)], column2, ...)
[USING BTREE|HASH]
[COMMENT 'string']
[ALGORITHM [=] {DEFAULT|INPLACE|COPY}]
[LOCK [=] {NONE|SHARED|DEFAULT|EXCLUSIVE}]

2.2 核心参数解析

2.2.1 索引类型

• UNIQUE索引：强制字段值唯一（允许NULL值重复）

  CREATE UNIQUE INDEX idx_email ON users(email);

• FULLTEXT索引：仅支持InnoDB/MyISAM的CHAR/VARCHAR/TEXT类型

  CREATE FULLTEXT INDEX idx_content ON articles(content);

• SPATIAL索引：必须用于NOT NULL的GEOMETRY类型字段

  CREATE SPATIAL INDEX idx_location ON maps(coordinates);

2.2.2 前缀索引

对字符串类型可指定前N个字符建立索引，节省存储空间：

CREATE INDEX idx_name ON customers(name(10)); -- 只索引前10个字符

2.2.3 复合索引

多列组合索引遵循最左前缀原则：

CREATE INDEX idx_name_age ON employees(last_name, age);
-- 可利用索引的查询：
-- WHERE last_name = 'Smith'
-- WHERE last_name = 'Smith' AND age > 30
-- 不可利用索引：
-- WHERE age = 30

2.2.4 算法与锁机制

• ALGORITHM：

• INPLACE：在线操作（推荐，减少阻塞）

• COPY：需要重建表（适用于大表）

• LOCK：

• NONE：不锁定表

• EXCLUSIVE：获取排他锁（阻塞其他操作）

2.3 创建索引的实践案例

案例1：电商订单表优化

-- 创建订单状态+创建时间的复合索引
CREATE INDEX idx_status_created ON orders(status, created_at DESC);

-- 查询最近30天已完成订单
SELECT * FROM orders 
WHERE status = 'completed' 
AND created_at > DATE_SUB(NOW(), INTERVAL 30 DAY);

案例2：用户表唯一约束

-- 确保手机号唯一且非空
CREATE UNIQUE INDEX idx_phone ON users(phone) 
WHERE phone IS NOT NULL;

案例3：全文搜索实现

-- 创建全文索引
CREATE FULLTEXT INDEX idx_search ON products(title, description);

-- 使用MATCH AGAINST语法
SELECT * FROM products 
WHERE MATCH(title, description) AGAINST('智能手机' IN NATURAL LANGUAGE MODE);

三、DROP INDEX命令详解

3.1 基本语法结构

DROP INDEX index_name ON table_name
[ALGORITHM [=] {DEFAULT|INPLACE|COPY}]
[LOCK [=] {NONE|SHARED|DEFAULT|EXCLUSIVE}]

3.2 删除索引的注意事项

3.2.1 主键与唯一约束

• 删除主键索引需先删除自增属性（如果存在）：

  ALTER TABLE users MODIFY id INT; -- 移除自增
  ALTER TABLE users DROP PRIMARY KEY;

• 删除唯一约束索引：

  DROP INDEX idx_email ON users; -- 索引名而非约束名

3.2.2 外键关联索引

删除被外键引用的索引可能导致错误，需先处理外键约束：

-- 查看外键关系
SELECT * FROM information_schema.KEY_COLUMN_USAGE 
WHERE TABLE_NAME = 'child_table' AND REFERENCED_TABLE_NAME IS NOT NULL;

-- 临时禁用外键检查
SET FOREIGN_KEY_CHECKS = 0;
DROP INDEX idx_parent_id ON child_table;
SET FOREIGN_KEY_CHECKS = 1;

3.3 删除索引的实践案例

案例1：清理冗余索引

-- 检查重复索引
SELECT 
  table_name, 
  index_name, 
  GROUP_CONCAT(column_name ORDER BY seq_in_index) AS columns
FROM information_schema.statistics
WHERE table_schema = 'your_database'
GROUP BY table_name, index_name
HAVING COUNT(*) > 1 OR 
    (COUNT(*) = 1 AND index_name != 'PRIMARY');

-- 删除重复索引（假设idx_name_v1和idx_name_v2相同）
DROP INDEX idx_name_v1 ON customers;

案例2：重建表时保留数据

-- 创建临时表结构
CREATE TABLE orders_new LIKE orders;

-- 添加优化后的索引
CREATE INDEX idx_customer_status ON orders_new(customer_id, status);

-- 迁移数据
INSERT INTO orders_new SELECT * FROM orders;

-- 原子替换
RENAME TABLE orders TO orders_old, orders_new TO orders;

-- 清理旧表
DROP TABLE orders_old;

四、索引管理高级技巧

4.1 索引信息查询

4.1.1 查看表索引

SHOW INDEX FROM products;
-- 或使用information_schema
SELECT * FROM information_schema.statistics 
WHERE table_name = 'products';

4.1.2 分析索引使用情况

-- 开启性能监控
SET GLOBAL userstat = 1; -- MySQL 5.7+需使用performance_schema

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

-- 或使用pt-index-usage工具（Percona Toolkit）

4.2 索引维护策略

4.2.1 定期重建碎片化索引

-- 分析表碎片率
ANALYZE TABLE orders;

-- 重建高碎片索引（碎片率>30%）
OPTIMIZE TABLE orders; -- 会锁表，建议在低峰期执行

4.2.2 动态调整索引

-- MySQL 8.0+支持隐形索引（测试索引效果而不影响生产）
ALTER TABLE users ALTER INDEX idx_phone INVISIBLE;

-- 根据查询性能决定是否恢复
ALTER TABLE users ALTER INDEX idx_phone VISIBLE;

4.3 索引设计最佳实践

1. 选择高选择性列：

• 计算列的选择性：SELECT COUNT(DISTINCT column)/COUNT(*) FROM table

• 选择性接近1的列更适合建索引（如用户ID）

2. 避免过度索引：

• 单表索引数量建议不超过5个

• 宽表（字段多）需特别谨慎

3. 合理使用覆盖索引：

   -- 创建覆盖索引
   CREATE INDEX idx_covering ON orders(customer_id, status, order_date, total_amount);
   
   -- 查询可直接从索引获取数据
   SELECT customer_id, status, order_date 
   FROM orders 
   WHERE status = 'pending';

4. 索引与排序优化：

   -- 对ORDER BY字段创建索引
   CREATE INDEX idx_date_status ON orders(order_date DESC, status);
   
   -- 避免Sort Buffer消耗
   SELECT * FROM orders 
   WHERE status = 'shipped' 
   ORDER BY order_date DESC 
   LIMIT 100;

五、常见错误与解决方案

5.1 创建索引失败案例

错误1：数据类型不匹配

-- 错误示例：对TEXT类型创建普通索引
CREATE INDEX idx_content ON articles(content); -- 需指定前缀长度

-- 正确做法
CREATE INDEX idx_content ON articles(content(255));

错误2：存储引擎限制

-- MyISAM支持全文索引，但InnoDB需MySQL 5.6+
CREATE FULLTEXT INDEX idx_text ON myisam_table(text_column); -- 成功
CREATE FULLTEXT INDEX idx_text ON innodb_table(text_column); -- MySQL 5.5失败

5.2 索引失效场景

场景1：函数操作导致失效

-- 错误示例
SELECT * FROM users WHERE DATE_FORMAT(created_at, '%Y-%m') = '2023-01';

-- 正确做法：直接比较日期范围
SELECT * FROM users 
WHERE created_at BETWEEN '2023-01-01' AND '2023-01-31 23:59:59';

场景2：隐式类型转换

-- 假设phone字段是VARCHAR
-- 错误示例（导致索引失效）
SELECT * FROM customers WHERE phone = 13800138000;

-- 正确做法
SELECT * FROM customers WHERE phone = '13800138000';

六、性能测试与验证

6.1 基准测试方法

-- 启用查询日志
SET GLOBAL general_log = 'ON';
SET GLOBAL log_output = 'TABLE';

-- 执行测试查询
SELECT * FROM orders 
WHERE customer_id = 1001 
AND order_date > '2023-01-01';

-- 分析执行计划
EXPLAIN SELECT * FROM orders 
WHERE customer_id = 1001 
AND order_date > '2023-01-01';

6.2 执行计划关键指标解读

七、总结与操作清单

7.1 核心操作总结

7.2 日常维护建议

1. 每周检查：

• 使用pt-duplicate-key-checker检测重复索引

• 监控慢查询日志中的全表扫描

2. 每月优化：

• 对碎片率>30%的表执行OPTIMIZE TABLE

• 评估新索引的收益（通过performance_schema）

3. 变更前验证：

• 在测试环境评估索引对写入性能的影响

• 使用EXPLAIN ANALYZE（MySQL 8.0+）获取实际执行成本

通过系统化的索引管理，可使查询性能提升10-100倍，同时避免不必要的资源消耗。建议结合具体业务场景，通过持续监控和调优建立最适合的索引策略。