LoginSignup
2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

@watame

Rails環境でのMySQLインデックス付与効果の調査

Posted at

概要

Rails環境でのMySQLインデックスの効果を調査します。

目的

インデックスがの効果を理解したうえで、インデックスを設定できるようになること。

環境

  • WSL2
    • Ubuntu 20.04.2 LTS (GNU/Linux 4.19.128-microsoft-standard x86_64)
  • Rails
    • version 6.1.4.4
    • Ruby 2.7.2
  • MySQL
    • 8.0.27 Community Server

テスト用プロジェクト作成

プロジェクトディレクトリの作成

ターミナル
$ mkdir considerable_db_select

Gemfile作成・編集

プロジェクトディレクトリに移動し、Gemfileを作成します。

ターミナル
$ cd considerable_db_select
$ touch Gemfile

Gemfileを編集し、Railsのバージョンを指定します。

Gemfile
source 'https://rubygems.org'
gem 'rails', '6.1.4.4'

Railsプロジェクト作成

先程作成したGemfileを基にRailsプロジェクトを作成します。

ターミナル
$ rails new . --database=mysql

モデル作成

Railsコマンドでモデルを作成します。

ターミナル
$ rails g model user
Running via Spring preloader in process 194
      invoke  active_record
      create    db/migrate/20220106143118_create_users.rb
      create    app/models/user.rb
      invoke    test_unit
      create      test/models/user_test.rb
      create      test/fixtures/users.yml

Modelの定義

今回は3つのカラムに対してインデックス、複合インデックスを付与して試験をします。

db/migrate/20220106143118_create_users.rb
class CreateUsers < ActiveRecord::Migration[6.1]
  def change
    create_table :users do |t|
      t.string :name, comment: '名前'
      t.string :email, comment: 'メールアドレス'
      t.integer :age, limit: 1, unsigned: true, comment: '年齢'
      t.timestamps
    end
  end
end

変更したModel定義をRailsコマンドで反映します。

ターミナル
$ rails db:migrate
Running via Spring preloader in process 208
== 20220106143118 CreateUsers: migrating ======================================
-- create_table(:users)
   -> 0.0511s
== 20220106143118 CreateUsers: migrated (0.0512s) =============================

大量のデータを投入

今回は試験のためseeds.rbで大量のデータを追加します。
並列でDBへ挿入しないと非常に時間がかかるのでparallelgemを利用して行います。

parallel gemの追加

Gemfilへ追加し、bundle installでGemを追加します。

Gemfile
gem `parallel`
ターミナル
$ bundle install

Seedファイルを設定

db/seed.rb
# parallelを利用して5スレッド並列で10万レコードのテストデータを挿入
# -> `in_threads`は`config/database.yml`の`pool`の値を設定する
Parallel.each(1..100_000, in_threads: 5) do |n|
  ActiveRecord::Base.connection_pool.with_connection do
    User.create!(
      name: "テスト太郎#{rand(1..9999)}",
      email: "test#{rand(1..9999)}@example.com",
      age: rand(0..150)
    )
  end
end

自分の環境では15分程度でデータの挿入が完了しました。

ベンチマーク計測用ファイルの作成

ベンチマークを用いて問い合わせ時のパフォーマンスを計測します。
ベンチマーク計測用ファイルはプロジェクト直下に作成します。

select_benchmark_all.rb
require 'benchmark'

# 問い合わせ回数
REPEAT_TIMES = 500.freeze

def start_benchmark
  # find_byのベンチマークを行う際にログが死ぬほど出るので、ベンチマークの間だけDB問い合わせログの表示を無効化
  prev_logger = ActiveRecord::Base.logger
  ActiveRecord::Base.logger = nil

  # タイトルを表示する領域を15字分設定
  Benchmark.bm 15 do |r|
    r.report "name" do
      REPEAT_TIMES.times do
        User.find_by(name: "test#{rand(1..100000)}")
      end
    end

    r.report "email" do
      REPEAT_TIMES.times do
        User.find_by(email: "test#{rand(1..100000)}@example.com")
      end
    end

    r.report "age" do
      REPEAT_TIMES.times do
        User.find_by(age: rand(0..150))
      end
    end

    r.report "name_email" do
      REPEAT_TIMES.times do
        User.find_by(name: "テスト太郎#{rand(1..100000)}", email: "test#{rand(1..100000)}@example.com")
      end
    end

    r.report "name_age" do
      REPEAT_TIMES.times do
        User.find_by(name: "テスト太郎#{rand(1..100000)}", age: rand(0..150))
      end
    end

    r.report "name_email_age" do
      REPEAT_TIMES.times do
        User.find_by(name: "テスト太郎#{rand(1..100000)}", email: "test#{rand(1..100000)}@example.com", age: rand(0..150))
      end
    end

    r.report "name_age_email" do
      REPEAT_TIMES.times do
        User.find_by(name: "テスト太郎#{rand(1..100000)}", age: rand(0..150), email: "test#{rand(1..100000)}@example.com")
      end
    end

    r.report "email_name" do
      REPEAT_TIMES.times do
        User.find_by(email: "test#{rand(1..100000)}@example.com", name: "テスト太郎#{rand(1..100000)}")
      end
    end

    r.report "email_age" do
      REPEAT_TIMES.times do
        User.find_by(email: "test#{rand(1..100000)}@example.com", age: rand(0..150))
      end
    end

    r.report "email_name_age" do
      REPEAT_TIMES.times do
        User.find_by(email: "test#{rand(1..100000)}@example.com", name: "テスト太郎#{rand(1..100000)}", age: rand(0..150))
      end
    end

    r.report "email_age_name" do
      REPEAT_TIMES.times do
        User.find_by(email: "test#{rand(1..100000)}@example.com", age: rand(0..150), name: "テスト太郎#{rand(1..100000)}")
      end
    end

    r.report "age_name" do
      REPEAT_TIMES.times do
        User.find_by(age: rand(0..150), name: "テスト太郎#{rand(1..100000)}")
      end
    end

    r.report "age_email" do
      REPEAT_TIMES.times do
        User.find_by(age: rand(0..150), email: "test#{rand(1..100000)}@example.com")
      end
    end

    r.report "age_name_email" do
      REPEAT_TIMES.times do
        User.find_by(age: rand(0..150), name: "テスト太郎#{rand(1..100000)}", email: "test#{rand(1..100000)}@example.com")
      end
    end

    r.report "age_email_name" do
      REPEAT_TIMES.times do
        User.find_by(age: rand(0..150), email: "test#{rand(1..100000)}@example.com", name: "テスト太郎#{rand(1..100000)}")
      end
    end
  end
  # ロガーにSQLログが表示されるように再設定
  ActiveRecord::Base.logger = prev_logger
  # ベンチマークのインスタンスを表示されても困るのでnilを戻す
  nil
end
+α. ベンチマーク時にロガーの設定をしている理由

ベンチマークをそのまま実行するとfind_byのSQLログが繰り返し回数分だけ出力されます。

                      user     system      total        real
name              User Load (0.5ms)  SELECT `users`.* FROM `users` WHERE `users`.`name` = 'test7587' LIMIT 1
  User Load (0.5ms)  SELECT `users`.* FROM `users` WHERE `users`.`name` = 'test65641' LIMIT 1
  User Load (0.5ms)  SELECT `users`.* FROM `users` WHERE `users`.`name` = 'test24924' LIMIT 1
  User Load (0.4ms)  SELECT `users`.* FROM `users` WHERE `users`.`name` = 'test86882' LIMIT 1
  User Load (0.5ms)  SELECT `users`.* FROM `users` WHERE `users`.`name` = 'test55158' LIMIT 1
  User Load (0.6ms)  SELECT `users`.* FROM `users` WHERE `users`.`name` = 'test20788' LIMIT 1
  User Load (0.8ms)  SELECT `users`.* FROM `users` WHERE `users`.`name` = 'test74850' LIMIT 1
  User Load (0.6ms)  SELECT `users`.* FROM `users` WHERE `users`.`name` = 'test33180' LIMIT 1
  User Load (0.6ms)  SELECT `users`.* FROM `users` WHERE `users`.`name` = 'test61106' LIMIT 1
  User Load (0.5ms)  SELECT `users`.* FROM `users` WHERE `users`.`name` = 'test35902' LIMIT 1
  ...
  合計500件分の問い合わせレコード
  ...
  0.124768   0.004476   0.129244 (  0.165278) <- 計測結果がヘッダと離れてしまうので、非常に見にくい

以下の設定でSQLログを非表示にし、ベンチマークの結果が流れないようにしています。

ActiveRecord::Base.logger = nil

ベンチマークでの速度計測

モデルの情報を取得する必要があるため、rails cでirbを起動してベンチマークファイルを呼び出します。

ターミナル
$ rails c
irb
irb(main):001:0> require './select_benchmark_all.rb'
=> true
irb(main):002:0> 3.times do start_benchmark end

ベンチマークのルール

以下のルールでベンチマークを計測し、結果の比較を行うこととしました。

  • 10万件のレコードをベンチマークの対象とする
  • ベンチマークは以下の条件で計測する
    • インデックスなし
    • インデックスあり
      • 各カラムにインデックスを付与
      • Whereに沿った複合インデックスを付与
  • 各条件で3回ベンチマークを取得し、各項目の平均を比較値として利用する

最終的には以下の2パターンでreal以外の項目について性能がどの程度上がったかを確認します。
（realは経過時間そのもので、CPUがぼーっとしている時間も含まれるので見る必要がないとのこと）

  1. Indexなし VS 各カラムにインデックスを付与
  2. Indexなし VS Whereに沿った複合インデックスを付与

結果

1. インデックスなし VS 各カラムにインデックスを付与

インデックスなし インデックスあり indexなし / インデックスあり
項目名/区分 user system total real user system total real user system total real
name 0.119977 0.05756 0.17753699999999997 12.037523 0.09258533333333334 0.030229000000000002 0.12281433333333334 0.185155 1.2958531948904792 1.9041317939726752 1.445572313763594 65.01322135508089
email 0.13663166666666668 0.030953000000000005 0.1675846666666667 11.741430999999999 0.09361033333333334 0.015792333333333335 0.10940266666666666 0.16877 1.4595788926436184 1.9600016885830678 1.5318151903670845 69.57060496533744
age 0.10503099999999999 0.028295666666666667 0.13332666666666668 0.22401133333333334 0.08147900000000001 0.029367333333333332 0.11084633333333334 0.21201599999999998 1.2890560757986718 0.9635082063971306 1.2028062873828334 1.056577491006968
name_email 0.176049 0.019003 0.195052 24.200944000000003 0.06833733333333332 0.03618433333333333 0.10452166666666667 0.172516 2.576176028720271 0.5251720357797574 1.8661393969352447 140.2823158431682
name_age 0.149232 0.02724666666666667 0.17647866666666667 18.97560866666667 0.08749033333333334 0.013404666666666667 0.100895 0.164223 1.70569701033638 2.0326254538220523 1.7491319358408908 115.54781404959517
name_email_age 0.164088 0.023389000000000004 0.187477 19.053912 0.09993 0.009172333333333333 0.10910233333333334 0.1806483333333333 1.6420294205944161 2.5499509394192685 1.7183592162709627 105.47516076354614
name_age_email 0.16347633333333333 0.047009999999999996 0.21048633333333333 24.41242766666667 0.08065266666666666 0.024000333333333335 0.104653 0.18292300000000003 2.0269178948412536 1.9587227955167286 2.011278542739657 133.4573982859819
email_name 0.17966766666666667 0.012885 0.19255266666666668 22.417591666666667 0.07405633333333334 0.026322 0.10037833333333333 0.17567033333333334 2.42609454964464 0.48951447458399816 1.9182692148039917 127.61171019200737
email_age 0.15613133333333332 0.028058666666666666 0.18419 19.154373333333336 0.08351633333333335 0.016311000000000003 0.09982733333333334 0.173489 1.8694706424691374 1.7202297018372057 1.8450858482312793 110.40684615931463
email_name_age 0.15354833333333334 0.026660666666666666 0.18020899999999998 18.98274566666667 0.07996633333333333 0.02410633333333333 0.10407266666666666 0.176481 1.9201622349405376 1.1059610890637317 1.7315689678365755 107.5625459209018
email_age_name 0.149717 0.038108 0.187825 22.171620666666666 0.084615 0.019504 0.104119 0.175933 1.7693907699580451 1.9538556193601313 1.8039454854541437 126.02309212408511
age_name 0.148166 0.03416533333333333 0.18233133333333332 22.189705333333336 0.07397966666666667 0.025941000000000002 0.09992066666666666 0.166651 2.0027935603927203 1.3170399496292868 1.8247609770417865 133.15074817032803
age_email 0.15577466666666667 0.03964033333333333 0.19541499999999998 24.123951 0.07083433333333333 0.029519666666666666 0.100354 0.16856866666666667 2.199140718013393 1.342844883072302 1.9472567112422023 143.11052864707955
age_name_email 0.16282333333333332 0.039232666666666666 0.20205599999999999 24.318613999999997 0.091434 0.013128 0.104562 0.17850233333333332 1.780774474848889 2.9884724761324395 1.9324037413209385 136.23695301835457
age_email_name 0.14960533333333334 0.05019233333333334 0.19979766666666668 22.528416333333336 0.063063 0.04117766666666667 0.10424066666666666 0.17867733333333333 2.3723155151726583 1.21892125990626 1.9166959791763933 126.08435503850518

各項目の比較値を平均すると以下です。

user system total real
1.8890300655510075 1.6020634911384024 1.7630059872271717 109.37265813495286

-> realを抜くと**約75%**の性能向上が確認できました。

2. インデックスなし VS Whereに沿った複合インデックスを付与

インデックスなし インデックスあり indexなし / インデックスあり
項目名/区分 user system total real user system total real user system total real
name 0.119977 0.05756 0.17753699999999997 12.037523 0.09323133333333333 0.045049 0.13828033333333334 0.21919033333333335 1.286874226833611 1.2777198162001377 1.2838919007523362 54.91812899291483
email 0.13663166666666668 0.030953000000000005 0.1675846666666667 11.741430999999999 0.08450400000000001 0.03976133333333333 0.12426533333333334 0.203291 1.6168662627410142 0.7784698702256801 1.3486035257889035 57.756767392555496
age 0.10503099999999999 0.028295666666666667 0.13332666666666668 0.22401133333333334 0.09956066666666667 0.019741333333333333 0.119302 0.231119 1.0549447238199823 1.4333209509658247 1.1175560063256833 0.9692467228282112
name_email 0.176049 0.019003 0.195052 24.200944000000003 0.088985 0.024878333333333336 0.11386333333333333 0.202157 1.9784120919256056 0.7638373417297514 1.7130360959044468 119.7136087298486
name_age 0.149232 0.02724666666666667 0.17647866666666667 18.97560866666667 0.08737033333333333 0.025171 0.11254133333333333 0.19444766666666666 1.7080397236285392 1.08246262232993 1.568123119209535 97.58722741166005
name_email_age 0.164088 0.023389000000000004 0.187477 19.053912 0.10206966666666667 0.022748 0.12481766666666666 0.23230866666666664 1.60760787566662 1.0281783013891332 1.5020069274381567 82.01980698094204
name_age_email 0.16347633333333333 0.047009999999999996 0.21048633333333333 24.41242766666667 0.09404933333333333 0.034511 0.12856033333333333 0.23657466666666668 1.738197683485263 1.3621743791834486 1.6372572151596787 103.19121658560229
email_name 0.17966766666666667 0.012885 0.19255266666666668 22.417591666666667 0.108145 0.016540666666666665 0.12468566666666665 0.21546833333333335 1.661358977915453 0.7789891580347428 1.544304744998757 104.04123575776795
email_age 0.15613133333333332 0.028058666666666666 0.18419 19.154373333333336 0.086296 0.035950666666666665 0.12224666666666667 0.21367666666666665 1.8092534223293468 0.7804769498942996 1.506707749359219 89.6418576353681
email_name_age 0.15354833333333334 0.026660666666666666 0.18020899999999998 18.98274566666667 0.094124 0.03335733333333333 0.12748133333333334 0.24030166666666666 1.6313409261541514 0.7992445439283716 1.4136108815931219 78.9954806805335
email_age_name 0.149717 0.038108 0.187825 22.171620666666666 0.11526033333333334 0.015042333333333333 0.13030266666666665 0.241448 1.298946442979805 2.5333835619473932 1.4414516970745035 91.82772550059087
age_name 0.148166 0.03416533333333333 0.18233133333333332 22.189705333333336 0.09018 0.034354 0.12453399999999999 0.2133263333333333 1.6430028831226435 0.9945081601366167 1.4641088645135731 104.01765682936474
age_email 0.15577466666666667 0.03964033333333333 0.19541499999999998 24.123951 0.10210933333333333 0.022233 0.12434233333333333 0.21774833333333335 1.5255673656995117 1.7829502691194772 1.5715886517597921 110.78822340775666
age_name_email 0.16282333333333332 0.039232666666666666 0.20205599999999999 24.318613999999997 0.095568 0.03349866666666667 0.12906666666666666 0.23559866666666665 1.7037432334393658 1.1711709918802737 1.5655165289256199 103.22050775612765
age_email_name 0.14960533333333334 0.05019233333333334 0.19979766666666668 22.528416333333336 0.08135833333333332 0.048427 0.12978533333333334 0.23627933333333337 1.8388446174331663 1.0364534935745213 1.5394471897184068 95.34653757275993

各項目の比較値を平均すると以下です。

user system total real
1.6068666971449386 1.1735560273693069 1.4811474065681154 86.26901519710808

-> realを抜くと**約42%**の性能向上が確認できました。

所感

各カラムにそれぞれインデックスを付与するパターンで、75%の性能向上を確認することが出来ました。
ただ、複合インデックスがWhereの順に設定すれば爆速になると聞いていたので、各カラムのインデックス付与よりも性能が向上しないのは残念でした。。。
インデックスなしの場合よりも性能自体は向上しているので、インデックスが効いていないことはないと思うのですが。。。
（恐らくインデックスの貼り方が間違っているだけかと思うので、引き続き調査を行う必要がありそうです）

また、今回の調査でインデックスを付けたり、外したりする方法も学ぶことが出来ました！
RailsからDBを扱うことに少しは慣れてきたのではないかと思います！

（おまけ）各項目の計測ログ

今回の調査の手順を各パターンごとにログとして残しました。
MySQLでのインデックスの確認等も含めて残しているので、かなり長いです。。。

ログ詳細

1. インデックスなし

インデックスなしの場合はベンチマーク計測のみを行います。

1-1. 結果

3回のベンチマークから平均を取得し比較対象の値とします。

実行回数 1回目 2回目 3回目 平均
項目名/区分 user system total real user system total real user system total real user system total real
name 0.184796 0.020163 0.204959 12.403299 0.096084 0.070318 0.166402 11.937761 0.079051 0.082199 0.16125 11.771509 0.119977 0.05756 0.17753699999999997 12.037523
email 0.125404 0.053447 0.178851 11.713384 0.140241 0.020188 0.160429 11.705146 0.14425 0.019224 0.163474 11.805763 0.13663166666666668 0.030953000000000005 0.1675846666666667 11.741430999999999
age 0.095059 0.033478 0.128537 0.201307 0.105612 0.025862 0.131474 0.231714 0.114422 0.025547 0.139969 0.239013 0.10503099999999999 0.028295666666666667 0.13332666666666668 0.22401133333333334
name_email 0.182493 0.012706 0.195199 24.216515 0.174361 0.018819 0.19318 24.165059 0.171293 0.025484 0.196777 24.221258 0.176049 0.019003 0.195052 24.200944000000003
name_age 0.13965 0.037509 0.177159 18.989737 0.158557 0.011399 0.169956 18.849954 0.149489 0.032832 0.182321 19.087135 0.149232 0.02724666666666667 0.17647866666666667 18.97560866666667
name_email_age 0.134688 0.046022 0.18071 19.076824 0.150945 0.023451 0.174396 18.932894 0.206631 0.000694 0.207325 19.152018 0.164088 0.023389000000000004 0.187477 19.053912
name_age_email 0.15958 0.042227 0.201807 24.222543 0.141402 0.061891 0.203293 24.272176 0.189447 0.036912 0.226359 24.742564 0.16347633333333333 0.047009999999999996 0.21048633333333333 24.41242766666667
email_name 0.173105 0.007625 0.18073 22.197503 0.184156 0.000951 0.185107 22.20186 0.181742 0.030079 0.211821 22.853412 0.17966766666666667 0.012885 0.19255266666666668 22.417591666666667
email_age 0.149757 0.022741 0.172498 18.953898 0.155153 0.017408 0.172561 18.902814 0.163484 0.044027 0.207511 19.606408 0.15613133333333332 0.028058666666666666 0.18419 19.154373333333336
email_name_age 0.134962 0.041572 0.176534 19.01526 0.153223 0.026015 0.179238 18.868271 0.17246 0.012395 0.184855 19.064706 0.15354833333333334 0.026660666666666666 0.18020899999999998 18.98274566666667
email_age_name 0.170493 0.014796 0.185289 22.213629 0.129427 0.060045 0.189472 22.136256 0.149231 0.039483 0.188714 22.164977 0.149717 0.038108 0.187825 22.171620666666666
age_name 0.160732 0.020446 0.181178 22.229864 0.144351 0.038095 0.182446 22.16279 0.139415 0.043955 0.18337 22.176462 0.148166 0.03416533333333333 0.18233133333333332 22.189705333333336
age_email 0.156309 0.038236 0.194545 24.15738 0.152898 0.044281 0.197179 24.144195 0.158117 0.036404 0.194521 24.070278 0.15577466666666667 0.03964033333333333 0.19541499999999998 24.123951
age_name_email 0.184938 0.018403 0.203341 24.471656 0.135366 0.065777 0.201143 24.153038 0.168166 0.033518 0.201684 24.331148 0.16282333333333332 0.039232666666666666 0.20205599999999999 24.318613999999997
age_email_name 0.145016 0.052333 0.197349 22.756954 0.134883 0.054077 0.18896 22.179658 0.168917 0.044167 0.213084 22.648637 0.14960533333333334 0.05019233333333334 0.19979766666666668 22.528416333333336

2. インデックスあり（各カラムにインデックスを付与）

2-1. マイグレーションファイルの作成

ターミナル
$ rails g migration add_index_user_each_columns
Running via Spring preloader in process 190
      invoke  active_record
      create    db/migrate/20220110125827_add_index_user_each_columns.rb
db/migrate/20220110125827_add_index_user_each_columns.rb
class AddIndexUserEachColumns < ActiveRecord::Migration[6.1]
  def change
    add_index :users, :name
    add_index :users, :email
    add_index :users, :age
  end
end

2-2. マイグレーションファイルの適用とインデックス確認

ターミナル
$ rails db:migrate
Running via Spring preloader in process 155
== 20220110125827 AddIndexUserEachColumns: migrating ==========================
-- add_index(:users, :name)
   -> 1.3526s
-- add_index(:users, :email)
   -> 1.8436s
-- add_index(:users, :age)
   -> 0.7364s
== 20220110125827 AddIndexUserEachColumns: migrated (3.9329s) ================

mysqlでインデックスが適用されているか確認します。

ターミナル
mysql> use considerable_db_select_development;
mysql> show index from users;
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users |          0 | PRIMARY              |            1 | id          | A         |       94032 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          1 | index_users_on_name  |            1 | name        | A         |       99826 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| users |          1 | index_users_on_email |            1 | email       | A         |       99826 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| users |          1 | index_users_on_age   |            1 | age         | A         |         192 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
4 rows in set (0.00 sec)

2-3. 結果

実行回数 1回目 2回目 3回目 平均
項目名/区分 user system total real user system total real user system total real user system total real
name 0.101244 0.052994 0.154238 0.22802 0.095663 0.012477 0.10814 0.157432 0.080849 0.025216 0.106065 0.170013 0.09258533333333334 0.030229000000000002 0.12281433333333334 0.185155
email 0.109867 0.009012 0.118879 0.18326 0.087584 0.015471 0.103055 0.15234 0.08338 0.022894 0.106274 0.17071 0.09361033333333334 0.015792333333333335 0.10940266666666666 0.16877
age 0.09528 0.029398 0.124678 0.224295 0.057978 0.046727 0.104705 0.213601 0.091179 0.011977 0.103156 0.198152 0.08147900000000001 0.029367333333333332 0.11084633333333334 0.21201599999999998
name_email 0.077991 0.035071 0.113062 0.175443 0.062348 0.038042 0.10039 0.172885 0.064673 0.03544 0.100113 0.16922 0.06833733333333332 0.03618433333333333 0.10452166666666667 0.172516
name_age 0.102032 0 0.102032 0.154649 0.071336 0.02884 0.100176 0.169989 0.089103 0.011374 0.100477 0.168031 0.08749033333333334 0.013404666666666667 0.100895 0.164223
name_email_age 0.111156 0.010722 0.121878 0.183617 0.088124 0.013819 0.101943 0.18144 0.10051 0.002976 0.103486 0.176888 0.09993 0.009172333333333333 0.10910233333333334 0.1806483333333333
name_age_email 0.101618 0.004041 0.105659 0.185949 0.06131 0.042637 0.103947 0.180886 0.07903 0.025323 0.104353 0.181934 0.08065266666666666 0.024000333333333335 0.104653 0.18292300000000003
email_name 0.086675 0.012576 0.099251 0.173273 0.086501 0.013465 0.099966 0.172219 0.048993 0.052925 0.101918 0.181519 0.07405633333333334 0.026322 0.10037833333333333 0.17567033333333334
email_age 0.093451 0.007663 0.101114 0.173296 0.096709 0.002186 0.098895 0.17418 0.060389 0.039084 0.099473 0.172991 0.08351633333333335 0.016311000000000003 0.09982733333333334 0.173489
email_name_age 0.082012 0.023237 0.105249 0.164651 0.088585 0.013772 0.102357 0.180251 0.069302 0.03531 0.104612 0.184541 0.07996633333333333 0.02410633333333333 0.10407266666666666 0.176481
email_age_name 0.08192 0.021129 0.103049 0.163807 0.08511 0.018672 0.103782 0.180851 0.086815 0.018711 0.105526 0.183141 0.084615 0.019504 0.104119 0.175933
age_name 0.095358 0.004731 0.100089 0.154133 0.038377 0.061667 0.100044 0.177529 0.088204 0.011425 0.099629 0.168291 0.07397966666666667 0.025941000000000002 0.09992066666666666 0.166651
age_email 0.057332 0.044002 0.101334 0.158317 0.066989 0.033157 0.100146 0.172823 0.088182 0.0114 0.099582 0.174566 0.07083433333333333 0.029519666666666666 0.100354 0.16856866666666667
age_name_email 0.091743 0.011442 0.103185 0.162566 0.087681 0.01675 0.104431 0.180981 0.094878 0.011192 0.10607 0.19196 0.091434 0.013128 0.104562 0.17850233333333332
age_email_name 0.060333 0.048183 0.108516 0.18156 0.07232 0.029589 0.101909 0.176594 0.056536 0.045761 0.102297 0.177878 0.063063 0.04117766666666667 0.10424066666666666 0.17867733333333333

2-4. インデックスを削除

次の計測を行うため、付与したインデックスを削除します。

ターミナル
$ rails g migration remove_index_user_each_columns
Running via Spring preloader in process 197
      invoke  active_record
      create    db/migrate/20220111144123_remove_index_user_each_columns.rb
db/migrate/20220111144123_remove_index_user_each_columns.rb
class RemoveIndexUserEachColumns < ActiveRecord::Migration[6.1]
  def change
    remove_index :users, :name
    remove_index :users, :email
    remove_index :users, :age
  end
end
ターミナル
$ rails db:migrate
Running via Spring preloader in process 206
== 20220111144123 RemoveIndexUserEachColumns: migrating =======================
-- remove_index(:users, :name)
   -> 0.0278s
-- remove_index(:users, :email)
   -> 0.0246s
-- remove_index(:users, :age)
   -> 0.0269s
== 20220111144123 RemoveIndexUserEachColumns: migrated (0.0796s) ==============

mysqlでインデックスが削除されているか確認します。

ターミナル
mysql> use considerable_db_select_development;
mysql> desc users;
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | bigint           | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255)     | YES  |     | NULL    |                |
| email      | varchar(255)     | YES  |     | NULL    |                |
| age        | tinyint unsigned | YES  |     | NULL    |                |
| created_at | datetime(6)      | NO   |     | NULL    |                |
| updated_at | datetime(6)      | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

3. インデックスあり（Whereに沿った複合インデックスを付与）

3-1. マイグレーションファイルの作成

ターミナル
rails g migration add_index_user_multiple_columns
Running via Spring preloader in process 215
      invoke  active_record
      create    db/migrate/20220112144927_add_index_user_multiple_columns.rb
db/migrate/20220112144927_add_index_user_multiple_columns.rb
class AddIndexUserMultipleColumns < ActiveRecord::Migration[6.1]
  def change
    add_index :users, [:age, :name, :email]
    add_index :users, [:name, :email, :age]
    add_index :users, [:email, :age, :name]
    add_index :users, [:age, :email, :name]
    add_index :users, [:email, :name, :age]
    add_index :users, [:name, :age, :email]
  end
end

3-2. マイグレーションファイルの適用とインデックス確認

ターミナル
rails db:migrate
Running via Spring preloader in process 304
== 20220112152642 AddIndexUserMultipleColumns: migrating =====================
-- add_index(:users, [:age, :name, :email])
   -> 2.1185s
-- add_index(:users, [:name, :email, :age])
   -> 2.0313s
-- add_index(:users, [:email, :age, :name])
   -> 2.2623s
-- add_index(:users, [:age, :email, :name])
   -> 2.2616s
-- add_index(:users, [:email, :name, :age])
   -> 2.0837s
-- add_index(:users, [:name, :age, :email])
   -> 2.2160s
== 20220112152642 AddIndexUserMultipleColumns: migrated (12.9740s) ===========

mysqlでインデックスが付与されているか確認します。

ターミナル
mysql> show index from users;
+-------+------------+---------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name                              | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+---------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| users |          0 | PRIMARY                               |            1 | id          | A         |       94032 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| users |          1 | index_users_on_age_and_name_and_email |            1 | age         | A         |         202 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| users |          1 | index_users_on_age_and_name_and_email |            2 | name        | A         |       99826 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| users |          1 | index_users_on_age_and_name_and_email |            3 | email       | A         |       99826 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| users |          1 | index_users_on_name_and_email_and_age |            1 | name        | A         |       99826 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| users |          1 | index_users_on_name_and_email_and_age |            2 | email       | A         |       99826 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| users |          1 | index_users_on_name_and_email_and_age |            3 | age         | A         |       99826 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| users |          1 | index_users_on_email_and_age_and_name |            1 | email       | A         |       99826 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| users |          1 | index_users_on_email_and_age_and_name |            2 | age         | A         |       99826 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| users |          1 | index_users_on_email_and_age_and_name |            3 | name        | A         |       99826 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| users |          1 | index_users_on_age_and_email_and_name |            1 | age         | A         |         191 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| users |          1 | index_users_on_age_and_email_and_name |            2 | email       | A         |       99826 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| users |          1 | index_users_on_age_and_email_and_name |            3 | name        | A         |       99826 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| users |          1 | index_users_on_email_and_name_and_age |            1 | email       | A         |       99826 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| users |          1 | index_users_on_email_and_name_and_age |            2 | name        | A         |       99826 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| users |          1 | index_users_on_email_and_name_and_age |            3 | age         | A         |       99826 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| users |          1 | index_users_on_name_and_age_and_email |            1 | name        | A         |       99826 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| users |          1 | index_users_on_name_and_age_and_email |            2 | age         | A         |       99826 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| users |          1 | index_users_on_name_and_age_and_email |            3 | email       | A         |       99826 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
+-------+------------+---------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
19 rows in set (0.00 sec)

3-3. 結果

実行回数 1回目 2回目 3回目 平均
項目名/区分 user system total real user system total real user system total real user system total real
name 0.142947 0.0262 0.169147 0.265678 0.118934 0.017906 0.13684 0.210385 0.017813 0.091041 0.108854 0.181508 0.09323133333333333 0.045049 0.13828033333333334 0.21919033333333335
email 0.110352 0.032993 0.143345 0.236412 0.053 0.071738 0.124738 0.196106 0.09016 0.014553 0.104713 0.177355 0.08450400000000001 0.03976133333333333 0.12426533333333334 0.203291
age 0.140528 0.002601 0.143129 0.26415 0.072299 0.035691 0.10799 0.221997 0.085855 0.020932 0.106787 0.20721 0.09956066666666667 0.019741333333333333 0.119302 0.231119
name_email 0.082666 0.057177 0.139843 0.240555 0.083477 0.017458 0.100935 0.183504 0.100812 0 0.100812 0.182412 0.088985 0.024878333333333336 0.11386333333333333 0.202157
name_age 0.095453 0.025902 0.121355 0.205616 0.060621 0.042125 0.102746 0.181752 0.106037 0.007486 0.113523 0.195975 0.08737033333333333 0.025171 0.11254133333333333 0.19444766666666666
name_email_age 0.096965 0.033171 0.130136 0.243576 0.08013 0.0229 0.10303 0.200773 0.129114 0.012173 0.141287 0.252577 0.10206966666666667 0.022748 0.12481766666666666 0.23230866666666664
name_age_email 0.118087 0.023659 0.141746 0.255481 0.048665 0.056811 0.105476 0.202183 0.115396 0.023063 0.138459 0.25206 0.09404933333333333 0.034511 0.12856033333333333 0.23657466666666668
email_name 0.111005 0.024605 0.13561 0.227609 0.102973 0 0.102973 0.186023 0.110457 0.025017 0.135474 0.232773 0.108145 0.016540666666666665 0.12468566666666665 0.21546833333333335
email_age 0.098197 0.030853 0.12905 0.225754 0.068812 0.030915 0.099727 0.181805 0.091879 0.046084 0.137963 0.233471 0.086296 0.035950666666666665 0.12224666666666667 0.21367666666666665
email_name_age 0.12836 0.009594 0.137954 0.249308 0.072985 0.033483 0.106468 0.223655 0.081027 0.056995 0.138022 0.247942 0.094124 0.03335733333333333 0.12748133333333334 0.24030166666666666
email_age_name 0.114365 0.022879 0.137244 0.251138 0.10205 0.011416 0.113466 0.223384 0.129366 0.010832 0.140198 0.249822 0.11526033333333334 0.015042333333333333 0.13030266666666665 0.241448
age_name 0.089613 0.045178 0.134791 0.227381 0.058726 0.041303 0.100029 0.179208 0.122201 0.016581 0.138782 0.23339 0.09018 0.034354 0.12453399999999999 0.2133263333333333
age_email 0.108056 0.029188 0.137244 0.2342 0.091907 0.006874 0.098781 0.184788 0.106365 0.030637 0.137002 0.234257 0.10210933333333333 0.022233 0.12434233333333333 0.21774833333333335
age_name_email 0.134192 0.008121 0.142313 0.253583 0.059607 0.045487 0.105094 0.200226 0.092905 0.046888 0.139793 0.252987 0.095568 0.03349866666666667 0.12906666666666666 0.23559866666666665
age_email_name 0.069038 0.071594 0.140632 0.252128 0.075505 0.033088 0.108593 0.205883 0.099532 0.040599 0.140131 0.250827 0.08135833333333332 0.048427 0.12978533333333334 0.23627933333333337

参考にさせていただいた記事

2
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?