タグ「Database」が付けられているもの

行値構成子(行値式)

今日はまってしまった行値式について。

行値式とはこんな感じのやつです。

WHERE (XXX,YYY) IN ((1,2),(3,4))

何ではまったかと言うと、INの後の()を忘れて IN  (1,2),(3,4)と書いていました。恥ずかしい(´・ω・`)

製品によっては行値式をサポートしていないらしいので、そういう場合は以下のように無理矢理書いちゃいます。(必ずしもこれがベストってわけではなく、EXISTSつかったり他の方法使った方がたぶん幸せになれます)

WHERE ((XXX = 1 AND YYY = 2) OR (XXX = 3 AND YYY = 4))

 

RDBで文字列にインデックスをそのまま張ってる事って結構あると思います。
(普通はないかな?)

例えばURLに対してINDEXを作った場合、1エントリに対してのインデックスの量が多くなってしまいます。
そこでカラムを一つ追加して、その文字列のハッシュ値を格納するようにします。
そして文字列のカラムにはインデックスを張りません。
WHERE句を書くときには、「ハッシュ値カラム AND 文字列カラム」とします。
これでインデックスツリーが小さくなってDBサーバのメモリ効率が上がります。
クライアントサイドでハッシュを求めることになりますが、クライアントサイドはスケール化が簡単な事と、ハッシュ値計算なんて微々たるものなので問題にはならないと思います。

Javaの場合であればhashCode()メソッドがあるので自前でハッシュ関数を作る必要はありません。

MySQLを例にしてみます。
(直接手打ちしたので構文エラーになるかもしれませんw)

-- これは悪い例
CREATE TABLE TBL_URL(
  IDX INTEGER NOT NULL,
  URL VARCHAR(4096) NOT NULL,
  STATUS INTEGER NOT NULL,
  TITLE VARCHAR(1024),
  BODY MEDIUMTEXT,
  PRCDATE TIMESTAMP NOT NULL,
  CONSTRAINT PK_TBL_URL PRIMARY KEY (IDX)
);
CREATE INDEX IDX_TBL_URL_01 ON TBL_URL(URL(255));
-- SELECT * FROM TBL_URL WHERE URL = ?
-- 改善例
CREATE TABLE TBL_URL(
  IDX INTEGER NOT NULL,
  URLCD INTEGR NOT NULL,
  URL VARCHAR(4096) NOT NULL,
  STATUS INTEGER NOT NULL,
  TITLE VARCHAR(1024),
  BODY MEDIUMTEXT,
  PRCDATE TIMESTAMP NOT NULL,
  CONSTRAINT PK_TBL_URL PRIMARY KEY (IDX)
);
CREATE INDEX IDX_TBL_URL_01 ON TBL_URL(URLCD);
-- SELECT * FROM TBL_URL WHERE URLCD = ? AND URL = ?

PreparedStatementのキャッシュ

  • 投稿日:
  • カテゴリ:

Wikiにも書きましたが、PreparedStatementをキャッシュするとスループットの向上が望めます。

parse, prepare処理が内部でどういった手順で行われているかは以下のサイトが参考になると思います。

実際にプログラムを書いて動作を確認したところ、確かにOracleでは30%ほど速くなりました。
MySQL(MyISAM)では200%ほど速くなりました。
INSERTしか確認していないので、実験の信頼性は低いと思いますが、気になる方は自分で試してみると良いかもしれません。

ちなみにWikiはこちらです。実験の結果も載せてます。

SQLチューニング

  • 投稿日:
  • カテゴリ:

この前書いたSQLチューニングの結果を。
DBはOracle9iR2。
こーいうのはWikiの方に書くべきかな。。

テーブルは以下のように定義。
NAME VARCHAR2(20)
POINT NUMBER(10,0)  ← こいつにINDEX張ってある

データ量は10万件
ObjectBrowserのデータ生成機能を使って生成。

比較したSQLは以下の通り。
[1]
SELECT NAME
FROM TBL_WORK1
WHERE POINT != 50;


[2]
SELECT NAME
FROM TBL_WORK1
WHERE POINT < 50 OR POINT > 50;

それぞれの実行計画と統計情報は以下の通り。
[1] 99807件選択されました(906 msec.)
------------------------ 実行計画 --------------------------
SELECT STATEMENT   Cost =
    TABLE ACCESS FULL TBL_WORK1

------------------------ 統計情報 --------------------------
recursive calls                                   0
db block gets                                     0
consistent gets                                 548
physical reads                                    0
redo size                                         0
bytes sent via SQL*Net to client            1517840
bytes received via SQL*Net from client         3106
SQL*Net roundtrips to/from client               252
sorts (memory)                                    0
sorts (disk)                                      0
rows processed                                99807

[2]99807件選択されました(672 msec.)
------------------------ 実行計画 --------------------------
SELECT STATEMENT   Cost =
    CONCATENATION  
        TABLE ACCESS BY INDEX ROWID TBL_WORK1
            INDEX RANGE SCAN IDX_TBL_WORK1_01
        TABLE ACCESS BY INDEX ROWID TBL_WORK1
            INDEX RANGE SCAN IDX_TBL_WORK1_01

------------------------ 統計情報 --------------------------
recursive calls                                   0
db block gets                                     0
consistent gets                               16712
physical reads                                    0
redo size                                         0
bytes sent via SQL*Net to client            1517840
bytes received via SQL*Net from client         3143
SQL*Net roundtrips to/from client               252
sorts (memory)                                    0
sorts (disk)                                      0
rows processed                                99807

まぁ統計情報使ってるので、実行時間は当てになりませんが・・・。
実行計画を見ると、1は全スキャンしてるのに対して、2は範囲スキャンをかけていますね。