时间戳

首页 > 解决方案 > 如何优化过滤系统中多个“LIKE”搜索中的慢 MySQL 查询？

php - 如何优化过滤系统中多个“LIKE”搜索中的慢 MySQL 查询？

问题描述

我在这里读了很多文章。有很多类似的问题，但我找不到任何一个适合我的情况。请原谅我，因为我是一个新的网站开发人员，而且我的代码很丑陋。

我使用 Laravel 8 和 MySQL InnoDB 来处理我的数据库。我的网站是一个多语言的视频信息网站。这是我的表结构。

我的表：

视频

视频流派

视频预览

有10组流派（c1-c10），每组大约有100个流派。即使它们在不同的组中，每种类型都有一个唯一的 ID。在 中video table，我将视频流派 id 存储为68#10#185#237#89#340#156. 然后我可以使用explode“#”将流派ID数据返回到数组中。然后我可以genre table使用这些 id 加入。

过滤系统是网站的核心功能。当人们选择多种类型时，他们可以缩小结果范围并准确地得到他们想要的。我使用 GET 方法将 url 请求传递给 Laravel 中的 VideoController，例如example.com/?c1=68,10&c2=185,237&c7=89,340,156

以下是搜索步骤：

  1. 我放入 /?c1=8&c2=33&c7=81数组$cArr[68,10,185,237,89,340,156]
  2. 然后$cArr在带有多个 LIKE 操作的查询中使用
$data = cache()->remember($_SERVER['REQUEST_URI'], 60*60*24, function() use($cArr){
                    return DB::table('videos')
                            ->Where(function ($query) use($cArr) {
                                for ($i = 0; $i < count($cArr); $i++){
                                    $query->where('genre_id', 'like',  $cArr[$i] .'#%');
                                }})
                            ->orWhere(function ($query) use($cArr) {
                                for ($i = 0; $i < count($cArr); $i++){
                                    $query->where('genre_id', 'like', '%#' . $cArr[$i]);
                                }})
                            ->orWhere(function ($query) use($cArr) {
                                for ($i = 0; $i < count($cArr); $i++){
                                    $query->where('genre_id', 'like', '%#' . $cArr[$i] .'#%');
                                }})
                            ->leftjoin('videos_preview','videos_preview.code','=','videos.code')
                            ->orderBy('videos.publish_date', 'DESC')
                            ->limit(400)->get();

这将生成一个慢速查询，如下所示。搜索 300K 行大约需要 10 秒。

select * from `videos` left join `videos_preview` on `videos_preview`.`code` = `videos`.`code` 
where (`genre_id` like '68#%' and `genre_id` like '10#%' and `genre_id` like '185#%' and `genre_id` like '237#%' and `genre_id` like '89#%' and `genre_id` like '340#%' and `genre_id` like '156#%') 
or (`genre_id` like '%#68' and `genre_id` like '%#10' and `genre_id` like '%#185' and `genre_id` like '%#237' and `genre_id` like '%#89' and `genre_id` like '%#340' and `genre_id` like '%#156') 
or (`genre_id` like '%#68#%' and `genre_id` like '%#10#%' and `genre_id` like '%#185#%' and `genre_id` like '%#237#%' and `genre_id` like '%#89#%' and `genre_id` like '%#340#%' and `genre_id` like '%#156#%') order by `videos.publish_date` desc limit 400;

我有一个 6GB Ram 和 6CPU 内核的 VPS。但随着最近流量的增加（大约同时有 500 名访问者），数据库每天增长 300 多行。我刚刚发现 MySQL 查询将我的 CPU 消耗到 100%。如您所见，我已经将结果缓存了 24 小时，但是多种类型的组合太多了。大多数组合是在 24 小时内首次出现，未缓存。

请帮我。有没有更好的方法在 Laravel 8 中以更好的方式归档相同的过滤器功能？提前感谢您让每一个生命更安全。对不起我的英语不好。

标签： phpmysqllaraveleloquentquery-builder

解决方案

  • AND 和 OR 非常混乱。重新思考。
  • LIKE '%...'必须检查每一行
  • OR必须检查每一行

这将不得不检查每一行，但它会更快：

WHERE FIND_IN_SET(genre, '68,10,185,237,89,340,156')

请注意，逗号是必需的。这将检查流派是这些数字之一。您想将用户提供的类型测试 AND 在一起吗？或者他们？

-- Both 185 and 10:
WHERE FIND_IN_SET(185, '68,10,185,237,89,340,156')
  AND FIND_IN_SET( 10, '68,10,185,237,89,340,156')

-- Both 185 or 10:
WHERE FIND_IN_SET(185, '68,10,185,237,89,340,156')
   OR FIND_IN_SET( 10, '68,10,185,237,89,340,156')

另一种方法是使用 FULLTEXT 索引：

-- The column `genre` might be "mystery drama documentary comedy"
WHERE MATCH(`genre`) AGAINST ("+comedy +musical" IN BOOLEAN MODE)

这会运行得更快，因为它会创建单词的逆索引。反对者说它必须既是喜剧又是音乐剧。（因此该样本genre不匹配。）

推荐阅读