SQL操作目录
单表存储500万以上数据时，查询缓慢。现根据业务对表按照年份进行分表，如原user表分为user_2019、user_2020、user_2021等。

材料

user_2020

DROP TABLE IF EXISTS `user_2020`;
CREATE TABLE `user_2020`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `gender` tinyint(255) NULL DEFAULT NULL,
  `city` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `introduce` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `createTime` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user_2020
-- ----------------------------
INSERT INTO `user_2020` VALUES (1, '张黑', 30, 0, '北京', '我叫张黑', '2021-08-04 10:31:23');
INSERT INTO `user_2020` VALUES (2, '李白', 17, 1, '长春', '我叫李白', '2021-08-03 09:31:55');
INSERT INTO `user_2020` VALUES (3, '王蓝', 23, 0, '长春', '我叫王蓝', '2021-08-04 09:32:26');
INSERT INTO `user_2020` VALUES (4, '赵绿', 21, 1, '济南', '我叫赵绿', '2021-08-07 09:32:53');
INSERT INTO `user_2020` VALUES (5, '刘紫', 29, 1, '杭州', '我叫刘紫', '2021-08-09 09:33:18');

user_2021

DROP TABLE IF EXISTS `user_2021`;
CREATE TABLE `user_2021`  (
  `id` int(11) NOT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `gender` tinyint(255) NULL DEFAULT NULL,
  `city` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `introduce` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `createTime` datetime(0) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user_2021
-- ----------------------------
INSERT INTO `user_2021` VALUES (1, '张三', 40, 0, '北京', '我叫张三', '2021-08-04 10:31:23');
INSERT INTO `user_2021` VALUES (2, '李四', 17, 1, '长春', '我叫李四', '2021-08-03 09:31:55');
INSERT INTO `user_2021` VALUES (3, '王五', 23, 0, '长春', '我叫王五', '2021-08-04 09:32:26');
INSERT INTO `user_2021` VALUES (4, '赵六', 21, 0, '沈阳', '我叫赵六', '2021-08-07 09:32:53');
INSERT INTO `user_2021` VALUES (5, '刘七', 29, 0, '沈阳', '我叫刘七', '2021-08-09 09:33:18');

多表查询时使用UNION ALL连接多张表。

GROUP BY与UNION ALL联合

按照城市分组求各城市平均年龄：

SELECT city,AVG(age) 
FROM(
	(SELECT city, AVG(age) AS age
	FROM user_2020
	GROUP BY city)
	UNION ALL
	(SELECT city, AVG(age) AS age
	FROM user_2021
	GROUP BY city)
) AS a
GROUP BY city