TDSQL Boundless Ordering Index

一、功能概述

·Skip Sort：避免 filesort，减少排序的 CPU、内存和临时空间开销；

·流式聚集：相同分组的数据连续到达，完成一组即可输出一组；

·LIMIT 提前终止：按目标顺序取得足够结果后停止扫描；

·LIMIT-aware Join Order：多表查询中，将有序读取和提前终止的收益纳入连接顺序选择。

该能力适合分页、Top-N、时间线、排行榜，以及按维度分组的聚集查询。

注意：说明：

注意：“使用 Ordering Index”既可能表示当前过滤索引本身已经满足顺序，也可能表示优化器从过滤性更强的索引切换到能够提供顺序的索引。

二、支持版本

适用于 TDSQL Boundless V21.6.4.0 及以上版本。

三、索引如何提供顺序

以联合索引 INDEX(a, b, c) 为例，索引先按 a 排序，再在相同 a 内按 b、c 排序。因此，下列查询可以直接利用索引顺序：

SELECT *FROM tORDER BY a, b, c;

如果前导列被等值条件固定，也可以从后续列开始利用顺序：

SELECT *FROM tWHERE a = 10ORDER BY b, c;

此时 a 只有一个值，INDEX(a, b, c) 对当前结果集提供的有效顺序等价于 (b, c)。

以下查询通常不能利用该索引完成全局排序：

SELECT *FROM tORDER BY b, c;

不同 a 值内部的 (b, c) 分别有序，但合并后不具备全局 (b, c) 顺序。

四、排序方向

索引可以正向或反向扫描。升序索引可以通过反向扫描满足：

SELECT *FROM tWHERE a = 10ORDER BY b DESCLIMIT 20;

传统 EXPLAIN 可能显示 Backward index scan。

如果 ORDER BY 包含混合方向，建议使索引定义与排序方向一致：

CREATE INDEX idx_score_timeON ranking(category ASC, score DESC, created_at ASC);

对应查询：

SELECT *FROM rankingWHERE category = 1ORDER BY score DESC, created_at ASCLIMIT 20;

方向不匹配、存储引擎不支持反向读取，或混合方向的多范围扫描无法保持顺序时，优化器仍会使用 filesort。

五、范围条件的影响

联合索引前导列上的范围条件可能破坏后续列的全局顺序：

— 索引为 INDEX(a, b)SELECT *FROM tWHERE a BETWEEN 1 AND 10ORDER BY b;

每个 a 值内部的 b 有序，但多个 a 范围合并后通常不能保证全局 b 有序，因此仍可能需要 filesort。IN 形成多个范围时也需按执行计划确认。

六、利用 Ordering Index 实现 Skip Sort

如果访问路径输出的记录已经满足最终顺序，执行阶段不再创建额外排序步骤，这就是 Skip Sort。

有序路径：

索引范围扫描或索引扫描 -> 条件过滤 -> 返回结果

普通排序路径：

范围扫描或全表扫描 -> 条件过滤 -> 临时结果 -> filesort -> 返回结果

Skip Sort 不要求索引覆盖所有返回列。非覆盖索引也可以提供顺序，但可能需要逐行回表。优化器会综合比较：

·当前过滤访问路径的读取成本；

·Ordering Index 的扫描和回表成本；

·过滤条件选择率；

·排序成本；

·LIMIT 大小；

·多表连接的 fanout。

七、单表 Top-N 示例

CREATE TABLE events ( id BIGINT PRIMARY KEY, user_id BIGINT NOT NULL, created_at DATETIME NOT NULL, payload VARCHAR(200), KEY idx_user_time(user_id, created_at DESC));EXPLAINSELECT id, created_at, payloadFROM eventsWHERE user_id = 100ORDER BY created_at DESCLIMIT 20;

理想计划特征：

·key 为 idx_user_time；

·Extra 中没有 Using filesort；

·反向读取升序索引时可能显示 Backward index scan。

是否使用 Ordering Index 由成本决定。数据规模很小，或其他索引过滤后只需排序少量数据时，选择 filesort 也可能是更优计划。

九、工作原理

对于 GROUP BY，只要输入数据按分组键兼容的顺序排列，相同分组的记录就会连续到达。执行器可以：

1. 初始化当前分组的聚集状态；

2. 持续累加当前组记录；

3. 发现分组键变化时输出上一组；

4. 重置状态并开始下一组。

该方式通常只需维护当前分组及聚集函数状态，不需要同时保存全部分组，因而可以降低聚集内存和首批结果延迟。

十、使用示例

CREATE TABLE metrics ( id BIGINT PRIMARY KEY, tenant_id BIGINT NOT NULL, category INT NOT NULL, value DECIMAL(18,2) NOT NULL, KEY idx_tenant_category(tenant_id, category));EXPLAINSELECT category, COUNT(*), SUM(value)FROM metricsWHERE tenant_id = 100GROUP BY category;

tenant_id 被等值条件固定后，索引可以按 category 连续输出数据。理想计划特征：

·key 为 idx_tenant_category；

·Extra 中没有 Using temporary；

·Extra 中没有 Using filesort。

十二、功能说明

prefer_ordering_index 控制优化器是否更积极地搜索能够满足 ORDER BY 或 GROUP BY 顺序的替代索引。

开启后，如果当前过滤路径不能提供所需顺序，优化器会评估其他候选索引，主要考虑：

·是否完整提供目标顺序；

·正向还是反向扫描；

·是否为覆盖索引；

·Ordering Index 的扫描、回表成本；

·当前访问路径的读取成本；

·LIMIT 大小；

·GROUP BY 平均每组记录数；

·后续连接表的 fanout。

该开关允许优化器选择成本合理的有序路径，并不强制使用某个索引。

十三、配置方式

当前会话开启：

SET SESSION optimizer_switch = 'prefer_ordering_index=on';

单语句开启：

SELECT /*+ SET_VAR(optimizer_switch='prefer_ordering_index=on') */ id, created_at, payloadFROM eventsWHERE user_id = 100ORDER BY created_at DESCLIMIT 20;

关闭：

SET SESSION optimizer_switch = 'prefer_ordering_index=off';

不同版本或部署模板的默认值可能不同，应以实例的 @@global.optimizer_switch 和 @@session.optimizer_switch 为准。

十四、关闭后仍可能 Skip Sort

prefer_ordering_index=off 不等于禁用所有索引排序优化。以下场景仍可能 Skip Sort：

·当前因过滤条件选择的索引本身已经提供顺序；

·当前访问方式为表扫描或全索引扫描，优化器找到成本合适的有序索引；

·使用了 FORCE INDEX FOR ORDER BY 或 FORCE INDEX FOR GROUP BY；

·其他优化规则已经确定有序访问路径。

该开关主要影响是否主动从已有访问路径切换到另一个 Ordering Index。

十五、适用场景与风险

典型场景：

SELECT …FROM tWHERE filter_col = ?ORDER BY order_col DESCLIMIT 10;

小 LIMIT、符合条件的记录在 Ordering Index 中分布较均匀，且待排序数据量较大时，收益通常更明显。

Ordering index 优先保证顺序，不一定具有最强过滤能力。例如：

SELECT *FROM messagesWHERE receiver_id = 100 AND status = 1ORDER BY created_at DESCLIMIT 20;

只使用 INDEX(created_at) 可能扫描大量无效记录。更合适的索引通常是：

CREATE INDEX idx_receiver_status_timeON messages(receiver_id, status, created_at DESC);

如果版本启用了 Adaptive Ordering Index，系统可以在实际扫描明显超出估算时重新优化，作为运行时保护。可通过下列状态观察是否触发：

SHOW SESSION STATUS LIKE 'Adaptive_ordering_used';

自适应保护不能替代合理的联合索引和准确的统计信息。

十七、功能说明

传统连接顺序优化主要比较各表的过滤、连接和读取成本。对于多表 ORDER BY … LIMIT，如果排序列位于非驱动表，通常需要完成较多连接后再排序。

LIMIT-aware Join Order 会评估另一类计划：

1. 让能按 ORDER BY 顺序读取的表作为驱动表；

2. 通过 Ordering Index 按目标顺序扫描；

3. 逐行连接后续表；

4. 产生足够结果后提前停止。

这样可能同时减少驱动表扫描量、后续连接次数和最终排序开销。

十八、配置方式

开启连接顺序成本优化：

SET SESSION optimizer_switch = 'limit_aware_join_order=on';

生产使用通常建议与 Ordering Index 偏好一并开启，使单表访问路径选择和多表连接顺序选择协同工作：

SET SESSION optimizer_switch = 'prefer_ordering_index=on,limit_aware_join_order=on';

单语句开启时，应在同一个 optimizer_switch 字符串中合并两个子选项：

SELECT /*+ SET_VAR( optimizer_switch='prefer_ordering_index=on,limit_aware_join_order=on' ) */ o.id, o.updated_at, d.tagFROM order_detail AS dJOIN orders AS o ON o.org_id = d.org_idWHERE d.tag = 1 AND o.region_id = 1ORDER BY o.updated_at DESCLIMIT 5;

十九、多表 Top-N 示例

建议索引：

CREATE INDEX idx_orders_region_timeON orders(region_id, updated_at);

查询：

EXPLAINSELECT o.id, o.updated_at, d.tagFROM order_detail AS dJOIN orders AS o ON o.org_id = d.org_idWHERE d.tag = 1 AND o.region_id = 1ORDER BY o.updated_at DESCLIMIT 5;

region_id 被等值固定后，orders 可以通过 idx_orders_region_time 反向扫描 updated_at。

优化前可能表现为：

·order_detail 为驱动表；

·连接后出现 Using temporary; Using filesort。

优化后可能表现为：

·orders 成为第一张非 const 表；

·使用 idx_orders_region_time；

·显示 Backward index scan；

·不再显示 Using filesort。

20、成本估算

优化器根据后续连接的平均 fanout，估算为了产生 LIMIT 条最终结果需要从驱动表读取多少行：

> 预计驱动表读取行数 = max(LIMIT / 有效后缀 fanout, 1)

再按预计读取量占驱动表候选行数的比例折算完整计划成本。

21、生效条件与限制

典型生效条件包括：

·多表查询包含有效的 ORDER BY 和 LIMIT；

·ORDER BY 可由驱动表的可用 ref 或 range 索引满足；

·排序项是可索引字段，而不是普通索引无法直接满足的表达式；

·驱动表有序输出能被后续连接算子保持；

·预计只需读取驱动表的一部分数据即可产生足够结果；

·调整后的完整计划成本优于其他连接顺序。

以下场景通常不应用该优化：

·没有 LIMIT 或没有 ORDER BY；

·LIMIT 很大，提前停止收益不足；

·排序字段来自多张表，不能由一个驱动表提供最终顺序；

·包含聚集、HAVING、窗口函数或 DISTINCT；

·ORDER BY 使用普通索引不能满足的表达式；

·后续 join buffer、BKA/MRR 等执行方式不能保持驱动表顺序；

·所选 range 访问无法按需要方向稳定输出。

STRAIGHT_JOIN 或固定连接顺序的 Hint 会限制优化器调整 join order。希望该能力自动选择驱动表时，不应固定一个与 Ordering Index 方案冲突的连接顺序。

23、WHERE + ORDER BY + LIMIT

推荐的联合索引顺序通常是：

等值过滤列 -> 排序列 -> 其他用于覆盖的列

例如：

SELECT id, created_at, titleFROM articlesWHERE tenant_id = 10 AND status = 1ORDER BY created_at DESCLIMIT 20;

推荐索引：

CREATE INDEX idx_article_tenant_status_timeON articles(tenant_id, status, created_at DESC);

24、WHERE + GROUP BY

推荐顺序通常是：

等值过滤列 -> 分组列 -> 其他用于过滤或覆盖的列

例如：

SELECT category, COUNT(*), SUM(amount)FROM salesWHERE tenant_id = 10GROUP BY category;

推荐索引：

CREATE INDEX idx_sales_tenant_categoryON sales(tenant_id, category);

25、覆盖索引

将高频返回列纳入索引可以减少回表，但需要权衡索引空间、写放大、DML 性能和缓存利用率。不建议仅为消除一次小规模排序而创建过宽索引。

26、分页方式

即使 Skip Sort，大偏移分页仍需跳过大量索引项：

ORDER BY created_at DESCLIMIT 100000, 20;

建议使用游标式翻页：

SELECT id, created_at, payloadFROM eventsWHERE tenant_id = 10 AND (created_at, id) < (?, ?)ORDER BY created_at DESC, id DESCLIMIT 20;

配套索引：

CREATE INDEX idx_events_tenant_time_idON events(tenant_id, created_at DESC, id DESC);

将主键等唯一列加入排序，可以在排序列重复时保证分页结果稳定。

28、查看 EXPLAIN

不要只看 key。即使选择了排序列相关索引，如果 Extra 仍有 Using filesort，说明该访问路径没有完整满足最终顺序。

29、查看 Optimizer Trace

SET SESSION optimizer_trace = 'enabled=on';SET SESSION optimizer_trace_max_mem_size = 1048576;EXPLAINSELECT …;SELECT JSON_PRETTY(TRACE)FROM information_schema.optimizer_trace;SET SESSION optimizer_trace = 'enabled=off';

Ordering Index 选择重点关注：

·ordering_index_selection；

·context：order_by、group_by 或 table_scan；

·indexes_considered；

·direction、used_key_parts、is_covering；

·index_scan_cost、chosen；

·selected_index、selected_direction。

常见未选原因包括：

·index cannot provide required order；

·index scan cost higher than read_time；

·no limit and not covering；

·covering index preferred over non-covering；

·ref_key covering with better selectivity。

LIMIT-aware Join Order 重点关注：

·ordering_limit_aware_cost；

·ordering_key、select_limit；

·suffix_fanout、fanout_safety_factor；

·driving_tab_early_stop_ratio；

·new_cost_for_plan。

如果没有 ordering_index_selection，可能是当前路径已经天然有序，无需搜索替代索引；也可能是查询不满足优化条件。

30、能力边界总结

Ordering Index 的核心价值不是尽可能以索引代替排序，而是在过滤、读取、回表、连接、排序和提前终止之间选择总体成本更低的执行路径。