MySQLのインデックスがうまく効かない

いつもありがとうございます。
MySQLのインデックスについて、どうしてもうまくいかないことがありご質問させていただきます。

下記のテーブル定義のように(product_id, version, received, id DESC)とインデックスを作成し、
クエリのwhere句に(product_id, version, received)を指定し、order by句に(id DESC)を指定しています。

ですが、Explainの結果のExtraが「Using index condition; Using filesort」となってしまいます。
「Using index」のみとなる想定だったのですが・・・。

クエリ、もしくはインデックスの書き方におかしいところなどありますでしょうか。

###テーブル

CREATE TABLE `measurements` (
  `id` INT ZEROFILL NOT NULL AUTO_INCREMENT,
  `product_id` INT ZEROFILL NOT NULL,
  `version` VARCHAR(16) NOT NULL,
  `key` VARCHAR(64) NOT NULL,
  `value` VARCHAR(128) NOT NULL,
  `datetime` DATETIME NOT NULL,
  `received` TIMESTAMP(3) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `idx01` (`product_id` ASC, `version` ASC, `received` ASC, `id` DESC),
  INDEX `idx02` (`received` ASC),
  INDEX `idx03` (`datetime` ASC))
ENGINE = InnoDB ;

###クエリ(explain)

MariaDB > EXPLAIN SELECT 
    ->   *
    -> FROM 
    ->   measurements
    -> WHERE 
    ->     `product_id` = 569 
    ->     AND
    ->     `version` = '2.00' 
    ->     AND 
    ->     `received` > '2017-02-06 19:27:40'
    -> ORDER BY 
    ->   `id` DESC ;
+------+-------------+--------------+-------+---------------+-------+---------+------+------+---------------------------------------+
| id   | select_type | table        | type  | possible_keys | key   | key_len | ref  | rows | Extra                                 |
+------+-------------+--------------+-------+---------------+-------+---------+------+------+---------------------------------------+
|    1 | SIMPLE      | measurements | range | idx01,idx02   | idx01 | 60      | NULL | 2675 | Using index condition; Using filesort |
+------+-------------+--------------+-------+---------------+-------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)

MySQL(MariaDB)のバージョン

MariaDB > SELECT version(); 
+-----------------------+
| version()             |
+-----------------------+
| 10.0.28-MariaDB-wsrep |
+-----------------------+
1 row in set (0.00 sec)

行動規範の内容に同意します

回答2件

ベストアンサー

ご提示のクエリの場合、SELECT句に指定した全てのカラム（すなわち、measurementsテーブルの全てのカラム）をカバーするインデックスが同テーブルに存在しないため、'Using index'は使用できません。

なぜなら、'Using index'は（カバリングインデックスを使用している場合など）インデックスの情報のみを使用してデータを取得できる場合に使用できる方法だからです。
https://mariadb.com/kb/en/mariadb/explain/

Using index

Only the index is used to retrieve the needed information from the table. There is no need to perform an extra seek to retrieve the actual record.

試しに、クエリを以下のように修正して実行してみてください。

sql
1EXPLAIN SELECT 
2#   *
3  `product_id`,
4  `version`,
5  `received`,
6  `id`
7FROM 
8  measurements
9WHERE 
10    `product_id` = 569 
11    AND
12    `version` = '2.00' 
13    AND 
14    `received` > '2017-02-06 19:27:40'
15ORDER BY 
16  `id` DESC ;

'Using index'が表れるかと思います。
（'Using filesort'など、他の情報も引き続き表示されると思います）

私の環境(MySQL5.7)では、以下の結果になりました。

sql
1mysql> EXPLAIN SELECT 
2    -> #   *
3    ->   `product_id`,
4    ->   `version`,
5    ->   `received`,
6    ->   `id`
7    -> FROM 
8    ->   measurements
9    -> WHERE 
10    ->     `product_id` = 569 
11    ->     AND
12    ->     `version` = '2.00' 
13    ->     AND 
14    ->     `received` > '2017-02-06 19:27:40'
15    -> ORDER BY 
16    ->   `id` DESC ;
17+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------------+
18| id | select_type | table        | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                                    |
19+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------------+
20|  1 | SIMPLE      | measurements | NULL       | range | idx01,idx02   | idx01 | 44      | NULL |    5 |   100.00 | Using where; Using index; Using filesort |
21+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------------+
221 row in set, 1 warning (0.00 sec)

ただし、だからと言って
「全てのカラムをカバーするインデックスを作成すれば良い」
というわけではありません。

以下のページの

全てのカラムにインデックスをつける

という項に説明されているようなデメリットがあるからです。
http://nippondanji.blogspot.jp/2009/04/mysql10.html

次に、'Using filesort'が表示される理由は、ORDER BY 句を実行する際にインデックスが使用できないことにあります。

ご提示のクエリは、以下のページの

次のクエリーではインデックスを使用して ORDER BY 部分を解決します。

という表の、いずれのパターンにも該当しません。
（MariaDB の情報が見当たらなかったため、MySQL からの引用です）
https://dev.mysql.com/doc/refman/5.6/ja/order-by-optimization.html

試しに、以下のようにクエリを修正して実行してみてください。

①

sql
1EXPLAIN SELECT 
2  *
3FROM 
4  measurements
5WHERE 
6    `product_id` = 569 
7    AND
8    `version` = '2.00' 
9    AND 
10#     `received` > '2017-02-06 19:27:40'
11    `received` = '2017-02-06 19:27:40'
12ORDER BY 
13  `id` DESC ;

②

sql
1EXPLAIN SELECT 
2  *
3FROM 
4  measurements
5WHERE 
6    `product_id` = 569 
7    AND
8    `version` = '2.00' 
9    AND 
10    `received` > '2017-02-06 19:27:40'
11ORDER BY 
12#   `id` DESC ;
13  `received` ;

①は

sql
1SELECT * FROM t1
2  WHERE key_part1 = constant
3  ORDER BY key_part2;

というパターン、
②は

sql
1SELECT * FROM t1
2  WHERE key_part1 = constant1 AND key_part2 > constant2
3  ORDER BY key_part2;

というパターンに該当するため、'Using filesort'が消えるかと思います。

私の環境では、以下の結果になりました。
①

sql
1mysql> EXPLAIN SELECT 
2    ->   *
3    -> FROM 
4    ->   measurements
5    -> WHERE 
6    ->     `product_id` = 569 
7    ->     AND
8    ->     `version` = '2.00' 
9    ->     AND 
10    -> #     `received` > '2017-02-06 19:27:40'
11    ->     `received` = '2017-02-06 19:27:40'
12    -> ORDER BY 
13    ->   `id` DESC ;
14+----+-------------+--------------+------------+------+---------------+-------+---------+-------------------+------+----------+-------------+
15| id | select_type | table        | partitions | type | possible_keys | key   | key_len | ref               | rows | filtered | Extra       |
16+----+-------------+--------------+------------+------+---------------+-------+---------+-------------------+------+----------+-------------+
17|  1 | SIMPLE      | measurements | NULL       | ref  | idx01,idx02   | idx01 | 44      | const,const,const |    1 |   100.00 | Using where |
18+----+-------------+--------------+------------+------+---------------+-------+---------+-------------------+------+----------+-------------+
191 row in set, 1 warning (0.00 sec)

②

sql
1mysql> EXPLAIN SELECT 
2    ->   *
3    -> FROM 
4    ->   measurements
5    -> WHERE 
6    ->     `product_id` = 569 
7    ->     AND
8    ->     `version` = '2.00' 
9    ->     AND 
10    ->     `received` > '2017-02-06 19:27:40'
11    -> ORDER BY 
12    -> #   `id` DESC ;
13    ->   `received` ;
14+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
15| id | select_type | table        | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                 |
16+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
17|  1 | SIMPLE      | measurements | NULL       | range | idx01,idx02   | idx01 | 44      | NULL |    5 |   100.00 | Using index condition |
18+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+-----------------------+
191 row in set, 1 warning (0.00 sec)

投稿2017/02/08 13:25

編集2017/02/08 13:34

KiyoshiMotoki

総合スコア4791

mosa

2017/02/09 02:45

詳細にご回答ありがとうございます。ご回答頂いていた内容をこちらでも試していたので遅くなりました。インデックスに対する知識・認識が甘く、②で Using index となると思っていました。「インデックスの情報のみを使用してデータを取得できる場合に使用できる方法」である、ということを理解していませんでした。（単純に対象レコードを探すという意味で検索そのものをインデックスだけで行えれば Using index となると思っていました）その際またご質問させていただくことがあるかもしれませんがよろしくお願いします。