MySQL分表查询

JWbonze

已于 2022-05-15 18:16:17 修改

阅读量4.9k

点赞数 1

分类专栏： # 数据库文章标签： sql 数据库 database

于 2021-12-07 10:08:13 首次发布

本文链接：https://blog.csdn.net/JWbonze/article/details/121762121

版权

数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

针对大数据量导致的查询效率问题，该博客介绍了通过按年份分表来提升SQL查询性能。以user表为例，创建了user_2020和user_2021两张表，并展示了数据插入。在进行多表查询时，使用UNION ALL结合GROUP BY进行城市平均年龄的统计，有效优化了查询速度。

摘要由CSDN通过智能技术生成

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