索引

覆盖索引包含某条查询求值和返回结果所需的列。在 InnoDB 中，二级索引记录还会携带主键列，因此即使二级索引定义没有再次列出主键，某条查询也可能已经被覆盖。覆盖描述的是一条具体查询与某个索引之间的关系，不是给索引贴上标签之后，整张表的所有查询都自动获得同样行为。 坏掉的样子 增加返回列可能使覆盖失效。原本能提供标识和时间戳的索引，不一定包含应用后来开始读取的大载荷。优化器仍然可能沿用这个索引定位匹配记录，再访问聚簇记录取得缺失字段。因此，“用到了索引”和“由索引覆盖查询”是两件不同的事，不能从计划中出现索引名字就直接推导后者。 覆盖也不保证访问范围足够小。一个扫描大量条目的查询，可以完全被索引覆盖，仍然花费很多工作。列顺序、过滤条件、排序要求和行数估计都很重要。不要把 Using index 覆盖标记当作查询很快的证明，也不要把它与 Using index condition 索引条件下推标记混淆；后者描述部分条件在索引层求值，并不证明整个返回投影都在索引里。 InnoDB 的可见性检查还会限制通常所说的“只读索引”解释。二级记录不包含与聚簇记录相同的隐藏事务元数据。遇到删除标记条目，或被更新事务修改过的二级索引页时，可能仍需访问聚簇记录才能获得正确可见版本。因此，在并发多版本活动下，一个覆盖计划并不普遍保证聚簇访问次数永远为零。 快速验证 准备一台新的 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; 最后的两个计数应为一万与一百。二级索引明确包含状态与创建时间，InnoDB 隐式提供主键标识。先检查并运行只需要这些值的查询： ...

并不是每一行都配得上一个索引槽位；部分索引让树尽量薄，正对查询从不关心的那些数据。多数表都有表结构假装看不见的偏斜：九成订单已关闭，几乎所有事件都已处理，标志列几乎处处为假。全量索引老老实实全记下来，每次写入都交维护费，保护的却是只碰一小片热数据的查询。部分索引不再为没人查的行付钱。 这笔交易拿精度换体积。索引更小、扫描更快、维护更便宜——但只有谓词与索引条件对得上的查询才能用它，其余统统回退到别的计划。所以部分索引是访问模式与存储的刻意配对，不是通用加速。只有热子集稳定、各查询对它的定义一致时才值得建。 只索引你要查的数据 部分索引带一个 WHERE 子句，不满足谓词的行根本不会进树。当大多数行很少被读时，树的体积因此更小，真正关心的那些行查找也更快。只给未关闭订单建的索引，可能比全量小一个数量级：更多内容装进缓存，每次查找碰更少的页，冷历史再怎么涨，计划依然走索引。 谓词从查询里挑，别从列统计里挑。正确的条件是每条重要查询本来就在重复的那一句：状态等于未关闭、删除时间为空。如果各查询切表的方式不同，一个部分索引伺候不了所有人——要么主导的那片胜出，要么建两个部分索引，每个都比全量瘦。定谓词之前，先让查询日志投票。 行更少、维护更省 条目越少，INSERT、UPDATE 还有 VACUUM 要碰的结构就越轻，磁盘占用也能肉眼可见地降下来。对写入频繁又大多是噪音的表，收益最明显。不满足谓词的插入整行跳过索引——没有页分裂风险，也不为它写日志；不改变谓词结果的更新同样跳过索引维护。对每秒吞噬几千冷写的表，省下的维护远超查询加速本身。 索引变小还会让清理更快：需要清理的页少了，膨胀无处藏身，表本来就要付的维护窗口跟着变短。上线后盯着它和全量等价物的体积比；部分索引一旦长到接近全量，说明谓词选出的早已不是少数，交易已经到期——删掉它，或重新定义切片。 部分索引还有一个容易漏掉的边界：规划阶段必须能证明查询条件蕴含索引谓词。把值写成预备语句参数，规划器无法对所有可能值提前证明，因此可能完全不用这个索引；不同写法的等价条件也未必能被识别。把热点条件固定成同一段查询构造，给带真实参数的 EXPLAIN 留一份回归样本，才能知道索引是在生产路径上工作，而不是只在建表演示里好看。 让语句与索引对得上 规划器通常只会为与部分索引谓词完全一致、或由其蕴含的查询选用它。所以查询里的过滤条件要照索引的写法写，规划器才能真正用得上。状态等于未关闭能命中条件相同的索引；状态属于两者之一则不行，哪怕人一眼能看出重叠。匹配器认的是逻辑蕴含，不是字面相似，而且设计上偏保守。 这份严格规定了一条纪律：全库统一热切片条件的写法。索引写的是删除时间为空，查询就不许写成删除时间等于空或包一层合并函数还指望走索引。把热条件收敛到一处——视图、作用域、查询构造器的片段——让每条语句继承逐字相同的谓词。执行计划是最终裁判：计划无视索引，说明两边谓词差在匹配器跨不过去的地方。 坏掉的样子 经典故障是谓词漂移：业务把"未关闭"重定义得多了一个状态，应用查询跟着改了，部分索引却悄悄对不上一半负载。不报错——计划一条一条退化成顺序扫描，延迟爬升，背锅的却找不到一次发布。上线时给核心查询加上计划断言，进持续集成或监控，断言计划里出现这个索引名。谓词漂移在发布当天就被抓住，而不是被发现的那个季度。 第二种故障是切太碎：一张表五个部分索引，各伺候一份报表，合计维护费超过当初要替换的全量索引。用索引统计视图审计：几乎零扫描的索引是删除候选，谓词重叠的是合并候选，合成一个更宽的部分索引。 快速验证 验证索引命中与体积收益： CREATE INDEX ON orders (created_at) WHERE status = 'open'; EXPLAIN SELECT * FROM orders WHERE status = 'open' ORDER BY created_at LIMIT 20; SELECT pg_size_pretty(pg_relation_size('orders_created_at_idx')); 计划必须走部分索引的索引扫描，体积应该是全量等价物的一小部分。 ...

PostgreSQL 哈希索引先利用哈希码缩小等值查询的候选范围，再回到表中核对原始值。哈希匹配只说明“可能相等”，不能直接证明相等。两个不同值可能具有相同哈希码，不同哈希码也可能映射到同一个桶。正常复核存在时，碰撞和共享桶都不意味着查询会返回错误结果；它们是实现需要处理的普通情况，而不是可以忽略的例外。 桶里究竟保存什么 索引条目包含四字节哈希值和指向表元组的引用，不保存完整的被索引列值。桶映射把查询导向相关索引页面。桶是共享存储空间，并不是给每个不同键单独分配一个专属位置。一个桶的主页装满后，可以继续连接溢出页，查询这个桶时也需要访问这些页面。因此，“一次算出桶”不等于“永远只读取一个页面”。 PostgreSQL 可以在单个索引页内部按哈希码排序，以便加速局部搜索，但这不会提供原始列值的顺序。哈希索引不能满足键范围或有序输出，只支持单列等值访问，不能强制唯一性，也不支持 IS NULL。B-tree 仍然可以服务等值查询，同时支持更广的一组操作，不能因为键看起来适合哈希就自动排除其他候选。 由于条目中没有原始值，哈希扫描是有损候选扫描，需要用表中实际值复核。因此，碰撞可以在执行计划中表现为 Rows Removed by Index Recheck。这说明数据库正确剔除了只在哈希上匹配的候选，而不是把两个碰撞值都当成相等返回。解读这项指标时，应把候选数量与最终答案区分开。 坏掉的样子 把索引键更新描述为“只碰一个桶”，掩盖了实际工作。修改被索引列会改变元组对应的索引值，新条目可能进入另一个桶。PostgreSQL 使用行版本与延后索引清理，旧引用不一定立刻从结构中消失。数据增长还可能触发桶分裂，溢出页也会增加读写。因此不能用一个理想化的哈希表赋值过程来解释数据库的全部更新成本。 高度重复的值、快速增长的表，与近乎唯一而稳定的等值负载，表现可能很不一样。哈希查询不是无条件的恒定延迟承诺。现代 PostgreSQL 的哈希索引支持崩溃恢复，但持久性既不会消除维护成本，也不会让它突然具备唯一性约束能力。读性能、写维护和数据契约必须分别确认。 快速验证 在可丢弃的 PostgreSQL 18 数据库中，用 psql 执行下面代码。它建立二十万个采用 C 排序规则的临时文本键，使用当前服务器自己的 hashtext 找到一组碰撞，再验证等值查询和键更新。实验没有把哈希码当成跨版本稳定的业务标识，最后回滚会移除所有夹具。 \set ON_ERROR_STOP on BEGIN; CREATE TEMP TABLE hash_items (key text COLLATE "C"); INSERT INTO hash_items SELECT 'key-'||g FROM generate_series(1,200000) AS g; CREATE TEMP TABLE collision_pair AS SELECT min(key) AS a,max(key) AS b FROM hash_items GROUP BY hashtext(key) HAVING count(*)>1 ORDER BY hashtext(key) LIMIT 1; SELECT a AS collision_a,b AS collision_b FROM collision_pair \gset SELECT a,b,hashtext(a),hashtext(b) FROM collision_pair; CREATE INDEX hash_items_key ON hash_items USING hash(key); ANALYZE hash_items; EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT key FROM hash_items WHERE key=:'collision_a'; UPDATE hash_items SET key='changed-'||key WHERE key=:'collision_a'; SELECT count(*) AS old_key_rows FROM hash_items WHERE key=:'collision_a'; SELECT count(*) AS new_key_rows FROM hash_items WHERE key='changed-'||:'collision_a'; DO $$ BEGIN IF (SELECT hashtext(a)=hashtext(b) AND a<>b FROM collision_pair) IS DISTINCT FROM true THEN RAISE EXCEPTION 'fixture did not establish a collision'; END IF; IF (SELECT count(*) FROM hash_items h JOIN collision_pair p ON h.key=p.a) <> 0 OR (SELECT count(*) FROM hash_items h JOIN collision_pair p ON h.key='changed-'||p.a) <> 1 OR (SELECT count(*) FROM hash_items h JOIN collision_pair p ON h.key=p.b) <> 1 THEN RAISE EXCEPTION 'hash lookup or indexed update returned wrong keys'; END IF; END $$; ROLLBACK; PostgreSQL 18.6 中找到的两个键是 key-129539 与 key-7295，哈希码都是 -1960436116。等值计划使用 hash_items_key，最终只返回一行，同时报告一行被索引复核移除。两段字符串虽然哈希相同，却仍然是不同的值。这是反对“哈希码没有碰撞”这一错误假设的实际负向验证，不能用普通的无碰撞点查替代。 ...

部分索引只为满足谓词的行保存索引项。需要的子集很小时，它可以缩小索引，但也可以表达只对这个子集生效的完整性规则。例如，同一个座位允许保留多条历史预订，却只能有一条当前有效预订。部分唯一索引可以把这条规则放在数据库边界上执行，而不只是提高某条查询的速度。 成员资格随谓词变化 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，并不能单独证明没有访问表数据，更不能据此宣称任何写入状态下都能省掉堆读取。 覆盖实际返回的列 按 sku 查价格时，可以建立 sku 的 B-tree，并用 INCLUDE (price) 把价格作为附加值存进去。搜索由 sku 键引导，附加的 price 不会自动成为第二个搜索键，也不提供按价格排序的顺序。如果这是唯一索引，唯一性约束针对键列，而不是键与附加列的整体组合。这些区别决定它究竟能回答哪些查询。 查询后来增加返回列，就可能破坏覆盖前提。例如 description 不在索引中，执行器需要从其他位置取得它，通常就是堆表。随手改成 SELECT 星号，也可能把原本专门优化的查找变成另一种访问模式。应从实际过滤条件和投影列出发，结合表达式与可见性需求设计，而不是把全部字段都塞进一个预想中的万能覆盖索引。 并非所有索引方法都能还原 index-only 执行所需的完整值。B-tree 支持这种扫描，GIN 通常保存的是从原值提取的键，而不是完整原值，因此不支持 index-only scan。索引不仅要包含或能够还原所需数据，也要支持对应操作。其他数据库也使用“覆盖索引”这个名称，但不能据此把 PostgreSQL 的可见性机制直接套过去。 可见性映射决定是否访问堆 PostgreSQL 必须判断候选元组是否对当前 MVCC 快照可见，索引项没有保存全部所需可见性信息。Index-only scan 会检查堆页在 visibility map 中的 all-visible 位；该位已设置时，才能直接返回索引中的值，而不读取堆元组。这样省下的是堆访问，仍然需要读取索引和可见性映射，并不等于完全没有 I/O。 ...

索引是一场下注：你先付出写入与存储的代价，换来读取时能跳过表的大部分。可当一条查询真的需要表的大部分时，这场下注就输了——引擎会为每一行都下一次 B 树，而不是把那些页按顺序读一遍。 优化器给两条路线都算了账，所以真正有意思的问题不是"原则上谁更快"，而是交叉点落在哪里。表小并不等于工作量小，你在 1000 行时看到的计划，也不是 1000 万行时该期待的计划。 真正的货币是页，不是行 顺序扫描按物理顺序读表的页，一页能拿到很多行。PostgreSQL 用 seq_page_cost（默认 1.0）对 random_page_cost（默认 4.0）来给这件事定价：非顺序取到的页按顺序页的四倍计价，而把 random_page_cost 相对 seq_page_cost 调低，正是让优化器开始偏好索引扫描的那个开关。文档也指出，如果整个数据库都能装进缓存，把两者设成相等是合理的。 机械盘上一次随机寻道以毫秒计，而顺序读完一整条磁道几乎免费；NVMe 把这个差距压到微秒级，但页数本身并没有变，所以代价模型里「页数」这一项始终在。既然货币是页，行数就是一个很差的规模代理。同样是 1000 行的两张表：4 字节负载的那张是 12 页，4000 字节负载的那张是 1010 页。在 sqlite 3.53 上配 2 MB 页缓存，命中约 90% 行的宽谓词查询在窄表上 0.02 ms 跑完，在宽表上要 1.30 ms——行数完全一样，差了 65 倍。只有其中一张算"小"。 ...

复合 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 字符串；索引选择和输入处理需要分别正确。 ...