一。当前所面临的问题
随着互联网的发展,数据量越来越大,既使分库分表后,单表数据量上千万都是很正常,很多业务表在未归档前都是要到几十亿以上,多个这样的业务表存在,到TB级非常正常。但业务的变化也是很快,后面的业务可能就会将分库分表的核心字段丢弃,那就会面临着,没有分库分表字段的存在,却要在几十亿的分库分表中查找,这些查找往往不是分库分表字段,甚至索引列都不是。采用大数据或搜索引擎,甚至一些自己优化的方式,都面临着各式各样的问题,本文将谈论现有的解决方案及优劣,然后给出一个新的方案,当然这个新方案也是有其局限所在。
二。现有可行的解决方案的思考
1.大数据
大数据解决方案基本上是存在放在HDFS(Hadoop Distributed FileSystem)上面
问题1:当然现在也有人使用spark+nosql,但我们都知道NOSQL也其实要有4种不同的类型,总有得失,同时内存的容量也是有限,不适应于大表(比如表有分库且库的量大,总体上量大)
问题2:当然还有人说Presto,将数据读到内存上,使用场景也受限于内存
总结:直接使用hadoop,适合报表相关的(T-1),使用spark或presto适合内存容器可控的计算场景
2.搜索引擎
搜索引擎基本原理是以空间换时间 ,这个空间换时间,就是在对应的分词下建索引
问题1:搜索引擎需要数据来源,最优雅的方式就是从数据库的BINLOG自动写入搜索引擎,这个过程会有一定的延迟(有些资源和架构足够好,可以减少到分钟级以下)
问题2:索引,使用了搜索引擎,同样也绕不开索引,如果没有建立索引,同样也会面临着同样问题,但搜索引擎基本上都支持动态引擎,但也这需要有运维动作才能(一般都不支持自动无脑创建,要不然磁盘空间会有很大的问题)
总结:适合对实时有要求,同时也有计划且能够按节奏进行的项目,并且有封装好的搜索引擎组件的场景使用
3.分库分表的组件个性化定制
这个要说到一个区间遍历的概念了:比如说我有100万数据,我要先知道指定字段的最小和最大数据,我按指定的字段>=? and <?的试试拆成多次,直到最大数据
大致市场上分为二种通过proxy的方式(比如mycat)和内嵌在客户端(比如:shardingJdbc)的二种方式,在此二种方式中可以做一些有序的取号服务的提前规划,从而在分库分表中有唯一主键,然后借用分库分表的组件,并使用此唯一主键区间遍历进行并行访问(fork->join)
问题1:看到用了全局唯一取号的字段后,可能有些人会想为什么不用时间区间遍历,因为同一秒或甚至同一毫秒都有可能被刷成大量的数据,依然会造成数据库不走索引的原理触发,按照互联网的小步快跑的思路,是不理想的,有风险。
问题2:因为分库分表的存在,全局唯一有序的取号实际上是每个表是跳号的,通过分库分表的组件,一旦甚至没有分库分表字段,就会根据所配置的分库分表的量去并行执行,这样的后果是大量的SQL执行,会产生费很大力气,才得到与之不配对的结果,浪费了一些性能开销,同时无脑的并行,在生产上也发现线程池里的排队情况,会引发新一轮的无效连接问题
问题3:全局唯一有序它不是聚合索引,所以它并不是最优的
总结:此方案与新的方案其实非常相似,这里面是部门内多个同事在项目中的实践中慢慢提炼出来的
三。新的方案
新的方案并不是代替上面所有的方案,只是提供新的一种选择,此方案封装的细节如下:
1.通过自已体系的代码生成在DAO模板中增加API
2.结合第二点的第三点,我们采用了数据库表的主键,然后通过主键的升降序分页的第1条,轻易无伤的获取整个表的最大和最小ID
3.我们通过count(*)的方式,按10000一个步长(目前测试来看10000的步长,比较中和)根据条件(此条件会涉及到一些自己系统的封装,我们采用的类似于hiberate面对象的方式,可以很轻易在在对象转成SQL时,植入id>=? and i<?,不同系统可以各自的方案去解决,但万变不离其中,都是按ID的步长去查找数据
4.当我们查到数据,会将count(*)的总量与指定的量相对比,如果小于指定量,就添加指定量的分页条件,仅仅获取指定量的数据,如果没有达到,继续区间遍历,积累到指定的量,再返回
总结一下:
上面的方案都是针对于单库的,既然是分库分表,为什么会涉及到单库,因为所有的优化到最后,基本上都是会有自己去控制每一步的消耗,使用方如果想并行,就自己启线程并行fork->join,使用方主想控制主机实例或进程的CPU使用率,可以自己单库循环,从封装的角度上来讲,不做过度封装,交给使用方决定。
见代码模板API(已体现出主要功能)
/**
* 根据condition按id区间循环查找(定制应用)
* @param condition
* @param dbNo
* @param minId
* @param maxId
* @param appointMaxSize
* @return
*/
public List<${beanName}> queryCirculationOfCustomByCondition(Condition condition, String dbNo, Long minId,
Long maxId, int appointMaxSize) {
String conditionWhere = ConditionParseUtil.getSqlWhere(condition);
List<Object> conditionParams = ConditionParseUtil.getParams(condition);
if (appointMaxSize > SqlDaoImpl.MAX_COUNT) {
log.warn(LoggerFormatUtil
.toStrByMsgParams("queryCirculationOfCustomByConditionException", "tableName", getTableName(),
"dbNo", dbNo, "where", conditionWhere, "param", conditionParams, "appointMaxSize",
appointMaxSize));
throw new JobRuntimeException("指定数量超过" + SqlDaoImpl.MAX_COUNT + "量!");
}
List<${beanName}> allList = ListUtil.newArrayList();
if (minId == null) {
minId = sqlDao.queryMinIdBy(getTableName(), dbNo);
}
if (maxId == null) {
maxId = sqlDao.queryMaxIdBy(getTableName(), dbNo);
}
int circulationCount = 0;
List<${beanName}> resultList = null;
boolean isEnd = false;
log.info(LoggerFormatUtil
.toStrByMsgParams("queryCirculationOfCustomByConditionStart", "tableName", getTableName(), "dbNo", dbNo,
"where", conditionWhere, "param", conditionParams, "minId", minId, "maxId", maxId,
"appointMaxSize", appointMaxSize));
for (long j = minId; j < maxId; j += SqlDaoImpl.BATCH_COUNT) {
circulationCount++;
//copy condition
Condition tempCondition = new Condition(condition);
tempCondition.add(RestrictionUtil.ge(BaseDomainDefinition.id, j));
tempCondition.add(RestrictionUtil.lt(BaseDomainDefinition.id, j + SqlDaoImpl.BATCH_COUNT));
String whereStr = ConditionParseUtil.getSqlWhere(tempCondition);
List<Object> params = ConditionParseUtil.getParams(tempCondition);
int count = sqlDao.queryCountByTableAndWhereStr(getTableName(), dbNo, whereStr, params);
if (count > 0) {
if(count>appointMaxSize){
PageCondition tempPageCondition = new PageCondition(tempCondition,null,appointMaxSize);
resultList = queryByPageCondition(tempPageCondition,dbNo);
}else{
resultList = queryByCondition(tempCondition, dbNo);
}
log.info(LoggerFormatUtil
.toStrByMsgParams("queryCirculationOfCustomByConditionFoundData", "tableName", getTableName(),
"dbNo", dbNo, "where", whereStr, "param", params, "minId", minId, "maxId", maxId,
"foundSize", resultList.size(), "size", allList.size(), "appointMaxSize",
appointMaxSize, "circulationCount", circulationCount));
for (${beanName} obj : resultList) {
allList.add(obj);
if (allList.size() >= appointMaxSize) {
isEnd = true;
break;
}
}
}
if (isEnd) {
break;
}
}
log.info(LoggerFormatUtil
.toStrByMsgParams("queryCirculationOfCustomByConditionEnd", "tableName", getTableName(), "dbNo", dbNo,
"where", conditionWhere, "param", conditionParams, "minId", minId, "maxId", maxId, "size",
allList.size(), "appointMaxSize", appointMaxSize, "circulationCount",
circulationCount));
return allList;
}
四。性能测试
只谈方案,不给出实际测试,这不是耍流氓,当然要给出来
场景 xxx单表数据量有441万(它真实是拆成128个表5.6亿数据) 查找的字段没有索引 `process_flag` = ?: 指定查找1000条
最终用了241毫秒 查了数据库442次,真正找到符合条件的数据是3次,获取到 273笔数据
[2018-07-25 21:12:09.828] [INFO] [main] [c.v.xxx.yyy.zz.fix.main.quality.ZZTestQualityMain] >>> arIntRecInList1.size:1
[2018-07-25 21:12:09.829] [INFO] [main] [c.v.f.yyy.zz.intfc.dao.base.BaseZZIntRecInDaoImpl] >>> [queryCirculationOfCustomByConditionStart] json={} msg={"tableName":"xxx","dbNo":"127","where":" 1=1 and `source_name` = ? and `process_flag` = ?","param":["ORDER","N"],"minId":3028541,"maxId":7443503}
[2018-07-25 21:12:10.015] [INFO] [main] [c.v.f.yyy.zz.intfc.dao.base.BaseZZIntRecInDaoImpl] >>> [queryCirculationOfCustomByConditionFoundData] json={} msg={"tableName":"xxx","dbNo":"127","where":" 1=1 and `source_name` = ? and `process_flag` = ? and `id` >= ? and `id` < ?","param":["ORDER","N",6848541,6858541],"minId":3028541,"maxId":7443503,"foundSize":93,"size":0,"circulationCount":383}
[2018-07-25 21:12:10.023] [INFO] [main] [c.v.f.yyy.zz.intfc.dao.base.BaseZZIntRecInDaoImpl] >>> [queryCirculationOfCustomByConditionFoundData] json={} msg={"tableName":"xxx","dbNo":"127","where":" 1=1 and `source_name` = ? and `process_flag` = ? and `id` >= ? and `id` < ?","param":["ORDER","N",6858541,6868541],"minId":3028541,"maxId":7443503,"foundSize":15,"size":93,"circulationCount":384}
[2018-07-25 21:12:10.069] [INFO] [main] [c.v.f.yyy.zz.intfc.dao.base.BaseZZIntRecInDaoImpl] >>> [queryCirculationOfCustomByConditionFoundData] json={} msg={"tableName":"xxx","dbNo":"127","where":" 1=1 and `source_name` = ? and `process_flag` = ? and `id` >= ? and `id` < ?","param":["ORDER","N",7438541,7448541],"minId":3028541,"maxId":7443503,"foundSize":165,"size":108,"circulationCount":442}
[2018-07-25 21:12:10.069] [INFO] [main] [c.v.f.yyy.zz.intfc.dao.base.BaseZZIntRecInDaoImpl] >>> [queryCirculationOfCustomByConditionEnd] json={} msg={"tableName":"xxx","dbNo":"127","where":" 1=1 and `source_name` = ? and `process_flag` = ?","param":["ORDER","N"],"minId":3028541,"maxId":7443503,"size":273,"circulationCount":442}
[2018-07-25 21:12:10.069] [INFO] [main] [c.v.xxx.yyy.zz.fix.main.quality.ZZTestQualityMain] >>> xxx.size:273
总结:此次测试虽然是单线程去做,但尝试过50个线程去访问MYSQL,MYSQL物理机各项指标均无明显变化,这种对数据库的伤害是非常小的
五。新方案的适用场景
1.整个条件中不含索引,且有索引的条件并没有产生效果
2.数据量单表至少要超过100万以上(小提示:对于百万级以上的表,不走索引的SQL,绝对是分分钟上数据库服务器CPU报警的)
3.可以容忍几百毫秒-5秒左右时间等待的使用
vipshop_ebs/朱杰
2018-12-28