mysql建表及数据重刷对比-CSDN博客

本文链接：https://blog.csdn.net/MusicDancing/article/details/135297153

1. mysql 设置及优化

通过某一台安装mysql环境的机器，进入mysql客户端：

mysql -h host -u root -P port -p password db_name
mysql -h17.9.9.16 -uroot -P3306 -proot finger_print

注意：mysql时区设置的是utc，数据库默认字符集是utf8mb4。

1. 设置最大连接数：

使用数据库连接池，设置20-100个连接即可(每日调用20W，qps=40)。

2. mysql 性能优化：

1. 分表：gk, hv, 设备信息分表；将仅需存储而无需返回的字段存入日志，后续采入hive；

2. 为查询字段建索引(包括fid，gk等)；
3. hash存储成数字而非text，从256位降低至64位；
4. 优化数据库连接方式为使用数据库连接池方案，用于处理线上高并发场景。

2. mysql数据库建表

1. 指纹数据库表

CREATE DATABASE finger_print;  -- 创建指纹库

CREATE TABLE `xx_device_fingerprint_gk` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键 id',
    `fid` bigint(20) DEFAULT NULL COMMENT '指纹 ID',
    `gk1` bigint(20) DEFAULT NULL,
    `gk50` bigint(20) DEFAULT NULL,
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='设备指纹中间表';

-- 创建索引
CREATE INDEX fid_inx ON xx_device_fingerprint_gk (fid);
CREATE INDEX gk1_inx ON xx_device_fingerprint_gk (gk1);
CREATE INDEX gk35_inx ON xx_device_fingerprint_gk (gk35);
CREATE INDEX update_time_inx ON xx_device_fingerprint_gk (update_time);
-- 查看索引
show index from xx_device_fingerprint_gk;

2. 指纹查找结果表

CREATE TABLE `xx_device_fingerprint_report_info_ext` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键 id',
    `msg_id` varchar(64) DEFAULT NULL COMMENT '消息数据 id, 唯一',
    `msg_upload_time` varchar(20) DEFAULT NULL COMMENT '报文上次时间',
    `fid` bigint(20) DEFAULT NULL COMMENT '指纹ID',
    `code` bigint(20) DEFAULT NULL COMMENT '请求返回状态(0:成功，其他：失败)',
    `res1` varchar(50) DEFAULT NULL COMMENT '结果',
    `res10` varchar(50) DEFAULT NULL COMMENT '结果',
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='设备报文信息结果表'
;
CREATE INDEX fid_inx ON xx_device_fingerprint_report_info_ext (fid);
CREATE INDEX msg_id_inx ON xx_device_fingerprint_report_info_ext (msg_id);
CREATE INDEX update_time_inx ON xx_device_fingerprint_report_info_ext (update_time);

3. 加工特征表

CREATE TABLE `xx_device_fingerprint_tongdun_info` (
    `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键 id',
    `msg_id` varchar(64) DEFAULT NULL COMMENT '消息数据 id, 唯一',
    `fid` bigint(20) DEFAULT NULL COMMENT '指纹ID',
    `Apply_at_suspicous_time` varchar(50) DEFAULT NULL COMMENT '敏感时间段申请',
    `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COMMENT='设备报文特征表';

CREATE INDEX fid_inx ON xx_device_fingerprint_tongdun_info (fid);
CREATE INDEX msg_id_inx ON xx_device_fingerprint_tongdun_info (msg_id);

4. 指纹hash value 表

3. 部分表的查改删

-- 子指纹命中结果查找
select res1, res2 from xx_device_fingerprint_report_info_ext limit 5;
-- 删除指定指纹ID的记录
delete from xx_device_fingerprint_gk where fid='123';

4. 数据重刷

4.1 TD数据处理与探索

拿到TD数据，解析，探索，筛选关键因子，设计算法，搭建服务，并下载一份TD数据，预处理成每行一个json的格式。

注意：去除数据，TD id为空等。

4.2 清空测试表

数据重刷时需要先清空数据表

truncate table finger_print.xx_device_fingerprint_gk;
truncate table finger_print.xx_device_fingerprint_report_info_ext;
truncate table finger_print.xx_device_fingerprint_tongdun_info;
truncate table finger_print.xx_device_fingerprint_hv;

然后清空服务日志文件

echo "" > fid_info.log
echo "" > msg_info.log
echo "" > res_info.log
echo "" > user.log

4.3 执行重刷

启动服务，执行批量重刷

4.4 分析处理结果

1. hue分析

将fid_info.log上传至hue，并建表分析；

2. python分析

fid_info.log 转换为 fid_info.csv(json --> 文本)；

fid-did-pair.csv 为根据fid,deviceid去重后的文件。

import pandas as pd
from collections import Counter
pd.set_option('display.max_columns', None)
df1 = pd.read_csv('fid_info.csv', names=['fid', 'deviceid', 'msg_upload_time',  
    'user_id', 'android_id', 'model', 'total_storage', 'available_storage'])
df2 = pd.read_csv('fid-did-pair.csv', names=['fid', 'deviceid'])
# 统计出一个deviceid对应多个fid的case
counter = Counter([item for item in df2['deviceid']])
counter_new = dict(filter(lambda x: x[1] >= 2, counter.items()))
print(counter_new)
print(len(counter_new))
# 计算对应deviceid个数
df = df2[df2['fid'].isin(counter_new.keys())]
counter = Counter([item for item in df2['deviceid']])
print(len(counter))