MySQLにおける遅いSQLの最適化の方向性について詳しく話しましょう

序文

システムの実行速度に影響を与える要因は多数あります。フロントエンド、バックエンド、データベース、ミドルウェア、サーバー、ネットワークなど、多面的であり、偶発的な場合もあります。問題を正確に特定するには、システムについてある程度理解し、独自の判断に基づいて問題の範囲を絞り込む必要があります。

今日は、他の最適化については触れませんが、データベースの最適化に焦点を当て、いくつかの最適化の方向性について説明します。

システムの最適化の方向性と同様に、データベースの最適化も多面的であり、SQL ステートメントの実行、データベース自体の状況などをカバーします。次に、MySQL データベース内の遅い SQL ステートメントの最適化の方向性について説明し、いくつかの最適化のアイデアを提供したいと思います。

SQL文の最適化

SQL ステートメントの最適化に関する記事は数多くあり、SQL の記述に関するガイドも数多くありますが、それらは基本的な開発をサポートするだけです。問題をトラブルシューティングしたい場合は、SQL の記述だけに留まらず、問題を発見するための全体的なプロセスが必要です。

今回の最適化の方向性は、遅いクエリSQLの発見、SQL実行プランの表示と解析、SQLの書き込みの最適化、インデックスの最適化など、いくつかの側面に大別されます。

遅いクエリSQLを記録する

MySQL でスロークエリ SQL をログに記録するには、MySQL の内部構成 (slow_query_log 構成) を使用します。

'%query%' のような show variables を使用すると、次の 3 つの関連する結果を照会できます。

長いクエリ時間 | 1.00000
slow_query_log | オフ
slow_query_log_file | /data/mysql/mysql_slow.log

これら 3 つのパラメータについて説明してください。

long_query_time: SQL クエリを低速クエリと区別するには、クエリ時間を指定する必要があります。この時間を超えるクエリは、低速クエリとして分類されます。このパラメータは時間範囲を設定するために使用されます。単位は秒で、小数点以下を設定できます。
slow_query_log: このパラメータは、スロークエリ SQL のログ記録を有効または無効にするスイッチです。オンまたはオフの 2 つのオプションがあります。デフォルト値はオフです。したがって、スロークエリ SQL のログ記録を有効にする場合は、手動で有効に設定する必要があることがわかります。
slow_query_log_file: スロークエリ SQL ログのファイルパス。自分で指定できます。

設定を変更する方法

方法は2つあります。

まず、my.ini または my.cnf ファイルを変更し、これら 3 つの構成を 1 つにまとめます。

2 番目: set 構文を使用して sqlplus で直接パラメータを変更しますが、mysql データベースを再起動すると無効になります。sql は次のとおりです。

グローバル long_query_time を 10 に設定します。

グローバル slow_query_log をオンに設定します。

グローバル slow_query_log_file を /data/mysql/mysql_slow.log に設定します。

この方法は再起動後に失敗するため、最初の方法を使用することをお勧めします。

スロークエリログを表示する

スロークエリログをクエリするにはどうすればよいでしょうか? 量が少ない場合は、ツールを使用する必要はなく、直接開くことができます。

量が多い場合は、mysqldumpslow ツールを使用してクエリを実行する方が便利です。

mysqldumpslow は、mysqld と同じタイプの実行スクリプトであり、コマンドラインで直接実行できます。具体的な使用方法は次のとおりです。

mysqldumpslow パラメータ:
-sは順序です
-----al 平均ロック時間
-----ar 平均リターン記録時間
-----平均クエリ時間（デフォルト）
-----c カウント
-----l ロック時間
-----r レコードを返す
-----t クエリ時間
-t、先頭、つまり先頭に何件のレコードが返されるか
-g、カスタム正規表現

たとえば、次のようになります。

mysqldumpslow -sr -t 5 /data/mysql/mysql_slow.log

最も多くのレコードを返す 5 つの低速クエリ SQL をクエリします。

後でテストライブラリを構築し、使用方法を詳しく説明する別の記事を書く予定です。

SQL実行プランの表示

実行プランを表示するキーワード: EXPLAIN

使い方

EXPLAIN SELECT * FROM TABLE_NAME を直接実行するだけです。

これについては、最初は簡単にお話ししようと思っていたのですが、長くなりすぎたので、次の記事に残しておきます。どうぞご了承ください。

SQL 書き込みの最適化

SQL の記述を最適化する方法はたくさんあります。ここではそのいくつかを整理しました。ご自身で確認して、不足している部分を補ってください。

クエリステートメントで使用される判定条件が等しい、より小さい、より大きいかどうかに関係なく、where の左側の条件クエリフィールドでは関数や式を使用しないでください。
select * を直接使用せず、クエリを実行する必要がある特定のテーブルフィールドを使用します。select * は完全なテーブルスキャンを使用し、インデックスは使用しません。
WHERE 句内の列に対する NULL チェックを避けてください。
WHERE では != または <> 演算子を使用しないでください。
IN の代わりに BETWEEN AND を使用してください。
一般的な検索条件のインデックスを作成する
InnoDB、MyISAM、MEMORY などの適切なストレージエンジンを選択します。さまざまなシナリオで異なるストレージエンジンを使用すると、より良い結果が得られます。
like %123% を使用するとインデックスは処理されませんが、like 123% を使用するとインデックスは処理されます。とても重要です! ！！
適切なフィールドタイプを選択します。
フィールドを設計するときは、NOT NULL を使用するようにしてください。

なぜ遅い SQL を管理する必要があるのでしょうか?

データベースの観点から: 各 SQL 実行は一定量の I/O リソースを消費します。SQL 実行の速度によって、リソースが占有される時間の長さが決まります。合計リソースが 100 で、遅い SQL ステートメントが合計 1 分間に 30 のリソースを占有すると仮定します。そして、この 1 分間に他の SQL ステートメントに割り当てることができるリソースの合計量は 70 となり、サイクルが継続されます。すべてのリソースが割り当てられると、すべての新しい SQL ステートメントがキューに入れられます。

アプリケーションの観点から見ると、SQL 実行時間が長いと待機時間が発生し、OLTP アプリケーションでのユーザーエクスペリエンスが低下します。

ガバナンスの優先事項

マスターデータベース -> スレーブデータベース
- 現在、データベースは基本的に読み取りと書き込みが分離されたアーキテクチャを採用しており、読み取りはスレーブデータベースで実行され、書き込みはマスターデータベースで実行されます。
- スレーブデータベースのデータはマスターデータベースからコピーされるため、マスターデータベースの待機時間が長くなると、スレーブデータベースとのレプリケーション遅延が増加します。
実行時間の長いSQL文が最初に管理される
同時実行性が高く、特定のテーブルへのアクセスが集中するタイプの SQL がある場合は、まずそれを管理する必要があります。

要約する

これはまだ完成には程遠いです。まだ多くの書き方のルールがあり、索引の確立についてはまだ議論されていません。皆さんが自分で本を読むための時間を残しておきます。皆さんが進歩することを願っています。

これで、MySQL の遅い SQL の最適化の方向性に関するこの記事は終了です。MySQL の遅い SQL の最適化の方向性に関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM を応援していただければ幸いです。

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