左链接嵌套子查询，如何做优化？ 比如下面我遇到的一个查询，如果能优化到查询时间在 2 s 内？

select
		attend.id,
		record.id AS recordId
	FROM
		za_attendance_count attend	
		LEFT JOIN (
		SELECT
			t.id,
			t.user_id,
      from_unixtime( LEFT ( t.retime, 10 ), '%Y-%m-%d' )  as clock_time
		FROM
			za_identity_record t 
		WHERE
			( t.STATUS != 1 AND t.io_send != - 1 ) 
			AND t.school_id = '35ab4e119caf4ff68a8d27fe77edb887' 
			AND (
				t.retime BETWEEN 1657036800000 
			AND 1657123199000) 
			AND t.attendance_type = 3 
		) record ON attend.clock_time = record.clock_time and attend.user_id = record.user_id	
	WHERE
		attend.school_id = '35ab4e119caf4ff68a8d27fe77edb887' 
		AND attend.attendance_type = 3 
		AND (
			attend.clock_time BETWEEN '2022-07-06 00:00:00.0' 
		AND '2022-07-06 23:59:59.0') 

整个执行完，大概10s
里面的子查询，执行下来，大概0.3s，返回结果集2条记录。如下：

SELECT
			t.id,
			t.user_id,
      from_unixtime( LEFT ( t.retime, 10 ), '%Y-%m-%d' )  as clock_time
		FROM
			za_identity_record t 
		WHERE
			( t.STATUS != 1 AND t.io_send != - 1 ) 
			AND t.school_id = '35ab4e119caf4ff68a8d27fe77edb887' 
			AND (
				t.retime BETWEEN 1657036800000 
			AND 1657123199000) 
			AND t.attendance_type = 3 

单查外面的sql:

SELECT
		attend.id
	FROM
		za_attendance_count attend	
		
	WHERE
		attend.school_id = '35ab4e119caf4ff68a8d27fe77edb887' 
		AND attend.attendance_type = 3 
		AND (
			attend.clock_time BETWEEN '2022-07-06 00:00:00.0' 
		AND '2022-07-06 23:59:59.0') 

返回结果集2076条，耗时0.5s.