mysql 复合索引 引导列_使用复合索引不一定需要引导列

我的测试了一下，却需要引导列：

SQL> create table test as select * from user_objects;

±íÒÑ´´½¨¡£

SQL> create index IX_TEST_FH on test(object_id,object_name) ;

Ë÷ÒýÒÑ´´½¨¡£

SQL> select object_name from test where rownum<2;

OBJECT_NAME

--------------------------------------------------------------------------------

AA

SQL> set autot trace exp stat

SQL> select object_name from test where OBJECT_NAME='AA';

Execution Plan

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   TABLE ACCESS (FULL) OF 'TEST'

Statistics

----------------------------------------------------------

0  recursive calls

0  db block gets

62  consistent gets

0  physical reads

0  redo size

518  bytes sent via SQL*Net to client

655  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select object_name,object_id from test where OBJECT_NAME='AA';

Execution Plan

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   TABLE ACCESS (FULL) OF 'TEST'

Statistics

----------------------------------------------------------

1  recursive calls

0  db block gets

62  consistent gets

0  physical reads

0  redo size

588  bytes sent via SQL*Net to client

655  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> select object_id,object_name from test where rownum<2;

Execution Plan

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   COUNT (STOPKEY)

2    1     TABLE ACCESS (FULL) OF 'TEST'

Statistics

----------------------------------------------------------

1  recursive calls

0  db block gets

4  consistent gets

0  physical reads

0  redo size

588  bytes sent via SQL*Net to client

655  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

1  rows processed

SQL> set autot off

SQL> select object_id,object_name from test where rownum<2;

OBJECT_ID

----------

OBJECT_NAME

--------------------------------------------------------------------------------

37606

AA

SQL> select object_name from test where OBJECT_id='37606';

OBJECT_NAME

--------------------------------------------------------------------------------

AA

SQL> set autot trace exp;

SQL> select object_name from test where OBJECT_id='37606';

Execution Plan

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   INDEX (RANGE SCAN) OF 'IX_TEST_FH' (NON-UNIQUE)

SQL> select object_id,object_name from test where OBJECT_id='37606';

Execution Plan

----------------------------------------------------------

0      SELECT STATEMENT Optimizer=CHOOSE

1    0   INDEX (RANGE SCAN) OF 'IX_TEST_FH' (NON-UNIQUE)

SQL>

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

PL/SQL Release 9.2.0.8.0 - Production

CORE    9.2.0.8.0       Production

TNS for Solaris: Version 9.2.0.8.0 - Production

NLSRTL Version 9.2.0.8.0 - Production