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

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

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

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

```bash
rails g scaffold Article title body:text user_id:integer
```

```ruby: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
```

```ruby:app/models/user.rb
class User < ApplicationRecord
  has_many :articles
end
```

```ruby:app/models/article.rb
class Article < ApplicationRecord
  belongs_to :user
end
```

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


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

```irb
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
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を使用する必要があります。

```bash
rails generate migration add_index_articles_user_id
```

```ruby:20171225000101_add_index_articles_user_id.rb
class AddIndexArticlesUserId < ActiveRecord::Migration[5.1]
  def change
    add_index :articles, :user_id
  end
end
```

```irb
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
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に指定しましょう。