您的查询无效。因此，在讨论性能之前，您应该修复查询。您按profiles.id分组，但选择post_files.filename。哪一个？可能有成千上万个文件名链接到一个配置文件。DBMS应该在这里引发一个错误，但是你说你可以运行它。这意味着您在MySQL臭名昭著的作弊模式下运行，该模式允许无效的聚合查询。在早期的MySQL版本中，这种作弊模式甚至是默认的，这意味着你正在运行一个旧的MySQL版本，或者你将默认模式更改为作弊模式。你也不该这么做在MySQL中，始终以full group by模式工作：

SET sql_mode = 'ONLY_FULL_GROUP_BY';

字符串
如果我没有理解错的话，那么您将连接到一个在两个表中都不唯一的ID上。这意味着您的连接可以以0到2，500 x 800，000 = 2，000，000，000的结果行结束。（0个ID不匹配，20亿个ID匹配，即所有的ID都是一样的。或者两者之间的任何数字。）限制此结果的唯一条件是文件类型，您希望是“photo”，但是，由于这是关于Instagram的，因此这可能不是非常限制。在最坏的情况下，DBMS试图对20亿个连接行进行排序。
让我们看看DBMS应该做什么。我正在说明我可能采取的方法，DBMS可能会选择另一种方法来获得相同的结果。
1.尚未找到任何照片。
1.获取最新更新的配置文件
1.显示他们的Instagram照片，直到你要么全部阅读，要么点击了总共200张照片。
1.如果还没有找到200张照片，获取下一个最新的配置文件，然后继续步骤3.
第一个问题是：你真的想这样吗显示最新更新的个人资料的所有Instagram照片，然后也许更多？我觉得这是个奇怪的任务。
如果你想要这样做，那么我们可以帮助DBMS快速找到最新更新的配置文件，并获取他们的Instagram ID用于加入：

create index idx1 on profiles (last_updated_on, instagram_id);

型
然后我们想尽快找到Instragram的照片文件。我们附带了一个Instagram ID，并想知道文件类型：

create index idx2 on post_files (instagram_id, filetype);

型
如果这没有帮助，那么这可能是在应用中使用循环并运行单独的查询（每个最近更新的配置文件一个查询）的罕见情况之一。

你那异常糟糕的表现

• “还有30秒”*

让我想知道你是否有一些配置问题。了解您正在运行的MySQL版本以及在什么操作系统上运行会很有帮助？您的数据集是否适合缓冲池？
通过将分页移到子查询（如果愿意，也可以移到CTE），可以显著提高性能。这个查询仍然非常依赖于添加Thorsten建议的两个索引。

SELECT p.*, ANY_VALUE(pf.filename), COUNT(*)
FROM (
    SELECT p.*
    FROM profiles p
    WHERE EXISTS (
        SELECT 1
        FROM post_files
        WHERE filetype = 'photo' AND instagram_id = p.instagram_id
    )
    ORDER BY p.last_updated_on ASC
    LIMIT 0,200
) p
JOIN post_files pf ON p.instagram_id = pf.instagram_id AND pf.filetype = 'photo'
GROUP BY p.id
ORDER BY p.last_updated_on ASC;

字符串
注意，将ANY_VALUE()用作pf.filename在功能上不依赖于GROUP BY子句。
我模拟了一个具有2.5K和800 K行的数据集（可能与您的分布非常不同），并且无法重现您的糟糕性能。即使只有单列索引，原始查询也会在大约1.5秒内返回。
我创建了这六个索引，最初设置为不可见：

CREATE INDEX idx1 ON profiles (instagram_id) INVISIBLE;
CREATE INDEX idx2 ON profiles (last_updated_on) INVISIBLE;
CREATE INDEX idx3 ON profiles (last_updated_on, instagram_id) INVISIBLE;

CREATE INDEX idx1 ON post_files (instagram_id) INVISIBLE;
CREATE INDEX idx2 ON post_files (filetype) INVISIBLE;
CREATE INDEX idx3 ON post_files (instagram_id, filetype) INVISIBLE;

型
您的原始查询只有PKs（0.469s）：
| 选择类型|表|分区|类型，类型|可能键|键|键透镜|参考|行|滤过的|额外的| Extra |
| --|--|--|--|--|--|--|--|--|--|--| ------------ |
| 简单|p||全部|初级||||两千五|一百元|使用临时;|使用文件排序 Using filesort |
| 简单|PF||全部|||||七九八六三九|0.01|使用where;|使用连接缓冲区（散列连接） Using join buffer (hash join) |
您的原始查询只有idx 1索引（1.413s）：
| 选择类型|表|分区|类型，类型|可能键|键|键透镜|参考|行|滤过的|额外的| Extra |
| --|--|--|--|--|--|--|--|--|--|--| ------------ |
| 简单|p||索引|主，idx 1|初级|四个||两千五|一百元|使用临时;|使用文件排序 Using filesort |
| 简单|PF||参考|idx1| idx1|四个|测试.p.instagram_id|三百一十|50元|在哪里使用| Using where |
您的原始查询使用idx 1和idx 2索引（1.328s）：
| 选择类型|表|分区|类型，类型|可能键|键|键透镜|参考|行|滤过的|额外的| Extra |
| --|--|--|--|--|--|--|--|--|--|--| ------------ |
| 简单|PF||参考|idx1、idx2| idx2|一个|康斯特|398212|一百元|使用索引条件;|使用临时;使用文件排序 Using filesort |
| 简单|p||参考|PRIMARY，idx1，idx2| idx1|四个|测试.pf.instagram_id|一个|一百元|||
您的原始查询与idx 1，idx 2和idx 3索引（0.573s）：
| 选择类型|表|分区|类型，类型|可能键|键|键透镜|参考|行|滤过的|额外的| Extra |
| --|--|--|--|--|--|--|--|--|--|--| ------------ |
| 简单|p||索引|PRIMARY，idx1，idx2，idx3|初级|四个||两千五|一百元|使用临时;|使用文件排序 Using filesort |
| 简单|PF||参考|idx1、idx2、idx3| idx3|五个|测试.p.instagram_id，const|一百零七|一百元|使用索引条件| Using index condition |
我建议的查询将分页移动到派生表（0.078s）：
| 选择类型|表|分区|类型，类型|可能键|键|键透镜|参考|行|滤过的|额外的| Extra |
| --|--|--|--|--|--|--|--|--|--|--| ------------ |
| 初级|||全部|||||两百|一百元|使用临时;|使用文件排序 Using filesort |
| 初级|PF||参考|idx1、idx2、idx3| idx3|五个|p.instagram_id，const|一百零七|一百元|使用索引条件| Using index condition |
| 衍生|p||索引|idx1| idx3|九个||两百|一百元|使用索引| Using index |
| 衍生|后文件||参考|idx1、idx2、idx3| idx3|五个|测试.p.instagram_id，const|一百零七|一百元|使用where;|使用索引; FirstMatch（p） FirstMatch(p) |

Rick的查询速度快了很多，至少在我的测试数据集上是这样（0.013s）：

| 选择类型|表|分区|类型，类型|可能键|键|键透镜|参考|行|滤过的|额外的| Extra |
| --|--|--|--|--|--|--|--|--|--|--| ------------ |
| 初级|p||索引||idx3|九个||两百|一百元|使用索引| Using index |
| 相关查询|PF||参考|idx1、idx2、idx3| idx3|五个|测试.p.instagram_id，const|一百零七|一百元|使用where;|使用索引 Using index |
P.S.这些都是在我的本地MySQL 8.0.33示例上运行的。上面包括的查询时间是十次执行的平均值。

正如已经讨论过的，GROUP BY很差，这既是因为filename，也是因为性能。让我们摆脱它：

SELECT  p.id, p.instagram_id, p.last_updated_on,
        ( SELECT count(*) FROM post_files AS pf
              WHERE p.instagram_id = pf.instagram_id
                AND pf.filetype = 'photo'
        ) as total_images
    FROM  profiles AS p
    ORDER BY  p.last_updated_on ASC
    LIMIT  0,200;

字符串
请注意，这是如何在pf中只搜索200次，而不是所有配置文件。
推荐指数：

p:  INDEX(last_updated_on, instagram_id)
pf:  INDEX(instagram_id, filetype)

型
如果last_updated_on真的来自pf，那么将其全部翻转。

如果ID是唯一的，我建议尝试从查询中删除group by。另外，检查Mysql配置中的缓冲区大小。