Laravel 数据库性能优化实战（六）：通过子查询和联合查询结合关联模型进行模糊匹配

在上篇教程中，学院君给大家演示了如何基于数据库索引优化形如 %XXX 和 XXX% 的模糊匹配，今天我们来看一些更复杂的模糊匹配的例子 —— 在关联查询中应用模糊匹配。

基于子查询结合关联模型进行模糊匹配

比如我们现在在用户搜索页面搜索框输入关键词后，希望能够加入对公司名称的模糊匹配，而公司名称字段是位于关联模型 Company 中的，要实现这样的功能，可以这样改写模糊匹配实现代码：

if ($term = request()->get('keyword')) {
    $query->where('name', 'like', $term . '%')
    ->orWhere('virtual_name_reverse', 'like', $term . '%')
    ->orWhereHas('company', function ($query) use ($term) {
        $query->where('name', 'like', $term . '%');
    });
}

这里我们调用 orWhereHas 方法使用查询存在的关联关系语法通过一个 EXISTS 子查询实现基于关联模型字段对 User 模型实例的筛选。

注：目前数据库中的公司名称格式都是类似 良诺信息有限公司 这种，所以通过 name like XXX% 就可以匹配到了，而我们在创建数据表时已经将 name 字段设置了索引，所以不需要做额外优化了，当然如果你想要实现更加通用的模糊匹配，可以在公司名称上设置函数索引或者虚拟生成列索引来实现对 %XXX 的匹配，这里我们不再演示了，和上篇教程用户名字段一样设置就好了。

现在在用户搜索页面就可以通过公司名称筛选用户了：

在对应的 SQL 查询语句中可以看到对应的 EXIST 子查询语句，如果子查询条件为真，则返回对应的用户记录。我们通过 explain 查看对应的执行计划：

可以看到虽然子查询可以使用索引，但是由于外部主查询需要进行全表扫描，整体的执行效率是相对较低的。

基于联合查询结合关联模型进行模糊匹配

我们还可以通过联合查询实现上述功能：

SELECT `id`, `name`, `email`, `company_id`, `created_at` FROM users WHERE `name` LIKE '强%' or `virtual_name_reverse` like '强%'

UNION

SELECT users.`id`, users.`name`, `email`, `company_id`, users.`created_at` FROM users INNER JOIN companies ON companies.id = users.company_id AND companies.`name` like '强%';

由于要去重，所以这里使用了 UNION 关键字，为了将其转化为 Eloquent 联合查询代码，可以将第二个连接查询转化为 IN 子查询：

SELECT `id`, `name`, `email`, `company_id`, `created_at` FROM users WHERE `company_id` IN (SELECT id FROM companies WHERE companies.`name` like '强%');

我们知道，公司和用户模型之间是一对多关联，用户表中有一个 company_id 字段用于实现这种一对多的关联映射，因此我们可以将上面的实现转化为先通过公司名模糊匹配拿到公司 ID，再通过 IN 子查询（公司 ID）作为筛选条件筛选出最终的用户记录。

将 UserController 中的 Eloquent 模型关联匹配实现代码重构为联合查询如下：

public function search()
{
    $columns = ['id', 'name', 'email', 'company_id', 'created_at'];
    $query = User::select($columns);
    if ($term = request()->get('keyword')) {
        $first = $query->where('name', 'like', $term . '%')
            ->orWhere('virtual_name_reverse', 'like', $term . '%');

        $second = User::select($columns)->whereIn('company_id', function ($query) use ($term) {
            $query->select('id')->from('companies')
                ->where('name', 'like', $term . '%');
        });

        $query = $first->union($second);
    }
    $users = $query->with('company')->orderByDesc('id')->paginate(20);
    return view('user.search', ['users' => $users, 'term' => $term]);
}

搜索结果和前面一样：

并且由于联合查询的两个 SQL 子查询都可以使用索引，所以效率比前面 EXISTS 子查询高很多：

注：执行计划的第一行全表扫描是针对联合查询结果对应的 temp_table 临时表的，这个临时表不大，所以即便是全表扫描也很快。

通过用户名和公司名分别进行模糊匹配

不过一个更精确的搜索需求应该是分别对用户名和公司名进行模糊匹配，而不是笼统的混在一起，比如我们在电商网站商品搜索页面或者管理后台业务报表查询界面经常看到的根据不同维度和属性进行搜索的场景就是这样。

这里我们简化下 UI，通过空格来分隔用户名和公司名，然后结合这两个模糊匹配条件对用户记录进行筛选：

if ($term = request()->get('keyword')) {
    list($uname, $cname) = explode(' ', $term);
    $query->whereRaw('(name like ? or virtual_name_reverse like ?)', [$uname . '%', $uname . '%'])
        ->whereHas('company', function ($query) use ($cname) {
            $query->where('name', 'like', $cname . '%');
        });
}

注意：name like ? or virtual_name_reverse like ? 需要通过括号括起来作为一个整体 where 条件，否则和 exists 子句通过 and 连接起来在筛选记录时会出现逻辑问题。

现在的查询效率也得到了大幅提升：

因为子查询和主查询之间的逻辑关系变成了 and，也就不需要对外层主查询进行全表扫描了，转而可以基于 users.name 字段上设置的索引进行优化：

不过用户输入的关键词空格数量可能不可控，多输入一个空格这段代码就失效了，要提高代码的鲁棒性，你可以通过正则表达式对用户名和公司名进行匹配：

$query = User::with('company')->select($columns);
if ($term = request()->get('keyword')) {
    preg_match('/([\x{4e00}-\x{9fa5}]+)\s+([\x{4e00}-\x{9fa5}]+)/u', $term, $matches);
    $uname = $matches[1];
    $cname = $matches[2];
    $query->whereRaw('(name like ? or virtual_name_reverse like ?)', [$uname . '%', $uname . '%'])
        ->whereHas('company', function ($query) use ($cname) {
            $query->where('name', 'like', $cname . '%');
        });
}
$users = $query->orderByDesc('id')->paginate(20);

转化为 IN 子查询进行关联匹配

当然，我们也可以将上面的实现转化为 IN 子查询来实现：

SELECT
	count(*) AS aggregate
FROM
	`users`
WHERE (`name` LIKE '强%'
	OR `virtual_name_reverse` LIKE '强%')
AND company_id IN(
	SELECT
		id FROM `companies`
	WHERE
		`name` LIKE '迪摩%');

由于这也是个子查询，并且在查询时相应字段都可以命中索引，所以查询性能和上面的 EXISTS 子查询相当。

对应的 Eloquent 关联查询实现代码如下：

if ($term = request()->get('keyword')) {
    preg_match('/([\x{4e00}-\x{9fa5}]+)\s+([\x{4e00}-\x{9fa5}]+)/u', $term, $matches);
    $uname = $matches[1];
    $cname = $matches[2];
    $query->whereRaw('(name like ? or virtual_name_reverse like ?)', [$uname . '%', $uname . '%'])
    ->whereIn('company_id', function ($query) use ($cname) {
        $query->select('id')->from('companies')->where('name', 'like', $cname . '%');
    });
}

搜索结果和之前一样：

当然，也可以通过 Company 模型类实现这个子查询：

$query->whereRaw('(name like ? or virtual_name_reverse like ?)', [$uname . '%', $uname . '%'])
    ->whereIn('company_id',
        Company::select(['id'])
            ->where('name', 'like', $cname . '%')
            ->pluck('id')
    );

不过这样一来会新增一个 SQL 查询操作，将原来的 IN 子查询分拆成两步来实现：