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

公開: 2022-03-11

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

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

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

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

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

  1. 開発者にいくつかの開発者側のSQLパフォーマンス調整手法を提供します。
  2. 開発者とDBAがどのように効果的に連携できるかを説明します。

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

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

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

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

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

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

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

さらに、DBAは、挿入プロセスを高速化するために、100万行以上のバッチ挿入を実行する前に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開発者は、多くの場合、この方法でクエリを構造化することに気づきます。これは、通常、これが簡単な方法であるためです。

相関サブクエリの例を次に示します。

 SELECT c.Name, c.City, (SELECT CompanyName FROM Company WHERE ID = c.CompanyID) AS CompanyName FROM Customer c

特に、問題は、外部クエリ（ SELECT c.Name… ）によって返される行ごとに内部クエリ（ SELECT CompanyName… ）が実行されることです。 しかし、なぜ外部クエリによって処理されるすべての行について何度も何度もCompanyを調べるのでしょうか。

より効率的なSQLパフォーマンス調整手法は、相関サブクエリを結合としてリファクタリングすることです。

 SELECT c.Name, c.City, co.CompanyName FROM Customer c LEFT JOIN Company co ON c.CompanyID = co.CompanyID

この場合、最初にCompanyテーブルを1回だけ調べ、 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スニペットはすべて同じデータを返します。 ただし、一時テーブルを使用すると、たとえば、パフォーマンスを向上させるために一時テーブルにインデックスを作成できます。 ここでは、一時テーブルとサブクエリの違いについていくつかの良い議論があります。

最後に、一時テーブルを使い終わったら、データベースへの接続が終了したときのように、自動的に削除されるのを待つのではなく、一時テーブルを削除してtempdbリソースをクリアします。

 DROP TABLE #temp

SQLチューニング：「私のレコードは存在しますか？」

このSQL最適化手法は、 EXISTS()の使用に関係しています。 レコードが存在するかどうかを確認する場合は、 COUNT() ）の代わりにEXISTS()を使用してください。 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'

SQLServer2016を使用したSQLパフォーマンスの調整

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

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

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

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

つぶやき

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

  1. アプリケーションが突然動作を停止した場合は、データベースの問題ではない可能性があります。 たとえば、ネットワークに問題がある可能性があります。 DBAを非難する前に、少し調べてください。

  2. 忍者SQLデータモデラーの場合でも、DBAにリレーショナル図の作成を依頼してください。 彼らは共有し、提供することがたくさんあります。

  3. DBAは急速な変化を好みません。 これは当然のことです。データベース全体を分析し、あらゆる角度から変更の影響を調べる必要があります。 列の単純な変更は、実装に1週間かかる場合がありますが、それは、エラーが会社にとって大きな損失として顕在化する可能性があるためです。 我慢して！

  4. SQL DBAに、実稼働環境でデータを変更するように依頼しないでください。 本番データベースにアクセスする場合は、すべての変更について責任を負う必要があります。

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

  1. データベースについて質問されるのが気に入らない場合は、リアルタイムのステータスパネルを提供してください。 開発者は常にデータベースのステータスに疑いを持っており、そのようなパネルはすべての人の時間とエネルギーを節約することができます。

  2. テスト/品質保証環境で開発者を支援します。 実際のデータに対する簡単なテストで、本番サーバーを簡単にシミュレートできます。 これは、自分だけでなく他の人にとっても大幅な時間の節約になります。

  3. 開発者は、ビジネスロジックが頻繁に変更されるシステムに一日中費やしています。 この世界がより柔軟であることを理解し、重要な瞬間にいくつかのルールを破ることができるようにしてください。

  4. SQLデータベースは進化します。 データを新しいバージョンに移行する必要がある日が来るでしょう。 開発者は、新しいバージョンごとに重要な新機能を期待しています。 変更を受け入れることを拒否するのではなく、事前に計画を立てて、移行の準備をしてください。

関連： SQLインデックスの説明、Pt。 1、Pt。 2、およびPt。 3