MySQL性能扩展的架构优化方案(三)

这是学习笔记的第 1816篇文章

在前几天分享过一个小系列的文章。

在后续也做了跟进和补充，从最初的方案到最后的落地，今天总算是做了一个初步的了结。

上次聊到关于一个密集型写入的MySQL业务，通过读写分离完成了写入和统计的负载均衡，初步解决了写入的问题，但是统计的问题就开始日趋严重。

严重到整个从库的负载开始难以满足业务的需求，到最后无法满足。

这部分的主要瓶颈在IO层面。主要是因为大量的统计语句导致。

在和业务同学讨论的过程中，其实使用Redis方向是一个相对合适的技术方向，对于统计的支持力度还是不错的，但是限于存储成本和程序改造的工作量，业务更倾向于暂时按照已有的方案，通过对比infobright的统计优势和MySQL的协议兼容性，从而得出在目前的情况下选择这种方案是一个比较快捷高效的方案。

在具体落地的过程中，发现有一大堆的事情需要提前搞定。 

比如第一个头疼的问题就是全量的同步，第一次同步肯定是全量的，这么多的数据怎么同步到infobright里面。 

第二个问题随之而来，也是更为关键的，那就是同步策略是怎么设定的，是否可以支持的更加灵活。

第三个问题是基于现有的增量同步方案，需要在时间字段上添加索引。对于线上的操作而言又是一个巨大的挑战。

从目前的业务需求来说，最多能够允许一个小时的统计延迟，如果后期要做大量的运营活动，需要更精确的数据支持，要得到半个小时的统计数据，按照现有的方案是否能够支持。 

这两个主要的问题，任何一个解决不了，数据流转能够落地都是难题，这个问题留给我的时间只有一天。所以我准备把前期的准备和测试做得扎实一些，后期接入的时候就会顺畅得多。 

部分脚本实现如下：

echo  $1 $2
#tab_name=$1
starttime=$1
endtime=$2
/usr/local/mysql/bin/mysql -udba_admin -pDxxxx -h127.0.0.1 -P4306
 -Ne  "select table_name from information_schema.tables where table_schema='testdata' 
and table_name like 'receipt_%' order by table_rows ;" >/tmp/tablst

function data_sync_to_infobright
{
#rm  /tmp/a.csv
echo "export data...start "`date`
/usr/local/mysql/bin/mysql -udba_admin -pDxxxx -h127.0.0.1 -P4306 <select *from testdata.${tab_name} where create_time between '$2' and '$3'  into outfile '/data/dump_data/${tab_name}.csv' FIELDS TERMINATED BY ' ' ENCLOSED BY '\"'; 
EOF

echo "export data...done "`date`

echo "load data...start "`date`
/usr/local/mysql/bin/mysql -umsg_data_sync -pxxxx -h10.x.2.0 -P5029 <CREATE TABLE if not exists testdata.${tab_name} (
id int(11)  NOT NULL  COMMENT '自增主键',
userid int(11)  NOT NULL DEFAULT '0' COMMENT '用户ID',
action int(11)  NOT NULL DEFAULT '0' COMMENT '动作',
readtimes int(11)  NOT NULL DEFAULT '0' COMMENT '阅读次数',
create_time datetime NOT NULL  COMMENT '创建时间'
)  COMMENT='广播回执接收明细';

load data local infile '/data/dump_data/${tab_name}.csv' into table testdata.${tab_name} FIELDS TERMINATED BY ' ' ENCLOSED BY '"'; 

EOF

echo "load data...done "`date`
rm -f /data/dump_data/${tab_name}.csv
}

while read line
do
  echo $tab_name  "$starttime"  "$endtime"
  tab_name=`echo $line|awk '{print $1}'` 
  data_sync_to_infobright $tab_name "$starttime" "$endtime"
done echo $endtime  >/tmp/end_time

脚本的输入参数有两个，一个是起始时间，一个是截止时间。第一次全量同步的时候，可以把起始时间给的很早，这样截止时间是固定的，对于整个脚本的结构来说就不需要做大的变化了。 另外全量同步的时候一定要确保主从延迟已经最低或者暂时停掉查询业务，使得数据全量抽取更加顺利。

考虑到每天落盘的数据量大概在10G左右，日志量在30G左右，所以考虑先使用客户端导入infobright的方式来操作。

从实践来看，涉及的表有600多个，我先导出了一个列表，按照数据量来排序，这样小表就可以快速导入，大表放在最后，整个数据量有150G左右，通过网络传输导入infobright，从导出到导入完成，这个过程大概需要1个小时。

而导入数据到infobright之后的性能提升也是极为明显的。原来的一组查询持续时间在半个小时，现在在70秒钟即可完成。对于业务的体验来说大大提高。完成了第一次同步之后，后续的同步都可以根据实际的情况来灵活控制。所以数据增量同步暂时是手动挡控制。 

从整个数据架构分离之后的效果来看，从库的压力大大降低，而效率也大大提高。