MySQL索引完整教程：创建、查看、修改、删除与日常管理
一、索引基础说明
InnoDB 支持：主键索引、普通索引、唯一索引、联合复合索引、前缀索引、全文索引；
索引核心作用：加速WHERE / JOIN / ORDER BY / GROUP BY查询；
代价：插入、更新、删除时需要维护 B+ 树，索引越多写入性能越差。
二、创建索引三种方式
方式1：建表时直接定义索引（推荐规范写法）
CREATETABLE`openapi_apilog`(idBIGINTAUTO_INCREMENTCOMMENT'主键',user_idVARCHAR(32)NOTNULL,dateDATENOTNULL,pathVARCHAR(500)NOTNULL,login_ipVARCHAR(50),priceDECIMAL(10,2),creat_timeDATETIME,-- 主键索引PRIMARYKEY(`id`),-- 普通联合索引INDEXidx_user_date(user_id,date),-- 唯一索引UNIQUEINDEXuk_path_uid(path,user_id),-- 字符串前缀索引（path只截取前40字符建索引，节省空间）INDEXidx_path_prefix(path(40)))ENGINE=InnoDBDEFAULTCHARSET=utf8mb4COMMENT'接口日志表';各类索引在建表时关键字区分
PRIMARY KEY：主键索引，一张表只能一个，非空且唯一；INDEX / KEY：普通索引，无唯一性限制；UNIQUE INDEX：唯一索引，字段值不能重复，允许一条 NULL；path(N)：前缀索引，长字符串专用。
方式2：已有表追加创建索引（线上最常用）
语法通用：
CREATE[UNIQUE]INDEX索引名ON表名(字段1,字段2...);1）普通单列索引
CREATEINDEXidx_user_idONopenapi_apilog(user_id);2）联合复合索引（多字段组合）
CREATEINDEXidx_user_date_pathONopenapi_apilog(user_id,date,path);3）唯一索引
CREATEUNIQUEINDEXuk_verify_idONopenapi_apilog(verify_idf_id);4）前缀索引（长URL、地址字段）
CREATEINDEXidx_path_prefixONopenapi_apilog(path(40));5）覆盖索引（查询字段全部放进索引，消除回表）
CREATEINDEXidx_coverONopenapi_apilog(user_id,date,path,login_ip,price,creat_time);方式3：ALTER TABLE 语句创建索引
底层和CREATE INDEX效果一致，兼容老版本：
-- 普通索引ALTERTABLEopenapi_apilogADDINDEXidx_date(date);-- 唯一索引ALTERTABLEopenapi_apilogADDUNIQUEINDEXuk_ip(login_ip);-- 主键索引（表无主键时添加）ALTERTABLEopenapi_apilogADDPRIMARYKEY(`id`);三、查看索引（管理必备命令）
1. SHOW INDEX FROM 表名（最常用）
SHOWINDEXFROMopenapi_apilog;关键字段解读：
Key_name：索引名称；Seq_in_index：联合索引内字段顺序；Column_name：索引字段；Non_unique：0=唯一索引/主键，1=普通索引；Cardinality：基数，代表区分度，数值越大索引效率越高。
2. DESCRIBE / DESC 查看表结构附带索引
DESCopenapi_apilog;3. 查询系统表，查看全库索引
SELECTTABLE_NAME,INDEX_NAME,COLUMN_NAME,NON_UNIQUEFROMINFORMATION_SCHEMA.STATISTICSWHERETABLE_SCHEMA=DATABASE()ORDERBYTABLE_NAME,INDEX_NAME,SEQ_IN_INDEX;4. 查询从未使用过的闲置索引（清理冗余用）
SELECT*FROMsys.schema_unused_indexes;5. EXPLAIN 验证索引是否生效
EXPLAINSELECTlogin_ip,priceFROMopenapi_apilogWHEREuser_id='10001'ANDdate='2026-07-02';type = ALL：全表扫描，未走索引；key列有索引名：成功命中索引；- Extra 出现
Using index：命中覆盖索引，无回表。
四、修改索引
MySQL不支持直接修改索引字段，只能先删除旧索引，再重建新索引。
示例：原有 idx_user_date，需要改成 user_id + date + creat_time
-- 1. 删除旧索引DROPINDEXidx_user_dateONopenapi_apilog;-- 2. 创建新索引CREATEINDEXidx_user_date_timeONopenapi_apilog(user_id,date,creat_time);五、删除索引
方式1：DROP INDEX（推荐）
DROPINDEXidx_path_prefixONopenapi_apilog;方式2：ALTER TABLE 删除索引
ALTERTABLEopenapi_apilogDROPINDEXidx_user_date_path;删除主键特殊写法
ALTERTABLEopenapi_apilogDROPPRIMARYKEY;注意：如果主键是自增字段，删除前必须先去掉
AUTO_INCREMENT。
六、索引日常管理规范与运维操作
1. 建索引线上注意事项
1）大表千万不要直接在线执行 CREATE INDEX
500万行以上表新建索引会锁表阻塞读写，解决方案：
- MySQL5.6+ 支持在线无锁创建：
ALTER TABLE ... ADD INDEX LOCK=NONE; - 使用 pt-online-schema-change 工具在线加索引，避免锁表；
- 业务低峰期凌晨执行。
2. 清理冗余索引规则
已有联合索引(a,b,c)，无需单独创建(a)、(a,b)单列索引，联合索引天然支持最左前缀查询，多余索引只会加重写入压力。
3. 索引碎片整理
大量 DELETE / UPDATE 会产生索引碎片，降低查询效率：
OPTIMIZETABLEopenapi_apilog;InnoDB 会重建表和索引，释放碎片空间。
4. 索引数量控制
单表索引建议不超过 5 个，INSERT / UPDATE / DELETE 时每条索引都要同步更新。
5. 区分度判断（建索引前校验）
-- 区分度越接近1，索引效果越好SELECTCOUNT(DISTINCTuser_id)/COUNT(*)FROMopenapi_apilog;区分度低于0.1（如status 0/1状态）不建议单独建索引。
七、常见索引管理踩坑
- 索引字段加函数、后置模糊匹配
%xxx、隐式类型转换 → 索引失效； - 联合索引顺序错误，范围字段放前面，后面字段无法利用索引；
- 长字符串不加前缀索引，索引文件体积过大，缓存命中率低；
- 线上大表直接创建索引，长时间锁表引发业务超时；
- 大量冗余索引，写入接口TPS持续下跌。
八、完整操作流程总结
- 建表阶段：按需定义主键、联合索引；
- 后期新增：
CREATE INDEX/ALTER TABLE ADD INDEX； - 查看校验：
SHOW INDEX+EXPLAIN确认是否命中； - 调整索引：先 DROP 再 CREATE；
- 清理维护：删除无用索引、定期 OPTIMIZE 整理碎片；
- 线上大表操作：使用在线DDL工具避免锁表。