【MySQL InnoDB 内核】Optimizer 与 Handler：ICP、MRR 与存储引擎边界
内容提要
本文讨论了MySQL 8.0.36中优化器与处理器的关系，重点介绍了索引条件下推（ICP）和多范围读取（MRR）机制。ICP通过在二级索引上过滤条件来减少回表次数，而MRR则通过批量收集主键来优化IO性能。文章分析了优化器如何利用统计信息选择索引，并使用EXPLAIN分析执行计划。同时，强调了MySQL与PostgreSQL在处理机制上的差异。
关键要点
-
优化器通过索引条件下推（ICP）在二级索引上过滤条件，减少回表次数。
-
多范围读取（MRR）机制通过批量收集主键并排序，优化IO性能。
-
EXPLAIN分析用于检查优化器如何利用统计信息选择索引。
-
MySQL与PostgreSQL在处理机制上存在差异，MySQL在存储引擎API层下推，而PostgreSQL在计划节点层下推。
-
覆盖索引可以避免回表，与ICP和MRR机制是正交的。
延伸解读
优化器与存储引擎的交互
MySQL的优化器与存储引擎之间的交互是通过handler接口实现的。优化器负责选择合适的索引，而handler则执行具体的读取操作。理解这一机制有助于开发者优化查询性能，特别是在复杂查询中，合理利用索引条件下推（ICP）和多范围读取（MRR）可以显著提高效率。
ICP与MRR的应用场景
索引条件下推（ICP）和多范围读取（MRR）在不同场景下各有优势。ICP适合于需要过滤大量数据的查询，而MRR则在处理高选择性范围查询时表现更佳。开发者在设计数据库查询时，应根据具体需求选择合适的机制，以优化性能和减少I/O开销。
MySQL与PostgreSQL的比较
MySQL与PostgreSQL在处理机制上存在显著差异。MySQL在存储引擎API层下推，而PostgreSQL则在计划节点层下推。这种差异可能影响到性能和查询优化策略，开发者在选择数据库时应考虑这些技术细节，以便更好地满足应用需求。
延伸问答
什么是索引条件下推（ICP）？
索引条件下推（ICP）是优化器通过在二级索引上过滤条件来减少回表次数的机制。
多范围读取（MRR）如何优化IO性能？
多范围读取（MRR）通过批量收集主键并排序，从而将随机IO转变为顺序IO，优化了IO性能。
如何使用EXPLAIN分析MySQL的执行计划？
使用EXPLAIN分析可以检查优化器如何利用统计信息选择索引，并显示实际行数与循环耗时。
MySQL与PostgreSQL在处理机制上有什么不同？
MySQL在存储引擎API层下推，而PostgreSQL在计划节点层下推，这导致了两者在处理机制上的差异。
覆盖索引与ICP和MRR有什么关系？
覆盖索引可以避免回表，与ICP和MRR机制是正交的，即它们在功能上是独立的。
在MySQL中，如何设置优化器开关以测试MRR？
可以通过设置优化器开关，如'SET optimizer_switch='mrr=on,mrr_cost_based=off';'来测试MRR的效果。
