+++ 大数据量情况下的Sql语句处理 +++

Question

表Tb1字段（A, B, C）
表Tb2字段（D, C）现向Tb1添加数据，要求:1、Insert前判读A是否唯一（select Tb1）
                     2、字段B通过C关联T2，对D进行计算得到(select 2*D from Tb2 where C = '某值')两个条件都通过，OK，Insert吧，添加完成之后select * from Tb1 where B= 'Insert记录B值'（返回多条）
随着数据的不断添加，Tb1表的数据越来越大（几十上百万条），此时，在进行插入记录、查询速度很慢
我想了解下大伙是怎样处理这种情况的，批量添加、临时表、存储过程等等方式，都详细说说，最好用代码说话，
当然更欢迎讲明原理.

Answer 1

Sql server2000数据库

Answer 2

Tb1表的数据越来越大（几十上百万条），此时，在进行插入记录、查询速度很慢
----------------------------------------------------------------1、你这个方法对于数据量大表来说，不仅速度慢，而且并发不好；
2、方法有2种：
   A、可以使用GUID，通过程序产生GUID，无需判断是否重复即可直接写入数据库；
   B、查询数据库的方法，但需要注意查询效率；方法如下：
       可以设定一个专门产生号码的表；
       例如：表名：       SysUniqueNumber 
            表的主键名：KeyName varchar(100),
            当前的值：  CurrentValue numeric(18,0)
     
      例如：Tb1表的主键为“Tb1ID”,假设系统第一次运行，需要向Tb1插入一条数据，那么从这个号码
            表中开始查询，查询条件为KeyName为“Tb1ID”(为了多个表都可以公用),如果该表中找不到，
            那么就说明第一次使用，返回1，同时增加一条记录，KeyName为“Tb1ID”，CurrentValue
          为1，以此类推，以后如果再去Tb1的主键的唯一值，则查询号码表，如果找到则则在当前的值上
            加1，然后返回。这样就可以保证，号码表数据量很小，查询和更新这个表速度会很快。比在业务表里取值效率要很多(数据量大的情况下)，而且系统中类似功能的ID都可以使用这种方法。我这里提供了一个思路，具体的代码可能还需要你写，你可以写在存储过程程里，也可以写在程序里。但相同的一点都需要事务。可以对这个过程再包装一下，即可以取一个，也可以一次取一批。如果还有问题，欢迎继续发帖讨论，也可以加入QQ高级群：9642802，和管理员大海一起讨论。               

Answer 3

多谢wxsan这么详细的讲述！多谢接着说，你说的第一种方法使用GUID有一点不明，是在Tb1另新增一字段还是将字段A作为GUID？Tb1要求A不可重复，直接说吧这里举例的A在实际中就是一个物件扫描码，都是物品上固有的（不可重新计算更改），所以添加过的就不能再添加了。第二种方法，就是将我举例中的A单独拿出放在SysUniqueNumber，这个一定程度上可以加快查询速度,从而加快判读A唯一性的速度，但这样对后续的Insert和最后的select * from Tb1 where B= 'Insert记录B值'，应该没有效率优化，不知道我说的对不对另外，我想问，单纯的插入（Insert），不进行查寻，表中已有的数据量对速度有影响吗，比如原有100条记录和10万条记录的两种情况，插入一条记录速度差别大吗？

Answer 4

100条记录和10万条记录的两种情况，插入一条记录速度差别大吗？//应该还是有差距的，特别是你的表的索引比较多的时候，插入记录除了写表，还要写索引，所以有些不常用的索引要删除，否则会影响插入的速度。你的GUID貌似还只能先select判断。如果用异常扑捉估计会耗时更长。
不知道你的情况能否用临时表先保存，然后用多线程在空闲的时间再往数据库写？
另外可能用存储过程中处理速度会快些。

Answer 5

修改设计，取消对A唯一性的判断，这个很浪费时间
避免减少循环插入，即单条插入，进行符合条件的批量插入
减少不必要的所引字段，所引可以加速对该字段的查找效率，但是过多的所引会对insert，update等操作影响很大

Answer 6

楼主,应特别注意: 在向一个表中大量插入数据后,需要重建索引(DBCC DBREINDEX (表名)),否则数据库性能降低很明显.....

Answer 7

1、对于数据量大的表一般都会建立索引，估计你的这个tb1表应该也会建立索引，否则查询速度会非常慢；加索引会极大的提高查询速度(数据量大时非常非常明显)；但对于插入数据来说，数据量少时和大时插入数据的速度差别也比较大，没有索引会很快，有索引就比较慢，数据量越大越慢，因为插入数据、要写索引文件，索引文件和数据文件不会在一起存储，在写数据时导致磁头定位需要花不少时间，所以如何找到最佳的方法就很重要；2、如果一定需要每次插入都判断是否重复的话，可以采用临时表的方式tb1_tmp，可以设定多长时间对临时表的数据插入到正式表，而且插入数据可以使用临时表关联正式表id的批处理插入，关联条件设置为在正式表不存在的ID。这种方法的好处是速度极快，不好就是数据不能向及时写入正式表；

Answer 8

写一个存储过程, 全部insert到临时表,多一个字段(Del),默认为0.
update 临表 set del = 1 where 临A in (select A from TB1)
...
再update ... 一次 (第二个运算)最后把为0的全插进实表

Answer 9

表结构是这样？
create table T1(a int primary key, b int, c int)
create table T2(d int primary key, e int)一般数据量多的，我一般是先将外部数据导入到临时表，T_Temp，然后再在那表打SQL insertinsert into T1(a, b, c)
  select a, null, c from T_Temp tt
    where exists (select 1 from T1 where tt.a = t1.a) and ...???update t1 set
  t1.B = 2 * t2.D
from t1, t2, T_Temp tt
where exists ( select 1 from T1 where tt.a = t1.a) and t1.b = t2.d随便打打，自己测下。
                    

Answer 10

insert的同时判断是否存在insert into ...  where not Exists (select * from ..)存在就不会insert,返回影响行数为0100万的数量集在使用索引列查询的时候速度很快,不会有明显的停顿感 但是索引列会影响插入速度还是要看业务流程了

Answer 11

看样子 TB1 应该是一个基本资料表,  TB2 这里没太看懂意思.呵呵TB1  A 肯定是有个索引的(聚集)的对吧?  基础资料的并发新增应该不会太大, 这个最后就是对字段加索引,一个SQL语句来查表返回是否 Existif Exists (Select 1 from TB1 where A = value ) ...

Answer 12

upupupupupupupupupupupupupu
学习中！！！

Answer 13

sql sever 2005的执行效率要好些，特别对于数据量比较大的，2000的适合一些普通小系统，数据量小，操作方便，简单。

Answer 14

问题应该比较简单,关键是要建索引.表Tb1的A字段建唯一非聚簇索引,唯一是用来保证数据正确性,否则SQL语句要加锁.
表Tb1的B字段建非聚簇索引.
表Tb2的C字段建索引,表记录少或者修改操作少就可以建聚簇索引.
insert into Tb1(A,B,C)select top 1 @A,2*D,@C from Tb2 where C=@C and(select top 1 A from Tb1 where A=@A)is null
if @@rowcount<>0 begin
 select * from Tb1 where B=(select top 1 2*D from Tb2 where C=@C)
end
我觉慢应该是在查询语句上,结果可能很多造成的,可以考虑分页只返回需要的数据.

Answer 15

1，是否可以将第二个table和第一个合并起来
2，索引建立会对查询有很大帮助（目前来看，你的ABC都要建索引），但是对于你的插入来说就不太好了，而且时间长了，要进行索引重建；这个要取决于你的作业模式和业务实际情况
3，优化可能还是要你实际的数据：并发数，数据格式，Server压力
4，其实百万的数据量还是很少的，如果感觉性能不行的话，可以用SQL2005，分区表也许会有点用处

Answer 16

在tbl1中可以对A创建索引，这样保证了唯一性就不用再去判断A是否唯一。
第2个条件我不是很明白。对B有什么限制吗？

Answer 17

oracle merge into 用法详解Oracle9i引入了MERGE命令,你能够在一个SQL语句中对一个表同时执行inserts和updates操作. MERGE命令从一个或多个数据源中选择行来updating或inserting到一个或多个表.Oracle 10g中MERGE有如下一些改进：
1、UPDATE或INSERT子句是可选的
2、UPDATE和INSERT子句可以加WHERE子句
3、ON条件中使用常量过滤谓词来insert所有的行到目标表中,不需要连接源表和目标表
4、UPDATE子句后面可以跟DELETE子句来去除一些不需要的行首先创建示例表：
create table PRODUCTS
    (
    PRODUCT_ID INTEGER,
    PRODUCT_NAME VARCHAR2(60),
    CATEGORY VARCHAR2(60)
    );    insert into PRODUCTS values (1501, 'VIVITAR 35MM', 'ELECTRNCS');
    insert into PRODUCTS values (1502, 'OLYMPUS IS50', 'ELECTRNCS');
    insert into PRODUCTS values (1600, 'PLAY GYM', 'TOYS');
    insert into PRODUCTS values (1601, 'LAMAZE', 'TOYS');
    insert into PRODUCTS values (1666, 'HARRY POTTER', 'DVD');
    commit;    create table NEWPRODUCTS
    (
    PRODUCT_ID INTEGER,
    PRODUCT_NAME VARCHAR2(60),
    CATEGORY VARCHAR2(60)
    );    insert into NEWPRODUCTS values (1502, 'OLYMPUS CAMERA', 'ELECTRNCS');
    insert into NEWPRODUCTS values (1601, 'LAMAZE', 'TOYS');
    insert into NEWPRODUCTS values (1666, 'HARRY POTTER', 'TOYS');
    insert into NEWPRODUCTS values (1700, 'WAIT INTERFACE', 'BOOKS');
    commit;
1、可省略的UPDATE或INSERT子句
在Oracle 9i, MERGE语句要求你必须同时指定INSERT和UPDATE子句.而在Oracle 10g, 你可以省略UPDATE或INSERT子句中的一个. 下面的例子根据表NEWPRODUCTS的PRODUCT_ID字段是否匹配来updates表PRODUCTS的信息:
SQL> MERGE INTO products p
    2 USING newproducts np
    3 ON (p.product_id = np.product_id)
    4 WHEN MATCHED THEN
    5 UPDATE
    6 SET p.product_name = np.product_name,
    7 p.category = np.category;    3 rows merged.    SQL> SELECT * FROM products;    PRODUCT_ID PRODUCT_NAME CATEGORY
    ---------- -------------------- ----------
    1501 VIVITAR 35MM ELECTRNCS
    1502 OLYMPUS CAMERA ELECTRNCS
    1600 PLAY GYM TOYS
    1601 LAMAZE TOYS
    1666 HARRY POTTER TOYS
    SQL>
    SQL> ROLLBACK;
    Rollback complete.
    SQL>在上面例子中, MERGE语句影响到是产品id为1502, 1601和1666的行. 它们的产品名字和种 类被更新为表newproducts中的值. 下面例子省略UPDATE子句, 把表NEWPRODUCTS中新的PRODUCT_ID插入到表PRODUCTS中, 对于在两个表中能够匹配上PRODUCT_ID的数据不作任何处理. 从这个例子你能看到PRODUCT_ID=1700的行被插入到表PRODUCTS中.
SQL> MERGE INTO products p
    2 USING newproducts np
    3 ON (p.product_id = np.product_id)
    4 WHEN NOT MATCHED THEN
    5 INSERT
    6 VALUES (np.product_id, np.product_name,
    7 np.category);    1 row merged.    SQL> SELECT * FROM products;    PRODUCT_ID PRODUCT_NAME CATEGORY
    ---------- -------------------- ----------
    1501 VIVITAR 35MM ELECTRNCS
    1502 OLYMPUS IS50 ELECTRNCS
    1600 PLAY GYM TOYS
    1601 LAMAZE TOYS
    1666 HARRY POTTER DVD
    1700 WAIT INTERFACE BOOKS
2、带条件的Updates和Inserts子句
你能够添加WHERE子句到UPDATE或INSERT子句中去, 来跳过update或insert操作对某些行的处理. 下面例子根据表NEWPRODUCTS来更新表PRODUCTS数据, 但必须字段CATEGORY也得同时匹配上:
SQL> MERGE INTO products p
    2 USING newproducts np
    3 ON (p.product_id = np.product_id)
    4 WHEN MATCHED THEN
    5 UPDATE
    6 SET p.product_name = np.product_name
    7 WHERE p.category = np.category;    2 rows merged.    SQL> SELECT * FROM products;    PRODUCT_ID PRODUCT_NAME CATEGORY
    ---------- -------------------- ----------
    1501 VIVITAR 35MM ELECTRNCS
    1502 OLYMPUS CAMERA ELECTRNCS
    1600 PLAY GYM TOYS
    1601 LAMAZE TOYS
    1666 HARRY POTTER DVD
    SQL>
    SQL> rollback;在这个例子中, 产品ID为1502,1601和1666匹配ON条件但是1666的category不匹配. 因此MERGE命令只更新两行数据. 下面例子展示了在Updates和Inserts子句都使用WHERE子句:
SQL> MERGE INTO products p
    2 USING newproducts np
    3 ON (p.product_id = np.product_id)
    4 WHEN MATCHED THEN
    5 UPDATE
    6 SET p.product_name = np.product_name,
    7 p.category = np.category
    8 WHERE p.category = 'DVD'
    9 WHEN NOT MATCHED THEN
    10 INSERT
    11 VALUES (np.product_id, np.product_name, np.category)
    12 WHERE np.category != 'BOOKS'
    SQL> /    1 row merged.    SQL> SELECT * FROM products;    PRODUCT_ID PRODUCT_NAME CATEGORY
    ---------- -------------------- ----------
    1501 VIVITAR 35MM ELECTRNCS
    1502 OLYMPUS IS50 ELECTRNCS
    1600 PLAY GYM TOYS
    1601 LAMAZE TOYS
    1666 HARRY POTTER TOYS    SQL>
注意由于有WHERE子句INSERT没有插入所有不匹配ON条件的行到表PRODUCTS.
3、无条件的Inserts
你能够不用连接源表和目标表就把源表的数据插入到目标表中. 这对于你想插入所有行到目标表时是非常有用的. Oracle 10g现在支持在ON条件中使用常量过滤谓词. 举个常量过滤谓词例子ON (1=0). 下面例子从源表插入行到表PRODUCTS, 不检查这些行是否在表PRODUCTS中存在:SQL> MERGE INTO products p
    2 USING newproducts np
    3 ON (1=0)
    4 WHEN NOT MATCHED THEN
    5 INSERT
    6 VALUES (np.product_id, np.product_name, np.category)
    7 WHERE np.category = 'BOOKS'
    SQL> /    1 row merged.    SQL> SELECT * FROM products;    PRODUCT_ID PRODUCT_NAME CATEGORY
    ---------- -------------------- ----------
    1501 VIVITAR 35MM ELECTRNCS
    1502 OLYMPUS IS50 ELECTRNCS
    1600 PLAY GYM TOYS
    1601 LAMAZE TOYS
    1666 HARRY POTTER DVD
    1700 WAIT INTERFACE BOOKS
    6 rows selected.
    SQL>4、新增加的DELETE子句
Oracle 10g中的MERGE提供了在执行数据操作时清除行的选项. 你能够在WHEN MATCHED THEN UPDATE子句中包含DELETE子句. DELETE子句必须有一个WHERE条件来删除匹配某些条件的行.匹配DELETE WHERE条件但不匹配ON条件的行不会被从表中删除.
下面例子验证DELETE子句. 我们从表NEWPRODUCTS中合并行到表PRODUCTS中, 但删除category为ELECTRNCS的行.
SQL> MERGE INTO products p
    2 USING newproducts np
    3 ON (p.product_id = np.product_id)
    4 WHEN MATCHED THEN
    5 UPDATE
    6 SET p.product_name = np.product_name,
    7 p.category = np.category
    8 DELETE WHERE (p.category = 'ELECTRNCS')
    9 WHEN NOT MATCHED THEN
    10 INSERT
    11 VALUES (np.product_id, np.product_name, np.category)
    SQL> /    4 rows merged.    SQL> SELECT * FROM products;    PRODUCT_ID PRODUCT_NAME CATEGORY
    ---------- -------------------- ----------
    1501 VIVITAR 35MM ELECTRNCS
    1600 PLAY GYM TOYS
    1601 LAMAZE TOYS
    1666 HARRY POTTER TOYS
    1700 WAIT INTERFACE BOOKS
    SQL>产品ID为1502的行从表PRODUCTS中被删除, 因为它同时匹配ON条件和DELETE WHERE条件. 产品ID为1501的行匹配DELETE WHERE条件但不匹配ON条件, 所以它没有被删除. 产品ID为1700 的行不匹配ON条件, 所以被插入表PRODUCTS. 产品ID为1601和1666的行匹配ON条件但不匹配DELETE WHERE条件, 所以被更新为表NEWPRODUCTS中的值.

Answer 18

/*Merge into 详细介绍
MERGE语句是Oracle9i新增的语法，用来合并UPDATE和INSERT语句。
通过MERGE语句，根据一张表或子查询的连接条件对另外一张表进行查询，
连接条件匹配上的进行UPDATE，无法匹配的执行INSERT。
这个语法仅需要一次全表扫描就完成了全部工作，执行效率要高于INSERT＋UPDATE。 
*/
/*語法：
MERGE [INTO [schema .] table [t_alias] 
USING [schema .] { table | view | subquery } [t_alias] 
ON ( condition ) 
WHEN MATCHED THEN merge_update_clause 
WHEN NOT MATCHED THEN merge_insert_clause;
*/
/*
我们还是以《sql中的case应用》中的表为例。在创建另两个表fzq1和fzq2
*/
--全部男生记录
create table fzq1 as select * from fzq where sex=1;
--全部女生记录
create table fzq2 as select * from fzq where sex=0;
/*涉及到两个表关联的例子*/
--更新表fzq1使得id相同的记录中chengji字段＋1，并且更新name字段。
--如果id不相同，则插入到表fzq1中.
--将fzq1表中男生记录的成绩＋1，女生插入到表fzq1中
merge into fzq1  aa     --fzq1表是需要更新的表
using fzq bb            -- 关联表
on (aa.id=bb.id)        --关联条件
when matched then       --匹配关联条件，作更新处理
update set
aa.chengji=bb.chengji+1,
aa.name=bb.name         --此处只是说明可以同时更新多个字段。
when not matched then    --不匹配关联条件，作插入处理。如果只是作更新，下面的语句可以省略。
insert values( bb.id, bb.name, bb.sex,bb.kecheng,bb.chengji);
--可以自行查询fzq1表。
/*涉及到多个表关联的例子，我们以三个表为例，只是作更新处理，不做插入处理。当然也可以只做插入处理*/
--将fzq1表中女生记录的成绩＋1，没有直接去sex字段。而是fzq和fzq2关联。
merge into fzq1  aa     --fzq1表是需要更新的表
using (select fzq.id,fzq.chengji 
       from fzq join fzq2
       on fzq.id=fzq2.id) bb  -- 数据集
on (aa.id=bb.id)        --关联条件
when matched then       --匹配关联条件，作更新处理
update set
aa.chengji=bb.chengji+1
--可以自行查询fzq1表。
/*不能做的事情*/
merge into fzq1  aa    
using fzq bb           
on (aa.id=bb.id)        
when matched then       
update set
aa.id=bb.id+1
/*系统提示：
ORA-38104: Columns referenced in the ON Clause cannot be updated: "AA"."ID"
我们不能更新on (aa.id=bb.id)关联条件中的字段*/
update fzq1 
set  id=(select id+1 from fzq where fzq.id=fzq1.id)
where id in
(select id from fzq)
--使用update就可以更新，如果有更好的方法，谢谢反馈！
作者：tshfang 
来源: 泥胚文章写作 http://www.nipei.com 原文地址： http://www.nipei.com/article/9855

Answer 19

merge into 出现错误 ora-30926,如何解决？
数据库： 9.2.0.1
我执行下面语句：
begin
    
  Merge Into testw dest using testq src
          On (dest.name = src.name)
  When Matched Then
          UPDATE SET
          dest.id= 1
  When Not Matched Then
          INSERT( id,name)
          VALUES(  src.id,src.name);end;
出现错误：
ora-30926:无法在原表中获得一组稳定的行
我的testq表中有多条相同的记录，在目标表上也有相匹配的记录，所以不能执行update操作，
请问该怎么办？
因为我的原表testq上有1亿条记录，所以我不想在上面执行distinct,max等操作，怎么可以快速执行上面的
语句？请问9.2.0.1有相关的补丁吗？谢谢了。

Answer 20

我发现有时候本意想让问题阐述的更简单点，到头来反而引起更多的猜测，索性就不用之前的假设的表和字段了，直接拿实际情况说问题！这是一个简单的产品（电表）装箱软件。产品出厂装箱时，用扫描枪扫描电表，得到扫描码，通过在软件中设置的读取表号规则，对扫描码进行处理得到表号，如扫描码为022M00393900098，根据订单信息最后算得的表号为000027313627，同时算出装箱号码如第105箱，最后将记录保存，与此同时显示出该105箱的已装箱表和未装箱表。说一下，我最后的调整处理方式，不一定是最合适，不过起码效率提高不少；另外特别感谢各位建言献策！添加数据放在临时表TmpMeterTb，这实际还是个物理表，建库时就创建此表，表结构比实际MeterTb多一个标记字段isExported，默认值为0，扫描添加数据时先添加到此表，判读唯一性也只检索此临时表，可随时将数据汇总到MeterTb中并将字段isExported改为1，此处用存储过程处理；另外等此订单的货全部出厂，可将此临时表的该订单的数据清掉，始终保持此临时表轻量级的数据量，这样可以提高日后检索此表、添加数据的效率。存储过程脚本：if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[HuizongPro]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[HuizongPro]
GOSET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
Create Proc HuizongPro 
 @OrderId varchar(40)
AS
SET XACT_ABORT ON -- 打开BEGIN TRAN  
  --将临时表的数据插入到实表中
  Insert into MeterTb(userId, scanTime, scanCode, meterNo, orderId, boxNo) 
         select userId, scanTime, scanCode, meterNo, orderId, boxNo from TmpMeterTb 
            where not Exists
               (select 1 from MeterTb 
                  where MeterTb.MeterNo = TmpMeterTb.MeterNo
                  and MeterTb.OrderId = @OrderId)
            and OrderId = @OrderId and isExported <> 1  --将临时表的标志0改为1
 update TmpMeterTb set isExported = 1 where OrderId = @OrderId and isExported <> 1COMMIT TRAN  
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO