今日は、遅いSQLクエリを考えるの日。
目次
とある日
データベースに関するトラブルとして、SQLが遅いというものがある。
自分でも、遅いSQLを見つけチューニングするという作業を行ってみたいが。
遅いクエリを解決する方法は調べるすぐみつかるが、どのようなテーブル構成やSQLなのかを開示しているものが見つからない。
SQLチューニングやってみたい。
なら、自分で環境を作るしかないのか。
ってことで、頑張って遅いSQLを作成する。
そして、解決方法を試してどれぐらい変わるのかを実際にやってみよう。
RDBMS
Oracle 12c
今回は、Oracleを使用してチューニングを行っていく。
理由は特にない。
強いて言うなら、そこにOracleがあったから。
DBMSごとに実行計画取得方法も違うので次やるときは違うものも試してみたい。
問題作成
まず、3つの問題に焦点を当てて行こうと思います。
1.INDEX未使用問題
言わずとしれた遅いSQLの原因といえばINDEXを適切に使用していない問題。
2. ORDER BY問題
ORDER BYでソートを行うと遅くなる問題。
3.IN句EXISTS句問題
IN句でできることEXISTS句にすると早くなる問題。
の以上3つの問題原因を作って解決したいと思います。
テーブル作成
複雑なテーブル構成をするのは大変ので簡単なテーブル作成。
テーブル作成の条件
各問題を同じテーブルで使用したいのですべての条件を満たさないといけない。
| 問題 | 条件 |
|---|---|
| INDEX未使用問題 | INDEX使用列とINDEX未使用列 |
| ORDER BY問題 | ソート使用列とソート未使用列 |
| IN句EXISTS句問題 | 自己参照するテーブル |
INDEX問題とORDER BY問題は、複数列必要となる。
上記の条件を加味すると、適切なテーブルは従業員テーブルかなと。
ということで、ありきたりな従業員テーブルを作っていきます。
テーブル設計
ER図
SQL
CREATE TABLE employees( EMPLOYEE_ID NUMBER(7) PRIMARY KEY, FIRST_NAME VARCHAR2(20) NOT NULL, LAST_NAME VARCHAR2(20) NOT NULL, EMAIL VARCHAR2(20) NOT NULL, HIRE_DATE DATE NOT NULL, MANAGER_ID NUMBER(7) , FOREIGN KEY(MANAGER_ID) REFERENCES employees(EMPLOYEE_ID) );
データ生成
1件1件入れるのめんどくさいので、ランダムで生成したいと思う。
SQL
連番を扱いたいので、シーケンスオブジェクトを使用したいと思います。
CREATE SEQUENCE seq_emp_id; insert /*+ APPEND */ into employees nologging ( EMPLOYEE_ID, FIRST_NAME, LAST_NAME,EMAIL, HIRE_DATE) (select seq_emp_id.NEXTVAL, DBMS_RANDOM.STRING('X', 10), DBMS_RANDOM.STRING('X', 10), CONCAT(CONCAT(DBMS_RANDOM.STRING('X', 5),'@'),DBMS_RANDOM.STRING('X', 5)), TO_CHAR(TO_DATE('20130101','YYYYMMDD') + FLOOR(DBMS_RANDOM.VALUE(0, 365)), 'YYYYMMDD') from (select 0 from all_catalog where rownum <= 100), (select 0 from all_catalog where rownum <= 1000) );
10万件作成してみた。
ちなみに、12秒かかった。
INDEX確認
念の為に、適切にINDEXがはられているかを確認する。
INDEX_NAME |INDEX_TYPE|TABLE_NAME|TABLE_TYPE|UNIQUENESS| ------------|----------|----------|----------|----------| SYS_C0010475|NORMAL |EMPLOYEES |TABLE |UNIQUE |
遅いSQL作成
1.INDEX未使用問題
INDEXが貼れられる列が、EMPLOYEE_IDなのでそれ以外の列を条件指定して検索する。
EXPLAIN PLAN FOR SELECT * FROM EMPLOYEES e2 WHERE MANAGER_ID = 10; SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT | -------------------------------------------------------------------------------| Plan hash value: 1445457117 | | -------------------------------------------------------------------------------| | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time || -------------------------------------------------------------------------------| | 0 | SELECT STATEMENT | | 1040 | 52000 | 205 (1)| 00:00:01 || |* 1 | TABLE ACCESS FULL| EMPLOYEES | 1040 | 52000 | 205 (1)| 00:00:01 || -------------------------------------------------------------------------------| | Predicate Information (identified by operation id): | --------------------------------------------------- | | 1 - filter("MANAGER_ID"=10) |
結果として、まずOperationでTABLE ACCESS FULLでINDEXが使用されていない。
そして、Costが合わせて410となっている。
2.不用意なソート問題
1番で使用したSQLにORDER BY句を追加する。
EXPLAIN PLAN FOR SELECT * FROM EMPLOYEES e2 WHERE MANAGER_ID = 10 ORDER BY FIRST_NAME DESC ; SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT | --------------------------------------------------------------------------------| Plan hash value: 3447538987 | | --------------------------------------------------------------------------------| | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time || --------------------------------------------------------------------------------| | 0 | SELECT STATEMENT | | 1040 | 52000 | 206 (1)| 00:00:01 || | 1 | SORT ORDER BY | | 1040 | 52000 | 206 (1)| 00:00:01 || |* 2 | TABLE ACCESS FULL| EMPLOYEES | 1040 | 52000 | 205 (1)| 00:00:01 || --------------------------------------------------------------------------------| | Predicate Information (identified by operation id): | --------------------------------------------------- | | 2 - filter("MANAGER_ID"=10) |
結果は、OperationでSORT ORDER BY が追加されていてソートがされたことがわかる。
Costがソート分増えて617となっている。
3.IN句問題
MANAGER_IDが上司のIDなので、部下がいる従業員を求めるIN句を使ったSQLを使用する。
EXPLAIN PLAN FOR SELECT * FROM EMPLOYEES e2 WHERE MANAGER_ID IN (SELECT EMPLOYEE_ID FROM EMPLOYEES); SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT | -------------------------------------------------------------------------------| Plan hash value: 1445457117 | | -------------------------------------------------------------------------------| | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time || -------------------------------------------------------------------------------| | 0 | SELECT STATEMENT | | 100K| 4882K| 205 (1)| 00:00:01 || |* 1 | TABLE ACCESS FULL| EMPLOYEES | 100K| 4882K| 205 (1)| 00:00:01 || -------------------------------------------------------------------------------| | Predicate Information (identified by operation id): | --------------------------------------------------- | | 1 - filter("MANAGER_ID" IS NOT NULL) |
結果は、1番と変わらない物となった。
ただ、Rows は1番と比べて格段に多い。
Costは410となっている。
解決方法
1.INDEX未使用問題
INDEXを使用してる列を条件指定して検索する。
EXPLAIN PLAN FOR SELECT * FROM EMPLOYEES e2 WHERE EMPLOYEE_ID = 10 ; SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT | --------------------------------------------------------------------------------------------| Plan hash value: 546905373 | | --------------------------------------------------------------------------------------------| | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time || --------------------------------------------------------------------------------------------| | 0 | SELECT STATEMENT | | 1 | 50 | 2 (0)| 00:00:01 || | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 50 | 2 (0)| 00:00:01 || |* 2 | INDEX UNIQUE SCAN | SYS_C0010475 | 1 | | 1 (0)| 00:00:01 || --------------------------------------------------------------------------------------------| | Predicate Information (identified by operation id): | --------------------------------------------------- | | 2 - access("EMPLOYEE_ID"=10) |
Operationで、INDEX UNIQUE SCAN が出ておりきちんとINDEXが使用されていることが確認できた。
Costも、INDEX未使用列と比べて格段に少なく5となっている。
差は、歴然である。
| INDEX | Cost |
|---|---|
| なし | 410 |
| あり | 5 |
2.不用意なソート問題
ソートを行う列を検索した列を使用する。
EXPLAIN PLAN FOR SELECT * FROM EMPLOYEES e2 WHERE MANAGER_ID = 10 ORDER BY MANAGER_ID ASC ; SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT | -------------------------------------------------------------------------------| Plan hash value: 1445457117 | | -------------------------------------------------------------------------------| | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time || -------------------------------------------------------------------------------| | 0 | SELECT STATEMENT | | 1040 | 52000 | 205 (1)| 00:00:01 || |* 1 | TABLE ACCESS FULL| EMPLOYEES | 1040 | 52000 | 205 (1)| 00:00:01 || -------------------------------------------------------------------------------| | Predicate Information (identified by operation id): | --------------------------------------------------- | | 1 - filter("MANAGER_ID"=10) |
ソート列を検索列と同じ列を使用したことによりOperationからSORT ORDER BYがなくなった。
なので、SORT ORDER BYで使用していたCostの206分がなくなる結果となった。
| ORDER BY | Cost |
|---|---|
| なし | 617 |
| あり | 410 |
3.IN句問題
IN句をEXISTS句を入れ替えるSQLを組み立てた。
EXPLAIN PLAN FOR SELECT * FROM EMPLOYEES T1 WHERE EXISTS (SELECT 1 FROM EMPLOYEES T2 WHERE T1.EMPLOYEE_ID = T2.MANAGER_ID ); SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT | ----------------------------------------------------------------------------------| Plan hash value: 3439112473 | | ----------------------------------------------------------------------------------| | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time || ----------------------------------------------------------------------------------| | 0 | SELECT STATEMENT | | 100 | 5300 | 410 (1)| 00:00:01 || |* 1 | HASH JOIN RIGHT SEMI| | 100 | 5300 | 410 (1)| 00:00:01 || | 2 | TABLE ACCESS FULL | EMPLOYEES | 100K| 292K| 205 (1)| 00:00:01 || | 3 | TABLE ACCESS FULL | EMPLOYEES | 100K| 4882K| 205 (1)| 00:00:01 || ----------------------------------------------------------------------------------| | Predicate Information (identified by operation id): | --------------------------------------------------- | | 1 - access("T1"."EMPLOYEE_ID"="T2"."MANAGER_ID") |
他のSQLと違ってCostが増加した。
| 比較方法 | Cost |
|---|---|
| IN | 410 |
| EXISTS | 1230 |
副問合せで行っている比較でHASH JOIN RIGHT SEMIが使用されている。
主問い合わせでINDEXを使用しない検索でCost205と副問合せでINDEXを使用しない検索をしたことによってそれらのCost足されたCostがかかっている。
これは、SQLチューニング失敗となった。
この場合のSQLなら、IN句を使用したほうがいいという結果となった。
一概に、EXISTS句を使用すれば良い結果となるわけではないことが判明した。
〆
今回行ったSQLは簡単なものであったため変化量が少なく感じた。
次は、複雑なSQLを書いてそれを早くする作業も行ってみたい。
今回は、チューニングの知識だけでなく、テストデータの自動生成も行えてとてもためになった。
チューニングはもっとやってみたいと思った。
