良いSQLと悪いSQL 研修コースに参加してみた
今回参加した研修コースは 良い SQL と悪い SQL です。
もう何年も開催している人気コースです。今回も満員です!
参加してみると、セキュリティでのケア、インデックスの使い方など、実際に SQL を実行しながら体験し、なぜ、それが起こるのか、アーキテクチャから解説いただいたので、体験が伴った理解に繋がりました!!
副問合せなど基礎的な SQL を一通り書いたことがある方にはとてもオススメです!
では、どんな内容だったのか、レポートします!
もくじ
コース情報
想定している受講者 | 自分で SQL を書くことができる |
---|---|
受講目標 | 悪い SQL のどこが悪いか見当がつけられるようになる |
自己紹介
SEカレッジのデータベース分野では 7 割ぐらいの講座で登壇いただいている、おなじみ 林 優子さん です!
Oracle 認定講師として Excellent Instructor を連続受賞した人気トレーナー。
Oracle だけでなく様々な RDBMS に精通し、内部構造もわかりやすく解説
いつもの通り、自己紹介はアッサリ、ただ、受講者の方が何を望んで受講しているのか、使っている RDBMS のアンケートと、事前アンケートでいただいた「パフォーマンスのよい SQL を知りたい」という声を紹介して、丁寧にスタートしました。
良いSQLとは
- メンテナンスのしやすさ
- 自分が一生メンテするのではない(
他人が解読しやすい SQL 文を!)
- 自分が一生メンテするのではない(
- パフォーマンスがよい SQL
悪いSQLとは
- 必要以上のデータを表示する
- パフォーマンス劣化
- 見せたくないデータまで表示する
- 索引を使用せず、全件検索をする
- パフォーマンス劣化になる
- ディスク I/O だけでなく、メモリが非効率な利用
セキュリティ上問題のある悪いSQL
- SQLインジェクション
- WHERE, GROUPBY などクエリの断片をフォームに入力し、データの改竄・削除・入手されてしまうこと
SQLインジェクションを起こすSQL
emp_id= ' OR 'x'='x
これは何を示しているのでしょうか?
実際やってみましょう。
SQL> select * from dept
2 where 1 = 1;
DEPTNO DNAME MGRNO
---------- -------------------- ----------
10 管理部 1007
20 開発部 1001
30 営業部 1028
40 サポート部
SQL> select * from dept
2 where 1 = 0;
レコードが選択されませんでした。
- WHERE句は TRUE / FALSE を見ている
- `emp_id= ‘ OR ‘x’=’x`
- TRUE になっているので、レコードが表示されてしまう
実際やってみると、「おおー、表示されてしまった」というのがリアルに分かって心臓に悪いです w 危機感の醸成、スゴイです。
セキュリティ上安全な良いSQL
- 適切なデータ型の使用
- アプリケーション側は型指定が面倒なので、自動キャストを使っていたりする
- 入力値を適切にエスケープ
- シングルクォーテーション ‘ がクエリと認識されるのがダメ
- SQL シークエンス
- バインド機構を使おう
SQLの共有とバインド機構とは?
アーキテクチャをおさらいしながらバインド機構を説明します
- キャッシュを上手く使う SQL
- 実は大文字小文字を RDBMS がリライトしている
- 無駄に解析させないようにしましょう
- SQL が異なるとクエリのパラメータ毎にキャッシュに Pコード を溜めてしまう
- バインド機構を使って、SQL を共有しましょう
索引(インデックス)の使い方
続いて、ここからはインデックスを実際やってみる演習です。
が、後続する「インデックス設計を見直してみよう」とオーバーラップしますので、ぜひ下の参加してみたレポートをご覧ください!!
複数の表を扱う構文の見直し ~副問合せとJOIN
最後のコーナーは複数の表を組み合わせた場合の SQL です。
RDBMS によって副問合せと JOIN のパフォーマンスが変わる
- departments という部署のテーブルがあり、emp テーブルと DEPARTMENT_ID で結合
- 部署には配属されていない社員がいる
- 社員が配属されている部署を抽出したい
副問合せ
SELECT * FROM departments
WHERE department_id =
(SELECT department_id
FROM employees);
JOIN
SELECT d. * FROM departments d
JOIN employee e
ON d. department_id =
e. department_id;
測定結果
- Cost は同じ
- Bytes が変わった
- 副問合せ: 21
- JOIN: 2544
- 106レコードぐらいしか無いので、それほど負荷にはならない
- が、数万件のレコードを対象とするとメガ、ギガ単位になる
- そうなると仮想メモリに退避して結合というようなことが行われる
RDBMS によって副問合せが得意だけど、JOIN は苦手、逆に副問合せが苦手で、JOIN が得意、というのがあるので、実行計画を比べてみてください。
まとめ
パフィーマンス、メンテナンス、セキュリティの観点からみて良いSQL、悪いSQLを体験して、なぜそれが起こるのか、アーキテクチャから理解しました。
特にこのレポートでは触れませんでしたが、インデックスを色々試してみて、どうするとインデックスが効くのか体感できたのは、とても良かったです。後続する 「インデックス設計を見直してみよう」 に参加すると、より理解を深めてもらえそうです。
基礎的な SQL を一通り書けるようになった DBA の方にはとてもオススメです!!
label SE カレッジの無料見学、資料請求などお問い合わせはこちらから!!
label SEカレッジを詳しく知りたいという方はこちらから !!
IT専門の定額制研修 月額25,000円 ~/ 1社 で IT研修 制度を導入できます。
年間 670 講座をほぼ毎日開催中!!
SEプラスにしかないコンテンツや、研修サービスの運営情報を発信しています。