一、高并发操作对B-Tree索引的影响

1. 创建测试表

-- Create table
drop table alex_t06 purge;
create table alex_t06 (col1 number, name varchar2(100));
drop sequence alex_t06_seq_col1;
create sequence alex_t06_seq_col1
minvalue 1
maxvalue 99999999999999999999
start with 1
increment by 1
cache 200;

-- Create/Recreate primary, unique and foreign key constraints
create index idx_alex_t06_col1 on alex_t06 (col1);

--Show table
SQL> desc alex_t06
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER
 NAME                                               VARCHAR2(100)
SQL> select table_name, index_name, column_name from user_ind_columns where table_name='ALEX_T06';
TABLE_NAME           INDEX_NAME           COLUMN_NAME
-------------------- -------------------- --------------------
ALEX_T06             IDX_ALEX_T06_COL1    COL1

2. 查看索引类型,为普通的B-Tree索引。

SQL> select index_name, index_type from user_indexes where index_name='IDX_ALEX_T06_COL1';
INDEX_NAME           INDEX_TYPE
-------------------- ------------------------------------------------------
IDX_ALEX_T06_COL1    NORMAL

3. 模拟100路并发,完成100万条数据的Insert操作。

EVENT                                              TOTAL_WAITS
-------------------------------------------------- -----------
enq: TX - index contention                             1443825

Insert之前记录一下数据库的“enq: TX – index contention”等待事件的数量。

--100路并发Insert
Load        Loops       Proto       TPS         Response(ms)
100         1000        thin        100882.0    0.0099
EVENT                                              TOTAL_WAITS
-------------------------------------------------- -----------
enq: TX - index contention                             1465129

再查询一下,计算差值,100路并发Insert的操作过程,导致“enq: TX – index contention”等待21304次。

4. 再分析一下并发Insert后的索引结构。

SQL> analyze index idx_alex_t06_col1 validate structure;

索引已分析

SQL> select height,round((del_lf_rows_len/lf_rows_len)*100,2)||'%' ratio,pct_used from index_stats where name= 'IDX_ALEX_T06_COL1';

    HEIGHT RATIO                  PCT_USED
---------- -------------------- ----------
         2 0%                           76

SQL> select segment_name,bytes from user_segments where segment_name='IDX_ALEX_T06_COL1';

SEGMENT_NAME                        BYTES
------------------------------ ----------
IDX_ALEX_T06_COL1                 4194304

可以看到,索引的空间使用量(PCT_USED)为76%,索引大小为4M,比之前单session的Insert操作的PCT_USED=100%,索引大小=2M要大了。

5. 小结
(1)高并发Insert会造成较高的“enq: TX – index contention”等待。
(2)此例中,其实有效的索引条目没有增加,但是高并发后,导致了索引结构产生后更多的空洞,叶块利用率不高。
(3)索引在分裂过程中,只有最右侧的叶块是按9:1的比例进行分裂出新叶块的,分裂出新块后,旧块空间利用率就较高,或者说旧块在充盈到一定比例后才会分裂,单进程操作就是如此。
(4)但不是最右侧的叶块分裂的时候,则是按照5:5的比例进行分裂的,高并发单靠最右侧叶块分裂肯定是不够的,要更多的叶块进行分裂,就出现了以上的现象。
索引分裂的进一步分析,以后的BLOG文章再行展开。

二、反键B-Tree索引对高并发的优化

1. 改造普通的B-Tree索引为反键B-Tree索引。

SQL> select index_name, index_type from user_indexes where index_name='IDX_ALEX_T06_COL1';
INDEX_NAME           INDEX_TYPE
-------------------- ------------------------------------------------------
IDX_ALEX_T06_COL1    NORMAL/REV

2. 模拟100路并发,完成100万条数据的Insert操作。

EVENT                                              TOTAL_WAITS
-------------------------------------------------- -----------
enq: TX - index contention                             1465129

Insert之前记录一下数据库的“enq: TX – index contention”等待事件的数量。

--100路并发Insert
Load        Loops       Proto       TPS         Response(ms)
100         1000        thin        101272.0    0.0099
EVENT                                              TOTAL_WAITS
-------------------------------------------------- -----------
enq: TX - index contention                             1465540

再查询一下,计算差值,100路并发Insert的操作过程,导致“enq: TX – index contention”等待411次,远小于步骤一的情况,该问题解决了。
同时发现Insert过程的TPS较步骤一要高了很多,就是因为解决了“enq: TX – index contention”等待问题。

3. 再分析一下并发Insert后的索引结构。

SQL> analyze index idx_alex_t06_col1 validate structure;

索引已分析

SQL> select height,round((del_lf_rows_len/lf_rows_len)*100,2)||'%' ratio,pct_used from index_stats where name= 'IDX_ALEX_T06_COL1';

    HEIGHT RATIO                  PCT_USED
---------- -------------------- ----------
         2 0%                           61

SQL> select segment_name,bytes from user_segments where segment_name='IDX_ALEX_T06_COL1';

SEGMENT_NAME                        BYTES
------------------------------ ----------
IDX_ALEX_T06_COL1                 4194304

可以看到,索引的空间使用量(PCT_USED)为61%,索引大小为4M,空间使用量更低了。

4. 再对比一下普通B-Tree索引和反键B-Tree索引的查询性能。

普通B-Tree索引:
SQL> select * from alex_t06 where col1=1;
统计信息
----------------------------------------------------------
        300  recursive calls
          0  db block gets
         61  consistent gets
         40  physical reads
          0  redo size
       2917  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
         50  rows processed

反键B-Tree索引:
SQL> select * from alex_t06 where col1=1;
统计信息
----------------------------------------------------------
        701  recursive calls
          0  db block gets
        127  consistent gets
         55  physical reads
          0  redo size
       2917  bytes sent via SQL*Net to client
        481  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
         10  sorts (memory)
          0  sorts (disk)
         50  rows processed

反键B-Tree索引查询物理读和逻辑读较普通B-Tree索引都高了不少。

4. 总结
(1)反键B-Tree索引在高并发过程中,能解决普通索引的“enq: TX – index contention”等待问题。
(2)反键B-Tree索引在高并发过程中,空间使用率更低了,因为其分散了索引列的数据存储分布,在高并发过程中,会有更多索引叶块参与分裂,则空间使用率低了。
(3)反键B-Tree索引虽然能解决高并发Insert的争用等待问题,却是会牺牲查询性能的。

Trackback

no comment untill now

Add your comment now

切换到手机版