问题背景：

历史服务分享数，关注数，好友数，每次获取都做DB count，影响查询效率。所以把用户的关注数，粉丝数，好友数，在关注，取消关注时维护具体值，记录在表 user_friend_extend，使用该表数据之前预处理所有历史数据。
背景表

#好友表结构
create table friend
(
    UserID bigint not null comment '用户ID',
    ToUserID bigint not null comment '关注对象用户ID',
    Status smallint(6) default '1' not null comment '状态 1 关注 0 无效 2 好友',
    CreateTime timestamp default CURRENT_TIMESTAMP not null comment '创建时间',
    UpdateTime timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP,
    primary key (UserID, ToUserID)
);

comment '好友(关注、粉丝)' engine=InnoDB charset=utf8;
create index inx_userid_full on friend (UserID, CreateTime, Status, ToUserID);
create index inx_touserid on friend (ToUserID, CreateTime, Status);
create index inx_1 on friend (ToUserID);

目的:

预处理现有用户的好友数据

方法：

依次分步获取每项记录

#清理环境
TRUNCATE  t1;
TRUNCATE  t2;
TRUNCATE  t3;
drop table t1;
drop table t2;
drop table t3;

#获取关注数
 create table t1 as select userId , count(*) followCount from friend where Status!=0 group by UserID;
#获取粉丝数
 create table t2 as select ToUserID , count(*) fansCount from friend where Status!=0 group by ToUserID;
#获取好友数
 create table t3 as select ToUserID , count(*) friendCount from friend where Status=2 group by ToUserID;

#临时表创建索引
 alter table t1 add index idx_userId(userId);
 alter table t2 add index idx_toUserId(toUserId);
 alter table t3 add index idx_toUserId(toUserId);

获取userId基础集合

# t1 所有关注 + t2 所有粉丝用户 +  t3所有好友数据
TRUNCATE  t4;
drop table t4;

create table t4 as
select userId from t1
  UNION
select touserid as userId from t2
  UNION
select toUserID as userId from t3;

生产预备数据

# 创建预备数据表
TRUNCATE  t5;
drop table t5;
create table t5
(
userId bigint PRIMARY KEY ,
followCount bigint,
fansCount bigint,
friendCount bigint
);
#生产数据
insert into t5
    select t4.userId ,ifnull(t1.followCount,0),ifnull(t2.fansCount,0),IFNULL(t3.friendCount,0)
from t4
  left join t1 on t4.userId =t1.userId
  left join t2 on t4.userId =t2.ToUserID
  left join t3 on t4.userId  =t3.ToUserID;

如果分主从库

#从库导出
select * from space.t5 into OUTFILE  '/home/mysql/a.txt';
#文件传输
scp  /home/mysql/a.txt mysql@主库ip:/home/mysql/a.txt
#主库导入
load data infile '/home/mysql/a.txt' ignore into table space.space_user_friend_extend

导入到生产库

#
insert ignore into user_friend_extend(userID, followCount, fansCount, friendCount)
    select t5.userId,t5.followCount,t5.fansCount,t5.friendCount from t5;

打扫战场

TRUNCATE  t1;
TRUNCATE  t2;
TRUNCATE  t3;
TRUNCATE  t4;
TRUNCATE  t5;
drop table t1;
drop table t2;
drop table t3;
drop table t4;
drop table t5;
#主从机器删除文件
rm home/mysql/a.txt -f