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にする

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の割合

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回実行

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

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にすべきだが、速度的には候補に入らない。