如何优化列表查询条件带like查询的业务场景?

数据列表搜索分页,其中有个搜索条件是like查询,在不使用ES的情况下最优的写法是怎么样的?

《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
《G01 Go 实战入门》
从零开始带你一步步开发一个 Go 博客项目,让你在最短的时间内学会使用 Go 进行编码。项目结构很大程度上参考了 Laravel。
讨论数量: 14

来自AI的答案

在 %LIKE% 的情况下提升性能确实是一个挑战,因为这种查询模式通常不能有效利用索引,从而导致全表扫描,降低查询效率。下面是一些建议来提升这种情况下性能的方法:

  1. 使用缓存
    描述:将查询结果缓存起来,当相同的查询再次执行时,直接从缓存中获取结果,而不是再次查询数据库。

适用场景:当查询结果不经常变化,且查询频率较高时,使用缓存是有效的。

注意事项:

需要设置合适的缓存过期时间,以确保数据的实时性。
缓存失效策略需要合理设计,避免缓存雪崩。

  1. 前缀搜索优化
    描述:虽然 %LIKE% 无法利用索引,但如果查询模式允许,可以尝试使用前缀搜索(如 LIKE ‘query%’),这样可以利用索引。

适用场景:当用户查询习惯倾向于使用前缀搜索时。

注意事项:这要求用户的查询模式具有一定的规律性。

  1. 分布式缓存与全文搜索引擎
    描述:使用如 Redis 这样的分布式缓存系统存储热点数据,或使用 Elasticsearch 这样的全文搜索引擎进行高效的文本搜索。

适用场景:适用于大规模数据集和高并发场景。

注意事项:

引入额外的系统会增加复杂性和维护成本。
需要对数据进行同步和更新操作,以保证数据的一致性。

  1. 数据分区与分桶
    描述:将数据按照某种规则进行分区或分桶,每个分区或桶内的数据相对独立,查询时可以减少扫描范围。

适用场景:当数据具有明显的地域、时间等分布特征时。

注意事项:需要对数据模型和查询逻辑进行相应的设计和调整。

使用 MongoDB 进行模糊查询
MongoDB 作为一个 NoSQL 数据库,在处理文本搜索时具有一些优势。它支持正则表达式查询,可以用于实现模糊查询。在某些情况下,MongoDB 的模糊查询性能可能优于 MySQL,尤其是在处理非结构化数据时。

优势:

MongoDB 的查询语言较为灵活,支持正则表达式等高级查询方式。
对于非结构化数据,MongoDB 的查询性能可能更佳。
注意事项:

MongoDB 不适合所有类型的查询,特别是复杂的联接和事务处理。
在使用 MongoDB 之前,需要评估其是否适合你的应用需求和数据模型。
结论
在 %LIKE% 的情况下提升性能需要综合考虑多种因素,包括查询模式、数据规模、系统架构等。使用缓存、优化查询模式、引入全文搜索引擎或考虑使用 MongoDB 等方法都可以在一定程度上提升性能。具体选择哪种方法取决于你的应用场景和需求。

1个月前 评论

常见的搜索可以这样写:

class ItemController extends Controller
{
    public function search(Request $request)
    {
        $query = $request->input('query');
        $page = (int)$request->input('page', 1);
        $perPage = (int)$request->input('per_page', 10);

        // 使用 `where` 和 `orWhere` 方法进行模糊匹配
        $items = Item::query()
            ->where('title', 'LIKE', "%{$query}%")
            ->orWhere('description', 'LIKE', "%{$query}%")
            ->orderBy('id')
            ->paginate($perPage, ['*'], 'page', $page);

        return $items;
    }
}

问题就在于%like% 无论怎么写都几乎是全表扫描,那么是否可以用索引?答案是否,只有LIKE 'query%'的情况下才能命中索引。

那么看起来是否就无解了?不是的。可以考虑在一些热点数据,例如用户数据存入redis的hash表中,然后把需要模糊查询的字段作为key,例如:

// 用户id和name作为key
Redis::set('user:' . $user->id . ':username', $user->name);

但是,又存在另外一个问题,redis本身不支持模糊查询,所以就需要一个个去匹配,如果数据量不大,这样的效率是比mysql高的:

// 封装一个扫描模糊搜索条件的函数
public static function scanKeys($pattern)
    {
        $cursor = 0;
        $keys = [];

        do {
            $result = Redis::scan($cursor, 'match', $pattern, 'count', 1000);
            $cursor = $result[0];
            $keys = array_merge($keys, $result[1]);
        } while ($cursor != 0);

        return $keys;
    }

    // 查询函数
    ```
public function search(Request $request)
{
    $query = $request->input('query');
    $pattern = 'user:*:username:*' . $query . '*';
    $keys = RedisHelper::scanKeys($pattern);

    $users = [];
    foreach ($keys as $key) {
        list($type, $userId, $field) = explode(':', $key);
        if ($field === 'username') {
            $user = User::find($userId);
            if ($user && strpos($user->name, $query) !== false) {
                $users[] = $user;
            }
        }
    }

    return $users;
}

但是的但是,是否还不够优雅和完美,redis在这种复杂数据上的维护更令人头疼。那么mongodb呢?
实际上,在一般的查询效率上,mongodb的查询效率是要优于mysql的,但是模糊查询(使用”like %item%”),单字段索引也会受到影响。

总结:

如果数据量大,目前还是es作为模糊查询的性能最优,如果数据量不大,mysql和Mongodb都是可以了。
然而mongodb还有一种索引叫做文本索引,它会去匹配关键字,但是呢,查询的准确性不能保证。
redis也可以作为一些热点数据的缓存,例如还是hash表,把用户信息缓存,可以大大减少mysql的查询,但是要注意数据一致性。

1个月前 评论

刚好遇到了这个问题,尝试了几种解决方案,如下:

  1. 给表like 字段加了全文索引, 在测试服上是快了点,但是上了生产以后线上效果没有达到预期
  2. 加了redis缓存,但是 正如上面回答的,不支持 redis
  3. 加了 es 速度是 快了好多, 但是 分词不理想, like 出来的结果乱七八糟的.
  4. 最终的解决方案是 拿 PostgreSQL 做了 缓存, 把表的数据 同步了一份到 PostgreSQL,然后 使用 PostgreSQL 的 like 效果 是 6 的一批

你也可以试试: 参考 (github.com/digoal/blog/blob/master...)

1个月前 评论
Smilephp 1个月前
晏南风 1个月前
PFinal南丞 (作者) 1个月前
PFinal南丞 (作者) 1个月前
Smilephp 1个月前
PFinal南丞 (作者) 1个月前
PFinal南丞 (作者) 1个月前

在数据库加全文索引就行了

1个月前 评论
DogLoML

jieba分词(fukuball/jieba-php)+TNTSearch全文检索(vanry/laravel-scout-tntsearch)

1个月前 评论

:+1: +1: 看了各位的答案,估计都需要借助其他技术栈了

1个月前 评论

转换成id 例如用户名查询 改成搜索下拉

3周前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!