mysql建表及其相关操作

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, 设备信息分表；
2. hash存储成数字而非text；
3. hash长度256降低64；

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. 清空测试表

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

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. 部分表的查改删

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

11