说明:
使用bdcqz表,主要字段 bsm(int类型 主键), qmzt (int 状态标识 值为0或1)
数据库环境:oracle 11g
建表:
create table bdcqz(bsm int primary, qmzt int, col1 clob,col2 varchar2(100)...)
测试 表记录的物理顺序 和 表记录的逻辑顺序(以bsm字段为主键)的不一致性
数据准备
新建bdcqz表
循环插入10000条,循环10000次,每次插入1条,bsm 每条自增1
查询 检查rowid和 bsm 的顺序一致性
结果发现rowid和 bsm 的顺序不一致,bsm是乱序,但是一个个小区间是连续,我的表结构的场景下,30条左右为一个小区间是连续的。
一个bsm连续的小区间的记录数n,跟你的表结构有关系,n可大可小
select t.rowid,t.bsm from bdcqz t;
要严格按bsm排序,需要order by bsm;
select t.rowid,t.bsm from bdcqz t order by bsm;
--测试 bsm 排序对查询时效率的影响,和优化
--表结构
--主键:bsm
--数据测试场景: 记录100w ,qmzt=0和1的记录均匀分散的分布
--查询需求 :批量取按bsm 排序 取靠前的1000条 qmzt=0 的记录
--s1
select * from bdcqz where qmzt=0 and rownum<=1000 order by bsm;
--分析
执行策略
会对主键索引全表扫描,然后通过rowid查找过滤qmzt=0的前1000条记录
存在不足
需要对主键索引全表扫描,并使用rowid 关联 物理表 进行条件过滤
改进
创建bsm, qmzt组合索引 联合索引
create index index_bsm_qmzt on bdcqz(bsm,qmzt);
--重新更新统计信息,不然不会立即生效
ANALYZE TABLE bdcqz COMPUTE STATISTICS FOR TABLE;
ANALYZE TABLE bdcqz COMPUTE STATISTICS FOR ALL INDEXES;
ANALYZE TABLE bdcqz COMPUTE STATISTICS FOR ALL INDEXED COLUMNS;
优点
查询时 直接使用 bsm, qmzt组合索引 查找和过滤 1000条记录,再rowid 关联物理表获取其他列信息
缺点
加入联合索引 会损失一部分插入删除的性能,但性能影响还是比较小的