同一条SQL在MariaDB控制台/PHP执行慢、DBeaver执行快问题咨询

问题成因 #

• SQL结构冗余：该查询由Laravel DataTables自动生成，外层仅需统计符合条件的行数，内层子查询却查询了十余个无关字段，还附加了没必要的ORDER BY排序，这些冗余操作会大幅拉高优化器对查询成本的错误预估。
• 优化器索引选择偏差：paquetes表共800万条记录，过滤条件为created_at时间范围+estado != 0，在表统计信息不准确的情况下，MariaDB优化器会判断该过滤条件命中的行数占总表比例较高，认为走索引后回表查询其他字段的成本高于全表扫描，因此选择全表扫描。当添加LIMIT 1时，优化器明确知晓仅需返回1行，走索引的成本远低于全表扫描，因此会正确选择索引。
• 不同客户端执行差异的原因：DBeaver默认对大结果集查询自动追加隐式LIMIT限制（默认通常为前1万行），测试时即使清理了查询缓存，也触发了和手动加LIMIT 1一致的优化器逻辑，因此执行速度快；而MariaDB控制台、PHP环境执行的是完整无LIMIT的语句，才会触发全表扫描。

解决建议 #

• 重构count查询逻辑：Laravel DataTables支持自定义总条数统计逻辑，避免使用默认生成的冗余子查询，直接统计paquetes表符合条件的行数即可，不需要关联其他表、查询多余字段或排序，示例代码：

$datatables = DataTables::of($query)
    ->totalRecords(
        Paquete::whereBetween('created_at', [$startTime, $endTime])
            ->where('estado', '!=', 0)
            ->count()
    );

• 添加覆盖索引：针对过滤和关联条件，给paquetes表添加联合覆盖索引，避免回表操作，让优化器优先选择索引执行：

-- 基础过滤索引
CREATE INDEX idx_paquetes_createdat_estado ON paquetes (created_at, estado);
-- 如果关联查询频繁，可使用覆盖索引，直接在索引中获取关联需要的字段，无需回表
CREATE INDEX idx_paquetes_covering ON paquetes (created_at, estado, tipo, ciudadentrega);

• 更新表统计信息：如果添加索引后仍偶尔出现全表扫描的情况，手动更新表统计信息，优化优化器的行数预估准确性：

ANALYZE TABLE paquetes;

• 临时兼容方案：如果暂时无法修改DataTables的生成逻辑，可以在子查询末尾添加足够大的LIMIT值（比如LIMIT 10000000），只要大于业务中可能的最大统计行数，就不会影响count结果，同时可以让优化器始终选择走索引。

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