問題現象
ライン上のmysqlデータベースは、低速クエリをバースト、DBAは、クエリサーバのIOが急騰し、IO占有率が100％に達し、実行時間は約7秒まで観察しました。
SQL文は以下の通りです：
SELECT DISTINCT g.*, cp.name AS cp_name, c.name AS category_name, t.name AS type_name
FROM gm_game g LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0
LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0
WHERE g.deleted = 0 ORDER BY g.modify_time DESC LIMIT 20 ;
問題分析
explainを使って実行計画を見ると、結果は以下のようになります：
このSQL文の問題は、実はまだはっきりしています：
大量のデータをクエリし、tempテーブルのORDER BYを使用しましたが、やはり20個のデータしか返されませんでした。
DBAが観測した高いIOは、SQLステートメントがメモリに収まりきらない巨大な一時テーブルを生成したためで、すべてディスクにコピーされたため、IOが急増しました。
最適化プログラム
最適化の一般的な考え方は、SQLを分割し、すべての情報のクエリからソート操作を分離することです。
最初のステートメント：条件に一致するデータをクエリーします。
SELECT DISTINCT FROM gm_game g LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0 LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0 LEFTJOIN gm_type t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 ORDER BY g.modify_time DESC LIMIT 20 ；
2番目のステートメント：条件を満たす詳細データをクエリーし、inオペレーションを使って1番目のSQLの結果を2番目のSQLにスプライスします。
SELECT DISTINCT cp.name AS cp_name,c.name AS category_name,t.name AS type_name FROM gm_game g LEFT JOIN gm_cp cp ON cp.id = g.cp_id AND cp.deleted = 0LEFT JOIN gm_category c ON c.id = g.category_id AND c.deleted = 0 LEFT JOIN gm_type t ON t.id = g.type_id AND t.deleted = 0 WHERE g.deleted = 0 AND g.id in(.........................) ORDER BY g.modify_time DESC ；
実効
SATAマシンでテストしたところ、最適化前は約50秒、最適化後は1回目が0.3秒、2回目が0.1秒かかりました。
SSDマシンでテストしたところ、最適化前は約7秒、1回目は0.3秒、2回目は0.1秒、最適化後は0.1秒かかりました
ご覧の通り、最適化前はディスクのioが性能のボトルネックとなっており、SSDはSATAよりも大幅に高速でしたが、最適化後はディスクがボトルネックではなくなり、SSDとSATAの性能差はなくなりました。
理論分析
MySQL は SQL クエリを実行する際にテンポラリテーブルを使用することがあり、一般的にテンポラリテーブルの使用はパフォーマンスの低下を意味します。
- 一時テーブルの保存
MySQLのテンポラリテーブルは「メモリ内テンポラリテーブル」と「ディスク上テンポラリテーブル」に分けられ、メモリ内テンポラリテーブルはMySQLのMEMORYストレージエンジンを使用し、ディスク上テンポラリテーブルはMySQLのMyISAMストレージエンジンを使用します；
一般的に、MySQL はメモリ テンポラリ テーブルを最初に作成しますが、メモリ テンポラリ テーブルが設定で指定した値を超えた後、MySQL はメモリ テンポラリ テーブルをディスク テンポラリ テーブルにエクスポートします；
デフォルトは Linux プラットフォームの /tmp ディレクトリで、/tmp ディレクトリが小さいシステムでは注意が必要です。
- 一時テーブルの使用シナリオ
3) DISTINCT キーワードは ORDER BY ORDERY BY DISTINCT(price) で使用されます。
4) SQL_SMALL_RESULT キーワードが SELECT 文で指定されている SQL_SMALL_RESULT とは、MySQL に結果が小さいので、インデックス・ソートなしで直接メモリ内テンポラリ・テーブルを使用してくださいと伝えることを意味します。DISTINCTROW 通常はこのオプションを使用する必要はありません。
- ディスク一時テーブルを直接使用するシナリオ
1) テーブルに TEXT 列または BLOB 列が含まれている場合；
(2) GROUP BY 節または DISTINCT 節に、長さが 512 バイトを超える列が含まれています；
3) UNIONまたはUNION ALLを使用する場合、SELECT句に512バイトより大きな列が含まれます；
- 一時テーブルの構成
tmp_table_size: システムが作成するメモリ内テンポラリ・テーブルの最大サイズを指定 します。 //--.#___ze
max_heap_table_size: ユーザーが作成するメモリー・テーブルの最大サイズを指定 します。 //--.#を指定します。
メモ：最終的にシステムで作成されるメモリー・テンポラリ・テーブルのサイズは、上記の2つの設定値の最小値となります。
- テーブル設計の原則
一般的に、一時テーブルの使用はパフォーマンスの低下を意味し、特にディスク一時テーブルを使用する場合、パフォーマンスはさらに低下します。 一時テーブルを避ける一般的な方法は以下のとおりです：
1) インデックスの作成： ORDER BYまたはGROUP BYのカラムにインデックスを作成します；
(2）非常に長い列を分割：一般的に、TEXT、BLOB、文字列の512バイトよりも大きい、基本的に情報を表示するために、クエリの条件に使用されないので、テーブルの設計は、これらの列は、別のテーブルから独立している必要があります。
- SQL
テーブルの設計がすでに決まっていて、それを変更することが難しい場合は、SQL文の最適化によってSQLの実行効率を上げることで、一時テーブルのサイズを小さくすることもできます。
SQL文を最適化する一般的な方法を以下に示します：
1) SQL文の分割
一時的なテーブルは、主にソートやグループ化するために使用され、多くの企業がソートし、詳細なページングデータを取り出すために必要な、このケースでは、並べ替えやグループ化の効率を向上させるために、一時的なテーブルのサイズを小さくするために、別のSQLに分割することができます詳細なデータを取り出すことができます、ケースは、このメソッドを使用することです。
2) ソートやグループ化などの操作を削除して操作を最適化
時にはビジネスが実際に並べ替えたり、グループ化する必要はありません、ただ見栄えや読みやすくするために、このようなデータのエクスポート、データクエリおよびその他の操作など、この場合、ビジネスの並べ替えやグループ化を削除するには、多くの影響はありません。
- テンポラリ・テーブルが使用されているかどうかを確認するにはどうすればよいですか？
実行計画を表示するにはexplainを使用してください。Extra列では、Using temporaryは一時テーブルが使用されていることを意味します。
