狠狠撸

PostgreSQLで学ぶ
データベース技術
SQL開発入門
SELECTとパフォーマンス
第18回中国地方DB勉強会 2016.11.26
NPO法人日本PostgreSQLユーザ会
株式会社アシスト喜田紘介

SQL開発入門－ SELECTとパフォーマンス－
注：ここでは、情報系の学生、社会人数年目、趣味でアプリ開発を
している方などで「SQLは必要に応じて書いたことはあるけど
得意分野ではない」という方を想定しています。
SQLの構文や出来ることは一通り知っている前提とします。

?最も利用頻度の高いSELECT文に絞って、使い方を解説
します。
?初心者のうちから身に着けてほしい、パフォーマンス
まで意識したSQLの書き方を学習しましょう。
学習すること
? SELECT文の書き方
? 索引（INDEX）と実行計画
? 結合（JOIN）の考え方
目次

?最も利用頻度の高いSELECT文に絞って、使い方を解説
します。
?初心者のうちから身に着けてほしい、パフォーマンス
まで意識したSQLの書き方を学習しましょう。
学習すること
目次
この資料は、
http://kkida-galaxy.blogspot.jp/2016/11/chugokudb18.html
で公開しています。
「kkida-galaxy」でググってください
また、この内容は、
Web業界で働くためのオンライン動画学習サービス Schooで実施した、
「PostgreSQLで学ぶデータベース技術」の一部を再編したものです。
https://schoo.jp/class/3529

?表と行と列
?取り出したい結果を考えて、表示する列を絞る！
?行の絞り込み（WHERE）と結合（JOIN ON）
目次

表と行と列
?表に格納されたデータ ?列
?索引の有無は？
?データに偏りや規則性は？
?集計に使う列？
?行
?WHERE句で絞られる
?その条件で何件ヒット？
クエリで取り出す対象をイメージしておこう
? どんなデータが
? どれだけ（何件）入ってる？
? そのうちのどれだけ必要と
してる？
? どの表と結合する？

取り出したい結果から考える
?テーブルから１件の結果を取り出す
?とあるサービスの利用者が自身のプロフィールを見る画面
?取り出したい結果は？
?既に答えは出ている！
SELECT文の書き方１
ユーザーID xxxx のプロフィール
ID、表示名、登録日
/* m_user表の定義 */
----------- -------------
u_id integer
u_name text
u_lastname text
u_firstname text
u_state integer
u_tel text
u_regdate timestamp
index : m_user_pk (u_id)
SELECT
u_id,u_name,u_regdate
FROM m_user
WHERE u_id = XXXXX;

?とあるサービスの利用者が自身の投稿一覧を見る画面
?結果を考えた後、結合の条件を
考える
SELECT文の書き方２結合の時も考え方は同じ
ユーザーID xxxx の投稿一覧
表示名、記事タイトル
/* article表の定義 */
----------- -------------
a_id integer
userid integer
a_title text
a_regdate timestamp
a_startdate timestamp
a_enddate timestamp
SELECT
u.u_name,a.a_title
FROM article a
INNER JOIN m_user u
ON u.u_id = a.userid
WHERE u.u_id = XXXXX;

?テーブルから複数件の結果を取り出す
?とあるサービスの管理者が
①アクティブなユーザを ②登録日が古い順に表示
?これもSQLは簡単
SELECT文の書き方３
ステータス active のプロフィール
ID、登録日を昇順にソート
SELECT
u_id,u_regdate
FROM m_user
WHERE u_state = active
ORDER BY u_regdate ASC;
? ステータスは条件であって
取得したい結果ではない
? 取り出したい結果を意識すれば
SQLは簡単に書ける
? 次はパフォーマンスを考えましょう

?まずは索引の有無を意識
?SQLとテーブル定義
?問題提起
?利用者が１万人いて、そのうち95％の人はアクティブ
?登録者（≒利用者）が100万人いて、そのうち1万人が
アクティブな利用者
SELECT文の書き方３ → 実は良しとは言えないケース
SELECT
u_id,u_regdate
FROM m_user
----------- -------------
u_id integer
u_name text
u_lastname text
u_firstname text
u_state integer
u_tel text
u_regdate timestamp

?RDBのパフォーマンスの要
?索引の動き
?ケーススタディデータの偏りを考える
?【ハイレベル】実行計画を見てみよう
目次

索引（INDEX）
?必要なデータ “だけ” をどうやって取り出すかを考える
?列で絞る → 取り出したい結果を考える
?行で絞る
?適切なインデックスを作成する
?そのインデックスが使われるようにクエリを書く
（参考）ディスクI/O削減には「行で絞る」
RDBのパフォーマンスの要
データは「ブロック」単位でディスクに
読み書きされる。
1block
||
8KB
平均200byteの行なら
約40行分を格納
ブロックの中には“行ごとに”データを
持っているため、ある1列だけ取り出す
場合もI/Oの量は増加してしまう
欲しい列行全体を取得 I/Oは増加

索引の動き
?索引の構造
先に索引にアクセスして、必要なブロックだけを取得
WHERE user_id = 4000
~5000 5001~
~2500~5000 ~7500 ~10000
索引ブロックヘッダ
user_id = 2501
(ブロックID,行ID)=(100,1)
user_id = 2502
user_id = 4000
：
user_id = 4001
user_id = 5000
：
200番
ブロック
199番
ブロック
201番
ブロック
表データ
10
9

索引の動き
?索引を利用するコスト
?表の1ブロックを利用す
るために最低4ブロック
?ランダムI/O
?テーブルを直接読むコスト
?シーケンシャルI/O
1回で全行を取得
?300ブロックの表なら
当然300ブロック取得
? 本例では、索引を使って75ブロックを取得
するコストで10000行を取得する計算
? 1ブロックあたり20行が格納されていると
すると、1500行までは索引を使ってアクセ
スしても良い →テーブルの行数の約15％
索引は万能ではない
~5000
200番
ブロック
199番
ブロック
201番
ブロック
10
9
200番199番 201番1番 300番

再掲
?問題提起
?利用者が１万人いて、そのうち95％の人はアクティブ
?登録者（≒利用者）が100万人いて、そのうち1万人が
アクティブな利用者
SELECT文の書き方３ → 実は良しとは言えないケース
SELECT
u_id,u_regdate
FROM m_user
----------- -------------
u_id integer
u_name text
u_lastname text
u_firstname text
u_state integer
u_tel text
u_regdate timestamp

データの偏りを考える
?インデックスが無いが、
テーブルのほぼ全行を取得したいため、問題なし
1万人中、95%の人がアクティブなstate列
SELECT
u_id,u_regdate
FROM m_user
----------- -------------
u_id integer
u_name text
u_lastname text
u_firstname text
u_state integer
u_tel text
u_regdate timestamp
【ケーススタディ】

データの偏りを考える
?必要な行は全体の1%でしかないが、表全体を取得して
しまうため非効率
?u_state列にCREATE INDEX
?u_state列があまり更新されないなら尚良し
100万人中、1万人程度がアクティブなサービス
SELECT
u_id,u_regdate
FROM m_user
----------- -------------
u_id integer
u_name text
u_lastname text
u_firstname text
u_state integer
u_tel text
u_regdate timestamp

ソートを高速に済ませる
?索引では構造上、ソートされたデータが格納されている
?SQL
?必要なデータを集めたあと、
最後に処理されるソートをスキップできる場合がある
1万行のソートをスキップする索引
SELECT
u_id,u_regdate
FROM m_user
～2015 2016~
~2014~2015 ~2016 ~2017
u_regdate列の索引
ソートされた状態

実行計画を確認する
?様々なアクセス方法のうち、どの方法が選択されたか確認
PostgreSQLはEXPLAIN文で簡単に実行計画を確認可能
【ハイレベル】
postgres=# EXPLAIN
postgres-# SELECT u_id,u_regdate FROM m_user
postgres-# WHERE u_state = 1
postgres-# ORDER BY u_regdate ASC;
QUERY PLAN
--------------------------------------------------------------
Sort (cost=15.88..15.89 rows=2 width=12)
Sort Key: u_regdate
-> Seq Scan on m_user (cost=0.00..15.88 rows=2 width=12)
Filter: (u_state = 1)
EXPLAIN
SELECT
u_id,u_regdate
FROM m_user
postgres=# CREATE INDEX u_idx ON m_user(u_regdate);
/* 上記のクエリを再実行すると実行計画が変化 */
QUERY PLAN
----------------------------------------------------------------------
Index Scan using u_idx on m_user (cost=0.15..52.37 rows=2 width=12)
Filter: (u_state = 1)

?結合の書き方が難しい？
?索引は結合の時こそ効果絶大
?いろいろな結合のアルゴリズム
目次

結合の構文が難しい？
?とあるサービスの利用者が自身の投稿一覧を見る画面
?結果を考えた後、結合の条件を
考える
SELECT文の書き方２結合の時も考え方は同じ
ユーザーID xxxx の投稿一覧
表示名、記事タイトル
----------- -------------
a_id integer
userid integer
a_title text
a_regdate timestamp
a_enddate timestamp
index : article_pk (a_id)
SELECT
u.u_name,a.a_title
FROM article a
INNER JOIN m_user u
ON u.u_id = a.userid
WHERE u.u_id = XXXXX;

内部結合
?内部結合（一般的な結合）はINNER JOINを用いる
Japan PostgreSQL User's Group
両者に共通する列から、値が一致する行を突き合わせる
SELECT
u.u_name,a.a_title
FROM article a
INNER JOIN m_user u
ON u.u_id = a.userid;
u.u_id = a.userid
?m_user表とarticle表の共通列は著者
?すべての記事には著者がいる
?しかし記事を書いていない著者もいる
記事id
1
2
3
著者id
10
10
20
著者id
10
10
20
4 30 30
5 10 10
著者
A
A
B
C
A
article
23

外部結合
?外部結合はLEFT（RIGHT）OUTER JOINを用いる
Japan PostgreSQL User's Group
両者に共通する列から、値が一致する行を突き合わせる
SELECT
u.u_name,a.a_title
FROM article a
RIGHT OUTER JOIN m_user u
u.u_id = a.userid
記事id
1
2
3
著者id
10
10
20
著者id
10
10
20
4 30 30
5 10 10
A
A
B
C
A
著者
40 D
50 E
article
24

索引を利用した結合
?同じ表を繰り返し検索するNested Loop Join
索引は結合の時こそ効果絶大
----------- -------------
a_id integer
userid integer
a_title text
a_regdate timestamp
a_enddate timestamp
index : article_pk (a_id)
userid_idx (userid)
----------- -------------
u_id integer
u_name text
u_lastname text
u_firstname text
u_state integer
u_tel text
u_regdate timestamp
ユーザ数 1万人
記事件数 30万件
（一人当たり平均30件）

?「kida」が書いた記事を検索
?喜田で2人が該当する場合、article表を2回検索する
?30万件×2回＝60万件
?該当する人数が多いと更に検索のコストが膨大になる
m_user表 article表
u_id
0001
0002
0003
0004
0005
0006
0007
0008
0009
0010
0011
a_id userid
xxxx
lsatname
kida
YYYY kida
firstname
kosuke
yusuke
xxxx
xxxx
xxxx
YYYY
YYYY

?article表のuserid列に索引が作られていると、
最少のアクセスで結果が得られる
?30件×2回=60件 ※索引を経由コストを考慮しても圧倒的に有利
u_id
0001
0002
0003
0004
0005
0006
0007
0008
0009
0010
0011
a_id userid
xxxx
lsatname
kida
YYYY kida
firstname
kosuke
yusuke
xxxx
xxxx
xxxx
YYYY
YYYY

いろいろな結合のアルゴリズム
?一度に全件を読み込んで処理するHash Join
?小さい表を全件読み取りHash表を作成
?大きい表の結合列をhash表の値と比較し結合
?両テーブルを一回ずつ全件読み取り
表の件数に差があり、表の大部分が選択される場合
u_id
0001
0002
0003
0004
0005
0006
0007
0008
0009
0010
0011
a_id useridlsatname firstname
hash ：
：
：
：
：

いろいろな結合のアルゴリズム
?全件をソートして上から順に比較するSort Merge Join
?二つの表の結合キーでソート
?ソートした後は、上から順に値を比較して結合
?ソートに用いる索引が作成されていると高速化できる
両方の件数が多く、表の大部分が選択される場合
u_id
0001
0002
0003
0004
0005
0006
0007
0008
0009
0010
0011
a_id useridlsatname firstname

IN句の後のサブクエリ
?IN句の後のサブクエリは結合条件による比較と同じ
結合と同じ考え方だが、複雑な条件を書ける
article
SELECT
u.u_name,a.a_title
FROM article a
INNER JOIN m_user u
u.u_id = a.userid
SELECT
u.u_name,a.a_title
FROM article a
WHERE a_userid IN (SELECT u_id
FROM m_user
WHERE ???）
【参考】

まとめ
?SQLを書く際は、求める結果をはじめに考える
?取得したい列が決まれば、あとは簡単
?どんな表で、どれだけの件数を対象にしているかイメージ
?SQLチューニングでは必ずデータまで気にする
?開発段階から性能への意識が必要
?索引を使ったデータの取得や、結合のアルゴリズム
?どんな時も万能な対策はない
?索引を使うと有利であるか、そうでないかはデータを
知っている人にしかわからない

PGConf.Asia 2016
アジア最大のPostgreSQL国際カンファレンスを日本で
?日時 2016年12月2日（金）、12月3日（土)
?Webサイト http://www.pgconf.asia/JP/
メインセッション
①トップ開発者が語る
PostgreSQL最新情報
②世界最大のB2Bマーケット
プレイス「Alibaba」の事例
多岐にわたるセッションを企画中
?国内/海外事例
?性能や運用の話
?コミュニティ関連
?他DBからの移行
?新機能や拡張の
開発現場から
?初級者向け
チュートリアル

狠狠撸

Chugokudb18_2

Recommended

More Related Content

What's hot (20)

Viewers also liked (16)

Similar to Chugokudb18_2 (20)

Chugokudb18_2