《深入浅出MySQL》読書ノート5:SQL最適化
4399 ワード
一、SQL最適化の一般手順
1、まず実行効率に問題があるSQLを特定する必要がある:
a、実行効率に問題があるSQLは一般的に時間がかかり、業務は感知され、位置決めを助けることができる.
b、MySQL起動時にslow_を設定するquery_logスロークエリーログlong_を開くquery_timeは、遅いクエリー時間のしきい値を設定します.これにより、遅いクエリーログを使用して、効率に問題のあるSQLを特定できます.
2、explainでsqlの実行計画を分析し、遅いクエリーsqlに対して主にpossible keysとkeyを見て、インデックスの使用状況を分析する.
keyがnullの場合、sqlはインデックスを付けません.
原因:1)、インデックスがなく、2)を作成する必要があります)、インデックスが存在しますがmysqlはインデックスを移動しないことを選択します
インデックスを使用する場合:a、複数のカラムインデックス、クエリー条件インデックスの一番左のカラムを使用
b、like'pattern%'の場合%は後
c、大きなテキストを検索し、like'%pattern%'ではなく全文インデックスを確立する
d、カラムnameにインデックスを作成する場合、where name is nullはインデックスを使用する
インデックスが存在するが、インデックスが適用されない場合:
a、範囲クエリーテーブルの大部分のデータの場合、MySQLが全テーブルスキャンがインデックスより適切であると判断した場合、インデックスは使用しない
b、orで区切られた条件を使用し、すべての条件列にインデックスが作成された場合にインデックスが使用される
c、列タイプは文字列タイプで、クエリー時に引用符を付けないとインデックスは使用されません(タイプの変換は行われますが)、列タイプは整数クラスタイプで、クエリー時に引用符を付けてもインデックスは移動します
二、最適化の一般的な方法
1、テーブルの最適化
MyISAMとInnoDBタイプのテーブルでは、定期的にテーブルを分析したり、テーブルをチェックしたり、テーブルを最適化したりすることができます.
2、SQL最適化
a、INSERTの最適化:同じ顧客が複数のデータを挿入する時、すべてのデータを1本のSQL文に組み立てて提出する;異なるクライアントは複数行を挿入し、insert delayed文を使用することができ、データのリアルタイム性に対する要求が高くない場合に使用することができ、すぐにデータが本当にファイルを挿入していないのではなく、メモリのキューに保存して挿入を待つためである.データベースを構成したり、テーブルの作成時にデータディレクトリとインデックスディレクトリを指定して異なるディスクに保存したりすることができ、平均的にディスクIOを分散し、性能が向上します.1つのテキストファイルからテーブルをロードする場合、load data infileを使用すると、多くのinsert文を使用するよりも多くのinsert文を使用するよりも速くなります.
b、最適化GROUP BY:デフォルトでは、MySQLはすべてのgroup by d 1,d 2のフィールドを並べ替え、order by d 1,d 2を加えるのと同じである.
ソート操作には追加のオーバーヘッドが発生します.groupbyの結果がソートに要求されない場合は、order by null表示を追加してソートしないことを指定できます.
c、order by文の最適化:場合によっては、MySQLは、追加のソートを必要とせずにorder by句を満たすためにインデックスを使用することができます.order byインデックス使用条件:where条件とorder byは同じインデックスを使用し、order by順序とインデックス順序は同じで、order byのフィールドは昇順または降順です.
栗を挙げます.
d、ネストされたクエリーの最適化:場合によってはネストされたクエリーの代わりに接続操作を使用することができ、接続(JOIN)がより効率的である理由は、MySQLがメモリにテンポラリテーブルを作成する必要がないためである
e、OR条件の最適化:orのすべての条件列にインデックスを作成し、where a=2 or b=2 a、bにそれぞれ独立したインデックスがある場合、クエリーはインデックスを使用することができ、a、bに結合インデックスしかない場合、クエリーはインデックスを実行しません.
f、SQLヒントを使用する:use index、ignore index、force indexを使用して、関連インデックスを強制的に使用し、無視する.
3、データベースオブジェクトの最適化
a、実際の状況に応じて、または一定期間使用した後、統計結果に基づいて、フィールドのタイプをより適切なタイプと長さに選択または変更する
b、MyISAMタイプのテーブルは、分割してテーブルのアクセス効率を高めることができる
≪水平分割|Horizontal Split|emdw≫:各テーブルの構造は完全に同じですが、格納されているデータ・カテゴリは異なり、フィールド別に分類されます(地域、月).データ量が多く、成長が速く、データを分類できる場合に適しています.利点は、分割後、クエリー時に読むデータとインデックスのページ数を低減し、インデックスのレイヤ数を低減し、クエリー速度を向上させることです.難点は、複雑度が増加し、すべてのデータをクエリーするにはUNION操作が必要であることです.インデックスキーワードが大きくなく、データの増加速度がそれほど速くない場合、水平分割のクエリー速度は、分割しない場合と比較してインデックスを歩くクエリー速度が著しく向上しない可能性があり、水分分割による複雑さが大きな問題となっている.
≪垂直分割｜Vertical Split｜oem_src≫:プライマリ・キーと一部の列を1つのテーブルに、プライマリ・キーと別の列を別のテーブルに配置します.共通列と非共通列を分けることができ、分割するとデータ行が小さくなり、1つのデータページに多くのデータを格納でき、クエリー時にI/O回数が減少します.欠点は冗長列の管理であり、すべてのデータを照会するにはJOIN操作が必要である.
c、逆正規化-冗長列:テーブルに必要な冗長列を追加し、クエリー時の接続の必要性を低減します.問題は、冗長カラムの更新は、トリガを確立して冗長カラムを統一的に更新することを考慮できることです.
d、統計クエリーの場合、中間テーブルを使用してクエリーの速度を向上させることができます.例えば、ユーザーの月額請求書を統計するには、その月のデータをinsert intoを先に使用することができます.select * from... 作成した中間テーブルにインポートし、中間テーブルで統計操作を行います.利点:より高速で、必要に応じてインデックスを作成し、フィールドを増やして統計操作を支援できます.
show status like 'pattern%';
show variables like 'pattern%'; 1、まず実行効率に問題があるSQLを特定する必要がある:
a、実行効率に問題があるSQLは一般的に時間がかかり、業務は感知され、位置決めを助けることができる.
b、MySQL起動時にslow_を設定するquery_logスロークエリーログlong_を開くquery_timeは、遅いクエリー時間のしきい値を設定します.これにより、遅いクエリーログを使用して、効率に問題のあるSQLを特定できます.
2、explainでsqlの実行計画を分析し、遅いクエリーsqlに対して主にpossible keysとkeyを見て、インデックスの使用状況を分析する.
keyがnullの場合、sqlはインデックスを付けません.
原因:1)、インデックスがなく、2)を作成する必要があります)、インデックスが存在しますがmysqlはインデックスを移動しないことを選択します
インデックスを使用する場合:a、複数のカラムインデックス、クエリー条件インデックスの一番左のカラムを使用
b、like'pattern%'の場合%は後
c、大きなテキストを検索し、like'%pattern%'ではなく全文インデックスを確立する
d、カラムnameにインデックスを作成する場合、where name is nullはインデックスを使用する
インデックスが存在するが、インデックスが適用されない場合:
a、範囲クエリーテーブルの大部分のデータの場合、MySQLが全テーブルスキャンがインデックスより適切であると判断した場合、インデックスは使用しない
b、orで区切られた条件を使用し、すべての条件列にインデックスが作成された場合にインデックスが使用される
c、列タイプは文字列タイプで、クエリー時に引用符を付けないとインデックスは使用されません(タイプの変換は行われますが)、列タイプは整数クラスタイプで、クエリー時に引用符を付けてもインデックスは移動します
二、最適化の一般的な方法
1、テーブルの最適化
MyISAMとInnoDBタイプのテーブルでは、定期的にテーブルを分析したり、テーブルをチェックしたり、テーブルを最適化したりすることができます.
analyze table b; ， SQL
check table b;
optimize table b; ， （varchar text blob） ， ， ，
2、SQL最適化
a、INSERTの最適化:同じ顧客が複数のデータを挿入する時、すべてのデータを1本のSQL文に組み立てて提出する;異なるクライアントは複数行を挿入し、insert delayed文を使用することができ、データのリアルタイム性に対する要求が高くない場合に使用することができ、すぐにデータが本当にファイルを挿入していないのではなく、メモリのキューに保存して挿入を待つためである.データベースを構成したり、テーブルの作成時にデータディレクトリとインデックスディレクトリを指定して異なるディスクに保存したりすることができ、平均的にディスクIOを分散し、性能が向上します.1つのテキストファイルからテーブルをロードする場合、load data infileを使用すると、多くのinsert文を使用するよりも多くのinsert文を使用するよりも速くなります.
b、最適化GROUP BY:デフォルトでは、MySQLはすべてのgroup by d 1,d 2のフィールドを並べ替え、order by d 1,d 2を加えるのと同じである.
explain select d2, count(d2) from b group by d2;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: index
possible_keys: d1
key: d1
key_len: 9
ref: NULL
rows: 7
filtered: 100.00
Extra: Using index; Using temporary; Using filesort
1 row in set, 1 warning (0.00 sec)
Extra Using filesort ソート操作には追加のオーバーヘッドが発生します.groupbyの結果がソートに要求されない場合は、order by null表示を追加してソートしないことを指定できます.
c、order by文の最適化:場合によっては、MySQLは、追加のソートを必要とせずにorder by句を満たすためにインデックスを使用することができます.order byインデックス使用条件:where条件とorder byは同じインデックスを使用し、order by順序とインデックス順序は同じで、order byのフィールドは昇順または降順です.
栗を挙げます.
：
SELECT * FROM t1 ORDER BY key_part1,key_part2,... ;
SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;
：
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC； --order by ASC DESC SELECT * FROM t1 WHERE key2=constant ORDER BY key1； -- ORDER BY
SELECT * FROM t1 ORDER BY key1, key2； -- ORDER BYd、ネストされたクエリーの最適化:場合によってはネストされたクエリーの代わりに接続操作を使用することができ、接続(JOIN)がより効率的である理由は、MySQLがメモリにテンポラリテーブルを作成する必要がないためである
e、OR条件の最適化:orのすべての条件列にインデックスを作成し、where a=2 or b=2 a、bにそれぞれ独立したインデックスがある場合、クエリーはインデックスを使用することができ、a、bに結合インデックスしかない場合、クエリーはインデックスを実行しません.
f、SQLヒントを使用する:use index、ignore index、force indexを使用して、関連インデックスを強制的に使用し、無視する.
3、データベースオブジェクトの最適化
a、実際の状況に応じて、または一定期間使用した後、統計結果に基づいて、フィールドのタイプをより適切なタイプと長さに選択または変更する
b、MyISAMタイプのテーブルは、分割してテーブルのアクセス効率を高めることができる
≪水平分割|Horizontal Split|emdw≫:各テーブルの構造は完全に同じですが、格納されているデータ・カテゴリは異なり、フィールド別に分類されます(地域、月).データ量が多く、成長が速く、データを分類できる場合に適しています.利点は、分割後、クエリー時に読むデータとインデックスのページ数を低減し、インデックスのレイヤ数を低減し、クエリー速度を向上させることです.難点は、複雑度が増加し、すべてのデータをクエリーするにはUNION操作が必要であることです.インデックスキーワードが大きくなく、データの増加速度がそれほど速くない場合、水平分割のクエリー速度は、分割しない場合と比較してインデックスを歩くクエリー速度が著しく向上しない可能性があり、水分分割による複雑さが大きな問題となっている.
≪垂直分割｜Vertical Split｜oem_src≫:プライマリ・キーと一部の列を1つのテーブルに、プライマリ・キーと別の列を別のテーブルに配置します.共通列と非共通列を分けることができ、分割するとデータ行が小さくなり、1つのデータページに多くのデータを格納でき、クエリー時にI/O回数が減少します.欠点は冗長列の管理であり、すべてのデータを照会するにはJOIN操作が必要である.
c、逆正規化-冗長列:テーブルに必要な冗長列を追加し、クエリー時の接続の必要性を低減します.問題は、冗長カラムの更新は、トリガを確立して冗長カラムを統一的に更新することを考慮できることです.
d、統計クエリーの場合、中間テーブルを使用してクエリーの速度を向上させることができます.例えば、ユーザーの月額請求書を統計するには、その月のデータをinsert intoを先に使用することができます.select * from... 作成した中間テーブルにインポートし、中間テーブルで統計操作を行います.利点:より高速で、必要に応じてインデックスを作成し、フィールドを増やして統計操作を支援できます.