カーディナリティは低いが、インデックス効果がありそうなパターンの検証

# はじめに

カーディナリティとは、**あるカラムにおいて、取りうる値の種類の事**です

例えば、性別の場合

* 男性
* 女性
* 不明

3種類とすると、カーディナリティは「3」になる。
この場合は、カーディナリティは低いと表現されます

B-treeインデックスを作成する際には、一般的にはカーディナリティが高いカラムを指定します。

例えば、性別のようなカーディナリティの低いカラムの場合。
データの比率が同じの場合において、インデックスに絞り込めるのは1/3程度なので、インデックスの効果は薄いと考えられます。

しかし、**データ比率が極端に異なる場合**。
インデックスが効果を発揮する場合もあるようなので、検証していきたいと思います

#  環境

mysql  Ver 8.0.32

# フラグのデータ数に偏りがある場合の検証

## インデックスあり

```sql
CREATE TABLE tests (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    is_test TINYINT(1)
);

CREATE INDEX is_test_index ON tests(is_test);
```

```
mysql> SHOW INDEX FROM tests;
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| tests |          0 | PRIMARY       |            1 | id          | A         |           0 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| tests |          1 | is_test_index |            1 | is_test     | A         |           0 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
```

testsテーブルを作成し、is_testカラムへインデックスを張ります。
is_testには、`0 or 1`のデータが入ります。

```
mysql> SELECT COUNT(is_test) FROM tests;
+----------------+
| COUNT(is_test) |
+----------------+
|         262144 |
+----------------+
1 row in set (0.06 sec)

mysql> SELECT COUNT(is_test) FROM tests WHERE is_test = 1;
+----------------+
| COUNT(is_test) |
+----------------+
|         262044 |
+----------------+
1 row in set (0.08 sec)
```

そして、100レコード分を除いた他全てのレコード(262044レコード)は、`is_test = 1`です。

それでは、indexが使用されるのか調べてみます。
`is_test = 0 → 1`へ更新する場合の処理をEXPLAINで見てみます

```
EXPLAIN UPDATE tests SET is_test = 1 WHERE is_test = 0\G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: tests
   partitions: NULL
         type: range
possible_keys: is_test_index
          key: is_test_index
      key_len: 5
          ref: const
         rows: 100
     filtered: 100.00
        Extra: Using where; Using temporary
1 row in set, 1 warning (0.00 sec)
```

`type: range`：is_test_indexへのindexを用いた範囲検索がされているようです。

では、`is_test = 1 → 0`への更新も見てみます

```
mysql> EXPLAIN UPDATE tests SET is_test = 0 WHERE is_test = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: tests
   partitions: NULL
         type: index
possible_keys: is_test_index
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 262201
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
```

こちらは、`type: index`となり**フルインデックススキャン**されています。
`key: PRIMARY`：プライマリーキーのindexが使用されています
`rows: 262201`：行数は不正確ですが、全行スキャンされるようになってしまっています。

## インデックスなし

先ほどと同じようtestsテーブルで、今度はインデックス無しの場合で検証します

```
mysql> EXPLAIN UPDATE tests SET is_test = 1 WHERE is_test = 0\G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: tests
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 262261
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
```

is_test = 0は、100レコードのみですが 結果は以下です。

`type: index`：**フルインデックススキャン**されている
`key: PRIMARY`：プライマリーキーのindexが使用
`rows: 262261`：全行スキャンされている

is_test = 1の場合も見てみます。

```
mysql> EXPLAIN UPDATE tests SET is_test = 0 WHERE is_test = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: UPDATE
        table: tests
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 262261
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)
```

同様の結果となることが分かります

>`type: index`：**フルインデックススキャン**されている
`key: PRIMARY`：プライマリーキーのindexが使用
`rows: 262261`：全行スキャンされている

# まとめ

データ比率が極端に異なる場合には、
分布の少ない値を検索する際にはインデックスの効果がありそうです

# 参考

https://www.amazon.co.jp/%E9%81%94%E4%BA%BA%E3%81%AB%E5%AD%A6%E3%81%B6SQL%E5%BE%B9%E5%BA%95%E6%8C%87%E5%8D%97%E6%9B%B8-%E7%AC%AC2%E7%89%88-%E5%88%9D%E7%B4%9A%E8%80%85%E3%81%A7%E7%B5%82%E3%82%8F%E3%82%8A%E3%81%9F%E3%81%8F%E3%81%AA%E3%81%84%E3%81%82%E3%81%AA%E3%81%9F%E3%81%B8-CodeZine-BOOKS/dp/4798157821