Zenn
📖

達人に学ぶSQL徹底指南書 メモ

2023/01/22に公開
MySQL
SQL
tech

概要

少し前に 達人に学ぶSQL徹底指南書 を読んだが、
振り返りついでに
null　と db の高速化は 実務でもよく課題に上がるので、メモした。

よく使うとこだけメモ

null は値ではない

  • sql は　3値論理を採用しており、 真理値型 true, false, unknown がある。
  • null は値ではないので、=,>,< などの 比較述語 が unknown となる
  • 3値論理演算の優先順位は下記
AND :false > unkown > true
OR  :true > unkown > false
  • それを踏まえて null が存在する カラムに対しての in 句 や case when 句 などでも配慮が必要

張ったインデックスが使われない

  • 索引列に加工をおこなっている
SELECT * FROM table where col * 1.1 > 10;

上記の場合、下記ならOK

SELECT * FROM table where col > 10 / 1.1;

関数を適用しているケースもNG

SELECT * FROM table where SUBSTR(col, 1, 1) = 'a';
  • インデックス列にNullが存在する
    IS NULL や IS NOT NULL を使用するとインデックスが使用されんたかったり、 null が多い列ではインデックス数が利用されなかったりという制限を受けることがある。
SELECT * FROM table where col IS NULL;
  • 否定系を使っている
    <> != NOT INはインデックスを利用できない
SELECT * FROM table where col <> 10;
  • ORを使っている
    col1 と col2　に別々のインデックス、または col1、col2に複合インデックスを張っている場合のいずれも、 orを使って条件を結合するとインデックス流が利用できなくなるか、使えても and に比べると非効率な検索になる。
SELECT * FROM table where col1 > 10 OR col2 = "a";
  • 複合インデックスの場合に、列の順番を間違えてる。
    複合インデックスの場合は、検索の順番が重要みたい。
    col1、col2、col3 に対してこの順番で複合インデックスが張られているとする。
OK
SELECT * FROM table where col1 = 10 AND col2 = 20 AND col3 = 30;
SELECT * FROM table where col1 = 10 AND col2 = 20 AND col3 = 30;
NG
SELECT * FROM table where col1 = 10 AND col3 = 30;
SELECT * FROM table where col2 = 20 AND col3 = 30;
  • 後方一致、または中間一致のLIKE
    LIKE は前方一致のみインデックスが使用される。
OK
SELECT * FROM table where col1 LIKE 'a%';
NG
SELECT * FROM table where col1 LIKE '%a';
SELECT * FROM table where col1 LIKE '%a%';
  • 暗黙の型変換を行なってる
    col が 文字列型だったとする
OK
SELECT * FROM table where col = "10";
NG
SELECT * FROM table where col = 10;

ソートが発生する演算

  • group by 句
  • order by 句
  • 集約関数 (sum, count, avg, max, min)
  • distinct
  • 集合演算子 (union, insteresrct, except)
  • ウィンドウ関数

本では下記対策が紹介されていた。

  • 極値関数(max/min)でインデックスを使う
  • where句で書ける条件はhavingには書かない
  • group by句 と order by 句でインデックスを使う。

当然テーブルの件数にもよる。
検索列に インデックスがある場合は 実表の検索を回避できるので、ソートによる負荷も軽減できる。
件数にもよるが上記を使わずに代用できる sql が書ける場合はそうするべきだと思った。

感想

db の高速化は 実務でもよく課題に上がるので、メモした。
他にも、集合での考え方とか面白かったし、付録にsqlの練習問題が沢山あるので練習できてとても良かった。

インデックスに関して、下記の記事も勉強になる。
https://qiita.com/marnie_ms4/items/576055abc355184c51a1

Discussion