🙅🏻‍♂️

SQLアンチパターン覚え書き

2023/09/11に公開

SQL

ActiveRecord

アンチパターン

tech

名前	何がダメか	解決方法	備考
信号無視	1つのカラムにIDを複数格納	中間テーブルを持つ
複数列属性	カラムを連番で増やす	適切なリレーション	xxx2 が出てきたら危険
闇雲インデックス	インデックスを張りまくる	絞る
幻のファイル	カラムにファイルパスを格納	BLOB型の検討
読み取り可能パスワード	パスワードを格納	ハッシュ化
31のフレーバー	限定する値をENUMで定義	別テーブルで管理
ランダムセレクション	order rand()	offset rand(count)
丸め誤差	float	decimal	緯度経度でありがち
SQLインジェクション	where("id = #{params[:id]}")	where(id: params[:id])
疑似キー潔癖症	欠番を埋めたがる	埋めたがらない
貧者のサーチエンジン	like '%foo%'	FULLTEXT等
魔法の豆	モデルに書かない	書く
とりあえずID	ほぼ使わないカラムができる	外部キーをそのままPKにする	解決する必要なし
ポリモルフィック	整合性が揺らぐ・柔軟性がない	使わない	便利な面も多い
外部キー嫌い	foreign_key: false	foreign_key: true	テストではまる要因
メタデータ大増殖	テーブルを連番で増やす	増やさない
曖昧なグループ	group by A で select B	Bが必要な理由を考える
スパゲッティクエリ	複雑すぎるSQL	シンプルにする	設計から見直そう
臭いものに蓋	エラーをないがしろにする	しない
恐怖のUnknown	NULL許可	NOT NULL	代用で -1 とかダメ
素朴な木	連携は parent_id だけ	入れ子集合との併用がよさげ	一長一短ある
暗黙の列	SELECT *	明示指定	正規化した利点はどこへ？
entity-attribute-value	融通が効かない
外交特権	SQL言語だけ特別扱いする	しない	DB管理者ってなに？

信号無視

ActiveRecord::Schema.define do
  create_table :users do |t|
    t.string :friends_ids
  end
end

user = User.create!
user.friends_ids = 8.times.collect { User.create!.id }.join(",")
user.friends_ids  # => "2,3,4,5,6,7,8,9"

さすがにこんなことをしているのは見たことがない。

複数列属性

ActiveRecord::Schema.define do
  create_table :users do |t|
    t.string :avatar1
    t.string :avatar2
  end
end

ありがち。二つぐらいならいいかで放置していると収拾つかなくなる。デメリットしかない。

闇雲インデックス

ActiveRecord::Schema.define do
  create_table :users do |t|
    t.string :name, index: true
    t.timestamps    index: true
    t.index [:name, :created_at, :updated_at]
    t.index [:created_at, :updated_at]
    t.index [:name, :created_at]
    t.index [:name, :updated_at]
  end
end

ActiveRecord::Base.connection.indexes(:users).count  # => 7

インデックスは SELECT が速くなる一方で INSERT が遅くなる。またメモリも消費する。そのバランスを無視してインデックスを作りまくると INSERT 多めのテーブルでは負荷になる。かといってそのちょうどいいバランスが難しかったりする。

幻のファイル

user = User.create!(file_path: "path/to/file.png")
File.exist?(user.file_path)  # => false

ドキュメントが一切ないプロジェクトを引き継いだあと、これで事故ったことあり。勘違いでファイルだけを掃除してしまったり、引っ越しする時にファイルを置き去りにしてきて、不整合に繋がる。

とはいえ、現在の常識では、ファイルは DB に入れるものではないという認識の方が強いと思われる。将来、DBの性能が上がればこの常識も変わるかもしれない。

読み取り可能パスワード

ActiveRecord::Schema.define do
  create_table :users do |t|
    t.string :password
  end
end

user = User.create!(password: "foo")
user.password  # => "foo"

ただ偉いだけの人が権限を持つプロジェクトでありがち。

31のフレーバー

ActiveRecord::Schema.define do
  create_table :users do |t|
    t.column :foo, "ENUM('a', 'b')"
  end
end

User.create!(foo: "a")  # => #<User id: 1, foo: "a">
User.create!(foo: "b")  # => #<User id: 2, foo: "b">

列挙型を使うと負債化する。値が指す情報はもうないからといって拡張性が終わっている機能を使ってはいけない。

そういう意味で言うと ActiveRecord の enum も同様で、マジックナンバーだらけのレガシーなアプリをリファクタリングする過程で列挙型にすることはあっても、最初から列挙型に寄せていくメリットはない。大事に育てたいプロジェクトであれば丁寧に別テーブルとする。

ランダムセレクション

User.order("rand()").take  # => #<User id: 1>

何も考えてないとやってしまいがち。単にランダムに一件欲しいなら次のようにすると O(1) になる。

User.offset(rand(User.count)).take  # => #<User id: 1>

丸め誤差

ActiveRecord::Schema.define do
  create_table :users do |t|
    t.float :a
    t.column :b, :double
    t.column :c, "DECIMAL(65, 30)"
  end
end

v = 5.5555555555555555555555555555555555555
user = User.create!(a: v, b: v, c: v).reload
user.a.to_d  # => 0.555556e1
user.b.to_d  # => 0.5555555555555555e1
user.c.to_d  # => 0.5555555555555555e1

初めて緯度経度を扱ったときこれではまった。

SQLインジェクション

id = "0 or name = 'admin'"
User.where("id = #{id}").to_sql  # => "SELECT `users`.* FROM `users` WHERE (id = 0 or name = 'admin')"
User.where("id = #{id}").take    # => #<User id: 1, name: "admin">

有名だけどこんな風に書いてるのは見たことがない。

疑似キー潔癖症

User.pluck(:id)                                       # => [1, 3]
sql = "SELECT id + 1 FROM users WHERE (id + 1) NOT IN (SELECT id FROM users) LIMIT 1"
id = ActiveRecord::Base.connection.select_value(sql)  # => 2
User.create!(id: id)                                  # => #<User id: 2>

id が整数だったとしてもハッシュと同じ扱いで考えるべきだが、連番の数字に固執してしまう人がまれにいる。

貧者のサーチエンジン

Article.where(["content LIKE ?", "%キーワード%"])

たしかにいけてないが数万件ならこの方法でじゅうぶん。

魔法の豆

user.rb がこれだけ

app/models/user.rb

class User < ActiveRecord::Base
end

書くのはリレーションのみ。いやリレーションもほぼ書かない。一方、コントローラーは特盛。挙げ句、わけのわからないサービスクラスなどを量産し始める。

とりあえずID

ActiveRecord::Schema.define do
  create_table :users do |t|
  end
  create_table :profiles, id: false do |t|
    t.belongs_to :user
  end
end

class User < ActiveRecord::Base
  has_one :profile
end

class Profile < ActiveRecord::Base
  self.primary_key = :user_id
  belongs_to :user
end

user = User.create!                  # => #<User id: 1>
user.create_profile!(:user => user)  # => #<Profile user_id: 1>

すべてのテーブルのプライマリーキーを id で統一しよう、がダメだと言っている。Profile#user_id が一意であれば Profile#id は余計だという考え方だが、そこまでしてカラムを削る必要に迫られたことはない。

ポリモルフィック

ActiveRecord::Schema.define do
  create_table :users do |t|
  end
  create_table :comments do |t|
    t.belongs_to :commentable, polymorphic: true
  end
end

class User < ActiveRecord::Base
  has_many :comments, as: :commentable
end

# あらゆるレコードにコメントできるモデル
class Comment < ActiveRecord::Base
  has_many :comments, as: :commentable # 自分に対してもコメントできるようにするため
  belongs_to :commentable, polymorphic: true
end

user = User.create!              # => #<User id: 1>
comment = user.comments.create!  # => #<Comment id: 1, commentable_type: "User", commentable_id: 1>
comment.comments.create!         # => #<Comment id: 2, commentable_type: "Comment", commentable_id: 1>
comment = user.comments.create!  # => #<Comment id: 3, commentable_type: "User", commentable_id: 1>
comment.comments.create!         # => #<Comment id: 4, commentable_type: "Comment", commentable_id: 3>

id	commentable_type	commentable_id
2	Comment	1
4	Comment	3
1	User	1
3	User	1

汎用的なモデルにすると融通が効かなくなるという指摘で、たしかにそういう面もあるけど共通化できるメリットの方がはるかに大きいとわかってやってるなら問題ない。

外部キー嫌い

ActiveRecord::Schema.define do
  create_table :users do |t|
  end
  create_table :articles do |t|
    t.belongs_to :user, foreign_key: true
  end
end

Article.create!(user_id: 1) rescue $!  # => #<ActiveRecord::InvalidForeignKey: Mysql2::Error: Cannot add or update a child row: a foreign key constraint fails (`__test__`.`articles`, CONSTRAINT `fk_rails_3d31dad1cc` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`))>

ユーザーID: 1 は存在しないのでエラーとしてくれている。このように無効な値が入ることを防ぐために foreign_key: true の指定が推奨されている。この機能は ORM がなかったころには安全性を保証するために役立ったかもしれないが、今どきは ORM を通じて操作するのが一般的なのでありがたみはない。それどころか、スムーズにデータを削除できず、開発時のストレスになることのほうが多い。それでも、やはり foreign_key: true は指定しておく方がいいんだろうなという気はする。

メタデータ大増殖

ダメな例:

ActiveRecord::Schema.define do
  (2000...2100).each do |year|
    create_table "users_#{year}" do |t|
    end
  end
end

User.table_name = :users_2016
User.create!  # => #<User id: 1>

User.table_name = :users_2017
User.create!  # => #<User id: 1>

やるとプロジェクトが終わる。

速度が問題なら次のようにパーティション分割する。ただAR経由でこの処理を書こうとすると大変。

CREATE TABLE users (
 id INTEGER AUTO_INCREMENT NOT NULL,
 created_at DATETIME,
 PRIMARY KEY (id, created_at)
);

ALTER TABLE users PARTITION BY HASH (YEAR(created_at)) PARTITIONS 3;
EXPLAIN PARTITIONS SELECT * FROM users;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	users	p0,p1,p2	index	NULL	PRIMARY	9	NULL	1	100.00	Using index

曖昧なグループ

User.create!(name: "a", score: 1)
User.create!(name: "a", score: 2)
User.create!(name: "b", score: 3)
User.create!(name: "b", score: 4)

SQLite3

sql "SELECT id, AVG(score) FROM users GROUP BY name"  # => [{"id"=>1, "AVG(score)"=>1.5}, {"id"=>3, "AVG(score)"=>3.5}]

同じ名前のレコードが複数あったのになぜ特定の id を取得したのだろう？

MySQL

sql "SELECT id, AVG(score) FROM users GROUP BY name"  # => #<ActiveRecord::StatementInvalid: Mysql2::Error: Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column '__test__.users.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by>

こちらでは曖昧なクエリをエラーとしてくれている。

スパゲッティクエリ

sql <<~EOS  # => [{"user_id"=>1, "count_a"=>2, "count_b"=>2}]
SELECT f1.user_id,
       COUNT(a1.id) AS count_a,
       COUNT(a2.id) AS count_b
FROM favorites f1
LEFT JOIN articles a1 ON (f1.article_id = a1.id AND a1.name = 'a')
LEFT JOIN favorites f2 USING (user_id)
LEFT JOIN articles a2 ON (f2.article_id = a2.id AND a2.name = 'b')
WHERE f1.user_id = 1
GROUP BY f1.user_id
EOS

JOINの数に累乗して行が増えていく。無理に一つにまとめようとすると破綻する。難しいSQLを書けるのはかっこいいことでもないし、分割するのは恥ずかしいことでもない。

臭いものに蓋

User.count rescue 0  # => 0

なんかエラーが出たんでユーザーは 0 人ってことにしとこう。

恐怖の Unknown

NULL が入っているせいで年齢がでないことに気づいたので

user = User.create!
"#{user.age}歳"  # => "歳"

age カラムを必須にした。しかし、未入力の場合もあるので NULL のかわりに -1 を入れるルールとしてみた。

user = User.create!(age: -1)
"#{user.age != -1 ? user.age : "?"}歳"  # => "?歳"

ところが、次に20歳の人を登録すると平均が9歳になってしまった。

User.create!(age: 20)
User.average(:age).to_i  # => 9

これは次のように -1 を除外しなければならなかった。

User.where.not(age: -1).average(:age).to_i  # => 20

そして後日、疑問に思う。-1 って何？

このように「NOT NULL 制約すべきかどうかの判断」と「未入力の扱い方」を誤ると一気に負債を抱える。

素朴な木

名前	方法	良い	ダメ	併用
素朴	parent のみ	美しい	遅い
経路列挙	path に '1/2/3/'		ダサい	○
入れ子集合	子の範囲を保持	美しい	再計算が面倒	◎
閉包	1:* の別テーブル	富豪的	面倒すぎる	△

「素朴な木」は兄妹や親へのアクセスなどがとてもやりやすい。そこで他の方法も利用したいなら「素朴な木」との併用がいい気がする。

暗黙の列

10.times { User.create!(bin: "x" * 1.megabyte) }

Benchmark.ms { User.all.to_a         }  # => 13.596000033430755
Benchmark.ms { User.select(:id).to_a }  # => 0.3570000408217311
Benchmark.ms { User.pluck(:id)       }  # => 0.2530000638216734

SELECT * がダメだと言うことだが実際にベンチマークを取って何秒も速くなるぐらいの効果がある場合のみカラムを絞るべき。そうでないと何のために正規化してあるのかわからない。

Entity Attribute Value

名前	仕組み	利点	欠点	複雑度	対象数(種類)
EAV	テーブル1つ。キーと値でがんばる	列が増えない。検索が楽。	値用のカラム1つを汎用的に使うのに無理がでてくる。融通が効かない	1	多い
STI	結局テーブルは1つ。モデルを活用		無駄なNULLカラムだらけになる。列を増やしたくなくなってくる	2	数えられるぐらい
具象テーブル継承	べたっと分ける	無駄なカラムがでない	跨ぎ検索が辛い	2	数えられるぐらい
クラステーブル継承	「具象テーブル継承」の改良版		複雑	3	数えられるぐらい
半構造化データ	TEXT型カラムを1つもつ	シンプル	検索が難しい	2	多い
半構造化データ(JSON)	JSON型カラムを1つもつ	シンプル	検索できる	2	多い

EAV と STI の堺は曖昧で EAV でも key に応じて STI のように柔軟な処理が行うようにはできる
STI はその方法を、より明確にして対応するクラスに割り当てるようにする
具象テーブル継承は、そもそも、それが嫌だったから、EAV にしたはずなので「元に戻す」のがはたして解決方法になるのか疑問
ActiveRecord::InternalMetadata は典型的な EAV
MySQL 5.7 から JSON 型が使える

外交特権

SQLを実行するにはデータベース管理者さまにお伺いを立て許可を取らねばならない。

以前「SQLを」と発言した瞬間、おまえごときにデータベースを触る権限などないんだ、という主旨でプロジェクトマネージャー風の人から急に叱られたことがある。たぶんそういうプロジェクトのこと。

参照

https://www.amazon.co.jp/dp/4873115892