(8.0.33)
https://dev.mysql.com/doc/refman/8.0/ja/innodb-compression.html
テーブル圧縮やページ圧縮(transparent page compression)
により
インデックスデータも圧縮されるため、セカンダリインデックスを含むテーブルでは利点も増幅されます。
(23c)
https://techblog.raccoon.ne.jp/archives/1622505873.html
https://docs.oracle.com/cd/F19136_01/sqlrf/CREATE-INDEX.html#GUID-1F89BBC0-825F-4215-AF71-7588E31D8BFE
https://blogs.oracle.com/otnjp/post/shibacho-042
-- 1. テストテーブル準備
drop table tab1 purge;
create table tab1(
col1 varchar2(100)
,col2 varchar2(100)
,col3 varchar2(100)
,col4 varchar2(100)
,col5 varchar2(100)
,col6 varchar2(100)
);
declare
begin
for i in 1..1000000 loop
insert into tab1 values(
to_char(floor(dbms_random.value(1, 100001) ) )
,to_char(floor(dbms_random.value(1, 100001) ) )
,to_char(floor(dbms_random.value(1, 100001) ) )
,to_char(floor(dbms_random.value(1, 100001) ) )
,to_char(floor(dbms_random.value(1, 100001) ) )
,to_char(floor(dbms_random.value(1, 100001) ) )
);
end loop;
end;
/
commit;
select count(*) from tab1;
select * from tab1 fetch first 10 rows only;
-- 2. 索引圧縮を有効化したインデックス作成
(1) 接頭辞圧縮
create index インデックス名 on テーブル名 (カラム1, カラム2 ...) compress 接頭辞の長さ;
一意の索引の場合、接頭辞の長さの有効範囲は、1からキー列の数から1を引いた数までです。デフォルトの接頭辞の長さは、(キー列の数-1)です。
一意でない索引の場合、接頭辞の長さの有効範囲は、1からキー列の数までです。デフォルトの接頭辞の長さはキー列数です。
create index ind1 on tab1(col1) compress 1 online;
create index ind4 on tab1(col4) online;
alter index ind4 rebuild compress 1 online;
(2) 拡張索引圧縮
→ Advanced Compression オプションに含まれる機能
create index インデックス名 on テーブル名 (カラム1, カラム2 ...) compress advanced low;
一意でない索引または2列以上の一意索引に指定できます。
create index インデックス名 on テーブル名 (カラム1, カラム2 ...) compress advanced high;
一意でない索引または1列以上の一意索引に指定できます。
create index ind2 on tab1(col2) compress advanced low online;
create index ind3 on tab1(col3) compress advanced high online;
create index ind5 on tab1(col5) online;
create index ind6 on tab1(col6) online;
alter index ind5 rebuild compress advanced low online;
alter index ind6 rebuild compress advanced high online;
-- 3. リーフブロック数確認
col index_name for a20
select index_name, index_type, compression, leaf_blocks
from user_indexes
where table_name = 'TAB1'
order by index_name;
(15)
索引圧縮機能なし
-- 1. テストテーブル準備
use test
drop table tab1;
create table tab1(
col1 char(100)
,col2 char(100)
,col3 char(100)
,col4 char(100)
);
set nocount on;
declare @i int;
set @i = 1;
begin transaction;
while @i <= 1000000
begin
insert into tab1
select cast( (floor(rand() * 100000)+1) as varchar )
, cast( (floor(rand() * 100000)+1) as varchar )
, cast( (floor(rand() * 100000)+1) as varchar )
, cast( (floor(rand() * 100000)+1) as varchar )
set @i = @i + 1;
end
commit;
select count(*) from tab1;
select top(10) * from tab1;
-- 2. 索引圧縮を有効化したインデックス作成
テーブルの圧縮プロパティは非クラスター化インデックスに継承されません。
インデックスを圧縮するには、インデックスの圧縮プロパティを明示的に設定する必要があります。
ROW または PAGE 圧縮は、オンラインまたはオフラインで有効または無効にすることができます。
データが最初のデータ ページに追加されると、データに対して行の圧縮が行われます。
(1) 行圧縮
create NONCLUSTERED index ind1 on tab1(col1) with ( data_compression = row, online = on );
create NONCLUSTERED index ind3 on tab1(col3) with ( online = on );
alter index ind3 on tab1 rebuild partition = all with ( data_compression = row, online = on );
(2) ページ圧縮 ( 行圧縮 + プレフィックス圧縮 + ディクショナリ圧縮 )
create NONCLUSTERED index ind2 on tab1(col2) with ( data_compression = page, online = on );
create NONCLUSTERED index ind4 on tab1(col4) with ( online = on );
alter index ind4 on tab1 rebuild partition = all with ( data_compression = page, online = on );
-- 3. リーフブロック数確認
select a.index_id, b.name, a.page_count
from sys.dm_db_index_physical_stats (db_id(N'test'),
object_id(N'dbo.tab1'), null, null, null) as a
join sys.indexes as b
on a.object_id = b.object_id and a.index_id = b.index_id
order by b.name
