ITPub博客

数据库CBO优化器里的动态规划，线性算法，贪心算法，基因算法优缺点，适用场景分析 对各算法时间复杂度，解质量，内存占用，可解释性，可扩展性，CPU使用，以及表数量等详细剖析

MySQL 8.0 引入了直方图统计信息，这是一种更精确的统计方法，用于优化查询性能。直方图提供了列数据分布的详细统计信息，帮助优化器生成更准确的执行计划。

Optimizer Trace 是 MySQL 数据库的一个高级调优工具，它能深入揭示优化器选择执行计划的决策过程，是理解复杂 SQL 性能问题的利器。

MySQL Profiling（性能分析）是MySQL提供的一种用于分析SQL查询执行过程中各阶段资源消耗的工具。它能够详细展示SQL语句在服务器端的执行细节，包括各个执行阶段的耗时、CPU使用、内存使用等信息，帮助开发者定位性能瓶颈。

MySQL没有任何plan cache，就算同一个会话用prepare也没有，prepare只是减少SQL语法语义解析，不能减少评估分析执行计划过程。

Oracle 12c/19c 在 CBO 里最大的动作就是把“一次性定执行计划”改成了“执行期还能再改”，核心就是 Adaptive Query Optimization 家族。下面把 12c 引入、19c 继续增强的功能按“Adaptive Plan”和“Adaptive Statistics”两条线给你捋一遍。

rows是访问方法（全表，索引index access)的估算行数，不包括ICP

oracle谓词越界的面积衰减计算，oracle计算选择率，有个谓词越界选择率计算，不会一刀切立马估算返回1行，而是采用面积衰减计算，逐步衰减，更符合实际情况，防止因为谓词越界导致估算返回行是1，走错索引，join变成nested loops，笛卡尔积等。而pg和MySQL没有谓词越界衰减计算，采用一刀切。

CBO无法深度理解业务，SQL写法还是很重要。SQL是关系代数语言，CBO优化器只是从关系代数角度进行优化，包括查询重写，物理执行计划生成，但是无法理解真正的业务，比如a条件满足则有b条件，b条件选择性更好有索引，这个优化器不知道，必须手动添加，所以，有时候理解业务内在联系，也很重要。

关于oracle和MySQL选择率计算以及默认选择率的内容

Oracle中基于直方图的连接基数（join cardinality）估计算法。

很多中间件（如 WebLogic, JBoss）或连接池在将连接归还池中时，会习惯性地发出一条 ROLLBACK 指令。 在实际系统中，多余的用户回滚并不少见。它们通常由连接池重置、客户端框架清理、脚本中的预防性回滚或错误处理例程引起。这些回滚可能会增加统计计数器， 但实际上很少涉及撤销操作，这意味着它们对系统性能的影响通常可以忽略不计——即使在 AWR 报告中看起来令人担忧。

cpu time:纯cpu时间，越接近elapsed time越好，表示主要在内存里操作，锁和物理读之类的少 elapsed time:SQL从开始执行到结束fetch的时间，不包括SQL*NET的IDLE时间，elap只计算客户端活跃时间，如果并行操作，则累计所有子操作时间 duration time:SQL从开始到结束的所有时间，包括SQL*NET的非IDLE时间，这个主要是在sql monitoring里面显示，并行也是按照SQL实际执行时间，不累计子操作时间

以前没有function result cache,要实现类似效果，自定义函数比如是DETERMINISTIC并且建立函数索引，走索引才行， 当然，如果函数改变，但是索引未rebuild,则索引里存的函数值还是不变的，必须rebuild索引。 ​使用result cache,ORACLE会自动跟踪变化

distinct/group by的分页，要先消除order by排序，然后还要消除distinct/group by排序才能count stop key,用hash group by不行，必须 sort group by nosort才可以

在 MySQL 中，表达式 (id, ext) > (1, 2) 是基于字典序的比较。这种比较方式类似于对两个元组进行逐元素比较： 在ANSI里俗称row constructor,一般都是用于子查询多列比较，像ORACLE只支持子查询等值多列比较（非等值不支持），非子查询的不支持row constructor。 注意这种非等值的，MySQL CBO无法改写为OR，导致走不了range scan,当然覆盖索引是可能走的，所以非等值不要用这个语法，可以改为or。

oracle的nested loops semi/anti包括Filter,隐式的Filter如标量子查询，有subquery cache,内部构建hash表，实现缓存优化 而MySQL只有非相关子查询且没有unnest走Filter的，然后子查询走materialization的才有subquery cache。 pg的子查询没有subquery cache,只有普通nested loops join才有memorize（也是cache), 可以使用lateral join,也可以改成普通join，但是必须走nested loops才行。

主要是在semi join/anti join里使用，不管是nested loops,还是hash join,sort merge join,只要走semi join/anti join都有 first match思想，这是一种“短路”算法，快速裁剪，类似加个rownum=1(但是得注意，如果你手动加rownum=1, oracle数据库则子查询不能unnest,pg,mysql可以，所以，CBO自己能处理，就不要“画蛇添足”),

MySQL的in很长，超过200条不走index dive,要用cardinality,可能不准，大表的甚至可能走全表，然后逐条判断Filter,造成性能慢， 可以改为values row行构造为临时表，转为semi join,转为的是semi join,按理说剔重后转为join更好

按照SQL性能=cpu time+wait time，考虑执行计划突变，有多种原因，统计信息收集错，谓词条件out of range统计信息，灌入大量数据未收集，分区表灌数据收集时机不对，条件倾斜，数据量变化，绑定变量peeking问题，DML影响一致性块重构导致逻辑读增加，数据从buffer cache刷出，表和索引碎片增加，索引失效以及优化器BUG，ACS，cardinality feedback，dynamic sampling等影响执行计划。