SQL の最適化 - samwellwang

一、なぜ SQL を最適化する必要があるのか
私たちがプロジェクトを開発している初期段階では、ビジネスデータの量が比較的少ないため、一部の SQL の実行効率がプログラムの実行効率に与える影響はあまり明らかではありません。また、開発者や運用担当者も SQL がプログラムの実行効率にどれほど影響を与えるか判断できないため、SQL に対して特別な最適化を行うことはほとんどありません。しかし、時間が経つにつれてビジネスデータの量が増加し、SQL の実行効率がプログラムの実行効率に与える影響が次第に大きくなります。このため、SQL の最適化が非常に重要になります。

二、SQL 最適化のいくつかの方法#

クエリを最適化し、全表スキャンを避けるようにし、まずは where および order by に関わる列にインデックスを作成することを検討してください。
where 句でフィールドに対して null 値の判断を行うことはできるだけ避けるべきです。そうしないと、エンジンはインデックスを使用せずに全表スキャンを行うことになります。例えば：
select id from t where num is null
num にデフォルト値 0 を設定し、表の num 列に null 値がないことを確認した後、次のようにクエリを実行できます：
select id from t where num=0
where 句で！= または <> 演算子を使用することはできるだけ避けるべきです。そうしないと、エンジンはインデックスを使用せずに全表スキャンを行います。
where 句で or を使用して条件を結合することはできるだけ避けるべきです。そうしないと、エンジンはインデックスを使用せずに全表スキャンを行います。例えば：
select id from t where num=10 or num=20
次のようにクエリを実行できます：
select id from t where num=10
union all
select id from t where num=20
in および not in も慎重に使用するべきです。そうしないと全表スキャンを引き起こすことになります。例えば：
select id from t where num in(1,2,3)
連続する数値の場合、between を使用できるなら in を使用しない方が良いです：
select id from t where num between 1 and 3
次のクエリも全表スキャンを引き起こします：
select id from t where name like ‘%abc%’
where 句でフィールドに対して式操作を行うことはできるだけ避けるべきです。そうしないと、エンジンはインデックスを使用せずに全表スキャンを行います。例えば：
select id from t where num/2=100
次のように変更すべきです：
select id from t where num=100*2
where 句でフィールドに対して関数操作を行うことはできるだけ避けるべきです。そうしないと、エンジンはインデックスを使用せずに全表スキャンを行います。例えば：
select id from t where substring (name,1,3)=’abc’–name が abc で始まる id
次のように変更すべきです：
select id from t where name like ‘abc%’
where 句の “=” の左側で関数、算術演算、または他の式演算を行わないでください。そうしないと、システムがインデックスを正しく使用できない可能性があります。
インデックスフィールドを条件として使用する場合、そのインデックスが複合インデックスであるなら、システムがそのインデックスを使用することを保証するために、インデックス内の最初のフィールドを条件として使用する必要があります。そうしないと、そのインデックスは使用されず、フィールドの順序をインデックスの順序とできるだけ一致させるべきです。
意味のないクエリを書くことは避けてください。例えば、空の表構造を生成する必要がある場合：
select col1,col2 into #t from t where 1=0
このようなコードは結果セットを返しませんが、システムリソースを消費します。次のように変更すべきです：
create table #t(…)
多くの場合、exists を in の代わりに使用することは良い選択です：
select num from a where num in(select num from b)
次の文に置き換えます：
select num from a where exists(select 1 from b where num=a.num)
すべてのインデックスがクエリに対して有効であるわけではありません。SQL は表のデータに基づいてクエリ最適化を行います。インデックス列に大量のデータの重複がある場合、SQL クエリはインデックスを利用しない可能性があります。例えば、ある表に sex というフィールドがあり、male と female がほぼ半分ずつ存在する場合、sex にインデックスを作成してもクエリ効率には影響しません。
インデックスは多ければ良いというわけではありません。インデックスは確かに対応する select の効率を向上させることができますが、同時に insert および update の効率を低下させます。
なぜなら、insert または update の際にインデックスが再構築される可能性があるからです。したがって、インデックスの作成方法は慎重に考慮し、具体的な状況に応じて判断する必要があります。
1 つの表のインデックス数は 6 個を超えない方が良く、あまりにも多い場合はあまり使用されない列にインデックスを作成する必要があるかどうかを検討するべきです。
数値型フィールドをできるだけ使用し、数値情報のみを含むフィールドは文字型として設計しない方が良いです。これにより、クエリや結合の性能が低下し、ストレージコストが増加します。
これは、エンジンがクエリや結合を処理する際に文字列の各文字を逐一比較する必要があるためであり、数値型の場合は一度の比較で済むからです。
できるだけ varchar を char の代わりに使用してください。なぜなら、可変長フィールドはストレージスペースが小さく、ストレージスペースを節約できるからです。
さらに、クエリにおいては、比較的小さなフィールド内で検索する方が効率が明らかに高くなります。
どこでも select * from t を使用しないでください。具体的なフィールドリストで “*” の代わりにし、使用しないフィールドを返さないようにしてください。
一時テーブルの頻繁な作成と削除を避け、システム表リソースの消費を減らしてください。
一時テーブルは使用できないわけではありません。適切に使用することで、特定のルーチンをより効率的にすることができます。例えば、大規模な表やよく使用される表のデータセットを繰り返し参照する必要がある場合です。しかし、一時的なイベントには、エクスポートテーブルを使用する方が良いです。
新しい一時テーブルを作成する際に、一度に挿入するデータ量が非常に大きい場合、create table の代わりに select into を使用して、大量のログを避け、速度を向上させることができます。データ量が少ない場合は、システム表のリソースを緩和するために、まず create table を行い、その後 insert を行うべきです。
一時テーブルを使用する場合、ストアドプロシージャの最後にすべての一時テーブルを明示的に削除することを必ず行ってください。最初に truncate table を行い、その後 drop table を行うことで、システム表の長時間ロックを避けることができます。
カーソルの使用はできるだけ避けてください。カーソルの効率は低いため、カーソル操作のデータが 1 万行を超える場合は、書き換えを検討すべきです。
カーソルベースの方法や一時テーブル方法を使用する前に、問題を解決するためのセットベースの解決策を探すべきです。セットベースの方法は通常、より効果的です。
一時テーブルと同様に、カーソルも使用できないわけではありません。小規模なデータセットに対して FAST_FORWARD カーソルを使用することは、他の逐次処理方法よりも優れています。特に、必要なデータを取得するために複数のテーブルを参照しなければならない場合です。
結果セットに “合計” を含むルーチンは、カーソルを使用して実行するよりも通常速いです。開発時間が許す場合、カーソルベースの方法とセットベースの方法の両方を試して、どちらの方法がより良い結果をもたらすかを確認することができます。
大きなトランザクション操作をできるだけ避け、システムの同時実行能力を向上させてください。
クライアントに大きなデータ量を返すことはできるだけ避けてください。データ量が過大な場合は、相応の要求が合理的かどうかを検討すべきです。