この記事は トラストバンクAdventCalendar2024 3日目の記事になります。
担当は、ふるさとチョイスバックエンド、文系担当のシンドウです。
日々様々な業務を対応している中で、「気になるけど時間が取れない！」と思って確認できていなかったことの一つに、「単一列インデックスと複合インデックスの速度差分」があったので、今回を機に確認してみたいと思います！
発端
弊社の運営するふるさと納税ポータル ふるさとチョイス には、検索に使用できる各種様々なお礼の品のカテゴリーがあり、検索画面の左メニューには、それぞれのカテゴリーに登録されているお礼の品数が表示されています。
ただこの「紐づく品数の集計」が、年々増加するお礼の品数に比例して重くなっており、改善策の検討を行っておりました。
重くなっている箇所は以下、件数取得のクエリで、
SELECT COUNT(1) FROM product_detail_categories WHERE category_id = 2;
product_detail_categoriesテーブルのCREATE文は以下の通りです。
-- お礼の品とカテゴリーの紐付けテーブル
Create Table: CREATE TABLE `product_detail_categories` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'お礼の品カテゴリーID',
`category_id` int(11) NOT NULL COMMENT 'カテゴリーID',
`product_detail_id` int(11) NOT NULL COMMENT 'お礼の品ID',
`main_flag` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'メインフラグ',
`created` datetime NOT NULL COMMENT '登録日時',
`modified` datetime NOT NULL COMMENT '更新日時',
PRIMARY KEY (`id`),
KEY `idx_category_id_product_detail_id` (`category_id`,`product_detail_id`),
KEY `idx_product_detail_id` (`product_detail_id`),
KEY `idx_modified` (`modified`)
)
カテゴリを単一インデックスにすると早くなると思う、多分。
この件について、メンバーから出た改善案は、category_idの単一列インデックスの追加でした。
対象テーブルにはcategory_id * product_detail_idの複合インデックスはあるものの、category_id単一でのインデックスがありません。
後述＞テーブル2に掲示しますが、件数取得のクエリにおいて、EXPLAINのrowsが結果件数よりも多く出ていたため、複合インデックスであるが故に、若干非効率なフィルタリングになっているのでは、という予想からの提案でした。
しかし、文系の私はやや疑問を感じます。
インデックスはよく本の索引に例えられますが、たとえばcategory_idの単一列インデックスがこんな感じで、
category_id id
1 3, 5, 129, 50473, 307581, ...
...
すでにテーブルに設定済みの複合インデックス「idx_category_id_product_detail_id」がこんな感じだとしたら、
category_id-product_detail_id id
1-1 3
1-2 5
1-54 129
1-30002 50473
1-64567 307581
...
複合インデックスとて、「category_id-product_detail_id」が対象のカテゴリIDから始まるところのidを全部ガポッと取れば、別にcategory_idの単一列インデックスと大体同じ速さで対象件数の取得いけそうじゃないですか？
やってみよう
ということで実際に確認してみましょう。
本当は「今回いい機会だし、MySQLで使用されているインデックスのデータ構造である【B木】（正確にはB+木）も理解して、論理的に速くなるかどうか予測したい」と思いましたが、「b tree わかりやすく」と検索してでてきた記事や動画を、歯を食いしばって5本くらい真面目に見ましたが、今回の疑問の解消に活用できるレベルの知識の獲得には至りませんでした。故の実地調査です。
方法
product_detail_categoriesテーブルを複製し、現行の「idx_category_id_product_detail_id」インデックスの設定を、それぞれ以下のインデックス設定に変更した3テーブルを用意し、問題のクエリのEXPLAINと実行速度を確認します。
-
category_idの単一列インデックス（idx_category_id） -
category_id * product_detail_idの複合インデックス（idx_category_id_product_detail_id）【オリジナルそのまま】 -
product_detail_id * category_idの複合インデックス（idx_product_detail_id_category_id）
全レコード数は96205638行、MySQLは諸事情によりバージョン5.6で確認します。
結果
テーブル1：category_idの単一インデックス
mysql> EXPLAIN SELECT COUNT(1) FROM product_detail_categories WHERE category_id = 2;
+----+-------------+---------------------------+------+-----------------+-----------------+---------+-------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------------+------+-----------------+-----------------+---------+-------+---------+-------------+
| 1 | SIMPLE | product_detail_categories | ref | idx_category_id | idx_category_id | 4 | const | 4515822 | Using index |
+----+-------------+---------------------------+------+-----------------+-----------------+---------+-------+---------+-------------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(1) FROM product_detail_categories WHERE category_id = 2;
+----------+
| count(1) |
+----------+
| 2491821 |
+----------+
1 row in set (0.49 sec)
テーブル2：category_id * product_detail_idの複合インデックス【オリジナル】
mysql> EXPLAIN SELECT COUNT(1) FROM product_detail_categories WHERE category_id = 2;
+----+-------------+---------------------------+------+-----------------------------------+-----------------------------------+---------+-------+---------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------------+------+-----------------------------------+-----------------------------------+---------+-------+---------+-------------+
| 1 | SIMPLE | product_detail_categories | ref | idx_category_id_product_detail_id | idx_category_id_product_detail_id | 4 | const | 4743408 | Using index |
+----+-------------+---------------------------+------+-----------------------------------+-----------------------------------+---------+-------+---------+-------------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(1) FROM product_detail_categories WHERE category_id = 2;
+----------+
| count(1) |
+----------+
| 2491821 |
+----------+
1 row in set (2.82 sec)
テーブル3：product_detail_id * category_idの複合インデックス
mysql> EXPLAIN SELECT COUNT(1) FROM product_detail_categories WHERE category_id = 2;
+----+-------------+---------------------------+-------+---------------+-----------------------------------+---------+------+----------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------------------+-------+---------------+-----------------------------------+---------+------+----------+--------------------------+
| 1 | SIMPLE | product_detail_categories | index | NULL | idx_product_detail_id_category_id | 8 | NULL | 94199610 | Using where; Using index |
+----+-------------+---------------------------+-------+---------------+-----------------------------------+---------+------+----------+--------------------------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(1) FROM product_detail_categories WHERE category_id = 2;
+----------+
| count(1) |
+----------+
| 2491821 |
+----------+
1 row in set (17.01 sec)
mysql> SELECT COUNT(1) FROM product_detail_categories; -- 参考：同テーブルでの全件数取得
+----------+
| count(1) |
+----------+
| 96205638 |
+----------+
1 row in set (16.22 sec)
まとめ
ある1カラムでの絞り込み検索において、単一列インデックスと、インデックスカラムの1つ目に検索条件がある複合インデックスでのselectの速度を比較した場合、単一列インデックスの方がやっぱりそこそこ速い
結局B+木の論理がわからなかったので詳細な理由は不明ですが、メンバーの予想通り、単一列インデックスに変更すると、カテゴリーIDを指定しての件数取得の速度が改善しました。
単一列インデックスの場合も、EXPLAINのrowsは結果件数と一致しない
もともと「複合インデックスだと、EXPLAINのrowsが結果件数と一致していない、初手でちゃんと絞り込みができていないのでは」という予想から単一列インデックスの提案があった次第でしたが、ここについては単一列インデックスの場合も、EXPLAINのrowsはやや減ったものの、状況として変化はありませんでした。
【おまけ】検索条件が複合インデックスの2つ目のカラムに存在していても、その複合インデックスはインデックスとして機能しない
検索条件カラムが複合インデックスの2つ目に設定されている場合、そのインデックスは「意味がなく、全件検索になる」と書いてある記事と、「効果が薄い」と書いてある記事があったので、今回ついでに確認してみました。
EXPLAINのpossible_keysがNULLであることや、クエリの速度が全件数取得と同等であること、つまり「意味がない=インデックスにならない」ということが体感としても確認できました。
感想
結局お礼の品カテゴリについては、もっとドラスティックな構造変更を行ったため、インデックスの変更には至らなかったのですが、今回を機に単一列・複合インデックスの効果差分を体感できてよかったです。
が、やはり「なぜ速くなるのか」「なぜ単一列インデックスの場合でもrowsは結果件数と差があるのか」あたりが気になるので、引き続き機を見て勉強しつつ、いつかMySQLあるいはB木の神様がふらっとやってきて全ての知識を授けてくれたら、私の日本語力の全てを尽くして説明する記事を書きます。
トラストバンクでは様々な職種のメンバーを募集中です。
気になる方や、あるいはB木の神様がいらしたら、お気軽にお問い合わせください。
https://www.wantedly.com/companies/trustbank/projects