索引

覆盖索引包含某条查询求值和返回结果所需的列。在 InnoDB 中，二级索引记录还会携带主键列，因此即使二级索引定义没有再次列出主键，某条查询也可能已经被覆盖。覆盖描述的是一条具体查询与某个索引之间的关系，不是给索引贴上标签之后，整张表的所有查询都自动获得同样行为。 坏掉的样子 增加返回列可能使覆盖失效。原本能提供标识和时间戳的索引，不一定包含应用后来开始读取的大载荷。优化器仍然可能沿用这个索引定位匹配记录，再访问聚簇记录取得缺失字段。因此，“用到了索引”和“由索引覆盖查询”是两件不同的事，不能从计划中出现索引名字就直接推导后者。 覆盖也不保证访问范围足够小。一个扫描大量条目的查询，可以完全被索引覆盖，仍然花费很多工作。列顺序、过滤条件、排序要求和行数估计都很重要。不要把 Using index 覆盖标记当作查询很快的证明，也不要把它与 Using index condition 索引条件下推标记混淆；后者描述部分条件在索引层求值，并不证明整个返回投影都在索引里。 InnoDB 的可见性检查还会限制通常所说的“只读索引”解释。二级记录不包含与聚簇记录相同的隐藏事务元数据。遇到删除标记条目，或被更新事务修改过的二级索引页时，可能仍需访问聚簇记录才能获得正确可见版本。因此，在并发多版本活动下，一个覆盖计划并不普遍保证聚簇访问次数永远为零。 投影覆盖与 MVCC 可见性分开；历史 MySQL 计划未找回原始日志，覆盖或下推都不证明速度。 快速验证 历史示例保持原样，本轮源文审阅没有重跑。保留的审阅记录说明当时无法使用 MySQL 8.4，且未在保留的验证文件中找到原始 MySQL 运行日志；计划结果仍是归属于历史文章的观察。 准备一台新的 MySQL 8.4 实例，使用能够创建测试模式的客户端会话。既有运行记录中的版本是 MySQL 8.4.11。确认实验模式名尚未使用，遇到非预期 SQL 错误就停止，并保持同一个会话，以保留当前选中的数据库。下面建立一万行数据，其中一百笔已付款订单，并让载荷列明显比索引所需字段更宽： CREATE DATABASE xbcatm_index_lab; USE xbcatm_index_lab; CREATE TABLE orders ( id INT PRIMARY KEY, status VARCHAR(10) NOT NULL, created_at DATETIME NOT NULL, payload VARCHAR(1000) NOT NULL, INDEX by_status_created (status,created_at) ) ENGINE=InnoDB; INSERT INTO orders WITH digits(n) AS ( SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) SELECT number+1,IF(MOD(number+1,100)=0,'paid','open'), TIMESTAMP '2026-01-01 00:00:00' + INTERVAL (number+1) SECOND, REPEAT('x',640) FROM (SELECT a.n+10*b.n+100*c.n+1000*d.n AS number FROM digits a CROSS JOIN digits b CROSS JOIN digits c CROSS JOIN digits d) AS numbered; ANALYZE TABLE orders; SELECT COUNT(*),SUM(status='paid') FROM orders; 须人工核对最后两个计数为一万与一百；这些 SELECT 只打印结果，没有自动断言。二级索引明确包含状态与创建时间，InnoDB 隐式提供主键标识。先检查并运行只需要这些值的查询： ...

部分索引筛选的是行，不是裁剪列。给 id 建索引并加上 WHERE status = 'open'，保存的是未关闭行的标识符；状态列决定哪些行进入索引，却不因此成为搜索键。估算维护成本时，这个区别很重要：即使某一列的值没有存进索引键，它仍可能影响更新需要付出的代价。 如果订单表大部分是已关闭的历史记录，把这些行排除在索引之外，确实可能省下大量索引空间。但这不代表历史行可以免费写入，也不保证查询一定更快，更不会消除数据表本身的清理工作。判断收益时，要同时看结构缩小了多少，以及维持正确状态仍需承担哪些写入。 成员资格决定哪些行入索引；写入成本还取决于键变化、谓词列依赖和同页 HOT 条件。 区分成员资格与更新成本 新插入的行不满足谓词时，这一个部分索引不需要为它新增条目。谓词判断、表内存储、其他索引以及正常事务处理仍然存在。把一行从子集外改到子集内，需要新增条目；把它改出子集，则会改变当前查询能看见的成员。旧行版本与过时索引条目的物理回收可能发生在之后，不能把逻辑上退出子集理解成立即释放所有存储空间。 成员资格没有变化，也不等于可以跳过索引维护。未关闭订单的标识符改变了，索引键就需要更新。即使只修改一列与索引无关的备注，只要数据库无法采用堆内元组更新，也就是 HOT，新行版本仍可能需要新的索引条目。因此，“谓词结果不变”不足以证明这次更新很便宜。 对本文讨论的 PostgreSQL 18 B-tree 索引，HOT 要求所有索引定义引用的列值保持不变，包括表达式引用的列和部分索引谓词引用的列；原来那张堆页面还必须容得下新行版本。把状态从已关闭改成已归档，两种状态虽然都不满足谓词，仍然改变了索引依赖的状态列，会阻止 HOT。只修改没有被任何索引引用的备注列，则可能满足条件。 ...

PostgreSQL 哈希索引先利用哈希码缩小等值查询的候选范围，再回到表中核对原始值。哈希匹配只说明“可能相等”，不能直接证明相等。两个不同值可能具有相同哈希码，不同哈希码也可能映射到同一个桶。正常复核存在时，碰撞和共享桶都不意味着查询会返回错误结果；它们是实现需要处理的普通情况，而不是可以忽略的例外。 桶里究竟保存什么 索引条目包含四字节哈希值和指向表元组的引用，不保存完整的被索引列值。桶映射把查询导向相关索引页面。桶是共享存储空间，并不是给每个不同键单独分配一个专属位置。一个桶的主页装满后，可以继续连接溢出页，查询这个桶时也需要访问这些页面。因此，“一次算出桶”不等于“永远只读取一个页面”。 PostgreSQL 可以在单个索引页内部按哈希码排序，以便加速局部搜索，但这不会提供原始列值的顺序。哈希索引不能满足键范围或有序输出，只支持单列等值访问，不能强制唯一性，也不支持 IS NULL。B-tree 仍然可以服务等值查询，同时支持更广的一组操作，不能因为键看起来适合哈希就自动排除其他候选。 由于条目中没有原始值，哈希扫描是有损候选扫描，需要用表中实际值复核。因此，碰撞可以在执行计划中表现为 Rows Removed by Index Recheck。这说明数据库正确剔除了只在哈希上匹配的候选，而不是把两个碰撞值都当成相等返回。解读这项指标时，应把候选数量与最终答案区分开。 坏掉的样子 把索引键更新描述为“只碰一个桶”，掩盖了实际工作。修改被索引列会改变元组对应的索引值，新条目可能进入另一个桶。PostgreSQL 使用行版本与延后索引清理，旧引用不一定立刻从结构中消失。数据增长还可能触发桶分裂，溢出页也会增加读写。因此不能用一个理想化的哈希表赋值过程来解释数据库的全部更新成本。 高度重复的值、快速增长的表，与近乎唯一而稳定的等值负载，表现可能很不一样。哈希查询不是无条件的恒定延迟承诺。现代 PostgreSQL 的哈希索引支持崩溃恢复，但持久性既不会消除维护成本，也不会让它突然具备唯一性约束能力。读性能、写维护和数据契约必须分别确认。 同码与同桶不同；形状仅为示意。历史具体碰撞未找回原始日志，也未重跑。 快速验证 在可丢弃的 PostgreSQL 18 数据库中，用全新的 psql 会话执行下面代码；任何 SQL 或 psql 元命令错误都应停止。它建立二十万个采用 C 排序规则的临时文本键，使用当前服务器自己的 hashtext 找到一组碰撞，再验证等值查询和键更新。实验没有把哈希码当成跨版本稳定的业务标识，最后回滚会移除所有夹具。 ...

部分索引只为满足谓词的行保存索引项。需要的子集很小时，它可以缩小索引，但也可以表达只对这个子集生效的完整性规则。例如，同一个座位允许保留多条历史预订，却只能有一条当前有效预订。部分唯一索引可以把这条规则放在数据库边界上执行，而不只是提高某条查询的速度。 成员资格随谓词变化 CREATE INDEX ... WHERE active 只包含 active 为 true 的行，false 和 null 都不满足条件。是否允许 null 是独立的模型选择；下面实验把 active 定义为非空，使每条预订明确处于有效或无效状态。索引键也不必就是谓词中出现的列：这里用 seat 作为键，用 active 决定哪些行参与。 普通写入会改变成员资格。无效记录变成有效记录时，必须进入索引并接受检查；有效记录失效后，则离开当前有效子集。这个边界不是一旦建索引就永远固定的历史分界线。每次写入仍要判断谓词，状态切换也有维护成本，所以索引更小不意味着所有更新都免费，更不能无条件承诺写入一定更快。 唯一性可以只针对子集 在 seat 上建立 WHERE active 的唯一索引，会拒绝同一个座位出现两条有效记录，却允许任意多条无效历史记录。即使没有 SELECT 使用这个索引，数据库仍会执行唯一性检查。因此查询加速与数据完整性是保留索引的两种不同理由，不能只看扫描计数为零就把承担约束的索引删除。 应用先查“有没有”再插入，比数据库约束弱，因为另一个事务可能在检查与写入之间插入。唯一索引会在 PostgreSQL 中协调冲突写入，失败方需要处理唯一性错误或明确选择的冲突策略。下面夹具按确定顺序验证拒绝行为；并发协调属于数据库约束契约，不代表已经实现支付、扣款和跨服务预订的完整业务流程。 查询也要表达同一边界 规划器把部分索引用于访问路径时，必须能够证明查询条件蕴含索引谓词。仅按 seat 查询，并不能证明调用方只需要有效记录，因为历史记录也可能匹配。显式查询 active 记录才可能符合条件，但小表仍可能采用更便宜的顺序扫描。逻辑上相似的表达式，也只有在规划器能够识别其蕴含关系时才有用，不能仅凭人眼觉得相近。 ...

覆盖索引包含某一条查询需要的值，PostgreSQL 有时可以通过 index-only scan 直接返回它们，避免访问对应的堆元组。这里的“有时”很关键：列是否齐全属于结构条件，执行时能否真正跳过堆访问，还取决于可见性信息。计划里出现 Index Only Scan，并不能单独证明没有访问表数据，更不能据此宣称任何写入状态下都能省掉堆读取。 覆盖提供查询值；堆页可见性标志决定 index-only 扫描是否仍须访问堆。写入和满足条件的 VACUUM 可以改变状态。堆访问不一定产生物理磁盘读取。 覆盖实际返回的列 按 sku 查价格时，可以建立 sku 的 B-tree，并用 INCLUDE (price) 把价格作为附加值存进去。搜索由 sku 键引导，附加的 price 不会自动成为第二个搜索键，也不提供按价格排序的顺序。如果这是唯一索引，唯一性约束针对键列，而不是键与附加列的整体组合。这些区别决定它究竟能回答哪些查询。 查询后来增加返回列，就可能破坏覆盖前提。例如 description 不在索引中，执行器需要从其他位置取得它，通常就是堆表。随手改成 SELECT 星号，也可能把原本专门优化的查找变成另一种访问模式。应从实际过滤条件和投影列出发，结合表达式与可见性需求设计，而不是把全部字段都塞进一个预想中的万能覆盖索引。 ...

索引用存储和写入成本，换取减少读取工作的机会。表很小，或者谓词选中大部分数据时，全表扫描可能更快，但行数与选择性都不能单独决定胜负。查询需要哪些列、排序要求、缓存状态、行宽和可用访问路径，都会改变比较结果。首先明确实际查询必须返回什么，再判断哪种访问方法做了更多有用工作。 看清两条路径实际做什么 B 树范围扫描并不是每匹配一个索引条目，都重新从根节点下降。定位范围起点后，可以沿相邻条目继续前进。非覆盖索引还可能需要回表取得缺少的列；宽谓词下，这部分工作可能成为主要成本。覆盖索引能够省去这些读取，因此，即使查询命中多数行，索引也仍有可能更划算，不能把宽谓词与索引无用直接画等号。 页很重要，但不是唯一成本。PostgreSQL 默认的 seq_page_cost 与 random_page_cost 是优化器使用的相对估计，不是毫秒，也不是每返回一行就机械收取一次的费用。缓存、数据相关性、CPU 工作、位图扫描与仅索引扫描，都影响计划选择。SQLite 普通表把行放在按 rowid 组织的 B 树中，与 PostgreSQL 堆表存储不同，不能直接把一种引擎测得的性能比例套到另一种引擎。 SQLite 的 PRAGMA page_count 返回整个数据库文件的页数，包括索引和其他对象，不能当作某个指定表的独立页数。逻辑页遍历也不保证磁盘上的连续物理读取。比较时应保持统计对象范围一致，记录页大小、模式、负载宽度和缓存条件，不能只报告行数，再把其他成本差异全部归因于扫描或索引。 保留历史测量的适用范围 此前笔记记录了 SQLite 3.53、二兆字节页缓存下的结果。宽谓词的扫描与索引时间，在一千行时为 0.04 与 0.05 毫秒，在五万行时为 1.79 与 6.61 毫秒，在百万行时为 44.37 与 531.18 毫秒。同一百万行表的高选择性查询，索引为 0.61 毫秒，扫描为 17.88 毫秒。相同行数的窄、宽负载样本还分别记录了十二与一千零十页，以及 0.02 与 1.30 毫秒。 ...

复合 B-tree 索引先按第一个键排序，再在第一个键相同的组内按下一个键排序。这种元组顺序可以让“某个租户的一段时间”很容易定位。应理解的是条件能缩小多少索引扫描范围，而不是禁止查询后面的列，也不是要求 WHERE 中的条件必须按建索引的顺序书写。这几个概念混在一起，会导致错误的索引设计。 租户优先键顺序的局部片段，以及一种可能的后列搜索策略。重复搜索是否划算取决于成本；本图不是计划跟踪，最终输出顺序仍取决于实际路径。 排序的是键，不是 SQL 条件 对于 (tenant, happened) 索引，tenant 等值条件加 happened 范围条件描述了树中的一小段。PostgreSQL 可以定位这一段，而不用检查所有租户。即使把时间条件写在 WHERE 的前面、租户条件写在后面，逻辑上的合取关系也没有改变。数据库定义中的键顺序与查询文本的书写顺序，是两件不同的事情，不能拿文本位置判断索引是否可用。 通常，前导列的等值条件和第一个非等值键上的范围条件，决定必须扫描的区间。更后面的键条件仍可在索引中检查，即使不能继续缩小一个简单连续区间，也可能减少访问堆表的次数。调换索引键会改变这些机会，但不意味着某种排列在所有查询中都完全无用。需要结合查询实际要求的范围和返回列判断。 后面的键仍可能有用 PostgreSQL 18 在合适时可以使用 B-tree skip scan：如果遍历缺少等值条件的前导键取值，并在每组内重新搜索，比读取大部分索引更便宜，就可能采用这种路径。只有少数几个租户时，仅过滤 happened 也可能通过各租户分组完成查找；前导键不同值很多时，这种重复搜索可能太贵，规划器便可能选择顺序扫描或其他索引。 即使没有合算的 skip scan，某些只涉及后面键的条件仍可能扫描复合索引。问题是成本、覆盖范围与选择性，不是 SQL 是否合法，也不是索引必然被忽略。不能把“最左前缀”这种便于记忆的说法扩展成绝对禁令，尤其在目标数据库明确支持其他访问路径时。版本和真实计划必须优先于口号。 把排序和工作负载一起考虑 固定 tenant 后，后面的 happened 顺序可以支持该租户的时间排序。但跨多个租户时，索引先按租户分组，不会直接提供全局时间顺序。Bitmap 扫描也不保留索引顺序，仍可能需要单独排序。因此应看完整计划，而不是看到索引包含排序列就断言不会发生 Sort。LIMIT 对尽快返回前几行的需求也会改变最合适的路径。 ...

PostgreSQL 的 JSONB 列可以容纳形态不同的文档，但高效查询仍要求条件与索引有准确的对应关系。对整篇文档建立 GIN 索引，只能支持特定的 JSONB 算子，不能自动加速所有从文档提取字段的表达式。先确定数据类型和查询语义，再选择索引，才能避免建完索引却没有收益。 让算子与索引对应 包含条件 payload @> '{"status":"pending"}' 检查文档是否包含该键及对应的 JSON 字符串值。采用默认 jsonb_ops 操作符类时，整列 GIN 可以支持包含、顶层键存在判断，以及能够提取索引条件的 JSON 路径查询。索引保存可检索的组成部分；位图堆扫描仍可能访问表，并重新检查候选行，不能把它理解为完全不用回表。 payload->>'status' = 'pending' 则是在比较提取后的文本。payload 上的 GIN 并不是这个文本表达式的索引。对 (payload->>'status') 建立 B-tree 表达式索引，才与标量等值查询对应。如果很多查询需要对 status 排序、关联或施加约束，独立的有类型列可能更清楚，也能减少不断从灵活文档中推导字段的成本。 不能未经类型检查，就把所有文本条件改写成包含条件。JSON 数字和 JSON 字符串经过文本提取后可能得到相同文本，但包含算子会区分它们的 JSON 类型。只有数据约束保证相关前提时，改写才等价。应用应通过数据库驱动绑定参数，而不是把不可信输入拼接进 SQL 或 JSON 字符串；索引选择和输入处理需要分别正确。 ...