mysql_查询优化之短路查询-CSDN博客

SELECT c.no AS '卡号',c.amt AS '余额' ,d.date AS '日期',ttt.day as '日期'
FROM c
LEFT JOIN  d  on c.no = d.no
left join  a on a.id = d.billid
left join (
	SELECT no,max(day) day from (
		SELECT no,max(day) day FROM  t_2013 GROUP BY no UNION ALL
		SELECT no,max(day) day FROM  t_2014 GROUP BY no UNION ALL
		SELECT no,max(day) day FROM  t_2015 GROUP BY no UNION ALL
		SELECT no,max(day) day FROM  t_2016 GROUP BY no UNION ALL
		SELECT no,max(day) day FROM  t_2017 GROUP BY no UNION ALL
		SELECT no,max(day) day FROM  t_2018 GROUP BY no
	) tt GROUP BY no
) ttt on ttt.no = c.no
WHERE d.f = '1' and a.y = '0' AND c.s = '00' AND d.date <= '20181231'
AND c.amt > 0 AND c.amt <= 10 AND c.no NOT IN (
SELECT DISTINCT no FROM
(	SELECT no FROM  t_2019 UNION ALL
	SELECT no FROM  t_2020 UNION ALL
	SELECT no FROM  t_2021 UNION ALL
	SELECT no FROM  t_2022 UNION ALL
	SELECT no FROM  t_2023 UNION ALL
	SELECT no FROM  t_2024 UNION ALL
	SELECT no FROM  t_2025 UNION ALL
	SELECT no FROM  t_2026 ) AS consumed_cards 
);

SELECT c.no AS '卡号',c.amt AS '余额',MAX(d.date) AS '充值日期', 
  COALESCE(
    (SELECT day FROM t_2018 WHERE no = c.no ORDER BY day DESC LIMIT 1),
    (SELECT day FROM t_2017 WHERE no = c.no ORDER BY day DESC LIMIT 1),
    (SELECT day FROM t_2016 WHERE no = c.no ORDER BY day DESC LIMIT 1),
    (SELECT day FROM t_2015 WHERE no = c.no ORDER BY day DESC LIMIT 1),
    (SELECT day FROM t_2014 WHERE no = c.no ORDER BY day DESC LIMIT 1),
    (SELECT day FROM t_2013 WHERE no = c.no ORDER BY day DESC LIMIT 1)
  ) AS '最后日期'
FROM  c
  INNER JOIN  d ON c.no = d.no
  INNER JOIN  a ON a.id = d.billid
WHERE d.f = '1' and a.y = '0' AND c.s = '00' AND d.date <= '20181231'
AND c.amt > 0 AND c.amt <= 10
  AND NOT EXISTS (SELECT 1 FROM t_2019 h WHERE h.no = c.no)
  AND NOT EXISTS (SELECT 1 FROM t_2020 h WHERE h.no = c.no)
  AND NOT EXISTS (SELECT 1 FROM t_2021 h WHERE h.no = c.no)
  AND NOT EXISTS (SELECT 1 FROM t_2022 h WHERE h.no = c.no)
  AND NOT EXISTS (SELECT 1 FROM t_2023 h WHERE h.no = c.no)
  AND NOT EXISTS (SELECT 1 FROM t_2024 h WHERE h.no = c.no)
  AND NOT EXISTS (SELECT 1 FROM t_2025 h WHERE h.no = c.no)
  AND NOT EXISTS (SELECT 1 FROM t_2026 h WHERE h.no = c.no)
GROUP BY c.no, c.amt;

主要是利用limit 1 ,以及exists 进行提取与数据排除,第一个sql查询超过180s,优化号 35秒以内,提取数据7w+

这将把原本需要扫描 6 张表并排序聚合的巨大开销，变成平均只扫描 1 张表（取决于数据分布），性能会有质的飞跃。