新卒の僕がはまったMySQLの落とし穴


こんにちは、新卒エンジニアの久保田です。
ここ最近gitの記事を書いている文系出身の男です。


さて、今回はすこし趣向を変えて、みんな大好き「MySQL」についてです。
毎日のように触るMySQLの、僕がこの一カ月ではまった落とし穴について触れていきます。
これから同じ道を通る人のためにも、僕が穴をふさいでおいておきたいと思います。


落とし穴その①~select文の読み込み順~

ある日の事です。僕が、すっかり使いなれた様子でASを使って以下のようなクエリーを実行しました。


sql_where_as


すると。。。


sql_where_as_err



え、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ですね。


sql_having_as



落とし穴その②~integer(1)~

カラムを定義するとき、カラムに入る数字の桁数を制限するため、integer(1)のようにかっこの中に数字を入れますよね。
この数字、実は、カラムに入れられる値を制限しているわけではないのです。

つまり、このようなテーブルを作って、
 

sql_integer_1_table



このようなクエリーを実行すると、、、


sql_integer_1_query

 
このように、カラムにデータが入ってしまうんですね!


sql_integer_1_result



この数字は、桁数を制限しているわけでなく、zero fillオプションを使った時に足りないスペースにいくつ0で穴を埋めるか、というだけの数字なんですね。

つまり、integer(1)としても、-2147483648 ~ 2147483647の値までがはいるわけです。

知らないと無駄な領域を確保してしまうので、tinyint等を用いてデータ型で指定するよう、気をつけた方がいいかも知れませんね。



落とし穴その③~indexが効かない LIKE '%hoge%'~

便利ですよね、index。一度知ってしまったら離れることのできない蜜の味なのですが、意外な事にも落とし穴がありました。
indexでどのくらい早くなるのかを計測して遊ぶため、このような同じ状態のindexを貼ったテーブル(index_table)と貼っていないテーブル(no_index_table)を作り、


sql_index_table



itemカラムに一万行の適当な文字列を入れたデータを作り、下のようなクエリーを一万回発行するrubyスクリプトを実行しました。。

select * from item where name LIKE '%ab%'

すると、indexがある時とない時の結果が、、、


sql_index_select

 

まったく変わらない結果になってしまいました。(今回はrealの数字で検証)
まさかindexというのは幻想だったのか、と思っていると隣でいつも傘をかぶって仕事しているチーフが、


「indexやるときは、%%で囲んじゃダメだよ」
と鶴の一声をささやいてくれました。

調べてみると、indexはバイナリツリーというアルゴリズムでできているらしく、
indexをつけたカラムをソートしてN個の子を持つツリー上にし、検索していくらしいのです。
こうしたツリー上に値を持つことでテーブルをフルスキャンする必要をなくし、検索のスピードを上げているんですね。
LIKE '%ab%' にしてしまうと、文字列中のどこかにabがある文字列を検索するので、ソートしても、結局テーブルをフルスキャンしているんです。
なので、結果的にindexがあってもなくても、SELECTにかかる時間は全く変わらなかった、というわけです。
つまり、 ab%のようにすると、以下のような結果に。

 
sql_index_select2


indexはやはりすごかった。。。

もしくは、InnoDBのフルテキストインデックスというものを使うと、文字列中にあってもインデックスの恩恵が受けられます。



今回はこんなところで失礼します。
これからも落とし穴にはまりながら、這い上がっていきたいと思います。