向大数据数据库中插入值时，还要判断插入是否重复，然后插入。如何提高效率

向大数据数据库中插入值时，还要判断插入是否重复，然后插入。如何提高效率。
每次insert时还要select下，现在数据库里的数据已有几十万条，这样批量插入的时候效率就会很低，不得不这么做，谁有好的方法。
想过把关键内容取来放在map或者list里，然后每次从集合里判断，可这样内存就吃不消了。
...........

评论

83 楼 wl95421 2008-09-16  

hallywang 写道

elmar 写道

wl95421 写道

elmar 写道

wl95421 写道

先说明我不是DBA，所以给的方案可能不一定符合数据库设计的美观。
我觉得如果有重复，那么重复的字段应该与业务有关
完全可以根据业务字段分区，如AB开头的数据放在Test_AB中，或者是用类HashCode的算法进行分类，然后估算规模，完全可以将一张表的数据控制在一个理想的范围。
如果数据增长超出预期，那么可以考虑迁移，重做数据库。（细节不讨论）
如果是我做的话，加个Lucene分段索引（只对指定字段索引），多了不好说，做个亿级的，速度应该没有大的问题。（如果再多，加几台机器分区处理了。）

其实最根本的东西，还是你的规模估算。

有这么复杂吗？这是一个简单的数学问题而不是一个复杂的工程问题

就象如果你要做一个1万页Html的搜索引擎，还是1万亿页Html的搜索引擎。
前者对于很多程序员都不是一个问题，一个Lucene就能解决，再直接点，用数据库的Clob加个索引都能解决。
如果后者呢，就完全不是这个方案了。
所以规模估算就决定你问题的复杂度。量变引起质变。

lz的问题是“向大数据数据库中插入值时，还要判断插入是否重复，然后插入。如何提高效率”

首先，一个不允许重复的字段加一个unique的index是一个必然的选择。
其次，在一个unique字段上插入，无论是1000条的数据，1M数据，1T数据都没有任何区别——你根本没办法干预数据的行为，你的任何干预都只会降低效率。

不知道Lucence怎么被扯进来了？

就是啊。什么都出来了，就几十万的数据，有必要整那么复杂嘛，是不是也得考虑解决问题的效率。。。。
最后搞一个完美方案，一年都过去了。。。。

我不是都说了吗，其实最根本的东西，还是规模估算。
如果数据量小的话，怎么处理都无所谓，但是现在的数据库已经有几十万条了，如果它的增长量很快，而且重复率比较高，那么就不是一个简单的方案了。
至于说到lucene，其实很简单，因为我觉得根本的问题在于:如何快速判断主键重复，可以演绎成一个如何快速搜索字段的问题，那么Lucene可以解决这个问题，所以我说了可以使用Lucene。
至于Lucene，也远谈不上完美，我只是给出自己的思路，如果真的达到百万甚至更多，那么只使用数据库，就未必是个好的方案。大家不是一定要将精力放在数据库处理或者异常上，而是根据自己实际的情况来选择方案。

82 楼 hallywang 2008-09-16  

elmar 写道

wl95421 写道

elmar 写道

wl95421 写道

先说明我不是DBA，所以给的方案可能不一定符合数据库设计的美观。
我觉得如果有重复，那么重复的字段应该与业务有关
完全可以根据业务字段分区，如AB开头的数据放在Test_AB中，或者是用类HashCode的算法进行分类，然后估算规模，完全可以将一张表的数据控制在一个理想的范围。
如果数据增长超出预期，那么可以考虑迁移，重做数据库。（细节不讨论）
如果是我做的话，加个Lucene分段索引（只对指定字段索引），多了不好说，做个亿级的，速度应该没有大的问题。（如果再多，加几台机器分区处理了。）

其实最根本的东西，还是你的规模估算。

有这么复杂吗？这是一个简单的数学问题而不是一个复杂的工程问题

就象如果你要做一个1万页Html的搜索引擎，还是1万亿页Html的搜索引擎。
前者对于很多程序员都不是一个问题，一个Lucene就能解决，再直接点，用数据库的Clob加个索引都能解决。
如果后者呢，就完全不是这个方案了。
所以规模估算就决定你问题的复杂度。量变引起质变。

lz的问题是“向大数据数据库中插入值时，还要判断插入是否重复，然后插入。如何提高效率”

首先，一个不允许重复的字段加一个unique的index是一个必然的选择。
其次，在一个unique字段上插入，无论是1000条的数据，1M数据，1T数据都没有任何区别——你根本没办法干预数据的行为，你的任何干预都只会降低效率。

不知道Lucence怎么被扯进来了？

就是啊。什么都出来了，就几十万的数据，有必要整那么复杂嘛，是不是也得考虑解决问题的效率。。。。
最后搞一个完美方案，一年都过去了。。。。

81 楼 elmar 2008-09-16  

wl95421 写道

elmar 写道

wl95421 写道

先说明我不是DBA，所以给的方案可能不一定符合数据库设计的美观。
我觉得如果有重复，那么重复的字段应该与业务有关
完全可以根据业务字段分区，如AB开头的数据放在Test_AB中，或者是用类HashCode的算法进行分类，然后估算规模，完全可以将一张表的数据控制在一个理想的范围。
如果数据增长超出预期，那么可以考虑迁移，重做数据库。（细节不讨论）
如果是我做的话，加个Lucene分段索引（只对指定字段索引），多了不好说，做个亿级的，速度应该没有大的问题。（如果再多，加几台机器分区处理了。）

其实最根本的东西，还是你的规模估算。

有这么复杂吗？这是一个简单的数学问题而不是一个复杂的工程问题

就象如果你要做一个1万页Html的搜索引擎，还是1万亿页Html的搜索引擎。
前者对于很多程序员都不是一个问题，一个Lucene就能解决，再直接点，用数据库的Clob加个索引都能解决。
如果后者呢，就完全不是这个方案了。
所以规模估算就决定你问题的复杂度。量变引起质变。

lz的问题是“向大数据数据库中插入值时，还要判断插入是否重复，然后插入。如何提高效率”

首先，一个不允许重复的字段加一个unique的index是一个必然的选择。
其次，在一个unique字段上插入，无论是1000条的数据，1M数据，1T数据都没有任何区别——你根本没办法干预数据的行为，你的任何干预都只会降低效率。

不知道Lucence怎么被扯进来了？

80 楼 wl95421 2008-09-16  

elmar 写道

wl95421 写道

先说明我不是DBA，所以给的方案可能不一定符合数据库设计的美观。
我觉得如果有重复，那么重复的字段应该与业务有关
完全可以根据业务字段分区，如AB开头的数据放在Test_AB中，或者是用类HashCode的算法进行分类，然后估算规模，完全可以将一张表的数据控制在一个理想的范围。
如果数据增长超出预期，那么可以考虑迁移，重做数据库。（细节不讨论）
如果是我做的话，加个Lucene分段索引（只对指定字段索引），多了不好说，做个亿级的，速度应该没有大的问题。（如果再多，加几台机器分区处理了。）

其实最根本的东西，还是你的规模估算。

有这么复杂吗？这是一个简单的数学问题而不是一个复杂的工程问题

就象如果你要做一个1万页Html的搜索引擎，还是1万亿页Html的搜索引擎。
前者对于很多程序员都不是一个问题，一个Lucene就能解决，再直接点，用数据库的Clob加个索引都能解决。
如果后者呢，就完全不是这个方案了。
所以规模估算就决定你问题的复杂度。量变引起质变。

79 楼 ft28 2008-09-16  

tou3921 写道

cuiyi.crazy 写道

我觉得这样的数据导入处理问题，没有必要自己写程序还要控制异常的；
最好用ETL，画好流程图，然后直接调用bat或者sh脚本，啥事都不用管，而且还能记录Log；
我用的是Kettle，最后的一步不是insert也不是update，而是insert/update;
我没搞过楼主说的那么大的数据量，我的就是几千条，但每条涉及16个表的插入和更新，速度还满意。


看了这么多回复，我比较看好 抛出异常的爱 和 wolfbrood提到的做法：
1) insert into temp table
2) 在temp table增加一个flag，并设置初始值: isRepeated=0
3) 然后select并把已经存在的标记改为isRepeated=1
4) 把isRepeated=0的记录insert正式表
5) 删除temp table中的isRepeated=1的记录

当然 也可以像tommy402 说的，可以借助于内存表或者缓存
1）insert之前，把已经存在的记录的关键字都提取到内存表中
2）
insert into a
select * from b
where b.id not exists (select 'x' from b where a.id = b.id)

目前我是采用了捕获异常，唯一索引的方案。
但好久都觉得ETl才更快，我知道ETL可以做很多事情，以前面试一家搞BI的，所以对ETL也了解过，
我用过微软的SSIS，指数初级阶段，对于更复杂的数据转换及其他控制我还不会。可目前恰恰正是我需要的。
我不是拿方案的人，只是建议性的私下摸索，最后还得上头说了算。不过还是学到了很多

删除temp表的记录是否有隐患，删除的记录是否被真正删除掉，是否还要占空间，是否要定期做优化。

78 楼 yaonc 2008-09-16  

几十万的记录不是很大啊，你可以在你的客户端实现比较啊！

77 楼 yunjingxinwu 2008-09-16  

方法2：写trigger，在插入之前做一个查询，如果有就抛弃，没有就插入数据


这个方法比较好,很实用

76 楼 vvgg 2008-09-16  

cuiyi.crazy 写道

我觉得这样的数据导入处理问题，没有必要自己写程序还要控制异常的；
最好用ETL，画好流程图，然后直接调用bat或者sh脚本，啥事都不用管，而且还能记录Log；
我用的是Kettle，最后的一步不是insert也不是update，而是insert/update;
我没搞过楼主说的那么大的数据量，我的就是几千条，但每条涉及16个表的插入和更新，速度还满意。


看了这么多回复，我比较看好 抛出异常的爱 和 wolfbrood提到的做法：
1) insert into temp table
2) 在temp table增加一个flag，并设置初始值: isRepeated=0
3) 然后select并把已经存在的标记改为isRepeated=1
4) 把isRepeated=0的记录insert正式表
5) 删除temp table中的isRepeated=1的记录

当然 也可以像tommy402 说的，可以借助于内存表或者缓存
1）insert之前，把已经存在的记录的关键字都提取到内存表中
2）
insert into a
select * from b
where b.id not exists (select 'x' from b where a.id = b.id)

如果是实时的这样还不如直接select count(*)  然后 insert 快

如果不实时怎么保证数据的一实时性,用户insert 以后,马上可以看到

75 楼 XMLDB 2008-09-16  

楼上这么多讨论有些已经很接近正确的解决方案了：
1。首先导入数据需要经过过滤处理（数据少就DB外处理，数据多就临时表，这点异常的经验是对的）
2。唯一键是必要的，异常处理也是要的

74 楼 elmar 2008-09-16  

wl95421 写道

先说明我不是DBA，所以给的方案可能不一定符合数据库设计的美观。
我觉得如果有重复，那么重复的字段应该与业务有关
完全可以根据业务字段分区，如AB开头的数据放在Test_AB中，或者是用类HashCode的算法进行分类，然后估算规模，完全可以将一张表的数据控制在一个理想的范围。
如果数据增长超出预期，那么可以考虑迁移，重做数据库。（细节不讨论）
如果是我做的话，加个Lucene分段索引（只对指定字段索引），多了不好说，做个亿级的，速度应该没有大的问题。（如果再多，加几台机器分区处理了。）

其实最根本的东西，还是你的规模估算。

有这么复杂吗？这是一个简单的数学问题而不是一个复杂的工程问题

73 楼 tidus2005 2008-09-16  

<pre name='code' class='java'> /**
* 分解采集的数据集,到两个参数组中去 这里要根据主表的ORGIN_ID去进行分析判断
*
* @throws TaskAppConfigException
*/
private void partitionInsertAndUpdateList(Mapping mapping,
List&lt;FieldMapping&gt; fieldMappings, List&lt;Map&gt; insertDatas,
List&lt;Map&gt; updateDatas, List&lt;Map&gt; datas)
throws TaskAppConfigException {
LOG.info(new StringBuffer("★ 开始分析采集到的原始数据. . ."));
long startTimes = System.currentTimeMillis();

insertDatas.clear();
updateDatas.clear();

// TODO 这里fieldMappings.get(0)是个很大的bad smell
String aliasA = mapping.getFieldMappingFieldAAlias();
Table tableA = mapping.getTableByAlias(aliasA);
String aliasB = fieldMappings.get(0).getAlias(
FieldMapping.ATTR_KEY_FIELD_B);
Table tableB = mapping.getTableByAlias(aliasB);

int batchNum = 50;
int startIndex = 1;
DataPage batchPage = new DataPage(startIndex, datas.size(), batchNum);

StringBuffer checkUniqueSqlA = new StringBuffer();
checkUniqueSqlA.append("select nvl2(b.").append(
tableB.getExtSysIdField().getName()).append(
",'1','0') as IS$EXIST from table(str2varlist_order('");

StringBuffer checkUniqueSqlB = new StringBuffer();

Object extSysIdFieldValue = null;
String extSysIdFieldName = aliasA + "$"
+ tableA.getPrimaryKeyField().getName();
Field extSysIdField = tableB.getExtSysIdField();
Map dataRow = null;

for (; batchPage.getPageIndex() &lt;= batchPage.getTotalPages(); batchPage
.setPageIndex(batchPage.getPageIndex() + 1)) {

checkUniqueSqlB = new StringBuffer();

for (int i = (int) batchPage.getStart(); i &lt;= batchPage.getEnd(); i++) {
dataRow = datas.get(i - 1);
// 这里要做数据转换
extSysIdFieldValue = dataRow.get(extSysIdFieldName);
if (Field.FIELD_TYPE_NAME_STRING
.equals(extSysIdField.getType())) {
String tempString = null;
if (extSysIdFieldValue instanceof Number) {
tempString = ((Number) extSysIdFieldValue).toString();
extSysIdFieldValue = tempString;
}
} else if (Field.FIELD_TYPE_NAME_NUMERIC.equals(extSysIdField
.getType())) {
if (extSysIdFieldValue instanceof String) {
extSysIdFieldValue = Long
.parseLong((String) extSysIdFieldValue);
}
}
checkUniqueSqlB.append(extSysIdFieldValue).append(",");
}

checkUniqueSqlB.deleteCharAt(checkUniqueSqlB.length() - 1);
checkUniqueSqlB.append("')) a, ").append(tableB.getName()).append(
" b ");
checkUniqueSqlB.append(" where a.var = b.").append(
tableB.getExtSysIdField().getName()).append("(+)");

if (tableB.getExtSysTypeField() != null) {
checkUniqueSqlB.append(" and b.").append(
tableB.getExtSysTypeField().getName()).append("(+)=")
.append(tableA.getExtSysTypeValue());
}

String sql = checkUniqueSqlA.toString()
+ checkUniqueSqlB.toString();
List&lt;Map&gt; results = this.queryForList(sql);

int i = (int) batchPage.getStart();
for (Map aData : results) {
String isExist = (String) aData.get("IS$EXIST");
if (isExist.equals("0")) {
insertDatas.add(datas.get(i - 1));
} else if (isExist.equals("1")) {
updateDatas.add(datas.get(i - 1));
}
i++;
}
}
// mannul -DtaskID=ftrade$boss$task -DtaskType=DATE_RANGE
LOG.info("★ 已经采集的原始数据分析完毕.【待UPDATE记录共: " + updateDatas.size() + " 条】"
+ "【待INSERT记录共: " + insertDatas.size() + " 条】 【用时"
+ (System.currentTimeMillis() - startTimes) + "毫秒】");
}</pre>
<p> </p>

72 楼 wl95421 2008-09-14  

先说明我不是DBA，所以给的方案可能不一定符合数据库设计的美观。
我觉得如果有重复，那么重复的字段应该与业务有关
完全可以根据业务字段分区，如AB开头的数据放在Test_AB中，或者是用类HashCode的算法进行分类，然后估算规模，完全可以将一张表的数据控制在一个理想的范围。
如果数据增长超出预期，那么可以考虑迁移，重做数据库。（细节不讨论）
如果是我做的话，加个Lucene分段索引（只对指定字段索引），多了不好说，做个亿级的，速度应该没有大的问题。（如果再多，加几台机器分区处理了。）

其实最根本的东西，还是你的规模估算。

71 楼 tou3921 2008-09-14  

cuiyi.crazy 写道

我觉得这样的数据导入处理问题，没有必要自己写程序还要控制异常的；
最好用ETL，画好流程图，然后直接调用bat或者sh脚本，啥事都不用管，而且还能记录Log；
我用的是Kettle，最后的一步不是insert也不是update，而是insert/update;
我没搞过楼主说的那么大的数据量，我的就是几千条，但每条涉及16个表的插入和更新，速度还满意。


看了这么多回复，我比较看好 抛出异常的爱 和 wolfbrood提到的做法：
1) insert into temp table
2) 在temp table增加一个flag，并设置初始值: isRepeated=0
3) 然后select并把已经存在的标记改为isRepeated=1
4) 把isRepeated=0的记录insert正式表
5) 删除temp table中的isRepeated=1的记录

当然 也可以像tommy402 说的，可以借助于内存表或者缓存
1）insert之前，把已经存在的记录的关键字都提取到内存表中
2）
insert into a
select * from b
where b.id not exists (select 'x' from b where a.id = b.id)

目前我是采用了捕获异常，唯一索引的方案。
但好久都觉得ETl才更快，我知道ETL可以做很多事情，以前面试一家搞BI的，所以对ETL也了解过，
我用过微软的SSIS，指数初级阶段，对于更复杂的数据转换及其他控制我还不会。可目前恰恰正是我需要的。
我不是拿方案的人，只是建议性的私下摸索，最后还得上头说了算。不过还是学到了很多

70 楼 cuiyi.crazy 2008-09-14  

我觉得这样的数据导入处理问题，没有必要自己写程序还要控制异常的；
最好用ETL，画好流程图，然后直接调用bat或者sh脚本，啥事都不用管，而且还能记录Log；
我用的是Kettle，最后的一步不是insert也不是update，而是insert/update;
我没搞过楼主说的那么大的数据量，我的就是几千条，但每条涉及16个表的插入和更新，速度还满意。


看了这么多回复，我比较看好 抛出异常的爱 和 wolfbrood提到的做法：
1) insert into temp table
2) 在temp table增加一个flag，并设置初始值: isRepeated=0
3) 然后select并把已经存在的标记改为isRepeated=1
4) 把isRepeated=0的记录insert正式表
5) 删除temp table中的isRepeated=1的记录

当然 也可以像tommy402 说的，可以借助于内存表或者缓存
1）insert之前，把已经存在的记录的关键字都提取到内存表中
2）
insert into a
select * from b
where b.id not exists (select 'x' from b where a.id = b.id)

69 楼 fzhq1970 2008-09-14  

采用 SELECT count(*) 为最佳选择

68 楼 tibetjungle 2008-09-13  

tou3921 写道

向大数据数据库中插入值时，还要判断插入是否重复，然后插入。如何提高效率。
每次insert时还要select下，现在数据库里的数据已有几十万条，这样批量插入的时候效率就会很低，不得不这么做，谁有好的方法。
想过把关键内容取来放在map或者list里，然后每次从集合里判断，可这样内存就吃不消了。
...........

这个应用的上下文太不明确了，目标表是否有主键？如果没有主键，重复的判断标准是什么？一条记录占用的最大的存储空间是多少？

再说提高效率的问题。什么才是你要的效率啊？是1min插入100条还是1s插入1000条？现在的情况是什么样？没有一个目标值，根本也就谈不上优化。如果明确给出一个目标值，你用select before insert可能已可以达到你的目标了。

67 楼 tibetjungle 2008-09-13  

抛出异常的爱 写道

tou3921 写道

向大数据数据库中插入值时，还要判断插入是否重复，然后插入。如何提高效率。
每次insert时还要select下，现在数据库里的数据已有几十万条，这样批量插入的时候效率就会很低，不得不这么做，谁有好的方法。
想过把关键内容取来放在map或者list里，然后每次从集合里判断，可这样内存就吃不消了。
...........

插入一个临时表.....
之后用一个job
当有重复不动
无重复,插入并删除


这个东西很好写,比java用网络来端口通信快多了.
我以前也写过

不错的方法。不过这种方法适用于数据量渐增的模式，当一次性有大量数据insert并且需要判断有无重复时，并不是很好的方法吧？

66 楼 王者之剑 2008-09-13  

为什么经常看到一帮人讨论不是问题的问题？
主机配置没有：1.0GHZ，256M DDR？
现状没有：慢，有多慢，现在要1秒？
要达到的目标没有：快，有多快，0.1秒？
你们列的方案有个屁用阿？

65 楼 bainiao 2008-09-13  

方法1：就是先把数据插入一个临时表，然后通过sql找出临时表里面哪些数据在正式表里面没有，然后把这些数据插入正式表。至于怎么找没有重复的数据那就是你的事，数据库存储过程可以完成，也可以写程序一条一条的读出
======================================================
貌似这个效率未必高啊

64 楼 elmar 2008-09-12  

bluemeteor 写道

异常不是这么用滴！

做好索引，select count(*)来判断才是正道

的确违背异常使用的常规。但是仅就数据库操作而言，直接insert应该是一个很合理的做法。