為什麼最後一個表不使用索引加入？

在其他成員的幫助下，我對我以前的問題有了一個查詢想法。但是在寫完之後，EXPLAIN顯示我的最後一個表不使用索引進行連接，但連接ROW是PRIMARY KEY，表正在使用FULL TABLE SCAN。

下面是那個查詢：

WITH cte AS (SELECT MAX(m.id) ids
            FROM messages m
                     JOIN messages_thread_user mtu USING (threadId)
            WHERE mtu.userId = 1
            GROUP BY mtu.threadId
            order by ids desc
            limit 50 offset 0)
select m1.*, u.userName
from cte
        inner join messages m1 on cte.ids = m1.id
        inner join user u on m1.userId = u.userId;

以下是查詢說明：

+----+-------------+------------+------------+--------+---------------------------------+----------------------------+---------+---------------------+------+----------+----------------------------------------------------+
| id | select_type | table      | partitions | type   | possible_keys                   | key                        | key_len | ref                 | rows | filtered | Extra                                              |
+----+-------------+------------+------------+--------+---------------------------------+----------------------------+---------+---------------------+------+----------+----------------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL                            | NULL                       | NULL    | NULL                |    8 |   100.00 | Using where                                        |
|  1 | PRIMARY     | m1         | NULL       | eq_ref | PRIMARY,messages_user_userId_fk | PRIMARY                    | 8       | cte.id              |    1 |   100.00 | NULL                                               |
|  1 | PRIMARY     | u          | NULL       | ALL    | PRIMARY                         | NULL                       | NULL    | NULL                |    3 |    50.00 | Using where; Using join buffer (Block Nested Loop) |
|  2 | DERIVED     | mtu        | NULL       | ref    | PRIMARY,messages_thread_user_pk | messages_thread_user_pk    | 3       | const               |    2 |   100.00 | Using index; Using temporary; Using filesort       |
|  2 | DERIVED     | m          | NULL       | ref    | messages_threadId_id_index      | messages_threadId_id_index | 3       | to_let.mtu.threadId |    4 |   100.00 | Using index                                        |
+----+-------------+------------+------------+--------+---------------------------------+----------------------------+---------+---------------------+------+----------+----------------------------------------------------+
5 rows in set, 1 warning (0.00 sec)

這是視覺解釋：

這是messages表結構：

CREATE TABLE `messages` (
 `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
 `userId` mediumint(8) unsigned NOT NULL,
 `threadId` mediumint(8) unsigned NOT NULL,
 `status` tinyint(3) unsigned DEFAULT '0' COMMENT '0->sent, 1->deliveried, 2->seen',
 `time` datetime DEFAULT CURRENT_TIMESTAMP,
 `message` text NOT NULL,
 `data_url` varchar(512) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `messages_user_userId_fk` (`userId`),
 KEY `messages_threadId_id_index` (`threadId`,`id`),
 CONSTRAINT `messages_messages_thread_id_fk` FOREIGN KEY (`threadId`) REFERENCES `messages_thread` (`id`),
 CONSTRAINT `messages_user_userId_fk` FOREIGN KEY (`userId`) REFERENCES `user` (`userId`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8

這是messages_thread_user表結構：

CREATE TABLE `messages_thread_user` (
 `threadId` mediumint(8) unsigned NOT NULL,
 `userId` mediumint(8) unsigned NOT NULL,
 PRIMARY KEY (`threadId`,`userId`),
 UNIQUE KEY `messages_thread_user_pk` (`userId`,`threadId`),
 CONSTRAINT `message_thread_user_messages_thread_id_fk` FOREIGN KEY (`threadId`) REFERENCES `messages_thread` (`id`),
 CONSTRAINT `message_thread_user_user_userId_fk` FOREIGN KEY (`userId`) REFERENCES `user` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

這是user表結構：

CREATE TABLE `user` (
 `userId` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `userName` varchar(31) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `userNumber` bigint(20) unsigned NOT NULL,
 `userPassword` varchar(64) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 `userStatus` tinyint(4) NOT NULL DEFAULT '0',
 `userRegIp` varchar(35) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
 PRIMARY KEY (`userId`),
 KEY `user_userNumber_index` (`userNumber`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

如果您在上面看到解釋視覺或文本，您會看到useru 表不使用任何KEYor INDEX。為什麼會這樣？它應該使用INDEX. 請說明原因。

不是問題。

“BNL”(Block Nested Loop)是執行JOIN. 如果有效地將它需要的所有行載入到記憶體雜湊中。然後INDEX，它不會使用 或以其他方式從表中獲取內容，而是簡單地（並且非常快速地）進行雜湊查找。

如果您想進一步討論，請執行包含少量行的查詢。

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