MySQLデータベースのパフォーマンスを最適化する8つの「妙手」

本文はMySQLデータベースの性能を向上させる構想を検討し,8つの面から具体的な解決方法を提示した.
　　
1、最も適切なフィールド属性を選択する
MySQLはビッグデータ量のアクセスをよくサポートしますが、一般的にはデータベース内のテーブルが小さいほど、その上で実行されるクエリーも速くなります.したがって、テーブルを作成するときに、より良いパフォーマンスを得るために、テーブル内のフィールドの幅をできるだけ小さくすることができます.例えば、郵便番号というフィールドを定義する際にCHAR(255)に設定すると、明らかにデータベースに不要なスペースが追加され、VARCHRのようなタイプを使用しても余計なことになります.CHAR(6)はタスクをうまく遂行できるからです.同様に、可能であれば、BIGINTではなくMEDIUMINTを使用して整数フィールドを定義する必要があります.
もう1つの効率化方法は、可能な限りフィールドをNOT NULLに設定することです.これにより、将来クエリーを実行するときに、データベースがNULL値を比較する必要がなくなります.
「省」や「性別」などのテキストフィールドの場合は、ENUMタイプとして定義できます.MySQLでは、ENUMタイプが数値型データとして扱われ、数値型データがテキストタイプよりも処理される速度がずっと速いからです.これにより、データベースのパフォーマンスを向上させることができます.
　
2、サブクエリ(Sub-Queries)の代わりにジョイン(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 salesinfo ON customerinfo.CustomerID=salesinfo.
CustomerID
WHERE salesinfo.CustomerID IS NULL

接続(JOIN)..より効率的なのは、MySQLがこの論理的に2つのステップを必要とするクエリーを完了するためにメモリにテンポラリ・テーブルを作成する必要がないためです.
　
3、手動で作成したテンポラリテーブルの代わりにユニオンを使用する
MySQLは4.0からUNIONクエリーをサポートしており、テンポラリ・テーブルを使用する必要がある2つ以上のSELECTクエリーを1つのクエリーにマージすることができます.クライアントのクエリー・セッションが終了すると、テンポラリ・テーブルが自動的に削除され、データベースが整然として効率的になります.UNIONを使用してクエリーを作成する場合は、UNIONをキーワードとして複数のSELECT文を接続するだけでよいので、すべてのSELECT文のフィールド数が同じであることに注意してください.次の例では、UNIONを使用したクエリを示します.

SELECT Name, Phone FROM client
UNION
SELECT Name, BirthDate FROM author
UNION
SELECT Name, Supplier FROM product

　　
4、事務
サブクエリ(Sub-Queries)、接続(JOIN)、およびUNIONを使用して、さまざまなクエリを作成できますが、すべてのデータベース操作が1つまたは少数のSQL文で実行できるわけではありません.より多くの場合、ある仕事を完了するために一連の文を使用する必要があります.しかし、この場合、この文ブロックのいずれかの文が実行中にエラーが発生すると、文ブロック全体の操作が不確定になります.2つの関連するテーブルにデータを同時に挿入すると、1つ目のテーブルが正常に更新された後、データベースに突然予期せぬ状況が発生し、2つ目のテーブルの操作が完了しないため、データが不完全になり、データベースのデータが破壊されることも考えられます.このような状況を回避するには、文ブロック内の各文が正常に操作されたか、失敗したかのいずれかのトランザクションを使用します.すなわち、データベース内のデータの一貫性と整合性を維持することができる.物事はBEGINキーワードで始まり、COMMITキーワードで終わる.その間のSQL操作が失敗すると、ROLLBACKコマンドはデータベースをBEGINが開始する前の状態に戻すことができます.

BEGIN;
INSERT INTO salesinfo SET CustomerID=14;
UPDATE inventory SET Quantity=11
WHERE item='book';
COMMIT;

トランザクションのもう1つの重要な役割は、複数のユーザーが同じデータ・ソースを同時に使用する場合、データベースをロックする方法を利用してユーザーに安全なアクセス方法を提供することであり、ユーザーの操作が他のユーザーに干渉されないことを保証することができます.
5、テーブルをロックする
トランザクションはデータベースの整合性を維持する非常に良い方法ですが、その独占性のため、特に大きなアプリケーションシステムでは、データベースのパフォーマンスに影響を与えることがあります.トランザクションの実行中にデータベースがロックされるため、他のユーザーリクエストはトランザクションが終了するまで一時的に待つしかありません.データベース・システムに少数のユーザーがいる場合
使用すると、トランザクションによる影響はあまり問題になりません.しかし、何千人ものユーザーが同時にデータベースシステムにアクセスし、例えば電子商取引サイトにアクセスすると、比較的深刻な応答遅延が発生します.
実際には、テーブルをロックする方法でより良いパフォーマンスを得ることができます.次の例では、テーブルをロックする方法で、前の例のトランザクションの機能を完了します.

LOCK TABLE inventory WRITE
SELECT Quantity FROM inventory
WHEREItem='book';
...
UPDATE inventory SET Quantity=11
WHEREItem='book';
UNLOCK TABLES

ここでは,SELECT文で初期データを取り出し,いくつかの計算によりUPDATE文で新しい値をテーブルに更新する.WRITEキーワードを含むLOCK TABLE文は、UNLOCK TABLESコマンドが実行されるまで、inventoryを挿入、更新、または削除するための他のアクセスがないことを保証します.
　　
6、外部キーの使用
テーブルをロックする方法は、データの整合性を維持しますが、データの関連性を保証することはできません.このとき、外部キーを使用することができます.たとえば、外部キーは、各販売レコードが存在する顧客を指すことを保証します.ここで、外部キーは、customerinfoテーブルのCustomerIDをsalesinfoテーブルのCustomerIDにマッピングすることができ、合法的なCustomerIDのないレコードは、salesinfoに更新または挿入されません.

CREATE TABLE customerinfo
(
CustomerID INT NOT NULL ,
PRIMARY KEY ( CustomerID )
) TYPE = INNODB;

CREATE TABLE salesinfo
(
SalesID INT NOT NULL,
CustomerID INT NOT NULL,
PRIMARY KEY(CustomerID, SalesID),
FOREIGN KEY (CustomerID) REFERENCES customerinfo
(CustomerID) ON DELETECASCADE
) TYPE = 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つのクエリも同じ結果を返しますが、後のクエリは前のクエリよりずっと速くなります.第三に、文字型フィールドを検索するときに、LIKEキーワードとワイルドカードを使用することがあります.これは簡単ですが、システムのパフォーマンスを犠牲にする代わりにします.たとえば、次のクエリは、テーブル内の各レコードを比較します.

SELECT * FROM books
WHERE name like "MySQL%"

しかし、次のクエリを置き換えると、結果は同じですが、速度はかなり速くなります.

SELECT * FROM books
WHERE name>="MySQL"and name<"MySQM"

最後に、変換プロセスによってインデックスが機能しないため、クエリでMySQLを自動タイプ変換しないように注意してください.