MySQL クエリの最適化: クエリが遅い原因と解決策

開発に携わっている友人、特に MySQL に関係のある友人は、非常に遅い MySQL クエリに遭遇することがあります。もちろん、数十のエントリではなく、数百万または数千万の大量のデータを意味します。

クエリが遅い場合の解決策を見てみましょう

開発者は、インデックスや制限 n のないステートメントをチェックすることがよくあります。これらのステートメントは、データベースに大きな影響を与える可能性があります。たとえば、数千万件のレコードを含む大きなテーブルを完全にスキャンする必要がある場合や、ファイルソートを継続的に実行する必要がある場合は、データベースとサーバーの IO に影響します。ミラーライブラリの状況は次の通りです。

オンラインデータベースに関しては、インデックスのないステートメントや制限のないステートメントに加えて、MySQL 接続が多すぎるという別の問題があります。これについて言えば、これまでの監視の実践を見てみましょう。

Zabbix などのオープンソースの分散監視システムを導入して、毎日のデータベース IO、CPU、接続数を取得します。
データ増加、iostat、vmstat、データサイズなどの週次パフォーマンス統計を展開します。
MySQL スローログコレクション、トップ 10 のリスト

以前はこれらの監視は完璧だと思っていましたが、MySQL ノードプロセス監視を導入した後、多くの欠点を発見しました。

最初のアプローチの欠点: Zabbix は大きすぎるため、監視は MySQL 内で行われません。大量のデータはあまり準備されておらず、通常は履歴データの確認に使用されます。
2 番目の方法の欠点: 週に 1 回しか実行されないため、多くの状況を検出して報告することができません。
3 番目のアプローチの欠点: ノードにスローログが多数ある場合、top10 は無意味になり、実行する必要がある定期的なタスクのステートメントが提供されることがよくあります。。あまり参考にならない

では、これらの問題をどのように解決し、調査すればよいのでしょうか?

トラブルシューティングとパフォーマンスのボトルネックの発見に関して、最も簡単に見つけて解決できる問題は、遅い MYSQL クエリとインデックスのないクエリです。
さて、MySQL で実行するのが「快適」ではない SQL ステートメントを探し始めましょう。

方法 1 : 現在、この方法を使用しています。ハハ、この方法の即時性が気に入っています。

Mysql バージョン 5.0 以降では、実行速度が遅い SQL ステートメントの記録をサポートできます。

mysql> show variables like 'long%'; 注: この long_query_time は、クエリが何秒遅い場合に「遅いクエリ」と見なされるかを定義するために使用されます。
+-----------------+-----------+
| 変数名 | 値 |
+-----------------+-----------+
| 長いクエリ時間 | 10.000000 |
+-----------------+-----------+
セット内の 1 行 (0.00 秒)
mysql> set long_query_time=1; 注: これを 1 に設定しました。これは、実行に 1 秒以上かかるクエリは遅いクエリとみなされることを意味します。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
mysql> 'slow%' のような変数を表示します。
+---------------------+---------------+
| 変数名 | 値 |
+---------------------+---------------+
| 遅い起動時間 | 2 |
| slow_query_log | ON | 注: ログ記録をオンにするかどうか | slow_query_log_file | /tmp/slow.log | 注: 設定する場所+---------------------+---------------+
セット内の 3 行 (0.00 秒)
mysql> set global slow_query_log='ON' 注: ログ記録をオンにします

slow_query_log 変数を ON に設定すると、mysql はすぐにログ記録を開始します。
上記のMYSQLグローバル変数の初期値は/etc/my.cnfで設定できます。
long_query_time=1
slow_query_log_file=/tmp/slow.log

方法 2 : mysqldumpslow コマンド

/path/mysqldumpslow -sc -t 10 /tmp/slow-log
これにより、最も多くのレコードを含む上位 10 個の SQL ステートメントが出力されます。
-sソート方法を示します。 c 、 t 、 l 、およびr 、それぞれレコード数、時間、クエリ時間、および返されたレコード数でソートします。 ac 、 at 、 al 、およびar 、対応する逆ソートを示します。
-t上位 n を意味し、返されるレコードの数を意味します。
-g後には、大文字と小文字を区別しない正規表現マッチングパターンを続けることができます。
例えば
/path/mysqldumpslow -sr -t 10 /tmp/slow-log
最も多くのレコードを返す上位 10 件のクエリを取得します。
/path/mysqldumpslow -st -t 10 -g “left join” /tmp/slow-log
時間順に並べられた左結合を含む最初の 10 個のクエリステートメントを取得します。最後に、ノード監視の利点をまとめます。

軽量な監視、リアルタイム、実際の状況に応じてカスタマイズおよび変更可能
実行する必要があるステートメントをフィルタリングするためのフィルターが設定されます。
未使用のインデックスや不正なクエリをタイムリーに検出します。これらの遅いステートメントの処理には時間がかかりますが、データベースのクラッシュを回避するには価値があります。
データベースへの接続が多すぎる場合、プログラムは現在のデータベースのプロセスリストを自動的に保存します。これは DBA が原因を見つけるための優れたツールです。
mysqlbinlogを分析に使用すると、データベースの状態が異常な期間を明確に把握できます。

MySQLの設定ファイルを設定することを提案する人もいます

tmp_table_sizeを調整すると、他のパラメータが見つかりました
Qcache_queries_in_cacheキャッシュに登録されたクエリの数
Qcache_insertsキャッシュに追加されたクエリの数
Qcache_hitsキャッシュサンプル数
Qcache_lowmem_prunesメモリ不足のためキャッシュから削除されたクエリの数
Qcache_not_cachedされなかったクエリの数（キャッシュできなかったか、QUERY_CACHE_TYPE が原因）
Qcache_free_memoryクエリキャッシュの合計空きメモリ
Qcache_free_blocksクエリキャッシュ内の空きメモリブロックの数
Qcache_total_blocksキャッシュ内のブロックの総数を照会します
Qcache_free_memory 、よく使用されるクエリの一部をキャッシュできます。よく使用される SQL の場合は、メモリにロードされます。これにより、データベースのアクセス速度が向上します。

これで、MySQL クエリの最適化、クエリが遅くなる原因と解決策に関するこの記事は終了です。MySQL クエリの最適化に関する関連コンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

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