给数据表添加索引的后台运作机制及查询提速原理咨询

添加索引时后台的具体操作 #

• 首先锁定目标表：不同存储引擎锁粒度不同，InnoDB用行级锁（仅锁定涉及的行），MyISAM则是表级锁（整个表无法写入），避免索引构建期间数据变更导致索引与数据不一致。
• 扫描目标字段的全量数据：根据指定的索引类型（如B+树、哈希、全文索引）构建对应的索引结构。以最常用的B+树为例：
  • 提取字段值并排序，构建多层树形索引——上层节点作为索引指引，叶子节点存储指向数据行的指针（InnoDB聚簇索引的叶子节点直接存整行数据）。
  • 生成索引元数据，记录索引名称、关联字段、类型等信息，存入数据库的数据字典。
• 更新表的统计信息：让查询优化器知晓新索引的存在，后续查询时能评估是否使用该索引。
• 释放锁，完成索引创建。

索引加速查询的核心原因 #

• 无索引时，数据库必须执行全表扫描：遍历表中每一行，逐一匹配查询条件，时间复杂度为O(n)，数据量越大，查询耗时呈线性增长。
• 有索引时，以B+树索引为例：
  • B+树是有序树形结构，查询时通过层级快速定位目标数据，时间复杂度为O(log n)——树的层级通常只有3-4层，哪怕百万级数据也能快速定位。
  • 叶子节点有序且连续，范围查询（如WHERE price BETWEEN 100 AND 200）可直接在叶子节点连续扫描，无需回溯上层节点。
  • InnoDB聚簇索引的叶子节点直接存储整行数据，找到索引节点就拿到了目标数据，无需额外回表；非聚簇索引虽需通过主键回表，但仍远快于全表扫描。
• 哈希索引则通过哈希函数将字段值映射到哈希表位置，等值查询时直接定位，时间复杂度接近O(1)，但仅支持等值查询，不适用范围、排序场景。

内容的提问来源于stack exchange，提问作者Trikesh Reddy