# database

**Repository Path**: dxpanpan/database

## Basic Information

- **Project Name**: database
- **Description**: No description available
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2017-11-04
- **Last Updated**: 2020-12-19

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

社工库整理
===

本文是在整理社工库时的一些想法和经历的记录, 偏向于工作日记, 所以有很多地方顺序不太清晰,
但这正是开始一个新项目从设计到动手到修改设计到怀疑人生的一个心路历程, 也可以作为代码的补充.

最近在对现有的一些社工库进行数据清洗,去重,格式化. 最终以SQL数据库的格式保存.
目前的想法是, 先手动进行清洗, 数据库接口用sqlalchemy中间件来访问,
以便于兼容市面上大多数SQL引擎, 如MySQL, PregreSQL, SQLite3, 等等.

入库方式有两种:

1. 先生成清理好后的社工库txt文件, 手动建立库和表, 然后手动插入各个库的数据,
最后用数据库的export接口导出为`.sql`格式文件, 多机部署时只要在对应的数据库进行import即可,
缺点是单个sql文件可能比较大.
2. 先生成清理好后的社工库txt文件,并编写好对应的解析脚本. 多机部署时携带txt文件并运行脚本,
自动建立库表并插入. 缺点是文件总量较大, 优点是可以选择性导入.

查询方式有两种:

1. 直接SQL语句查询
2. 通过ORM接口查询

个人偏向于后者, 一来ORM接口相对安全, 二来后续的数据操作(聚合,统计)也会方便很多.

# 数据库表设计

社工库文件的格式五花八门, 核心是`用户名`+`密码(哈希)`, 但也有许多其他记录, 如手机号, 身份证号,
邮箱, 昵称等独特信息. 从需求上而言, 我们查询社工库目的是为了匹配出某个用户的尽可能多的信息,
所以往往要进行模糊查询, 为了速度上提升, 就要对所有列建立索引. 因此列的数量应该尽量少而且最好统一.
对于单表情况, 理想是用所有社工库列的交集来作为我们表的基础结构, 再新增一列(Detail)存储详细信息.

不过考虑到社工库体量巨大,单表存储从效率上来说让人担忧. 目前不考虑分库的情况下, 较常规的解决方案是
每个社工库用一张表来存储.

# 社工库清洗

在对现有的社工库进行数据清洗时发现了一些比较显著的问题:
1. 首先是数据重复问题, 以3.6G的163裤子为例, 经过排序去重之后, 数据仅有1.3G, 重复数据比例竟达到64%,
多亏了Linux下的`sort`命令, 通过外部R路归并排序, 可以对大文件进行快速并行的排序, 所以这也不算太严重的问题.
2. 其次, 数据质量低下. 有很多裤子, 仅仅通过肉眼就能发现其中大多数是随机生成的非法帐号, 对于这种裤子,
目前的处理方式是一刀切, 直接不要; 但是对于另外一些在有效裤子中插入无效数据的, 就很蛋疼了, 以盛大1000W
的裤子为例, 排序后发现有些帐号随机, 密码仅有一两位数字, 烂芋充数地穿插其间. 目前还没有很好的方式处理这类数据,
就只能将其保留了.
3. 最后, 裤子的格式和编码. 这也是个比较恶心的问题, 同一个裤子, 每一列的的格式居然都有点不同, 比如
有的格式为`帐号----密码`, 有的格式为`帐号 密码`, 还有的甚至列数都不同, 比如如下几行:
```
aass8877aass8877@163.com--------灰溜溜|----
aasd171@163.com----liu_1985----Mo_Liu
aasd1577@163.com--------aasd1577
abeemkx@163.com----abee1985----一只蜜��?$*0
```
可以说是让人莫名其妙, 最后一行还有未知的编码, 这就给数据库格式化带来了一个严峻的问题. 所幸的是,
这类裤子质量也不咋地, 可以先依照少数服从多数的方法, 对于个别奇怪的行可以手工整理或者直接丢弃.

目前希望把手上的裤子尽可能清洗后保存为格式一致的文本文件, 并用UTF-8编码, 以便程序进行读取.

# 载入数据

经过艰苦的数据清洗, 取其精华, 去其糟粕, 最后获得了约13G的文本数据. 但并不意味着数据就是完全干净的,
每个文本少则十几MB, 多则五六GB, 实在没有办法一行行检查. 所以目前的解决方式是在用Python将数据映射到
数据表中时, 对于疑似错误的行, 暂时输出到另外的文件中人工校验, 就像自动存取款机点钞一样, 把褶皱或者
损坏的纸币突出给用户进行检查. 不过这种情况一般只在第一次读取时发生, 因为疑似错误的行会在检查之后进行修正.
也就是说二次清洗后的文件不再有`.success`和`.review`输出, 所以可以考虑去掉对应的记录, 但前提是清洗规则不变.

读取的过程中要特别注意的是换行符的问题, 文件有的是\r\n换行, 有的是\n换行, 还有个别是两者混杂的(都是泪TT).

# 关于索引

为什么要建立索引? 其实这是个算法问题, 对于无序数据的搜索上界是O(N), 而排序后可以达到O(logN),
建立索引可以看作是建立了一份排序后的映射.

索引有两种类型, 一种是每列建立索引, 另一种是建立组合索引, 从我们查找的方式来看, 一般是
`SELECT * FROM table WHERE col1='xxx' OR col2='xxx';`的形式, 组合索引不一定能命中, 详见[MySQL的文档][mysql].

建立索引的时机, 也有两种选择, 一是创建数据库的时候就建立索引, 二是导入完之后再新建索引.
虽然前者的代码量较少, 但从时间上来说可能后者比较好点, 而且可以后续选择性的增加(其他)索引, 详见[文档][index].

当然, 从性能上来说, 如果数据量不大, 索引带来的提升就相对有限, 所以目前在建立数据库的时候没有声明索引,
一来是为了加速导入时间, 二来可以在以后遇到性能瓶颈时再建立以对比一下速度到底能提升多少.

# 关于重复数据

在将各个裤子加载到数据库中时,发现有些`帐号-密码`的数据是重复的, 但是却属于不同的裤子, 猜测有的是通过
撞裤的方式获取的, 对于这类数据其实可以在程序上去除重复, 但考虑到要保留来源信息, 还是将这些数据保留在
不同的表里, 也许性能会有略微损耗, 但为了保留了完整性也算是值得.

# 内存泄漏

在使用SQLAlchemy的时候, 发现如果session没有及时commit, 会导致数据一直驻留在内存里, 从而内存暴涨
导致我的电脑卡死. 后来改成每写一条数据commit一次, 又似乎没有充分利用CPU的性能. 权衡之下, 决定以
1000~8000条记录为单位进行bulk assert, 以达到一个较优的速度.

经测试每1000条记录commit一次可以使单核CPU(i7-4710MQ CPU @ 2.50GHz)使用率在30%左右, 当然这也和连接
数据库的网络速度或者磁盘速度(SQLite3)有关.

# 并行加载

写好加载脚本, 载入数据库的时候, 感受就是一个字--慢. 读取十几MB的小裤子都要好几分钟, 对于上G的裤子的
加载速度可想而知了. 所以第一反应就是上多线程.

然而事与愿违, 由于Python固有的GIL, 使用多线程来加载不同的数据库也无法有效使用多核CPU资源,
而是将单核占满在100%, 简单的方法就是用多进程的方式去加载, 从结果来看这种方法也很有效,
当然前提是数据库后端能支持并发, 比如SQLite3这种就不行了.

在等待加载的时间里, 可以编写其他loader脚本和测试, Flask的前后端, 和这篇笔记. 一寸光阴一寸金啊.

# 单元测试

```bash
python -m unittest discover -s tests
```

# 启动

1. 加载所有数据

```bash
python loader.py
```

2. 启动web页面

```bash
python webapp.py
```

# 清洗进度

- [x] `12306`
- [x] `wangyi_100w`
- [x] `mao_1000w`
- [x] `shengda_1000w`
- [x] `178com_1000w`
- [x] `csdn_600w`
- [x] `duowan_800w`
- [x] `7k7k_2000w`
- [x] `duduniu`
- [x] `17173com`
- [x] `tianya_4000w`
- [x] `163com`
- [x] `163mail`

# 运行示例

![demo](demo.gif)

[index]:http://docs.sqlalchemy.org/en/latest/core/constraints.html#indexes
[mysql]:https://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html