mysql中索引创建与查询优化中的实践经验_网络技术

应为能显著缩小结果集且查询频繁的字段建索引，如 user_id、status、created_at；避免对低基数、JSON（未建函数索引）、违反最左前缀的字段建索引。

什么时候该给 `WHERE` 字段加索引？

不是所有 WHERE 条件字段都值得建索引。真正需要建索引的，是那些能显著缩小结果集、且查询频率高的字段。比如 user_id、status（值分布较均匀）、created_at（范围查询多）——这些在 EXPLAI

N 中常表现为 type=ref 或 range 的字段。

容易踩的坑：
• 对 is_deleted TINYINT(1) 这类低基数字段（比如 95% 是 0）建单列索引，MySQL 很可能直接放弃使用；
• 在 JSON 字段上用 WHERE data->'$.name' 查询却不建函数索引，会导致全表扫描；
• 忘记索引最左前缀原则：对 (a,b,c) 建联合索引后，WHERE b = ? 不会走索引。

`ORDER BY` 和 `LIMIT` 组合为什么慢？怎么加速？

当查询带 ORDER BY created_at DESC LIMIT 20，但 created_at 没有索引，或索引没覆盖 WHERE 条件时，MySQL 可能先取出全部匹配行再排序，内存或磁盘临时表开销极大。

实操建议：
• 把 WHERE 条件字段和 ORDER BY 字段合并进一个联合索引，顺序按「等值条件 → 最左前缀 → 排序字段」排列，例如：WHERE status = 1 AND is_valid = 1 ORDER BY updated_at DESC → 索引应为 (status, is_valid, updated_at)；
• 避免在 ORDER BY 中使用函数或表达式，如 ORDER BY DATE(created_at)，除非你建了函数索引；
• 如果分页很深（如 LIMIT 10000, 20），考虑用游标分页（记录上一页最大 updated_at 值），避免 OFFSET 跳过大量行。

为什么 `ALTER TABLE ... ADD INDEX` 会锁表？如何安全加索引？

MySQL 5.6+ 默认使用 ALGORITHM=INPLACE，但并非所有操作都免锁。比如在大表（千万级）上对未压缩的 TEXT 字段建前缀索引，仍可能触发重建表（COPY 算法），造成写阻塞。

关键判断点：
• 先执行 ALTER TABLE t ADD INDEX idx_name (col) ALGORITHM=INPLACE, LOCK=NONE;，如果报错提示不支持，说明必须降级为 LOCK=SHARED 或等待维护窗口；
• 使用 pt-online-schema-change 工具做在线 DDL，它通过影子表 + 触发器同步数据，但要注意主从延迟和触发器性能开销；
• 在 RDS（如阿里云 PolarDB、AWS RDS）上，部分版本支持“在线创建二级索引”特性，需确认实例版本和参数 innodb_online_alter_log_max_size 是否足够。

`EXPLAIN` 输出里哪些字段最值得盯？

别只看 type 是不是 ALL，重点盯这三列：key（实际用了哪个索引）、rows（预估扫描行数）、Extra（有没有 Using filesort 或 Using temporary）。

典型信号：
• key 为 NULL：没走索引，检查字段类型是否隐式转换（如 varchar 字段传入数字）；
• rows 远大于实际返回行数（比如 rows=500000 但 LIMIT 10）：说明索引选择性差或统计信息过期，可运行 ANALYZE TABLE t;；
• Extra 出现 Using index condition 是好现象（ICP，索引下推），但若同时出现 Using where; Using index，说明是覆盖索引，不用回表；
• type=range 却 rows 极大：可能是范围太宽（如 created_at > '2025-01-01'），考虑分区或更精确的过滤条件。

EXPLAIN SELECT * FROM orders 
WHERE user_id = 12345 AND status IN (1,2) 
ORDER BY created_at DESC LIMIT 10;

索引设计是否合理，就看这一条 EXPLAIN 里 key 是否命中、rows 是否可控、Extra 有没有刺眼的警告。

# mysql # js # json # go # 工具 # 阿里云 # ai # 报错提示 # 排列 # 隐式转换 # 为什么 # red # NULL # date # using # copy # table # 算法 # 分页 # 行数 # 这一 # 上一页 # 什么时候 # 用了 # 这类 # 很可能 # 不支持 # 就看

相关栏目：【网站优化151355 】【网络推广146373 】【网络技术251813 】【 AI营销90571 】