一、高并发操作对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的争用等待问题,却是会牺牲查询性能的。
no comment untill now