带有索引查找和过滤器的嵌套循环内部联接速度较慢

我测试了您的查询，并在每个表中尝试了不同的索引。

ALTER TABLE library ADD KEY bk1 (user_id, path); 

ALTER TABLE plays ADD KEY bk2 (user_id, path, time); 

EXPLAIN SELECT
    COUNT(*)
FROM
    library AS l USE INDEX (bk1)
    JOIN plays AS p USE INDEX (bk2)
      ON p.user_id = l.user_id 
      AND l.path = p.path
WHERE
    l.user_id = 20977 
    AND p.time >= '2022-10-17';

+----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref               | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+--------------------------+
|  1 | SIMPLE      | l     | NULL       | ref  | bk1           | bk1  | 4       | const             |    1 |   100.00 | Using index              |
|  1 | SIMPLE      | p     | NULL       | ref  | bk2           | bk2  | 2056    | const,test.l.path |    1 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------------------+------+----------+--------------------------+

EXPLAIN 报告每一行中的注释“使用索引”表明，它从两个表的覆盖索引中获益。

我没有使用前缀索引语法，因为这会破坏覆盖索引优化。在现代MySQL版本上，没有必要使用此示例的前缀索引，因为它们默认为支持3072字节索引的InnoDB行格式，而不是默认情况下仅支持768字节索引的旧MySQL。

在我的测试中，我测试的表中没有行，因此我不得不使用索引提示来让优化器选择我的新索引。在具有大量行的表中，优化程序可能会自行选择新索引。

删除这些，它们挡住了路和/或冗余：

l: `user_id` (`user_id`),
p: `user_id` (`user_id`,`name`,`path`(255)),
p: `user_id_2` (`user_id`,`name`),

添加以下内容：

l:  INDEX(user_id,  path)
p:  INDEX(user_id,  path, time)
p:  INDEX(user_id,  time, path)   -- see below

更改（MySQL 5.7/8.0 不再需要前缀 kludge）：

l:  `user_id_loc` (`user_id`,`name`,`path`)  -- tossing 191

尽量避免测试 中不同表中的列。WHERE

我第一次看到

    WHERE  l.user_id = 20977
      AND  p.time >= '2022-10-17';

并认为这是问题的症结所在。但后来我看到你没有打开，并且表格[部分]连接起来.INDEX(user_id, time)puser_id

建议（以避免我的混淆）您进行以下更改：

    WHERE  l.user_id = 20977   -- >
    WHERE  p.user_id = 20977

优化器应该足够聪明，能够意识到这一点，然后使用

p:  INDEX(user_id,  time, path)   -- as mentioned above

但是，一旦完成此操作，查询将折叠到

SELECT COUNT(DISTINCT user_id, path)
    FROM plays
    WHERE  user_id = 20977
      AND  time >= '2022-10-17';

我认为它会说“覆盖索引跳过扫描以进行重复数据删除”，以表明它实际上并没有扫描所有 60K 行，而是跳过索引！plays

但是，如果有些“播放”在“库”中没有相应的条目，则计数将因缺少用户播放组合的数量而增加。

当表同时具有 .：INDEX(a), INDEX(a,b)

• 当查询只需要 时，则任一索引都将起作用。(a)
• 当查询需要时，优化器可能会选择它，因为它较小，而没有意识到更大的索引会更好。(a,b)(a)

出于这个原因，我建议一些.DROPs

另一个原因是去掉“前缀”索引（），它要么适得其反，要么不再需要。DROPspath(255)