こんにちは、新卒エンジニアの久保田です。
ここ最近gitの記事を書いている文系出身の男です。
さて、今回はすこし趣向を変えて、みんな大好き「MySQL」についてです。
毎日のように触るMySQLの、僕がこの一カ月ではまった落とし穴について触れていきます。
これから同じ道を通る人のためにも、僕が穴をふさいでおいておきたいと思います。
落とし穴その①～select文の読み込み順～
ある日の事です。僕が、すっかり使いなれた様子でASを使って以下のようなクエリーを実行しました。
すると。。。
え、Unknown column？WHEREで別名使えないの？GROUP BYは使えるのに？と軽くパニックになりました。
これは、WHEREではなく、HAVINGを使えば解決というお決まりのようなものらしいのですが、どうしてWHEREではダメなのか調べてみました。
まず、MySQLには、クエリーの読み込み順が決まっています。
僕は単純に左から読んでいると思っていましたが、そうではありませんでした。
MySQLでは、
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY
という順になっています。
あれ？これだと WHEREどころか別名はどこでも使えない？となりますよね。
調べに調べたら、書いてありました。
つまり、
「WHERE句の段階では、まだ、カラムの値が完全に参照できる状態ではない可能性があるから」
ということがあるため、制限が設けられているそうです。
おそらく、WHEREが終わった段階で、WHEREで絞り込んだテーブルをもとにGROUP BYなどを行うため、一度読みこむ必要があるから、ではないでしょうか。
なんにせよ、ASにはHAVINGですね。
落とし穴その②～integer(1)～
カラムを定義するとき、カラムに入る数字の桁数を制限するため、integer(1)のようにかっこの中に数字を入れますよね。
この数字、実は、カラムに入れられる値を制限しているわけではないのです。
つまり、このようなテーブルを作って、
このようなクエリーを実行すると、、、
このように、カラムにデータが入ってしまうんですね！
この数字は、桁数を制限しているわけでなく、zero fillオプションを使った時に足りないスペースにいくつ0で穴を埋めるか、というだけの数字なんですね。
つまり、integer(1)としても、-2147483648 ～ 2147483647の値までがはいるわけです。
知らないと無駄な領域を確保してしまうので、tinyint等を用いてデータ型で指定するよう、気をつけた方がいいかも知れませんね。
落とし穴その③～indexが効かない LIKE '%hoge%'～
便利ですよね、index。一度知ってしまったら離れることのできない蜜の味なのですが、意外な事にも落とし穴がありました。
indexでどのくらい早くなるのかを計測して遊ぶため、このような同じ状態のindexを貼ったテーブル(index_table)と貼っていないテーブル(no_index_table)を作り、
select * from item where name LIKE '%ab%'
すると、indexがある時とない時の結果が、、、
まったく変わらない結果になってしまいました。(今回はrealの数字で検証)
まさかindexというのは幻想だったのか、と思っていると隣でいつも傘をかぶって仕事しているチーフが、
「indexやるときは、%%で囲んじゃダメだよ」
と鶴の一声をささやいてくれました。
indexをつけたカラムをソートしてN個の子を持つツリー上にし、検索していくらしいのです。
こうしたツリー上に値を持つことでテーブルをフルスキャンする必要をなくし、検索のスピードを上げているんですね。
LIKE '%ab%' にしてしまうと、文字列中のどこかにabがある文字列を検索するので、ソートしても、結局テーブルをフルスキャンしているんです。
なので、結果的にindexがあってもなくても、SELECTにかかる時間は全く変わらなかった、というわけです。
つまり、 ab%のようにすると、以下のような結果に。
indexはやはりすごかった。。。
もしくは、InnoDBのフルテキストインデックスというものを使うと、文字列中にあってもインデックスの恩恵が受けられます。
今回はこんなところで失礼します。
これからも落とし穴にはまりながら、這い上がっていきたいと思います。
これからも落とし穴にはまりながら、這い上がっていきたいと思います。
