携带足够列、能独自满足整条查询的索引

InnoDB 覆盖索引包含查询所需的列

覆盖索引包含某条查询求值和返回结果所需的列。在 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 隐式提供主键标识。先检查并运行只需要这些值的查询: ...

三月 3, 2028 · 2 分钟 · XB Field Notes
依据分区键把整块分区从查询中排除

分区裁剪跳过查询不可能用到的整张分区

分区裁剪排除的是整张不可能包含匹配行的分区。它与在剩余分区内部利用索引寻找行不同,也不是按页工作的缓存技巧。PostgreSQL 可以在查询规划阶段完成排除,也可以等参数值在执行过程中可用后再裁剪。要判断它是否生效,需要查看实际访问的分区,而不是只看最终返回了多少行。 证明哪些分区不可能命中 声明式分区的边界来自分区键。把日期键限制在十月,可以证明九月和十一月与查询无关。裁剪依赖这些边界,而不是估计其他分区“可能很少匹配”。统计信息仍影响其他规划决策,但行数估计不准,不会让分区归属变成概率判断;这两个层面的信息应分开解释。 裁剪不要求先建索引。排除无关分区后,如果剩余分区的大部分行都需要读取,PostgreSQL 完全可能选择顺序扫描。所以,出现 Index Scan 并不是裁剪成功的判据。应先检查物理关系名和计划结构,再单独评价剩余每张表内部的访问方式。顺序扫描一张相关分区,仍然可能已经省掉其他分区的大量工作。 条件必须能对应分区键及其比较语义。把日期列转换成文本,可能隐藏直接的边界关系,即使某个字符串条件恰好仍选出相同行。应使用有类型的参数和直接表达目标区间的条件,不能假定分区键外面套任意表达式都保留裁剪能力,甚至不能默认它保留原来的日期语义。 参数也能在执行时触发裁剪 常量可以支持规划期裁剪,使计划只留下相关子表。预备语句的通用计划无法提前知道未来每次传入的参数值,但这不表示它必须扫描全部分区:执行初始化时已经知道的值,也可以用于执行期裁剪。看见通用计划中原本存在多个分支,还不足以判断最终都会读取。 EXPLAIN ANALYZE 用 Subplans Removed 报告初始化阶段排除的分区。另一些情况,例如嵌套循环内侧参数在执行期间变化,可以在实际执行过程中继续裁剪。分析这些场景时,需要查看循环次数和子计划是否运行。某个节点显示从未执行,也可能是外层没有输入等其他原因,不能脱离完整计划单独当成裁剪证据。 因此,不能把这些规则简化成“只要不是常量表达式,就会失去裁剪”。参数何时可用,以及表达式是否受支持,共同决定何时能够排除分区。定制计划和通用计划可能不同,但返回同样答案。真正值得确认的是:负载使用真实参数时,哪些分区实际被访问了。 坏掉的样子 只过滤无关字段,可能让所有分区仍然相关;把键转换成无法对应边界的表达式,也可能产生同样结果。调整分区布局前,应检查准确条件和 enable_partition_pruning 设置。手工添加相互矛盾的 CHECK 约束并不是修复方法;普通本地声明式分区会执行自己的边界规则,而基于 CHECK 的约束排除是另一种优化机制。 裁剪也不保证没有锁或规划成本可以忽略。执行初始化阶段排除的分区,在执行开始时仍然会被锁定。大型分区层级可能消耗规划时间和会话内的元数据内存,即使最后只扫描少数子表。应根据真实查询和维护模式选择层级,而不是追求尽可能多的分区数量。 最后,即使裁掉两个分区,第三个分区内部仍可能有几千条无关行。合适的局部索引可能帮助第二阶段。最终返回一百行,不证明只检查了一百行;缓冲命中也不等于物理磁盘读取。必须沿着计划节点看清扫描、过滤与输出之间的区别。 快速验证 把下面内容保存为 partition-pruning.sql,在可丢弃的 PostgreSQL 18 数据库中执行 psql -X -v ON_ERROR_STOP=1 -f partition-pruning.sql。脚本在事务内启用裁剪,并用通用预备计划展示初始化阶段的行为。临时分区上没有建立任何索引。 \set ON_ERROR_STOP on BEGIN; SET LOCAL datestyle='ISO, YMD'; SET LOCAL enable_partition_pruning=on; CREATE TEMP TABLE observations (day date,id integer) PARTITION BY RANGE(day); CREATE TEMP TABLE obs_sep PARTITION OF observations FOR VALUES FROM ('2026-09-01') TO ('2026-10-01'); CREATE TEMP TABLE obs_oct PARTITION OF observations FOR VALUES FROM ('2026-10-01') TO ('2026-11-01'); CREATE TEMP TABLE obs_nov PARTITION OF observations FOR VALUES FROM ('2026-11-01') TO ('2026-12-01'); INSERT INTO observations SELECT DATE '2026-09-01'+g%90,g FROM generate_series(0,8999) AS g; ANALYZE observations; EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT count(*) FROM observations WHERE day>=DATE '2026-10-01' AND day<DATE '2026-11-01'; SET LOCAL plan_cache_mode=force_generic_plan; PREPARE by_day(date) AS SELECT count(*) FROM observations WHERE day=$1; EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) EXECUTE by_day(DATE '2026-10-10'); EXECUTE by_day(DATE '2026-10-10'); EXPLAIN (ANALYZE, BUFFERS, TIMING OFF) SELECT count(*) FROM observations WHERE day::text='2026-10-10'; DO $$ BEGIN IF (SELECT count(*) FROM observations WHERE day=DATE '2026-10-10') <> 100 OR (SELECT count(*) FROM observations WHERE day>=DATE '2026-10-01' AND day<DATE '2026-11-01') <> 3100 THEN RAISE EXCEPTION 'unexpected pruned result'; END IF; END $$; DEALLOCATE by_day; ROLLBACK; PostgreSQL 18.6 把常量十月区间规划为只扫描 obs_oct,读取三千一百条匹配行。通用日期查询报告 Subplans Removed 为二,返回一百行,但仍需在十月分区中过滤三千条其他记录。这同时展示了成功排除无关分区,以及相关分区内部尚未消除的工作量。 ...

二月 13, 2028 · 2 分钟 · XB Field Notes
在语句顶层命名一个子查询,让复杂语句保持可读。

公用表表达式把一个查询先命名再复用

公用表表达式在一条 SQL 内为查询结果命名。它可以先说明数据总体和中间计算,再写最终 SELECT,让复杂报表更容易阅读。这个名字不是持久表,也不自动承诺缓存结果、固定扫描次数或按顺序执行的一组命令。理解 CTE 时,应把可读结构与执行方式分别检查。 先确定作用域,再讨论执行 WITH 子句只属于当前语句。后续语句不能继续引用它的名字,执行结束后也没有一个可供建立索引或查询的 schema 对象。如果计算结果或查询定义需要保留到后续语句继续使用,应选择临时表、视图或其他合适的数据库对象。CTE 适合命名结果与语句作用域相匹配的工作,不能靠命名制造跨请求缓存。 现代 PostgreSQL 可以把非递归、没有 volatile 函数的 SELECT CTE 折叠进外层查询。默认情况下,只引用一次时会进行这种合并,使外层限制参与共同优化。因此,外层按主键查找时,条件可以直接作用于底层表的索引,而不必先生成独立中间结果再过滤。 同一个符合条件的 CTE 被引用多次时,PostgreSQL 默认通常会物化它。这能减少重复昂贵计算,但也可能阻止某个引用位置上的选择性条件被推入共享计算。这个区别从 PostgreSQL 十二开始适用;认为所有 WITH 都是固定优化边界,描述的是旧行为,不能覆盖当前默认规则。应根据实际版本和引用次数检查,而不是依赖旧经验。 物化是一项取舍 MATERIALIZED 请求独立计算。NOT MATERIALIZED 允许多次引用时也进行折叠,可能重复一部分工作,但让每次使用只访问自己需要的数据。对于递归或不是无副作用 SELECT 的 CTE,PostgreSQL 会忽略 NOT MATERIALIZED;它不会因此允许任意复制写入或 volatile 操作。 物化的 SELECT CTE 也不一定完整算到末尾。消费方可能只请求部分结果,不过排序、关联等操作又可能要求读取更多。物化同样不等于为后续语句建立一张带索引的可复用表。决定强制边界之前,应查看计划并测量真实负载,不能因为“物化”听起来像缓存就默认收益更好。 ...

十二月 19, 2027 · 2 分钟 · XB Field Notes
服务加权比例记录主题封面,展示观察、判断与行动三个步骤。

记录规则应保留比例的分子和分母

当查询成本和复用频率足以支持预计算时,反复使用的服务级查询适合做成记录规则。关键问题是保存什么。如果只保存每个实例的错误百分比,可能丢掉之后聚合必需的流量规模;同时保存错误速率与请求速率,则保留了分子和分母,消费者能够据此计算按实际请求量加权的服务错误占比,而不是把每台实例当成相同流量。 百分比必须对应明确的总体 假设繁忙实例每秒处理十个请求,每秒发生零点一个错误;空闲实例每秒处理一个请求,却每秒发生零点五个错误。两个实例的错误占比分别为百分之一与百分之五十。直接平均得到百分之二十五点五,相当于给两个实例相同权重,但它们的请求量实际上相差十倍。这个数描述平均实例百分比,不描述服务处理的全部请求。 服务合计每秒处理十一个请求,发生零点六个错误,因此错误占比是 0.6/11,约百分之五点四五。这是聚合错误量除以聚合请求量。两侧必须描述同一组符合条件的流量,并使用相同时间窗口。如果分子排除了分母包含的一类失败,或者两侧聚合了不同服务,面板即使计算得很快,结果也仍然不成立。 按顺序建立规则依赖 先对每条计数器序列应用 rate,使已经观测到的重置能够在聚合前得到处理,再按照消费者确实需要的维度求和。本例依次记录服务与实例维度的请求速率、服务请求速率、服务错误速率,以及最终服务错误占比。第一个实例级中间结果用于展示明确的前后依赖,后续规则实际消费这个结果,而不是重复写一个看似相关的名字。 四条规则位于同一个组,按声明顺序运行,使用相同评估时间。分子与分母都保留匹配的 service 标签,因此除法能够把对应服务配对。如果分母额外保留实例标签,就会改变向量匹配关系,可能导致没有结果,或需要重新设计连接方式。测试应同时检查输出标签和数值,不能只看到一个合理百分比就认为查询正确。 命名应说明聚合层级和范围。两分钟速率与三十分钟速率即使当前值恰好相同,也不是同一种量。应在规则旁记录哪些事件计入总体,并明确消费它的面板与告警。沿用旧名称但修改公式,只会改变未来记录样本,不会重新解释已经写入的历史。需要保持时间可比性时,定义变更必须被消费者看见。 坏掉的样子 平均百分比可能让一台低流量故障实例主导整个服务报告,也可能掩盖高流量实例的失败。提高评估频率不会修复这个算术问题,应恢复底层计数或速率,在分别聚合后再相除。如果最后只保留了百分比,通常无法仅靠这些百分比重建原始流量权重,因为相同占比可以来自完全不同的请求数量。 零流量是另一类情况。分母为零时,错误占比并没有被定义为零;分子缺失也可能代表仪表化缺少序列,而不是没有错误。应根据指标契约明确这些语义,避免自动把所有空结果或未定义数值显示成健康的零。还应确认健康期间错误计数器是否持续导出,避免只有发生错误后才创建序列,导致正常状态与数据缺失难以区分。 快速验证 保存为 recording-weighted.py 后执行 python3 recording-weighted.py。实验需要 Docker 与 Prometheus 3.5.1 镜像,首次运行可能下载镜像。真实 promtool 使用每分钟一份的合成计数器样本评估四条记录规则,故意为两个实例提供差异很大的流量权重与错误占比,让错误平均方法无法碰巧得到正确答案。 import subprocess import uuid # Requires Docker and the public Prometheus 3.5.1 image. tests = r"""evaluation_interval: 1m rule_files: - /tmp/rules.yml tests: - interval: 1m input_series: - series: "http_requests_total{service=\"checkout\",instance=\"busy\"}" values: "0+600x5" - series: "http_errors_total{service=\"checkout\",instance=\"busy\"}" values: "0+6x5" - series: "http_requests_total{service=\"checkout\",instance=\"quiet\"}" values: "0+60x5" - series: "http_errors_total{service=\"checkout\",instance=\"quiet\"}" values: "0+30x5" promql_expr_test: - expr: "service:http_requests:rate2m" eval_time: 5m exp_samples: - labels: "{__name__=\"service:http_requests:rate2m\",service=\"checkout\"}" value: 11 - expr: "abs(service:http_errors:rate2m - 0.6) < bool 1e-12" eval_time: 5m exp_samples: - labels: "{service=\"checkout\"}" value: 1 - expr: "abs(service:http_errors:ratio_rate2m - 0.6/11) < bool 1e-12" eval_time: 5m exp_samples: - labels: "{service=\"checkout\"}" value: 1 - expr: "abs(avg(rate(http_errors_total[2m]) / rate(http_requests_total[2m])) - 0.255) < bool 1e-12" eval_time: 5m exp_samples: - labels: "{}" value: 1 """ rules = r"""groups: - name: service-rates interval: 1m rules: - record: instance:http_requests:rate2m expr: sum by (service, instance) (rate(http_requests_total[2m])) - record: service:http_requests:rate2m expr: sum by (service) (instance:http_requests:rate2m) - record: service:http_errors:rate2m expr: sum by (service) (rate(http_errors_total[2m])) - record: service:http_errors:ratio_rate2m expr: service:http_errors:rate2m / service:http_requests:rate2m """ script = "set -eu\ncat > /tmp/tests.yml <<'TEST_DATA'\n" + tests + "\nTEST_DATA\n" if rules: script += "cat > /tmp/rules.yml <<'RULE_DATA'\n" + rules + "\nRULE_DATA\n" script += "exec /bin/promtool test rules /tmp/tests.yml\n" name = "xbcatm-prom-unit-" + uuid.uuid4().hex[:12] try: result = subprocess.run( ["docker", "run", "--rm", "--name", name, "--network", "none", "-i", "--entrypoint", "/bin/sh", "prom/prometheus:v3.5.1", "-s"], input=script, text=True, capture_output=True, timeout=45, ) finally: subprocess.run(["docker", "rm", "-f", name], stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL) print(result.stdout, end="") if result.returncode: print(result.stderr, end="") raise SystemExit(result.returncode) 预期输出为 SUCCESS。记录请求速率为十一,错误速率为零点六,最终占比为 0.6/11。另外一项断言明确展示,错误的实例比例平均法得到 0.255。涉及浮点除法的比较使用很小的数值容差,标签断言则确认仍保留 checkout 服务身份。这些检查同时覆盖计算次序、依赖输出和最终聚合口径。 ...

八月 31, 2027 · 2 分钟 · XB Field Notes
把查询计划画成从顺序扫描经索引到结果的路线,并高亮慢的那一步。

EXPLAIN 计划揭示查询路径

慢查询首先是一个路线问题。EXPLAIN 逐节点画出规划器选择的路径,让你针对真正耗时的步骤去修,而不是凭猜测。 把计划当作路线来读 EXPLAIN 输出是一棵树:根节点是最终答案,真正的活是子节点干的。每个节点有操作名、行数估算和成本,真正值得盯的是离群点——行数估算远大于实际返回数的大扫描,或者与实际执行差距巨大的估算。读树的顺序很关键:要顺着成本最大的节点找,而不是盯着模式里第一个出现的那层。把 EXPLAIN 和 EXPLAIN ANALYZE 并排看,能直接看到规划器「猜的」和真实「跑出来」的差距,慢查询多半就藏在那里。 统计信息决定路线 有索引只是「有了选项」,不等于「会走到那里」。规划器是根据观测到的统计量——行数、可空性、列值分布——来选路线,所以一次 autovacuum、一次大量导入、一次缓存命中率变化,都可能让同一条 SQL 改道。卡在热点路径上的查询,应该在数据画像变化时重新分析,而不是只在建的时候看一次。统计信息过期时,一个 ANALYZE 有时比一顿索引微调改写更能改变计划。把计划当成需要拿真实 EXPLAIN 数据反复复核的证据,而不是背下来就完事的快照。 确认路线真的变了 把计划塞进改动循环:先留下基线 EXPLAIN ANALYZE,加索引或用改写后的 SQL,确认路线真的移动了再宣布成功。如果目标节点还是那个大的扫描、成本结构没变,那这改动无论听起来多顺,都只是表面功夫。经验再丰富,也不如前后两份计划来得实在;真实的执行耗时、取行数也要一并记录。新计划生效后,把前后对比放进合入请求里,让这次收益可追溯、可复现。 坏掉的样子 统计信息过期会让规划器走错路。一次大批量导入之后,如果很久没有跑过 ANALYZE,规划器可能还以为表里只有几千行,而实际已经有几百万行,于是继续选择只在小数据量下才划算的索引或嵌套循环。EXPLAIN ANALYZE 会露出马脚:估算行数与实际行数差出数量级。先跑 ANALYZE 再对比这两个数字,确认路线是基于新鲜画像选出来的,而不是急着加索引。数据分布变化之后不复核计划,再合理的索引也可能建在错误的假设上。 预编译语句会把某一次参数的计划复用到所有参数上。围绕高区分度的首个参数缓存下来的计划,对它很快,对匹配半张表的值却很慢;而你用 EXPLAIN 拿字面量测出的计划,往往并不是线上真正绑定的那个值。排查时要把区分度最高和最低的两个极端值都测一遍,并确认到底命中了定制计划还是通用计划,再下结论。只测一个最顺的值,就等于只看了一半的路线图。 隐式转换会悄悄让索引退休。拿 varchar 列直接和数字比较、排序规则不一致,或者在列上套 LOWER、日期截断,规划器只能全表扫描再过滤,索引明明存在却用不上。症状很固定:Seq Scan 上被过滤掉的行数远大于最终返回行数。修法是把调用处的类型改对,或者补一个匹配表达式的函数索引,让写法和索引站在同一边。类型不对时加再多索引,也只是给用不上的路线添摆设。 只看 cost 是最后一个陷阱。cost 是无量纲的规划估算,不是执行耗时;估算最便宜的计划,实际可能打出天文数字的共享缓冲命中,或者把排序溢出到临时文件。务必用 EXPLAIN (ANALYZE, BUFFERS) 确认,把实际耗时、buffers、被过滤掉的行数和 cost 并排读,几个数字一致向好,才能说这条路线真的健康。脱离 buffers 谈 cost,和脱离实际行数谈估算一样,都是在看路线的广告而不是路况。 ...

五月 21, 2027 · 2 分钟 · XB Field Notes