MYSQL壓縮表測試

1. 建立表

-- 壓縮表

SET GLOBAL innodb_file_per_table=1;

SET GLOBAL innodb_file_format=Barracuda;

CREATE TABLE compress_01 (

    id varchar(36) PRIMARY KEY

    ,name varchar(255)

)

ROW_FORMAT=COMPRESSED

KEY_BLOCK_SIZE=8

;

CREATE TABLE compress_02 (

    id varchar(36) PRIMARY KEY

    ,name varchar(255)

)

ROW_FORMAT=COMPRESSED

KEY_BLOCK_SIZE=8

;

CREATE TABLE compress_03 (

    id varchar(36) PRIMARY KEY

    ,name varchar(255)

)

ROW_FORMAT=COMPRESSED

KEY_BLOCK_SIZE=8

;

-- 普通表

CREATE TABLE normal_01 (

    id varchar(36) PRIMARY KEY

    ,name varchar(255)

)

;

CREATE TABLE normal_02 (

    id varchar(36) PRIMARY KEY

    ,name varchar(255)

)

;

2. 造測試資料

insert into compress_01 

select uuid() as id, 'name_1' as name

;

insert into compress_01 

select uuid() as id, 'name_2' as name

from compress_01

;

insert into compress_01 

select uuid() as id, 'name_3' as name

from compress_01

;

insert into compress_01 

select uuid() as id, 'name_24' as name

from compress_01

;

3. 插入效能測試

-- 3.1 壓縮表 to 普通表

insert into normal_01

select * from compress_01

;

> Affected rows: 8388608

> 時間: 244.767s

-- 3.2 普通表 to 普通表

insert into normal_02

select * from normal_01

;

> Affected rows: 8388608

> 時間: 242.232s

-- 3.3 壓縮表 to 壓縮表

insert into compress_02

select * from compress_01

;

> Affected rows: 8388608

> 時間: 250.885s

-- 3.4 普通表 to 壓縮表

insert into compress_03

select * from normal_02

;

> Affected rows: 8388608

> 時間: 253.055s

4. 單表查詢效能測試

-- 4.1 select查詢 主鍵

select * from normal_01 where id = '408f0dd5-3abb-11eb-b5bb-0242ac110002' limit 10; -- 0.037

select * from compress_02 where id = '408f0dd5-3abb-11eb-b5bb-0242ac110002' limit 10; -- 0.039

-- 4.2 select查詢 索引

-- 4.3 select查詢 非主鍵

select * from normal_01 where name = 'name_23' limit 10; -- 0.616

select * from compress_02 where name = 'name_23' limit 10; -- 1.087

-- 4.4 count查詢

select count(1) as cnt from normal_01;   -- 1.018

select count(1) as cnt from compress_02; -- 2.269

-- 4.5 count distinct 查詢

select count(distinct name) as cnt from normal_01;   -- 5.044

select count(distinct name) as cnt from compress_02; -- 7.131

-- 4.6 分頁查詢

select * from normal_01 limit 50000,10; -- 0.043

select * from compress_02 limit 50000,10; -- 0.043

-- 4.6 in查詢

5. 多表關聯查詢效能測試（雪花模型）

6. 多表關聯查詢效能測試（大表關聯大表）

7. 刪除資料效能

-- 7.1 刪除記錄

-- 7.2 truncate table