SQL Serverインデックス構造とその使用(二)

8867 ワード
sql データベース 職場 レジャー
SQL文の改善
SQL文がSQL SERVERでどのように実行されているのか分からない人が多く、自分が書いたSQL文がSQL SERVERに誤解されるのではないかと心配しています.例:
select * from table1 where name=''zhangsan'' and tID > 10000

および実行:
select * from table1 where tID > 10000 and name=''zhangsan''

一部の人は以上の2つの文の実行効率が同じかどうか分からない.簡単に文の前後から見ると、この2つの文は確かに異なるからだ.tIDが集約インデックスであれば、後の文は表の10000条以降の記録から検索すればいい.前の文では、名前=''zhangsan'がいくつかあるかを全表から検索し、制限条件tID>10000に基づいてクエリー結果を提示します. 
実際、このような心配は必要ありません.SQL SERVERには、where句の検索条件を計算し、どのインデックスがテーブルスキャンの検索スペースを縮小できるかを決定する「クエリー分析オプティマイザ」があります.つまり、自動最適化が可能です. 
クエリー・オプティマイザは、where句に基づいて自動的にクエリーを最適化できますが、クエリー・オプティマイザの動作原理を理解する必要があります.そうしないと、クエリー・オプティマイザが意図通りに迅速にクエリーを行わない場合があります. 
クエリー分析フェーズでは、クエリー・オプティマイザがクエリーの各フェーズを表示し、スキャンするデータ量を制限するのに役立つかどうかを決定します.一段階が走査パラメータ(SARG)として使用できるならば,最適化可能と呼ばれ,インデックスを用いて必要なデータを迅速に得ることができる. 
SARGの定義:検索を制限するための1つの動作であり、通常は特定のマッチング、1つの値範囲内のマッチング、または2つ以上の条件のAND接続を指すためである.形式は次のとおりです.
       <       >
                         
                        <       >

カラム名はオペレータの片側に表示され、定数または変数はオペレータの反対側に表示されます.次のようになります.
Name=’  ’
                          >5000
                        5000<  
                        Name=’  ’ and   >5000

1つの式がSARGの形式を満たすことができない場合、検索の範囲を制限することはできません.つまり、SQL SERVERは、各行に対してWHERE句のすべての条件を満たすかどうかを判断しなければなりません.したがって,1つのインデックスはSARS形式を満たさない式には役に立たない. 
SARSを紹介した後、SARSの使用と実践の中で出会ったいくつかの資料と結論の異なる経験をまとめてみましょう.
1、Like文がSARGに属するかどうかは使用するワイルドカードのタイプによって決まる
 ：name like ‘ %’ ，    SARG
                         ：name like ‘% ’ ,    SARG。

ワイルドカード%が文字列の開通でインデックスが使用できないためです. 
2、or全テーブルスキャンを引き起こす
Name='張三'and価格>5000記号SARG、Name='張三'or価格>5000はSARGに該当しません.orを使用すると、全テーブルスキャンが発生します. 
3、非オペレータ、関数によるSARS形式を満たさない文
SARS形式を満たさない文の最も典型的なケースは、NOT、!=、<>、!<、!>、NOT EXISTS、NOT IN、NOT LIKEなど、他にも関数があります.以下に、SARSの形態を満たさない例をいくつか示します.
ABS(  )<5000
                        Name like ‘% ’
                             ， ：
                        WHERE   *2>5000
                        SQL SERVER     SARG，SQL SERVER       ：
                        WHERE   >2500/2

しかし、SQL SERVERが元の式と完全に等価であることを保証できない場合があるため、このような使用は推奨されません. 
4、INの作用はORに相当する
文:
Select * from table1 where tid in (2,3)
                         
                        Select * from table1 where tid=2 or tid=3

同じです.すべてのテーブルスキャンが発生し、tidにインデックスがある場合、インデックスも失効します. 
5、なるべくNOTを使わない
6、existsとinの実行効率は同じです
多くの資料によると、existsはinの実行効率よりも高いと同時に、not inの代わりにできるだけnot existsを使うべきだという.しかし、実際に実験してみると、両者は前にnotを持たなくても、両者の間の実行効率は同じであることが分かった.サブクエリに関連しているため,今回SQL SERVERで持参したpubsデータベースを実験した.実行前にSQL SERVERのstatistics I/O状態を開くことができます.
（1）select title,price from titles where title_id in (select title_id from sales where qty>30)

文の実行結果は次のとおりです.
表''sales'.スキャンカウント18、論理読み56回、物理読み0回、前読み0回. 
表''titles'.スキャンカウント1,論理読み2回,物理読み0回,前読み0回である.
（2）select title,price from titles
                        　　　　　　　where exists (select * from sales
                        　　　　　　　where sales.title_id=titles.title_id and qty>30)

2番目の文の実行結果は次のとおりです.
表''sales'.スキャンカウント18、論理読み56回、物理読み0回、前読み0回. 
表''titles'.スキャンカウント1,論理読み2回,物理読み0回,前読み0回である. 
existsとinの実行効率は同じであることがわかります. 
7、関数charindex()と前にワイルドカード%を付けたLIKEの実行効率は同じである
前述したように、LIKEの前にワイルドカード%を付けると、全テーブルスキャンが発生するため、実行効率が低下する.しかし、一部の資料では、LIKEの代わりに関数charindex()を使用すると速度が大きく向上すると紹介されています.私が実験したところ、このような説明も間違っていることがわかりました.
　 
select gid,title,fariqi,reader from tgongwen
                        　　　　　　　　　where charindex(''    '',reader)>0 and fariqi>''2004-5-5''

時間:7秒、また:スキャンカウント4、論理読み7155回、物理読み0回、予読0回.
select gid,title,fariqi,reader from tgongwen
                        　　　　　　　　　where reader like ''%'' + ''    '' + ''%'' and fariqi>''2004-5-5''

時間:7秒、また:スキャンカウント4、論理読み7155回、物理読み0回、予読0回. 
8、unionはorの実行効率より決して高くない
私たちは前にwhere句でorを使うと全表スキャンを引き起こすことについて話しましたが、一般的に、私が見た資料はここでorの代わりにunionを使うことをお勧めします.事実は、この説が大部分に適用されていることを証明している.
select gid,fariqi,neibuyonghu,reader,title from Tgongwen
                        　　　　　　　　　　where fariqi=''2004-9-16'' or gid>9990000

使用時間:68秒.スキャンカウント1、論理読み4008回、物理読み283回、予読392163回.
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16''
                        union
                        select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid>9990000

使用時間:9秒.スキャンカウント8、論理読み67489回、物理読み216回、前読み7499回. 
unionを使うのは通常orを使うより効率が高いようです. 
しかし、実験の結果、orの両方のクエリー列が同じであれば、unionでは逆にorでの実行速度とは大きく差があることが分かった.ここでunionではインデックスをスキャンしているが、orでは全表をスキャンしている. 
　 
select gid,fariqi,neibuyonghu,reader,title from Tgongwen
                        　　　　　　　　　　where fariqi=''2004-9-16'' or fariqi=''2004-2-5''

時間:6423ミリ秒.スキャンカウント2、論理読み14726回、物理読み1回、予読7176回.
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16''
                        union
                        select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-2-5''

時間:11640ミリ秒.スキャンカウント8、論理読み14806回、物理読み108回、前読み1144回. 
9、フィールド抽出は「必要量、提出量」の原則に従い、「select*」を避ける
実験をしてみましょう
select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc

時間:4673ミリ秒
select top 10000 gid,fariqi,title from tgongwen order by gid desc

時間:1376ミリ秒
select top 10000 gid,fariqi from tgongwen order by gid desc

時間:80ミリ秒
このように、フィールドを1つ少なく抽出するたびに、データの抽出速度が向上します.アップグレードの速度は、捨てたフィールドのサイズによって判断されます. 
10、count(*)はcount(フィールド)より遅くない
一部の資料では、*を使用するとすべてのカラムが統計され、明らかに1つの世界のカラム名よりも効率的ではありません.この説には根拠がない.次のように考えてみましょう.
select count(*) from Tgongwen

経過時間:1500ミリ秒
select count(gid) from Tgongwen

時間:1483ミリ秒
select count(fariqi) from Tgongwen

使用時間:3140ミリ秒
select count(title) from Tgongwen

時間:52050ミリ秒
以上から分かるように、count(*)とcount(プライマリ・キー)の速度が同等であるのに対し、count(*)は他のプライマリ・キー以外のフィールドよりも速く、フィールドが長ければ長いほど、要約の速度が遅くなります.count(*)を使用すると、SQL SERVERは自動的に最小フィールドを検索して要約する可能性があると思います.もちろん、count(プライマリ・キー)を直接書くと、もっと直接来ます. 
11、order byは集約インデックス列によって並べ替え効率が最も高い
(gidはプライマリ・キー、fariqiは集約インデックス・カラム):
select top 10000 gid,fariqi,reader,title from tgongwen

使用時間:196ミリ秒.スキャンカウント1,論理読み289回,物理読み1回,前読み1527回である.
select top 10000 gid,fariqi,reader,title from tgongwen order by gid asc

時間:4720ミリ秒.スキャンカウント1、論理読み41956回、物理読み0回、前読み1287回.
select top 10000 gid,fariqi,reader,title from tgongwen order by gid desc

時間:4736ミリ秒.スキャンカウント1、論理読み55350回、物理読み10回、前読み775回.
select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi asc

時間:173ミリ秒.スキャンカウント1,論理読み290回,物理読み0回,前読み0回である.
select top 10000 gid,fariqi,reader,title from tgongwen order by fariqi desc

時間:156ミリ秒.スキャンカウント1、論理読み289回、物理読み0回、前読み0回. 
以上から、ソートされない速度および論理読み取り回数は、「order by集計インデックス列」の速度に相当しますが、これらは「order by非集計インデックス列」のクエリーよりもずっと速いことがわかります. 
また,あるフィールドに従ってソートを行う場合,正順でも逆順でも速度はほぼ同等である. 
12、効率的なTOP
実際、データベース応答時間に影響を与える最大の要因は、大容量のデータセットをクエリーおよび抽出する際のデータ検索ではなく、物理的なI/0操作です.次のようになります.
select top 10 * from (
                        select top 10000 gid,fariqi,title from tgongwen
                        where neibuyonghu=''   ''
                        order by gid desc) as a
                        order by gid asc

この文は,理論的には,文全体の実行時間がサブ文の実行時間よりも長いはずであるが,事実は逆である.サブステートメントの実行後に返されるレコードは10000個で、文全体が10個しか返さないため、データベース応答時間に影響を与える最大の要因は物理I/O操作です.物理I/O操作を制限するここで最も効果的な方法の一つはTOPキーワードを使うことです.TOPキーワードは、SQL SERVERでシステム最適化された上位数または上位数パーセントのデータを抽出するための語です.筆者の実践における応用により,TOPは確かに使いやすく,効率も高いことが分かった.しかし、この言葉は別の大規模なデータベースORACLEにはありません.これは残念ではありません.ORACLEでは他の方法(rownumberなど)で解決することができますが.今後の「千万レベルのデータを実現するページング表示ストレージプロセス」についての議論では,TOPというキーワードを用いる. 
ここまで、大容量のデータベースから必要なデータを迅速に検索する方法について説明しました.もちろん、私たちが紹介したこれらの方法はすべて“ソフト”の方法で、実践の中で、私達はまた各種の“ハード”の要素を考慮して、例えば:ネットワークの性能、サーバーの性能、オペレーティングシステムの性能、甚だしきに至ってはネットカード、スイッチなど.
HNOI 2017プレゼント
単純統計SQL Serverユーザーデータテーブルサイズ