【ソリューション事業部 ヤマモト】
今作ってるレゴの記事を書くつもりが、、、下記のとおりまだ出来てないので
SQLのチューニングの記事にしました。。。
SQLのチューニングでは比較的容易に済むインデックスを作成する対応を取られがちですが
数万レコード程度でのデータ量ではあまり効き目がなく
本格的な対応は下記のような方法を基づいてチューニングを進める必要があります。
下記の項目類は私が経験してきた事で特に重要と感じた事を列挙しています。
※全てoracleですが、考え方はRDB共通のはずです。
特にRDBは結合順を間違ってしまいその制御で遅くなる事がほとんどなので
ヒント句が使えるなら１０番の結合順の指定、
また、postgresqlならばコストベースなのに
ややこしいSQLでは結合の順番がルールベースみたいな事になってしまう事などを
意識すれば大体は対処できるようになると思います。
１、不要なサブクエリ
サブクエリでは可能な限り
exists、not existsを使用すること
Indexが使用されやすい、全走査の必要がない
–１．遅い
SELECT name
FROM Personnel WHERE birthday
IN (SELECT birthday FROM Celebrities);
–２．速い SELECT P.name
FROM Personnel AS P WHERE
EXISTS (SELECT FROM Clelebrities AS C WHERE P.birthday = C.birthday);
２、不要な結合、不要なdistinct
–１．遅い
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
–２．速い
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)
３、左辺の関数
条件では列側に関数はかまさない。
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型は秒まで管理しないのであれば、使いにくいので使わない方が良い。
４、暗黙の型変換
暗黙の型変換は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 (‘東京’, ‘大阪’, ‘鳥取’, ‘徳島’);
６、その他豆知識
●Union より union all
重複行を排除するためのソートが発生するので、重複を許すならunion all
●withを使用すると早くなるとか言う人がいるが大きな間違い。
早くなりません。
●viewも同じく。
マテリアライズドビューなら早くなるが、MVは最終手段だと思っといた方が良い。
●is nullは全走査に必ずなる。
●not in、!=、<>などはなるべく使わないように。
Not exists、orやinで代用できるならする。工夫しても無理なら使ってもよいと思う。
とは言ってもIndexが貼られているようなキーでこういった事はしないだろう。
７、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の設計は業務をよく知り、練っていく必要がある。
８、不可視インデックス
オプティマイザから見えないようにする事が出来る。
alter index in_res_guest invisible;
select /* INDEX (res IN_RES_GUEST) */ res_id
from res where guest_id = 101;
９、bindpeek機能の停止
千万以上の大量データを扱うようなシステムでは必須かと。
ORACLE BindPeek機能を停止する隠しパラメータ
_optim_peek_user_binds
通常はTRUEでBindPeek機能有効 FLASEで無効になります
設定方法 alter system set “_optim_peek_user_binds”=false;
確認方法(sysユーザにて実行)
select a.ksppinm “Parameter”, a.KSPPDESC “Description”, b.ksppstvl “Value“
from x$ksppi a, x$ksppcv b
where a.indx = b.indx and a.ksppinm like ‘%パラメータ名%’;
１０、ヒント句
ヒント句を指定する事により実行計画をある程度操作する事ができる。
http://docs.oracle.com/cd/E16338_01/server.112/b56299/sql_elements006.htm
重要なヒント句
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;
以上。
この記事に関連する情報
テーブルといえば、家具の名産地、福岡県大川市で木が持つ本来の魅力を堪能できる上質な無垢家具を製造する、「こもれび家具」の通販サイトをオープンしました。
家具好きの方も納得する出来栄えです。是非一度ご覧下さい！
