MySQLの気持ちを理解するやつ

bench.rb

# coding: utf-8

require 'bundler/setup'
require 'faker'
require 'mysql2'
require 'thor'
require 'benchmark'

class Bench < Thor

  # LOAD DATA INFILEで読み込むcsvを作る
  desc 'create_data', 'Create data file(csv)'
  option :out, :required => true, :type => :string
  option :from, :required => true, :type => :numeric
  option :to, :required => true, :type => :numeric
  def create_data
    File.open(options[:out], 'w') do |f|

      # idは連番で生成する. 名前は適当で, ただしユニークになるよう末尾にidを追加しておく
      ( options[:from] .. options[:to] ).each do |id|
        name = "#{Faker::Internet.user_name}#{id.to_s}"

        f.print "\"#{id.to_s}\",\"#{name}\"\n"
      end
    end

  end

  # id列で検索してSELECTするベンチマークの実行
  desc 'select_id_bench', 'Benchmark selecting by id'
  option :count, :required => true, :type => :numeric
  option :table, :required => true, :type => :string
  def select_id_bench
    ids = ( 1 .. options[:count] ).to_a.shuffle
    db  = Mysql2::Client.new(:host => 'localhost', :username => 'USER', :password => 'PASS', :database => 'bench')

    time = Benchmark.realtime do
      for id in ids
        db.query("SELECT * FROM #{options[:table]} WHERE id = #{id.to_s}").first
      end
    end

    puts "Result: #{time.to_s}"
  end

  # name列で検索して指定列をSELECTするベンチマークの実行
  desc 'select_name_bench', 'Benchmark selecting by name'
  option :count, :required => true, :type => :numeric
  option :table, :required => true, :type => :string
  option :fetch_column, :required => true, :type => :string
  def select_name_bench

    names = [ ]
    db = Mysql2::Client.new(:host => 'localhost', :username => 'NAME', :password => 'PASS', :database => 'bench')
    db.query("SELECT name FROM #{options[:table]} LIMIT #{options[:count].to_s}").each do |row|
      names.push(row['name'])
    end

    time = Benchmark.realtime do
      for name in names
        db.query("SELECT #{options[:fetch_column]} FROM #{options[:table]} WHERE name = '#{name}'")
      end
    end

    puts "Result: #{time.to_s}"
  end

end

Bench.start(ARGV)