如何高效匹配通话记录的主叫归属国，并根据通话日期获取对应生效的附加费率

看起来你现在遇到的核心问题是两个：一是原来靠双层循环匹配手机号段找归属国+费率的方式不够高效，二是现在要根据通话日期匹配对应生效的费率，还要避免每条CDR单独查库拖慢速度。我来给你梳理下优化的思路和具体实现方案：

一、先确认并优化数据库表结构 #

你想到给费率表添加price_effective_from和price_effective_to字段的思路完全正确，补充几个细节：

• price_effective_to可以设为NULL，表示该费率当前持续生效（无截止日期）
• 给surcharge_rates表添加联合索引：client_id + phonecode_id + price_effective_from，这能大幅提升后续日期匹配的查询速度
• phonecodes表给phonecode字段加索引，优化前缀匹配的性能

二、核心优化：用SQL关联查询替代双层循环（彻底解决低效问题） #

原来的PHP双层循环匹配逻辑，在CDR数量较多时会产生巨大的性能开销。最好的方式是把归属国匹配、费率日期匹配的逻辑全部放到数据库层面，直接查询出带归属国和对应费率的CDR数据，避免PHP的循环开销。

Laravel 查询构造器实现示例 #

use Carbon\Carbon;

$cdrs = DB::connection('db2')
    ->table('cdr')
    // 子查询：先按手机号段长度倒序，确保长前缀优先匹配（比如3538会比353先匹配，避免短前缀误判）
    ->leftJoinSub(
        DB::table('phonecodes')
            ->select('id', 'phonecode', 'nicename')
            ->orderByRaw('LENGTH(phonecode) DESC'),
        'matched_phonecodes',
        function ($join) {
            // 匹配主叫号码的前缀与手机号段
            $join->whereRaw('LEFT(cdr.src, LENGTH(matched_phonecodes.phonecode)) = matched_phonecodes.phonecode');
        }
    )
    // 关联费率表，匹配当前客户+生效日期的费率
    ->leftJoin('surcharge_rates', function ($join) use ($clientID) {
        $join->on('matched_phonecodes.id', '=', 'surcharge_rates.phonecode_id')
             ->where('surcharge_rates.client_id', '=', $clientID)
             // 匹配费率生效区间：通话日期在生效起始日之后，且在截止日之前（无截止日则匹配）
             ->where(function ($dateQuery) {
                 $dateQuery->whereNull('surcharge_rates.price_effective_to')
                           ->orWhereDate('cdr.calldate', '<=', 'surcharge_rates.price_effective_to');
             })
             ->whereDate('cdr.calldate', '>=', 'surcharge_rates.price_effective_from');
    })
    // 处理无匹配的情况，给默认值
    ->select(
        'cdr.*',
        DB::raw('COALESCE(matched_phonecodes.nicename, "Unknown Country") as country'),
        DB::raw('COALESCE(matched_phonecodes.phonecode, "Unknown Phonecode") as phonecode'),
        DB::raw('COALESCE(surcharge_rates.rate, 0) as rate')
    )
    // 过滤用户指定的日期范围
    ->whereDate('cdr.calldate', '>=', $filters['start_date'])
    ->whereDate('cdr.calldate', '<=', $filters['end_date'])
    // 确保每个CDR只取第一个匹配的归属国（避免多段匹配重复数据）
    ->groupBy('cdr.id')
    ->get();

这个方案的优势：

• 把原来PHP的双层循环逻辑全部交给数据库处理，性能提升非常明显（尤其是CDR数量上千/万级时）
• 直接在关联时匹配对应日期的费率，不需要后续二次处理
• 自动处理了长前缀优先匹配的规则，避免短前缀误判

三、如果必须用PHP处理的备选方案（比如有特殊业务逻辑） #

如果因为某些限制无法用SQL关联查询，可以提前把所有手机号段+对应费率（带生效日期）查询到PHP中，整理成结构化数组，再循环匹配CDR，全程只需要2次数据库查询（CDR+手机号段费率），避免每条CDR单独查库。

实现示例 #

// 1. 提前查询所有手机号段+对应费率，按手机号段分组
$phonecodeRates = DB::table('phonecodes')
    ->select('phonecode', 'nicename', 'surcharge_rates.rate', 'surcharge_rates.price_effective_from', 'surcharge_rates.price_effective_to')
    ->join('surcharge_rates', 'phonecodes.id', '=', 'surcharge_rates.phonecode_id')
    ->where('surcharge_rates.client_id', '=', $clientID)
    ->orderByRaw('LENGTH(phonecode) DESC')
    ->get()
    ->groupBy('phonecode');

// 2. 整理成易查询的数组结构
$phonecodeMap = [];
foreach ($phonecodeRates as $code => $items) {
    $firstItem = $items->first();
    $phonecodeMap[$code] = [
        'nicename' => $firstItem->nicename,
        // 把无截止日期的费率替换为远未来日期，方便后续日期比较
        'rates' => $items->map(function ($item) {
            return [
                'rate' => $item->rate,
                'from' => $item->price_effective_from,
                'to' => $item->price_effective_to ?? '9999-12-31'
            ];
        })->sortByDesc('from')->values() // 按生效日期倒序，优先匹配最新生效的费率
    ];
}

// 3. 查询CDR数据
$cdrs = DB::connection('db2')
    ->table('cdr')
    ->whereDate('calldate', '>=', $filters['start_date'])
    ->whereDate('calldate', '<=', $filters['end_date'])
    ->get();

// 4. 循环匹配归属国和费率
foreach ($cdrs as $cdr) {
    $matched = false;
    $callDateStr = Carbon::parse($cdr->calldate)->toDateString();

    // 遍历手机号段（已按长度倒序，长前缀优先）
    foreach ($phonecodeMap as $code => $data) {
        if (str_starts_with($cdr->src, $code)) { // PHP 8+ 原生前缀匹配函数，比substr更直观
            $cdr->country = $data['nicename'];
            $cdr->phonecode = $code;
            // 匹配对应日期的费率
            foreach ($data['rates'] as $rateItem) {
                if ($callDateStr >= $rateItem['from'] && $callDateStr <= $rateItem['to']) {
                    $cdr->rate = $rateItem['rate'];
                    break;
                }
            }
            $matched = true;
            break;
        }
    }

    // 处理无匹配的情况
    if (!$matched) {
        $cdr->country = 'Unknown Country';
        $cdr->phonecode = 'Unknown Phonecode';
        $cdr->rate = 0;
    }
}

额外性能优化建议 #

1. 给cdr表的calldate字段添加索引，加速日期范围查询
2. 给surcharge_rates表添加联合索引：client_id + phonecode_id + price_effective_from
3. 给phonecodes表的phonecode字段添加索引，加速前缀匹配查询

内容来源于stack exchange