この記事では、MySQL データベースのパフォーマンスを向上させるためのアイデアを検討し、8 つの側面から具体的なソリューションを提供します。
1. 最も適切なフィールド属性を選択します。MySQL
は大量のデータへのアクセスを十分にサポートしますが、一般的に、データベース内のテーブルが小さいほど、テーブル上で実行されるクエリが高速になります。したがって、テーブルを作成するときに、パフォーマンスを向上させるために、テーブル内のフィールドの幅をできるだけ小さく設定できます。たとえば、郵便番号フィールドを定義する場合、CHAR(255) に設定すると、CHAR(6) で問題ないため、VARCHAR 型を使用しても明らかに冗長になります。同様に、可能であれば、整数フィールドを定義するには BIGIN の代わりに MEDIUMINT を使用する必要があります。
効率を向上させるもう 1 つの方法は、可能であればフィールドを NOT NULL に設定し、今後クエリを実行するときにデータベースが NULL 値を比較する必要がないようにすることです。
「都道府県」や「性別」などの一部のテキスト フィールドについては、ENUM タイプとして定義できます。 MySQL では ENUM 型は数値データとして扱われるため、数値データはテキスト型よりもはるかに高速に処理されます。このようにして、データベースのパフォーマンスを向上させることができます。
2. サブクエリ (サブクエリ) の代わりに結合 (JOIN) を使用します。MySQL
は 4.1 以降、SQL サブクエリをサポートします。この手法を使用すると、SELECT ステートメントを使用してクエリ結果の単一列を作成し、この結果を別のクエリのフィルター条件として使用できます。例えば、基本顧客情報テーブルにある注文のない顧客を削除したい場合、サブクエリを使用して、まず売上情報テーブルから注文を行ったすべての顧客のIDを取得し、その結果を次のテーブルに渡すことができます。以下に示すメインクエリ:
DELETE FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)
サブクエリを使用すると、論理的に複数のステップを一度に完了する必要がある多くの SQL 操作を完了でき、トランザクションやテーブルのロックも回避でき、記述も簡単です。ただし、場合によっては、サブクエリをより効率的な結合 (JOIN) に置き換えることができます。たとえば、注文レコードを持たないすべてのユーザーを取得したいとすると、次のクエリを使用できます:
SELECT * FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)
このクエリを完了するために接続 (JOIN).. を使用すると、速度が大幅に速くなります。特に salesinfo テーブルに CustomerID のインデックスがある場合、クエリは次のようになります。
SELECT * FROM customerinfo
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.
顧客ID
WHERE salesinfo.CustomerID IS NULL
データ パスを設定し、データベース データ ファイルを共有 NFS ディレクトリ (NAS サーバー) に配置します。
サービスを正常に開始および停止するには、PID および innioDB ファイルをサーバーのローカル ディレクトリに配置する必要があります
。 vi /etc/ my.cnf
[mysqld]
#データベースデータをインストールする場所
datadir=/data/mysqldata
#innoDB エンジンをインストールする場所
innodb_data_home_dir = /usr/local/mysql/data
innodb_log_group_home_dir= /usr/local/mysql/data
innodb_data_file_path=ibdata1: 50M;ibdata2:50M:autoextend
1106 cp ./support-files/mysql.server /etc/rc.d/init.d/
vi /etc/rc.d/init.d/mysql.server
コンパイル関連222 番目から始まる項目 の 2 行は、PID ファイルをサーバーのローカル ディレクトリに配置します:
pid_file=/usr/local/mysql/data/mysqlmanager-`/bin/hostname`.pid
server_pid_file=/usr/local/mysql /data/`/bin/ hostname`.pid は
MySQL の基本データベースをインストールします:
1123 mount 10.4.66.251:/data /data
1124 mkdir /data/mysqldata
1127 ./scripts/mysql_install_db --user=mysql
1145 chown -R mysql .mysql /data/mysqldata/
if 正常であれば、mysql が正常に起動することがわかります。
1146
/etc/rc.d/init.d/mysql.server
HA 高可用性の構成を停止します
。設定しないでください。 NFS マウントと mysql サービスは、サーバーの起動時に自動的に実行されます。
5. テーブルのロック
トランザクションはデータベースの整合性を維持するための非常に優れた方法ですが、その排他性により、特に大規模なアプリケーション システムでは、データベースのパフォーマンスが低下します。トランザクションの実行中はデータベースがロックされるため、他のユーザーのリクエストはトランザクションが終了するまで待つことしかできません。データベース システムを少数のユーザーだけが使用する場合、トランザクションの影響は大きな問題にはなりませんが、電子商取引 Web サイトへのアクセスなど、数千のユーザーがデータベース システムに同時にアクセスすると、重大な問題が発生します。応答の遅れ。
実際、場合によっては、テーブルをロックすることでパフォーマンスが向上することがあります。次の例では、ロック テーブル メソッドを使用して、前の例のトランザクション関数を完了します。
LOCK TABLE インベントリ書き込み
在庫から数量を選択
WHEREItem='本';
...
在庫設定を更新 数量=11
WHEREItem='本';
テーブルのロックを解除する
ここでは、SELECT ステートメントを使用して初期データを取得し、いくつかの計算を通じて UPDATE ステートメントを使用して新しい値をテーブルに更新します。 WRITE キーワードを含む LOCK TABLE ステートメントは、UNLOCK TABLES コマンドが実行される前に、挿入、更新、または削除のためのインベントリへの他のアクセスがないことを保証します。
6.
外部キーを使用してテーブルをロックする方法では、データの整合性は維持できますが、データの関連性は保証できません。現時点では、外部キーを使用できます。たとえば、外部キーを使用すると、各販売レコードが既存の顧客を指していることを確認できます。ここで、外部キーは、customerinfo テーブルの CustomerID を salesinfo テーブルの CustomerID にマップできます。有効な CustomerID のないレコードは更新されず、salesinfo に挿入されません。
テーブルの作成顧客情報
(
CustomerID INT NOT NULL 、
主キー (顧客ID)
) TYPE = INNODB
CREATE TABLE salesinfo
;
(
SalesID INT が NULL ではありません。
CustomerID INT が NULL ではありません。
主キー(顧客ID、販売ID)、
外部キー (顧客 ID) の参照 customerinfo
(CustomerID) ON DELETECASCADE
) タイプ = INNODB;
例のパラメータ「ON DELETE CASCADE」に注目してください。このパラメータにより、customerinfo テーブル内の顧客レコードが削除されると、salesinfo テーブル内の顧客に関連するすべてのレコードも自動的に削除されます。 MySQL で外部キーを使用する場合は、テーブルの作成時にテーブル タイプをトランザクション セーフな InnoDB タイプとして定義することを忘れないでください。このタイプは、MySQL テーブルのデフォルトのタイプではありません。定義された方法は、CREATE TABLE ステートメントに TYPE=INNODB を追加することです。例に示すように。
7. インデックスを使用すると、
特にコマンドの実行時にクエリ ステートメントに MAX()、MIN()、および ORDERBY が含まれる場合、データベース サーバーはインデックスを使用しない場合よりもはるかに高速に特定の行を取得できます。パフォーマンスの向上はより明らかです。それでは、どのフィールドにインデックスを付ける必要があるのでしょうか?一般的に、インデックスは JOIN、WHERE 判定、ORDER BY ソートに使用されるフィールドに構築する必要があります。多数の重複値を含むデータベース内のフィールドにインデックスを作成しないようにしてください。 ENUM タイプのフィールドの場合、customerinfo の "province".. フィールドなど、重複する値が多数存在する可能性が非常に高くなります。そのようなフィールドにインデックスを作成しても、逆に役に立たない可能性があります。データベースのパフォーマンスを低下させます。テーブルの作成時に適切なインデックスを同時に作成することも、ALTER TABLE または CREATE INDEX を使用して後でインデックスを作成することもできます。さらに、MySQL
全文インデックス作成と検索は、バージョン 3.23.23 以降でサポートされています。フルテキスト インデックスは MySQL では FULLTEXT 型のインデックスですが、MyISAM 型のテーブルでのみ使用できます。大規模なデータベースの場合、FULLTEXT インデックスを使用せずにテーブルにデータをロードし、ALTER TABLE または CREATE INDEX を使用してインデックスを作成すると、非常に高速になります。ただし、すでに FULLTEXT インデックスがあるテーブルにデータをロードすると、実行プロセスが非常に遅くなります。
8. 最適化されたクエリ ステートメント
ほとんどの場合、インデックスを使用するとクエリの速度が向上しますが、SQL ステートメントが不適切に使用されると、インデックスが本来の役割を果たせなくなります。以下に、注意すべきいくつかの側面を示します。まず、同じ型のフィールド間で比較演算を実行するのが最善です。 MySQL バージョン 3.23 より前では、これは必須条件でさえありました。たとえば、インデックス付き INT フィールドは BIGINT フィールドと比較できませんが、特殊な場合として、CHAR 型フィールドと VARCHAR 型フィールドが同じサイズの場合は比較できます。次に、インデックス付きフィールドを操作するために関数を使用しないようにしてください。
たとえば、DATE 型フィールドに対して YEAE() 関数を使用すると、インデックスは正常に機能しません。したがって、次の 2 つのクエリは同じ結果を返しますが、後者の方が前者よりもはるかに高速です。
SELECT * FROM order WHERE YEAR(OrderDate)<2001;
SELECT * FROM order WHERE OrderDate<"2001-01-01";
数値フィールドを計算する場合にも同じ状況が発生します。
SELECT * FROM inventory WHERE Amount/7<24;
SELECT * FROM inventory WHERE Amount<24*7;
上記の 2 つのクエリも同じ結果を返しますが、後者のクエリは前のクエリよりもはるかに高速です。 3 番目に、文字フィールドを検索するときに、LIKE キーワードとワイルドカードを使用することがあります。このアプローチは単純ですが、システムのパフォーマンスも犠牲になります。たとえば、次のクエリはテーブル内のすべてのレコードを比較します。
本から * を選択してください
WHERE name like "MySQL%"
ただし、次のクエリを使用すると、返される結果は同じですが、速度ははるかに速くなります: ..
SELECT * FROM Books
WHERE name>="MySQL" and name<"MySQM"
最後に、MySQL がクエリ内で自動型変換を実行しないように注意する必要があります。変換プロセスによってインデックスも無効になるためです。