とある日

mita2db.hateblo.jp

この記事を見てて。

論理削除に使用するフラグ列deleted_atの型をTIMESTAMP型で定義してた。

そしてis NULLで判定していた。

論理削除をそうやって使用するんだと関心を持った。

論理削除を使用したことは、あまり多くないが過去に使用したときは、char型などを使用していた。

なので、TIMESTAMP型やDATE型で定義して削除日時を記録できるのはいいと思った。

が、気になった点が一つあった。

IS NULLってINDEX効くのかな？

TIMESTAMP型やDATA型より適した型ってあるのかと気になった。

ということで、調べてみます。

環境

項目	詳細
DataBase	MySQL 8.0.23
OS	CentOS8

詳しくは下記のテスト環境の記事を参照してください。

updraft.hatenadiary.com

検証内容

DATA型,CHAR型,BOOLEAN型のどれが論理削除のフラグ列として適しているのをINDEXの観点から調べようと思います。

諸注意：論理削除とは？や論理削除は必要か？などの話はしないです。

いざ検証

DATA

日時が記録されていたら、削除判定となる。

削除されていない行には、NULLが格納されている。

削除されているデータを探すときはIS NULLを使用するためINDEXが効くのか気になります。

テーブル構造

flag

CREATE TABLE null_index (
    id int PRIMARY KEY AUTO_INCREMENT,
    flag date
);

mysql> show create table null_index\G;
*************************** 1. row ***************************
       Table: null_index
Create Table: CREATE TABLE `null_index` (
  `id` int NOT NULL AUTO_INCREMENT,
  `flag` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `null_index_check` (`flag`)
) ENGINE=InnoDB AUTO_INCREMENT=131071 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

データ生成

任意のデータ数を生成してください。

INSERT INTO
    null_index(flag)
SELECT
    DATE_ADD('1900-02-03', INTERVAL 10000 * RAND() DAY)
FROM
    performance_schema.events_errors_summary_by_thread_by_error;

mysql> SELECT
    -> COUNT(*)
    -> FROM
    -> null_index;
+----------+
| COUNT(*) |
+----------+
|    70735 |
+----------+
1 row in set (0.05 sec)

NULLデータ生成

UPDATE
    null_index
SET
    flag = NULL
WHERE
    MONTH(flag) IN ('1', '2', '3', '4', '5', '6');

適当に、NULLデータを生成する。

INDEX作成

CREATE INDEX null_index_check ON null_index(flag);

mysql> show index from  null_index\G;
*************************** 1. row ***************************
        Table: null_index
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 71155
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: null_index
   Non_unique: 1
     Key_name: null_index_check
 Seq_in_index: 1
  Column_name: flag
    Collation: A
  Cardinality: 9035
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.00 sec)

INDEX検索

mysql> EXPLAIN SELECT * FROM null_index WHERE flag IS NULL\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: null_index
   partitions: NULL
         type: ref
possible_keys: null_index_check
          key: null_index_check
      key_len: 4
          ref: const
         rows: 35279
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

type: ref

IS NULLの検索でもしっかりとINDEXが使用されているのが確認できた。

NULLを撲滅しろと育ったので、あまりIS NULLを使用したクエリを作成しなかったため知らなかった。

検索速度

mysql> SELECT * FROM null_index WHERE flag IS NULL;

35556 rows in set (0.02 sec)

否定検索

否定した場合の検索速度とかも変わるのか検証。

mysql> EXPLAIN SELECT * FROM null_index WHERE flag IS NOT NULL\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: null_index
   partitions: NULL
         type: range
possible_keys: null_index_check
          key: null_index_check
      key_len: 4
          ref: NULL
         rows: 35279
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

type: rangeに変わった。

否定検索速度

mysql> SELECT * FROM null_index WHERE flag IS NOT NULL;

35179 rows in set (0.03 sec)

検索結果

あまり、差がでなかったが否定したときのほうが時間がかかる。

件数をもっと増やすとはっきりと結果が変わるかと。

検索種類	平均	一回目	二回目	三回目
IS NULL	0.02	0.02	0.02	0.02
IS NOT NULL	0.02333...	0.02	0.02	0.03

CHAR

「0」が格納されているとFALSE

「1」が格納されているとTRUEとなる。

テーブル構造

flag

CREATE TABLE char_index (
    id int PRIMARY KEY AUTO_INCREMENT,
    flag char(1) DEFAULT 0 NOT NULL
);

mysql> show create table char_index\G;
*************************** 1. row ***************************
       Table: char_index
Create Table: CREATE TABLE `char_index` (
  `id` int NOT NULL AUTO_INCREMENT,
  `flag` char(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `char_index_check` (`flag`)
) ENGINE=InnoDB AUTO_INCREMENT=131071 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

データ生成

任意のデータ数を生成してください。

さきほどのDATA型と違って生成時にランダムで0と1のデータを生成しています。

INSERT INTO
    char_index(flag)
SELECT
    TRUNCATE(RAND() + 0.5, 0)
FROM
    performance_schema.events_errors_summary_by_thread_by_error;

mysql> SELECT
    -> COUNT(*)
    -> FROM
    -> char_index;
+----------+
| COUNT(*) |
+----------+
|    75250 |
+----------+
1 row in set (0.05 sec)

INDEX作成

CREATE INDEX char_index_check ON char_index(flag);

mysql> show index from  char_index\G;
*************************** 1. row ***************************
        Table: char_index
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 75640
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: char_index
   Non_unique: 1
     Key_name: char_index_check
 Seq_in_index: 1
  Column_name: flag
    Collation: A
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.01 sec)

INDEX検索

mysql> EXPLAIN SELECT * FROM char_index WHERE flag = '1'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: char_index
   partitions: NULL
         type: ref
possible_keys: char_index_check
          key: char_index_check
      key_len: 4
          ref: const
         rows: 37820
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

type: ref

DATA型と同じtypeとなっている。

検索速度

mysql> SELECT * FROM char_index WHERE flag = '1';

37414 rows in set (0.03 sec)

FALSE検索

「0」がFALSEを表すフラグとなっている。

FALSEを検索した場合の検索速度とかも変わるのか検証。

mysql> EXPLAIN SELECT * FROM char_index WHERE flag = '0'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: char_index
   partitions: NULL
         type: ref
possible_keys: char_index_check
          key: char_index_check
      key_len: 4
          ref: const
         rows: 37820
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

変更点なし。

否定検索

「!＝」を使用した否定検索を行う。

mysql> EXPLAIN SELECT * FROM char_index WHERE flag != '1'\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: char_index
   partitions: NULL
         type: range
possible_keys: char_index_check
          key: char_index_check
      key_len: 4
          ref: NULL
         rows: 37821
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)

FALSE検索速度

mysql> SELECT * FROM char_index WHERE flag = '0';

37836 rows in set (0.03 sec)

否定検索速度

mysql> SELECT * FROM char_index WHERE flag != '1';

37836 rows in set (0.04 sec)

検索結果

flag = 1(TRUE)とflag = 0(FALSE)では検索方法は変わらないので速度差はあまり出ない。

否定検索を行うと少し遅くなった。

検索種類	平均	一回目	二回目	三回目
flag = 1(TRUE)	0.03666...	0.04	0.04	0.03
flag = 0(FALSE)	0.0333...	0.04	0.03	0.03
flag != 1(否定 FALSE)	0.04	0.04	0.04	0.04

BOOLEAN

あまり使ったことのない型。

CHAR型と格納する値は変わらない。

テーブル構造

CREATE TABLE bool_index (
    id int PRIMARY KEY AUTO_INCREMENT,
    flag boolean DEFAULT TRUE NOT NULL
);

mysql> show create table bool_index\G;
*************************** 1. row ***************************
       Table: bool_index
Create Table: CREATE TABLE `bool_index` (
  `id` int NOT NULL AUTO_INCREMENT,
  `flag` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  KEY `bool_index_check` (`flag`)
) ENGINE=InnoDB AUTO_INCREMENT=131071 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

データ生成

任意のデータ数を生成してください。

INSERT INTO
    bool_index(flag)
SELECT
    CASE
        WHEN TRUNCATE(RAND() + 0.5, 0) = 1.0 THEN false
        ELSE TRUE
    END AS b
FROM
    performance_schema.events_errors_summary_by_thread_by_error;

mysql> SELECT
    -> COUNT(*)
    -> FROM
    -> bool_index
    -> ;
+----------+
| COUNT(*) |
+----------+
|    75250 |
+----------+
1 row in set (0.05 sec)

INDEX作成

CREATE INDEX bool_index_check ON bool_index(flag);

mysql>  show index from  bool_index\G;
*************************** 1. row ***************************
        Table: bool_index
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 75699
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
*************************** 2. row ***************************
        Table: bool_index
   Non_unique: 1
     Key_name: bool_index_check
 Seq_in_index: 1
  Column_name: flag
    Collation: A
  Cardinality: 1
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
2 rows in set (0.02 sec)

INDEX検索

mysql> EXPLAIN SELECT * FROM bool_index WHERE flag = TRUE\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: bool_index
   partitions: NULL
         type: ref
possible_keys: bool_index_check
          key: bool_index_check
      key_len: 1
          ref: const
         rows: 37849
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

特に変わっては何はない。

検索速度

mysql> SELECT * FROM bool_index WHERE flag = TRUE;

37749 rows in set (0.03 sec)

FALSE検索

FALSEを検索した場合の検索速度とかも変わるのか検証。

mysql> EXPLAIN SELECT * FROM bool_index WHERE flag = false\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: bool_index
   partitions: NULL
         type: ref
possible_keys: bool_index_check
          key: bool_index_check
      key_len: 1
          ref: const
         rows: 37849
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

変更点なし。

否定検索

「!＝」を使用した否定検索を行う。

mysql> EXPLAIN SELECT * FROM bool_index WHERE flag != TRUE\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: bool_index
   partitions: NULL
         type: range
possible_keys: bool_index_check
          key: bool_index_check
      key_len: 1
          ref: NULL
         rows: 37850
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

FALSE検索速度

mysql>  SELECT * FROM bool_index WHERE flag = false;
37501 rows in set (0.02 sec)

否定検索速度

mysql> SELECT * FROM bool_index WHERE flag != TRUE;

37501 rows in set (0.03 sec)

検索結果

CHAR型と同じで検索種類の変化はあまり結果わ変わらないが、速度自体が少し早い。

検索種類	平均	一回目	二回目	三回目
flag = TRUE	0.0233...	0.02	0.02	0.03
flag = FALSE	0.02	0.02	0.02	0.02
flag != TRUE	0.03	0.03	0.03	0.03

検証結果

DATA型

検索種類	平均	一回目	二回目	三回目
IS NULL	0.02	0.02	0.02	0.02
IS NOT NULL	0.02333...	0.02	0.02	0.03

CHAR型

検索種類	平均	一回目	二回目	三回目
flag = 1(TRUE)	0.03666...	0.04	0.04	0.03
flag = 0(FALSE)	0.0333...	0.04	0.03	0.03
flag != 1(否定 FALSE)	0.04	0.04	0.04	0.04

BOOLEAN型

検索種類	平均	一回目	二回目	三回目
flag = TRUE	0.0233...	0.02	0.02	0.03
flag = FALSE	0.02	0.02	0.02	0.02
flag != TRUE	0.03	0.03	0.03	0.03

まとめ

型	TYPE	TYPE（否定）	検索速度平均	検索速度平均（否定）
DATA	ref	range	0.02	0.02333...
CHAR	ref	range	0.03666...	0.04
BOOLEAN	ref	range	0.02333....	0.03

INDEXの挙動として、各型に変化はみられなかった。

ただ、CHAR型が速度がほか2つに比べて遅かった。

否定検索については、全ての型で速度の低下が見られた。

しかし、CHAR型とBOOLEAN型では、等価比較のままFALSEを検索できるので否定検索を行わない検索手段を選択できる。

〆

IS NULLでのINDEX使えるの知らなかったので勉強になった。

フラグ列としては、データサイズや今後の仕様変更による状態追加とかで拡張性など複数の要件によって型の選択は変わるとは思いますが、今回のINDEXが一つの参考になればと。

最近は、INDEXについて調べたがる年頃になってしまった。

論理削除の使用は計画的に。

目次

とある日

環境

検証内容

いざ検証

DATA

テーブル構造

データ生成

NULLデータ生成

INDEX作成

INDEX検索

検索速度

否定検索

否定検索速度

検索結果

CHAR

テーブル構造

データ生成

INDEX作成

INDEX検索

検索速度

FALSE検索

否定検索

FALSE検索速度

否定検索速度

検索結果

BOOLEAN

テーブル構造

データ生成

INDEX作成

INDEX検索

検索速度

FALSE検索

否定検索

FALSE検索速度

否定検索速度

検索結果

検証結果

DATA型

CHAR型

BOOLEAN型

まとめ

〆