MySQL8.0 prefer_ordering_index参数详解MySQL 8.0 中的 prefer_ordering_index 参数是一个重要的优化器开关，用于控制优化器在处理包含 ORDER BY、GROUP BY 或 LIMIT 子句的查询时，是否优先使用有序索引。该参数通过 optimizer_switch 系统变量进行控制。
参数详解
1. 参数作用
prefer_ordering_index 参数用于调整优化器对“排序索引”的偏好。该参数的值可以是 on 或 off，默认值为 on 。
开启（默认） ：prefer_ordering_index=on，优化器优先选择排序相关索引，以减少排序操作。这意味着优化器会优先使用有序索引，以避免不必要的排序操作，从而提高查询性能 。
关闭：prefer_ordering_index=off，优化器弱化对排序索引的偏好，更倾向于选择过滤性好的索引，即使需要额外排序。关闭该参数后，优化器可能会选择其他更高效的查询策略，例如使用索引过滤和 ICP（Index Condition Pushdown）特性来减少物理 I/O 。
2. 参数设置方式
该参数通过 optimizer_switch 系统变量进行设置。设置方式如下：
开启（默认）：
SET optimizer_switch = "prefer_ordering_index=on";
关闭：
SET optimizer_switch = "prefer_ordering_index=off";
3. 适用场景
该参数主要适用于以下场景：
包含 ORDER BY、GROUP BY 或 LIMIT 子句的查询。
当查询中存在高效过滤索引且 ORDER BY 字段无关时，关闭该参数可能提升性能 。
在某些情况下，关闭该参数可以避免全表扫描，减少物理 I/O，提高查询性能 。
4. 潜在问题与注意事项
性能影响：该参数是一个“双刃剑”，开启或关闭该参数可能会影响查询性能。例如，开启该参数可能导致某些查询性能下降，而关闭该参数可能导致其他查询性能提升 。
BUG 与缺陷：MySQL 曾提交过与该参数相关的 BUG，例如 Bug#74602 和 Bug#78612，这些问题可能影响优化器的行为 。
兼容性：该参数在 MySQL 8.0.21 及以后版本中引入，适用于 MySQL 8.0 及更高版本 。
5. 验证与测试
可以通过以下方式验证参数是否生效：
检查当前参数状态：
SELECT @@optimizer_switch LIKE '%prefer_ordering_index=on%';
如果返回 1，表示参数已启用 。
通过 EXPLAIN 分析查询执行计划，观察优化器是否使用了有序索引 。
总结
prefer_ordering_index 是 MySQL 8.0 中一个重要的优化器开关，用于控制优化器在处理排序查询时的行为。通过调整该参数，开发者可以根据具体查询场景和性能需求，灵活控制优化器的行为，以达到最佳性能。在实际应用中，建议结合 EXPLAIN 和性能测试，根据具体场景调整该参数 。
