聚集/非聚集索引页访问次数差异及Employee表查询计算疑问

1. 聚集索引与非聚集索引在页访问次数计算上的差异 #

• 聚集索引：它的叶节点就是实际的数据页，只要通过索引路径定位到目标叶页，就能直接获取所有数据列，不需要额外回表操作。比如查询列都能在聚集索引中找到时，页访问次数就是索引路径的页数（根+中间层）加上目标叶页的数量。
• 非聚集索引：叶节点存储的是索引键值+书签（堆表中是RID，聚集表中是聚集索引键）。如果查询的列不在非聚集索引的键或包含列里，就需要通过书签去查找对应的数据页，这会增加额外的页访问次数（回表次数）。比如非聚集索引找到100个叶页，每个叶页的索引行对应不同的数据页，可能需要额外访问100个数据页。

2. 不同场景下的查询页访问次数计算 #

给定条件：Employee表共8,000,000行，无索引时每页存100行，非聚集索引叶页存400条索引行，薪资2500-3000的记录共250,000行。

a) 无索引时执行Select Ssn From Employee Where Salary>2500 and Salary<3000 and DepartmentID=1 #

因为没有任何索引，数据库只能做全表扫描，需要访问所有数据页：
总数据页数 = 总行数 ÷ 每页行数 = 8,000,000 ÷ 100 = 80,000页
所以页访问次数是80,000次。

b) 创建聚集索引ixEmpSal on Employee (Salary)后，执行Select Ssn From Employee Where Salary>2500 and Salary<3000 and Gender=’M’ #

聚集索引的叶页就是数据页，薪资范围250,000行对应的叶页数 = 250,000 ÷ 100 = 2,500页。
数据库会先通过聚集索引的路径（根节点+1层中间节点，共2页）定位到薪资范围的起始叶页，接着扫描这2,500个叶页筛选符合Gender条件的记录。
总页访问次数 = 索引路径页数 + 目标叶页数 = 2 + 2,500 = 2,502次（注：若索引高度为2，路径页数为1，总次数为2,501，此处按常规索引高度3计算）。

3. 相关疑问解答 #

① 如何运用“非聚集索引叶页存400条索引行”这一条件？改为聚集索引会有什么变化？ #

• 非聚集索引场景：这个条件用来计算非聚集索引的层级和叶页数。比如全表8,000,000行，非聚集索引叶页数 = 8,000,000 ÷ 400 = 20,000页；若非叶节点每页也存400条索引条目，中间层页数 = 20,000 ÷ 400 = 50页，根节点1页，索引高度为3，访问时需经过根→中间层→叶页，共2次非叶节点访问。
• 聚集索引场景：聚集索引的叶页是数据页，叶页行数由数据页存储能力决定（此处为100行/页），不会用到“400条索引行”的条件；但非叶节点的条目数仍可按类似逻辑计算（比如每页存400条），核心差异是叶页本质是数据而非单独的索引条目。

② 查询的WHERE子句中，数据库是否会分别处理每个AND条件？ #

不会。数据库的查询优化器会根据条件的选择性、是否有可用索引等因素选择最优执行策略，而非依次处理每个AND条件：

• 无索引场景（问题a）：优化器直接全表扫描，每读取一行就同时判断Salary>2500 and Salary<3000 and DepartmentID=1三个条件是否满足，不会先筛选Salary再筛选DepartmentID。
• 有聚集索引场景（问题b）：优化器先利用Salary的聚集索引快速定位薪资范围的数据页，再在这些页里扫描并判断Gender='M'的条件，而非单独处理每个条件。

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