MySQLでのdesc(降順)で貼るインデックス

公開日:  最終更新日:2014/09/25

データベースを高速化するものとして、インデックスを貼るという方法がある。
いわゆる索引ですね。
張り方にもいろいろあるのですが、今回はその中でも逆キーインデックス降順について、
はめられたので、忘れないように備忘録。

20140925 ご指摘をうけ修正しました。MySQLに逆キーインデックスはないです!!お恥ずかしいかぎりです。ご指摘ありがとうございます!

詳細は↓へ。

sponcer link

・前置き ~降順インデックスって?~

そもそも降順インデックスとは?
ってのがあるとおもいますが、通常インデックスはソートされた状態でならんでいます。
例えば以下のようなデータがあった場合。

1, 3, 2, 6, 1, 2, 4, 2, 5

数字もバラバラでアクセスしづらいですね。
そこで昇順でインデックスを用意しておくと、データを整理整頓された状態になります。

1, 1, 2, 2, 2, 3, 4, 5, 6

データが綺麗に並んでいるので、アクセスも速くなります。
例えば値が2のものを全部ほしいといった場合、
前者の揃っていないものだと最後まで観に行かないとデータ収集できないですよね?
しかし、後者のように並んでいれば2のものは5番目以降にはないのでそこでスキャンを終えることができます。
ね?はやいでしょ?
そして本題の降順インデックス。
どういうこったというと、逆(desc)に整理整頓するってことです。
つまり、先程の例で言うと、以下のようになります。

6, 5, 4, 3, 2, 2, 2, 1, 1

こんなかんじで逆にソートするものなんですが、なにがいいのかというと、
よく効果が出やすいのが最新情報のものの取得といった場合。
数字だとよくわからないですが、日付の場合ですね。
昇順(ASC)で日付を並べると通常日付が古い順に並びます。
でも普通日付で並べるときって新しい日付順に並べることが多いですよね?
そこで降順インデックスを日付型のデータにはっておくことで、新しい順(desc)でソートされるので、
圧倒的な効果を発揮して、
爆 ☆ 速 ☆ 検 ☆ 索
ができるわけです。

・MySQLでの降順インデックスの挙動

さて、前置きが非常に長くなってしまいましたが、
そんなかんじで降順インデックスが非常に使える物と分かっていただけたかと思います。
早速これを使ってみよう!
ということで、降順インデックスを作ります。
作り方は至って簡単。
DESCをつけるだけ!
楽勝ですね。

CREATE INDEX インデックス名 ON インデックス張るテーブル名 (カラム名 DESC);

こんなかんじです。
しかし、以下のコマンドでインデックスの調子を見てみると・・・

show index from テーブル名

CollationがAのまま。。。
あれ・・・昇順?
そして調べてみると衝撃的事実が判明した!
ソースはMySQLの公式より。
MySQL 5.1 リファレンスマニュアル :: 12.1.7 CREATE INDEX 構文
以下引用

index_col_name 仕様は ASC か DESC で終わる事ができます。これらのキーワードは昇順や降順インデックス値ストレージを指定する為の将来の拡張子として許容されます。現在は、それらは解析されますが無視されます。インデックス値は毎回昇順で格納されます。

(;゚Д゚)
(゚Д゚;)
(;つД⊂)ゴシゴシ

それらは解析されますが無視されます。インデックス値は毎回昇順で格納されます。

(゚Д゚)え?
ということで一生懸命、降順(DESC)ではっても残念ながら昇順(ASC)として認識されてしまうのであった!
なんてこったい!!!

・結論 ~夢の果てに~

ということで結論として、MySQLでの降順インデックスは、
解析はされるが、無視されて降順インデックスを作成できない!
まぁでも将来追加するかもみたいなかんじで書かれてるので、
将来のために一応DESCを書いといて作成するのはいいかもしれない。

それでは良いインデックスライフを。

  • このエントリーをはてなブックマークに追加
  • Pocket
  • SQL初心者なので認識が間違っていたら申し訳ありませんが、
    上記のような場合にはインデックスは効いていると思います。
    おそらくexplainで見てもfilesort等は出ず、パフォーマンスも変わらないのではないでしょうか?
    インデックスはB+Treeという構造になっていてそれはソートされたキーの間で双方向のリストを持っています。
    なので単一のインデックスにおいて昇順・降順というのは特に意味を持たないと思います。
    インデックスの昇順・降順が問題になるのは複合インデックスの場合だと考えています。
    例えば(時刻, ID)の2つのキーで時刻の大きい順かつ(同じ時刻のものは)IDの小さい順に並べたいと考えた場合、
    時刻とIDの並びが降順かつ昇順のインデックスが欲しくなります。
    このように複数のキーで降順・昇順が異なると双方向リストで解決できる話ではなくなるため、
    降順・昇順というのが意味を持つのだと思っています。
    現状では、言われている通り降順インデックスが張れないので、
    index(date desc, id asc) としても index(date asc, id asc) の意味にしかならず、
    このような場合にインデックスを効かせるには時刻もしくはIDの逆順カラムを追加するなどの工夫が必要かと思います。
    それにしてもどうして降順インデックスに対応していないんでしょうね。
    参考
    http://dev.mysql.com/doc/refman/4.1/ja/order-by-optimisation.html

  • モグメット

    >mattennerさん
    インデックスが効かないことはないと思われます。
    MySQL自体は降順インデックス自体を実装していないだけという話なので、explainをみても結局は昇順ではられてるのでPerformanceはかわらないはずです。
    なるほど、昇順降順は単一ではあまり関係ないのですね。
    ただ、インデックス自体あまり単一ではるのは好ましくないので、出来る限り複合で貼るべきではありますね。
    降順インデックスに対応してないのは中の人の大人の都合とかではないでしょうか?笑

  • kt

    本文中で「逆キーインデックス」と呼んでいるものは「降順(逆順)インデックス」のことのように思われます。紛らわしいですが、「逆キーインデックス」はキーのバイト順を逆にしたものを指すはずです。

    • mogmet

      ご指摘ありがとうございます!
      誤った情報を載せてしまい申し訳ございません!
      こちら修正させていただきました。

PAGE TOP ↑