Rails+MySQLでのDB設計のポイント

はじめに

弊社サービスを導入いただいているお客様の中には、月あたり300万件以上の注文データを扱われている企業様もございます。
このような大規模なデータを扱う環境下では、お客様に快適にご利用いただくために、DBのパフォーマンスや保守性を維持することが不可欠です。

そこで本記事では、株式会社Samuraiが行っているDB設計をする上で意識しているポイントをご紹介します。

1. DBレベルでの制約

DBレベルで制約を設けることによって、予期しないデータの登録や更新を防ぐことができます。
本記事では代表的な3つの制約とRailsでの実装方法をご紹介します。

NOT NULL制約

DB設計の方針には、「可能な限りNULLを許容しない」というものがあります。
これはSQLで扱う上でNULLがあると、さまざまな問題が発生するためです。
そこでNOT NULL制約を設定することで、NULLで登録更新しようとするとエラーを発生させることができます。

Railsでは、null: false を設定することでNOT NULL制約を設定することができます。

class CreateUsers < ActiveRecord::Migration[7.2]
  def change
    create_table :users do |t|
      t.string :name, null: false
      t.timestamps
    end
  end
end

app(dev)> User.create!(name: nil)
  TRANSACTION (0.4ms)  BEGIN
  User Create (3.2ms)  INSERT INTO `users` (`name`, `created_at`, `updated_at`) VALUES (NULL, '2025-03-17 12:38:18.935595', '2025-03-17 12:38:18.935595')
  TRANSACTION (0.5ms)  ROLLBACK
(app):1:in `<main>': Mysql2::Error: Column 'name' cannot be null (ActiveRecord::NotNullViolation)

一意制約

一意制約は、同じ値が重複して登録されることを防ぐための制約です。
主キーとは異なり、複数の列に設定することができます。

Railsでは、unique: true を設定することで一意制約を設定することができます。
(インデックスのオプションとして設定します。)

class AddIndexToUsers < ActiveRecord::Migration[7.2]
  def change
    add_index :users, :name, unique: true
  end
end

app(dev)> User.create!(name: "test")
  TRANSACTION (0.8ms)  BEGIN
  User Create (6.0ms)  INSERT INTO `users` (`name`, `created_at`, `updated_at`) VALUES ('test', '2025-03-17 12:40:48.073375', '2025-03-17 12:40:48.073375')
  TRANSACTION (9.1ms)  ROLLBACK
(app):3:in `<main>': Mysql2::Error: Duplicate entry 'test' for key 'users.index_users_on_name' (ActiveRecord::RecordNotUnique)

CHECK制約

CHECK制約は、特定の列の値が特定の条件を満たすことを保証するための制約です。
例えば、「10~30までの整数」や「りんご、バナナ、メロンのいずれかの文字列」などです。

Railsでは、check_constraint を設定することでCHECK制約を設定することができます。

class CreateUsers < ActiveRecord::Migration[7.2]
  def change
    create_table :users do |t|
      t.integer :age, null: false
      t.check_constraint 'age >= 10 AND age <= 30', name: 'age_range_check'
      t.timestamps
    end
  end
end

app(dev)> User.create!(age: 50)
  TRANSACTION (0.4ms)  BEGIN
  User Create (3.6ms)  INSERT INTO `users` (`age`, `created_at`, `updated_at`) VALUES (50, '2025-03-17 12:55:57.361982', '2025-03-17 12:55:57.361982')
  TRANSACTION (0.5ms)  ROLLBACK
(app):1:in `<main>': Mysql2::Error: Check constraint 'age_range_check' is violated. (ActiveRecord::StatementInvalid)

2. 正規化

正規化は、データの冗長性を排除し、データの整合性を保つための手法です。
同じデータを複数のテーブルに持たないため、更新時の整合性を保ちやすくなります。
正規化は第1~第5正規化まで存在しますが、パフォーマンスとのトレードオフの関係から第3正規化までが一般的です。
詳細な正規化の方法は様々な記事でも紹介されておりますので割愛致します。

3. インデックス

正規化によって分割されたテーブルからデータを取得するには、テーブル同士の結合という高コストな処理が必要であり、処理時間の悪化が懸念されます。

そこで、効果を発揮するのがインデックスです。
インデックスは下記の条件で効果を発揮します。

• テーブルのデータが多い（10万件以上が目安）
• カーディナリティが高いカラム
• SQLでWHERE句で使用されているカラム

本記事では、Railsでのインデックスの設定方法と確認方法をご紹介します。

設定方法

Railsでは、add_indexメソッドを使用することでインデックスを設定することができます。

class AddIndexToUsers < ActiveRecord::Migration[7.2]
  def change
    add_index :users, :name
  end
end

確認方法

MySQLでは、EXPLAINを使用することでインデックスの効果を確認することができます。（実行計画）
下記例では、10万件レコードを登録したテーブルで、nameカラムを検索した結果です。
特に注目するべき点は下記の3点です。

• type
  • ALL はフルスキャンを表しており、ref はインデックスを使用していることを表します。
• rows
  • フルスキャン時（97682行）と比べて、スキャンされる行数が約半分（48841行）に削減されています。
• Extra
  • Using where がなくなり、フィルタリングなしでインデックスを使用した検索ができています。

mysql> EXPLAIN SELECT * FROM users WHERE name = 'User000001';
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | users | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 97682 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

mysql> EXPLAIN SELECT * FROM users WHERE name = 'User000001';
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys       | key                 | key_len | ref   | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | users | NULL       | ref  | index_users_on_name | index_users_on_name | 4       | const | 48841 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------------+---------------------+---------+-------+-------+----------+-------+
1 row in set, 2 warnings (0.00 sec)

MySQL 8.0.18 では、EXPLAIN ANALYZE が導入されています。
EXPLAIN ANALYZE は EXPLAIN と異なり、実際にクエリを実行しながら実行計画を測定し、各ステップの実行時間や実際に処理された行数を表示します。
特に注目するべき点は下記2点です。

• cost
  • MySQL オプティマイザの見積もり値であり、数値が大きいほど処理に時間がかかる可能性が高いことを表します。
• actual time
  • クエリを実際に実行した際に測定された処理時間で、「(最初の行が返されるまでの時間)...(クエリ全体が完了するまでの時間)」で表されています。

mysql> EXPLAIN ANALYZE SELECT * FROM users WHERE name = 'User000001';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                                                                                      |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (users.`name` = 'User000001')  (cost=9696 rows=9608) (actual time=0.0956..42.2 rows=1 loops=1)
    -> Table scan on users  (cost=9696 rows=96077) (actual time=0.0892..29.5 rows=100000 loops=1)
 |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)

mysql> EXPLAIN ANALYZE SELECT * FROM users WHERE name = 'User000001';
+-----------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN                                                                                                                                 |
+-----------------------------------------------------------------------------------------------------------------------------------------+
| -> Index lookup on users using index_users_on_name (name='User000001')  (cost=0.35 rows=1) (actual time=0.0557..0.0604 rows=1 loops=1)
 |
+-----------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

まとめ

本記事では、DBレベルでの制約と正規化、インデックスの設定方法と確認方法をご紹介しました。
また、DBクライアントの進化により日々新しい機能が追加されています。
私たちも常に最新の技術や手法をキャッチアップし続けていきます！

株式会社Samuraiでは、オムニチャネルを管理するためのパッケージシステムAirbeyondをサービス提供しております。
商品情報や注文情報、会員情報などを一元管理することができ、モバイルオーダーを利用する飲食店様やECサイトを運営している企業様にご利用いただいております。
詳しくはこちらをご覧ください。

https://www.samurai-net.co.jp/samurai_order/
https://www.samurai-net.co.jp/product