这是我的table：

create table program_versions
(
    id                  int auto_increment primary key,
    program_id          int,
    version             smallint,
    start_date          datetime,
    constraint uniq_contraint unique (program_id, version),
    index idx_pi_sd (program_id, start_date)
)

这是有问题的SELECT查询：

SELECT 
    pv.program_id,
    max(pv.version) as version,
    pv.start_date
FROM program_versions pv 
INNER JOIN (
    SELECT
        program_id,
        max(start_date) recent_date
    FROM program_versions
    WHERE start_date <= NOW()
    GROUP BY program_id 
    ) pv_temp 
        ON pv.program_id = pv_temp.program_id
        AND pv.start_date = pv_temp.recent_date
GROUP BY pv.program_id, pv.start_date;

该表有大约100万行。当MySQL版本为5.6时，查询被罚款（在预期时间内运行）。但是，当服务器升级到5.7时，这个查询的执行速度开始变得非常慢（大约4分钟）
我没有办法更改MySQL服务器版本，所以我需要找到另一种方法来优化这个查询。任何建议/建议将不胜感激
我尝试创建一个临时表而不是子查询，但我仍然需要运行外部GROUP BY，这是极其缓慢的

更新
SELECT VERSION（）

5.7.41-log

SHOW CREATE TABLE program_versions

CREATE TABLE `program_versions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `program_id` int(11) DEFAULT NULL,
  `version` smallint(6) DEFAULT NULL,
  `start_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_contraint` (`program_id`,`version`),
  KEY `idx_pi_sd` (`program_id`,`start_date`),
  KEY `idx_pi_sd_vs` (`program_id`,`start_date`,`version`),
  KEY `idx_sd` (`start_date`)
) ENGINE=InnoDB AUTO_INCREMENT=3014611 DEFAULT CHARSET=utf8mb4

ANALYZE TABLE程序版本;

+---------------------------+-------+--------+--------+
|Table                      |Op     |Msg_type|Msg_text|
+---------------------------+-------+--------+--------+
|local_test.program_versions|analyze|status  |OK      |
+---------------------------+-------+--------+--------+

显示来自program_versions的索引;

+----------------+----------+--------------+------------+-----------+---------+-----------+--------+------+----+----------+-------+-------------+
|Table           |Non_unique|Key_name      |Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|
+----------------+----------+--------------+------------+-----------+---------+-----------+--------+------+----+----------+-------+-------------+
|program_versions|0         |PRIMARY       |1           |id         |A        |2994640    |null    |null  |    |BTREE     |       |             |
|program_versions|0         |uniq_contraint|1           |program_id |A        |1498041    |null    |null  |YES |BTREE     |       |             |
|program_versions|0         |uniq_contraint|2           |version    |A        |2994640    |null    |null  |YES |BTREE     |       |             |
|program_versions|1         |idx_pi_sd     |1           |program_id |A        |1499264    |null    |null  |YES |BTREE     |       |             |
|program_versions|1         |idx_pi_sd     |2           |start_date |A        |1499264    |null    |null  |YES |BTREE     |       |             |
|program_versions|1         |idx_pi_sd_vs  |1           |program_id |A        |1498952    |null    |null  |YES |BTREE     |       |             |
|program_versions|1         |idx_pi_sd_vs  |2           |start_date |A        |1498952    |null    |null  |YES |BTREE     |       |             |
|program_versions|1         |idx_pi_sd_vs  |3           |version    |A        |2994640    |null    |null  |YES |BTREE     |       |             |
|program_versions|1         |idx_sd        |1           |start_date |A        |124        |null    |null  |YES |BTREE     |       |             |
+----------------+----------+--------------+------------+-----------+---------+-----------+--------+------+----+----------+-------+-------------+

解释SELECT...

+--+-----------+----------------+----------+-----+--------------------------------------------+------------+-------+--------------------------------------+-------+--------+--------------------------------------------+
|id|select_type|table           |partitions|type |possible_keys                               |key         |key_len|ref                                   |rows   |filtered|Extra                                       |
+--+-----------+----------------+----------+-----+--------------------------------------------+------------+-------+--------------------------------------+-------+--------+--------------------------------------------+
|1 |PRIMARY    |<derived2>      |null      |ALL  |null                                        |null        |null   |null                                  |1497320|100     |Using where; Using temporary; Using filesort|
|1 |PRIMARY    |pv              |null      |ref  |uniq_contraint,idx_pi_sd,idx_pi_sd_vs,idx_sd|idx_pi_sd_vs|11     |pv_temp.program_id,pv_temp.recent_date|1      |100     |Using index                                 |
|2 |DERIVED    |program_versions|null      |index|uniq_contraint,idx_pi_sd,idx_pi_sd_vs,idx_sd|idx_pi_sd   |11     |null                                  |2994640|50      |Using where; Using index                    |
+--+-----------+----------------+----------+-----+--------------------------------------------+------------+-------+--------------------------------------+-------+--------+--------------------------------------------+