为什么这样写全表扫描，不走索引呢（mysql5.7）

Question

问题遇到的现象和发生背景

这样写全表扫描，不走索引呢

问题相关代码，请勿粘贴截图

EXPLAIN
SELECT
    y.city_name,
    y.employee_number,
    y.last_name,
    y.org_name,
    y.person_type_name,
    y.employee_category,
    y.data_of_birth,
    y.age,
    y.edu_level,
    y.edu_degreen,
    y.job_name,
    y.job_zi_name,
    y.job_zhuanye,
    y.job_zhize 
FROM
    report_month_person_yonggong y
    INNER JOIN report_look_up l ON y.`year_month` = '202203' 
    AND l.`year` = '2022'
    AND y.job_zhuanye = l.job_zhuanye     
    AND y.org_id IN (
    SELECT
        o2.organization_id 
    FROM
        dhr.g_organization o1
        INNER JOIN dhr.g_organization o2 ON o2.leftnode >= o1.leftnode 
        AND o2.rightnode <= o1.rightnode 
    WHERE
        o1.organization_id IN ( '123321' ) 
    ) 
AND
CASE
        WHEN l.job_name = 'xxx' THEN
        y.job_zhize = l.job_zhize ELSE 1 = 1 
    END 
        AND y.person_type_name = 'xx'
LEFT JOIN report_not_detail AS d ON d.employee_number = y.employee_number 
        AND d.`year` = '2022' 
    WHERE
        d.employee_number IS NULL 

report_month 的索引字段：

运行结果及报错内容

我的解答思路和尝试过的方法

我想要达到的结果

Answer 1

SELECT
y.city_name,
y.employee_number,
y.last_name,
y.org_name,
y.person_type_name,
y.employee_category,
y.data_of_birth,
y.age,
y.edu_level,
y.edu_degreen,
y.job_name,
y.job_zi_name,
y.job_zhuanye,
y.job_zhize
FROM
report_month_person_yonggong y
INNER JOIN report_look_up l ON y.year_month = '202203'
AND l.year = '2022'
AND y.job_zhuanye = l.job_zhuanye
where y.year_month = '202203' AND l.year = '2022'
and exists(selelct 1 from dhr.g_organization o1
INNER JOIN dhr.g_organization o2 ON o2.leftnode >= o1.leftnode
AND o2.rightnode <= o1.rightnode
where o1.organization_id IN ( '123321' ) and o2.organization_id =y.org_id )
and not exits(select 1 from report_not_detail AS d where d.employee_number = y.employee_number
AND d.year = '2022' )
and l.job_name='xxx'
and y.job_zhize = l.job_zhize
union all
SELECT
y.city_name,
y.employee_number,
y.last_name,
y.org_name,
y.person_type_name,
y.employee_category,
y.data_of_birth,
y.age,
y.edu_level,
y.edu_degreen,
y.job_name,
y.job_zi_name,
y.job_zhuanye,
y.job_zhize
FROM
report_month_person_yonggong y
INNER JOIN report_look_up l ON y.year_month = '202203'
AND l.year = '2022'
AND y.job_zhuanye = l.job_zhuanye
where y.year_month = '202203' AND l.year = '2022'
and exists(selelct 1 from dhr.g_organization o1
INNER JOIN dhr.g_organization o2 ON o2.leftnode >= o1.leftnode
AND o2.rightnode <= o1.rightnode
where o1.organization_id IN ( '123321' ) and o2.organization_id =y.org_id )
and not exits(select 1 from report_not_detail AS d where d.employee_number = y.employee_number
AND d.year = '2022' )
and l.job_name!='xxx'

1.in 的判断改为exists,如果是存储过程或者批脚本，可以考虑把内表数据写到临时表去重，再来内关联；
2.left join改为not exists，同意道理，report_not_detail 的year=2022的数据量不大，而整个表数据量大的话，也可以考虑写到临时表
3.条件用case when子句来判断，你这是讨厌索引么，把逻辑拆开，使用union all来处理，一部分处理job_name='xxx'，一部分处理不等于的