mysql中常见的索引类型及其优缺点_技术教程

主键索引是强制非空唯一的聚簇索引；唯一索引允许一个NULL且值唯一；普通索引无约束仅加速查询；复合索引需遵守最左前缀原则。

它们不是“并列类型”，而是按约束语义和使用方式划分的索引形态，底层全都是 B+Tree（InnoDB）或 B-Tree（MyISAM）。关键差异在约束力和是否自动创建：

PRIMARY KEY 是特殊的唯一索引，强制非空 + 唯一，InnoDB 下自动成为聚簇索引——数据行直接按主键顺序物理存储，查主键 = 直接定位数据，无需回表
UNIQUE INDEX 允许 NULL（最多一个），不强制非空，但值必须唯一；适合邮箱、手机号等业务唯一字段，校验由数据库完成，比应用层判重更可靠
INDEX（普通索引）无任何约束，只加速查询；可建多个，适合状态、分类、时间等高频 WHERE 字段，但单独建它容易浪费空间
COMPOSITE INDEX（复合索引）是单个索引包含多列，如 INDEX idx_user_time (user_id, create_time)；必须遵守“最左前缀原则”——只有带 user_id 的查询才能命中，create_time 单独查无效

注意：MySQL 不会自动为非主键字段建索引，哪怕你加了 UNIQUE 约束，也得显式声明 UNIQUE INDEX 或 UNIQUE KEY 才生效。

InnoDB 默认且几乎只用 B+Tree 索引，HASH 索引仅由其“自适应哈希索引（AHI）”在运行时动态生成，不可手动创建；而 MEMORY 引擎才支持显式 HASH。二者能力边界非常清晰：

B+Tree 支持：=、>、BETWEEN、LIKE 'abc%'、ORDER BY、覆盖索引；叶子节点有序链表，天然利于范围扫描
HASH 仅支持精确匹配：= 和 IN，不支持范围、排序、前缀匹配；一旦哈希冲突高或数据分布倾斜，性能断崖下跌
InnoDB 的 AHI 是“自动缓存热点等值查询路径”的优化手段，不是独立索引结构；关掉它（innodb_adaptive_hash_index=OFF）有时反而提升并发写性能

实操建议：别试图在 InnoDB 表上“强制用 HASH”，那是徒劳；需要极致等值查询性能时，应考虑把热 key 提到 Redis，而不是依赖 MySQL 的 HASH 索引。

这两种索引完全脱离 B+Tree 范式，解决的是特定领域问题，误用会导致索引失效甚至报错：

FULLTEXT INDEX 专为文本搜索设计，依赖分词器（ngram 插件用于中文，mechanical 用于英文）；不能用 LIKE '%关键词%' 触发，必须走 MATCH() AGAINST() 语法；WHERE content LIKE '%mysql%' 再快也用不上它
SPATIAL INDEX 只能建在 GEOMETRY 类型列（如 POINT、POLYGON）上，且要求引擎为 InnoDB 或 MyISAM（5.7+）；WHERE ST_Distance(p1, p2) 这类地理查询才真正受益，普通数值字段加 SPATIAL 会报错
两者都不支持事务安全的 DML 同步更新——FULLTEXT 有延迟（需 OPTIMIZE TABLE 刷新），SPATIAL 在高并发插入时可能触发锁等待

常见错误：给 VARCHAR(255) 的标题字段加 FULLTEXT 却仍用 LIKE 查询，结果比没索引还慢；或对经纬度用两个 DOUBLE 字段分别建索引，却忽视 POINT + SPATIAL 才是正确解法。

很多人只盯着“查询变快”，却忽略索引对写入、空间和执行计划的反向影响：

写放大：每条 INSERT/UPDATE/DELETE 都要同步更新所有相关索引页；1 个表有 5 个索引，写一行 = 实际写 6 页（1 数据页 + 5 索引页），SSD 寿命和延迟都受影响
内存挤占：索引数据加载进 innodb_buffer_pool 后，会挤占真实数据页缓存空间；一个 10GB 表配了 8GB 索引，Buffer Pool 大部分被索引占满，反而导致热数据频繁换出
优化器误判：索引过多时，MySQL 成本估算易失准；比如明明 SELECT * FROM t WHERE status=1 应走索引，但因统计信息不准或索引基数低，优化器选了全表扫描，且 FORCE INDEX 也不能总用