はじめに
カーディナリティとは、あるカラムにおいて、取りうる値の種類の事です
例えば、性別の場合
- 男性
- 女性
- 不明
3種類とすると、カーディナリティは「3」になる。
この場合は、カーディナリティは低いと表現されます
B-treeインデックスを作成する際には、一般的にはカーディナリティが高いカラムを指定します。
例えば、性別のようなカーディナリティの低いカラムの場合。
データの比率が同じの場合において、インデックスに絞り込めるのは1/3程度なので、インデックスの効果は薄いと考えられます。
しかし、データ比率が極端に異なる場合。
インデックスが効果を発揮する場合もあるようなので、検証していきたいと思います
環境
mysql Ver 8.0.32
フラグのデータ数に偏りがある場合の検証
インデックスあり
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：全行スキャンされている
まとめ
データ比率が極端に異なる場合には、
分布の少ない値を検索する際にはインデックスの効果がありそうです
参考