SQLが重いときに見るお気軽チューニング方法

SQLのチューニング方法

昔Qiitaで書いたものをzennにうつして、若干の修正、追加をしてみました。

ORACLEでの経験を元に書いていますがコストベースのリレーショナルデータべースなら大体共通の考え方だと思うので他にも使えると思います。

SQLのチューニングといえば比較的容易に済むインデックスをとりあえず作成する。といった対応を取られがちですが、数万レコード程度でのデータ量ではあまり効き目がなく（自分の経験則）、どちらかといえば、結合順が大幅に狂ってたりすることが原因のことが多かったりします。よって本当にインデックスがないことが原因なのか？を熟考する必要があります。（例えばID以外のフラグとかコードに単項目indexを貼ってるのもみたことがあります。怖いけど実話）
また、インデックスを作りすぎるとオプティマイザが狂いやすくなって他のSQLにも悪影響を及ぼしたりするので結構熟慮して追加する必要があると思います。

最近のDBはコストベースになるのでオプティマイザによって作成される実行計画が望み通りの計画になっているのかを見ながらチューニングしていく必要があります。
今まで見てきた遅いSQLはとんでもない結合順や使用されるインデックスがなんでここから？といった実行計画になっていた事が大半でした。
そういう時に正しい実行計画をオプティマイザに作ってもらうようにSQLを試行錯誤し変更してチューニングしていく必要があります。絶対的な回答はありませんが下記のようにとれる手段を増やしていくのは有効な手だと思います。

実行計画を知ろう

実行計画をEXPLAIN文などで知ることが大切。これで出される実行計画に結合順やどのキーで結合されたのかなどすべて載っています。開発や遅いSQLを修正するときはとても有用です。
ただし大きな罠があります
それはプログラムではバインド変数を使ってSQLを流しますが、デバッグでは実数を入れることが多々あったり、EXPALINという文字が入ることによってSQLIDが本番と異なっています。実行計画はSQLの形によって発行されるSQLIDごとに作成されるため、プログラムで動いている実行計画とデバッグの実行計画が必ずしも一致するとは限らないことが結構厄介だったりします。
そのため、デバッグで速いのに本番では遅い。といった時はまず使用されている実行計画が違うんだろうと疑う必要もあると思います。
本番でどのような実行計画が流れているか知りたい場合はSQLIDを割り出し、実行計画を出力したりするツールが用意されているのでそれを使って解析するといったフェーズに移ったりします。

何故実行計画が急に狂ったりしてしまうのか？

急にSQLが遅くなった！といったことがしばしば発生すると思います。これは実行計画が特定の定期的なタイミングで変わるからです。このタイミングなどについては非公開となっています。
では急に狂うようなことが起こるのか？

例えば、電車の指定席予約システムを全国版で作成して、空席を検索する機能を作るにあたって路線テーブルと在庫テーブルを結合する必要があるとします。

例えば東京の電車と日付を指定された場合、東京の電車が殆どだと思うので在庫テーブルを日付で絞った後に路線、という結合順でいった方がより絞込りこむ量が減らせそうです。
これが沖縄になると沖縄はモノレールで全国からみると、とても少ない路線数なので路線テーブルを沖縄で絞ってから在庫と結合するのが良いはずです。
こういった殆どは普段は日付から検索にいく方がいいが、実行計画を作られるときに運悪く沖縄って検索されたタイミングで悲劇が起こったりします。
※上記に関しては11g、12cのバージョンでの経験なので最新は変わっているかもしれません
これを防ぐためにindexや適切なテーブル設計、どうしようもない時はヒント句やsqlid指定による実行計画の固定。といった手段をとる必要が出てきます。（個人的にはsqlid指定による実行計画固定はSQLの形が変わると効かなくなる秘伝のだし的なものになるのであまりオススメは出来ないと思います。ヒント句も効いてくれないような場合の最終手段だと思います。）

具体的なチューニング手段

統計情報の最新化

実行計画を作る際の統計情報が古い可能性があります。データの実体と合わない統計情報だと変な実行計画ができかねないので最新の物を作りましょう。
Oracleのデフォルトでは確か自動でとってます。ただ、自動取得のタイミングがデフォルトの時間が夜とかで結構いやなタイミングだったりするのでそこは任意の時間にした方が良さそうです。
また、データのサンプリング数（比率）を変えることも可能です。デフォルトのサンプリング数だと不十分だと判断された場合はこちらも変えましょう。

左辺の関数

条件では列側に関数はかまさない。計算が入ってしまうのでindexが使用されない。

例）Birthdayがdate型（値には日付までしか入れていない）の場合。

遅い

SELECT a.name 
FROM shain a
Where to_char(a.birthday,’YYYY/MM/DD’) = ‘2014/03/21’

速い

SELECT a.name
FROM shain a
Where a.birthday = to_date(‘2014/03/21’ ,’YYYY/MM/DD’) 

そもそもプログラム側でdate型にしておくべき。
更に言えばdate型は秒まで管理しないのであれば、使いにくいので使わない方が良い。

ファンクションインデックス

左辺の関数といった問題でSQLの書き方やプログラム側の改修などでどうしようもない場合の回避策としてファンクションインデックスもあると思います。指摘頂くまですっかり忘れていました。

CREATE INDEX BIRTHDAY_INDEX01
ON shain (to_char(a.birthday,’YYYY/MM/DD’));

ってしておくと左辺の関数の関数で紹介した事例でも

SELECT a.name 
FROM shain a
Where to_char(a.birthday,’YYYY/MM/DD’) = ‘2014/03/21’

インデックスを効かせてくれます。

暗黙の型変換

暗黙の型変換はNG

× SELECT * FROM SomeTable WHERE col_1 = 10;
○ SELECT * FROM SomeTable WHERE col_1 = '10';
○ SELECT * FROM SomeTable WHERE col_1 = CAST(10, AS CHAR(2)); 

※そもそもプログラム側でcharにしておくべき。
最近はバインド変数なのでこういったミスはあまりないと思うが。

in句

IN 述語の引数リストには、最もありそうなキーを左寄せする
※これによって劇的に早くなり、他の手ではどうしようもない場合。
これによってソースの可視性や保守性が落ちるなら他の手を考えるべき

遅い(かもしれない)

SELECT * FROM Address WHERE prefecture IN ('鳥取', '徳島', '東京', '大阪');

速い(かもしれない)

SELECT * FROM Address WHERE prefecture IN ('東京', '大阪', '鳥取', '徳島'); 

indexについて

indexについてはとりあえずはっとけばいいだろうとか言うラフな方をよく見かけますが結構危険です。
indexを貼る時は、細心の注意を払った方が良いです。

とりあえず外部キー全てにindex貼っておくといった事はしないように。
→オプティマイザが誤動作し、変な実行計画を作ってしまう。

よく誤解している人がいるが、
Shainテーブル(bumon_id,sisha_id)とあり、両方にindexが貼られていたとしても
bumon_idをインデックススキャン、絞った後のデータをsisha_idで
インデックススキャンといった事は出来ない。
あくまでもbumon_idのindex range scan後、
結果に対してsisha_idで抽出という事になる。
どちらの列もindexを効かせたければ連キーを貼るしか無い。
ただ、連キーを貼るとsisha_idだけで検索があった場合など、
index skip scanになってしまう。

上記のようにindexの設計は業務をよく知り、練っていく必要がある。

テーブルのパーティーション化、パーティショニング

巨大なデータを格納する必要、または見込まれる場合、データを小分けすることによってパフォーマンスを上げる事ができます。
例えば在庫テーブルや履歴といった日毎に増えていくようなテーブルは日付単位で容易にわける事ができると思うので日付を使ったレンジ・パーティションなどを作成しておくと日付で検索する場合などでとても有用だと思います。
ただ、注意点としてクエリの条件に合わせて加味しながらパーティション設計をしなければなりません。
ddlにも影響するので気軽に出来ないとは思いますが紹介します。実際に運用して助かっている事例も多々ありますので参考になれば。
パーティショニングしたからといって無条件に早くなるわけではなく、SQLの条件にパーティションの条件を入れる必要があります

パーティションの種類

代表的なパーティションの種類をあげます。
個人的には業務的にわかりやすいレンジとハッシュしか使ったことありません。

レンジパーティション

データを期間、範囲で分割する方式です。イメージがつきやすいのが日付を範囲にする方法だと思います。時系列でデータが作成されていくデータなどは有効だと思います。
この場合は例えば１ヶ月単位で区切っていくパーティションなどが多いと思います。

リストパーティション

「任意の値リスト」を元にデータを分割します。
例えばコードマスタに入れるような国、地域、職制など。連続性がないデータの集合かつ、それぞれがある程度の均一性をもっているときに有用だと思います。

ハッシュパーティション

ハッシュ関数を使用し、データを均一に分割します。レンジやリスト化できあに不連続なデータを均一に分割したいときに用います。連続性のない体系の商品番号などで有用だと思います。

コンポジットパーティション

レンジパーティション＋ハッシュパーティションまたはレンジパーティション＋リストパーティションの組み合わせをするパーティションの形式です。レンジの中を更に分けたい場合に使います。
（使ったことないので使用感はわかりません。。。）

インデックスの種類

代表的なパーティションで用いられるインデックスをあげます。
特にパーティションを跨る必要のある場合はグローバルインデックスをつけることをオススメします。
ローカル ＝ 一つ一つのパーティションに合わせてインデックスも分割される
グローバル ＝ パーティションをまたがった全体で一つのインデックスが作成される

ローカル同一キー索引

索引列の左プリフィックスでパーティション化されているローカル索引は、同一キー索引になります。
例えば
出庫日
商品NO
というテーブルがあり、
出庫日でパーティショニングされている場合は

CREATE INDEX インデックス名 ON テーブル名(出庫日) LOCAL;
CREATE INDEX インデックス名 ON テーブル名(出庫日,商品NO) LOCAL;

上記どちらもローカル同一キー索引です。

CREATE INDEX インデックス名 ON テーブル名(商品NO) LOCAL;
CREATE INDEX インデックス名 ON テーブル名(商品NO,出庫日) LOCAL;

上記はローカル同一キー索引とは違います。
なおローカル同一キー索引は、一意にも非一意にもできます。
パフォーマンス上最も有効な索引となります。

ローカル非同一キー索引

索引列の左プリフィックスでパーティション化されていないローカル索引は、非同一キー索引です。
例えば
出庫日
商品NO
というテーブルがあり、
出庫日でパーティショニングされている場合、

CREATE INDEX インデックス名 ON テーブル名(商品NO) LOCAL;

上記はローカル非同一キー索引になります。
また、パーティション化キーが索引キーのサブセットでない場合、一意のローカル非同一キー索引は定義できません。

グローバルパーティション索引

パーティションをまたがった検索をしたい場合に作るインデックスです。

不可視インデックス

オプティマイザから見えないようにする事が出来る。
ある特定のSQLだけはこのindexを使いたいが他のSQLには影響を与えないようにという時に使える。
Oracle MySQLは使える模様だが、Postgresqlは使えないかも

alter index in_res_guest invisible; 

select /* INDEX (res IN_RES_GUEST) */ res_id
from res where guest_id = 101; 

ヒント句

どうしてもオプティマイザが言う事を聞いてくれない！！という時はヒント句を使うと解決する事が多いです。ヒント句を指定する事により実行計画をある程度操作する事ができます。
というか最近のチューニングはSQLの書き方ではどうしようもないことも多く、ほぼヒント句に頼らざるを得ないのかもしれない。

ヒント句一覧

特に有用なヒント句

実行計画が大幅に狂う時は結合順、結合方法が間違っているときが経験上ほとんどでした。それを下記ヒント句でただすことができます。

LEADINGヒント

SELECT /* LEADING(e j) */ * FROM employees e, departments d, job_history j WHERE e.department_id = d.department_id AND e.hire_date = j.start_date; 

USE_NLヒント

SELECT /* USE_NL(l h) */ h.customer_id, l.unit_price * l.quantity FROM orders h, order_items l WHERE l.order_id = h.order_id; 

SPMによる実行計画の固定化

個人的に最終手段だと考えているのがSQL Plan Managementによる実行計画の固定化です。
SQLの形が変わってしまうと固定化が外れてしまうのでSQLのコメントに固定化しているよ！などといった恥ずかしいことを書かないといけない運用ルールが増えたりするので、ヒント句すら効かないようなときの最終手段だと思っています。方法についてはSPM　固定化といったキーワードで環境にあった方法を見ると良いと思います。下記の説明がうまくまとまっています。
https://www.oracle.com/jp/technical-resources/article/cosol-db12cr2/cosol-1.html

その他豆知識

●union より union all
unionは重複行を排除するためのソートが発生するので、重複を許すならunion all

●withを使用すると早くなるとか言う人がいるが大きな間違い。
早くなりません。
※上記のように思ってましたが早くなるケースもあるみたいです。今までいくら書き直しても早くならなかったのですが、間違いだった模様。

●viewも同じく。
マテリアライズドビューなら早くなるが、MVは最終手段だと思っといた方が良い。

●is nullはなるべく使わない。

●not in、!=、<>などはなるべく使わないように。
not exists、orやinで代用できるならする。工夫しても無理なら使ってもよいと思う。
とは言ってもindexが貼られているようなキーでこういった事はしないだろう。

昔は有用だと思われていた者達

今はオプティマイザがかなり賢くなっているため、効果がない模様。（DBの種類やバージョンによっては有用かもしれない。）
ただし、existsやnot existsやinなどその時々において最適なものを選ぶべきだとも思います。
存在しているかなどの条件でinや結合を使うのはどうも好きにはなれません。

exists、not exists

昔はinよりexistsの方が速いなんて話があったが、今はオプティマイザが賢くて同じ模様。
ただ、こういう書き方もできる。もし狂ってたら、こういう書き方で回避できるかもしれないと覚えても良いかもしれない。
NOT INやNOT EXISTSも同じ話。

IN句

SELECT name 
FROM Personnel WHERE birthday 
IN (SELECT birthday FROM Celebrities); 

EXISTS句

SELECT P.name 
FROM Personnel AS P WHERE 
EXISTS (SELECT FROM Clelebrities AS C WHERE P.birthday = C.birthday); 

不要な結合、不要なdistinct

あるテーブルに存在するかどうか、ないかどうかを実現するためだけにjoinをして
結果をdistinctするSQLをみたことがあります。こちらも昔は大変無駄なのでexists、not existsを使用すること。
と言われていましたが、現在ははオプティマイザが賢くて同じ模様。
もし狂ってたら、こういう書き方で回避できるかもしれないと覚えても良いかもしれない。
ただ、きれいなのはどう見てもexistsだよね。。。

サブクエリで結合

SELECT a.name 
FROM shain a,
(select distinct shain_id from sikaku where sikaku_name in (‘基本情報’,’java’) )b
Where a.shain_id = b.shain_id

EXISTS

SELECT a.name
FROM shain a
Where exists
(select ‘X’ from sikaku b where sikaku_name in (‘基本情報’,’java’) and
a.shain_id = b.shain_id)

更新履歴

• ファンクションインデックス：ご指摘いただき追加しました。普段あまり使ってないのでかくの忘れてました。ありがとうございます。
• with句も効果ある。と指摘いただきました
• SPMの紹介の追加
• 統計情報を最新化：ご指摘いただきました。これはあまりにも大前提だったので書くの忘れてました。ありがとうございます。
• bindpeek機能については最近は更に賢くなり有用との声が多かったので削っておきます。
• テーブルのパーティーション化、パーティショニング：普通に忘れていたので追加しました。