我正在構建一個基于 Laravel 作為 API 的“廣告”網站，其中包含動態高級搜索。
廣告可以有多個屬性，用戶可以進行高級搜索，但是如果用戶檢查了這么多屬性進行過濾并且查詢變得緩慢，會導致很多子查詢，關于比這個更好的查詢的任何建議。
select
*
from
`ads`
where
`ads`.`id` in (
select
`ad_attributes`.`ad_id`
from
`ad_attributes`
where
`attribute_id` = 32
and `value` in ('odio', 'quos', 'dignissimos', 'dolorem', 'qui')
)
and `ads`.`id` in (
select
`ad_attributes`.`ad_id`
from
`ad_attributes`
where
`attribute_id` = 171
and `value` in ('itaque', 'non', 'dolor', 'laborum')
)
and `ads`.`id` in (
select
`ad_attributes`.`ad_id`
from
`ad_attributes`
where
`attribute_id` = 111
and `value` in ('quia', 'non', 'nam', 'molestias')
)
and `ads`.`id` in (
select
`ad_attributes`.`ad_id`
from
`ad_attributes`
where
`attribute_id` = 144
and `value` in ('delectus', 'nam', 'exercitationem', 'sit')
)
and `ads`.`id` in (
select
`ad_attributes`.`ad_id`
from
`ad_attributes`
where
`attribute_id` = 160
and `value` in ('repellat', 'fugit', 'quaerat', 'vero')
)
and `ads`.`id` in (
select
`ad_attributes`.`ad_id`
from
`ad_attributes`
where
`attribute_id` = 176
and `value` in ('mollitia', 'voluptates', 'maxime', 'culpa')
)
and `ads`.`id` in (
select
`ad_attributes`.`ad_id`
from
`ad_attributes`
where
`attribute_id` = 177
and `value` in ('necessitatibus', 'id')
)
這是搜索的代碼，PS。我使用的是 mpyw/eloquent-has-by-non-dependent-subquery 而不是使用 whereHas 因為它更慢......
->when(!empty($search->attrs), function (Builder $query) use ($search) {
foreach ($search->attrs as $key => $value) {
if (!is_null($value)) {
$query->hasByNonDependentSubquery('adAttributes', function (Builder $q) use ($value, $key, $search) {
$q->where('attribute_id', $key)
->when(is_array($value), fn($q) => $q->whereIn('value', $value))
->when(!is_array($value), fn($q) => $q->where('value', $value));
});
}
}
return $query;
});
我在我的資料庫中添加了一些資料，我無法分享所有這些資料，因為它的虛擬種子資料在 Fiddle [https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=6f00cc71fff716837906c22d46b0c899][1]
uj5u.com熱心網友回復：
Entity-Attribute-Value 模式模式是出了名的冗長和低效。但它是靈活的。
它可以幫助這個“復合”指數（即還“覆寫”）添加到表ad_attributes：
INDEX(attribute_id, value, ad_attributes)
uj5u.com熱心網友回復：
有沒有理由不只是加入表格并使用where()？
$query = Ads::select('ads.*')->join('ads_attributes', 'ads.id', 'ads_attributes.ad_id');
if (!empty($search->attrs) {
foreach ($search->attrs as $k=>$v) {
$q->orWhere(function (Builder $q) (use $k, $v) {
$q->where('ads_attributes.id', $k)
->when(
is_array($v),
fn ($q) => $q->whereIn('value', $v),
fn ($q) => $q->where('value', $v),
);
});
}
}
return $query;
uj5u.com熱心網友回復：
這可能絕對沒用，而且性能非常糟糕，但我依稀記得多年前在與 EAV 性能問題作斗爭時使用了這些方法。我沒有合適的測驗資料集來對此進行測驗，所以我提出這個建議可能是在自欺欺人。
select
*
from
`ads`
where
`ads`.`id` in (
select
`ad_attributes`.`ad_id`
from
`ad_attributes`
where
(`attribute_id` = 32 and `value` in ('odio', 'quos', 'dignissimos', 'dolorem', 'qui')) OR
(`attribute_id` = 171 and `value` in ('itaque', 'non', 'dolor', 'laborum')) OR
(`attribute_id` = 111 and `value` in ('quia', 'non', 'nam', 'molestias')) OR
(`attribute_id` = 144 and `value` in ('delectus', 'nam', 'exercitationem', 'sit')) OR
(`attribute_id` = 160 and `value` in ('repellat', 'fugit', 'quaerat', 'vero')) OR
(`attribute_id` = 176 and `value` in ('mollitia', 'voluptates', 'maxime', 'culpa')) OR
(`attribute_id` = 177 and `value` in ('necessitatibus', 'id'))
group by `ad_attributes`.`ad_id`
having count(`attribute_id`) = 7
)
編輯
正如我在下面的第一條評論中所述 - You may need to COUNT(DISTINCT attribute_id), depending on whether ads can have multiple rows for the same attribute_id。由于您的測驗資料集對于同一個 ad_id、attribute_id 對確實有多行，因此您需要添加DISTINCT.
我已經添加到你原來的SQL Fiddle和你的db<>fiddle。不幸的是 db<>fiddle 不會回傳每個查詢的執行時間。
根據您的兩個小提琴中的表格，目前這些表格上沒有索引。您的 ad_attributes 表上還有一個不必要的代理主鍵，而不是 (ad_id, attribute_id) 或 (ad_id, attribute_id, value) 上的自然鍵。如果您允許/需要每個 ad_id 具有相同 attribute_id 的多行，則需要第二個版本，就像您的測驗資料中目前的情況一樣。這是故意的還是只是您的測驗資料創建方式的錯誤？
在你的 ad_attributes 中的 776 個測驗行中，有 106 組（ad_id、attribute_id、attribute_option_id、value）有兩個或多個副本，最壞的有六個副本。我建議您的測驗資料沒有用，因為它不遵循生產資料集中所需的基本規則和約束。使用完全隨機的資料測驗查詢并不是特別有用。您的測驗資料應該嘗試對您可能在生產資料中看到的內容進行建模。
我并不完全相信我理解你的資料結構的意圖。擁有 attribute_option_id 和 value 列背后的想法是什么？文本和選項屬性背后的想法是什么？你真的需要一個文本屬性嗎？仔細考慮最后一個，因為隨著資料集的增長，它有可能對資料質量和性能產生非常顯著的影響。
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/385393.html