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

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

ただいまの
回答率

90.52%

  • MySQL

    5854questions

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

  • データベース

    700questions

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

  • MariaDB

    294questions

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

  • データベース設計

    145questions

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

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

解決済

回答 2

投稿

  • 評価
  • クリップ 0
  • VIEW 4,946

mosa

score 200

いつもありがとうございます。
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)
  • 気になる質問をクリップする

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+4

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

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

EXPLAIN SELECT 
#   *
  `product_id`,
  `version`,
  `received`,
  `id`
FROM 
  measurements
WHERE 
    `product_id` = 569 
    AND
    `version` = '2.00' 
    AND 
    `received` > '2017-02-06 19:27:40'
ORDER BY 
  `id` DESC ;


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

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

mysql> EXPLAIN SELECT 
    -> #   *
    ->   `product_id`,
    ->   `version`,
    ->   `received`,
    ->   `id`
    -> 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        | partitions | type  | possible_keys | key   | key_len | ref  | rows | filtered | Extra                                    |
+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------------+
|  1 | SIMPLE      | measurements | NULL       | range | idx01,idx02   | idx01 | 44      | NULL |    5 |   100.00 | Using where; Using index; Using filesort |
+----+-------------+--------------+------------+-------+---------------+-------+---------+------+------+----------+------------------------------------------+
1 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

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

EXPLAIN SELECT 
  *
FROM 
  measurements
WHERE 
    `product_id` = 569 
    AND
    `version` = '2.00' 
    AND 
#     `received` > '2017-02-06 19:27:40'
    `received` = '2017-02-06 19:27:40'
ORDER BY 
  `id` DESC ;

EXPLAIN SELECT 
  *
FROM 
  measurements
WHERE 
    `product_id` = 569 
    AND
    `version` = '2.00' 
    AND 
    `received` > '2017-02-06 19:27:40'
ORDER BY 
#   `id` DESC ;
  `received` ;

①は

SELECT * FROM t1
  WHERE key_part1 = constant
  ORDER BY key_part2;


というパターン、
②は

SELECT * FROM t1
  WHERE key_part1 = constant1 AND key_part2 > constant2
  ORDER BY key_part2;


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

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

mysql> EXPLAIN SELECT 
    ->   *
    -> FROM 
    ->   measurements
    -> WHERE 
    ->     `product_id` = 569 
    ->     AND
    ->     `version` = '2.00' 
    ->     AND 
    -> #     `received` > '2017-02-06 19:27:40'
    ->     `received` = '2017-02-06 19:27:40'
    -> ORDER BY 
    ->   `id` DESC ;
+----+-------------+--------------+------------+------+---------------+-------+---------+-------------------+------+----------+-------------+
| id | select_type | table        | partitions | type | possible_keys | key   | key_len | ref               | rows | filtered | Extra       |
+----+-------------+--------------+------------+------+---------------+-------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | measurements | NULL       | ref  | idx01,idx02   | idx01 | 44      | const,const,const |    1 |   100.00 | Using where |
+----+-------------+--------------+------------+------+---------------+-------+---------+-------------------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/09 11:45

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

    その際またご質問させていただくことがあるかもしれませんがよろしくお願いします。

    キャンセル

  • 2017/02/09 12:07

    b

    キャンセル

0

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/02/09 11:39

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

    キャンセル

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

  • ただいまの回答率 90.52%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

  • 解決済

    MySQL トリガーについて

    MySQLトリガーについて トリガーという機能を知らなくて使ってみると便利! user1というテーブルで行った操作(INSERT/UPDATE/DELETE)を別テーブルに

  • 受付中

    ORDER BY RAND(); が遅い

     質問 タイトルの通りなのですが、 MariaDBにおいてORDER BY RAND()が非常に遅いため改善を試みましたが、 上手くいかなかった為、 「どうすれば結果取得ま

  • 受付中

    自動生成列の作成時の挙動の違い

    MariaDB 10.2.2を使用していますが10.2.3以降では、このようなGENERATED ALWAYSの列のテーブルを作成する際にエラーが出るようになってしまいました。

  • 解決済

    MySQLのテーブル作成について

    CREATE TABLE T_VendorShohin( F_ShohinName nvarchar(30) not null primary key, F_ShohinPrice

  • 解決済

    mariaDBテーブル作成エラー

    カレンダーテーブルを作成しています F_Dateフィールド⇒テーブル内で重複しない値をprimary keyにしたかったのでnot nullでprimary keyにしました F_

  • 解決済

    ユーザ定義変数に配列を代入したい

    前提・実現したいこと タイトルの通りですが、 ユーザー定義変数に配列(ベクトル)を代入したいのですが上手く出来ませんでした。 そもそも、スカラー値しか代入できないのでしょうか?

  • 解決済

    SQLにおける日時検索

    毎日毎時間10分単位で時間と風向・風量を測定しDBに記録をしていく様な、添付した画像のテーブルがあります。 そこで質問ですが、このテーブルから 「30分毎のレコード」や「

  • 解決済

    コミット前に別トランザクションでAUTO_INCREMENTした場合の挙動は?

     前提 PRIMARY KEYとAUTO_INCREMENTを付けた”ID”カラムを持つテーブルがあります。 このテーブルに対してレコードを登録し、 採番されたIDをもとにフォルダ

同じタグがついた質問を見る

  • MySQL

    5854questions

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

  • データベース

    700questions

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

  • MariaDB

    294questions

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

  • データベース設計

    145questions

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