mysql 索引 倒序_使用倒序索引提升ORDER BY DESC性能

使用倒序索引(INDEX DESC)，可以大幅提升带有order by desc子句的SQL语句性能。

一、场景

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

索引方式

createindexidx_test_t_id_DESContest_t(owner,object_iddesc)

COST

与执行计划

Description

Object owner

Object name

Cost

Cardinality

Bytes

SELECT STATEMENT, GOAL = FIRST_ROW