SQL文を最速にする11のポイント

SQL文を最速にする11のポイント

 たとえ最終的な結果が同じでも,SQL文は書き方一つでパフォーマンスがずいぶんと変わってきます。ここでは,速いSQL文を記述するためのポイ ントや注意点をいくつか紹介しておきましょう。

●WHEREの左辺で算術演算子や関数を使わない

 WHERE句の左辺に算術演算や関数を指定すると,インデックスが使われません。例えば,
SELECT NAME FROM CUSTOMERS
WHERE SAL – TAX > 1000
とすると,たとえSALフィールドにインデックスが定義されていてもテーブル全体を走査してしまいます。こうした場合は,
SELECT NAME FROM CUSTOMERS
WHERE SAL > TAX + 1000
のように記述すれば良いでしょう。

●「後方一致」検索はなるべく避ける

 インデックスが付加されているフィールドであっても,LIKE ‘%AAA’ のような「後方一致」を指定すると,インデックスを検索せずにデータ部の全表走査が行われます。したがって「後方一致」の使用はなるべく避けるようにしま しょう。どうしても必要であるなら,
・何らかの,少量まで絞り込める条件とAND条件で組み合わせる
・複数のフィールドに分割し,少しでも前方・完全一致できる範囲を広げる
といった方法を検討して下さい。

●IS NULL,IS NOT NULLを単独で使わない

 条件を表すWHERE句にIS NULL/IS NOT NULLを指定したときは,インデックスを定義したフィールドであっても,全表走査が行われます。したがって,これらの条件を指定するときは,単独で指定 するのではなく,何らかのかなり絞り込める条件を合わせて指定してください。例えば,問い合わせの結果を変更せずに「B = 10」の条件を付加できるなら
…WHERE A IS NULL
とする代わりに
…WHERE A IS NULL AND B = 10
とします。

●SELECT文で「*」を使わない

 レコード長が長いときや,フィールド数が多いときには,すべてのフィールドを表す「*」を指定するのはできるだけ避けて,使用するフィールドだけ を指定するようにします。「*」を指定すると,参照系のSQL文では,すべてのフィールドを繰り返してコピーするため,リソースを無駄に使うことになりま す。最低限度必要なフィールドだけを指定するのが基本です。

●ORはある程度絞り込んでから使う

 論理演算子ORを使用した場合,一応インデックスが使用されるものの,個々の条件が抽出する件数が少ない(数%程度)状態でないと,あまり効果が ありません。

●GROUP BY,ORDER BY,HAVINGは注意する

 GROUP BY句,ORDER BY句,HAVING句は,余分なディスク入出力が発生したりディスク領域を使うので,自分もしくはほかのプログラムのパフォーマンスに悪影響を及ぼしま す。このことを念頭において,使わずに済むならなるべく使わないようにしましょう。

●演算子の組み合わせで速度が変わる

 検索条件に,「>」「<」「=」をANDで組み合わせるときは,指定の仕方によってインデックスの使われ方が異なります。等号と不等号の組み合わ せは,等号のみインデックスが使われます。例えば,
SELECT NAME FROM CUSTOMERS
WHERE JOB = ‘MANAGER’
AND SAL > 1000
とすると,「JOB = ‘MANAGER’」にはインデックスが使われますが,「SAL > 1000」には使われません。また,不等号同士の組み合わせでは,先に指定した条件だけにインデックスが使われます。つまり
SELECT NAME FROM CUSTOMERS
WHERE TAX > 100
AND SAL > 1000
のSQL文では,RDBMSは「TAX > 100」だけにインデックスを使い「SAL > 1000」には使いません。

●テーブルの別名を利用する

 テーブルに別名をつけて,フィールド名にはその別名をつけると,SQL文の解析処理を減らすことができます。例えば,
SELECT ID, NAME FROM CUSTOMERS
WHERE SAL < 1000
よりも,
SELECT a.ID, a.NAME FROM CUSTOMERS a
WHERE SAL < 1000
のほうが高速になります。

●SQL文の表現を統一する

 本文中で述べたように,RDBMSは実行計画をキャッシュに保存しておいて再利用します。ところが,SQL文に定数を直接記述してしまう と,RDBMSは定数値だけが異なるSQL文を別のものと解釈するため,再利用されません*B。 バインド変数を使用して,できる限りSQL文を統一するようにします。また,文字の大小や記述の仕方なども統一しておかないと別のSQL文だと認識されて しまうので,気を付けてください。

●SQL文を簡潔に記述する

 SQL文はなるべく簡潔に記述するようにします。そうすることで,SQL文の処理時間を短縮することができます。

Postgres9.2.4インストールCentOS6.4

そもそもCentOSには初めからPostgreSQLが入っているので一応確認

$yum list | grep postgres

8.**が表示されているんで始めに削除

#yum remove postgresql-server
#yum remove postgresql
#yum remove postgresql-libs

PostgreSQLのrpmファイルをインストール

「rpm」コマンドのインストールするオプションは「-i」。 「-v」と「-h」オプションでインストール情報を出力するので、「-ivh」

#PostgreSQLのrpmファイルのダウンロード
$ wget http://yum.postgresql.org/9.2/redhat/rhel-6-i386/pgdg-centos92-9.2-6.noarch.rpm

#rpmファイルのインストール
$ rpm -ivh pgdg-centos92-9.2-6.noarch.rpm

#インストールしたパッケージ情報を参照
$ rpm -q pgdg-centos92
pgdg-centos92-9.2-6.noarch

PostgreSQLのインストール

#パッケージ検索
$ yum search postgresql92
…
(省略)
…
postgresql92.i686 : PostgreSQL client programs and libraries
postgresql92-devel.i686 : PostgreSQL development header files and libraries
postgresql92-server.i686 : The programs needed to create and run a PostgreSQL server

#PostgreSQLのインストール(以下の3つ)
$ yum install postgresql92
$ yum install postgresql92-devel
$ yum install postgresql92-server

#環境変数「PATH」の表示
$ echo $PATH
/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/hide/bin

#環境変数「PATH」へ追加
$ PATH=$PATH:/usr/pgsql-9.2/bin

#環境変数「PATH」の表示
$ echo $PATH
/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/hide/bin:/usr/pgsql-9.2/bin

#PostgreSQLのバージョン確認
$ psql --version
psql (PostgreSQL) 9.2.4

なお、PostgreSQLの設定ファイルとプログラムファイルの場所は、今回の場合、以下のようになっています

#設定ファイル等
/var/lib/pgsql/9.2/data

#PostgreSQLプログラムファイル
/usr/pgsql-9.2

PostgreSQLのサービス起動

#サービス自動起動の確認
$ chkconfig --list postgresql-9.2
postgresql-9.2  0:off   1:off   2:off   3:off   4:off   5:off   6:off

#PostgreSQLのサービスを自動起動にする
$ chkconfig postgresql-9.2 on

#サービス自動起動の確認
$ chkconfig --list postgresql-9.2
postgresql-9.2  0:off   1:off   2:on    3:on    4:on    5:on    6:off

サービスを自動起動にしても、それと同時にサービスが起動されるわけではないので、起動してやらなくてはいけない。 初回起動時は「initdb」を実行してデータベース初期化を行う必要があるよう。 なお、サービスのコマンドは「service」コマンドでも、「/etc/init.d/postgresql-9.2 start」というようにしても可能。

#サービスの確認
$ service postgresql-9.2 status
 は停止しています

#サービスの確認
$ /etc/init.d/postgresql-9.2 status
 は停止しています

#サービスの起動(エラー)
$ service postgresql-9.2 start
/var/lib/pgsql/9.2/data is missing. Use "service postgresql-9.2 initdb" to initialize the cluster first.
                                                 [失敗]
#データベース初期化
$ service postgresql-9.2 initdb
データベースを初期化中:                          [  OK  ]

#サービスの起動
$ service postgresql-9.2 start
postgresql-9.2 サービスを開始中:                 [  OK  ]

#サービスの確認
$ service postgresql-9.2 status
 (pid  2627) を実行中...

PostgreSQLへログイン

「/etc/passwd」を見ると、ユーザー「postgres」が追加されている。 とりあえずパスワード変更しておき、ユーザー「postgres」にスイッチユーザーすると「psql」コマンドで、PostgreSQLへログインできた。 なお、「psql -l」のように「-l」オプションを付けると、データベース一覧が参照できるよう。 ちなみにログアウトは「\q」です。

#ユーザー確認
$ cat /etc/passwd
root:x:0:0:root:/root:/bin/bash
…
(省略)
…
postgres:x:26:26:PostgreSQL Server:/var/lib/pgsql:/bin/bash

#ユーザー「postgres」のパスワード変更
$ passwd postgres

#ユーザー「postgres」へスイッチユーザー
$ su postgres

#「psql」コマンドでログイン
bash-4.1$ psql
psql (9.2.4)
"help" でヘルプを表示します.

postgres=#

#ログアウト
postgres=# \q

Linux(CentOS)でPostgreSQLのユーザー、データベース作成


PostgreSQLユーザー、データベースの作成

続いて、PostgreSQLユーザーとデータベースを作成する。
なお、今回作成したユーザー名は「ginji」、データベース名は「moodle」です。
ユーザーとデータベースの作成は、PostgreSQLのスーパーユーザー「postgres」で行う。
このユーザーはPostgreSQLをインストールしたら、同時に作成されているはず。
ついでに、削除コマンドも参考に記載しておく。
createuserのオプションは次のよう。
-a…ユーザー作成権限付与(スーパーユーザー)
-d…データベース作成権限付与
-U…接続ユーザー指定
-P…パスワードプロンプトを表示
createdbのオプションは次のよう。
-O…所有ユーザー指定

#Linuxユーザー「postgres」へスイッチユーザー
$ su postgres

#PostgreSQLユーザー「ginji」の作成
$ createuser -d -U postgres -P ginji

#PostgreSQLユーザー「ginji」の削除
$ dropuser ginji

#データベース「moodle」の作成
$ createdb -O ginji moodle

#データベース「moodle」の削除
$ dropdb moodle

psqlで接続

#ユーザー「postgres」でログイン
$ psql -U ginji -d moodle

#テーブル一覧表示
$ \d

#テーブル構造表示
$ \d テーブル名

#psqlで使用できるコマンドヘルプ
$ \?

#psqlログアウト
$ \q

phpPgAdmin エクスポート(バックアップ) インポート

phpPgAdminのデータベースをローカルからリモートのサーバー環境へエクスポートする際のやりかた 。

ようするに別のサーバへDBごと移動したい場合の操作。

だいたいレンタルした安価なサーバーでpostgreSQLが使えるところは少ない。
当然、DBの接頭詞が付いているので好きな名前という訳にはいかない。なので、
ここはDBまるごとCREATEするのはやめてテーブルをインポートする。

pgAdmin-1 

  1. PgAdminⅢで、サーバ→データベース→スキーマ→テーブル
  2. ダンプオプションでエクスポートしたいテーブルを選択。全部でいいと思う。
  3. 右クリック→バックアップを選択。→わかりやすいフォルダを選ぶ
  4. フォーマットはPLAIN(プレーン)、「インサートコマンド」とオプションで「データのみ」をチェックしてOK

phpPgAdminでインポート

  1. インポートするデータベース名をクリック
  2. 選択したデータベースの[SQL]タブをクリックします
  3. SQL」タブを開きます。
  4. 「SQLスクリプトをアップロード」の参照ボタンをクリックします。
  5. [ファイルのアップロード]が表示されます。
  6. ローカルにバックアップしたファイル(SQL形式でエクスポートしたファイル)を選択し、「開く」をクリックします。
  7. アップロードするファイルの場所が自動的に入力されます
  8. 実行するを押す。