三大数据库（MySQL/MongoDB/PostgreSQL）常用索引及索引失效总结

核心前提：三者索引核心目的一致——避免全表/全集合扫描、提升查询效率，代价均为占用磁盘空间、降低写入性能；底层默认结构均以B+树为主（MongoDB WiredTiger引擎实际为B+树，官方统称B树）。

一、MySQL（InnoDB引擎，最常用，关系型）

聚簇索引（主键索引）：系统默认创建，数据与索引绑定，叶子节点是完整行数据；每张表必存在（无主键自动生成隐藏rowid），主键查询无需回表，速度最快。
二级索引（普通索引）：单字段建索引，用于单条件查询、单字段排序（如idx_age(age)）；叶子节点存主键值，查询需回表。
唯一索引：约束字段值唯一（如手机号、账号），语法：CREATE INDEX idx_phone ON user(phone) UNIQUE; 与聚簇索引逻辑一致，仅多了唯一性约束。
复合索引：多字段联合索引（如idx_name_age(name, age)），严格遵循「最左前缀原则」，等值查询字段放前、范围查询字段放后。

单字段索引：单字段建索引（如db.user.createIndex({age:1})），用于单条件查询、单字段排序，与MySQL普通索引逻辑一致。
唯一索引：系统默认自带_id唯一索引（无法删除、不能为空、绝对唯一）；可手动创建业务唯一索引（如db.user.createIndex({phone:1},{unique:true})），允许多个null值。
复合索引：多字段联合索引（如db.user.createIndex({name:1, age:-1})），严格遵循「最左前缀原则」，与MySQL一致。
多键索引：数组字段自动生成（无需手动配置），索引数组每一个元素，用于查询数组包含某单个元素（如hobby: "打球"）。

使用$where运算符（如db.user.find({$where: "this.age>18"})），直接彻底不走任何索引，全集合扫描，线上严禁使用。
数组查询（多键索引）失效：使用$all匹配多个元素（如hobby: {$all: ["打球","听歌"]}）、查询完整数组（如hobby: ["打球","听歌"]）、按数组下标查询（如"hobby.0": "打球"）、用$size判断数组长度（如hobby: {$size:3}）。
自定义唯一索引查询null值（大量null值时，大概率不走索引）。
分片集群环境，分片键不合理，跨分片查询不走本地索引。

B-tree索引：默认索引，与MySQL B+树一致，用于等值、范围查询，适配普通单字段、复合索引场景（如CREATE INDEX idx_age ON user(age)）。
唯一索引：与MySQL一致，约束字段唯一（如CREATE UNIQUE INDEX idx_phone ON user(phone)），无Mongo的null值漏洞。
复合索引：遵循最左前缀原则，但优化器更智能，非严格遵循时也可能走索引（如索引(a,b,c)，查询a+c也可能生效）。
GIN索引（PG核心特色）：专为数组、JSONB、全文检索设计，解决Mongo数组查询失效问题（如CREATE INDEX idx_hobby ON user USING GIN (hobby)），支持多元素同时匹配。
表达式索引（PG特色）：对函数/表达式建索引（如CREATE INDEX idx_lower_name ON user (lower(name))），查询需与表达式完全一致才能走索引。

复合索引违反最左前缀原则（概率低于MySQL）、索引字段做函数/运算、隐式类型转换、左模糊/全模糊查询、NOT IN/!=/IS NOT NULL大概率失效、大结果集放弃索引。

对比维度	MySQL	MongoDB	PostgreSQL
常用索引	聚簇、二级、唯一、复合	单字段、唯一、复合、多键	B-tree、唯一、复合、GIN、表达式
核心失效特色	严格遵循最左前缀，优化器较简单	$where必失效，数组查询坑多	统计信息过时、表达式不匹配、类型严格
数组/JSON支持	弱（需拆表）	强，但查询易失效	极强（GIN索引，无失效坑）
优化器智商	中等	中等（文档场景优）	极高（复杂场景最优）

（注：文档部分内容可能由 AI 生成）

本文由萧兮的博客原创发布，欢迎转载，转载务必保留原文链接。