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]INDEXindex_name
ONtable_name(column1[(length)],column2,...)
[USINGBTREE|HASH]
[COMMENT'string']
[ALGORITHM[=]{DEFAULT|INPLACE|COPY}]
[LOCK[=]{NONE|SHARED|DEFAULT|EXCLUSIVE}]

2.2 核心参数解析

2.2.1 索引类型

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

  CREATEUNIQUEINDEXidx_emailONusers(email);

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

  CREATEFULLTEXTINDEXidx_contentONarticles(content);

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

  CREATESPATIALINDEXidx_locationONmaps(coordinates);

2.2.2 前缀索引

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

CREATEINDEXidx_nameONcustomers(name(10));--只索引前10个字符

2.2.3 复合索引

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

CREATEINDEXidx_name_ageONemployees(last_name,age);
--可利用索引的查询：
--WHERElast_name='Smith'
--WHERElast_name='Smith'ANDage>30
--不可利用索引：
--WHEREage=30

2.2.4 算法与锁机制

• ALGORITHM：

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

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

• LOCK：

• NONE：不锁定表

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

2.3 创建索引的实践案例

案例1：电商订单表优化

--创建订单状态+创建时间的复合索引
CREATEINDEXidx_status_createdONorders(status,created_atDESC);

--查询最近30天已完成订单
SELECT*FROMorders
WHEREstatus='completed'
ANDcreated_at>DATE_SUB(NOW(),INTERVAL30DAY);

案例2：用户表唯一约束

--确保手机号唯一且非空
CREATEUNIQUEINDEXidx_phoneONusers(phone)
WHEREphoneISNOTNULL;

案例3：全文搜索实现

--创建全文索引
CREATEFULLTEXTINDEXidx_searchONproducts(title,description);

--使用MATCHAGAINST语法
SELECT*FROMproducts
WHEREMATCH(title,description)AGAINST('智能手机'INNATURALLANGUAGEMODE);

三、DROP INDEX命令详解

3.1 基本语法结构

DROPINDEXindex_nameONtable_name
[ALGORITHM[=]{DEFAULT|INPLACE|COPY}]
[LOCK[=]{NONE|SHARED|DEFAULT|EXCLUSIVE}]

3.2 删除索引的注意事项

3.2.1 主键与唯一约束

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

  ALTERTABLEusersMODIFYidINT;--移除自增
  ALTERTABLEusersDROPPRIMARYKEY;

• 删除唯一约束索引：

  DROPINDEXidx_emailONusers;--索引名而非约束名

3.2.2 外键关联索引

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

--查看外键关系
SELECT*FROMinformation_schema.KEY_COLUMN_USAGE
WHERETABLE_NAME='child_table'ANDREFERENCED_TABLE_NAMEISNOTNULL;

--临时禁用外键检查
SETFOREIGN_KEY_CHECKS=0;
DROPINDEXidx_parent_idONchild_table;
SETFOREIGN_KEY_CHECKS=1;

3.3 删除索引的实践案例

案例1：清理冗余索引

--检查重复索引
SELECT
table_name,
index_name,
GROUP_CONCAT(column_nameORDERBYseq_in_index)AScolumns
FROMinformation_schema.statistics
WHEREtable_schema='your_database'
GROUPBYtable_name,index_name
HAVINGCOUNT(*)>1OR
(COUNT(*)=1ANDindex_name!='PRIMARY');

--删除重复索引（假设idx_name_v1和idx_name_v2相同）
DROPINDEXidx_name_v1ONcustomers;

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

--创建临时表结构
CREATETABLEorders_newLIKEorders;

--添加优化后的索引
CREATEINDEXidx_customer_statusONorders_new(customer_id,status);

--迁移数据
INSERTINTOorders_newSELECT*FROMorders;

--原子替换
RENAMETABLEordersTOorders_old,orders_newTOorders;

--清理旧表
DROPTABLEorders_old;

四、索引管理高级技巧

4.1 索引信息查询

4.1.1 查看表索引

SHOWINDEXFROMproducts;
--或使用information_schema
SELECT*FROMinformation_schema.statistics
WHEREtable_name='products';

4.1.2 分析索引使用情况

--开启性能监控
SETGLOBALuserstat=1;--MySQL5.7+需使用performance_schema

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

--或使用pt-index-usage工具（PerconaToolkit）

4.2 索引维护策略

4.2.1 定期重建碎片化索引

--分析表碎片率
ANALYZETABLEorders;

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

4.2.2 动态调整索引

--MySQL8.0+支持隐形索引（测试索引效果而不影响生产）
ALTERTABLEusersALTERINDEXidx_phoneINVISIBLE;

--根据查询性能决定是否恢复
ALTERTABLEusersALTERINDEXidx_phoneVISIBLE;

4.3 索引设计最佳实践

1. 选择高选择性列：

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

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

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

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

--创建覆盖索引
CREATEINDEXidx_coveringONorders(customer_id,status,order_date,total_amount);

--查询可直接从索引获取数据
SELECTcustomer_id,status,order_date
FROMorders
WHEREstatus='pending';

--对ORDERBY字段创建索引
CREATEINDEXidx_date_statusONorders(order_dateDESC,status);

--避免SortBuffer消耗
SELECT*FROMorders
WHEREstatus='shipped'
ORDERBYorder_dateDESC
LIMIT100;

五、常见错误与解决方案

5.1 创建索引失败案例

错误1：数据类型不匹配

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

--正确做法
CREATEINDEXidx_contentONarticles(content(255));

错误2：存储引擎限制

--MyISAM支持全文索引，但InnoDB需MySQL5.6+
CREATEFULLTEXTINDEXidx_textONmyisam_table(text_column);--成功
CREATEFULLTEXTINDEXidx_textONinnodb_table(text_column);--MySQL5.5失败

5.2 索引失效场景

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

--错误示例
SELECT*FROMusersWHEREDATE_FORMAT(created_at,'%Y-%m')='2023-01';

--正确做法：直接比较日期范围
SELECT*FROMusers
WHEREcreated_atBETWEEN'2023-01-01'AND'2023-01-3123:59:59';

场景2：隐式类型转换

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

--正确做法
SELECT*FROMcustomersWHEREphone='13800138000';

六、性能测试与验证

6.1 基准测试方法

--启用查询日志
SETGLOBALgeneral_log='ON';
SETGLOBALlog_output='TABLE';

--执行测试查询
SELECT*FROMorders
WHEREcustomer_id=1001
ANDorder_date>'2023-01-01';

--分析执行计划
EXPLAINSELECT*FROMorders
WHEREcustomer_id=1001
ANDorder_date>'2023-01-01';

6.2 执行计划关键指标解读

七、总结与操作清单

7.1 核心操作总结

7.2 日常维护建议

1. 每周检查：

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

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

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

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

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

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

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