MySQLデータベース最適化の一般的な8つの方法

引用:
データベースの最適化については、ネット上には多くの資料と方法がありますが、多くの品質がバラバラで、一部の総括が不十分で、内容が冗長です.たまにこの文章を発見して、とても経典を総括して、文章の流量もとても大きくて、だから自分の総括文集の中で手に入れて、良質な文章を蓄積して、個人の能力を高めて、みんなの今後の開発の中でも役に立つことを望みます
 
1、最も適切なフィールド属性を選択する
MySQLはビッグデータ量のアクセスをよくサポートしますが、一般的にはデータベース内のテーブルが小さいほど、その上で実行されるクエリーも速くなります.したがって、テーブルを作成するときに、より良いパフォーマンスを得るために、テーブル内のフィールドの幅をできるだけ小さくすることができます.
例えば、郵便番号というフィールドを定義する際にCHAR(255)に設定すると、明らかにデータベースに不要なスペースが追加され、VARCHRのようなタイプを使用しても余計なことになります.CHAR(6)はタスクをうまく遂行できるからです.同様に、可能であれば、BIGINではなく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、テーブルをロックする
トランザクションはデータベースの整合性を維持する非常に良い方法ですが、その独占性のため、特に大きなアプリケーションシステムでは、データベースのパフォーマンスに影響を与えることがあります.トランザクションの実行中にデータベースがロックされるため、他のユーザーリクエストはトランザクションが終了するまで一時的に待つしかありません.1つのデータベース・システムに少数のユーザーしか使用していない場合、トランザクションによる影響はあまり問題になりません.しかし、何千人ものユーザーが同時にデータベースシステムにアクセスし、例えば電子商取引サイトにアクセスすると、比較的深刻な応答遅延が発生します.
実際には、テーブルをロックする方法でより良いパフォーマンスを得ることができます.次の例では、テーブルをロックする方法で、前の例のトランザクションの機能を完了します.

LOCK TABLE inventory WRITE SELECT quantity  FROM   inventory   WHERE Item='book';

...

UPDATE   inventory   SET   Quantity=11   WHERE  Item='book';UNLOCKTABLES

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

CREATE  TABLE   customerinfo( customerid   int primary key) engine = innodb;

CREATE  TABLE   salesinfo( 
        salesid int not null,
        customerid  int not null, 
        primary key(customerid,salesid),
        foreign key(customerid)  references  customerinfo(customerid) on delete cascade)engine = innodb;

例のパラメータ「on delete cascade」に注意してください.このパラメータは、customerinfoテーブルの顧客レコードが削除されると、salesinfoテーブルの顧客に関連するすべてのレコードが自動的に削除されることを保証します.MySQLで外部キーを使用する場合は、テーブルの作成時にテーブルのタイプをトランザクション・セキュリティ・テーブルInnoDBタイプとして定義することを忘れないでください.このタイプはMySQLテーブルのデフォルトタイプではありません.定義する方法は、CREATE TABLE文にengine=INNODBを付けることです.例のように.
7、索引の使用
インデックスは、データベースのパフォーマンスを向上させる一般的な方法です.特に、クエリー文にMAX()、MIN()、ORDERBYなどのコマンドが含まれている場合、データベース・サーバがインデックスなしよりもはるかに速い速度で特定のローを取得できるようにします.
では、どのフィールドにインデックスを作成すればいいですか?
一般に、インデックスは、JOIN、WHERE判定、ORDERBYソートに使用されるフィールドに確立されるべきである.データベース内の重複する値が多数含まれるフィールドにインデックスを作成しないでください.1つのENUMタイプのフィールドでは、大量の重複値が発生する可能性があります.
例えばcustomerinfoの「province」..このようなフィールドにインデックスを作成するのは役に立ちません.逆に、データベースのパフォーマンスを低下させる可能性もあります.テーブルを作成するときに適切なインデックスを同時に作成したり、ALTERTABLEまたはCREATEINDEXを使用して後でインデックスを作成したりすることができます.また、MySQLはバージョン3.23.23から全文インデックスと検索をサポートしています.全文インデックスはMySQLではFULLTEXTタイプのインデックスですが、MyISAMタイプのテーブルでのみ使用できます.大きなデータベースの場合、FULLTEXTインデックスのないテーブルにデータをロードし、ALTERTABLEまたはCREATEINDEXを使用してインデックスを作成すると、非常に高速になります.ただし、FULLTEXTインデックスがすでに存在するテーブルにデータをロードすると、実行プロセスが非常に遅くなります.
8、最適化されたクエリー文
ほとんどの場合、インデックスを使用するとクエリーの速度が向上しますが、SQL文が適切でない場合、インデックスは機能しません.
以下は注意すべきいくつかの面です.
a、まず、同じタイプのフィールド間で比較する操作が望ましい
MySQL 3.23版までは必須条件だった.たとえば、インデックスが作成されたINTフィールドとBIGINTフィールドを比較することはできません.ただし、特殊なケースとして、CHARタイプのフィールドとVARCHARタイプのフィールドのサイズが同じ場合、それらを比較することができる.
b、次に、インデックスが作成されたフィールドでは関数を使用しないようにする
たとえば、DATEタイプのフィールドでYEAE()関数を使用すると、インデックスが機能しなくなります.したがって、次の2つのクエリは、同じ結果を返しますが、後者は前者よりずっと速いです.
c、第三に、文字型フィールドを検索する際に、LIKEキーワードとワイルドカードを使用することがあります.これは簡単ですが、システムのパフォーマンスを犠牲にする代わりに、
たとえば、次のクエリは、テーブル内の各レコードを比較します.

SELECT  *  FROM  books  WHERE  name  like   "MySQL%"

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

SELECT  *  FROM  books  WHERE  name ＞=  "MySQL"  and  name  ＜"MySQM"

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