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