MySQLでindexをつけると、どのくらいSelectのスピードが変わるのか

仕事で良く使っているMySQLのクエリの実行速度について、 感覚として知っておきたかったので実験。

注）個人メモなので、オチとかないです。あらかじめ言っておきます。

• MacbookPro 2.2Ghz コアi7　メモリ16GB
• homebrewでインストールしただけのmysql
• 10万件のユーザデータを1つのテーブルに格納。
• シンプルに東京在住、20代、男性、のように3カラムのWHEREで絞る。
• Rubyのbenchmarkモジュールを使って実行時間を計測。

データサンプル

こんな感じ。

RubyのFakerというgemでダミーデータは入れました。

まず普通に実行

Benchmark.bm do |x|
  x.report {
    # 検索結果が少ない順
    puts User.where(:state => '東京都', :age => 20..29, :sex => 1).count
  }
end

都道府県は、日本語で最初はやりました。

• 結果

      user     system      total        real
  0.010000   0.000000   0.010000 (  0.046598)

indexをつけてみる。

MySQLには、indexという機能がある。

要は、普通に以下の様なクエリを投げると、

SELECT * FROM users WHERE 'state' = '東京' AND 'sex' = 1 ...

MySQLは最初にテーブル全件を読んで、そっから絞り込むので 件数が多くなるほど遅くなると。 それを解消するために本のさくいんのようなものをつけるのがindexだそうです。 以下参考。

今回は、state、age、sexに複合インデックスをつけてみたいと思います。

• 結果

      user     system      total        real
  0.010000   0.010000   0.020000 (  0.018678)

半分以下になりました。

都道府県を数字にするとどの程度かわるか

さっきまでは、都道府県をVACHARでやっていたので それを数字に置き換えてINTEGERに変更してやっていました。

      user     system      total        real
  0.010000   0.000000   0.010000 (  0.017745)

あんまかわんないね。

結論

あたりまえですが、indexの効果は大きく、 今回のテストでは半分くらいの実行時間にまで 短縮することができました。

あとは、こういうことを実験しておくと、 体感として効果が実感できるので、 業務の時の判断にも生かせそう。

なのでまた色々ベンチマークやテストとかしていきたい。