Mysqlチューニング:INNER JOINクエリーUsing temporary;Using filesort問題の最適化
5022 ワード
最近筆者は生産環境の中で非常に遅いsqlを発見した.時間は大体5 sぐらいで、そこでSQLシーンを変えてEXPLAIN分析を行って、1つが実行する過程の中で“Using temporary;Using filesort”に対して現れることを発見します.すなわち、実行中に結果を格納するテンポラリ・テーブルが生成され、ソート時に接続タイプとソート・キー値と一致条件を格納するすべての行の行ポインタに基づいてソートされます.合理的な最適化を経て、最終的にEXPLAIN後、詳細なSQL性能のボトルネックは発見されず、SQL実行時間は1 s以内である.この点の経験を記録した.
SQLと大まかなテーブル構造とデータ量を見てみましょう.
オーダーテーブルorderテーブル:15 wくらいのデータがあります
受注表に関連付けられた支払表:約11 wデータ
テーブル構造を大体理解した後、普通のsqlクエリーシーンを見て、すべての運送状のステータスが完了していない注文をクエリーし、その注文IDと支払い金額を取得し、注文時間によって順序を変更します.
SQLは簡単です.
そしてEXPLAIN分析により、
select_type
table
type
possible_key
key
rows
Extra
SIMPLE
op
ALL
i_order_id
NULL
110000
Using temporary; Using filesort
SIMPLE
o
eq_ref
PRIMARY
PRIMARY
150000
Using where
上記EXPLAINの各列の意味については本稿ではあまり詳しく述べないが,興味のあるものは筆者の他のブログを参照して紹介することができる.
まず、Using temporaryを理解します.Using filesortはいったい何なのか、なぜ性能殺し屋になったのか.
Using temporary
ソートにインデックスがない、unionを使用する、サブクエリを使用してクエリーを接続する、いくつかのビューを使用するなどの理由で、内部テンポラリ・テーブルが作成されたことを示します.ここでのテンポラリ・テーブルは、メモリ上のテンポラリ・テーブルであるか、ハード・ディスク上のテンポラリ・テーブルであるかのいずれかであることに注意してください.当然、メモリ・ベースのテンポラリ・テーブルの時間消費は、ハード・ディスク・ベースのテンポラリ・テーブルの実際の消費よりも小さいに違いありません.mysqlがテンポラリ・テーブルを作成する必要がある場合、メモリ・テンポラリ・テーブルとハード・ディスク・テンポラリ・テーブルのどちらを選択するかは、パラメータtmp_に依存します.table_sizeとmax_heap_table_size、メモリテンポラリテーブルの最大容量はtmp_table_sizeとmax_heap_table_size値の最小値です.必要なテンポラリ・テーブルの容量が両方の最小値より大きい場合、mysqlはハード・ディスク・テンポラリ・テーブルを使用してデータを格納します.
メモリベースでもハードディスクベースでも.テンポラリ・テーブルを構築するプロセスは、比較的時間がかかる操作です.
Using filesort
Using filesortの意味は、ファイルソートを使用するわけではありません.インデックスが使用されていないソートのみを表します.filesortで使用されるアルゴリズムはQuickSortであり,ソートが必要なレコードに対してメタデータをブロックソートし,mergesortメソッドを用いてブロックをマージする.filesortで使用できるメモリ領域のサイズはパラメータsort_です.buffer_sizeの値は、デフォルトでは2 Mです.ソートレコードが多すぎる場合sort_buffer_sizeが足りない場合、mysqlは一時ファイルを使用して各ブロックを格納し、各ブロックをソートした後、ブロックを複数回マージして最終的にグローバルにソートを完了します.個人的には、Using filesortは必ずしも性能殺し屋ではないと理解しています.Using filesortを最適化する唯一の方法は、インデックスフィールドをソートすることですが、実際の状況に応じて選択する必要があります.インデックスを確立するのも代価があります.
上記の2つの問題が発生した原因を理解した後,前のsqlは和のためにこの2つの問題が発生した.
Using filesortは言うまでもなく、インデックスがないためです.ポイントはUsing temporary.テンポラリ・テーブルが生成される理由.
MYSQLオプティマイザ:JOINでの順番選択
Mysql inner join結合文に遭遇した場合,MySQLテーブルに関連付けられたアルゴリズムはNest Loop Join(ネスト結合ループ)であり,Nest Loop Joinは2層のループ手段で順次マッチング操作を行い,最後に結果集合を返す.SQL文は、接続するオブジェクトとルールを記述するだけで、計画と実行操作を実行するには、実際に行のレコードを一致させる必要があります.Nest Loop Joinの操作過程は簡単で,我々の最も簡単なソート検索アルゴリズム,2層循環構造に似ている.接続する2つのデータセット(データテーブル)をそれぞれ外側テーブル(駆動テーブル)と内側テーブル(非駆動テーブル)と呼ぶ.Mysqlはどのようにして、どのテーブルが駆動テーブルで、どのテーブルが非駆動テーブルであるかを確定しますか?mysqlは、テーブルのデータが最も小さいテーブルを駆動テーブル(すなわちベーステーブル)とし、もう1つのテーブルを非駆動テーブルと呼び、まず駆動テーブルの各行が条件を満たすデータを処理し、その後の各行のデータと非駆動テーブルを接続マッチングし、ループが終了するまで結果を集計し、結果をユーザーに返す.ドライバ・テーブルのフィールドは直接ソートできますが、非ドライバ・テーブルのフィールド・ソートは、循環クエリーの集計結果(テンポラリ・テーブル)でソートする必要があるため、order by o.order_timeの場合、まずusing temporary(テンポラリテーブルを使用)が生成されます.
前にorderを知っていましたpaymentのデータ量が11 wしかないなら当然のorder_paymentはドライバテーブルです.したがって、using temporaryを避けるためには、orderをドライバテーブルとして使用する必要があり、このときSTRAGHT_JOINキーワードが来ました.
STRAIGHT_JOIN強指定ドライバテーブル
分析の結果、Using temporaryが消えていることがわかります.
Using filesortを解決するのは簡単ですorder_にあげるだけですtimeはインデックスを増やせばいいです.
ただし、このクエリーシーンではorder_のみを使用することはできません.timeフィールドにインデックスを追加すると、Using filesortは避けられません.InnerDBインデックスの特性上、B TREEツリーのリーフノードにはインデックス列データとプライマリキーIDが格納される.orderが1つしかない場合timeインデックスの場合、クエリー条件order_があるためstatusがあるので、インデックスは使用できません.
最適なインデックススキーム
これにより、B TREEツリーのリーフノードには、インデックス列データ(order_statusおよびorder_time)およびプライマリ・キーIDが格納される.このインデックスは、ソートとクエリーの両方を実行できます.上記の条件クエリーで最適なパフォーマンスを実現します.
最終SQL分析:
select_type
table
type
possible_key
key
rows
Extra
SIMPLE
o
ref
PRIMARY,i_order_status_time
i_order_status_time
150000
Using where; Using index
SIMPLE
op
ref
i_order_id
i_order_id
110000
検索時に大幅にアップグレードします.
SQLと大まかなテーブル構造とデータ量を見てみましょう.
オーダーテーブルorderテーブル:15 wくらいのデータがあります
CREATE TABLE `order` (
`id` char(32) NOT NULL COMMENT 'id',
`order_status` varchar(5) NOT NULL DEFAULT '0' COMMENT ' ',
`order_time` datetime DEFAULT NULL COMMENT ' ',
PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=' ';
受注表に関連付けられた支払表:約11 wデータ
CREATE TABLE `order_payment` (
`id` char(32) NOT NULL COMMENT 'id',
`order_id` char(32) NOT NULL DEFAULT '' COMMENT ' id',
`money` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT ' ',
PRIMARY KEY (`id`),
KEY `i_order_id` (`order_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT=' ';
テーブル構造を大体理解した後、普通のsqlクエリーシーンを見て、すべての運送状のステータスが完了していない注文をクエリーし、その注文IDと支払い金額を取得し、注文時間によって順序を変更します.
SQLは簡単です.
select o.id, op.money from `order` o
inner join order_payment op on o.id = op.order_id
where o.order_status = '4' order by o.order_time DESC;
そしてEXPLAIN分析により、
select_type
table
type
possible_key
key
rows
Extra
SIMPLE
op
ALL
i_order_id
NULL
110000
Using temporary; Using filesort
SIMPLE
o
eq_ref
PRIMARY
PRIMARY
150000
Using where
上記EXPLAINの各列の意味については本稿ではあまり詳しく述べないが,興味のあるものは筆者の他のブログを参照して紹介することができる.
まず、Using temporaryを理解します.Using filesortはいったい何なのか、なぜ性能殺し屋になったのか.
Using temporary
ソートにインデックスがない、unionを使用する、サブクエリを使用してクエリーを接続する、いくつかのビューを使用するなどの理由で、内部テンポラリ・テーブルが作成されたことを示します.ここでのテンポラリ・テーブルは、メモリ上のテンポラリ・テーブルであるか、ハード・ディスク上のテンポラリ・テーブルであるかのいずれかであることに注意してください.当然、メモリ・ベースのテンポラリ・テーブルの時間消費は、ハード・ディスク・ベースのテンポラリ・テーブルの実際の消費よりも小さいに違いありません.mysqlがテンポラリ・テーブルを作成する必要がある場合、メモリ・テンポラリ・テーブルとハード・ディスク・テンポラリ・テーブルのどちらを選択するかは、パラメータtmp_に依存します.table_sizeとmax_heap_table_size、メモリテンポラリテーブルの最大容量はtmp_table_sizeとmax_heap_table_size値の最小値です.必要なテンポラリ・テーブルの容量が両方の最小値より大きい場合、mysqlはハード・ディスク・テンポラリ・テーブルを使用してデータを格納します.
メモリベースでもハードディスクベースでも.テンポラリ・テーブルを構築するプロセスは、比較的時間がかかる操作です.
Using filesort
Using filesortの意味は、ファイルソートを使用するわけではありません.インデックスが使用されていないソートのみを表します.filesortで使用されるアルゴリズムはQuickSortであり,ソートが必要なレコードに対してメタデータをブロックソートし,mergesortメソッドを用いてブロックをマージする.filesortで使用できるメモリ領域のサイズはパラメータsort_です.buffer_sizeの値は、デフォルトでは2 Mです.ソートレコードが多すぎる場合sort_buffer_sizeが足りない場合、mysqlは一時ファイルを使用して各ブロックを格納し、各ブロックをソートした後、ブロックを複数回マージして最終的にグローバルにソートを完了します.個人的には、Using filesortは必ずしも性能殺し屋ではないと理解しています.Using filesortを最適化する唯一の方法は、インデックスフィールドをソートすることですが、実際の状況に応じて選択する必要があります.インデックスを確立するのも代価があります.
上記の2つの問題が発生した原因を理解した後,前のsqlは和のためにこの2つの問題が発生した.
Using filesortは言うまでもなく、インデックスがないためです.ポイントはUsing temporary.テンポラリ・テーブルが生成される理由.
MYSQLオプティマイザ:JOINでの順番選択
Mysql inner join結合文に遭遇した場合,MySQLテーブルに関連付けられたアルゴリズムはNest Loop Join(ネスト結合ループ)であり,Nest Loop Joinは2層のループ手段で順次マッチング操作を行い,最後に結果集合を返す.SQL文は、接続するオブジェクトとルールを記述するだけで、計画と実行操作を実行するには、実際に行のレコードを一致させる必要があります.Nest Loop Joinの操作過程は簡単で,我々の最も簡単なソート検索アルゴリズム,2層循環構造に似ている.接続する2つのデータセット(データテーブル)をそれぞれ外側テーブル(駆動テーブル)と内側テーブル(非駆動テーブル)と呼ぶ.Mysqlはどのようにして、どのテーブルが駆動テーブルで、どのテーブルが非駆動テーブルであるかを確定しますか?mysqlは、テーブルのデータが最も小さいテーブルを駆動テーブル(すなわちベーステーブル)とし、もう1つのテーブルを非駆動テーブルと呼び、まず駆動テーブルの各行が条件を満たすデータを処理し、その後の各行のデータと非駆動テーブルを接続マッチングし、ループが終了するまで結果を集計し、結果をユーザーに返す.ドライバ・テーブルのフィールドは直接ソートできますが、非ドライバ・テーブルのフィールド・ソートは、循環クエリーの集計結果(テンポラリ・テーブル)でソートする必要があるため、order by o.order_timeの場合、まずusing temporary(テンポラリテーブルを使用)が生成されます.
前にorderを知っていましたpaymentのデータ量が11 wしかないなら当然のorder_paymentはドライバテーブルです.したがって、using temporaryを避けるためには、orderをドライバテーブルとして使用する必要があり、このときSTRAGHT_JOINキーワードが来ました.
STRAIGHT_JOIN強指定ドライバテーブル
explain select o.id, op.money from `order` o straight_join order_payment op on o.id = op.order_id where o.order_status = '4' order by o.order_time DESC;
分析の結果、Using temporaryが消えていることがわかります.
Using filesortを解決するのは簡単ですorder_にあげるだけですtimeはインデックスを増やせばいいです.
ただし、このクエリーシーンではorder_のみを使用することはできません.timeフィールドにインデックスを追加すると、Using filesortは避けられません.InnerDBインデックスの特性上、B TREEツリーのリーフノードにはインデックス列データとプライマリキーIDが格納される.orderが1つしかない場合timeインデックスの場合、クエリー条件order_があるためstatusがあるので、インデックスは使用できません.
最適なインデックススキーム
create index i_order_status_time on `order`(`order_status`,`order_time`)
これにより、B TREEツリーのリーフノードには、インデックス列データ(order_statusおよびorder_time)およびプライマリ・キーIDが格納される.このインデックスは、ソートとクエリーの両方を実行できます.上記の条件クエリーで最適なパフォーマンスを実現します.
最終SQL分析:
select_type
table
type
possible_key
key
rows
Extra
SIMPLE
o
ref
PRIMARY,i_order_status_time
i_order_status_time
150000
Using where; Using index
SIMPLE
op
ref
i_order_id
i_order_id
110000
検索時に大幅にアップグレードします.