インデックスのリーフノードに含まれるデータ

• MySQL

(8.0.22)
https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html

(1)クラスタインデックスの場合(通常、主キーの場合)
すべてのカラムがリーフノードに含まれる

(2)セカンダリインデックスの場合
セカンダリインデックスのカラムと主キーのカラムが含まれる

-- 動作確認

drop table tab1;
create table tab1(col1 int,col2 int,col3 int,col4 int);

alter table tab1 add constraint tab1pk primary key(col1,col2);
create index ind1 on tab1(col3);

insert into tab1 values(1,1,1,1);
insert into tab1 values(2,2,2,2);

explain
select col1,col2,col3,col4
from tab1 force index ( primary )
where col1 = 1 and col2 = 1;

explain
select col1,col2,col3,col4
from tab1 force index ( ind1 )
where col3 = 1;

explain
select col1,col2,col3
from tab1 force index ( ind1 )
where col3 = 1;

explain
select col3
from tab1 force index ( ind1 )
where col3 = 1;

• Oracle

(19c)
https://docs.oracle.com/cd/F19136_01/cncpt/indexes-and-index-organized-tables.html#GUID-89A9F85F-BE0E-4596-AEC3-CAF0D821B1CA

リーフノードには、インデックスカラム値と、実際の行を検索するためのROWIDが含まれる

-- 動作確認

drop table tab1 purge;
create table tab1(col1 int,col2 int,col3 int,col4 int);

alter table tab1 add constraint tab1pk primary key(col1,col2);
create index ind1 on tab1(col3);

insert into tab1 values(1,1,1,1);
insert into tab1 values(2,2,2,2);
commit;

explain plan for
select /*+ index( tab1 tab1pk ) */
col1,col2,col3,col4
from tab1
where col1 = 1 and col2 = 1;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

explain plan for
select /*+ index( tab1 ind1 ) */
col1,col2,col3,col4
from tab1
where col3 = 1;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

explain plan for
select /*+ index( tab1 ind1 ) */
col1,col2,col3
from tab1
where col3 = 1;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

explain plan for
select /*+ index( tab1 ind1 ) */
col3
from tab1
where col3 = 1;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

• PostgreSQL

(13)

https://www.postgresql.org/docs/current/storage-page-layout.html#HEAPTUPLEHEADERDATA-TABLE
http://pgsqldeepdive.blogspot.com/2012/12/postgresqlhot.html

リーフノードには、インデックスカラム値と、実際の行を検索するためのctidが含まれる

-- 動作確認

drop table tab1;
create table tab1(col1 int,col2 int,col3 int,col4 int);

alter table tab1 add constraint tab1pk primary key(col1,col2);
create index ind1 on tab1(col3);

insert into tab1 values(1,1,1,1);
insert into tab1 values(2,2,2,2);

/*+ IndexOnlyScan(tab1 tab1pk) */
explain
select col1,col2,col3,col4
from tab1
where col1 = 1 and col2 = 1;

/*+ IndexOnlyScan(tab1 ind1) */
explain
select col1,col2,col3,col4
from tab1
where col3 = 1;

/*+ IndexOnlyScan(tab1 ind1) */
explain
select col1,col2,col3
from tab1
where col3 = 1;

/*+ IndexOnlyScan(tab1 ind1) */
explain
select col3
from tab1
where col3 = 1;

• SQL Server

(2019)
https://docs.microsoft.com/ja-jp/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15#nonclustered-index-design-guidelines

(1)クラスター化インデックスの場合(通常、主キーの場合)
すべてのカラムがリーフノードに含まれる

(2)非クラスター化インデックスの場合
非クラスター化インデックスのカラムと主キーのカラムが含まれる

-- 動作確認

drop table tab1;
create table tab1(col1 int not null,col2 int not null,col3 int,col4 int);

alter table tab1 add constraint tab1pk primary key(col1,col2);
create index ind1 on tab1(col3);

insert into tab1 values(1,1,1,1);
insert into tab1 values(2,2,2,2);

select col1,col2,col3,col4
from tab1 with( index(0) )
where col1 = 1 and col2 = 1;

select col1,col2,col3,col4
from tab1 with( index(ind1) )
where col3 = 1;

select col1,col2,col3
from tab1 with( index(ind1) )
where col3 = 1;

select col3
from tab1 with( index(ind1) )
where col3 = 1;