Query-Performance

使用 EXISTS 子查詢的查詢在 80% 的情況下大約需要 280 毫秒，在 20% 的情況下少於 1 毫秒

  • May 9, 2022

我注意到一個查詢在大多數情況下似乎花費了太長時間，所以我使用 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是空的。解釋上述查詢：

省略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您可以手動擴展條件：

select * 
from  `companies` 
where  `name` = ? 
and    `companies`.`deleted_at` is null
union all
select * 
from  `companies` 
where  exists (select null 
              from   `company_aliases` 
              where  `companies`.`id` = `company_aliases`.`company_id` 
              and    `name` = ?
             )
and    `companies`.`deleted_at` is null
and   ( `name` <> ? or `name` is null)

這應該給你一個計劃，它使用一個索引companies.name來滿足第一部分，它應該使用一個索引，company_aliases.name然後是一個循環來companies基於id第二部分。假設這些索引存在，你應該沒問題。

引用自：https://dba.stackexchange.com/questions/311883

相關問答

Performance

如何在純 SQL 中過濾連續的時間線事件？

  • June 12, 2018
檢視問答
Sql-Server

TSQL 慢查詢，未按預期使用索引

  • September 13, 2022
檢視問答
Postgresql

並行和更快地評估 SELECTing 布爾比較計數

  • September 10, 2022
檢視問答
Sql-Server

為什麼對索引列的不等式搜尋會給出恆定掃描

  • September 7, 2022
檢視問答
Postgresql

pgAdmin 是否增加了查詢時間成本？

  • September 6, 2022
檢視問答
Sql-Server

Select * Order by vs Select column 按性能排序

  • September 3, 2022
檢視問答