mysql索引倒序_使⽤倒序索引提升ORDERBYDESC性能使⽤倒序索引(INDEX DESC)，可以⼤幅提升带有order by desc⼦句的SQL语句性能。
⼀、场景
1、表名：test_t，有⼀字段名为object_id
2、总数据量：580000⾏，gment_size：72MB
axa3、Where条件(Owner=’SYS’ and Object_id>50000)的⾏数：32472⾏
4、SQL语句：lect * 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语句：lect * from test_t where owner='SYS' and object_id > 50000 order by object_id desc
索引⽅式
createindexidx_test_t_id_DESContest_t(owner,object_iddesc)
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
attention是什么意思INDEX RANGE SCAN
SYS
IDX_TEST_T_ID_DESC
3
1
2、采⽤B*树索引(默认为ASC)，COST：94103，执⾏计划⾛的INDEX RANGE SCAN DESENDING 索引⽅式
createindexidx_test_t_id_DESContest_t(owner,object_id)
COST
与执⾏计划
Description
divergentObject 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
INDEXRANGESCAN DESCENDING
SYS
IDX_TEST_T_ID_ASC
287
93792
总结：
guoxin索引⽅式
COST
执⾏计划
(owner,object_id desc)
4
INDEX RANGE SCAN
(owner,object_id)
94103
INDEXRANGESCAN DESENDING
三、实际测试过程
SQL语句：lect * 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
interesting的音标
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
ppoo
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
realize用法
93792
9097824
INDEXRANGESCAN DESCENDING
SYS
IDX_TEST_T_ID_ASC
287
93792
