数据库新增列遇到的问题

小鱼儿 2022-02-15 13:15 327阅读 0赞

难免会遇到线上环境数据表需要新增列的情况，如果的大表新增列的情况下，那就需要谨慎，新增列可能会导致长时间锁表，影响线上访问。

以下是我遇到的数据库表新增列的情况(数据库名和表名用DB1和TABLE1代替)，数据库为ORACLE,为新增列时做的测试，总共三种类型的语句:

1.  新增列，但不设置默认值
2.  新增列，设置默认值
3.  新增列，设置默认值，并且设置新增列值为NOT NULL

以下为抽取出的各个语句执行消耗时间情况，从下面可以看出第2中情况速度最慢，第3中情况效率很快，和第1中情况差不多（如有条件可以自行多测试几次），第1中情况和第2中情况比，第2中情况需要做更新操作，需要锁表，第2中情况慢那么多可以理解，毕竟所有的数据都要更新；**但第3中情况竟然比第2中情况快了近100倍，简直不可以思议**。

ALTER TABLE "DB1"."TABLE1" 
    ADD ("CERT_SOURCE2" VARCHAR2(2 CHAR) DEFAULT 0 NOT NULL )
    > OK
    > 时间: 0.015s
    
    ALTER TABLE "DB1"."TABLE1" 
    ADD ("CERT_SOURCE3" VARCHAR2(2 CHAR) DEFAULT 0)
    > OK
    > 时间: 1.154s
    
    ALTER TABLE "DB1"."TABLE1" 
    ADD ("CERT_SOURCE4" VARCHAR2(2 CHAR) DEFAULT 0  NOT NULL )
    > OK
    > 时间: 0.011s
    
    ALTER TABLE "DB1"."TABLE1" 
    ADD ("CERT_SOURCE5" VARCHAR2(2 CHAR) DEFAULT 0 )
    > OK
    > 时间: 1.647s
    
    ALTER TABLE "DB1"."TABLE1" 
    ADD ("CERT_SOURCE6" VARCHAR2(2 CHAR)  
    > OK
    > 时间: 0.011s

经过多方求证：  
**如果要在一张已经存在的表中添加一个字段，没有指定默认值的话他默认为NULL,当你指定了默认值的时候数据库直接在每行上直接更新添加的值， 这需要花上一段时间，并且会在这段时间内产生DML 锁。  
对于一些类型表（例如没有lob字段的表） 如果你添加的字段是not null 的并且有一个默认的初始值，数据库能够对添加列进行优化， 减少大量数据库被DML锁的时间。**

**另外：default 0 not null，Oracle并没有真实的去修改列的值，只不过通过查询的时候，采用类似NVL(null,新值)的 方法转换一下，让以前的列看起来象有值一样。这个过程在select 的时候就转换好了，所以，对于使用者看来，他们是有值的。但如果抽取这个表的数据，抽取到的列值还是null。**

而我的表中所有的字段要么是varchar，要么是int，没有特殊字段，当增加了NOT NULL时，数据库对添加列进行了优化，减小了数据库被行锁的时间。

如果想了解更多关于数据库添加列时需要注意的，我觉得[这篇文章可以参考下][Link 1]

[Link 1]: http://blog.itpub.net/26451536/viewspace-1815860/