mysql数据库中的索引类型及优化策略_网络技术

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

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 ag e > 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'。