SQL筛选含Year列全集数据的国家及嵌套查询报错问题

问题背景 #

现有数据库表包含Country、Year、Electricity Generation三列，Year的取值覆盖1985至2021年的所有年份。需要筛选出在1985-2021时间段内数据完全完整的国家，排除以下三类异常数据的国家：

• 数据起始年份晚于1985
• 数据终止年份早于2021
• 时间段内存在年份缺失

遇到的问题：

1. 初始SQL查询无法满足需求
2. 改用嵌套查询时触发Error Code:1038内存不足错误

1. Error Code:1038 内存不足的原因 #

这个错误本质是MySQL处理临时表时内存资源耗尽，常见触发场景：

• 嵌套查询（尤其是多层嵌套或子查询未过滤数据）会生成临时表存储中间结果。MySQL默认优先用内存临时表，当数据量超过tmp_table_size或max_heap_table_size的限制时，会转为磁盘临时表；如果磁盘临时表的规模仍然超出系统可分配资源，就会触发1038错误。
• 如果Country和Year列没有建立联合索引，嵌套查询会触发全表扫描，导致临时表数据量暴增，进一步加剧内存占用。

2. 嵌套查询是否属于不良实践？ #

不是绝对的不良实践。嵌套查询在很多场景下逻辑清晰、易于理解和维护，问题出在不合理的嵌套写法，比如：

• 多层不必要的嵌套
• 子查询返回未经过滤的大量数据
• 未利用索引优化查询性能

更高效的替代写法 #

针对你的需求，用聚合分组的写法可以避免嵌套查询的内存问题，同时精准筛选出符合条件的国家：

SELECT Country
FROM your_table_name
WHERE Year BETWEEN 1985 AND 2021
GROUP BY Country
HAVING 
    MIN(Year) = 1985 
    AND MAX(Year) = 2021 
    AND COUNT(DISTINCT Year) = 37; -- 2021-1985+1=37，即完整年份数

逻辑说明：

• MIN(Year) = 1985：确保该国家数据起始年份为1985，排除起始晚的情况
• MAX(Year) = 2021：确保该国家数据终止年份为2021，排除终止早的情况
• COUNT(DISTINCT Year) = 37：确保该国家在时间段内的年份数量刚好是完整的37年，排除年份缺失的情况

优化建议 #

• 给Country和Year建立联合索引，大幅提升分组和聚合的查询效率：

  CREATE INDEX idx_country_year ON your_table_name(Country, Year);
  

• 如果一定要使用嵌套查询，可适当调大MySQL的临时表参数（tmp_table_size和max_heap_table_size），但这只是临时解决方案，优先优化查询写法和索引才是根本。

内容的提问来源于stack exchange，提问作者Swarnim Khosla