MariaDB基于binary(16) UUID列的LEFT JOIN慢查询优化问题

• 核心异常点：你贴出的索引信息里，两张表所有索引的Cardinality（基数）字段值均为0。该值是数据库优化器判断索引区分度、选择执行计划的核心依据，基数为0时优化器会判定索引无使用价值，直接选择全表扫描+BNL（块嵌套循环）连接的执行策略，这是查询耗时过长的根本原因。
• 带LIMIT 300的第二版查询比全量查询稍快，并非优化器在连接完成前截断了结果，而是BNL连接遍历数据时，凑够返回行数就提前终止了扫描，但因为依然需要遍历大量前置数据，耗时仍处于秒级以上。
• 第三版子查询写法耗时更高的原因：无ORDER BY的子查询LIMIT没有被优化器做结果缓存，执行时依然要全表扫描pb_work生成300行的派生临时表，额外增加了临时表构建的开销；单独执行子查询速度快，是因为单表扫描凑够300行就直接返回，不需要执行后续连接逻辑。
• 额外性能损耗：你用binary(16)存储无序UUID时，InnoDB的聚簇主键索引会因为主键值随机产生大量页分裂、索引碎片，进一步拉低索引扫描效率。

1. 修复索引统计信息异常
   这是优先级最高的操作，直接执行以下命令重新收集两张表的持久化统计信息，让优化器能正确识别索引区分度：

   ANALYZE TABLE pb_work, pb_instance PERSISTENT FOR ALL;
   

   执行完成后重新查询索引信息，确认Cardinality字段显示为接近100万的数值，即代表统计信息修复完成。
2. 分页查询必须显式加排序，禁止无ORDER BY的LIMIT
   无排序的LIMIT返回结果不确定，优化器也无法做提前终止扫描的优化。分页场景请以主键为排序依据，将查询改写为：

   SELECT * FROM pb_work w
   LEFT JOIN pb_instance pi ON w.id = pi.work_id
   ORDER BY w.id
   LIMIT 300;
   

   统计信息正常的前提下，该语句的执行逻辑为：按主键顺序扫描pb_work，每取到1条记录就通过pb_instance.work_id上的索引匹配关联数据，凑够300条work对应的结果集就直接终止执行，正常耗时应在几十毫秒级别。
3. 避免用派生表子查询的方式实现分页
   MariaDB对无索引的派生表优化能力较差，你之前写的子查询版本会引入额外的临时表构建开销，直接使用主表排序+LIMIT的写法即可。
4. UUID存储长期优化（可选）
   如果你当前使用的是随机生成的UUID（比如UUIDv1、v4），建议替换为有序UUID（比如UUIDv7）存储，可大幅减少InnoDB聚簇索引的页分裂与碎片，提升分页、范围扫描性能。如果暂时无法调整生成逻辑，定期执行OPTIMIZE TABLE pb_work, pb_instance;整理索引碎片也能获得一定性能提升。

优化完成后重新执行EXPLAIN查看执行计划，符合以下特征即代表执行逻辑正确：

• pb_work表的type字段为index，命中PRIMARY主键索引，Extra字段无全表扫描相关提示
• pb_instance表的type字段为ref，命中IDX_CA4ED742BB3453DB索引，ref字段关联到pb_work.id，预估扫描行数rows为1左右，Extra字段不再出现Using join buffer (flat, BNL join)提示

内容的提问来源于stack exchange，提问作者Wolfone