この記事はモバイルファクトリー Advent Calendar 2020 8日目の記事です。

はじめに

こんにちは、エンジニアの id:mp0liiu です。

MySQLでは基本的にクエリを実行する際インデックスは1つしか効きませんが、インデックスマージという仕組みによって複数のインデックスを使った検索結果をマージし、その和集合や共通集合を効率よく取得できる場合があります。
とはいっても具体的にどのようなケースでインデックスマージが利用されるのかわかっていなかったので、MySQLの公式ドキュメントを見つつ実際にテーブルを作って検証してみました。
本記事では検証した結果を基にインデックスマージが利用される具体的なケースをいくつか紹介します。

検証に使った環境は以下の通りです。

Ubuntu 18.04
MySQL 5.7.32

事前準備

まず検索対象のテーブルを作ります。

CREATE TABLE user_item (
    id         INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED NOT NULL,
    item_id    INT UNSIGNED NOT NULL,
    count      INT UNSIGNED NOT NULL DEFAULT 0,
    created_at DATETIME     NOT NULL,
    INDEX user_id (user_id),
    INDEX item_id (item_id),
    INDEX created_at (created_at)
);

user_id, item_id, created_at にインデックスを貼っています。

次に以下のスクリプトでデータを挿入します。
インデックスは検索対象のテーブルの行が十分に大きく、かつカーディナリティが高い列でないと利用されないです。
今回はランダムな値のレコードを1万件挿入し、カーディナリティ100程度でインデックスマージが利用されているケースを確かめられました。

use strict;
use warnings;
use utf8;
use DBI;
use Time::Moment;

my $dbh = DBI->connect(
    'dbi:mysql:database=sandbox',
    'root',
    '',
    +{
        AutoCommit          => 1,
        PrintError          => 0,
        RaiseError          => 1,
        ShowErrorStatement  => 1,
        AutoInactiveDestroy => 1
    }
) or die $DBI::errstr;

my $time = Time::Moment->from_string('2020-12-01T00:00:00Z');
for my $n (1 .. 10000) {
    $dbh->do(
        q{ INSERT INTO user_item (user_id, item_id, created_at) VALUES (?, ?, ?)},
        undef,
        (
          int( rand(100) ),
          int( rand(100) ),
          $time->plus_seconds( int( rand(100) ) )->strftime('%Y-%m-%d %H:%M:%S'),
        )
    );
}

検証

スクリプトで挿入したデータをインデックスが効きそうな条件で検索し、EXPLAIN でクエリ実行計画を見てみます。

インデックスを貼ったそれぞれのカラムをANDで繋げて条件指定する

mysql> EXPLAIN SELECT * FROM user_item WHERE item_id = 1 AND user_id = 51 AND created_at = '2020-12-01 00:00:25'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_item
   partitions: NULL
         type: index_merge
possible_keys: user_id,item_id,created_at
          key: user_id,item_id
      key_len: 4,4
          ref: NULL
         rows: 1
     filtered: 5.00
        Extra: Using intersect(user_id,item_id); Using where
1 row in set, 1 warning (0.00 sec)

type列をみていると、 index_merge となっており、インデックスマージが効いていることがわかります。
key列からは user_id, item_id のインデックスが利用されたことがわかります。
Extra列は Using intersect(user_id,item_id) となっており、公式ドキュメントに書いてあるインデックスマージ共通集合アクセスアルゴリズムでインデックスマージが行われることがわかります。
つまり user_id で絞り込んだ結果と item_id で絞り込んだ結果の共通集合が返ってくる、ということでしょう。

created_at のインデックスも利用可能になっていますが、rows が1になっていることを考えると恐らく user_id, item_id だけで十分結果を絞りこめるので利用されていないということでしょう。
試しにデータ量を増やしてみるとすべてのキーが使われる場合もありました。
条件の値によっても使われるキーが変化していて、より結果を絞り込みやすいインデックスから優先的に利用されていました。

動き的に複合インデックスを貼った場合と似ていますが、複合インデックスの場合は検索に利用するカラムの順番が決まっているのに対して、インデックスマージは効率に応じて利用されるインデックスが変化する点が違っていそうです。

インデックスを貼ったそれぞれのカラムをORで繋げて条件指定する

mysql> EXPLAIN SELECT * FROM user_item WHERE item_id = 1 OR user_id = 10 OR created_at = '2020-12-01 00:00:01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_item
   partitions: NULL
         type: index_merge
possible_keys: user_id,item_id,created_at
          key: item_id,user_id,created_at
      key_len: 4,4,5
          ref: NULL
         rows: 259
     filtered: 100.00
        Extra: Using union(item_id,user_id,created_at); Using where
1 row in set, 1 warning (0.00 sec)

これもtype列をみていると、 index_merge となっており、インデックスマージが効いていることがわかります。
key列からは user_id, item_id, created_at のインデックスが利用されたことがわかります。
Extra列は Using union(item_id,user_id,created_at) となっており、公式ドキュメントに書いてあるインデックスマージ和集合アクセスアルゴリズムでインデックスマージが行われることがわかります。
つまり user_id, item_id, created_at の各インデックスで絞り込んだ結果の和集合が返ってくる、ということでしょう。

インデックスを貼ったカラムをORで繋げて範囲条件を指定する

mysql> EXPLAIN SELECT * FROM user_item WHERE item_id IN (1, 3, 5) OR created_at < '2020-12-01 00:00:05'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_item
   partitions: NULL
         type: index_merge
possible_keys: item_id,created_at
          key: item_id,created_at
      key_len: 4,5
          ref: NULL
         rows: 779
     filtered: 100.00
        Extra: Using sort_union(item_id,created_at); Using where
1 row in set, 1 warning (0.00 sec)

こちらは Extra列が Using sort_union(item_id,user_id) となっており、公式ドキュメントに書いてあるインデックスマージソート和集合アクセスアルゴリズムでインデックスマージが行われることがわかります。
インデックスマージソート和集合アクセスアルゴリズムがどのような場合に使われるのかよくわからなくてこの状況を作り出すのが難しかったのですが、このケースのように範囲条件で絞り込まれる結果が比較的少ない場合（item_id は 1, 3, 5 のいずれか、 created_at は 2020-12-01 00:00:00' 〜 2020-12-01 00:00:05' のいずれか）はインデックスマージが行われるようでした。

インデックスを貼った片方のカラムを条件指定し、もう片方のカラムでソートする

ORDER BY でもインデックスマージが効くのか気になったので調べてみました。

mysql> EXPLAIN SELECT * FROM user_item WHERE user_id = 20 ORDER BY created_at\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user_item
   partitions: NULL
         type: ref
possible_keys: user_id
          key: user_id
      key_len: 4
          ref: const
         rows: 89
     filtered: 100.00
        Extra: Using index condition; Using filesort
1 row in set, 1 warning (0.00 sec)

インデックスマージは効かず、通常通り1つのインデックスだけが利用されています。
このような場合両方の列に対してインデックスを効かせるには複合インデックスを貼るしかなさそうです。

おわりに

実際に検証してみて具体的にどのような場合にインデックスマージが行われるのかがかなり理解できました。
まとめると別々のインデックスが効く複数の問い合わせ結果を集合演算したものが得られるような仕組みだと言えるかなと思いました。

明日は id:charines さんです。

Mobile Factory Tech Blog

技術好きな方へ！モバイルファクトリーのエンジニアたちが楽しい技術話をお届けします！

どのようなケースでインデックスマージが利用されるのか検証する