MySQL INDEX 実験してみた

ソーシャルゲーム開発者なら知っておきたい MySQL INDEX + EXPLAIN入門 | 株式会社インフィニットループ技術ブログを読んで自分で実験してみたくなって書いた。コードぐっちゃぐちゃだけど飽きてきたから忘れないうちに貼っちゃう。

ダミーデータ生成とか、結果をCSVファイルに吐き出すとか、ただそれだけ。

それっぽいデータが取れたので満足。

USE test;

CREATE TABLE IF NOT EXISTS test_records (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  col_1 int(11) NOT NULL,
  col_2 varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  col_3 int(11) NOT NULL,
  col_4 int(11) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS test_indexed_records (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  col_1 int(11) NOT NULL,
  col_2 varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  col_3 int(11) NOT NULL,
  col_4 int(11) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE INDEX col_1_index ON test_indexed_records(col_1);
CREATE INDEX col_2_index ON test_indexed_records(col_2);

なぜかRakefile

# -*- coding: utf-8 -*-

require 'pp'
require 'mysql2'
require 'active_record'
require 'benchmark'

ActiveRecord::Base.configurations = {
  'development' => {
    :adapter => 'mysql2',
    :database => 'test'
  }
}
ActiveRecord::Base.establish_connection('development')
class TestRecords < ActiveRecord::Base; end
class TestIndexedRecords < ActiveRecord::Base; end

TABLES = [TestRecords, TestIndexedRecords]

namespace :db do
  desc "create table"
  task :create do
  end

  def db_seed(count = 1000, data_max = 3000)
    count.times do
      TABLES.each do |t|
        t.create(:col_1 => rand(data_max),
                 :col_2 => rand(data_max),
                 :col_3 => rand(data_max),
                 :col_4 => rand(data_max))
      end
    end
  end

  desc "insert seed data"
  task :seed do
    count = (ENV["COUNT"] || 1000).to_i
    puts "inserting #{count} records"
    db_seed(count)
  end


  def db_count(table)
    table.count
  end
  desc "show record count"
  task :count do
    puts db_count(TestRecords)
    puts db_count(TestIndexedRecords)
  end

  desc "clean up data"
  task :clean do
    TestRecords.delete_all
    TestIndexedRecords.delete_all
  end
end

namespace :bench do
  desc "set up and run benchmarks"
  task :all => "db:clean" do
    upto = (ENV["upto"] || 10).to_i
    by   = (ENV["by"]   || upto / 10).to_i

    #upto, by = 100_000, 10_000
    #upto, by = 10_000, 2_000
    #upto, by = 10, 2

    result = {}
    Benchmark.bm do |x|
      by.step(upto, by) do |n|
        db_seed(by)

        c = TestRecords.count
        count = c.to_s.rjust(upto.to_s.length)
        t = x.report("count(#{count}) w/o index:") do
          sql = "select * from test_records where col_1 = 2000"
          10.times { TestRecords.find_by_sql sql }
        end
        result[c] ||= {}
        result[c][:unindexed] = t

        c = TestIndexedRecords.count
        count = c.to_s.rjust(upto.to_s.length)
        t = x.report("count(#{count}) w/  index:") do
          sql = "select * from test_indexed_records where col_1 = 2000"
          10.times { TestIndexedRecords.find_by_sql sql }
        end
        result[c] ||= {}
        result[c][:indexed] = t
      end
    end

    def output(filename, results)
      File.open(filename, 'w') do |f|
        f.puts "count,unindexed,indexed"
        results.each do |k, rs|
          r1 = rs[:unindexed]
          r2 = rs[:indexed]
          f.puts "#{k},#{r1.real},#{r2.real}"
        end
      end
    end

    output("result.csv", result)
  end
end