mysql パフォーマンス最適化ガイド

この記事の主な目的は、MySQL データベースを最適化する方法を紹介することです。定期的にテーブルを分析してチェックし、定期的にテーブルを最適化し、いくつかの一般的な SQL ステートメントを最適化することで、MySQL 開発でより効率的な SQL を作成する方法を学ぶことができます。

1. 通常の分析テーブルとチェックリスト

分析テーブルの構文は次のとおりです:

ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name[, tbl_name]...

テーブルのキーワード分布と分析結果により、システムは正確な統計情報を取得できるようになり、SQL が正しい実行計画を生成できるようになります。実際の実行計画が予期した実行計画と異なるとユーザーが感じた場合は、解析テーブルを実行すると問題が解決される可能性があります。分析中、テーブルは読み取りロックでロックされます。これは、MyISAM、DBD、および InnoDB テーブルで機能します。

たとえば、データテーブルを分析するには

analyze table table_name

チェックテーブルの構文は次のとおりです:

CHECK TABLE tb1_name[,tbl_name]...[option] ...option = {QUICK | MEDIUM | EXTENDED | CHANGED}

チェック テーブルの機能は、MyISAM テーブルと InnoDB テーブルでエラーがあるかどうかを確認することです。

CHECK TABLE は、ビュー定義で参照されているテーブルが存在しないなど、ビューにエラーがあるかどうかをチェックすることもできます。

2. テーブルを定期的に最適化します

最適化されたテーブルの構文は次のとおりです:

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tb1_name [,tbl_name]...

テーブルが削除された場合、または可変長行を含むテーブル (VARCHAR、BLOB、または TEXT 列を含むテーブル) にさらに変更を加えた場合は、テーブルの最適化に OPTIMIZE TABLE コマンドを使用する必要があります。このコマンドはテーブル内のスペース フラグメントをマージし、削除または更新によるスペースの無駄を排除できますが、OPTIMIZE TABLE コマンドは MyISAM、BDB、および InnoDB テーブルでのみ機能します。

例: テーブル table_name を最適化します

注: 分析、チェック、最適化の実行中はテーブルがロックされるため、関連する操作は MySQL データベースがビジーでないときに必ず実行してください。

一般的に使用される SQL の最適化

開発中によく使用される SQL ステートメントは、INSERT、GROUPBY などにすぎません。これらの SQL ステートメントを最適化するにはどうすればよいでしょうか?

1. データを大きなバッチで挿入します

load コマンドを使用してデータをインポートする場合、適切な設定を行うとインポート速度が向上します。

MyISAM ストレージ エンジン テーブルの場合、次の方法で大量のデータをすばやくインポートできます

コードは次のとおりです:

ALTER TABLE tb1_name DISABLE KEYS;
loading the data
ALTER TABLE tb1_name ENABLE KEYS;

DISABLE KEYS と ENABLE KEYS は、非-MyISAM テーブルの一意のインデックス。空ではない MyISAM テーブルに大量のデータをインポートする場合、これら 2 つのコマンドを設定するとインポートの効率が向上します。

空の MyISAM テーブルに大量のデータをインポートする場合、デフォルトでは、最初にデータがインポートされてからインデックスが作成されます。インデックスを設定する必要はありません。

データ infile '/home/mysql/text_txt' をテーブル text にロードします

InnoDB タイプのテーブルの場合、この方法ではデータのインポートの効率を向上させることはできませんが、InnoDB タイプのテーブルにはいくつかの方法があります。方法。

1. InnoDB タイプのテーブルは主キーの順序で保存されるため、インポートされたデータを主キーの順序で並べ替えることで、インポートされたデータの効率を効果的に向上させることができます。

2. データをインポートする前に SET UNIQUE_CHECKS=0 を実行して一意性検証をオフにします。インポートが完了したら、SET UNIQUE_CHECKS=1 を実行して一意性検証を復元します。これにより、インポートの効率が向上します。

3. アプリケーションが自動送信を使用する場合は、インポート前に SET AUTOCOMMIT=0 を実行して自動送信をオフにすることをお勧めします。インポートが完了したら、SET AUTOCOMMIT=1 を実行して自動送信をオンにします。これによってもインポートが改善されます。効率。

INSERT ステートメントを最適化する

データ INSERT を実行する場合、次の最適化方法を検討できます

1。1 人の顧客から同時に多数の行が挿入される場合は、複数の値テーブルの INSERT ステートメントを使用してみてください。これにより、クライアントと MySQL データベース間の接続とシャットダウンが大幅に短縮され、単一の INSERT ステートメントを個別に実行するよりも効率的になります。たとえば、次の 3 つの文を次のように変更します。 ( 1,2),(3,4),(5,6)...

2. 異なるクライアントから多くの行を挿入する場合、INSERT DELAYED ステートメントを使用すると高速化できます。

DELAYED は、INSERT ステートメントを即時に実行することを意味し、実際にはデータがメモリ キューに置かれ、LOW_PRIORITY の挿入はその逆になります。テーブルの読み取りおよび書き込み後に実行されます。

3. インデックス ファイルとデータ ファイルを別のディスクに保存します。

4. バッチ挿入を実行する場合は、Bulk_insert_buffer_size 変数の値を増やすことができます。ただし、これは MyISAM テーブルにのみ使用できます。

5. テキスト ファイルからテーブルをロードする場合は、LOAD DATA INFILE を使用します。 これは通常、多数の挿入ステートメントを使用するよりも約 20 倍高速です。

上記は、MySQL のパフォーマンスの最適化において注意が必要なその他の事項です。mysql のパフォーマンスを最大限に活用するために、これらを組み合わせてみましょう。