我注意到一个查询在大多数情况下似乎花费了太长时间,所以我使用 Laravel 查询记录器(从查询构建器获取时间和实际 SQL)对其进行了调查。
摘抄:
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::findAllByNameOrAlias('Frankfurt University of Applied Sciences'); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where (`name` = ? or exists (select * from `company_aliases` where `companies`.`id` = `company_aliases`.`company_id` and `name` = ?)) and `companies`.`deleted_at` is null",
"bindings" => [
"Frankfurt University of Applied Sciences",
"Frankfurt University of Applied Sciences",
],
"time" => 278.46,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::findAllByNameOrAlias('Frankfurt University of Applied Sciences'); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where (`name` = ? or exists (select * from `company_aliases` where `companies`.`id` = `company_aliases`.`company_id` and `name` = ?)) and `companies`.`deleted_at` is null",
"bindings" => [
"Frankfurt University of Applied Sciences",
"Frankfurt University of Applied Sciences",
],
"time" => 0.72,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::findAllByNameOrAlias('Frankfurt University of Applied Sciences'); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where (`name` = ? or exists (select * from `company_aliases` where `companies`.`id` = `company_aliases`.`company_id` and `name` = ?)) and `companies`.`deleted_at` is null",
"bindings" => [
"Frankfurt University of Applied Sciences",
"Frankfurt University of Applied Sciences",
],
"time" => 0.67,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::findAllByNameOrAlias('Frankfurt University of Applied Sciences'); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where (`name` = ? or exists (select * from `company_aliases` where `companies`.`id` = `company_aliases`.`company_id` and `name` = ?)) and `companies`.`deleted_at` is null",
"bindings" => [
"Frankfurt University of Applied Sciences",
"Frankfurt University of Applied Sciences",
],
"time" => 298.88,
],
]
注意查询时间。
用例:我正在按名称查找公司,但我们希望能够指定标识同一公司的别名。该companies
表具有名称索引,并且该company_aliases
表也具有名称索引。company_aliases.company_id
有一个外键约束引用companies.id
.
上面的樱桃:桌子company_aliases
是空的。解释上述查询:
ID | 选择类型 | 桌子 | 类型 | 可能的键 | 钥匙 | key_len | 参考 | 行 | 额外的 |
---|---|---|---|---|---|---|---|---|---|
1 | 基本的 | 公司 | 全部 | 公司名称索引 | 无效的 | 无效的 | 无效的 | 241068 | 使用哪里 |
2 | 物化 | 公司别名 | 参考 | company_aliases_company_id_foreign,company_aliases_name_index | company_aliases_name_index | 1022 | 常量 | 1 | 使用索引条件 |
省略company_aliases
表格并companies
仅搜索,始终提供低于 1 毫秒的运行时间。
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.84,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.95,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.83,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.73,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.6,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.9,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.86,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.64,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.53,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.47,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.55,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.53,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.66,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.79,
],
]
>>> DB::flushQueryLog(); DB::enableQueryLog(); Company::where('name', 'Frankfurt University of Applied Sciences')->first(); DB::getQueryLog()
=> [
[
"query" => "select * from `companies` where `name` = ? and `companies`.`deleted_at` is null limit 1",
"bindings" => [
"Frankfurt University of Applied Sciences",
],
"time" => 0.84,
],
]
搜索 in与搜索 incompany_aliases
一样快companies
,因此它是一种可行的解决方法来查询companies
,如果未找到结果,请查询company_aliases
。总查询时间仍低于 2 毫秒。本质上,我只是将惰性求值OR
转移到我的 PHP 代码中。但这不应该比完全在数据库中执行相同的逻辑更快。
什么原因?