不明白為什么mysql查詢這么慢-有解無憂

我有一個非常慢的遺留查詢。我將展示查詢以及之后的背景。查詢大約需要 10 秒，這非常慢。解釋給我：

詢問：

select  staff.id as Id, 
        staff.eid as AccountId, 
        staff.Surname 
from staff 
LEFT JOIN app_roles ON (app_roles.app_staff_id = staff.id )
where staff.eid = 7227 
AND app_roles.application_id = '1' 
and staff.last_modified > '2022-05-11 13:15:21Z'

Staff 表包含 280k 行，app_roles 包含 644k 行。eid 7727 - 87 行的員工行。匹配員工 ID 的 app_roles 行 - 75 行

表結構：

CREATE TABLE `app_roles` (
  `application_id` varchar(40) NOT NULL,
  `app_staff_id` varchar(40) NOT NULL,
  `role` varchar(40) NOT NULL,
  PRIMARY KEY (`application_id`,`app_staff_id`),
  KEY `application_id` (`application_id`),
  KEY `app_staff_id` (`app_staff_id`)
) ENGINE=InnoDB

CREATE TABLE `staff` (
  `eid` int NOT NULL,
  `id` varchar(40) NOT NULL,
  `forename` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `surname` varchar(150) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  last_nodified DATETIME NOT NULL,
  ... columns omitted for simplicity
  PRIMARY KEY (`eid`,`id`),
  KEY `email` (`email`),
  KEY `app_login` (`app_login`),
  KEY `app_passwd` (`app_password`),
  KEY `id` (`id`),
  KEY `eid` (`eid`)
) ENGINE=InnoDB


 ---- ------------- ----------- ------------ -------- ------------------------------------- ---------------- --------- --------------------------------------- -------- ---------- -------------------------- 
| id | select_type | table     | partitions | type   | possible_keys                       | key            | key_len | ref                                   | rows   | filtered | Extra                    |
 ---- ------------- ----------- ------------ -------- ------------------------------------- ---------------- --------- --------------------------------------- -------- ---------- -------------------------- 
|  1 | SIMPLE      | app_roles | NULL       | ref    | PRIMARY,application_id,app_staff_id | application_id | 42      | const                                 | 330114 |   100.00 | Using where; Using index |
|  1 | SIMPLE      | staff     | NULL       | eq_ref | PRIMARY,id,eid                      | PRIMARY        | 126     | const,inventry.app_roles.app_staff_id |      1 |    33.33 | Using where              |
 ---- ------------- ----------- ------------ -------- ------------------------------------- ---------------- --------- --------------------------------------- -------- ---------- --------------------------

我不明白為什么左連接和 where 沒有過濾掉行，以及為什么索引沒有幫助。

uj5u.com熱心網友回復：

我假設您對字符集/排序規則有疑問。確保您加入的欄位匹配。要驗證這一點，請運行：

SHOW FULL COLUMNS FROM staff;
SHOW FULL COLUMNS FROM app_roles;

更具體地說，確保 app_roles.app_staff_id 和 staff.id 是同一型別。

uj5u.com熱心網友回復：

在所有其他條件相同的情況下，MySQL 喜歡通過主鍵查找進行連接。它對此有強烈的偏好，因為主鍵查找比輔助鍵查找更有效。

它甚至可以改變連接的順序來滿足這個偏好。內連接是可交換的，因此優化器可以先訪問其中一個表，然后再連接到另一個表。

但是您使用了 a LEFT [OUTER] JOIN，那么如何像內部連接一樣對其進行優化呢？app_roles.application_id = '1'您在 WHERE 子句中寫了一個條件。如果您在左外連接的右表上測驗非 NULL 值，它會消除任何會使該連接成為外連接的行。它實際上是一個內部連接。因此，優化器可以自由地對連接中的表進行重新排序。

兩種連接順序都會導致使用主鍵查找的連接。在這兩種情況下，查找的第一列都基于查詢中的恒定條件。查找的第二列是來自第一個表的參考。

所以優化器進退兩難。它可以選擇任一連接順序，并且都滿足主鍵查找的偏好。所以它任意選擇一個。

失敗在于它顯然沒有考慮到條件 onapplication_id導致它檢查超過 330k 行。要么優化器對這個成本視而不見，要么表統計資訊不是最新的并且欺騙了優化器。

您可以重繪表統計資訊。這很容易做到，并且對運行系統的影響很小，所以您不妨這樣做以排除不良統計資訊導致不良查詢優化的可能性。

ANALYZE TABLE app_roles;
ANALYZE TABLE staff;

然后再次嘗試您的查詢。

如果它仍然選擇了錯誤的優化策略，您可以使用連接提示強制它使用與您在查詢中撰寫的連接順序相匹配的連接順序。

select  id as Id, 
        eid as AccountId, 
        Surname 
from staff 
STRAIGHT_JOIN app_roles ON (app_roles.app_staff_id = staff.id )
where staff.eid = 7227 
AND app_roles.application_id = '1' 
and last_modified > '2022-05-11 13:15:21Z'

可能還有一種方法可以合并last_modified到索引中，但我不知道它屬于哪個表。

uj5u.com熱心網友回復：

這些“復合”和“覆寫”索引應該有助于：

staff:  INDEX(eid, last_modified, id,  Surname)
app_roles:  INDEX(application_id, app_staff_id)

去掉字面上Z的；DATETIMEMySQL不明白這樣的。

轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/484593.html

標籤：mysql 表现优化

上一篇：在mysql中使用PHP的Concat陳述句導致mysqli_query回傳false

下一篇：如何找到所有最新日期的站點？