索引是基于原始列值构建的文章目录数据库索引详解原理、类型与最佳实践一、什么是数据库索引二、索引的底层原理1. B 树B Tree为什么不用红黑树 / 哈希三、索引的分类1. 按结构分类1B 树索引默认2哈希索引2. 按逻辑分类1主键索引Primary Key2唯一索引Unique Index3普通索引Index4联合索引Composite Index四、聚簇索引 vs 非聚簇索引1. 聚簇索引Clustered Index2. 非聚簇索引Secondary Index五、索引的核心原则1. 最左前缀原则2. 覆盖索引Covering Index3. 索引下推Index Condition Pushdown六、什么时候该建索引✅ 适合建索引的场景❌ 不适合建索引的场景七、索引的代价1. 空间开销2. 写入开销3. 维护成本八、常见索引失效场景1. 使用函数2. 隐式类型转换3. LIKE 以 % 开头4. OR 条件不合理九、索引优化实战建议1. 建立合理的联合索引2. 避免重复索引3. 使用 EXPLAIN 分析4. 控制索引数量十、总结最后一条建议数据库索引详解原理、类型与最佳实践在日常开发中数据库性能往往成为系统瓶颈而“索引Index”则是优化查询性能最重要的手段之一。本文将从概念、原理、类型到实践系统性地介绍数据库索引帮助你真正理解它而不是“只会加索引”。一、什么是数据库索引数据库索引是一种用于提高查询效率的数据结构类似于书籍的目录。没有索引全表扫描Full Table Scan有索引通过索引快速定位数据 类比数据表 一本书索引 目录查询 查某一页内容如果没有目录你只能一页一页翻有了目录可以快速定位。二、索引的底层原理大多数关系型数据库如 MySQL、PostgreSQL默认使用1. B 树B Tree索引底层通常是B 树结构其特点多路平衡树非二叉树所有数据存储在叶子节点叶子节点之间通过链表连接方便范围查询为什么不用红黑树 / 哈希结构适合场景缺点哈希等值查询不支持范围查询红黑树内存结构层级深不适合磁盘B 树数据库索引⭐ 最优选择 B 树的优势IO 次数少树高度低支持范围查询支持排序ORDER BY三、索引的分类1. 按结构分类1B 树索引默认最常见支持范围查询、排序2哈希索引基于哈希表仅支持查询 常见于Memory 引擎Redis类似思想2. 按逻辑分类1主键索引Primary Key唯一且不能为空一张表只能有一个InnoDB 中是聚簇索引2唯一索引Unique Index不允许重复可以为空视数据库而定3普通索引Index最基本的索引允许重复4联合索引Composite Index多个字段组成一个索引CREATEINDEXidx_user_name_ageONuser(name,age); 使用规则遵循最左前缀原则四、聚簇索引 vs 非聚簇索引1. 聚簇索引Clustered Index数据和索引存储在一起主键索引就是聚簇索引 优点查询快少一次回表 缺点插入可能导致页分裂2. 非聚簇索引Secondary Index索引和数据分开存储叶子节点存储的是主键 查询过程先查索引再根据主键回表查询数据 这一步叫回表Back to Table五、索引的核心原则1. 最左前缀原则对于联合索引(a, b, c)查询条件是否使用索引a✅a, b✅a, b, c✅b❌c❌2. 覆盖索引Covering Index如果查询的字段都在索引中SELECTname,ageFROMuserWHEREnameTom; 如果(name, age)有索引不需要回表性能更高3. 索引下推Index Condition Pushdown数据库会尽量在索引层过滤数据减少回表次数。六、什么时候该建索引✅ 适合建索引的场景WHERE 条件字段JOIN 关联字段ORDER BY / GROUP BY 字段高频查询字段❌ 不适合建索引的场景数据量很小如几百行频繁更新字段写入成本高低选择性字段如性别七、索引的代价索引不是免费的它有成本1. 空间开销索引会占用额外存储空间2. 写入开销INSERT / UPDATE / DELETE 都需要维护索引3. 维护成本索引过多会拖慢写入性能 原则查询优化 vs 写入性能的权衡八、常见索引失效场景1. 使用函数WHEREYEAR(create_time)2024; 索引失效原因对列使用函数如YEAR()会导致数据库无法直接利用该列上的索引进行查找。因为索引是基于原始列值构建的而函数会改变列值的形态使得优化器无法将查询条件与索引结构匹配。2. 隐式类型转换WHEREid123; 可能导致索引失效原因当列的数据类型与查询值类型不一致时例如id是整型但传入的是字符串123数据库可能进行隐式类型转换。这种转换通常发生在列上即把id转为字符串比较从而破坏了索引的使用条件导致全表扫描。3. LIKE 以 % 开头WHEREnameLIKE%Tom; 无法使用索引原因B树索引依赖前缀匹配。如果LIKE模式以通配符%开头数据库无法确定从索引的哪个位置开始查找因此不能利用索引的有序性只能进行全表扫描。只有LIKE Tom%这类前缀匹配才能有效使用索引。4. OR 条件不合理WHEREa1ORb2; 可能导致索引失效原因如果a和b分别有单独的索引但没有联合索引或合适的覆盖索引数据库优化器可能认为分别使用两个索引再合并结果的成本高于全表扫描从而放弃使用索引。只有当OR的每个子句都能高效使用索引如各自有独立索引且选择性高或者使用了“索引合并”Index Merge策略时才可能有效利用索引。九、索引优化实战建议1. 建立合理的联合索引优先把区分度高的字段放前面2. 避免重复索引(a,b)已存在就不需要单独建(a)3. 使用 EXPLAIN 分析EXPLAINSELECT*FROMuserWHEREnameTom;关注type访问类型key使用的索引rows扫描行数4. 控制索引数量 一张表建议3~5 个核心索引避免“索引泛滥”十、总结数据库索引的本质是用空间换时间用复杂结构换查询效率核心要点回顾索引底层通常是 B 树聚簇索引 vs 非聚簇索引要理解联合索引要遵循最左前缀原则覆盖索引是性能优化关键索引并非越多越好最后一条建议如果你只记住一句话“不是所有查询都需要索引但高频查询一定要有合适的索引。” 数据库索引Index介绍（B+树、主键索引、非聚簇索引、唯一索引、普通索引、联合索引、最左前缀原则、回表Back to Table、覆盖索引、索引下推）常见索引失效原因
索引是基于原始列值构建的文章目录数据库索引详解原理、类型与最佳实践一、什么是数据库索引二、索引的底层原理1. B 树B Tree为什么不用红黑树 / 哈希三、索引的分类1. 按结构分类1B 树索引默认2哈希索引2. 按逻辑分类1主键索引Primary Key2唯一索引Unique Index3普通索引Index4联合索引Composite Index四、聚簇索引 vs 非聚簇索引1. 聚簇索引Clustered Index2. 非聚簇索引Secondary Index五、索引的核心原则1. 最左前缀原则2. 覆盖索引Covering Index3. 索引下推Index Condition Pushdown六、什么时候该建索引✅ 适合建索引的场景❌ 不适合建索引的场景七、索引的代价1. 空间开销2. 写入开销3. 维护成本八、常见索引失效场景1. 使用函数2. 隐式类型转换3. LIKE 以 % 开头4. OR 条件不合理九、索引优化实战建议1. 建立合理的联合索引2. 避免重复索引3. 使用 EXPLAIN 分析4. 控制索引数量十、总结最后一条建议数据库索引详解原理、类型与最佳实践在日常开发中数据库性能往往成为系统瓶颈而“索引Index”则是优化查询性能最重要的手段之一。本文将从概念、原理、类型到实践系统性地介绍数据库索引帮助你真正理解它而不是“只会加索引”。一、什么是数据库索引数据库索引是一种用于提高查询效率的数据结构类似于书籍的目录。没有索引全表扫描Full Table Scan有索引通过索引快速定位数据 类比数据表 一本书索引 目录查询 查某一页内容如果没有目录你只能一页一页翻有了目录可以快速定位。二、索引的底层原理大多数关系型数据库如 MySQL、PostgreSQL默认使用1. B 树B Tree索引底层通常是B 树结构其特点多路平衡树非二叉树所有数据存储在叶子节点叶子节点之间通过链表连接方便范围查询为什么不用红黑树 / 哈希结构适合场景缺点哈希等值查询不支持范围查询红黑树内存结构层级深不适合磁盘B 树数据库索引⭐ 最优选择 B 树的优势IO 次数少树高度低支持范围查询支持排序ORDER BY三、索引的分类1. 按结构分类1B 树索引默认最常见支持范围查询、排序2哈希索引基于哈希表仅支持查询 常见于Memory 引擎Redis类似思想2. 按逻辑分类1主键索引Primary Key唯一且不能为空一张表只能有一个InnoDB 中是聚簇索引2唯一索引Unique Index不允许重复可以为空视数据库而定3普通索引Index最基本的索引允许重复4联合索引Composite Index多个字段组成一个索引CREATEINDEXidx_user_name_ageONuser(name,age); 使用规则遵循最左前缀原则四、聚簇索引 vs 非聚簇索引1. 聚簇索引Clustered Index数据和索引存储在一起主键索引就是聚簇索引 优点查询快少一次回表 缺点插入可能导致页分裂2. 非聚簇索引Secondary Index索引和数据分开存储叶子节点存储的是主键 查询过程先查索引再根据主键回表查询数据 这一步叫回表Back to Table五、索引的核心原则1. 最左前缀原则对于联合索引(a, b, c)查询条件是否使用索引a✅a, b✅a, b, c✅b❌c❌2. 覆盖索引Covering Index如果查询的字段都在索引中SELECTname,ageFROMuserWHEREnameTom; 如果(name, age)有索引不需要回表性能更高3. 索引下推Index Condition Pushdown数据库会尽量在索引层过滤数据减少回表次数。六、什么时候该建索引✅ 适合建索引的场景WHERE 条件字段JOIN 关联字段ORDER BY / GROUP BY 字段高频查询字段❌ 不适合建索引的场景数据量很小如几百行频繁更新字段写入成本高低选择性字段如性别七、索引的代价索引不是免费的它有成本1. 空间开销索引会占用额外存储空间2. 写入开销INSERT / UPDATE / DELETE 都需要维护索引3. 维护成本索引过多会拖慢写入性能 原则查询优化 vs 写入性能的权衡八、常见索引失效场景1. 使用函数WHEREYEAR(create_time)2024; 索引失效原因对列使用函数如YEAR()会导致数据库无法直接利用该列上的索引进行查找。因为索引是基于原始列值构建的而函数会改变列值的形态使得优化器无法将查询条件与索引结构匹配。2. 隐式类型转换WHEREid123; 可能导致索引失效原因当列的数据类型与查询值类型不一致时例如id是整型但传入的是字符串123数据库可能进行隐式类型转换。这种转换通常发生在列上即把id转为字符串比较从而破坏了索引的使用条件导致全表扫描。3. LIKE 以 % 开头WHEREnameLIKE%Tom; 无法使用索引原因B树索引依赖前缀匹配。如果LIKE模式以通配符%开头数据库无法确定从索引的哪个位置开始查找因此不能利用索引的有序性只能进行全表扫描。只有LIKE Tom%这类前缀匹配才能有效使用索引。4. OR 条件不合理WHEREa1ORb2; 可能导致索引失效原因如果a和b分别有单独的索引但没有联合索引或合适的覆盖索引数据库优化器可能认为分别使用两个索引再合并结果的成本高于全表扫描从而放弃使用索引。只有当OR的每个子句都能高效使用索引如各自有独立索引且选择性高或者使用了“索引合并”Index Merge策略时才可能有效利用索引。九、索引优化实战建议1. 建立合理的联合索引优先把区分度高的字段放前面2. 避免重复索引(a,b)已存在就不需要单独建(a)3. 使用 EXPLAIN 分析EXPLAINSELECT*FROMuserWHEREnameTom;关注type访问类型key使用的索引rows扫描行数4. 控制索引数量 一张表建议3~5 个核心索引避免“索引泛滥”十、总结数据库索引的本质是用空间换时间用复杂结构换查询效率核心要点回顾索引底层通常是 B 树聚簇索引 vs 非聚簇索引要理解联合索引要遵循最左前缀原则覆盖索引是性能优化关键索引并非越多越好最后一条建议如果你只记住一句话“不是所有查询都需要索引但高频查询一定要有合适的索引。”