More than 5 years have passed since last update.

user_id:integer is evil (user_id:integerは悪である)

Last updated at 2017-12-24Posted at 2017-12-24

user_id:integer is evil (user_id:integerは悪である)

私は今まで何度もuser_id:integerを使っていましたが、とても後悔しています。

Article modelでuser_id:integerを使ったとします。

rails g scaffold Article title body:text user_id:integer

db/migrate/20171225000001_create_articles.rb

class Create Articles < ActiveRecord::Migration[5.1]
  def change
    create_table :hoges, id: false do |t|
      t.string :title
      t.text :body
      t.integer :user_id

      t.timestamps
    end
  end
end

app/models/user.rb

class User < ApplicationRecord
  has_many :articles
end

app/models/article.rb

class Article < ApplicationRecord
  belongs_to :user
end

user_id:integerは私の予想外の動作をしました。

1. user_id:integerはRDBにindexを作成しません。例えば、user.articlesを取得する時

irb(main):001:0> User.create
   (1.1ms)  SET NAMES utf8,  @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'),  @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
   (0.8ms)  BEGIN
  User Create (1.0ms)  INSERT INTO `users` (`created_at`, `updated_at`) VALUES ('2017-12-23 16:23:32', '2017-12-23 16:23:32')
   (2.6ms)  COMMIT
=> #<User id: 1, name: nil, created_at: "2017-12-23 16:23:32", updated_at: "2017-12-23 16:23:32">

irb(main):002:0> User.create
   (1.3ms)  BEGIN
  User Create (1.0ms)  INSERT INTO `users` (`created_at`, `updated_at`) VALUES ('2017-12-23 16:24:14', '2017-12-23 16:24:14')
   (1.6ms)  COMMIT
=> #<User id: 2, name: nil, created_at: "2017-12-23 16:24:14", updated_at: "2017-12-23 16:24:14">

irb(main):003:0> 10000.times do |i|
irb(main):004:1* User.first.articles.create
irb(main):005:1> end
  User Load (1.4ms)  SELECT  `users`.* FROM `users` ORDER BY `users`.`id` DESC LIMIT 1
   (1.3ms)  BEGIN
  Article Create (1.2ms)  INSERT INTO `articles` (`user_id`, `created_at`, `updated_at`) VALUES (1, '2017-12-23 16:26:37', '2017-12-23 16:26:37')
   (2.5ms)  COMMIT
...................................................................
=> 10000

irb(main):006:0> User.last.articles.create
  User Load (1.4ms)  SELECT  `users`.* FROM `users` ORDER BY `users`.`id` DESC LIMIT 1
   (1.3ms)  BEGIN
  Article Create (1.2ms)  INSERT INTO `articles` (`user_id`, `created_at`, `updated_at`) VALUES (5, '2017-12-23 16:26:37', '2017-12-23 16:26:37')
   (2.5ms)  COMMIT
=> 1

クエリが実際にどのような計画で実行されるのかは次のようにすると分かります。

irb(main):001:0> User.first.articles.explain
   (1.0ms)  SET NAMES utf8,  @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'),  @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
  User Load (1.5ms)  SELECT  `users`.* FROM `users` ORDER BY `users`.`id` ASC LIMIT 1
  Article Load (48.6ms)  SELECT `articles`.* FROM `articles` WHERE `articles`.`user_id` = 1
=> EXPLAIN for: SELECT `articles`.* FROM `articles` WHERE `articles`.`user_id` = 1
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | articles | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9705 |     10.0 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.00 sec)

irb(main):002:0> User.last.articles.explain
  User Load (1.4ms)  SELECT  `users`.* FROM `users` ORDER BY `users`.`id` DESC LIMIT 1
  Article Load (7.2ms)  SELECT `articles`.* FROM `articles` WHERE `articles`.`user_id` = 2
=> EXPLAIN for: SELECT `articles`.* FROM `articles` WHERE `articles`.`user_id` = 2
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | articles | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 9705 |     10.0 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set (0.00 sec)

possible_keysとkeyの両方で表示されている通り、indexは存在しません。
indexを明示的に作成するには、add_indexを使用する必要があります。

rails generate migration add_index_articles_user_id

20171225000101_add_index_articles_user_id.rb

class AddIndexArticlesUserId < ActiveRecord::Migration[5.1]
  def change
    add_index :articles, :user_id
  end
end

irb(main):001:0> User.first.articles.explain
   (1.0ms)  SET NAMES utf8,  @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'),  @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
  User Load (1.7ms)  SELECT  `users`.* FROM `users` ORDER BY `users`.`id` ASC LIMIT 1
  Article Load (48.0ms)  SELECT `articles`.* FROM `articles` WHERE `articles`.`user_id` = 1
=> EXPLAIN for: SELECT `articles`.* FROM `articles` WHERE `articles`.`user_id` = 1
+----+-------------+----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys             | key                       | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | articles | NULL       | ref  | index_articles_on_user_id | index_articles_on_user_id | 5       | const | 4969 |    100.0 | NULL  |
+----+-------------+----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
1 row in set (0.00 sec)

irb(main):002:0> User.last.articles.explain
  User Load (1.3ms)  SELECT  `users`.* FROM `users` ORDER BY `users`.`id` DESC LIMIT 1
  Article Load (1.5ms)  SELECT `articles`.* FROM `articles` WHERE `articles`.`user_id` = 2
=> EXPLAIN for: SELECT `articles`.* FROM `articles` WHERE `articles`.`user_id` = 2
+----+-------------+----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys             | key                       | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | articles | NULL       | ref  | index_articles_on_user_id | index_articles_on_user_id | 5       | const |    1 |    100.0 | NULL  |
+----+-------------+----------+------------+------+---------------------------+---------------------------+---------+-------+------+----------+-------+
1 row in set (0.00 sec)

2. user_id:integerはdestroy時の挙動に影響します。例えばarticlesを持ったuserをdestroyする時

irb(main):001:0> user = User.last
   (0.9ms)  SET NAMES utf8,  @@SESSION.sql_mode = CONCAT(CONCAT(@@sql_mode, ',STRICT_ALL_TABLES'), ',NO_AUTO_VALUE_ON_ZERO'),  @@SESSION.sql_auto_is_null = 0, @@SESSION.wait_timeout = 2147483
  User Load (1.6ms)  SELECT  `users`.* FROM `users` ORDER BY `users`.`id` DESC LIMIT 1
=> #<User id: 2, name: nil, created_at: "2017-12-23 15:36:36", updated_at: "2017-12-23 15:36:36">
irb(main):002:0> user_id = user.id
=> 2
irb(main):003:0> user.destroy
   (1.6ms)  BEGIN
  User Destroy (1.1ms)  DELETE FROM `users` WHERE `users`.`id` = 2
   (5.8ms)  COMMIT
=> #<User id: 2, name: nil, created_at: "2017-12-23 15:36:36", updated_at: "2017-12-23 15:36:36">
irb(main):004:0> Article.find_by_user_id(user_id)
  Article Load (7.5ms)  SELECT  `articles`.* FROM `articles` WHERE `articles`.`user_id` = 2 LIMIT 1
=> #<Article id: 10001, title: "1", body: "Article 1", user_id: 2, created_at: "2017-12-23 15:37:12", updated_at: "2017-12-23 15:37:12">
irb(main):005:0> Article.find_by_user_id(user_id).user
  Article Load (6.1ms)  SELECT  `articles`.* FROM `articles` WHERE `articles`.`user_id` = 2 LIMIT 1
  User Load (1.1ms)  SELECT  `users`.* FROM `users` WHERE `users`.`id` = 2 LIMIT 1
=> nil

ほとんどのエンジニアはこの点を把握しておらず、user_id:integerは関連するarticleが存在しているuserをdestroyしようとすると、ActiveRecord::StatementInvalidがraiseすると思っています。これはuser_id:integerの間違った使い方です。

user_id:integerを使うのをやめましょう。user:refarencesを使い、これがusersテーブルへのFOREIGN KEYである事をRDBに指定しましょう。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

user_id:integer is evil (user_id:integerは悪である)

user_id:integer is evil (user_id:integerは悪である)

1. user_id:integerはRDBにindexを作成しません。 例えば、user.articlesを取得する時

2. user_id:integerはdestroy時の挙動に影響します。例えばarticlesを持ったuserをdestroyする時

1. user_id:integerはRDBにindexを作成しません。例えば、user.articlesを取得する時