1. 问题背景
1.1 全国各级地方,包括省、市、县的学生,他们用小天才电话手表给其他地方的学生发消息,接收消息也会有记录,这些消息的明细记录在消息明细表中。只有成功发送消息,对方还要成功接收消息,有至少1条发送,一条接收成功的消息,互动才算成功。
1.2 每个学生的户籍地是不一样的,只能从他们的入学登记表中查询出来户籍地,入学登记表与消息明细表通过学生ID关联。
1.3 入学登记表中查询出来户籍地可能登记到县、市、省,还需要地区关系映射表转换成省。
1.4 现在需要统计互动成功的学生,他们向户籍地在本省的学生互动成功、向户籍地在其他生活上的学生互动成功的比例
1.5 消息明细表每天流水过千万,单张表过亿;入学登记表单张表数据量过亿。
2. 建表语句
-- 地区关系映射表
create table `region`(
`id` int(11) not null AUTO_INCREMENT COMMENT '主键',
`province_id` int(11) COMMENT '省级代码',
`province_name` varchar(50) COMMENT '省级名称',
`city_id` int(11) COMMENT '市级代码',
`city_name` varchar(50) COMMENT '市级名称',
`county_id` int(11) COMMENT '县级代码',
`county_name` varchar(50) COMMENT '县级名称',
`level` varchar(50) COMMENT '地区级别,省-1,市-2,县-3',
PRIMARY KEY(`id`) USING BTREE
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT_DYNAMIC;
-- 消息明细表
create table `messages_detail`(
`id` int(11) not null AUTO_INCREMENT COMMENT '主键',
`student_id` int(11) COMMENT '学号',
`student_name` varchar(50) COMMENT '姓名',
`status` varchar(5) COMMENT '状态,0-发送消息,1-接收消息',
`message_id` int(11) COMMENT '消息流水号',
`create_time` datetime DEFAULT NULL COMMENT '消息时间',
PRIMARY KEY(`id`) USING BTREE,
INDEX index_create_time (`create_time`),
INDEX index_message_id (`message_id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT_DYNAMIC;
-- 入学登记表
create table `enrollment_form`(
`id` int(11) not null AUTO_INCREMENT COMMENT '主键',
`student_id` int(11) COMMENT '学号',
`student_name` varchar(50) COMMENT '姓名',
`status` varchar(5) COMMENT '状态,0-正常,1-毕业',
`region_id` int(11) COMMENT '户籍地ID',
`create_time` datetime DEFAULT NULL COMMENT '时间',
PRIMARY KEY(`id`) USING BTREE,
INDEX index_message_id (`message_id`)
)ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 ROW_FORMAT_DYNAMIC;
3. 联表查询
因本省的学生互动较多,普通联表查询,查询出本省学生互动时较多,查询本省学生互动时耗时30分钟,但是查询与其他省学生互动时,耗时较久,约90分钟。
思路:
因为只有成功发送消息,对方还要成功接收消息,有至少1条发送,一条接收成功的消息,互动才算成功。发送多条消息的时候,流水号会有多条,但是消息明细表的状态一定至少有一个0-发送消息,与一个1-接收消息,因此通过消息流水号与状态两个字段进行去重,如果有2条记录,则代表互动成功。
3.1 本省学生互动
-- 1层
select count(*) from (
-- 2层
select mi, count(*) as cn from (
-- 3层
(select md.message_id as mi, md.status as sts from messages_detail md join enrollment_form ef
on md.student_id=ef.student_id
join
(select province_id as lowId, province_id as highId from region where level=1
union
select city_id as lowId, province_id as highId from region where level=2
union
select county_id as lowId, province_id as highId from region where level=3
) as rg
on ef.region_id = rg.lowId
-- 限定消息日期,与消息类型为0-发送
where md.create_time='20240801' and md.status =0 and rg.highId ='湖北省的ID')
-- 进行两个字段的去重
union
(select md.message_id as mi, md.status as sts from messages_detail md join enrollment_form ef
on md.student_id=ef.student_id
join
(select province_id as lowId, province_id as highId from region where level=1
union
select city_id as lowId, province_id as highId from region where level=2
union
select county_id as lowId, province_id as highId from region where level=3
) as rg
on ef.region_id = rg.lowId
-- 限定消息日期,与消息类型为1-接收消息
where md.create_time='20240801' and md.status =1 and rg.highId ='湖北省的ID')
) group by mi
) as c
where c.cn=2;
3.2 本省学生与其他省学生互动
-- 1层
select count(*) from (
-- 2层
select mi, count(*) as cn from (
-- 3层
(select md.message_id as mi, md.status as sts from messages_detail md join enrollment_form ef
on md.student_id=ef.student_id
join
(select province_id as lowId, province_id as highId from region where level=1
union
select city_id as lowId, province_id as highId from region where level=2
union
select county_id as lowId, province_id as highId from region where level=3
) as rg
on ef.region_id = rg.lowId
-- 限定消息日期,与消息类型为0-发送
where md.create_time='20240801' and md.status =0 and rg.highId ='湖北省的ID')
-- 进行两个字段的去重
union
(select md.message_id as mi, md.status as sts from messages_detail md join enrollment_form ef
on md.student_id=ef.student_id
join
(select province_id as lowId, province_id as highId from region where level=1
union
select city_id as lowId, province_id as highId from region where level=2
union
select county_id as lowId, province_id as highId from region where level=3
) as rg
on ef.region_id = rg.lowId
-- 限定消息日期,与消息类型为1-接收消息
where md.create_time='20240801' and md.status =1 and rg.highId !='湖北省的ID')
) group by mi
) as c
where c.cn=2;
4. 脚本
通过建立临时表,查询效率较高,耗时60分钟。
脚本名称,region_stu_connect.sh
#!/bin/sh
if[$# -ne 2];then
echo"\nUSAGE:`basename $0`<省份><互动日期>\n"
exit
fi
date
set session transaction isolation level READ UNCOMMITTED;
-- 获取地区
select province_id as lowId from region where province_id = '$1'
union
select city_id as lowId from region where province_id = '$1'
union
select county_id as lowId from region where province_id = '$1'
into temp tmp_region with no log;
-- 获取消息类型为0-发送消息的记录
select student_id, message_id from messages_detail where create_time='$2' and status ='0' and exists (
select 1 from enrollment_form where region_id in (
select lowId from tmp_region )
and messages_detail.student_id = enrollment_form.student_id
) into temp tmp_messages_detail_out with no log;
-- 关联消息类型为0-发送消息的记录,获取接收到消息的记录
select a.message_id , a.student_id,b.student_id as sid from tmp_messages_detail_out a
left join messages_detail b
on b.create_time='$2' and b.message_id =a.message_id and b.status=1
into temp tmp_messages_detail_in with no log;
-- 两张临时表进行关联
select distinct a.message_id , a.student_id,'$1' as region_id ,a.sid ,c.province_id from tmp_messages_detail_in a,enrollment_form b, region c where b.student_id=a.sid and (
c.city_id=b.region_id or c.county_id=b.region_id )
into temp tmp_messages_detail with no log;
select region_id,province_id,count(*) from tmp_messages_detail group by region_id,province_id;
!
date
exit 0
执行命令
chmod +x ./region_stu_connect.sh
nohup ./region_stu_connect.sh 湖北省的ID 20240801 > region_stu_connect_20240801.sh > &1 &