MySQL の最適化: 高品質の SQL 文を書く方法

序文

インターネット上にはデータベースの最適化に関する情報や方法が数多くありますが、その多くは品質にばらつきがあり、要約が不十分で内容が冗長なものもあります。この記事では、26 個の最適化提案について詳しく紹介します。早速見ていきましょう。

1. SQL クエリを実行するときは、完全検索 select * ではなく、select + 特定のフィールドを使用するようにしてください。

反例:

学生から*を選択します。

良い例:

学生からID、名前、年齢を選択します。

理由：

リソースを節約し、CPU、IO、ネットワークのオーバーヘッドを削減するために、必要なフィールドのみが取得されます。
select * を使用してクエリを実行する場合、カバーインデックスは使用できないため、テーブルを返すクエリが実行されます。
特定のフィールドを使用すると、テーブル構造の変更による影響を軽減できます。

2. データベース操作に準備されたステートメントを使用する

理由：

プリコンパイルされたステートメントはプランを再利用し、SQLコンパイルに必要な時間を短縮できます。
動的SQLによって引き起こされるSQLインジェクション問題を解決できます
パラメータのみを渡す方がSQL文を渡すよりも効率的です。
同じ文を一度解析して複数回使用することで、処理効率を向上させることができます。

3. フィールドリストなしで挿入ステートメントを使用しないでください

反例:

値に挿入します（'a'、'b'、'c'）。

良い例:

t(a, b, c) に値 ('a','b','c') を挿入します。

理由：

フィールド名のない挿入ステートメントでは、どのフィールドに対応するのかを区別するのが難しく、完全な値しか挿入できないため、読みやすさが悪くなります。
テーブル構造が変更されると、修正が難しくなります。

4. where句で条件を接続するために、またはの使用を避けるようにしてください。

例: 共通インデックス userId を持つ新しいユーザーテーブルを作成します。テーブル構造は次のようになります。

テーブル `user` を作成します (  
`id` int(11) NOT NULL AUTO_INCREMENT,  
`user_id` int(11) NULLではない、  
`age` int(11) NOT NULL,  
`name` varchar(30) NOT NULL,  
主キー (`id`)、  
キー `idx_userId` (`userId`)
)ENGINE=InnoDB デフォルト文字セット=utf8;

ユーザーIDが1または年齢が18歳のユーザーを照会する

反例:

userid=1 または age =18 のユーザーから id、user_id、age、name を選択します。

良い例:

# union all を使用する 
userid=1 のユーザーから id、user_id、age、name を選択し、union all を実行し、age = 18 のユーザーから * を選択します。
# または、2 つの別々の SQL 文を記述します: select id, user_id, age, name from user where userid=1; select * from user where age = 18

理由：

またはを使用するとインデックスが無効になり、テーブル全体がスキャンされる可能性があります。
上記の age のようなインデックスのない or + フィールドの場合、userId インデックスを使用すると仮定しますが、age クエリ条件になると、テーブル全体をスキャンする必要があり、フルテーブルスキャン + インデックススキャン + マージの 3 つの手順が必要になります。フルテーブルスキャンから開始する場合は、1 回のスキャンで実行できます。
MySQL にはオプティマイザーがあります。効率とコストを考慮すると、OR 条件に遭遇するとインデックスが無効になることがあります。これは妥当なようです。

5. where 条件を使用してクエリを実行する場合は、冗長な行が返されることを避け、データ型の暗黙的な変換を回避するために、クエリするデータを制限します。

idがint型であると仮定して、id = 1のデータを照会します。

反例:

id = '1' の学生から id、name を選択します。

良い例:

id = 1 の学生から id、name を選択します。

理由：

必要なデータのみをクエリし、不要なデータを返さないようにして、コストを節約します。
暗黙的な変換によりインデックスが無効化される

6. where句のフィールドに対して式操作または関数変換を実行します。これにより、システムはインデックスの使用を中止し、テーブル全体のスキャンを実行します。

ユーザーテーブルの年齢フィールドにインデックスが付けられていると仮定し、そのデータをクエリします。

反例:

ユーザーから名前と年齢を選択します。年齢 - 1 = 20;

良い例:

age = 21 のユーザーから名前と年齢を選択します。

理由：

age にインデックスが追加されましたが、それに対する操作 query のため、インデックスは有効ではなく、効率が大幅に低下します。

7. where 句では != または <> 演算子の使用を避けてください。そうしないと、エンジンはインデックスの使用を中止し、テーブル全体のスキャンを実行します。

(MySQLに適用可能)

反例:

年齢 <> 18 のユーザーから年齢、名前を選択します。

良い例:

# 2 つの別々の SQL ステートメントを記述することを検討できます: select age,name from user where age < 18;
年齢が 18 を超えるユーザーから、年齢と名前を選択します。

理由：

!=と<>を使用するとインデックスが無効になる可能性があります

8. クエリを最適化するには、テーブル全体のスキャンを可能な限り回避するために、where および order by に関係する列にインデックスを作成することを検討する必要があります。

反例:

select name, age, address from user where address = '深圳' order by age;

良い例: インデックスを追加してクエリを実行する

テーブルユーザーを変更して、インデックス idx_address_age (アドレス、年齢) を追加します。

9. where句ではnullではなくデフォルト値を使用することを検討する

反例: (すべてのデータをチェックします)

年齢が null でないユーザーから user_id、name、age を選択します。

良い例:

# テーブルフィールド age をデフォルト値として null ではなく 0 に設定します
age > 0 のユーザーから user_id、name、age を選択します。
1
2

理由：

これは、is null または is not null が使用されている場合にインデックスが使用されないことを意味するものではありません。これは、MySQL のバージョンとクエリコストに関連しています。
MySQL オプティマイザは、インデックスを使用するコストがインデックスを使用しないコストよりも高いと判断した場合、必ずインデックスを破棄します。 !=、> isnull、is not null などの条件により、インデックスが無効になることがよくあります。実際、これは一般にクエリコストが高く、オプティマイザがインデックスを自動的に破棄するためです。
null 値をデフォルト値に置き換えると、インデックスを使用できることが多く、意味も比較的明確になります。

10. クエリ結果にレコードが1つしかない場合、または1つのレコードのみが必要な場合（最大値/最小値など）、制限1を使用することをお勧めします。

学生テーブルがあり、Tom という名前の人物を見つけたいとします。

テーブル「学生」を作成（  
`id` int(11) NULLではない、  
`name` varchar(50) デフォルト NULL,  
`age` int(11) デフォルト NULL,  
`date` datetime デフォルト NULL、  
`sex` int(1) デフォルト NULL,  
主キー (`id`)
)ENGINE=InnoDB デフォルト文字セット=utf8mb4;

反例:

name='Tom ' の学生から id、name を選択します。

肯定的な例

name='Tom ' の従業員から id、name を選択します。limit 1;

理由：

制限 1 ページングを追加すると、対応するレコードが見つかるとスキャンは続行されなくなり、効率が大幅に向上します。
name が唯一のインデックスである場合、limit 1 を追加する必要はありません。limit の存在は主にフルテーブルスキャンを防ぎ、パフォーマンスを向上させるためです。ステートメント自体がフルテーブルスキャンが必要ないことを予測できる場合、limit の有無によるパフォーマンスの違いはほとんどありません。

11. ページング制限ステートメントを最適化する

日常的にページングを行う場合、通常は limit を使用してページングを実現しますが、オフセットが特に大きい場合は、クエリの効率が低下します。

反例:

学生制限10000,10からID、名前、年齢を選択

良い例:

# 解決策1: 最後のクエリの最大レコード（オフセット）を返す
id > 10000 の学生から id、name を選択し、limit 10 を指定します。

# 解決策 2: order by + index select id, name from student order by id limit 10000,10;

# オプション 3: ビジネスで許可されている場合はページ数を制限する:

理由：

オフセットが大きい場合、Mysql はオフセットをスキップして後続のデータを直接フェッチするのではなく、まずオフセット + フェッチするエントリの数を追加し、前のオフセットセクションのデータを破棄してから返すため、クエリの効率が低下します。
最適化ソリューション 1 を使用すると、最後の最大クエリレコード (オフセット) が返されるため、オフセットをスキップすることができ、効率が大幅に向上します。
ソリューション 2 では order by + index が使用され、これによってもクエリの効率が向上します。
オプション 3 については、後でページングを確認する必要があるかどうかをビジネス側と話し合うことをお勧めします。ほとんどのユーザーは、あまり多くのページをスクロールして戻らないからです。

12. クライアントに返すデータが多すぎないようにし、制限ページングを使用する

ビジネス要件として、ユーザーが過去 1 年間に視聴した映画データの表示を要求するとします。

反例:

# すべてのデータを一度にクエリする select * from LivingInfo 
watchId = useIdの場合 
かつwatchTime >= Date_sub(now(),Interval 1 Y)

良い例:

# ページネーションクエリ select * from LivingInfo 
watchId = useIdの場合 
かつwatchTime>= Date_sub(now(),Interval 1 Y) 
制限オフセット、ページサイズ

# フロントエンドのページングの場合は、平均的なユーザーは多くのページをスクロールダウンする必要はないため、最初に最初の200レコードをクエリできます。select * from LivingInfo 
watchId = useIdの場合 
かつwatchTime>= Date_sub(now(),Interval 1 Y) 
制限 200 ;

13. 類似文を最適化する

LIKE を使用してあいまいキーワード検索を使用すると、LIKE によってインデックスが無効になる可能性があります。

反例:

学生から*を選択
WHERE name LIKE '%strive_day';
-- または%を使用してSELECT * FROM studentをラップします
WHERE name LIKE '%strive_day%';

良い例:

学生から*を選択
WHERE name LIKE 'strive_day%';

理由：

先頭に % を置くと、インデックス検索は使用されません。
キーワードの後に % を付けると、インデックスを使用して検索します。
キーワードを % で囲むと、インデックス検索は行われません。
先頭の%がない場合、末尾の%のみがインデックスクエリになります。

14. インデックス列にMySQL組み込み関数を使用しないようにする

例: 過去 7 日間にログインしたユーザーをクエリする (loginTime フィールドがインデックス化されていると仮定)

反例:

SELECT * FROM system_user ユーザー
ここで、Date_ADD(user.loginTime、間隔 7 DAY) >= now();

良い例:

SELECT * FROM system_user ユーザー
ここで、user.loginTime >= Date_ADD(NOW(),INTERVAL - 7 DAY);

理由：

インデックス列にMySQL組み込み関数を使用すると、インデックスが無効になります。
インデックス列に組み込み関数がない場合、インデックスクエリが実行されます。

15. 結合インデックスを使用する場合は、インデックス列の順序に注意し、通常は左端の一致原則に従います。

先頭に user_id、末尾に age がある結合インデックス (user_id、age) があるとします。

反例:

age = 10 のユーザーから user_id、name、age を選択します。

良い例:

# 左端の一致原則に従って、userid = 1、age = 21 の場合、user_id、name、age を user から選択します。
# 左端の一致原則に従って、userid = 1 の user から user_id、name、age を選択します。

理由：

(k1, k2, k3) のような結合インデックスを作成すると、(k1)、(k1, k2)、(k1, k2, k3) という 3 つのインデックスを作成するのと同じになります。これが左端の一致原則です。
結合インデックスは最左原則を満たしていないため、インデックスは一般的に無効になりますが、これも MySQL オプティマイザに関連しています。

16. 適切な場合はカバーインデックスを使用します。

カバーインデックスを使用すると、SQL ステートメントでテーブルに戻る必要がなくなります。インデックスにアクセスするだけで必要なデータをすべて取得できるため、クエリの効率が大幅に向上します。

反例:

# あいまいクエリと同様、インデックス選択なし user_id、name、age から user_id が '%123%' のようなユーザー

# id が主キーの場合、それは通常のインデックス、つまりカバーリング インデックスです。
userid が '%123%' のようなユーザーから user_id、name、age を選択します。

17. 冗長なインデックスと重複したインデックスを削除する

反例:

  キー `idx_userId` (`userId`)
  キー `idx_userId_age` (`userId`,`age`)

良い例:

  キー `idx_userId_age` (`userId`,`age`)
# userId のインデックスを削除します (KEY `idx_userId_age` (`userId`,`age`))
# 結合されたインデックス (A, B) は、(A) インデックスと (A, B) インデックスの両方を作成することと同じであるためです。

理由：

重複したインデックスを維持する必要があり、クエリを最適化するときにオプティマイザーがそれらを 1 つずつ考慮する必要があるため、パフォーマンスに影響します。

18. 内部結合、左結合、右結合、内部結合が推奨されます。左結合の場合、左のテーブルの結果はできるだけ小さくする必要があります。

内部結合: クエリのために 2 つのテーブルが結合されると、2 つのテーブルに完全に一致する結果セットのみが保持されます。

左結合が 2 つのテーブルに対して結合クエリを実行すると、右側のテーブルに一致するレコードがない場合でも、左側のテーブルのすべての行が返されます。

右結合が 2 つのテーブルに対して結合クエリを実行すると、左側のテーブルに一致するレコードがない場合でも、右側のテーブルのすべての行が返されます。

SQL 要件が満たされているという前提では、内部結合が優先されます。左結合を使用する場合は、左のテーブルのデータ結果をできるだけ小さくし、条件が許せば、処理のために左側に配置する必要があります。

反例:

tab1 t1 から名前と年齢を選択し、tab2 t2 で t1.age = t2.age を結合します (t1.id = 2)。

良い例:

名前と年齢を選択 (ID = 2 の tab1 から * を選択) t1 を左結合 tab2 t2 で t1.age = t2.age;

理由：

内部結合が等価結合である場合、返される行数は比較的少なくなるため、パフォーマンスは比較的向上します。
左結合を使用すると、左のテーブルのデータ結果が可能な限り小さくなり、条件が可能な限り左側で処理されるため、返される行数が比較的少なくなる可能性があります。

19. 挿入するデータが多すぎる場合は、バッチ挿入を検討してください

反例:

for(ユーザー u :リスト)
{ ユーザー(名前、年齢) に INSERT し、値(名前、年齢) を書き込みます}

良い例:

// 一度に 500 個の値を挿入し、user(name,age) の値をバッチで挿入します
<foreach collection="リスト" item="項目" index="インデックス" セパレーター=",">
 (#{アイテム名},#{アイテムの年齢})
</foreach>

理由：

バッチ挿入はパフォーマンスが良好で、時間の損失が削減されます。

20. distinctiveキーワードをできるだけ使わない

distinctive キーワードは通常、重複レコードをフィルタリングして重複しないレコードを返すために使用されます。 1 つのフィールドまたはいくつかのフィールドをクエリするときに使用すると、クエリに最適化効果をもたらします。ただし、フィールドの数が多い場合は、クエリの効率が大幅に低下します。

反例:

# 重複フィールドを削除します SELECT DISTINCT * from user;

良い例:

ユーザーから DISTINCT 名を選択します。

理由：

distinctive を使用したステートメントの CPU 時間と占有時間は、distinct を使用しないステートメントよりも長くなります。
多くのフィールドをクエリするときに、distinct を使用すると、データベースエンジンはデータを比較し、重複データを除外します。ただし、この比較とフィルタリングのプロセスは、システムリソースと CPU 時間を消費します。

21. テーブル結合は5つ以下にする

理由：

リンクされたテーブルの数が増えるほど、コンパイル時間とオーバーヘッドが大きくなります。
リンクされたテーブルの読みやすさは低いです。リンクされたテーブルをいくつかの小さな実行に分割すると、読みやすさが向上します。

22. データ量が多い場合に更新ステートメントを最適化する方法。

データ量が多い場合は、一度に大量のデータを変更または削除しないようにする必要があります。大量のデータを変更または削除すると、CPU 使用率が過度に高くなり、他の人のデータベースへのアクセスに影響します。

反例:

# 一度に 100,000 件または 100 万件以上のレコードを削除します。delete from user where id < 1000000;
# または、(User user: list) {delete from user;} のように、非効率的で時間のかかる単一のループ操作を使用します。

良い例:

# 一度に500個ずつなど、一括で削除する   
ID が 500 未満のユーザーを削除します
ID >= 500 かつ ID < 1000 のユーザーを削除します。
...
ID >= 999500 かつ ID < 1000000 のユーザーを削除します。

理由：

一度に大量のデータを削除/更新すると、ロック待機タイムアウト超過エラーが発生する場合がありますので、バッチで操作することをお勧めします。

23. 存在と存在を適切に使い分ける

テーブル A が会社の従業員テーブルを表し、テーブル B が部門テーブルを表しているとします。すべての部門のすべての従業員を照会する SQL ステートメントは次のようになります。

反例:

A から * を選択し、deptId が (B から deptId を選択) であるかどうかを確認します。

これは次のように書くことと同じです:

最初のクエリ部門テーブルB
BからdeptIdを選択
次に、部門deptIdでAの従業員を照会します。
A.deptId = B.deptId となる A から * を選択します

これは次のようにループ文に抽象化できます。

リスト<> 結果セット;    
for(int i = 0; i < B.length; i++) {
 for(int j = 0; j < A.length; j++) {
     A[i].id == B[j].idの場合{
         結果セットにA[i]を追加します。
            壊す;          
        }       
     }    
 }

同じクエリ機能を実現するためにexistsを使用することもできます。

存在する場合、A から * を選択します (A.deptId = B.deptId の場合、B から 1 を選択します)。

上記のコードは次のコードと同等です:

Aから*を選択し、最初にテーブルAからループします
A.deptId = B.deptId となるように B から * を選択し、テーブル B をループします。

なぜなら、exists クエリの理解は、まずメインクエリを実行し、データを取得してから、それをサブクエリに入れて条件を検証することです。検証結果 (true または false) に基づいて、メインクエリのデータ結果を保持できるかどうかが決定されます。

同様に、次のようなサイクルに抽象化することもできます。

リスト<>結果セット;    
for(int i = 0; i < A.length; i++) {
 for(int j = 0; j < B.length; j++) {
     A[i].deptId == B[j].deptIdの場合{
         結果セットにA[i]を追加します。
            壊す;          
            }       
        }    
    }

理由：

データベースの最も難しい部分は、それをプログラムにリンクしてリリースすることです。リンクが 2 回使用され、そのたびに数百万回のデータセットに対してクエリが実行され、2 回だけ実行されるとします。逆に、そのたびに別のクエリが実行されると、数百万のリンクが確立され、リンク解放アプリケーションが何度も繰り返されます。
MySQL の最適化の原則は、小さなテーブルが大きなテーブルを駆動し、小さなデータセットが大きなデータセットを駆動することで、パフォーマンスが向上するというものです。
外側のループが最も小さいものを選択する必要があります。つまり、B のデータ量が A のデータ量よりも少ない場合は、B を使用するのが適切です。B のデータ量が A のデータ量よりも大きい場合は、存在するものを選択するのが適切です。

24. できるだけ数値フィールドを使用します。フィールドに数値情報のみが含まれる場合は、文字タイプとして設計しないようにしてください。

反例:

`king_id` varchar(20) NOT NULL コメント '123'

良い例:

 `king_id` int(11) NOT NULL コメント '123'

理由：

数値フィールドと比較すると、文字フィールドではクエリと接続のパフォーマンスが低下し、ストレージのオーバーヘッドが増加します。

25. unionをunion allに置き換えてみる

検索結果に重複レコードがない場合は、union を union all に置き換えることをお勧めします。

反例:

ユーザーID = 1 のユーザーから * を選択します
連合
年齢が20のユーザーから*を選択

良い例:

ユーザーID = 1 のユーザーから * を選択します
すべて結合
年齢が20のユーザーから*を選択

理由：

union を使用すると、検索結果が重複しているかどうかに関係なく、それらをマージし、並べ替えてから最終結果を出力しようとします。
検索結果に重複レコードがないことがわかっている場合は、効率を向上させるために、union ではなく union all を使用します。

26. フィールドタイプが文字列の場合、where を使用するときは必ず引用符で囲んでください。そうしないと、インデックスが無効になります。

反例:

userid = 1 の場合、ユーザーから * を選択します。

良い例:

userid = '1' のユーザーから * を選択します。

理由：

最初のステートメントは、一重引用符で囲まれていない場合はインデックスを使用しません。これは、一重引用符がないと文字列が数値と比較され、その型が一致しないためです。MySQL は暗黙的な型変換を実行し、比較のためにそれらを浮動小数点数に変換します。

要約する

これで、MySQL の最適化と高品質の SQL 文の書き方に関するこの記事は終了です。MySQL の最適化 SQL 文に関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: