SQLパフォーマンスの調整は、特に、ごくわずかな変更でもパフォーマンスに劇的な(プラスまたはマイナスの)影響を与える可能性がある大規模なデータを処理する場合、非常に困難な作業になる可能性があります。
中規模および大規模の企業では、ほとんどのSQLパフォーマンスチューニングはデータベース管理者(DBA)によって処理されます。しかし、私を信じてください、 たくさんの開発者 DBAのようなタスクを実行する必要がある人がいます。さらに、私が見た多くの企業では、 行う DBAを持っていると、開発者とうまく連携するのに苦労することがよくあります。ポジションには問題解決のさまざまなモードが必要であり、同僚間の意見の不一致につながる可能性があります。
どのタイプのエンティティがLLCであるか大規模なデータを処理する場合、ごくわずかな変更でもパフォーマンスに劇的な影響を与える可能性があります。
それに加えて、企業構造も役割を果たすことができます。 DBAチームがすべてのデータベースとともに10階に配置され、開発者が15階に配置されている、または完全に別個のレポート構造の下にある別の建物に配置されているとしましょう。これらの条件下でスムーズに連携することは確かに困難です。
この記事では、2つのことを達成したいと思います。
データベースをまったく使用したことがなく、「SQLとは何ですか」と自問する場合 性能調整 ?」、インデックス作成は、開発中に無視されることが多いSQLデータベースを調整するための効果的な方法であることを知っておく必要があります。基本的には、 インデックス は、高速のランダムルックアップと順序付けられたレコードへの効率的なアクセスを提供することにより、データベーステーブルでのデータ取得操作の速度を向上させるデータ構造です。つまり、インデックスを作成すると、以前よりも速く行を選択または並べ替えることができます。
インデックスは、他の列が同じ値を持たないことを保証する主キーまたは一意のインデックスを定義するためにも使用されます。もちろん、データベースのインデックス作成は非常に興味深いトピックであり、この簡単な説明では正義を行うことはできません(ただし これがより詳細な記事です )。
インデックスを初めて使用する場合は、クエリを構成するときに次の図を使用することをお勧めします。
基本的に、目標は主要な検索列と順序列にインデックスを付けることです。
テーブルが常にINSERT
、UPDATE
、およびDELETE
によって打たれている場合は、インデックスを作成するときに注意する必要があります。 パフォーマンスの低下 これらの操作の後にすべてのインデックスを変更する必要があるためです。
さらに、DBAは、数百万を超える行のバッチ挿入を実行する前に、SQLインデックスを削除することがよくあります。 挿入プロセスをスピードアップ 。バッチが挿入された後、インデックスを再作成します。ただし、インデックスを削除すると、そのテーブルで実行されているすべてのクエリに影響することに注意してください。したがって、このアプローチは、単一の大きな挿入で作業する場合にのみ推奨されます。
ちなみに、SQLServerの実行プランツールはインデックスの作成に役立ちます。
その主な機能は、SQLServerクエリオプティマイザーによって選択されたデータ取得方法をグラフィカルに表示することです。これまでに見たことがない場合は、 詳細なウォークスルー 。
(SQL Server Management Studioで)実行プランを取得するには、クエリを実行する前に[実際の実行プランを含める](CTRL + M)をクリックするだけです。
その後、「実行プラン」という名前の3番目のタブが表示されます。欠落しているインデックスが検出される場合があります。作成するには、実行プランを右クリックして、「MissingIndexDetails…」を選択します。とても簡単です!
(( クリックしてズーム )
1000個のクエリがデータベースを順番に処理するシナリオを想像してみてください。何かのようなもの:
for (int i = 0; i <1000; i++) { SqlCommand cmd = new SqlCommand('INSERT INTO TBL (A,B,C) VALUES...'); cmd.ExecuteNonQuery(); }
あなたがすべき そのようなループを避けてください あなたのコードで。たとえば、一意のINSERT
を使用して、上記のスニペットを変換できます。またはUPDATE
複数の行と値を持つステートメント:
INSERT INTO TableName (A,B,C) VALUES (1,2,3),(4,5,6),(7,8,9) -- SQL SERVER 2008 INSERT INTO TableName (A,B,C) SELECT 1,2,3 UNION ALL SELECT 4,5,6 -- SQL SERVER 2005 UPDATE TableName SET A = CASE B WHEN 1 THEN 'NEW VALUE' WHEN 2 THEN 'NEW VALUE 2' WHEN 3 THEN 'NEW VALUE 3' END WHERE B in (1,2,3)
WHERE
を確認してください句は、既存の値と一致する場合、保存された値の更新を回避します。このような些細な最適化により、数千行ではなく数百行のみを更新することで、SQLクエリのパフォーマンスを劇的に向上させることができます。例えば:
UPDATE TableName SET A = @VALUE WHERE B = 'YOUR CONDITION' AND A @VALUE -- VALIDATION
に 相関サブクエリ 親クエリの値を使用するものです。この種のSQLクエリは実行される傾向があります 行ごと 、外部クエリによって返される行ごとに1回、したがってSQLクエリのパフォーマンスが低下します。新しいSQL開発者は、通常、この方法でクエリを構成していることに気付くことがよくあります。これは通常、簡単な方法だからです。
すべてのデバイスのメディアクエリcss
相関サブクエリの例を次に示します。
SELECT c.Name, c.City, (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c
特に、問題は、内部クエリ(SELECT CompanyName…
)が実行されることです。 各 外部クエリによって返される行(SELECT c.Name…
)。しかし、なぜCompany
を超えるのですか外部クエリによって処理されるすべての行に対して何度も何度も?
より効率的なSQLパフォーマンスチューニング手法は、相関サブクエリを結合としてリファクタリングすることです。
SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co ON c.CompanyID = co.CompanyID
この場合、Company
を確認します。最初に一度だけテーブルを作成し、Customer
で結合しますテーブル。それ以降、必要な値(co.CompanyName
)をより効率的に選択できます。
私のお気に入りのSQL最適化のヒントの1つは、SELECT *
を避けることです!代わりに、必要な特定の列を個別に含める必要があります。繰り返しますが、これは単純に聞こえますが、このエラーはいたるところに見られます。数百の列と数百万の行を含むテーブルについて考えてみます。アプリケーションに実際に必要な列が数個しかない場合は、クエリを実行しても意味がありません。 すべて データ。それは膨大なリソースの浪費です。 (( その他の問題については、を参照してください。 ここに 。 )
例えば:
SELECT * FROM Employees
対。
SELECT FirstName, City, Country FROM Employees
本当にすべての列が必要な場合は、すべての列を明示的にリストしてください。これはそれほどルールではありませんが、将来のシステムエラーや追加のSQLパフォーマンスチューニングを防ぐ手段です。たとえば、INSERT... SELECT...
を使用している場合また、新しい列の追加によってソーステーブルが変更された場合、その列が宛先テーブルで必要とされていなくても、問題が発生する可能性があります。例:
INSERT INTO Employees SELECT * FROM OldEmployees Msg 213, Level 16, State 1, Line 1 Insert Error: Column name or number of supplied values does not match table definition.
SQL Serverからのこの種のエラーを回避するには、各列を個別に宣言する必要があります。
INSERT INTO Employees (FirstName, City, Country) SELECT Name, CityName, CountryName FROM OldEmployees
ただし、SELECT *
を使用する場合があることに注意してください。適切かもしれません。たとえば、一時テーブルの場合、次のトピックに進みます。
一時テーブル 通常、クエリの複雑さが増します。コードをシンプルでわかりやすい方法で記述できる場合は、一時テーブルを避けることをお勧めします。
ただし、データ操作を伴うストアドプロシージャがある場合は、 できません 単一のクエリで処理する場合は、一時テーブルを仲介として使用して、最終結果を生成するのに役立てることができます。
大きなテーブルを結合する必要があり、そのテーブルに条件がある場合は、一時テーブルでデータを転送してから結合することで、データベースのパフォーマンスを向上させることができます。 それ 。一時テーブルの行数は元の(大きい)テーブルよりも少ないため、結合はより速く終了します。
バイヤーの交渉力
決定は必ずしも簡単ではありませんが、この例は、一時テーブルを使用する可能性のある状況を理解するのに役立ちます。
何百万ものレコードを持つ顧客テーブルを想像してみてください。特定の地域に参加する必要があります。 SELECT INTO
を使用してこれを実現できますステートメントとその後の一時テーブルとの結合:
SELECT * INTO #Temp FROM Customer WHERE RegionID = 5 SELECT r.RegionName, t.Name FROM Region r JOIN #Temp t ON t.RegionID = r.RegionID
(( 注:一部のSQL開発者は、SELECT INTO
の使用も避けています。一時テーブルを作成するには、このコマンドはtempdbデータベースをロックし、他のユーザーが一時テーブルを作成できないようにします。幸いなことに、これは 7.0以降で修正済み 。)
一時テーブルの代わりに、サブクエリをテーブルとして使用することを検討してください。
SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT * FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID
ちょっと待って!この2番目のクエリに問題があります。上記のように、サブクエリには必要な列のみを含める必要があります(つまり、SELECT *
を使用しないでください)。それを考慮に入れて:
SELECT r.RegionName, t.Name FROM Region r JOIN (SELECT Name, RegionID FROM Customer WHERE RegionID = 5) AS t ON t.RegionID = r.RegionID
これらのSQLスニペットはすべて同じデータを返します。ただし、一時テーブルを使用すると、たとえば、パフォーマンスを向上させるために一時テーブルにインデックスを作成できます。良い議論があります ここに 一時テーブルとサブクエリの違いについて。
cssメディアクエリレスポンシブデザイン
最後に、一時テーブルが完成したら、データベースへの接続が終了したときのように、自動的に削除されるのを待つのではなく、一時テーブルを削除してtempdbリソースをクリアします。
DROP TABLE #temp
このSQL最適化手法は、EXISTS()
の使用に関係しています。レコードが存在するかどうかを確認する場合は、EXISTS()
を使用しますCOUNT()
の代わりに。 COUNT()
テーブル全体をスキャンし、条件に一致するすべてのエントリをカウントしますEXISTS()
必要な結果が表示されるとすぐに終了します。これはあなたに与えるでしょう パフォーマンスの向上とコードの明確化 。
IF (SELECT COUNT(1) FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') > 0 PRINT 'YES'
対。
IF EXISTS(SELECT FIRSTNAME FROM EMPLOYEES WHERE FIRSTNAME LIKE '%JOHN%') PRINT 'YES'
SQL Server 2016を使用するDBAはおそらく認識しているため、バージョンは デフォルトと互換性管理 。メジャーバージョンとして、もちろん、新しいクエリの最適化が付属していますが、それらを使用するかどうかの制御は、sys.databases.compatibility_level
を介して合理化されています。
SQLデータベース管理者(DBA)と開発者は、データに関連する問題とデータに関連しない問題について衝突することがよくあります。私の経験から導き出された、ここに、うまくやって、効果的に一緒に働く方法に関するいくつかのヒントがあります(両方の当事者のために)。
アプリケーションが突然動作を停止した場合は、データベースの問題ではない可能性があります。たとえば、ネットワークに問題がある可能性があります。 DBAを非難する前に、少し調べてください。
忍者SQLデータモデラーの場合でも、DBAにリレーショナル図の作成を依頼してください。彼らは共有し、提供することがたくさんあります。
DBAは急速な変化を好みません。これは当然のことです。データベース全体を分析し、あらゆる角度から変更の影響を調べる必要があります。列の単純な変更は、実装に1週間かかる場合がありますが、それは、エラーが会社にとって大きな損失として顕在化する可能性があるためです。我慢して!
SQL DBAに、実稼働環境でデータを変更するように依頼しないでください。本番データベースにアクセスする場合は、すべての変更について責任を負う必要があります。
データベースについて質問されるのが気に入らない場合は、リアルタイムのステータスパネルを提供してください。 開発者 は常にデータベースのステータスを疑っています。そのようなパネルは、すべての人の時間とエネルギーを節約する可能性があります。
テスト/品質保証環境で開発者を支援します。実際のデータに対する簡単なテストで、本番サーバーを簡単にシミュレートできます。これは、自分だけでなく他の人にとっても大幅な時間の節約になります。
開発者は、ビジネスロジックが頻繁に変更されるシステムに一日中費やしています。この世界がより柔軟であることを理解し、重要な瞬間にいくつかのルールを破ることができるようにしてください。
SQLデータベースは進化します。データを新しいバージョンに移行する必要がある日が来るでしょう。開発者は、新しいバージョンごとに重要な新機能を期待しています。変更の受け入れを拒否する代わりに、事前に計画を立てて、移行の準備をしてください。
SQL Serverのようなデータベース管理システムは、提供したSQLクエリを、データベース内のデータを読み取ったり変更したりするために実行する必要のある実際の命令に変換する必要があります。処理後、データベースエンジンは、可能な場合はクエリを自動的に最適化しようとします。
非営利の継続ケア退職コミュニティ
クエリの最適化とは、開発者またはデータベースエンジンが、SQLServerが同じ結果をより効率的に返すことができるようにクエリを変更することです。 COUNT()の代わりにEXISTS()を使用するのと同じくらい簡単な場合もありますが、別の方法でクエリを書き直す必要がある場合もあります。
パフォーマンスの調整には、クエリの最適化、SQLクライアントコードの最適化、データベースインデックスの管理、および別の意味で、開発者とDBA間のより良い調整が含まれます。
インデックスは、テーブルのデータのターゲットサブセットを追跡するため、サーバーはそのテーブルのデータの最後のビットをすべて調べる必要がなく、選択と順序付けをはるかに高速に実行できます。
EXISTS()は、一致する行が見つかるとすぐに処理を停止しますが、COUNT()は、最終的にその詳細が実際に必要かどうかに関係なく、すべての行をカウントする必要があります。