シンプルなCRUDを題材としたMySQLのインデックス効果の検証

やったこと

RailsでCRUDを実装する練習として、タスクの名前、内容、終了日、ステータスを持つシンプルなタスク管理アプリを作成しました。検索機能を作る過程でデータベースにインデックスを追加し、その効果を検証しました。

テストデータは Faker で生成しています。内容はランダムな文字列になりますが、今回はシンプルなタスク名のみを対象としました。

確認できたこと

検証が長いので先に確認できたことを書きます。

• RailsのActiveRecordのメソッドは裏でSQL文を実行している。
• MySQLではEXPLAINコマンドを使ってインデックスが追加されたか確認できる。後方一致検索（LIKE '%文字列'）の場合、インデックスが効かず、パフォーマンスが改善されない。

環境

データベース: mysql Ver 8.0.39-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))
Rails: 7.1.3.2
Ruby: 3.2.2

検索機能の実装

ransackという gem に頼らずに formオブジェクト を使って実装しました。
検索するだけで複雑な操作はしないため、生のSQL文はほぼ書かずにActiveRecordが持つメソッドだけで実現できます。

db\migrate\20240807001643_create_tasks.rb

class CreateTasks < ActiveRecord::Migration[7.1]
  def change
    create_table :tasks, comment: "タスク" do |t|
      t.string :name, null: false, limit: 100, comment: "タスク名"
      t.text :description, comment: "説明"
      t.datetime :end_at, comment: "終了日"
      t.integer :status, null: false, default: 0, comment: "ステータス"

      t.timestamps
    end
  end
end

app\controllers\tasks_controller.rb

class TasksController < ApplicationController
  def index
    @search_form = TaskSearchForm.new(search_params)
    @tasks = @search_form.search
  end

  def show
    @task = Task.find(params[:id])
  end

  // create, update, deleteは省略

  private

  def search_params
    params.fetch(:task_search_form, {}).permit(:name, :status)
  end

  def task_params
    params.require(:task).permit(:name, :description, :end_at, :status)
  end
end

app\views\tasks\index.html.slim

h1= t('helpers.task.index_title')

= link_to t('helpers.task.new'), new_task_path, class: 'btn btn_primary'

= form_with model: @search_form, url: tasks_path, method: :get, local: true do |form|
  .field
    = form.label :name, 'タイトル'
    = form.text_field :name
  .field
    = form.label :status, 'ステータス'
    = form.select :status, Task.statuses.keys.map { |k| [t("enums.task.status.#{k}"), k] }, include_blank: true, class: 'form-control'
  .actions
    = form.submit '検索', name: 'commit'

- if @tasks.any?
  table
    thead
      tr
        th= t('activerecord.attributes.task.name')
        th= t('activerecord.attributes.task.status')
        th= t('helpers.task.created_at')
        th= t('helpers.task.updated_at')
        th= t('helpers.task.end_at')
    tbody
      - @tasks.each do |task|
        tr
          td = link_to task.name, task
          td = t("enums.task.status.#{task.status}")
          td = l(task.created_at, format: :long)
          td = l(task.updated_at, format: :long)
          td = l(task.end_at, format: :long)
          td = link_to t('helpers.task.edit'), edit_task_path(task), class: 'btn btn-primary'
          td = link_to t('helpers.task.delete'), task, data: { turbo_method: :delete, turbo_confirm: t('helpers.task.confirm_delete', name: task.name) }, class: 'btn btn-danger'
- else
  p = t('helpers.task.no_tasks')

検索対象はタスク名nameとステータスstatusで、タスク名は完全一致ではなく中間一致（LIKE '%文字列%'）で検索できるようにしています。ステータスはenum(未着手todo,着手中doing,完了done)で定義しています。

app\forms\task_search_form.rb

class TaskSearchForm
  include ActiveModel::Model

  attr_accessor :name, :status

  def search
    scope = Task.all
    scope = scope.where("name LIKE ?", "%#{name}%") if name.present?
    scope = scope.where(status: status) if status.present?
    scope
  end
end

部分一致検索は3種類あります。用語の定義は「ゼロからはじめるデータベース操作」に習います。

• 前方一致

  • 前方一致とは、その名のとおり、検索対象の文字列(今回は「ddd」)が、検索対象の文字列の最初に位置しているレコードだけが選択される検索の仕方です。

• 中間一致

  • 中間一致は、検索条件となる文字列(今回は「ddd」)が、検索対象の文字列の「どこか」に含まれていればレコードだけが選択される検索の仕方です。最初でも最後でも、真ん中でもかまいません。

• 後方一致

  • 後方一致は、前方一致の反対です。つまり、検索条件となる文字列(今回は「ddd」)が、検索対象の文字列の最後尾にあるレコードだけが選択対象となる検索の仕方です。

引用：プログラミング学習シリーズSQL ゼロからはじめるデータベース操作 P205

検索機能が実装できたのでインデックスを追加することで検索が早くなるか検証します。

検証

クエリ検索にかかる時間はログで確認します。エディタで直接見るか、tailコマンドを使ってターミナルでログを監視します。

tail -f log/development.log

テスト用に1万件のレコードを作りました。Railsで以下のコマンドを実行するだけです。テストデータはランダムにしたかったのでFakerを使っています。

10000.times do
  Task.create!(
    name: Faker::Lorem.sentence(word_count: 3),
    description: Faker::Lorem.paragraph,
    status: %w[todo doing done].sample,
    end_at: Faker::Date.between(from: 1.year.ago, to: 1.year.from_now)
  )
end

1万件のレコードができたので実際にクエリ検索にかかる時間を見ます。検索ワード、ステータスは適当です。

Started GET "/tasks?task_search_form%5Bname%5D=%E5%AD%A6%E9%99%A2&task_search_form%5Bstatus%5D=todo&commit=%E6%A4%9C%E7%B4%A2" for 127.0.0.1 at 2024-08-18 22:51:08 +0900
Processing by TasksController#index as HTML
  Parameters: {"task_search_form"=>{"name"=>"学院", "status"=>"todo"}, "commit"=>"検索"}
  Rendering layout layouts/application.html.slim
  Rendering tasks/index.html.slim within layouts/application
  Task Exists? (0.3ms)  SELECT 1 AS one FROM `tasks` WHERE (name LIKE '%学院%') AND `tasks`.`status` = 0 LIMIT 1
  ↳ app/views/tasks/index.html.slim:15
  Task Load (4.3ms)  SELECT `tasks`.* FROM `tasks` WHERE (name LIKE '%学院%') AND `tasks`.`status` = 0
  ↳ app/views/tasks/index.html.slim:25
  Rendered tasks/index.html.slim within layouts/application (Duration: 11.3ms | Allocations: 8488)
  Rendered layout layouts/application.html.slim (Duration: 12.7ms | Allocations: 9991)
Completed 200 OK in 14ms (Views: 8.5ms | ActiveRecord: 4.6ms | Allocations: 10369)

今回のクエリ検索に必要なところだけを説明します。
Task Exists? (0.3ms) SELECT 1 AS one FROM `tasks` WHERE (name LIKE '%学院%') AND `tasks`.`status` = 0 LIMIT 1
タスクが存在するかの確認に0.3msかかりました。タスクが1件もないときはタスクがない旨を示すメッセージを表示させたかったので、タスクが存在するか確認しています。

Task Load (4.3ms) SELECT `tasks`.* FROM `tasks` WHERE (name LIKE '%学院%') AND `tasks`.`status` = 0
タスクのロードに4.3msかかりました。タスク名が「学院」、ステータスが未着手(内部では0として扱っています)のタスクが存在しているのは確認済なので、一覧表示のためロードしています。

残りのログはレンダリング関連でクエリ検索とは無関係なので省略。
タスクの存在確認とロードで合計4.6msかかることがわかりました。

インデックスは追加していませんが、比較のためにEXPLAINコマンドで確認します。
EXPLAINコマンドはMySQLのチューニングのために使われるコマンドのことです。
https://nippondanji.blogspot.com/2009/03/mysqlexplain.html

mysql> EXPLAIN SELECT tasks.* FROM tasks WHERE (name LIKE '%学院%') AND tasks.status = 0;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | tasks | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9096 |     1.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

typeがALLになっており、インデックスを使わずにテーブルすべてをスキャンしていることがわかります。

次に、インデックスを追加したら早くなるのか検証します。
インデックスの追加はマイグレーションファイルを作成し、マイグレーションを実行するだけです。

class AddIndexToTasksNameAndStatus < ActiveRecord::Migration[7.1]
  def change
    add_index :tasks, :name
    add_index :tasks, :status
  end
end

インデックスを追加したら検索してログを見ます。

Started GET "/tasks?task_search_form%5Bname%5D=%E5%AD%A6%E9%99%A2&task_search_form%5Bstatus%5D=todo&commit=%E6%A4%9C%E7%B4%A2" for 127.0.0.1 at 2024-08-21 00:29:13 +0900
Processing by TasksController#index as HTML
  Parameters: {"task_search_form"=>{"name"=>"学院", "status"=>"todo"}, "commit"=>"検索"}
  Rendering layout layouts/application.html.slim
  Rendering tasks/index.html.slim within layouts/application
  Task Exists? (0.5ms)  SELECT 1 AS one FROM `tasks` WHERE (name LIKE '%学院%') AND `tasks`.`status` = 0 LIMIT 1
  ↳ app/views/tasks/index.html.slim:15
  Task Load (2.9ms)  SELECT `tasks`.* FROM `tasks` WHERE (name LIKE '%学院%') AND `tasks`.`status` = 0
  ↳ app/views/tasks/index.html.slim:25
  Rendered tasks/index.html.slim within layouts/application (Duration: 10.3ms | Allocations: 8528)
  Rendered layout layouts/application.html.slim (Duration: 11.6ms | Allocations: 10047)
Completed 200 OK in 13ms (Views: 8.6ms | ActiveRecord: 3.3ms | Allocations: 10472)

期待していたほどパフォーマンスが向上していません。このブログの例では40秒から0秒になるほどの効果が見られましたが、今回のケースでは1桁の改善が得られませんでした。

EXPLAINコマンド

mysql> EXPLAIN SELECT tasks.* FROM tasks WHERE (name LIKE '%学院%') AND tasks.status = 0;
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | tasks | NULL       | ref  | index_tasks_on_status | index_tasks_on_status | 4       | const | 3341 |    11.11 | Using where |
+----+-------------+-------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

typeがref、keyがindex_tasks_on_statusになっていることから、statusにインデックスは追加されているもののnameにはインデックスがついていないことがわかりました。

nameにインデックスがつかない原因を調べたところ、後方一致(中間一致も)の検索を行うカラムはインデックスが効かないためでした。
https://zenn.dev/tm35/articles/80179ed377ba76

原理的にはMySQLのインデックスはB+tree形式のため、後方一致の検索はデータ構造の特性を生かせず、全文検索に陥るというものです。(文字だけだとわかりにくいので図を加えて後日記事にします。)

実際に前方一致検索の場合でも試した結果を踏まえて中間一致と比較しました。

## 中間一致
### add_index前
logのActiveRecord(ms): 4.6
EXPLAINのtype: ALL
EXPLAINのkey: NULL

### add_index後
logのActiveRecord(ms): 3.3
EXPLAINのtype: ALL
EXPLAINのkey: index_tasks_on_status

##前方一致
### add_index前
logのActiveRecord(ms): 4.8
EXPLAINのtype: ALL
EXPLAINのkey: NULL

### add_index後
logのActiveRecord(ms): 0.9
EXPLAINのtype: range
EXPLAINのkey: index_tasks_on_name

確かに4.8ms→0.9msと、中間一致に比べて差分が2.6ms増えました。
前方一致でインデックスを追加したときにkeyがindex_task_on_nameだけになっていてstatusについてはインデックス追加は無駄になったのか気になりました。(possible keysには出てくる)
これについては、次回調べようと思います。

mysql> EXPLAIN SELECT tasks.* FROM tasks WHERE (name LIKE '学院%') AND tasks.status = 0;
+----+-------------+-------+------------+-------+-------------------------------------------+---------------------+---------+------+------+----------+------------------------------------+
| id | select_type | table | partitions | type  | possible_keys                             | key                 | key_len | ref  | rows | filtered | Extra
          |
+----+-------------+-------+------------+-------+-------------------------------------------+---------------------+---------+------+------+----------+------------------------------------+
|  1 | SIMPLE      | tasks | NULL       | range | index_tasks_on_name,index_tasks_on_status | index_tasks_on_name | 402     | NULL |   17 |    34.54 | Using index condition; Using where |
+----+-------------+-------+------------+-------+-------------------------------------------+---------------------+---------+------+------+----------+------------------------------------+
1 row in set, 1 warning (0.00 sec)

後方一致、中間一致ではインデックスの効果がないことがわかりました。検索フォームとして修正案としてはいくつかあります。

• インデックスを追加しない
• 後方一致検索を使わず、前方一致検索にする
• Elasticsearchを使う

今回はインデックスの検証がしたかったこともあり、前方一致検索を採用することにしました。(手段と目的が逆転しているような気がしますが…練習なのでヨシ)

NEXT

• B+treeを題材にインデックスの仕組みを図解
• インデックスが複数ある場合のEXPLAINのkeyについて調査

所感

1万件のデータだと実際の業務で扱うデータと比べて桁が少ないため、インデックスの効果が実感しにくいと感じました。ただこれ以上桁を増やすとseedの時間がかかるため今回は1万件にしました。何かよい検証方法があればコメントいただきたいです。

zennで記事を書くのは初めてです。継続して書いていきたいので、技術的な誤りの指摘、励ましの言葉などのコメント歓迎です。