一、索引基础说明

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'接口日志表';

各类索引在建表时关键字区分

1. PRIMARY KEY：主键索引，一张表只能一个，非空且唯一；
2. INDEX / KEY：普通索引，无唯一性限制；
3. UNIQUE INDEX：唯一索引，字段值不能重复，允许一条 NULL；
4. 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状态）不建议单独建索引。

七、常见索引管理踩坑

1. 索引字段加函数、后置模糊匹配%xxx、隐式类型转换 → 索引失效；
2. 联合索引顺序错误，范围字段放前面，后面字段无法利用索引；
3. 长字符串不加前缀索引，索引文件体积过大，缓存命中率低；
4. 线上大表直接创建索引，长时间锁表引发业务超时；
5. 大量冗余索引，写入接口TPS持续下跌。

八、完整操作流程总结

1. 建表阶段：按需定义主键、联合索引；
2. 后期新增：CREATE INDEX/ALTER TABLE ADD INDEX；
3. 查看校验：SHOW INDEX+EXPLAIN确认是否命中；
4. 调整索引：先 DROP 再 CREATE；
5. 清理维护：删除无用索引、定期 OPTIMIZE 整理碎片；
6. 线上大表操作：使用在线DDL工具避免锁表。