近段时间负责开发一个微信项目的监控功能。由于项目启动时,就使用mysql的文本类型来存储微信端返回的json格式、xml格式的数据,导致mysql很难高效的提取出对应的报表数据。一开始为了快速实现demo,就先使用了临时解决方案:
a.给表结构添加关键字段
b.遍历所有json、xml字符串,提取关键数据到新字段(非常耗时)
c.根据新字段写sql,再通过explain优化性能。
功能虽然做出来了,但是问题来了,如果之后又有了新的报表需求,岂不是又得重复a b c吗?所以就引出了数据库选型问题。
据我所知,对于存储json格式的数据,MongoDB看起来或许是比较合适的。后来内部讨论,决定让我负责尝试MongoDB。
首要的问题,就是把mysql原有的数据导入MongoDB中。在网上查阅了mysql导入MongoDB的相关资料,感觉没有什么拿来即用的工具,最后决定自己写导入脚本。
为了对比性能,我写了PHP跟GO两种版本,在我本地测试感觉不出来GO的优势(可能还有优化的空间),导入操作应该属于IO密集型,性能瓶颈不在于语言层面,所以就先使用PHP了。之后有时间可以再写一篇GO导入的文章。
先上一段PHP脚本的核心代码:$mysqli = new mysqli('localhost', 'root', 'root', 'weixin_monitor');
$result = $mysqli->query('set names utf8');
$sql = 'select id from w_log order by id desc limit 1';
$result = $mysqli->query($sql);
$maxId = $result->fetch_assoc();
$maxId = $maxId['id'];//最大ID
$id = 0;//初始ID
$leng = 5000;//一次从mysql中读取的数据量
while( $id
$sql = "select * from w_log where id > $id order by id asc limit $leng";
$result = $mysqli->query($sql);//从mysql中读数据
while( $row = $result->fetch_assoc() ) {
//mysql数据处理操作
$id = $row['id'];//当前ID
}
//导入MongoDB操作
}
代码简单分析:
1)先找出数据库中最大ID
2)根据当前ID与最大ID判断是否继续执行3) 4)
3)从mysql中读取数据并处理,更新当前ID
4)数据批量导入MongoDB
从mysql中读数据
这一块是最容易出现性能瓶颈的地方。我本地测试环境单表是130W行数据,需要以分段的方式(分页),把数据导出。如果使用如下的sql语句获取数据,读到越后边的行,就会越慢。select * from w_log limit x,y
改良后的sql如下,理论上,读到上亿条数据都不会变慢。有兴趣可以看一下我之前写的的《MariaDB数据库优化,实现百万级数据环境快速翻页》。select * from w_log where id > $id order by id asc limit $leng
接下来分别是把json字符串、xml字符串转换成PHP数组。
1)json字符串转PHP数组<?php
$strJson = '{"list" :[{"date":"2015-08-25"},{"date":"2015-08-26"}]}';
$arrData = json_decode($strJson,true);
print_r($arrData);
2)xml字符串转PHP数组<?php
$xml = <<
XML;
$xml_parser = xml_parser_create();
if(!xml_parse($xml_parser,$xml,true)){
xml_parser_free($xml_parser);
$arrData = [];
} else {
$arrData = simplexml_load_string($xml, 'SimpleXMLElement', LIBXML_NOCDATA);
$arrData = json_decode( json_encode( $arrData), true);
}
print_r($arrData);
最后是PHP数组批量导入MongoDB$conn = new MongoClient();//链接MongoDB
$db = $conn->test_db;//选择要操作的数据库
$collection = $db->log;//选择要操作的文档集,可以理解为mysql的表
$collection->batchInsert($arrData);//批量插入PHP数组数据