我注意到一个查询在大多数情况下似乎花费了太长时间,所以我使用 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 代码中。但这不应该比完全在数据库中执行相同的逻辑更快。
什么原因?
“在 company_aliases 中的搜索与在公司中的搜索一样快,因此查询公司是一种可行的解决方法,如果未找到结果,则查询 company_aliases。总查询时间仍低于 2 毫秒。本质上,我只是在移动对我的 PHP 代码中的 OR 进行延迟评估。但这不应该比完全在数据库中执行相同的逻辑更快。这不是同一个逻辑。查询是说给我那些
companies
与这个值同名的行或者可以在company_aliases
这个值中找到的行。没有什么可以告诉它在找到匹配项时停止。OR
您可以手动扩展条件:这应该给你一个计划,它使用一个索引
companies.name
来满足第一部分,它应该使用一个索引,company_aliases.name
然后是一个循环来companies
基于id
第二部分。假设这些索引存在,你应该没问题。