一、索引基础说明InnoDB 支持主键索引、普通索引、唯一索引、联合复合索引、前缀索引、全文索引索引核心作用加速WHERE / JOIN / ORDER BY / GROUP BY查询代价插入、更新、删除时需要维护 B 树索引越多写入性能越差。二、创建索引三种方式方式1建表时直接定义索引推荐规范写法CREATETABLEopenapi_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)))ENGINEInnoDBDEFAULTCHARSETutf8mb4COMMENT接口日志表;各类索引在建表时关键字区分PRIMARY KEY主键索引一张表只能一个非空且唯一INDEX / KEY普通索引无唯一性限制UNIQUE INDEX唯一索引字段值不能重复允许一条 NULLpath(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);方式3ALTER 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_unique0唯一索引/主键1普通索引Cardinality基数代表区分度数值越大索引效率越高。2. DESCRIBE / DESC 查看表结构附带索引DESCopenapi_apilog;3. 查询系统表查看全库索引SELECTTABLE_NAME,INDEX_NAME,COLUMN_NAME,NON_UNIQUEFROMINFORMATION_SCHEMA.STATISTICSWHERETABLE_SCHEMADATABASE()ORDERBYTABLE_NAME,INDEX_NAME,SEQ_IN_INDEX;4. 查询从未使用过的闲置索引清理冗余用SELECT*FROMsys.schema_unused_indexes;5. EXPLAIN 验证索引是否生效EXPLAINSELECTlogin_ip,priceFROMopenapi_apilogWHEREuser_id10001ANDdate2026-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);五、删除索引方式1DROP INDEX推荐DROPINDEXidx_path_prefixONopenapi_apilog;方式2ALTER TABLE 删除索引ALTERTABLEopenapi_apilogDROPINDEXidx_user_date_path;删除主键特殊写法ALTERTABLEopenapi_apilogDROPPRIMARYKEY;注意如果主键是自增字段删除前必须先去掉AUTO_INCREMENT。六、索引日常管理规范与运维操作1. 建索引线上注意事项1大表千万不要直接在线执行 CREATE INDEX500万行以上表新建索引会锁表阻塞读写解决方案MySQL5.6 支持在线无锁创建ALTER TABLE ... ADD INDEX LOCKNONE;使用 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 INDEXEXPLAIN确认是否命中调整索引先 DROP 再 CREATE清理维护删除无用索引、定期 OPTIMIZE 整理碎片线上大表操作使用在线DDL工具避免锁表。