PG開発時におけるブックマーク備忘録。

別ページ にて記載

指定したファイル以外のファイルを一律削除するコマンド例

@echo off

rem 削除前のファイル一覧(これはdirでいいよ)
forfiles /p D:\sample /s /c "cmd /c if @isdir==FALSE echo @file" > D:\log\01_filelist.txt

rem ファイル削除（aaa.txt,ccc.xlsx以外）
rem サブシェルには（forfilesの仕様上）「"」では渡せないため文字コード(ASCII)で記載する「0x22」
forfiles /p D:\sample /s /c "cmd /c if @isdir==FALSE if not @file==0x22aaa.txt0x22 if not @file==0x22ccc.xlsx0x22 del @path" > D:\log\forfiles.txt 2>&1

rem 削除後のファイル一覧(これはdirでいいよ)
forfiles /p D:\sample /s /c "cmd /c if @isdir==FALSE echo @file" > D:\log\02_filelist.txt
pause

SQLネタ

■簡単な結論
・SELECTは必要な列のみにする。
　「処理に不要な列」は、無駄にSELECTしないほうが良い。
　不要な列を取得するために実データにわざわざアクセスするのは、処理の無駄。

・WHERE句で指定する列は、インデックスキーを一番最初に指定すること。
作成時に「一番最初に指定した列」でツリー構造が作成されるため、
WHERE句の検索条件で指定される列を、一番最初に指定しておくようにする。

・複合インデックスのあるテーブルからSELECTする場合、
WHERE句にはその順番で指定しないとインデックスが効かない。

・ORDER BY / GROUP BYを利用する場合、インデックスを張った列を使用する。
インデックスを張った列は既にソートがされているので、並べ替える処理が不要だから。
実務の中ではインデックスが張られてない列に対してORDER BYやGROUP BYをかけるケースは、適切なWHERE句を書くことで、処理対象となるデータの量を少なくする。

・サブクエリを引数に取る場合、IN述語よりもEXISTS述語を使う
INは全表検索。EXISTSは見つかったらそこで検索終了。

・SELECT * は使用しない

・SELECT COUNT(*)は使用しない
　COUNT関数を使用する時は、COUNT(列名）で指定する。
　但し、COUNT(列名）は、指定した列がNULLの場合は、カウントしないので、プライマリキー項目などのNOT NULL列を指定する。

・ANDよりBETWEENの使用を検討する
　BETWEENは、指定された範囲評価の操作が1回で済むのに対し、ANDは複数回の操作となるので、ANDよりBETWEENの使用を検討する

・HAVINGよりWHEREの使用を検討する
　HAVINGは非常に重い処理なので、集計結果の判定以外は、HAVING句よりWHERE句の使用を検討する。

・UNIONよりUNION ALLの使用を検討する
　UNIONはSELECTの結果をマージした後に、暗黙のソート処理をして重複データを排除しますが、重複データが無いことが分かっている場合は、暗黙のソート処理を回避するために、UNION ALLを使用する
　※UNIONの他にも、DISTINCTや、GROUP BY、INTERSEST、MINUS等にも暗黙のソート処理が実行されるので、極力使用を避けた方が良い。

・テーブルに別名を付ける
　テーブルに別命を付けると、解析速度が向上するので、別名を付けるようにする


********************************************
■参考ページ
https://qiita.com/kazuho39/items/0d3e617661670311ea05
http://mickindex.sakura.ne.jp/database/db_optimize.html
https://sites.google.com/site/orapeform/sql_minaoshi
https://qiita.com/itito/items/22fabe94d1fc0c881d1c

********************************************
■実行計画
SQLServerがクエリを実行する際、「クエリの結果を得るために、そのクエリをどのように処理するか」というのを表したのが「実行計画」。

■「実行計画」から何を読み解けばいい？
「クエリを実行する際に無駄な処理をしていないか」を見ることが必要。
データの読み込み量が少なく済む処理をしていれば当然早く結果を得ることができるし、
データの読み込み量が多い（無駄が多い）処理をしていれば、その分遅いクエリになってしまう。


■インデックスの基礎知識

(1)「インデックス」って？
「検索のパフォーマンスを向上させる」ための機能。
インデックスが存在しない場合は、どんなデータを検索する場合でも、必ず表の先頭から最後まで探し続けなければならない。
（この動作のことを、「テーブルスキャン」「全表走査」とよぶ。）

(2)インデックスの仕組み
内部的には、「ツリー(Tree)構造）」で作成される。
最上部を「ルート」ノード、中間部を「中間」ノード、最下部を「リーフ」ノードと呼ぶ。
データは「昇順」に並び替えれて、データの範囲によって枝分かれする。
インデックスには、「クラスター化インデックス」「非クラスター化インデックス」がある。

(3)DBの内部構造
「クラスター化インデックス」「非クラスター化インデックス」の仕組みを理解するにあたって、
DBの内部構造を理解しておくことが重要。
SQLServerのデータベースは、「データファイル(.mdf)」と「トランザクションログファイル(.ldf)」の２種類で構成される。
　
データファイルには、テーブルやデータ、インデックス、ビュー、ストアドプロシージャなどが格納されている。
内部的には「ページ」という8KBの大きさで区切られており、「ディスク入出力の単位」。
データファイル内の連続した８ページは「エクステント」と呼ばれ、テーブルやインデックスに割り当てられる領域の単位になる。
データが追加されるとエクステントが１つ追加され、そのあとに追加されるデータが連続した８ページに格納される。

(4)「クラスター化インデックス」「非クラスター化インデックス」
①「非クラスター化インデックス」
インデックス内はデータが昇順で並び替えられて、データの範囲によって枝分かれしている。
リーフノードには実際のデータへのポインタ(位置情報)が格納され、ポインタには「行識別子(RID：Row ID)」が使用されている。
インデックスページには、「データの値(インデックスを作成した列の値)」が格納されており、
検索時には、この値と検索条件に指定された値との大小関係によって、ツリー構造が走査される。

②「クラスター化インデックス」
非クラスター化インデックスは、リーフレベルには「実際のデータへのポインタ(RID)」が格納されるのに対し、
クラスター化インデックスの場合は、リーフレベルに「実際のデータそのもの」が格納される。
実際のデータをインデックス内へ格納するため、テーブル内で１つしか作成できない。
クラスター化インデックスを利用して検索することを「Clusterd Index Seek」という。

③クラスター化インデックスが存在する場合の「非クラスター化インデックス」の内部構造
クラスター化インデックスが作成されると、非クラスター化インデックスの構造が変更され、
リーフレベルへ格納されるポインタが「行識別子：RID」から「クラスター化インデックスの値」へ変更される。
実際のデータを探すことなく（RID Lookupすることなく）インデックスのSeekのみで検索が完了するため、検索が高速に実行できる。
（例）社員テーブルの「社員番号」列にクラスター化インデックスが作成され、「姓」列に非クラスター化インデックスが作成されている場合。
　「姓」と「社員番号」列のみを取得する際に実データを探す必要がないため、高速に処理が実行できる。

＜個人的メモ＞
やはり「処理に不要な列」は、無駄にSELECTしないほうが良い、ということ。
不要な列を取得するために実データにわざわざアクセスするのは、処理の無駄。


(5)カバリングインデックス（複合インデックス）
複数カラムをインデックスのキーに指定すると、「複合インデックス」になる。
例えば「user_id」「item_id」という２つのカラムをインデックスキーに指定するような場合。
利用したいデータが「user_id」「item_id」のみであった場合、「欲しいデータはインデックスツリーが保持している」ため、
実データ領域にアクセスする必要がなくなる。
このことを生かして「取得するデータをインデックスに全て含めてしまおう」という考えを、「カバリングインデックス」という。

作成時に「一番最初に指定した列」でツリー構造が作成されるため、
WHERE句の検索条件で指定される列を、一番最初に指定しておくようにする。
　
また、カバリングインデックスは、中間ページとルートページにも2つ目以降に指定した列の値が格納されるため、
その列データのサイズが大きい場合にはインデックスサイズが大きくなり、パフォーマンス低下につながるケースがある。
「付加列インデックス(includeオプション)」の場合は、中間とルートへ値を格納することなく、リーフのみへ値を格納することができる。