一个索引创建引出的思考

- 日理万妓 2024-04-19 23:59 39阅读 0赞

前两天上线，碰见了些问题，还是很值得探讨。

**问题1**，

一张3000万记录的单表中，创建了唯一约束，(A,B,C)，其中A的重复值非常多，B的重复值很少，接近于主键效果，但是某个逻辑，用到的是B作为检索条件，而且在开发测试阶段，因为数据量有限，未识别出这个字段需要创建索引，只在实际量级的生产系统下，才会暴露问题。但是，(A,B,C)自带的唯一约束索引，是无法使用的，因为业务不能停，为了避免造成问题数据，所以不能改这个唯一约束。只可以选择再创建个索引，即B的单键值索引。

这个问题暴露的，其实就是在索引创建的选择上，应该多考虑一些不同场景的使用，(A,B,C)和B这两个不同的使用场景，如果提前知道，并且对索引的基本原理有些了解，应该知道(B,A,C)是正确的创建路径，一方面这两个场景的索引使用要求，另一方面降低了存储两个索引的成本消耗。

但往往这种案例，非常常见，如何能从根本解决此类问题？我觉得可能有这几种方式：

1. 提高开发人员对数据库的了解，尤其是这种和开发直接相关的使用场景。

2. 提高DBA在此类问题的审核力度。

方法1其实是王道，开发了解业务，同时对数据库的基本原理了解，自然能更容易地识别这类问题，授人以鱼不如授人以渔。方法2是被动的，毕竟DBA或者专业的数据库人员，对数据库了解，但对业务不了解，除非开发告诉他，我有这两种用法，当然有其他方式，或许能找到这类问题，但是消耗不小，投入产出比不高。方法3是技术手段的辅助，例如在非生产环境下，通过工具，即使数据量小，我也能通过执行计划等信息，找出此类问题，结合方法2，对这种问题进行预警提示，一定程度上，还是能接受的。

**问题2**，

因为正常业务在频繁地做增删改操作，所以不能直接创建索引，他会阻塞DML，需要采用online方式，但是问题来了，在PLSQL Developer中，执行如下语句，等了将近30分钟，还未完成，

create index idx_t_01 on t(id) online;

当然这个语句，存在一定的问题，就是没用parallel，没用充分利用数据库服务器的多CPU，一定程度上，影响执行效率，毕竟创建索引，无论在线，还是非在线，尤其对大表，还是很消耗资源，所以正确做法，应该是加上parallel，当然要结合当前数据库的CPU个数以及idle，来确定parallel并行度，

create index idx_t_01 on t(id) online parallel 16;

另外，如果要中断当前创建索引的过程，可能会出现未回收的索引段，导致不能重新创建索引，解决方案是，

1. 可以尝试利用如下SQL捕获异常的Index Object\_id，

select i.obj#, i.flags, u.name, o.name, o.type#
    from sys.obj$ o, sys.user$ u, sys.ind_online$ i
    where (bitand(i.flags, 256) = 256 or bitand(i.flags, 512) = 512)
    and (not ((i.type# = 9) and bitand(i.flags, 8) = 8))
    and o.obj# = i.obj#
    and o.owner# = u.user#;

2. 将异常的object\_id带入如下存储过程手工清理，

declare
    isclean boolean;
    begin
    isclean := false;
    while isclean = false loop
    isclean := DBMS_REPAIR.ONLINE_INDEX_CLEAN(对应OBJECT_ID, dbms_repair.lock_wait);
    dbms_lock.sleep(10);
    end loop;
    end;
    /

关于在线online和非online创建索引的方式，各自背后执行的路径、优缺点、成本消耗，以及中断索引创建的案例等，可以参考，如下文章，

《

《》

《

读到此处的朋友，如果对上面这两个问题，你有自己的见解，欢迎一起讨论。