有个关于查询效率的问题请教一下大神
mysql吧
全部回复
仅看楼主
level 1
SELECT DISTINCT
sui.id
FROM
system_user_info sui
INNER JOIN system_user_assign sua ON sua.user_id = sui.user_id
INNER JOIN sys_rank sr ON sr.id = sua.rank
INNER JOIN sys_user su ON su.id = sua.user_id
INNER JOIN (
SELECT
username_real,
MAX( evaluation ) evaluation_low,
MIN( evaluation ) evaluation_high
FROM
hr_performance_appraisal
WHERE
assessment_type = 1
AND del_flag = 0
AND YEAR < CAST(( DATE_FORMAT( NOW(), '%Y' )) AS SIGNED ) AND YEAR > CAST(( DATE_FORMAT( NOW(), '%Y' )) AS SIGNED ) - 4
GROUP BY
username_real having evaluation_high <= '3'
) hpa ON hpa.username_real = su.username
WHERE
evaluation_high <= '3' // 查询条件1
or (
rank_name = 'D3'
OR ( rank_name != 'D3' AND evaluation_low <= '2' )) // 查询条件2
上面是查询语句,上面的两个查询条件用 or连接查的就很快,用and连接就会巨慢无比,explain的时候发现用or的时候使用的是autokey,用and则是all,请问一下这是什么原因。
2024年11月27日 06点11分 1
1