引越しました!

http://blog.mogmet.com/blog-entry-49.html

へアクセスしてください。

スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

MySQLでの逆キーインデックスの挙動 ~desc(降順)で貼るインデックス~

データベースを高速化するものとして、インデックスを貼るという方法がある。

いわゆる索引ですね。


張り方にもいろいろあるのですが、今回はその中でも逆キーインデックスについて、

はめられたので、忘れないように備忘録。




詳細は↓へ。




























・前置き ~逆キーインデックスって?~



そもそも逆キーインデックスとは?

ってのがあるとおもいますが、通常インデックスはソートされた状態でならんでいます。

例えば以下のようなデータがあった場合。

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を書いといて作成するのはいいかもしれない。










※P.S
ちなみにOracleなら普通に逆キーインデックスは使えます。

作戦ガンガンいこうぜなみに日付にはDESCをつけて逆キーインデックスを作成しちゃいましょう!


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

テーマ : サーバ
ジャンル : コンピュータ

コメントの投稿

非公開コメント

逆インデックスについて

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

Re: 逆インデックスについて

>mattennerさん
インデックスが効かないことはないと思われます。
MySQL自体は降順インデックス自体を実装していないだけという話なので、explainをみても結局は昇順ではられてるのでPerformanceはかわらないはずです。

なるほど、昇順降順は単一ではあまり関係ないのですね。
ただ、インデックス自体あまり単一ではるのは好ましくないので、出来る限り複合で貼るべきではありますね。

降順インデックスに対応してないのは中の人の大人の都合とかではないでしょうか?笑
スポンサーリンク
FC2カウンター
最新記事
最新コメント
カテゴリ
アマゾンドリンク
検索フォーム
リンク
ブロとも申請フォーム

この人とブロともになる

アクセスランキング
[ジャンルランキング]
コンピュータ
2287位
アクセスランキングを見る>>

[サブジャンルランキング]
未設定
--位
アクセスランキングを見る>>
バックリンク
最新トラックバック
上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。