当前判断Rates表查询结果为空的实现方案是否为最高效方式？

现有方案的问题 #

• 最坏场景下会产生10次数据库查询请求，多次网络IO+SQL解析执行的开销会导致性能至少下降数倍，高并发场景下数据库压力会陡增。
• 大量重复的查询条件导致代码冗余，后续调整业务优先级或通用规则需要修改多处，维护成本很高。
• 直接拼接变量到SQL语句中存在SQL注入风险，属于安全隐患。

最优优化方案：单查询+应用层优先级匹配 #

你的业务逻辑本质是有明确的匹配优先级降级规则，完全可以一次查询出所有符合基础条件的记录，再在应用层按优先级筛选即可，性能提升最明显。

核心思路 #

先把你现有的降级顺序抽象为优先级评分规则，在SQL中用CASE语句给每条匹配的记录打优先级分数，查询后取分数最高的一组记录即可，仅需1次数据库请求。

示例代码 #

// 基础条件始终一致，提前定义
$baseCondition = "status_active = 'active' AND CAST(? AS time) BETWEEN book_from AND book_by";
$params = [$order_placed_time];

$whereOr = [];
// 第一优先级：位置精确匹配
$whereOr[] = "(master_account = ? AND branch_id = ? AND location_from = ? AND location_to = ?)";
array_push($params, $account, $branch, $location_from_id, $location_to_id);
$whereOr[] = "(master_account = ? AND branch_id = '-1' AND location_from = ? AND location_to = ?)";
array_push($params, $account, $location_from_id, $location_to_id);

// 第二优先级：区域匹配（仅当区域参数非空时加入）
if ($region_from_id != '' && $region_to_id != '') {
    $regionScenarios = [
        ['master' => $account, 'branch' => $branch, 'restricted' => 1, 'restrict_loc' => $location_from_id],
        ['master' => $account, 'branch' => $branch, 'restricted' => 2, 'restrict_loc' => $location_to_id],
        ['master' => $account, 'branch' => $branch, 'restricted' => 0, 'restrict_loc' => null],
        ['master' => $account, 'branch' => '-1', 'restricted' => 1, 'restrict_loc' => $location_from_id],
        ['master' => $account, 'branch' => '-1', 'restricted' => 2, 'restrict_loc' => $location_to_id],
        ['master' => $account, 'branch' => '-1', 'restricted' => 0, 'restrict_loc' => null],
        ['master' => '-1', 'branch' => '-1', 'restricted' => 1, 'restrict_loc' => $location_from_id],
        ['master' => '-1', 'branch' => '-1', 'restricted' => 2, 'restrict_loc' => $location_to_id],
        ['master' => '-1', 'branch' => '-1', 'restricted' => 0, 'restrict_loc' => null],
    ];
    foreach ($regionScenarios as $idx => $scenario) {
        if ($scenario['restricted'] == 0) {
            $cond = "(master_account = ? AND branch_id = ? AND region_from = ? AND region_to = ? AND restricted = 0)";
            array_push($params, $scenario['master'], $scenario['branch'], $region_from_id, $region_to_id);
        } else {
            $cond = "(master_account = ? AND branch_id = ? AND region_from = ? AND region_to = ? AND restricted = ? AND restricted_location_id = ?)";
            array_push($params, $scenario['master'], $scenario['branch'], $region_from_id, $region_to_id, $scenario['restricted'], $scenario['restrict_loc']);
        }
        $whereOr[] = $cond;
    }
}

// 拼接完整SQL，加入优先级排序
$fullCondition = $baseCondition . " AND (" . implode(" OR ", $whereOr) . ")";
$priorityCase = "CASE ";
// 按优先级顺序给每个场景打分，分值越高优先级越高
$priorityScore = count($whereOr);
foreach ($whereOr as $cond) {
    $priorityCase .= "WHEN $cond THEN $priorityScore ";
    $priorityScore--;
}
$priorityCase .= "END AS priority";

// 执行查询，按优先级倒序，取最高优先级的所有记录
$rates = Rates::find()
    ->select(["*", $priorityCase])
    ->where($fullCondition, $params)
    ->orderBy("priority DESC")
    ->all();

// 过滤出最高优先级的记录
if (!empty($rates)) {
    $maxPriority = $rates[0]->priority;
    $rates = array_filter($rates, fn($rate) => $rate->priority == $maxPriority);
}

这个方案同时解决了性能、代码维护、SQL注入三个问题，是最优选择。

最低成本优化方案（不改变逐次查询逻辑） #

如果暂时不想改核心逻辑，也可以做如下优化，性能和可维护性也能有明显提升：

• 把所有查询场景按优先级存入数组，循环执行直到查到非空结果，避免重复写if(empty)判断
• 所有查询使用参数绑定，避免SQL注入
• 给常用查询条件加联合索引，比如(status_active, master_account, branch_id, location_from, location_to)、(status_active, master_account, branch_id, region_from, region_to, restricted)，查询速度能提升数倍。

示例代码 #

// 按优先级定义所有查询场景
$scenarios = [];
// 位置匹配场景
$scenarios[] = [
    'master' => $account,
    'branch' => $branch,
    'type' => 'location',
    'loc_from' => $location_from_id,
    'loc_to' => $location_to_id
];
$scenarios[] = [
    'master' => $account,
    'branch' => '-1',
    'type' => 'location',
    'loc_from' => $location_from_id,
    'loc_to' => $location_to_id
];
// 区域匹配场景
if ($region_from_id != '' && $region_to_id != '') {
    $scenarios[] = ['master' => $account, 'branch' => $branch, 'type' => 'region', 'restricted' => 1, 'restrict_loc' => $location_from_id];
    $scenarios[] = ['master' => $account, 'branch' => $branch, 'type' => 'region', 'restricted' => 2, 'restrict_loc' => $location_to_id];
    $scenarios[] = ['master' => $account, 'branch' => $branch, 'type' => 'region', 'restricted' => 0];
    $scenarios[] = ['master' => $account, 'branch' => '-1', 'type' => 'region', 'restricted' => 1, 'restrict_loc' => $location_from_id];
    $scenarios[] = ['master' => $account, 'branch' => '-1', 'type' => 'region', 'restricted' => 2, 'restrict_loc' => $location_to_id];
    $scenarios[] = ['master' => $account, 'branch' => '-1', 'type' => 'region', 'restricted' => 0];
    $scenarios[] = ['master' => '-1', 'branch' => '-1', 'type' => 'region', 'restricted' => 1, 'restrict_loc' => $location_from_id];
    $scenarios[] = ['master' => '-1', 'branch' => '-1', 'type' => 'region', 'restricted' => 2, 'restrict_loc' => $location_to_id];
    $scenarios[] = ['master' => '-1', 'branch' => '-1', 'type' => 'region', 'restricted' => 0];
}

$rates = [];
foreach ($scenarios as $s) {
    $condition = "status_active = 'active' AND master_account = ? AND branch_id = ? AND CAST(? AS time) BETWEEN book_from AND book_by";
    $params = [$s['master'], $s['branch'], $order_placed_time];
    if ($s['type'] == 'location') {
        $condition .= " AND location_from = ? AND location_to = ?";
        array_push($params, $s['loc_from'], $s['loc_to']);
    } else {
        $condition .= " AND region_from = ? AND region_to = ? AND restricted = ?";
        array_push($params, $region_from_id, $region_to_id, $s['restricted']);
        if ($s['restricted'] != 0) {
            $condition .= " AND restricted_location_id = ?";
            $params[] = $s['restrict_loc'];
        }
    }
    $rates = Rates::find()->where($condition, $params)->all();
    if (!empty($rates)) break;
}

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