LoginSignup
126
152

More than 3 years have passed since last update.

@lvflwr

実録！SQL アンチパターン

Last updated at Posted at 2020-08-29

はじめに

『SQL アンチパターン』には，リレーショナル・データベースの設計・運用にあたってのよくある失敗例が25個紹介されている．本記事では，5ヶ月間の開発経験の中で，自分が実際に遭遇した事例のうち，3つを紹介する．コード例は，実際の事例を単純化して作成したものである．

環境は以下を想定する．
フレームワーク: Ruby on Rails 6.0
データベース: MySQL 8.0

12章 インデックスショットガン（闇雲インデックス）

エンジニアのインターンを始めてはや2ヶ月が過ぎようとしていた頃，ボスから与えられた指令は，「お前，ちょっとインデックス見てこい」というものだった．これまで複合インデックスの効果的な使用を十分に意識できていなかったからその辺りを重点的に見てほしいとのことだった．MENTOR の原則の出番である．

MENTOR の原則

MENTOR の原則は，データベースのインデックスの管理を効果的に行うための指針である．

  1. Measure（測定）: クエリの実行時間を計測し，スロークエリを特定する
  2. Explain（解析）: QEP レポートを取得し，処理の遅さの原因を明らかにする
  3. Nominate（指名）: 場合によっては解析ツールによる提案を検討する
  4. Test（テスト）: 変更後，再びクエリのプロファイリングを行う
  5. Optimize（最適化）: キャッシュメモリの設定を見直してみる
  6. Rebuild（再構築）: 定期的にインデックスを張り替えて均衡な状態を保つ

実際にやったこと

まず，必要なインデックスがきちんと張られているかを確かめるために，コントローラとモデルで発行されうる全てのクエリに関して，QEP（クエリ実行計画）を精査し，テーブルスキャンが行われているものがないか調べた．その結果，テーブルスキャンが行われているクエリが数件見つかったが，いずれもインデックスとは関係がなく，問題のないクエリであることがわかった．

次に，不要なインデックスが張られていないかを確かめるために，データベースのスキーマを眺めて，冗長なものがないか調べた．その結果，以下のようなテーブルを発見した．

db/schema.rb
# ユーザーがクエストを達成すると，その達成状況(rating)とともにレコードが追加される
create_table "quest_users" do |t|
  t.bigint "quest_id"
  t.bigint "user_id"
  t.integer "rating", null: false
  t.datetime "created_at", default: -> { "CURRENT_TIMESTAMP" }, null: false
  t.datetime "updated_at", default: -> { "CURRENT_TIMESTAMP" }, null: false
  t.index ["rating"], name: "index_quests_on_rating"
  t.index ["rating", "created_at"], name: "index_quests_on_rating_and_created_at"
end

これは，t.index ["rating"] が冗長である．なぜなら，MySQL の複合インデックスは左端のカラムの組のインデックスを兼ねるからである．つまり，INDEX (A_1, ..., A_n) は，INDEX (A_1, ..., A_i) (i = 1, ..., n) の機能を持つということだ．これと同じようなインデックスが合計2つあったため，それらを取り除いた．その後，該当箇所のクエリを走らせてみて，きちんとインデックスが使用されていることを確認した．

「仕様」を理解することの重要性

上で述べたことは，主に，MENTOR の原則の Explain，Test に相当する作業である．実際の開発現場では，Measure，Explain，Test の比重が大きくなるのではないかと思う．さらにいえば，実践的には，あるインデックスが必要かそうでないかを判断する場面では，データベース製品やアプリケーション・フレームワークの仕様に対する理解が重要になってくると思われる．なぜなら，インデックスや QEP，オプティマイザの仕様は，ANSI SQL の規格において定められておらず，各ベンダーが独自に策定し実装しているからだ．

たとえば，MySQL では，該当のインデックスが存在する場合でも，レコード数が十分に少ないときにはテーブルスキャンが行われる．これが，先ほどの事例で問題なしとした，テーブルスキャンを行うクエリである．また，MySQL の B Tree で作成されたインデックスでは，複合インデックスが左端のカラムの組のインデックスの役割を果たし，範囲指定での検索をサポートする．これらの事情は RDBMS の仕様に依存している．さらに，Ruby on Rails において，疑似キーや，外部キーのインデックスが自動で張られるという仕様は，基本的なことではあるが，知らなければ冗長なインデックスを作成してしまうおそれがある．

MySQL におけるインデックス

というわけで，さっそく，公式ドキュメント（MySQL 8.0 Reference Manual）を読み込んで，MySQL のインデックスの仕様に対する理解を深めよう．とりわけ留意すべきと思われる事実をピックアップして紹介する．

  • 使用可能なインデックスの候補が複数存在する場合は，もっとも選択性の高いカラムに対するインデックスを使用する．選択性とは，データ分布の性質を表す概念であり，カラムごとの異なる値の数をテーブルにおけるレコードの総数で割ったものである．
  • 型変換を伴う比較を条件に含む JOIN に対しては，インデックスを使用できない．例えば，数値型の 1 と同等な文字列型の値としては，'1'，' a1'，'0001'，'1.0e1' など無数に考えられるが，これらを文字列に対するインデックスで効率的に調べ上げることはできない．
  • 複合インデックスは，左端のカラムの組のインデックスの機能を持つ．例えば，INDEX (col1, col2) と張られているとき，これは INDEX (col1) が同時に張られていることと等しい．しかし，INDEX (col2) の効果は期待できない．
  • データ構造に B Tree が用いられている場合，インデックスのカラムが，WHERE 句のすべての AND 群において現れていなければ，そのインデックスを使用できない．AND 群とは，WHERE 句の条件式の部分論理式のうち，結合子に AND のみが使われていて，この性質を満たす他の部分論理式の部分論理式とならないもののことである．
  • ストレージエンジンが InnoDB であるとき，主キーに対するインデックス以外のインデックスはすべて，末尾に主キーのカラムを追加した，複合インデックスに自動的に拡張される．ただし，インデックスのキーの数や長さの制約を超えることはできない．設定でこの自動拡張をオフにすることができる．
  • MySQL のインデックスは，主キーに対するそれを除いて，visibility の設定を VISIBLE から INVISIBLE に変えることにより，オプティマイザから使用されなくすることができる．これによって，インデックスを削除することなく，そのインデックスの必要性をテストすることができる．
  • MySQL 8.0 では，InnoDB かつ B Tree という条件の下，降順インデックスを作成できるようになった．昇順インデックスをつくれる場面ではいつでも降順を指定できる．降順ソートを頻繁に用いる場合は，こちらを選択した方が効率がよい．

以上で抜粋して紹介したものはほんの一部にすぎない．先述した通り，インデックスの仕様は各ベンダーによって異なり，それらをすべて理解するのには膨大な学習コストがかかる．さらに，それらの仕様はシステムのバージョンアップに伴って変化していくのが普通である．したがって，何か課題が浮かび上がるたびに，その都度レファレンスを参照することが望ましいと考えられる．

最後に，もっとも基本的で重要なことが述べられている箇所を引用して，本章の結びとしたい．

Although it can be tempting to create an indexes for every possible column used in a query, unnecessary indexes waste space and waste time for MySQL to determine which indexes to use. Indexes also add to the cost of inserts, updates, and deletes because each index must be updated. You must find the right balance to achieve fast queries using the optimal set of indexes.

（from: MySQL 8.0 Reference Manual）

13章 フィア・オブ・ジ・アンノウン（恐怖の unknown）

これはわりと最近の事例である．コード例のコメントにある通り，あるユーザーの未達成のクエストの一覧を取得することが要件だ．ユーザーがクエストを達成すると，quest_users テーブルに該当のレコードが追加されるという仕組みになっている．したがって，その交差テーブルを参照すれば，ユーザーごとのクエストの達成状況を調べることができる．簡単なタスクのはずだった．

app/models/quest.rb
# あるユーザーの未達成のクエストの一覧を取得しようとしている
scope :list_not_achieved_quests, lambda { |scenario_id, user_id|
  distinct.eager_load(quest_users)
          .where(scenario_id: scenario_id)
          .where.not(quest_users: { user_id: user_id })
}

事前にいくら逡巡しても気づかないのに，衆目に晒した途端，ふいに過ちを自覚して恥と後悔に苛まれるのは人の世の常である．CI が緑になってほっとしたのもつかの間，自分のコードが全く仕様を満たしていないことを悟った．このままでは馬鹿だと思われるとの焦りから脳がフル回転するのがわかった．

SELECT
  DISTINCT *
FROM
  `quests`
LEFT OUTER JOIN `quest_users` ON
  `quest_users`.`quest_id` = `quests`.`id`
WHERE
  `quests`.`scenario_id` = SCENARIO_ID
  AND `quest_users`.`user_id` != USER_ID

これが，実際に発行されるクエリである．何がいけないかわかるだろうか？ このクエリでは，もしまだ誰にも達成されていないクエストが存在する場合，そのクエストが一覧に含まれないのである．この場合，該当の quest_users のレコードが存在しないため，結合後のテーブルにおいて，該当レコードの `quest_users`.`user_id` カラムの値は NULL となる．すると，上のクエリの WHERE 句において，`quest_users`.`user_id` != 1 という式の評価値が NULL となり，畢竟，WHERE 句全体の条件式の評価値が NULL となる．SQL は WHERE 句の条件式の評価値が TRUE となるレコードのみを抽出するから，当のレコードはこぼれ落ちてしまうというわけだ．

SQL における NULL の扱い

NULL != 1 は TRUE じゃないのか．答えは否，NULL である．SQL では，TRUE/FALSE の2値論理ではなく，TRUE/FALSE/NULL の3値論理が採用されている．この点をしっかりおさえないと先ほどのような過ちを犯すことになる．

式 真理値 理由
NULL = 0 NULL 欠損値が 0 に等しいかどうかは不明である
NULL = 12345 NULL 欠損値が 12345 に等しいかどうかは不明である
NULL <> 12345 NULL 欠損値が 12345 と等しくないかどうかは不明である
NULL + 12345 NULL 欠損値と 12345 の和は不明である　
NULL || 'string' NULL 欠損値と 'string' の連結は不明である
NULL = NULL NULL ある欠損値とある欠損値が等しいかどうかは不明である
NULL <> NULL NULL 　ある欠損値とある欠損値が等しくないかどうかは不明である
NULL AND TRUE NULL 欠損値の真偽は不明である
NULL AND FALSE FALSE FALSE を含む論理積は恒偽である
NULL OR FALSE NULL 欠損値の真偽は不明である
NULL OR TRUE TRUE TRUE を含む論理和は恒真である
NOT(NULL) NULL 欠損値の真偽は不明である

あるカラムの値が NULL であるかどうかを評価するのに = 演算子を使うことはできない．その代わりに用意されているのが，かの有名な IS NULL 述語である．これは，その値が NULL であるときに TRUE，そうでないときに FALSE を返す．これとは逆に，IS NOT NULL 述語は，NULL でないときに TRUE，それ以外のときに FALSE を返す．次稿ではさっそく，IS NULL 述語が活躍する．

正しいクエリはこれだ！

app/models/quest.rb
# あるユーザーの未達成のクエストの一覧を取得しようとしている
scope :list_not_achieved_quests, lambda { |scenario_id, user_id|
  joins(sanitize_sql_array(['LEFT OUTER JOIN `quest_users` ON `quest_users`.`quest_id` = `quests`.`id` AND `quest_users`.`user_id` = :user_id', { user_id: user_id }])).where(scenario_id: scenario_id, quest_users: { user_id: nil })
}

賢明な読者諸君は下記のクエリをお読みになれば，どのようなロジックでねらいを達成しているかおわかりになるだろう．もし，達成済みのクエストの一覧を取得したければ，Active Record で where(card_users: { user_id: user_id }) のように指定すればよい．だから，いわばその補集合である未達成のクエストの一覧を取得するには，where.not(card_users: { user_id: user_id }) と指定すればよいと考えるのは，思春期を迎える前には誰しもが持っていた，向こう見ずで純粋な，きわめて健康的な発想であるように思われる．しかし，それは2値論理を暗黙に前提してしまっているため，通用しないのだ．このようなとき，IS NULL 述語を用いて条件を指定するのは常套手段である．WHERE hoge IS NULL OR hoge <> piyo のような記法がよく使われる．SQL-99 標準では，これに相当する WHERE hoge IS DISTINCT FROM piyo という省略記法が用意されている．

SELECT
  *
FROM
  `quests`
LEFT OUTER JOIN `quest_users` ON
  `quest_users`.`quest_id` = `quests`.`id`
  AND `quest_users`.`user_id` = USER_ID
WHERE
  `quests`.`scenario_id` = SCENARIO_ID
  AND `quest_users`.`user_id` IS NULL

それにしても，JOIN の際，ON 句に条件式を追加するのに生の SQL 文を書かなければならないのはなんとも不便である．数学的にはただの論理否定，補集合なのに，SQL では事態が複雑となり，アプリケーション・フレームワークのクエリインターフェースのカバー範囲を容易に逸脱してしまうのだ．このことが，次章に見る問題の誘因となる．

20章 SQL インジェクション

ハッキングの花形といえば，SQL インジェクションである（偏見）．下のコード例を見てほしい．美しい脆弱性だ．まるで金額を書かずに小切手を切るように，クエリの文字列に変数をそのまま展開している．これでは何をされても仕方がない．昔，同じミスをしたことがあった．真の愚者は経験にすら学ばないのだ．Brakeman の介護がなければ生きていけない．

app/models/quest.rb
# あるユーザーの未達成のクエストの一覧を取得しようとしている
scope :list_not_achieved_quests, lambda { |scenario_id, user_id|
  joins("LEFT OUTER JOIN `quest_users` ON `quest_users`.`quest_id` = `quests`.`id` AND `quest_users`.`user_id` = #{user_id}").where(scenario_id: scenario_id, quest_users: { user_id: nil })
}

例えば，user_id 変数に '755; DELETE FROM `users` --' という値が格納されていた場合，users テーブルのデータが全て消去される可能性がある．このような SQL インジェクション脆弱性が存在する場合，ユーザーテーブルからクレジットカードの情報を盗み出す，自身のアカウント情報を改変して管理者権限を得る，など様々な悪用が想定できる．考えるだけで恐ろしい．

SQL インジェクションへの対処法

SQL インジェクションのロジックは，文字列と変数を用いてクエリを生成する動的SQLにおいて，変数に値ではなく，コードを格納し，それを実行するというものである．したがって，これを防ぐためのアプローチは，変数に格納されている値をチェックする，クエリの生成を変数の値（パラメータ）から分離する，の2つに大きく分けられる．後者に相当するものが，プリペアドステートメントと呼ばれるものであり，前処理時にプレースホルダー付きのクエリを作成し，実行時にパラメータ値をプレースホルダーに渡す，という形でクエリとパラメータ値の分離を実現している．変数に SQL 文が含まれていても，前処理後にクエリの構文が変更されることはない．前者を実現するための方法は，エスケープ，フィルタリング，ヴァリデーション，マッピングなどいくつか挙げることができる．例えば，エスケープでは，「'」，「\」などの特殊な文字を，「''」，「\\」のように置き換えてクエリの改変を防ぐ．どのような手法をとるにせよ，大事なことは，これらを自力で実装しようとしないということである．手作業では誤りや見落としが混入する可能性がきわめて高い．フレームワークやライブラリにおいて提供されている，信頼性の高いメソッドを使うという姿勢が大切である．

Ruby on Rails のメソッドを使う

実際にわたしが行ったのは，Ruby on Rails の sanitize_sql_array メソッドを用いるという解決策である．これは，文字列と変数を引数として渡すと，変数の値を sanitize して文字列に挿入し，安全な SQL 文を作成する．先ほどのカテゴリでいえば，主にエスケープにあたる．

app/models/quest.rb
# あるユーザーの未達成のクエストの一覧を取得しようとしている
scope :list_not_achieved_quests, lambda { |scenario_id, user_id|
  joins(sanitize_sql_array(['LEFT OUTER JOIN `quest_users` ON `quest_users`.`quest_id` = `quests`.`id` AND `quest_users`.`user_id` = :user_id', { user_id: user_id }])).where(scenario_id: scenario_id, quest_users: { user_id: nil })
}

sanitize_sql_array のソースコードの一部を以下に示しておく．全体像を把握したければ，直接リポジトリを覗いてみることをオススメする．

activerecord/lib/active_record/sanitization.rb
def sanitize_sql_array(ary)
  statement, *values = ary
  if values.first.is_a?(Hash) && /:\w+/.match?(statement)
    replace_named_bind_variables(statement, values.first)
  elsif statement.include?("?")
    replace_bind_variables(statement, values)
  elsif statement.blank?
    statement
  else
    statement % values.collect { |value| connection.quote_string(value.to_s) }
  end
end

(source: GitHub)

おわりに

『SQL アンチパターン』には，この記事で紹介したものの他に，多くの興味深い事例が取り上げられている．開発を始めて数カ月の身ではあるが，意外とそのうち半数以上が見に覚えのあるケースであり，本記事を作成するにあたってその取捨選択に大いに頭を悩ませた．わたしが本書を手にしたのは，いまは亡き師が後学のためにと貸してくださったからである．いまとなっては返す術が無くなってしまった．彼の形見として後生大事に使っていきたい．

親愛なる imtan に捧ぐ．

練習問題

Index
あるテーブルにおいて，INDEX (col1)，INDEX (col2)，INDEX (col3, col4, col5) が張られているとする．次のクエリのうち，インデックスが全く使用されないものをすべて選べ．ただし，環境は MySQL 8.0（B Tree）を想定する．また，Constant は定数を表すメタ変数とする．

/*a*/ SELECT * FROM tbl WHERE col1 = Constant OR col0 = Constant;
/*b*/ SELECT * FROM tbl WHERE col1 = Constant OR col0 = Constant AND col1 = Constant;
/*c*/ SELECT * FROM tbl WHERE col1 = Constant AND col2 = Constant;
/*d*/ SELECT * FROM tbl WHERE col3 = Constant AND col4 = Constant AND col0 = Constant;
/*e*/ SELECT * FROM tbl WHERE col3 = Constant OR col4 = Constant;
/*f*/ SELECT * FROM tbl WHERE col3 = Constant AND col5 = Constant;
/*g*/ SELECT * FROM tbl WHERE col4 = Constant AND col5 = Constant;
/*h*/ SELECT * FROM tbl WHERE col3 = Constant AND col1 = Constant OR col3 = Constant AND col2 = Constant;

Three-Valued Logic
以下の3つの式に関して，その真理値をそれぞれ答えよ．

/*a*/ NULL = NULL OR NULL <> NULL OR 2020 < 2021
/*b*/ NULL || 'foo' = NULL AND 'hoge' <> 'piyo'
/*c*/ NULL IS DISTINCT FROM 334 OR NOT('' || NULL IN ('NULL', '', 'bar'))

SQL Injection
Ruby on Rails の Active Record では，クエリパラメータの文字列から「'」と「\\」をエスケープする，sanitize_sql_* メソッド群が用意されている．もし，「\\」のエスケープを行わないとすると，クエリパラメータとして渡されたときに，意図しない SQL 文を実行されるおそれのあるものはどれか．あてはまるものをすべて選べ．Ruby on Rails 6.0 の仕様に基づいて解答すること．

a. name = "\'; DELETE FROM `engineers` --"
b. name = "\''; DELETE FROM `engineers` --"
c. name = "\\'; DELETE FROM `engineers` --"
d. name = "\\''; DELETE FROM `engineers` --"
e. name = "\\\'; DELETE FROM `engineers` --"
f. name = "\\\''; DELETE FROM `engineers` --"
126
152
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
126
152