msyql order by 后速度慢40倍以上优化

SELECT
    t1.schoolname,
    t1.kelei,
    t1.zxkm,
    t1.xkyq,
    t1.zdf,
    t1.zdfwc,
    t1.yearid,
    t1.zgf,
    t1.zgfwc,
    t1.enrollcode,
    t1.majorgroup,
    t1.major,
    t2.province AS schoolprovince,
    t2.isnatativevip,
    t2.is211,
    t2.is985,
    t2.bxxz,
    t2.bxcc 
FROM
    t_gkzy_admit_majorscore_hunan t1
    LEFT JOIN t_gkzy_school_base t2 ON t1.schoolname = t2.schoolname 
WHERE
    t1.yearid = '2022' 
    AND t1.shengyuandi = '湖南' 
    AND t1.batchname = '本科' 
    AND t1.zdf BETWEEN 500 
    AND 600 
ORDER BY
    t1.zdf DESC 
    LIMIT 0,
    10

 

数据表大概30W条数据，去除 order by 后，大概执行 0.2 秒； 加上后 8秒。

经过尝试，发现单表查询、排序还是很快的，于是需要改造SQL，将order by 放置于单表查询中分页，然后再基于结果 left join  来实现。改造后大概 0.2 秒，成功。