1 查询场景
原始表 4 亿+行，日新增 60w+ 行数据，查询场景不复杂，主要是根据字段 time 时间范围筛选，再加几个 where 查询条件，测试查询跨度几天的数据耗时很久。
建表语句大致如下（实际表有20几个字段，这里省略）。
CREATE TABLE `migu_plus_mgp_mgomsserviceusing` (
`id` varchar(8) NOT NULL COMMENT '记录序号',
`passid` varchar(40) DEFAULT NULL COMMENT '用户唯一标识',
`gcpid` varchar(40) DEFAULT NULL COMMENT '如果是一点接入合作伙伴，记录于此字段',
`time` datetime NOT NULL COMMENT '使用请求时间',
`dayid` varchar(8) DEFAULT NULL COMMENT '日期',
`autoid` bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`autoid`)
) ENGINE=MyISAM AUTO_INCREMENT=840241842 DEFAULT CHARSET=utf8
2 优化思路
大表优化的思路我能想到的就是：分库分表，加索引等。针对这个 BI 查询场景，分库分表不现实；
考虑到现在的场景，决定借鉴 hive 的分区思路，对 mysql 表也进行分区，初步思路就是按天分区，这样查询几天的数据，扫描规模就会大幅度降低。
考虑到在原始表增加分区可能特别慢，且可能会影响原始表，初步思路：
- 复制新表
- 设置并创建好所有分区
- 导入 4 亿行历史数据
3 优化过程
3.1 复制新表，按时间批量创建 range 分区
- 导出表结构，创建新表：
CREATE TABLE `testp` (
`id` varchar(8) NOT NULL COMMENT '记录序号',
`passid` varchar(40) DEFAULT NULL COMMENT '用户唯一标识',
`gcpid` varchar(40) DEFAULT NULL COMMENT '如果是一点接入合作伙伴，记录于此字段',
`time` datetime NOT NULL COMMENT '使用请求时间',
`dayid` varchar(8) DEFAULT NULL COMMENT '日期',
`autoid` bigint(20) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`autoid`,`time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='咪咕+业务使用行为日志'
这里有两个点需要注意：
- 如果 primary key 还是原来的 PRIMARY KEY (
autoid) 创建分区会报错，提示every unique key on the table must use every column in the table's partitioning expression。这里参考 mysql 官方文档: Partitioning Keys, Primary Keys, and Unique Keys
All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.
意思是 分区表达式里面涉及到的列，必须在表中有唯一性配置，可以是 primary key 或 unique key 。
- ENGINE=MyISAM 创建分区时会报错，换成 InnoDB 就可以了。
表创建好之后，利用脚本提前批量创建好所有的分区
#!/bin/sh
# author: Chen Yao
export LANG=en_US.UTF-8
# mysql 连接配置
mysql_host=
mysql_port=
mysql_user=
mysql_db=
# 分区相关配置
table=testpp;
# 待分区列
partition_column=time
# 分区开始结束时间，建议单表分区数不超过 1024
startDay=20180711
end=20190110
start=$startDay
function execute_sql(){
export MYSQL_PWD=
mysql -h${mysql_host} -P${mysql_port} -u${mysql_user} -D${mysql_db} --default-character-set=utf8 -e "
set names utf8mb4;
$1"
}
# 分区索引数值
while [ ${start} -le ${end} ]
do
echo ${start}
if [ ${start} -eq $startDay ];then
# 注意：无分区新表第一次创建分区
sql="alter table $table PARTITION BY RANGE(to_days($partition_column)) (PARTITION p${start} VALUES LESS THAN (to_days('$start')));"
else
# 创建分区之后新增分区 add partition
sql="alter table $table add PARTITION (PARTITION p${start}VALUES LESS THAN (to_days('$start')));"
fi
execute_sql $sql
# 日期自增
start=`date -d "1 day ${start}" +%Y%m%d`
done
3.2 数据 load 到新表
分区创建好之后可以往里面灌历史数据了，有两种灌数据的方式：
1.mysqldump 源表之后，修改里面的表名，重新 import 进去。
2.利用调度系统按天导入数据
笔者在尝试第一种方式失败后（莫名其妙），采用第二种，利用调度系统逐天导入，经过几个小时成功将数据导入。
3.3 查询验证
分区创建好之后我们经过简单的验证，可以看出，查询落到对应的分区去了。
image.png
数据灌入之后，查询一个月的跨度，2 秒内就出结果了。
