遅くならないSQLの書き方

社内勉強会の資料です。
SQLが遅くならないための方法の一つとして、インデックスに重点を置いて説明した資料になります。

Transcript

1. 遅くならないSQLの書き方 株式会社福岡情報ビジネスセンター OT勉強会資料
2. アジェンダ • 検索が速いSQLの条件 • インデックスについて • 遅いSQL
3. 検索が速いSQLの条件
4. 検索が速いSQLの条件 • テーブル内のレコード件数が少ない • サーバの性能が十分である • 全検索とならない検索ができている →インデックスが効いた検索ができている
5. インデックスについて
6. インデックスとは インデックスは（索引）は、データベースの性能を向上させる方法の一つです。 データの格納位置を特定し、その位置に直接アクセスする事で、検索速度を上げることができます。 インデックスが設定されていない場合の検索では、テーブルの最初から最後まで探すため時間がかかります。 利用料金 利用者 利用日 ID 100 A 2022/01/01 1 200 B 2022/01/01 2 150 A 2022/01/02 3 300 A 2022/01/03 4 200 A 2022/12/31 99997 100 B 2022/12/31 99998 400 A 2023/01/01 99999 350 B 2023/01/01 100000 10万件のレコードがあるテーブルで、利用日、利用者で検索を行った場合 SELECT * FROM table WHERE 利用日 = ‘2022/12/31’ AND 利用者 =‘A’ インデックスがなければ1件目から10万件目までを全検索して該当データ を特定して抽出します。時間がかかります。 利用日と利用者でインデックスが作成されている場合は、該当データに 絞った抽出が可能です。速いです。 インデックスがあれば 範囲を絞った検索ができる インデックスがないと 最初から最後まで検索する
7. 利用料金 利用者 利用日 ID 100 A 2022/01/01 1 200 B 2022/01/01 2 150 A 2022/01/02 3 300 A 2022/01/03 4 250 A 2022/12/31 99997 100 B 2022/12/31 99998 400 A 2023/01/01 99999 350 B 2023/01/01 100000 インデックスが効いている検索 検索条件に対して有効なインデックスでないと意味がありません。 インデックスに沿った検索条件の場合は威力を発揮しますが、検索条件と無関係、またはインデックスが効かない検索条件の場合は全検索となります。 利用日と利用者でインデックスが作成されている場合 ①利用日と利用者で検索 SELECT * FROM table WHERE 利用日 = ‘2022/12/31’ AND 利用者 =‘A’ →インデックスが効く ②利用料金で検索(インデックスに含まれていない) SELECT * FROM table WHERE 利用料金 = ‘250’ →インデックスが効かない インデックスが無効の場合は 最初から最後まで検索する インデックスが有効ならば 範囲を絞った検索ができる
8. 利用料金 利用者 利用日 ID 100 A 2022/01/01 1 200 B 2022/01/01 2 150 A 2022/01/02 3 300 A 2022/01/03 4 200 A 2022/12/31 99997 100 B 2022/12/31 99998 400 A 2023/01/01 99999 350 B 2023/01/01 100000 インデックスが効いている検索 インデックスは1つの列で作る場合と、複数の列で作る場合があります。 複数列でインデックスを作成する場合は、順番の指定が命となります。 インデックスの順番に沿った検索条件である必要があります。 利用日→利用者の順でインデックスが作成されている場合 ①日付と利用者で検索 SELECT * FROM table WHERE 利用日 = ‘2022/12/31’ AND 利用者 =‘A’ →インデックスが効く ②利用日で検索 SELECT * FROM table WHERE 利用日 = ‘2022/12/31’ →インデックスが効く ③利用者で検索 SELECT * FROM table WHERE 利用者 =‘A’ →インデックスが効かない
9. 複数列でのインデックスの効果範囲 複数列でインデックスを作成した場合 インデックスの順番が 列A→列B→列C の場合 ① 条件 A、B、C で検索 WHERE A = 1 AND B = 1-1 AND C = 1-1-1 →インデックスが効く ダイレクトに1件まで絞れる ② 条件 A、B で検索 WHERE A = 1 AND B = 1-1 →インデックスが効く ダイレクトに4件まで絞れる ③ 条件 A、B、Z で検索 WHERE A = 1 AND B = 1-1 AND Z = Z1 →インデックスが効くが、A→Bまでの範囲で4件までフェッチした結果からZの条件で検索する ④ 条件 A、C で検索 WHERE A = 1 AND C = 1-1-1 →インデックスが効くが、Aの範囲で10件までフェッチした結果からCを検索する ⑤条件 C で検索 WHERE C = 1-1-1 →インデックスは効かない 全件からCを検索する。この場合は先頭がCの別のインデックスが必要 インデックスは最後の1件まで絞れる必要はないと思います。(1件まで絞れるようにインデックスを用意しろと言う人もいます) システムの検索条件に合わせたインデックスが用意されていれば、ある程度の件数まで絞れるインデックスでも十分な効果はあります。 Z列 C列 B列 A列 ID Z1 1-1-1 1-1 1 1 Z2 1-1-2 1-1 1 2 Z3 1-1-3 1-1 1 3 Z4 1-1-4 1-1 1 4 Z5 1-2-1 1-2 1 5 Z6 1-2-2 1-2 1 6 Z7 1-2-3 1-2 1 7 Z8 1-3-1 1-3 1 8 Z9 1-3-2 1-3 1 9 Z10 1-3-3 1-3 1 10 Z11 2-1-1 2-1 2 11 Z12 2-1-2 2-1 2 12 Z13 2-2-1 2-2 2 13
10. その他にインデックスが効かないケース インデックスが効かないケースとしては ・ORの条件句にインデックス有効外のカラムが指定されている SELECT * FROM table WHERE 利用日 = ‘2022/12/31’ OR 利用料金 = 250 ← 利用料金はインデックス化されていない ・LIKE検索、ただし書き方による 先頭一致 LIKE ‘ABC%’ →インデックスが効く、ただし文字ABCの該当範囲までをフェッチして、フェッチした中を全検索 含む LIKE ‘%ABC%’ 、後方一致 LIKE ‘%ABC’ →インデックスが効かない ・カラムで演算、関数を行って検索した場合(ただし右辺での演算はOK) SELECT * FROM table WHERE 利用料金 * 1.1 >= 220 ← 効かない SELECT * FROM table WHERE 利用料金 >= 220 / 1.1 ← 効く SELECT * FROM table WHERE DATE_FORMAT(利用日, ‘%Y%m’) = ‘202201‘ ← 効かない SELECT * FROM table WHERE 利用日 BETWEEN ‘2022/01/01’ AND ‘2022/01/31’ ← 効く ・否定形検索 日付 <> ‘2022/12/31’ ・IS NULL検索、IS NOT NULL検索 (DBによってはインデックスが効かないそうです。MySQLは効くそうです。) TRIM なども注意
11. 「EXPLAIN」を使おう このSQLは遅くなってないかを調べる「EXPLAIN」というコマンドがあります。(IPAの読み方では「イクスプレイン」) SQLの先頭に記述するだけで調査できます。 EXPLAIN SELECT……. (explainの詳細は https://free-engineer.life/mysql-explain/) Extra rows ref key_len key possible_keys type table select_type id 48915 const 4 key_tbl_price_staff key_tbl_price_staff ref tbl_price_index SIMPLE 1 Extra rows ref key_len key possible_keys type table select_type id 100345 ALL tbl_price_noindex SIMPLE 1 ②インデックスが効いている ①インデックスが効いていない ALL、indexになってると注意 検索対象件数が減った
12. 豆知識 ORDER BY にもインデックスが有効 ORDER BY を使うと、検索結果を並び替える「Creating sort index」が発生します。 1000万件から検索して100万件が対象になり、日付順にソートして上位1件だけ持ってくる場合は、 抽出した100万件を日付順にソートしてからTOP1件を持ってくる処理が内部(メモリ上)で発生しています。 日付で昇順にする場合、日付にインデックスがあると処理が速くなります。 ORDER BY 日付, 利用者 とした場合、有効なインデックスは 日付 → 利用者 の順のインデックスのみです。 利用者 → 日付 のインデックスはORDER BYには適用されません。
13. 遅いSQL
14. 利用料金 used_price 社員ID staff_id 利用日 used_date ID 100 1 2022/01/01 1 200 2 2022/01/01 2 150 1 2022/01/02 3 300 1 2022/01/03 4 遅いSQLはインデックスが無い、効いていない 環境に問題がないのにSQLが遅いときは、基本的にインデックスが無い、効いていないことがほとんどです。 以下のテーブルで説明します。 社員氏名 staff_name ID 城島 茂 1 国分 太一 2 松岡 昌宏 3 ⾧瀬 智也 4 社員テーブル tbl_staff 費用テーブル tbl_price 主キー:ID インデックス なし レコード4件 主キー:ID 社員IDで社員テーブルとひも付きます レコード10万件
15. 利用料金 used_price 社員ID staff_id 利用日 used_date ID 100 1 2022/01/01 1 200 2 2022/01/01 2 150 1 2022/01/02 3 300 1 2022/01/03 4 JOINを考慮したインデックスが作成されていない 社員テーブルと費用テーブルは社員IDでひも付きます。 結合条件となるカラムには、インデックスを作った方が速くなります。 SELECT * FROM tbl_staff INNER JOIN tbl_price ON tbl_staff.id = tbl_price.staff_id なお、主キーは自動的にインデックス化されるので、以下の場合は社員テーブルのIDに対するインデックスの指定は不要です。 社員氏名 staff_name ID 城島 茂 1 国分 太一 2 松岡 昌宏 3 ⾧瀬 智也 4 社員テーブル tbl_staff(主) 費用テーブル tbl_price(副) 主キー:ID インデックス なし 主キー:ID インデックス 社員ID
16. JOINを考慮したインデックスが作成されていない 費用テーブル.社員IDのインデックスの有無で、社員テーブルと費用テーブルをJOINしたSQLを実行して比較します。 ALTER TABLE tbl_price ADD INDEX key_tbl_price_staff(staff_id); (今回は、インデックスがある「 tbl_price_index」とインデックスがない「 tbl_price_noindex」のテーブルを用意) explainコマンドを使ってインデックスの利用状況を確認します。 SELECT tbl_staff.staff_name , tbl_p.used_date , tbl_p.used_price FROM tbl_staff INNER JOIN 費用テーブル tbl_p ON tbl_staff.id = tbl_p.staff_id WHERE tbl_staff.id = 1 ①は2行目のtbl_priceのtypeが「ALL」になっていて、全検索になっているのが分かります。 ②はインデックスが効いて、フェッチされた行数が全件検索の10万行から4.8万に減ったのが分かります。 JOINするためにtbl_priceを全件見ていたのが、社員IDで絞り込むことができました。 Extra rows ref key_len key possible_keys type table select_type id 1 const 4 PRIMARY PRIMARY const tbl_staff SIMPLE 1 Using where 100345 ALL tbl_p SIMPLE 1 Extra rows ref key_len key possible_keys type table select_type id 1 const 4 PRIMARY PRIMARY const tbl_staff SIMPLE 1 48915 const 4 key_tbl_price_staff key_tbl_price_staff ref tbl_p SIMPLE 1 ①インデックスなし(tbl_price_noindex) ②インデックスあり(tbl_price_index)
17. サブクエリの使い方が悪い ①サブクエリの中でテーブル全体を集計して結合 SELECT tbl_staff.staff_name , subtable.price_sum FROM tbl_staff JOIN ( SELECT staff_id , SUM(used_price) as price_sum FROM tbl_price_index GROUP BY staff_id ) subtable ON tbl_staff.id = subtable.staff_id WHERE tbl_staff.id = 1 ②サブクエリの中で検索条件を指定して集計したあとに結合 SELECT tbl_staff.staff_name , subtable.price_sum FROM tbl_staff JOIN ( SELECT staff_id , SUM(used_price) as price_sum FROM tbl_price_index WHERE staff_id = 1 GROUP BY staff_id ) subtable ON tbl_staff.id = subtable.staff_id WHERE tbl_staff.id = 1 インデックスが有効でも、サブクエリを使ってJOINする場合、書き方によっては全検索が走ることがあります。 下記は2つとも、費用テーブルの中を社員ごとに集計し、社員ID「1」の結果だけを表示するSQLです。 結果 → サブクエリ price_sum staff_name 61394354950 城島 茂 サブクエリ
18. サブクエリの使い方が悪い 同様に、 explainコマンドを使ってインデックスの利用状況を確認します。 費用テーブル.社員IDのインデックスは「有り」の状態です。 ①サブクエリの中で4人全員の集計を行い、その結果とJOINして1名に絞る動きになります。 3行目のtypeが「index」になっています。一見良さそうな名前に見えますが、これはフルスキャンが走っています。 ②サブクエリの中で社員ID「1」だけを対象として検索、集計を行い、その結果とJOINしました。 インデックスが効いた検索を行ったので、集計対象が9.7万行から4.8万行に減ったのが分かります。 Extra rows ref key_len key possible_keys type table select_type id 1 const 4 PRIMARY PRIMARY const tbl_staff PRIMARY 1 10 const 4 <auto_key0> <auto_key0> ref <derived2> PRIMARY 1 97830 4 key_tbl_price_staff key_tbl_price_staff index tbl_price_index DERIVED 2 Extra rows ref key_len key possible_keys type table select_type id 1 system <derived2> PRIMARY 1 1 const 4 PRIMARY PRIMARY const tbl_staff PRIMARY 1 48915 const 4 key_tbl_price_staff key_tbl_price_staff ref tbl_price_index DERIVED 2 ①サブクエリの中が全検索になっているパターン ②サブクエリの中でも検索条件を指定したパターン 検索対象件数が減った
19. サブクエリの使い方が悪い サブクエリの中の結果の件数が多い場合も速度は低下します。 サブクエリの中が10件に絞られ、主のSQLで1件だけを表示する場合と サブクエリの中が10万件に絞られ、主のSQLで1件だけを表示する場合とでは、処理の負荷具合が違います。 可能な限り、最終的に抽出される対象と同じ条件でサブクエリ内を絞る方が効率的でしょう。
20. サブクエリの使い方が悪い IN句 SELECT tbl_staff. staff_name FROM tbl_staff WHERE tbl_staff.id IN ( SELECT DISTINCT tbl_price_index.staff_id FROM tbl_price WHERE 条件文 ) SELECT tbl_staff. staff_name FROM tbl_staff INNER JOIN ( SELECT DISTINCT staff_id FROM tbl_price_index WHERE 条件文 ) subtable ON tbl_staff.id = subtable.staff_id MySQL限定かもしれませんが、IN句とサブクエリを組み合わせるとインデックスが効かない場合があります。 下記は実経験からの例文ですが、IN句の中にサブクエリを使っていたSQLをJOIN句での結合に変更したところ、 22秒かかっていたSQLが0.2秒に短縮されました。
21. SELECTの中を必要最低限にして、抽出量を減らしましょう。 カラムが30個あるテーブルで SELECT * FROM table としていると、全てのカラムを取り出そうとして負荷がかかります。 使うカラムが数個であれば SELECT カラム1, カラム2 FROM table として、無駄な取得を減らしましょう。 件数を確認するためにCOUNT文を使って SELECT COUNT(*) FROM table としている箇所は SELECT COUNT(1) FROM table と記述することで、速度が向上します。 無駄を減らそう
22. 参考サイト MySQL実行計画（EXPLAIN）の見方 https://free-engineer.life/mysql-explain/ MySQLでORDER BYの項目にインデックスを適用してみる https://yk5656.hatenadiary.org/entry/20140206/1392097362 MySQL でテーブルをロックせずに index を貼る方法いろいろ https://blog.kasei-san.com/entry/2019/03/26/234542#:~:text=InnoDB%E3%81%AE%E5%A0%B4%E5%90%88
23. 追記、質疑応答 インデックスを追加すると、テーブルがロックされる。 →InnoDBの場合 online alter table を使うことでロックは回避できる。 条件はあるがロックやテーブルコピーなしにindexが追加できる。 https://blog.kasei-san.com/entry/2019/03/26/234542#:~:text=InnoDB%E3%81%AE%E5%A0%B4%E5%90%88 インデックスを追加するとDBの書き込みが遅くなるので、全部に貼ればいいというものではない。 が、書き込み速度を懸念してインデックスを作らないより、インデックスを適切に作る方が照会なども含め全体のパフォーマンスは良い。 →https://tomozo6.hatenablog.jp/entry/2024/05/13/130415 MySQLでの検証、インデックス追加で遅くはなるが微々たるものという結果 単一インデックスと複数カラムでのインデックスで速度の違いはあるのか？ →ない https://zenn.dev/jnuank/articles/0da8d4755e69fea30bab