データサイエンスとデータベース

開発者向けのSQLデータベースのパフォーマンスチューニング

SQLパフォーマンスの調整は、特に、ごくわずかな変更でもパフォーマンスに劇的な（プラスまたはマイナスの）影響を与える可能性がある大規模なデータを処理する場合、非常に困難な作業になる可能性があります。

中規模および大規模の企業では、ほとんどのSQLパフォーマンスチューニングはデータベース管理者（DBA）によって処理されます。しかし、私を信じてください、たくさんの開発者 DBAのようなタスクを実行する必要がある人がいます。さらに、私が見た多くの企業では、行う DBAを持っていると、開発者とうまく連携するのに苦労することがよくあります。ポジションには問題解決のさまざまなモードが必要であり、同僚間の意見の不一致につながる可能性があります。

どのタイプのエンティティがLLCであるか

大規模なデータを処理する場合、ごくわずかな変更でもパフォーマンスに劇的な影響を与える可能性があります。

それに加えて、企業構造も役割を果たすことができます。 DBAチームがすべてのデータベースとともに10階に配置され、開発者が15階に配置されている、または完全に別個のレポート構造の下にある別の建物に配置されているとしましょう。これらの条件下でスムーズに連携することは確かに困難です。

この記事では、2つのことを達成したいと思います。

開発者に開発者側のSQLパフォーマンスチューニング手法を提供します。
開発者とDBAが効果的に連携する方法を説明します。

SQLパフォーマンスチューニング（コードベース内）：インデックス

データベースをまったく使用したことがなく、「SQLとは何ですか」と自問する場合性能調整？」、インデックス作成は、開発中に無視されることが多いSQLデータベースを調整するための効果的な方法であることを知っておく必要があります。基本的には、インデックスは、高速のランダムルックアップと順序付けられたレコードへの効率的なアクセスを提供することにより、データベーステーブルでのデータ取得操作の速度を向上させるデータ構造です。つまり、インデックスを作成すると、以前よりも速く行を選択または並べ替えることができます。

インデックスは、他の列が同じ値を持たないことを保証する主キーまたは一意のインデックスを定義するためにも使用されます。もちろん、データベースのインデックス作成は非常に興味深いトピックであり、この簡単な説明では正義を行うことはできません（ただしこれがより詳細な記事です）。

インデックスを初めて使用する場合は、クエリを構成するときに次の図を使用することをお勧めします。

この図は、すべての開発者が知っておくべきSQLパフォーマンスチューニングのヒントをいくつか示しています。

基本的に、目標は主要な検索列と順序列にインデックスを付けることです。

テーブルが常にINSERT、UPDATE、およびDELETEによって打たれている場合は、インデックスを作成するときに注意する必要があります。パフォーマンスの低下これらの操作の後にすべてのインデックスを変更する必要があるためです。

さらに、DBAは、数百万を超える行のバッチ挿入を実行する前に、SQLインデックスを削除することがよくあります。挿入プロセスをスピードアップ。バッチが挿入された後、インデックスを再作成します。ただし、インデックスを削除すると、そのテーブルで実行されているすべてのクエリに影響することに注意してください。したがって、このアプローチは、単一の大きな挿入で作業する場合にのみ推奨されます。

SQLチューニング：SQLServerでの実行プラン

ちなみに、SQLServerの実行プランツールはインデックスの作成に役立ちます。

その主な機能は、SQLServerクエリオプティマイザーによって選択されたデータ取得方法をグラフィカルに表示することです。これまでに見たことがない場合は、詳細なウォークスルー。

（SQL Server Management Studioで）実行プランを取得するには、クエリを実行する前に[実際の実行プランを含める]（CTRL + M）をクリックするだけです。

その後、「実行プラン」という名前の3番目のタブが表示されます。欠落しているインデックスが検出される場合があります。作成するには、実行プランを右クリックして、「MissingIndexDetails…」を選択します。とても簡単です！

このスクリーンショットは、SQLデータベースのパフォーマンスチューニング手法の1つを示しています。

（（ クリックしてズーム ）

SQLチューニング：コーディングループを回避する

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チューニング：相関するSQLサブクエリを回避する

に相関サブクエリ親クエリの値を使用するものです。この種の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チューニング：慎重に選択

私のお気に入りの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 *を使用する場合があることに注意してください。適切かもしれません。たとえば、一時テーブルの場合、次のトピックに進みます。

SQLチューニング：一時テーブルの賢明な利用（#Temp）

一時テーブル通常、クエリの複雑さが増します。コードをシンプルでわかりやすい方法で記述できる場合は、一時テーブルを避けることをお勧めします。

ただし、データ操作を伴うストアドプロシージャがある場合は、 できません 単一のクエリで処理する場合は、一時テーブルを仲介として使用して、最終結果を生成するのに役立てることができます。

大きなテーブルを結合する必要があり、そのテーブルに条件がある場合は、一時テーブルでデータを転送してから結合することで、データベースのパフォーマンスを向上させることができます。それ。一時テーブルの行数は元の（大きい）テーブルよりも少ないため、結合はより速く終了します。

バイヤーの交渉力

決定は必ずしも簡単ではありませんが、この例は、一時テーブルを使用する可能性のある状況を理解するのに役立ちます。

何百万ものレコードを持つ顧客テーブルを想像してみてください。特定の地域に参加する必要があります。 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チューニング：「私のレコードは存在しますか？」

この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 Server2016を使用したSQLパフォーマンスチューニング

SQL Server 2016を使用するDBAはおそらく認識しているため、バージョンはデフォルトと互換性管理。メジャーバージョンとして、もちろん、新しいクエリの最適化が付属していますが、それらを使用するかどうかの制御は、sys.databases.compatibility_levelを介して合理化されています。

SQLパフォーマンスチューニング（Office内）

SQLデータベース管理者（DBA）と開発者は、データに関連する問題とデータに関連しない問題について衝突することがよくあります。私の経験から導き出された、ここに、うまくやって、効果的に一緒に働く方法に関するいくつかのヒントがあります（両方の当事者のために）。

DBAと開発者が効果的に連携する必要がある場合、SQLパフォーマンスチューニングはコードベースを超えます。

つぶやき

開発者向けのデータベース最適化：

アプリケーションが突然動作を停止した場合は、データベースの問題ではない可能性があります。たとえば、ネットワークに問題がある可能性があります。 DBAを非難する前に、少し調べてください。
忍者SQLデータモデラーの場合でも、DBAにリレーショナル図の作成を依頼してください。彼らは共有し、提供することがたくさんあります。
DBAは急速な変化を好みません。これは当然のことです。データベース全体を分析し、あらゆる角度から変更の影響を調べる必要があります。列の単純な変更は、実装に1週間かかる場合がありますが、それは、エラーが会社にとって大きな損失として顕在化する可能性があるためです。我慢して！
SQL DBAに、実稼働環境でデータを変更するように依頼しないでください。本番データベースにアクセスする場合は、すべての変更について責任を負う必要があります。

SQL Server DBAのデータベース最適化：

データベースについて質問されるのが気に入らない場合は、リアルタイムのステータスパネルを提供してください。開発者は常にデータベースのステータスを疑っています。そのようなパネルは、すべての人の時間とエネルギーを節約する可能性があります。
テスト/品質保証環境で開発者を支援します。実際のデータに対する簡単なテストで、本番サーバーを簡単にシミュレートできます。これは、自分だけでなく他の人にとっても大幅な時間の節約になります。
開発者は、ビジネスロジックが頻繁に変更されるシステムに一日中費やしています。この世界がより柔軟であることを理解し、重要な瞬間にいくつかのルールを破ることができるようにしてください。
SQLデータベースは進化します。データを新しいバージョンに移行する必要がある日が来るでしょう。開発者は、新しいバージョンごとに重要な新機能を期待しています。変更の受け入れを拒否する代わりに、事前に計画を立てて、移行の準備をしてください。

関連： SQLインデックスの説明、Pt。 1