質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.46%
MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

データベース

データベースとは、データの集合体を指します。また、そのデータの集合体の共用を可能にするシステムの意味を含めます

データベース設計

データベース設計はデータベースの論理的や物理的な部分を特定する工程です。

MariaDB

MariaDBは、MySQL派生のオープンソースなリレーショナルデータベースシステムです。 また、MySQLとほぼ同じデータベースエンジンに対応しています。

Q&A

解決済

2回答

22177閲覧

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

mosa

総合スコア218

MySQL

MySQL(マイエスキューエル)は、TCX DataKonsultAB社などが開発するRDBMS(リレーショナルデータベースの管理システム)です。世界で最も人気の高いシステムで、オープンソースで開発されています。MySQLデータベースサーバは、高速性と信頼性があり、Linux、UNIX、Windowsなどの複数のプラットフォームで動作することができます。

データベース

データベースとは、データの集合体を指します。また、そのデータの集合体の共用を可能にするシステムの意味を含めます

データベース設計

データベース設計はデータベースの論理的や物理的な部分を特定する工程です。

MariaDB

MariaDBは、MySQL派生のオープンソースなリレーショナルデータベースシステムです。 また、MySQLとほぼ同じデータベースエンジンに対応しています。

1グッド

0クリップ

投稿2017/02/08 10:45

いつもありがとうございます。
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)
KiyoshiMotoki👍を押しています

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

ベストアンサー

ご提示のクエリの場合、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)

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

以下のページの

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

という項に説明されているようなデメリットがあるからです。
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 となると思っていました) その際またご質問させていただくことがあるかもしれませんがよろしくお願いします。
guest

0

今回の場合、行数2675行の全体に対してWHERELIMITで絞らずにソートを掛けていますが、そのような場合にはインデックスからデータを抜き出したところで全行処理しなければならないことには変わりないので、全体にfilesortをかけてもそこまで負荷は変わらないです。

LIMIT 100のように一部だけ抜き出すとなれば、filesortとはならないと思います。

投稿2017/02/08 11:09

maisumakun

総合スコア145208

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

mosa

2017/02/09 02:39

インデックスについての認識が甘かったようです。 ご回答ありがとうございます。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.46%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問