Edited at

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

More than 1 year has passed since last update.


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