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

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

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

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

データベース

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

データベース設計

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

MariaDB

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

Q&A

解決済

5回答

6677閲覧

MySQLには降順のインデックスがない?

mosa

総合スコア218

MySQL

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

データベース

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

データベース設計

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

MariaDB

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

0グッド

0クリップ

投稿2017/03/09 03:06

編集2017/03/09 05:41

###############################################
大変申し訳ありません。タイトルと内容が一致していなかったため、意味不明な質問になっていました。
問題自体は解決しました。お騒がせしました。
###############################################


いつもお世話になっております。

MySQLのインデックスについての質問です。
毎度つまらない質問ですみません。頭がこんがらがってしまいまして。。。

a,b,c,d というカラムがあるテーブルに対して、a,b,c,d というインデックスを作成した場合、
以下のクエリを実行すると、OrderBy句に b が入っているため、Using Index Condition, Using FileSort となってしまいます。

DDL

SQL

1CREATE TABLE IF NOT EXISTS `hoge` ( 2 `id` INT NOT NULL AUTO_INCREMENT, 3 `a` INT NULL, 4 `b` INT NULL, 5 `c` INT NULL, 6 `d` INT NULL, 7 PRIMARY KEY (`id`), 8 INDEX `idx01` (`a`, `b`, `c`, `d`)) 9ENGINE = InnoDB 10;

SQL

SQL

1SELECT 2 * 3FROM 4 hoge 5WHERE 6 a = 10 AND b > 100 AND c < 1000 7ORDER BY 8 d, b 9LIMIT 10 100 11;

Index Condition として既に使用しているキーを OrderBy の第2ソートキーで再度使用することはできないのでしょうか。
つまり、上記のクエリで Using Index Condition を維持したまま Using FileSort を発生させない方法はないのでしょうか。
(sort buffer を増やす、とかはなしで)


MariaDB [cake]> select count(*) from hoge; +----------+ | count(*) | +----------+ | 1048576 | +----------+ 1 row in set (0.45 sec) MariaDB [cake]> select version(); +-----------------------+ | version() | +-----------------------+ | 10.0.29-MariaDB-wsrep | +-----------------------+ 1 row in set (0.00 sec)

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

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

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

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

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

退会済みユーザー

退会済みユーザー

2017/03/09 03:21

「a,b,c,d というカラムがあるテーブルに対して、a,b,c,d というインデックスを作成した」とは、aのインデックス、bのインデックス、cのインデックス、dのインデックスを作成したのではなく、a&b&c&dのインデックスを一つ作った、という意味でしょうか?
退会済みユーザー

退会済みユーザー

2017/03/09 03:30

mysqlなのかmariadbなのか、そしてそのバージョンは。
mosa

2017/03/09 03:56

a&b&c&dのインデックスを一つ作った、という意味です。
mosa

2017/03/09 03:56

10.0.28-MariaDB ですが、MySQLにおける一般的な挙動について質問しました。
guest

回答5

0

(sort buffer を増やす、とかはなしで)

ということなので、ソートそのものをなくす方法についてお応えします。

order by使用時に索引でソートを省略するには、order byと索引でカラムの指定順序を合わせる必要があります。

今回のSQLであれば、以下のどちらかの索引を追加してみてください

d,b,a,c
a,d,b,c

投稿2017/03/09 04:25

編集2017/03/09 07:43
SVC34

総合スコア1149

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

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

mosa

2017/03/09 04:33

ホントですか!?試してみます! 結果をまた記載します。
mosa

2017/03/09 05:11

できました!ありがとうございます! 今ちょっと色々考えていて混乱していると思うので、後日また色々調べてみます。 ありがとうございました。
SVC34

2017/03/09 07:31

逆から考えてみましょう。 order byがあっても並べ替えを行わなくてよいのはどのような場合か? →結果があらかじめ希望した順序に並んでいることが保障されていればよい →索引を使用したアクセスであれば、索引のエントリーがその順序に並んでいればよい →索引のエントリーは指定したカラムをキーとした順序で登録されている(Bツリーアルゴリズムを使用しているため、順序は保障される) 今回2つの索引を追加する候補として挙げました。 ①d,b,a,c ②a,d,b,c ①は当然d,bの順序で索引のエントリーが並んでいます。②はaが先に指定されているので索引全体としてはd,bの順序にはなりませんが、今回のSQLではWHERE句でaを一意に指定しているため、その中(aの同順位内)でのd,bの順序は保障されます(従って、a < 100 のような条件に変えると②ではソートを回避できません)。
mosa

2017/03/09 10:01

わかりやすく説明いただいてありがとうございます。こうして理路整然と書かれていると納得できます。 BTreeインデックスについての知識が甘いせいか、Where句とOrderBy句が複合すると毎回混乱してしまいます。 ちょうど今回の件で頂いたURLのサイトを見ていました。
mosa

2017/03/09 10:01

本文を修正しましたが、タイトルの内容が一致していませんでした。 ご迷惑をおかけしました。
guest

0

ベストアンサー

MySQL(少なくとも5.6時点)では、原則として1テーブルに対して1つのインデックスしか使えません。index_mergeという仕組みもありますが、これもWHEREに対して複数のインデックスが必要な場合しか効かないようです(参考)。

ということで、WHEREでd列を含まないインデックスを適用してしまっているので、ソートにはインデックスが使えなくなっています。

投稿2017/03/09 03:25

maisumakun

総合スコア145121

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

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

mosa

2017/03/09 03:59

ありがとうございます。そういうことですよね。 何か使えるような気になってしまって混乱してしまいました。
guest

0

using FileSort は、ORDER BY による並び替えをする際に、オンメモリでは処理しきれないほどデータ量が多い場合にも発生します。

そもそも当該の SELECT の結果セット、何行ぐらいを想定しているのですか?

投稿2017/03/09 03:43

tacsheaven

総合スコア13703

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

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

mosa

2017/03/09 03:59

具体的なものではなく、一般的なものとして質問しました。 全件数を数百万、数千万など、Where句での絞込後は数十万レコードなどをイメージしています。 つまり、Where句でもOrderBy句でもインデックスを使用して意味があるという程度のイメージです。
tacsheaven

2017/03/09 04:07

MySQL は降順格納インデックスを実装していません(すべて昇順格納。降順格納は将来機能として予約されている)ので、ソートを発生「させない」ためには、order by の項目がインデックス上にあり(しかも左端から連続している)、さらに昇順と降順を混在させない(order by の全項目に desc 指定がある場合はインデックスを逆スキャンする)、といった条件を満たす必要があります。 ちなみにインデックスの絞り込み、意味があるのは全体件数と対象件数の他に、テーブルサイズも影響します。結果セットに含める項目がインデックス構成項目のみであればいいのですが、そうでない場合(たいていは当てはまる)は、インデックスをみてレコードを特定し、次にそのレコードを読んで必要な項目を取り出す必要があります。フルスキャンであれば一括で読めるレコード群も、インデックスを使うことで読み取りが数万回に膨れ上がると、下手するとフルスキャンの方が早い場合もあります。
guest

0

LIMITの問題では?
試しにはずしてEXPLAINしてみてください

投稿2017/03/09 04:04

yambejp

総合スコア114583

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

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

mosa

2017/03/09 04:32

試してみます。お待ち下さい。
mosa

2017/03/09 05:01

結果は変わりませんでした。
yambejp

2017/03/09 05:41

そうなるとデータが多いのでファイルに一時保管が必要と判断されたのでしょう
mosa

2017/03/09 05:48

ご回答ありがとうございます。 本文を修正しましたが、タイトルの内容が一致していませんでした。 ご迷惑をおかけしました。
guest

0

「order by d,b」したいなら、dとbを伴ったインデックスを作ればいいのでは?
実行するクエリーに最適なインデックスを用意するべきだと思いました。

なお、降順にしたい場合は、「order by d,b desc」などとdescを補います。
これはdについてはデフォルトの昇順だけどbについては降順を指定するの意味です。

投稿2017/03/09 03:24

編集2017/03/09 03:26
退会済みユーザー

退会済みユーザー

総合スコア0

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

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

mosa

2017/03/09 04:05

d, b のインデックスのみとしてしまうと、Where句での絞込にインデックスが利用できなくなってしまうので、Where句、OrderBy句で共に使えるインデックスはないものか・・・、という質問でした。 わかりづらくてすみません。 order by d,b desc としてしまうと OrderBy句 にもインデックスが利用できなくなってしまうかと思います。 https://dev.mysql.com/doc/refman/5.6/ja/order-by-optimization.html
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

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

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

質問する

関連した質問