MySQL在內部連接中不使用主鍵

我有兩張桌子：

表格1：

CREATE TABLE `lk_transaction_types` (
 `transactionTypeID` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `isActive` tinyint(2) unsigned NOT NULL,
 `code` varchar(8) NOT NULL,
 `description` varchar(150) NOT NULL,
 `isInbound` tinyint(2) unsigned NOT NULL DEFAULT '1',
 `isOutbound` tinyint(2) unsigned NOT NULL DEFAULT '1',
 PRIMARY KEY (`transactionTypeID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表 2：

CREATE TABLE `ediLoad` (
 `loadID` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `processID` int(10) unsigned NOT NULL,
 `success` tinyint(2) unsigned NOT NULL DEFAULT '0',
 `transactionTypeID` tinyint(2) unsigned DEFAULT NULL,
 `escoID` int(10) unsigned DEFAULT NULL,
 `ldcID` int(10) unsigned DEFAULT NULL,
 `commodityType` tinyint(3) unsigned NOT NULL DEFAULT '0',
 `filename` varchar(150) NOT NULL,
 `loadDate` datetime NOT NULL,
 `processed` tinyint(2) unsigned NOT NULL DEFAULT '0',
 `processedDate` datetime DEFAULT NULL,
 `dataApplied` tinyint(2) unsigned NOT NULL DEFAULT '0',
 `dataAppliedDate` datetime DEFAULT NULL,
 `errorID` tinyint(3) unsigned DEFAULT NULL,
 `error` tinyint(2) unsigned DEFAULT '0',
 `warning` tinyint(2) unsigned DEFAULT '0',
 PRIMARY KEY (`loadID`),
 KEY `idx_processID` (`processID`,`transactionTypeID`,`escoID`),
 KEY `idx_escoID` (`escoID`),
 KEY `idx_filename` (`success`,`filename`),
 KEY `idx_bulk` (`processed`,`loadDate`),
 KEY `idx_loadDate` (`loadDate`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

嘗試執行簡單查詢時，它沒有使用 lk_transaction_types 表上的主鍵：

SELECT COUNT(0)
FROM edi.ediLoad l
INNER JOIN edi.lk_transaction_types lk 
 ON  lk.transactionTypeID = l.transactionTypeID
WHERE l.escoID = 2
AND lk.isActive = 1
AND lk.isInbound = 1;

查詢很慢。所以我執行解釋並得到這個：

+----+-------------+-------+------------+------+-----------------------------------+------------------------+---------+--------------------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys                     | key                    | key_len | ref                      | rows | filtered | Extra                              |
+----+-------------+-------+------------+------+-----------------------------------+------------------------+---------+--------------------------+------+----------+------------------------------------+
|  1 | SIMPLE      | lk    | NULL       | ALL  | PRIMARY                           | NULL                   | NULL    | NULL                     |   31 |     3.23 | Using where                        |
|  1 | SIMPLE      | l     | NULL       | ref  | idx_escoID,idx_ transactionTypeID | idx_ transactionTypeID | 2       | edi.lk.transactionTypeID | 7158 |    50.00 | Using index condition; Using where |
+----+-------------+-------+------------+------+-----------------------------------+------------------------+---------+--------------------------+------+----------+------------------------------------+

這似乎是一個簡單的查詢，主鍵上有一個聯接。為什麼不使用主鍵？我什至嘗試將“FORCE INDEX FOR JOIN (PRIMARY)”添加到連接中，但它仍然不使用主鍵。任何幫助都會很棒。謝謝！

筆記

• AFOREIGN KEY增加一個約束；如果那裡已經有一個索引，它不會添加索引。
• 數據類型應該匹配，但是對於JOINing.
• 出了什麼問題—— an 的列是INDEX從左到右查看的。由於沒有提及processID（第一列），因此索引沒有用。
• 時JOINing，MySQL通常從一張表開始，然後反復進入另一張表。它選擇了lk。為什麼？…
• 在實際情況下，MySQL 將從可以對其進行一些過濾的表開始。但是這兩個表都沒有任何有用的索引。所以？…
• 作為備份，MySQL 從“較小”表開始，lk_transaction_types並進行全表掃描（“ALL”）。

為了使查詢更有效， 通常首先處理過濾（WHERE）：

WHERE l.escoID = 2
 AND lk.isActive = 1
 AND lk.isInbound = 1;

為了幫助優化器選擇l第一個表，有

INDEX(escoID, ...)

或者，為了幫助它選擇lk，有

INDEX(isActive, isInbound, ...)  -- in either order

...是可選的更多列。

在不了解數據值分佈的情況下，讓我繼續推導每種情況的最佳索引。

SELECT  COUNT(0)
   FROM  edi.ediLoad l
   INNER JOIN  edi.lk_transaction_types lk
       ON lk.transactionTypeID =
           l.transactionTypeID
   WHERE   l.escoID = 2
     AND  lk.isActive = 1
     AND  lk.isInbound = 1;

案例：開始於l：

l:   INDEX(escoID,             -- first, so as to satisfy `WHERE`
          transactionTypeID)  -- added to make the index "covering"

“覆蓋索引”包含滿足整個查詢所需的所有列（一個表的）。這讓處理只在索引 BTree 中工作，而忽略 Data BTree。

lk:  INDEX(isActive, isInbound, transactionTypeID)  -- in any order

同樣，這是“覆蓋”。

案例：開始於lk：

lk:  INDEX(isActive, isInbound,  -- first, in either order
          transactionTypeID)
l:   INDEX(escoID, transactionTypeID) -- in either order

在這兩種情況下都沒有PRIMARY KEY用。所以，我聲稱你最初的問題是不明智的。相反，您應該問“我怎樣才能使這個查詢更有效率”。 這就是我一直在回答的問題。

還有一件事。 讓我們結合這兩種情況來最小化索引的數量。

lk:  INDEX(isActive, isInbound,  -- first, in either order
          transactionTypeID)
l:   INDEX(escoID, transactionTypeID)  -- in this order

使用 InnoDB，PK 與數據“聚集”在一起。PRIMARY KEY(transactionTypeID)對於第二種情況就足夠了，但對於第一種情況就足夠了。

更多關於創建索引。

foreign key這兩個表之間沒有引用。

其次，在表ediLoad列transactionTypeID數據類型是tinyint(2) unsigned DEFAULT NULL，而在表lk_transaction_types列transactionTypeID數據類型是int(10) unsigned NOT NULL AUTO_INCREMENT。

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