Optimize MySQL average query for millions of rows
I would add an array to monitor_node, containing the most recent 100 response times.  When adding recent response times, drop those exceeding 100.  No need for extra data when a new average has to be calculated.

As an image may say more than a thousand words, I've updated the DB Fiddle.  A trigger does the job, but that logic could be incorporated in the job doing the inserts.

create trigger monitor_checks_air
  after insert on monitor_checks for each row
  begin
  update monitor_node mn
    set mn.rec_resp_times =
          json_extract(
             json_array_insert(mn.rec_resp_times,'$[0]',new.response_time),
             '$[0 to 99]'),
        mn.average_response_time = (
            select sum(jt.rt)
              from json_table(mn.rec_resp_times,
                             '$[*]' columns( rt double path '$[0]')) as jt
          )
          / json_length(mn.rec_resp_times) 
    where mn.monitor_id = new.monitor_id
      and mn.node_id    = new.node_id;
  end;


The fastest query is the one you don't execute.