DBの0/1のデータの型を違う型に置き換えた場合の速度検証比較

DBの0/1のデータの型を違う型に置き換えた場合の速度検証比較

display_flg等のflg系はvarchar、int、boolといろいろできる。
カーディナリティが低くなりそうだし、検索では毎回where句に入るため使用頻度が高い。

ちなみにpertitionはあまり効果が得られなかった。

# 環境

- m-1mac
- mariadb10.2.44(mysql5.7互換)
- phpstorm

# 準備

`my.cnf`で`performance_schema=on`になってることを確認し、実行時間が取れるように各項目をONにする

``` sql
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED   = 'YES' WHERE NAME LIKE '%statement/%';
UPDATE performance_schema.setup_instruments SET ENABLED = 'YES', TIMED   = 'YES' WHERE NAME LIKE '%stage/%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_statements_%';
UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME LIKE '%events_stages_%';
```

## 検証SQL

1000万件のデータを挿入後、selectを10回実行し、平均値を求める。


データ挿入
1と0とnullが8:1:1の割合

```sql
create table test
(
    varflg  varchar(4) null,
    intflg  int        null,
    boolflg bool       null,
    bitflg bit       null
);

create index test_intflg_index
    on test (intflg);

create index test_varflg_index
    on test (varflg);

create index test_boolflg_index
    on test (boolflg);

create index test_bitflg_index
    on test (bitflg);

INSERT INTO `test` (`varflg`, `intflg`, `boolflg`, `bitflg`)
VALUES ('1', 1, 1, 1),
       ('1', 1, 1, 1),
       ('1', 1, 1, 1),
       ('1', 1, 1, 1),
       ('1', 1, 1, 1),
       ('1', 1, 1, 1),
       ('1', 1, 1, 1),
       ('1', 1, 1, 1),
       (null, null, null, null),
       ('0', 0, 0, 0);

insert into test(select test.varflg,
                        test.intflg,
                        test.boolflg,
                        test.bitflg
                 from test,
                      test sample2,
                      test sample3,
                      test sample4,
                      test sample5,
                      test sample6,
                      test sample7);
```

以下を10回実行

```sql
select SQL_NO_CACHE * from test where varflg = 0;
select SQL_NO_CACHE * from test where varflg = 1;
select SQL_NO_CACHE * from test where varflg = '0';
select SQL_NO_CACHE * from test where varflg = '1';
select SQL_NO_CACHE * from test where varflg is null;
select SQL_NO_CACHE * from test where varflg is not null;

select SQL_NO_CACHE * from test where intflg = 0;
select SQL_NO_CACHE * from test where intflg = 1;
select SQL_NO_CACHE * from test where intflg = '0';
select SQL_NO_CACHE * from test where intflg = '1';
select SQL_NO_CACHE * from test where intflg is null;
select SQL_NO_CACHE * from test where intflg is not null;

select SQL_NO_CACHE * from test where !boolflg;
select SQL_NO_CACHE * from test where boolflg;
select SQL_NO_CACHE * from test where boolflg = '0';
select SQL_NO_CACHE * from test where boolflg = '1';
select SQL_NO_CACHE * from test where boolflg = 0;
select SQL_NO_CACHE * from test where boolflg = 1;
select SQL_NO_CACHE * from test where boolflg = true;
select SQL_NO_CACHE * from test where boolflg = false;
select SQL_NO_CACHE * from test where boolflg is null;
select SQL_NO_CACHE * from test where boolflg is not null;

select SQL_NO_CACHE * from test where !bitflg;
select SQL_NO_CACHE * from test where bitflg;
select SQL_NO_CACHE * from test where bitflg = 0;
select SQL_NO_CACHE * from test where bitflg = 1;
select SQL_NO_CACHE * from test where bitflg = true;
select SQL_NO_CACHE * from test where bitflg = false;
select SQL_NO_CACHE * from test where bitflg is null;
select SQL_NO_CACHE * from test where bitflg is not null;
```

各直近10回実行の平均を求めるSQL

```sql
SELECT SQL_TEXT,
       AVG(Duration) as AVG
FROM (SELECT EVENT_ID,
             TRUNCATE(TIMER_WAIT / 1000000000000, 6) AS Duration,
             SQL_TEXT,
             row_number() over (partition by SQL_TEXT order by TIMER_END desc) as rank
      FROM performance_schema.events_statements_history_long
      WHERE SQL_TEXT like '%flg%') res
WHERE rank <= 10
GROUP BY SQL_TEXT

```

## 結果表


| where句  | varchar(4)   | int          | bool,tinyint(1) | bit          | 
| -------- | ------------ | ------------ | --------------- | ------------ | 
| '0'      | 0.0013776667 | 0.0011710000 | 0.0018630000    |              | 
| '1'      | 0.0013313333 | 0.0013320000 | 0.0016633333    |              | 
| 0        | 0.0048543333 | 0.0016660000 | 0.0013770000    | 0.0024933333 | 
| 1        | 0.0010170000 | 0.0020743333 | 0.0013925000    | 0.0031886667 | 
| null     | 0.0016286667 | 0.0018106667 | 0.0015942500    | 0.0022587778 | 
| not null | 0.0024780000 | 0.0015013333 | 0.0014205000    | 0.0019101111 | 
| false    |              |              | 0.0015870000    | 0.0015330000 | 
| true     |              |              | 0.0016912500    | 0.0029142222 | 
| !(flg)   |              |              | 0.0015446667    | 0.0013951250 | 
| (flg)    |              |              | 0.0019150000    | 0.0023671111 | 

## まとめ

- varcharフラグ
  - `'1'`なら`'1'`で比較するより`1`で比較したほうが約1.3倍早い
  - ただし`0`比較は遅くなる。

- intフラグ
  - 容量的に基本はtinyintでいいかも。

- tinyintフラグ
  - どのパターンでも安定した速さ。`true/false`ではなく`1/0`で比較するほうが速い。

- bitフラグ
  - `1/0`のみ許容するならbitにすべきだが、速度的には候補に入らない。