http://tihiro.hatenablog.com/entry/2017/08/31/120917
create table tab1(col1 int);
create index ind11 on tab1(col1);
insert into tab1 values(1);
insert into tab1 values(2);
insert into tab1 values(3);
commit;
--インデックスの断片化を確認
analyze index ind11 validate structure;
select * from index_stats;
select
name
, height
, lf_rows
, del_lf_rows
, del_lf_rows / lf_rows as fragmentation
from
index_stats
;
--断片化解消
alter index ind11 rebuild online;
https://yohei-a.hatenablog.jp/entry/20180610/1528650004
sysbench --db-driver=mysql --mysql-user=root --mysql-password='Aaa!1234' \
--tables=10 --table_size=10000 --mysql-db=test oltp_read_write prepare
--DATA_FREE カラムには、InnoDB テーブルの空き領域がバイト単位で表示されます。
select table_schema, table_name, data_free, table_rows
from information_schema.tables
where table_name='sbtest1';
--ALTER TABLE でフラグメンテーションを解消
alter table sbtest1 engine innodb;
create extension pgstattuple;
SELECT * FROM pgstattuple('tab1');
SELECT * FROM pgstatindex('ind1');
断片化の解消にはvacuum fullやreindex index ind1を使用できるが
これらは使用中に更新できないのでpg_repackを使用する
--インデックスの断片化を確認
USE test; GO
SELECT a.index_id, name, avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats (DB_ID(N'test'), OBJECT_ID(N'dbo.tab2'), NULL, NULL, NULL) AS a JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id; GO
--カラムストアインデックスの断片化の確認SELECT OBJECT_NAME(object_id), 100 * (ISNULL(deleted_rows,0)) / total_rows AS fragmentation, * FROM sys.dm_db_column_store_row_group_physical_stats
--断片化解消
USE test; GO
--断片化率10-30%の場合ALTER INDEX tab2p ON dbo.tab2 REORGANIZE ; GO
alter index all on sales.salesorderdetail reorganize
--断片化率30%以上の場合ALTER INDEX tab2p ON dbo.tab2 REBUILD WITH (ONLINE = ON);GO
alter index all on sales.salesorderdetail rebuild
