![[初心者向け] MySQL-カーディナリティが分からなかったので検証してみた](https://devio2024-media.developers.io/image/upload/f_auto,q_auto,w_3840/v1762394953/user-gen-eyecatch/hpwv5ctq7q4unxyfkps9.png)
[初心者向け] MySQL-カーディナリティが分からなかったので検証してみた
2025.11.06
MySQL のインデックスを作成するときによくカーディナリティという言葉を耳にするのですが、自分がいまいち理解できていなかったため、今回色んなパターンで検証してみました。
本ブログでは、カーディナリティが高いカラム、カーディナリティが同じ数だが値に偏りがあるカラムに対してインデックスを作成し、検索速度がどう変化するのか調べてみました。サンプルデータは 10 万行です。
なお、実行環境は EC2（Amazon Linux 2023）上にインストールした mysql です。
EC2 に mysql をインストールする方法は下記ブログをご参照ください。
使用した mysql のバージョンです。
$ mysql --version
mysql Ver 8.4.6 for Linux on x86_64 (MySQL Community Server - GPL)
カーディナリティとは
カーディナリティとは、「そのカラムが何種類の値を持つか」を表す指標です。
例えば以下のようなメールアドレスの例だと、ほとんど全部異なる値になるので、カーディナリティは比較的高いと言えます。（カーディナリティ = メールアドレス数）
【メールアドレス】
user1@example.com
user2@example.com
user3@example.com
user4@example.com
...
一方で性別を例に挙げると、以下のように取りうる値は male(男性), female(女性), Other(他) の 3 種類となり、カーディナリティは低いと言えます。(カーディナリティ = 3)
【性別】
male
female
Other
male
Other
male
female
male
すなわち
- カーディナリティが高い = バリエーションが豊富
- カーディナリティが低い = 同じ値が何度も出てくる
となります。
サンプルデータ作成
検証用のサンプルデータを作成していきます。
データベースおよびテーブルの作成
-- データベース作成
CREATE DATABASE IF NOT EXISTS cardinality_test;
USE cardinality_test;
-- テーブル削除（既にある場合）
DROP TABLE IF EXISTS users;
-- テーブル作成
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255),
country VARCHAR(50),
city VARCHAR(50)
) ENGINE=InnoDB;
10 万行データ作成のためのプロシージャを作成
DELIMITER //
CREATE PROCEDURE generate_final_data()
BEGIN
DECLARE i INT DEFAULT 1;
SET autocommit = 0;
WHILE i <= 100000 DO
INSERT INTO users (email, country, city)
VALUES (
-- email: 全部ユニーク（100,000種類）
CONCAT('user', i, '@example.com'),
-- country: 1,000種類（99%が偏り）
-- 最初の99,000行は'Japan'、残り1,000行は全部違う国
IF(i <= 99000,
'Japan',
CONCAT('Country', i - 99000) -- Country1 ~ Country1000
),
-- city: 1,000種類（均等分散）
-- 各cityに100行ずつ
CONCAT('City', FLOOR((i - 1) / 100)) -- City0 ~ City999
);
IF i % 5000 = 0 THEN
COMMIT;
END IF;
SET i = i + 1;
END WHILE;
COMMIT;
SET autocommit = 1;
END //
DELIMITER ;
プロシージャを実行
CALL generate_final_data();
データが作成されたか確認。10 万行レコードがカウントされているので OK
mysql> SELECT COUNT(*) FROM users;
+----------+
| COUNT(*) |
+----------+
| 100000 |
+----------+
1 row in set (0.01 sec)
サンプルテーブルのデータ構造
前項にて作成した users テーブルのデータ構造について説明します。
全体像は以下の通りです。
- id と email カラムは連番
- country カラム は 99000 までは
Japan, それ以降はCountry1,Country2,...と続く - city カラムは 100 個ずつ
City0,City1,City2... の値を取っていく
mysql> select * from users;
+--------+------------------------+-------------+---------+
| id | email | country | city |
+--------+------------------------+-------------+---------+
| 1 | user1@example.com | Japan | City0 |
| 2 | user2@example.com | Japan | City0 |
| 3 | user3@example.com | Japan | City0 |
| 4 | user4@example.com | Japan | City0 |
...
| 99 | user99@example.com | Japan | City0 |
| 100 | user100@example.com | Japan | City0 |
| 101 | user101@example.com | Japan | City1 |
| 102 | user102@example.com | Japan | City1 |
...
| 199 | user199@example.com | Japan | City1 |
| 200 | user200@example.com | Japan | City1 |
| 201 | user201@example.com | Japan | City2 |
| 202 | user202@example.com | Japan | City2 |
...
| 98999 | user98999@example.com | Japan | City989 |
| 99000 | user99000@example.com | Japan | City989 |
| 99001 | user99001@example.com | Country1 | City990 |
| 99002 | user99002@example.com | Country2 | City990 |
| 99003 | user99003@example.com | Country3 | City990 |
...
| 99996 | user99996@example.com | Country996 | City999 |
| 99997 | user99997@example.com | Country997 | City999 |
| 99998 | user99998@example.com | Country998 | City999 |
| 99999 | user99999@example.com | Country999 | City999 |
| 100000 | user100000@example.com | Country1000 | City999 |
+--------+------------------------+-------------+---------+
100000 rows in set (0.06 sec)
email, country, city カラムのそれぞれのカーディナリティ(種類数)は以下です。
-- email のカーディナリティは全体レコード数(=10万)と同値
SELECT
COUNT(*) as 全体の行数,
COUNT(DISTINCT email) as ユニークな値の数
FROM users;
+-----------------+--------------------------+
| 全体の行数 | ユニークな値の数 |
+-----------------+--------------------------+
| 100000 | 100000 |
+-----------------+--------------------------+
1 row in set (0.63 sec)
-- country のカーディナリティは 1001 (Japan 1個 + Country1〜1000 の 1000個)
SELECT COUNT(DISTINCT country) as 国の種類数 FROM users;
+-----------------+
| 国の種類数 |
+-----------------+
| 1001 |
+-----------------+
1 row in set (0.09 sec)
-- city のカーディナリティは 1000 (City0~City999)
SELECT COUNT(DISTINCT city) as 都市の種類数 FROM users;
+--------------------+
| 都市の種類数 |
+--------------------+
| 1000 |
+--------------------+
1 row in set (0.14 sec)
まとめると今回作成した users テーブルの特徴は以下です。
| カラム | カーディナリティ(種類数) | 分布の特徴 |
|---|---|---|
| 100,000 | 全てユニーク | |
| country | 1,001 | 99 % が Japan (極端に偏りがある) |
| city | 1,000 | 各都市100行ずつ（完全に均等） |
country と city はほぼ同程度のカーディナリティですが、分布に違いがある点がポイントです。
【country】
Japan ■■■■■■■■■■■■■■■■■■■■ 99,000行
Country1 ■ 1行
Country2 ■ 1行
...
【city】
City0 ■■■■■ 100行
City1 ■■■■■ 100行
City2 ■■■■■ 100行
City3 ■■■■■ 100行
...
時間計測の準備
以下を参考に SHOW PROFILE ステートメントで各クエリの時間計測ができるように設定しておきます。
設定手順
mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SET profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SELECT @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+
1 row in set, 1 warning (0.00 sec)
検証1: カーディナリティが高い場合 (email)
emailカラム（100,000種類）で特定のメールアドレス user50000@example.com を検索します。
まず、EXPLAIN で実行計画を見ます。
以下結果より、type が all のため、フルテーブルスキャンであることがわかります。
mysql> EXPLAIN SELECT * FROM users WHERE email = 'user50000@example.com';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 100116 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
実際に実行してみると、0.036 秒かかりました。
mysql> SELECT * FROM users WHERE email = 'user50000@example.com';
+-------+-----------------------+---------+---------+
| id | email | country | city |
+-------+-----------------------+---------+---------+
| 50000 | user50000@example.com | Japan | City499 |
+-------+-----------------------+---------+---------+
1 row in set (0.04 sec)
mysql> show profiles;
+----------+------------+-------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------------+
...
| 4 | 0.03677050 | SELECT * FROM users WHERE email = 'user50000@example.com' |
+----------+------------+-------------------------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)
続いて、インデックスを作成し、再度同様の検索を行ってみます。
インデックス作成
mysql> CREATE INDEX idx_email ON users(email);
Query OK, 0 rows affected (0.86 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from users;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users | 0 | PRIMARY | 1 | id | A | 100116 | NULL | NULL | | BTREE | | | YES | NULL |
| users | 1 | idx_email | 1 | email | A | 100000 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.01 sec)
以下結果より、実行時間が 0.0003 秒とインデックスなしの時の比較し、かなり高速になっていることがわかります。
EXPLAIN の rows も 1 行のため、処理効率が上がっていることが見て取れます。
mysql> EXPLAIN SELECT * FROM users WHERE email = 'user50000@example.com';
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | users | NULL | ref | idx_email | idx_email | 1023 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM users WHERE email = 'user50000@example.com';
+-------+-----------------------+---------+---------+
| id | email | country | city |
+-------+-----------------------+---------+---------+
| 50000 | user50000@example.com | Japan | City499 |
+-------+-----------------------+---------+---------+
1 row in set (0.00 sec)
mysql> show profiles;
+----------+------------+-------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------------+
...
| 9 | 0.00037200 | SELECT * FROM users WHERE email = 'user50000@example.com' |
+----------+------------+-------------------------------------------------------------------+
9 rows in set, 1 warning (0.00 sec)
カーディナリティが高いカラムの場合、インデックスを作成することで劇的に検索効率が上がることがわかりました。
検証2: 偏った分布の場合 (country)
インデックスなし
- 多数派(
Japan)を検索
インデックスなしで Japan を検索すると、以下より実行時間は 0.069 秒でした。
mysql> EXPLAIN SELECT * FROM users WHERE country = 'Japan';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 100116 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.01 sec)
mysql> SELECT * FROM users WHERE country = 'Japan';
+-------+-----------------------+---------+---------+
| id | email | country | city |
+-------+-----------------------+---------+---------+
| 1 | user1@example.com | Japan | City0 |
| 2 | user2@example.com | Japan | City0 |
...
| 98999 | user98999@example.com | Japan | City989 |
| 99000 | user99000@example.com | Japan | City989 |
+-------+-----------------------+---------+---------+
99000 rows in set (0.07 sec)
mysql> show profiles;
+----------+------------+-------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------------+
...
| 10 | 0.06963775 | SELECT * FROM users WHERE country = 'Japan' |
+----------+------------+-------------------------------------------------------------------+
10 rows in set, 1 warning (0.00 sec)
- 少数派 (
Country500) を検索
インデックスなしで特定行 Country500 を検索すると、以下より 0.032 秒でした。
mysql> EXPLAIN SELECT * FROM users WHERE country = 'Country500';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 100116 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM users WHERE country = 'Country500';
+-------+-----------------------+------------+---------+
| id | email | country | city |
+-------+-----------------------+------------+---------+
| 99500 | user99500@example.com | Country500 | City994 |
+-------+-----------------------+------------+---------+
1 row in set (0.04 sec)
mysql> show profiles;
+----------+------------+-------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------------+
...
| 13 | 0.03256675 | SELECT * FROM users WHERE country = 'Country500' |
+----------+------------+-------------------------------------------------------------------+
13 rows in set, 1 warning (0.00 sec)
インデックスあり
インデックスを作成し、同様に検索をします。
mysql> CREATE INDEX idx_country ON users(country);
Query OK, 0 rows affected (0.47 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW INDEX FROM users;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users | 0 | PRIMARY | 1 | id | A | 100116 | NULL | NULL | | BTREE | | | YES | NULL |
| users | 1 | idx_email | 1 | email | A | 100000 | NULL | NULL | YES | BTREE | | | YES | NULL |
| users | 1 | idx_country | 1 | country | A | 999 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
3 rows in set (0.01 sec)
- 多数派(
Japan)を検索
インデックスありで Japan を検索すると、以下より実行時間は 0.24 秒でした。インデックスなしの場合（0.069 秒）と比較し、むしろ遅くなっています。
mysql> EXPLAIN SELECT * FROM users WHERE country = 'Japan';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+-------+----------+-------+
| 1 | SIMPLE | users | NULL | ref | idx_country | idx_country | 203 | const | 50058 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+-------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM users WHERE country = 'Japan';
+-------+-----------------------+---------+---------+
| id | email | country | city |
+-------+-----------------------+---------+---------+
| 1 | user1@example.com | Japan | City0 |
| 2 | user2@example.com | Japan | City0 |
...
| 98999 | user98999@example.com | Japan | City989 |
| 99000 | user99000@example.com | Japan | City989 |
+-------+-----------------------+---------+---------+
99000 rows in set (0.25 sec)
mysql> show profiles;
+----------+------------+-------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------------+
...
| 17 | 0.24781550 | SELECT * FROM users WHERE country = 'Japan' |
+----------+------------+-------------------------------------------------------------------+
15 rows in set, 1 warning (0.00 sec)
- 少数派 (
Country500) を検索
インデックスありで Country500 を検索すると、以下より実行時間は 0.0003 秒でした。インデックスなしの場合（0.032 秒）と比較し、高速化しています。
mysql> EXPLAIN SELECT * FROM users WHERE country = 'Country500';
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | users | NULL | ref | idx_country | idx_country | 203 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM users WHERE country = 'Country500';
+-------+-----------------------+------------+---------+
| id | email | country | city |
+-------+-----------------------+------------+---------+
| 99500 | user99500@example.com | Country500 | City994 |
+-------+-----------------------+------------+---------+
1 row in set (0.00 sec)
mysql> show profiles;
+----------+------------+-------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------------+
...
| 19 | 0.00035075 | SELECT * FROM users WHERE country = 'Country500' |
+----------+------------+-------------------------------------------------------------------+
15 rows in set, 1 warning (0.00 sec)
以上の結果より、値に偏りがあるカラムの場合、インデックスを作成しても結果が速くなる場合と遅くなる場合の両方が存在し、結果的に速度が安定しないことがわかりました。
検証3: 均等分布の場合 (city)
インデックスなし
均等分布の場合、インデックスなしだと、0.036 秒かかっています。
mysql> EXPLAIN SELECT * FROM users WHERE city = 'City500';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | users | NULL | ALL | NULL | NULL | NULL | NULL | 100116 | 10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM users WHERE city = 'City500';
+-------+-----------------------+---------+---------+
| id | email | country | city |
+-------+-----------------------+---------+---------+
| 50001 | user50001@example.com | Japan | City500 |
| 50002 | user50002@example.com | Japan | City500 |
...
| 50099 | user50099@example.com | Japan | City500 |
| 50100 | user50100@example.com | Japan | City500 |
+-------+-----------------------+---------+---------+
100 rows in set (0.03 sec)
mysql> show profiles;
+----------+------------+-------------------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-------------------------------------------------------------------+
...
| 22 | 0.03645000 | SELECT * FROM users WHERE city = 'City500' |
+----------+------------+-------------------------------------------------------------------+
15 rows in set, 1 warning (0.00 sec)
インデックスあり
インデックスを作成します。
mysql> CREATE INDEX idx_city ON users(city);
Query OK, 0 rows affected (0.60 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW INDEX FROM users;
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users | 0 | PRIMARY | 1 | id | A | 100116 | NULL | NULL | | BTREE | | | YES | NULL |
| users | 1 | idx_email | 1 | email | A | 100000 | NULL | NULL | YES | BTREE | | | YES | NULL |
| users | 1 | idx_country | 1 | country | A | 999 | NULL | NULL | YES | BTREE | | | YES | NULL |
| users | 1 | idx_city | 1 | city | A | 1020 | NULL | NULL | YES | BTREE | | | YES | NULL |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.01 sec)
インデックスなしの時と同様に City500 を検索した結果、以下の通り 0.0005 秒となり、なしの時の 0.036 秒と比較すると劇的に速くなりました。
mysql> EXPLAIN SELECT * FROM users WHERE city = 'City500';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | users | NULL | ref | idx_city | idx_city | 203 | const | 100 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM users WHERE city = 'City500';
+-------+-----------------------+---------+---------+
| id | email | country | city |
+-------+-----------------------+---------+---------+
| 50001 | user50001@example.com | Japan | City500 |
| 50002 | user50002@example.com | Japan | City500 |
...
| 50099 | user50099@example.com | Japan | City500 |
| 50100 | user50100@example.com | Japan | City500 |
+-------+-----------------------+---------+---------+
100 rows in set (0.00 sec)
mysql> show profiles;
+----------+------------+----------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------------------------------+
...
| 26 | 0.00054350 | SELECT * FROM users WHERE city = 'City500' |
+----------+------------+----------------------------------------------------------+
15 rows in set, 1 warning (0.00 sec)
City500 だけでなく、他の値も検索してみます。
City0,City999 のどちらの検索も 0.001 秒 とかなり高速ですね。
他の値でも問題なく高速に検索が行えることがわかりました。
mysql> SELECT * FROM users WHERE city = 'City0';
+-----+---------------------+---------+-------+
| id | email | country | city |
+-----+---------------------+---------+-------+
| 1 | user1@example.com | Japan | City0 |
| 2 | user2@example.com | Japan | City0 |
...
| 99 | user99@example.com | Japan | City0 |
| 100 | user100@example.com | Japan | City0 |
+-----+---------------------+---------+-------+
100 rows in set (0.00 sec)
mysql> SELECT * FROM users WHERE city = 'City999';
+--------+------------------------+-------------+---------+
| id | email | country | city |
+--------+------------------------+-------------+---------+
| 99901 | user99901@example.com | Country901 | City999 |
| 99902 | user99902@example.com | Country902 | City999 |
...
| 99999 | user99999@example.com | Country999 | City999 |
| 100000 | user100000@example.com | Country1000 | City999 |
+--------+------------------------+-------------+---------+
100 rows in set (0.00 sec)
mysql> show profiles;
+----------+------------+----------------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+----------------------------------------------------------+
...
| 27 | 0.00127300 | SELECT * FROM users WHERE city = 'City0' |
| 28 | 0.00130400 | SELECT * FROM users WHERE city = 'City999' |
+----------+------------+----------------------------------------------------------+
15 rows in set, 1 warning (0.00 sec)
まとめ
今回実際にやってみて、インデックスを作成しても検索が速くならないこともあり(country の Japan カラムの検索)、またカーディナリティの高さは重要だが、その中身のデータ分布が平均的に分散していないと検索速度は上がらないといったことがわかりました。
| カラム | 種類数 (カーディナリティ) |
分布 | インデックス | 検索例 | ヒット行数 | 実行時間(秒) | 評価 |
|---|---|---|---|---|---|---|---|
| 100,000 | 全てユニーク | なし | 特定のemail | 1行 | 0.0367 | ||
| 100,000 | 全てユニーク | あり | 特定のemail | 1行 | 0.0003 | ○ | |
| country | 1,001 | 99 % が Japan (極端に偏りがある) |
なし | Japan |
99,000行 | 0.0696 | |
| country | 1,001 | 99 % が Japan (極端に偏りがある) |
あり | Japan |
99,000行 | 0.2478 | × |
| country | 1,001 | 99 % が Japan (極端に偏りがある) |
なし | Country500 |
1行 | 0.0325 | |
| country | 1,001 | 99 % が Japan (極端に偏りがある) |
あり | Country500 |
1行 | 0.0003 | ○ |
| city | 1,000 | 各都市100行ずつ（完全に均等） | なし | City500 |
100行 | 0.0364 | |
| city | 1,000 | 各都市100行ずつ（完全に均等） | あり | City500 |
100行 | 0.0005 | ○ |
インデックスを作った方がいい場合
- カーディナリティが高い
例: メールアドレス, ユーザーID
理由: ほとんどの値がユニークのため効果大 - 検索結果が絞り込める
目安: 検索した際の読み込みデータが全体の5-10%程度
読むデータが少ないため検索が速い - データが均等に分散している
例: 今回の検証の city カラムのような均等な分布
どの値を検索しても安定して速くなる
インデックスを作らない方がいい場合
- カーディナリティが低い（=検索結果が絞り込めない）
種類数が少ないと、絞り込みができず結果的に多くの範囲を見る必要があるため効率が悪い - データが極端に偏っている
例: 今回の検証の country カラムのJapanのような値
インデックスを使っても広範囲の検索が求められるため、結果的にフルスキャンの方が早くなることがある
今回検証してみて、インデックスの効果を決めるのはカーディナリティ(種類数)ももちろんですが、データの分布なども重要なことがわかりました。
よく書籍や他の方のブログで述べられているような「カーディナリティが低い列にはインデックスは作らないこと」「値は均等に分散されるようにデータベースを設計しよう」などの意図が少し理解できてよかったです。
今回の内容はデータベースの世界では当たり前な話かと思いますが、初心者の方がカーディナリティという言葉を理解するときにこのブログの内容が少しでも役に立ってくれたら幸いです。お疲れ様でした〜
参考情報
