使用倒序索引提升ORDER BY DESC性能
来源:互联网 发布:大数据搜索论文 编辑:程序博客网 时间:2024/05/17 00:14
一、场景
1、表名:test_t,有一字段名为object_id
2、总数据量:580000行,segment_size:72MB
3、Where条件(Owner=’SYS’ and Object_id>50000)的行数:32472行
4、SQL语句:select * from test_t where owner='SYS' and object_id > 50000 order by object_id desc
5、希望借助倒序索引,提升order by object_id desc的性能
二、B*树倒序索引(DESC)比 B*树索引(默认为ASC升序)带来的性能提升对比
1、采用B*树倒序索引(DESC),COST:4,执行计划走的INDEX RANGE SCAN
SQL语句:select * from test_t where owner='SYS' and object_id > 50000 order by object_id desc
索引方式
create index idx_test_t_id_DESC on test_t(owner,object_id desc)
COST
与执行计划
Description
Object owner
Object name
Cost
Cardinality
Bytes
SELECT STATEMENT, GOAL = FIRST_ROWS
4
94298
9806992
TABLE ACCESS BY INDEX ROWID
SYS
TEST_T
4
94298
9806992
INDEX RANGE SCAN
SYS
IDX_TEST_T_ID_DESC
3
1
2、采用B*树索引(默认为ASC),COST:94103,执行计划走的INDEX RANGE SCAN DESENDING
索引方式
create index idx_test_t_id_DESC on test_t(owner,object_id)
COST
与执行计划
Description
Object owner
Object name
Cost
Cardinality
Bytes
SELECT STATEMENT, GOAL = FIRST_ROWS
94103
93792
9097824
TABLE ACCESS BY INDEX ROWID
SYS
TEST_T
94103
93792
9097824
INDEX RANGE SCAN DESCENDING
SYS
IDX_TEST_T_ID_ASC
287
93792
总结:
索引方式
COST
执行计划
(owner,object_id desc)
4
INDEX RANGE SCAN
(owner,object_id)
94103
INDEX RANGE SCAN DESENDING
三、实际测试过程
SQL语句:select * from test_t where owner='SYS' and object_id > 50000 order by object_id desc
1、采用B*树倒序索引(DESC)测试过程与结果
(1)test_t表上创建(OWNER+OBJECT_ID DESC)的倒序索引
SQL>create index idx_test_t_id_DESC on test_t(owner,object_id desc)
(2)重新收集统计信息
SQL> execdbms_stats.gather_table_stats(ownname=>'SYS',tabname => 'TEST_T',estimate_percent=> 20 , cascade=> TRUE );
(3)SQL执行计划与COST
Description
Object owner
Object name
Cost
Cardinality
Bytes
SELECT STATEMENT, GOAL = FIRST_ROWS
4
94298
9806992
TABLE ACCESS BY INDEX ROWID
SYS
TEST_T
4
94298
9806992
INDEX RANGE SCAN
SYS
IDX_TEST_T_ID_DESC
3
1
2、B*树索引(默认为ASC升序)测试过程与结果
(1)test_t表上创建(OWNER+OBJECT_ID)的普通默认ASC索引
SQL>create index idx_test_t_id_ASC on test_t(owner,object_id)
(2)重新收集统计信息
SQL> execdbms_stats.gather_table_stats(ownname=>'SYS',tabname => 'TEST_T',estimate_percent=> 20 , cascade=> TRUE );
(3)SQL执行计划与COST
Description
Object owner
Object name
Cost
Cardinality
Bytes
SELECT STATEMENT, GOAL = FIRST_ROWS
94103
93792
9097824
TABLE ACCESS BY INDEX ROWID
SYS
TEST_T
94103
93792
9097824
INDEX RANGE SCAN DESCENDING
SYS
IDX_TEST_T_ID_ASC
287
93792
http://www.linuxidc.com/Linux/2013-07/87433.htm
- 使用倒序索引提升ORDER BY DESC性能
- 使用倒序索引提升ORDER BY DESC性能
- 注意使用 BTREE 复合索引各字段的 ASC/DESC 以优化 order by 查询效率
- 注意使用 BTREE 复合索引各字段的 ASC/DESC 以优化 order by 查询效率
- sql order by,desc和limit使用(mysql)
- ORDER BY xxx DESC/ASC
- row_number() over(order by col2 desc)用法
- sql2008 order by desc 查询速度慢
- 排序用法 order by asc desc
- mysql:sql order by */* desc (查询)
- order by 使用索引的情况
- MySQL ORDER BY 索引使用情况
- MySQL Order By 使用索引的情况
- order by排序使用联合索引
- row_number() over(partition by a order by b desc) rn
- row_number() over (partition by idno order by id desc)
- row_number() over(partition by a order by b desc) 用法
- mysql实现ROW_NUMBER() over (PARTITION BY xx ORDER BY ** DESC)
- myeclipse安装git插件
- 用gdb调试nasm汇编程序
- stm32f407 GPIO_PinAFConfig 函数
- 这些渐行渐远的友情,你有过吗?
- 日期格式化
- 使用倒序索引提升ORDER BY DESC性能
- FreeRTOS高级篇3---FreeRTOS调度器启动过程分析
- 遍历map的四种方式
- 计算机概述
- 161018
- mysql索引总结----mysql 索引类型以及创建
- 文章标题
- LoRa开发8: 下载源代码
- 黑马程序员:Java学习路线大揭秘连载2-JavaWeb篇!