mysql数据库中的索引类型及优化策略

mysql常见索引类型有B+Tree、Hash、Fulltext和Spatial索引：B+Tree是InnoDB默认且唯一支持的索引结构；Hash仅MEMORY引擎原生支持；Fulltext用于全文检索；Spatial索引支持空间数据类型。

MySQL 中常见的索引类型有哪些

MySQL 支持多种索引类型，不同存储引擎支持程度不同，实际选型必须结合 InnoDB（主流）或 MyISAM（已基本弃用）来判断。

• B+Tree 索引：InnoDB 默认且唯一支持的索引结构，适用于 =、>、BETWEEN、LIKE 'abc%' 等查询；所有索引数据都按键值顺序组织，叶子节点存完整记录（聚簇索引）或主键值（二级索引）
• Hash 索引：仅在 MEMORY 引擎中原生支持，InnoDB 的自适应哈希索引（adaptive hash index）是内部机制，不可手动创建；它只加速等值查询（=、IN），不支持范围或排序
• Fulltext 索引：用于全文检索，仅支持 char/VARCHAR/TEXT 字段，InnoDB 和 MyISAM 均支持，但语法和分词行为有差异（如 InnoDB 使用 ngram 插件）
• Spatial 索引：仅 MyISAM 支持 R-Tree，InnoDB 从 5.7 起支持 POINT/POLYgoN 类型的 SRID 空间索引，需字段类型为 GEOMETRY 且非 NULL

什么时候该建索引，什么时候不该建

建索引不是越多越好。核心判断依据是「查询是否高频、过滤性是否强、写入压力是否可接受」。

• 应该建索引的情况：WHERE 条件中频繁出现的列、ORDER BY 或 GROUP BY 的字段、外键列、连接条件（JOIN ON）中的被驱动表字段
• 不应建索引的情况：enum 或低基数列（如 status 只有 0/1）、很少被查询的冷字段、大文本字段（TEXT 默认不支持索引，需前缀长度限制）、写多读少的表（如日志表）
• 特别注意：select * + LIMIT 1 且无有效 WHERE 条件时，索引几乎无效；全表扫描反而更快

复合索引怎么写才真正生效

复合索引遵循最左前缀原则（leftmost prefix principle），但很多人误以为只要包含字段就命中——实际取决于查询条件是否构成连续前缀。

CREATE INDEX idx_user_city_age ON users(city, age, name);

• ✅ 会走索引：WHERE city = 'Beijing'、WHERE city = 'Beijing' AND age > 25、WHERE city = 'Beijing' AND age = 28 AND name LIKE 'A%'
• ❌ 不走索引：WHERE age = 28（跳过 city）、WHERE name = 'Alice'（最左缺失）、WHERE city = 'Beijing' OR age = 28（OR 拆分后无法共用同一索引）
• ⚠️ 注意：ORDER BY city, age DESC 可用该索引；但 ORDER BY city ASC, age DESC 在 MySQL 8.0 之前不支持混合排序方向，会导致索引失效

如何验证索引是否被正确使用

别靠猜，用 EXPLaiN 看执行计划，重点关注 type、key、rows、Extra 四个字段。

• type 值优先级从高到低：const ≈ eq_ref > ref > range > index > ALL；出现 ALL 即全表扫描，大概率缺索引
• key 显示实际使用的索引名；为 NULL 表示没走索引（可能因隐式类型转换、函数包裹字段等）
• rows 是优化器预估扫描行数，远大于实际结果集时说明索引选择不佳或统计信息过期（可运行 ANALYZE table table_name 更新）
• Extra 中出现 using filesort 或 Using temporary 往往意味着排序/分组未利用索引完成，需检查是否缺失覆盖索引或排序字段顺序不匹配

隐式转换是高频陷阱：比如 WHERE phone = 13800138000（phone 是 VARCHAR），MySQL 会把字段转成数字比较，导致索引失效。应统一写成 WHERE phone = '13800138000'。