MYSQLの最適化をまとめる
4067 ワード
これは1年以上前に書いたものです.後で新しい最適化を共有する時間があります.結局、当時の認識は非常に浅い.
もし皆さんが異議があれば、後で補充してもいいです.いつでも更新します.
以下に大まかに列挙します.(補足をお願いします)
1.データベースの設計
データベースの設計をできるだけ小さくディスク領域を占める.
1).できるだけ小さい整数タイプを用いる.(mediumintはintより適切である).
2).このフィールドにnullが必要でない限り、可能な限り定義フィールドはnot nullである.(
このルールは、フィールドがKEYの場合にのみ適用されます)
3).長くなるフィールドがないvarcharのような場合は、charのような固定サイズの記録フォーマットを採用する.(CHARはいつもVARCRHRより速い
)
4).テーブルのプライマリインデックスはできるだけ短くする必要があります.これにより、各記録に名前マークがあり、より効率的になる.
5).必要なインデックスのみを作成します.インデックスはレコードの取得に役立ちますが、レコードの迅速な保存には役立ちません.常にテーブルの組合せフィールドで検索する場合は、これらのフィールドにインデックスを作成します.インデックスの第1の部分は最もよく使用されるフィールドである必要があります.常に多くのフィールドを使用する必要がある場合は、まずこれらのフィールドを多くコピーして、インデックスをより圧縮する必要があります.
(このMYISAMエンジンのみに適した表は、INNODBについては記録を保存する際にはあまり関係ありません.INNODBはトランザクションベースなので、記録を素早く保存したい場合、特に大量のインポート記録の場合)
6).すべてのデータは、データベースに保存する前に処理する必要があります.
7).すべてのフィールドにデフォルト値が必要です.
8).場合によっては、頻繁にスキャンされるテーブルを2つに分けると、より速くなります.動的フォーマット・テーブルをスキャンして関連レコードを取得する場合、より小さな静的フォーマット・テーブルを使用する場合があります.
(
具体的には、MYISAMテーブルのMERGEタイプ、およびMYISAMとINNODB共通のパーティションを示します.詳細はマニュアルを参照してください.
9).外部キーコンストレイントが使用されない場所では、できるだけ外部キーを使用しないでください.
2.システムの用途
1).MYSQLへの接続をタイムリーにオフにします.
2).explain複雑なSQL文.(
SELECT文が最適化されているかどうかを確認できます)
3).2つの関連テーブルを比較する場合は、比較するフィールドのタイプと長さが一致する必要があります.(
データが膨大なときにINDEXを構築)
4).LIMIT文はできるだけorder byまたはdistinctに従う.これにより1回のfull table scanを回避することができる.
5).テーブルのすべてのレコードを空にするには、delete from tablenameではなくtruncate table tablenameを使用することをお勧めします.
ただし、truncateはトランザクションでロールバックしないという問題があります.彼女はcreate table文を呼び出すからです.
(
Truncate Table文はまずテーブルを削除してから再構築します.これはファイル境界に属するので、自然に速いNが多いです.
実測例:
song 2はINNODBテーブルです.
mysql> select count(1) from song2;
+----------+
| count(1) |
+----------+
| 500000 |
+----------+
1 row in set (0.91 sec)
mysql> delete from song2;
Query OK, 500000 rows affected (15.70 sec)
mysql> truncate table song2;
Query OK, 502238 rows affected (0.17 sec)
mysql>
{
このマニュアルには、次のような詳細が記載されています.
13.2.9.TRUNCATE構文
TRUNCATE TABLEは、テーブルを完全に空にするために使用されます.論理的には、この文はすべてのローを削除するために使用される
DELETE文は同じですが、場合によっては使用上の違いがあります.
について
InnoDBテーブル、参照テーブルが必要な外部キー制限がある場合、
TRUNCATE TABLEは
DELETE上;そうでない場合は、クイック削除(テーブルのキャンセルと再作成)を使用します.使用
TRUNCATE TABLE再設定
AUTO_INCREMENTカウンタは、設定時に外部キーの制限があるかどうかを考慮しません.
他のストレージエンジンでは、
MySQL 5.1では、
TRUNCATE TABLEと
DELETE FROMでは以下の点が異なります.
・削除操作は、1行1行の削除行よりもかなり速く、テーブルをキャンセルして再作成します.
・削除操作は事務に対して安全であることを保証できない.トランザクションとテーブルのロック中に削除しようとすると、エラーが発生します.
・削除されたローの数が戻されない.
・テーブル定義ファイルのみ
tbl_name.frmは合法的であれば使用できます
TRUNCATE TABLEは、データまたはインデックスファイルが破壊された場合でも、テーブルを空のテーブルに再作成します.
・テーブルマネージャが最後に使用したことを覚えていない
AUTO_INCREMENT値ですが、最初からカウントします.たとえ
MyISAMと
InnoDBもそうです.
MyISAMと
InnoDBでは通常、シーケンス値は再使用されません.
・パーティション付きテーブルに使用する場合、
TRUNCATE TABLEはパーティションを保持します.つまり、パーティション定義(
.par)ファイルは影響を受けません.
TRUNCATE TABLEは
MySQLで採用されている
Oracle SQL拡張.
}
6).STORE PROCEDUREやUSER FUNCTIONが使えるとき.(
ROUTINEは常にサーバ側のオーバーヘッドを削減します)
7).1つのinsert文に複数のレコード挿入フォーマットを採用する.また、load data infileを用いる大量のデータを導入することは、単純なindertよりもはるかに速い.(
MYSQLでは、INSERT INTO TABLEQ VALUES(),(),...();)
(
また、MYISAMテーブルに大量のレコードを挿入する場合は、KEYSの後に無効にしてからKEYSを作成し、具体的な表現文を作成します.
ALTER TABLE TABLE1 DISABLE KEYS;ALTER TABLE TABLE1 ENABLE KEYS;
INNNODBテーブルについては挿入前にset autocommit=0にする.完了後:set autocommit=1;このほうが効率的です.
8).よくOPTIMIZE TABLEで破片を整理します.
9).また、dateタイプのデータは頻繁に比較する場合はunsigned intタイプに保存するのが早いです.
3.システムのボトルネック
1).ディスク検索
並列検索は、複数のディスクにデータを別々に格納ことで、検索時間を短縮することができる.
2).ディスク読み書き(IO)
複数のメディアから並列にデータを読み込むことができます.
3).CPUサイクル
データはメインメモリに格納.これにより,CPUの個数を増やしてこれらのデータを処理しなければならない.
4).メモリ帯域幅
CPUがより多くのデータをCPUのキャッシュに格納ようとすると、メモリの帯域幅がボトルネックとなる.
====
Another article more about tuning details:
[url]http://www.informit.com/articles/article.aspx?p=29406&seqNum=1[/url]
もし皆さんが異議があれば、後で補充してもいいです.いつでも更新します.
以下に大まかに列挙します.(補足をお願いします)
1.データベースの設計
データベースの設計をできるだけ小さくディスク領域を占める.
1).できるだけ小さい整数タイプを用いる.(mediumintはintより適切である).
2).このフィールドにnullが必要でない限り、可能な限り定義フィールドはnot nullである.(
このルールは、フィールドがKEYの場合にのみ適用されます)
3).長くなるフィールドがないvarcharのような場合は、charのような固定サイズの記録フォーマットを採用する.(CHARはいつもVARCRHRより速い
)
4).テーブルのプライマリインデックスはできるだけ短くする必要があります.これにより、各記録に名前マークがあり、より効率的になる.
5).必要なインデックスのみを作成します.インデックスはレコードの取得に役立ちますが、レコードの迅速な保存には役立ちません.常にテーブルの組合せフィールドで検索する場合は、これらのフィールドにインデックスを作成します.インデックスの第1の部分は最もよく使用されるフィールドである必要があります.常に多くのフィールドを使用する必要がある場合は、まずこれらのフィールドを多くコピーして、インデックスをより圧縮する必要があります.
(このMYISAMエンジンのみに適した表は、INNODBについては記録を保存する際にはあまり関係ありません.INNODBはトランザクションベースなので、記録を素早く保存したい場合、特に大量のインポート記録の場合)
6).すべてのデータは、データベースに保存する前に処理する必要があります.
7).すべてのフィールドにデフォルト値が必要です.
8).場合によっては、頻繁にスキャンされるテーブルを2つに分けると、より速くなります.動的フォーマット・テーブルをスキャンして関連レコードを取得する場合、より小さな静的フォーマット・テーブルを使用する場合があります.
(
具体的には、MYISAMテーブルのMERGEタイプ、およびMYISAMとINNODB共通のパーティションを示します.詳細はマニュアルを参照してください.
9).外部キーコンストレイントが使用されない場所では、できるだけ外部キーを使用しないでください.
2.システムの用途
1).MYSQLへの接続をタイムリーにオフにします.
2).explain複雑なSQL文.(
SELECT文が最適化されているかどうかを確認できます)
3).2つの関連テーブルを比較する場合は、比較するフィールドのタイプと長さが一致する必要があります.(
データが膨大なときにINDEXを構築)
4).LIMIT文はできるだけorder byまたはdistinctに従う.これにより1回のfull table scanを回避することができる.
5).テーブルのすべてのレコードを空にするには、delete from tablenameではなくtruncate table tablenameを使用することをお勧めします.
ただし、truncateはトランザクションでロールバックしないという問題があります.彼女はcreate table文を呼び出すからです.
(
Truncate Table文はまずテーブルを削除してから再構築します.これはファイル境界に属するので、自然に速いNが多いです.
実測例:
song 2はINNODBテーブルです.
mysql> select count(1) from song2;
+----------+
| count(1) |
+----------+
| 500000 |
+----------+
1 row in set (0.91 sec)
mysql> delete from song2;
Query OK, 500000 rows affected (15.70 sec)
mysql> truncate table song2;
Query OK, 502238 rows affected (0.17 sec)
mysql>
{
このマニュアルには、次のような詳細が記載されています.
13.2.9.TRUNCATE構文
TRUNCATE [TABLE] tbl_nameTRUNCATE TABLEは、テーブルを完全に空にするために使用されます.論理的には、この文はすべてのローを削除するために使用される
DELETE文は同じですが、場合によっては使用上の違いがあります.
について
InnoDBテーブル、参照テーブルが必要な外部キー制限がある場合、
TRUNCATE TABLEは
DELETE上;そうでない場合は、クイック削除(テーブルのキャンセルと再作成)を使用します.使用
TRUNCATE TABLE再設定
AUTO_INCREMENTカウンタは、設定時に外部キーの制限があるかどうかを考慮しません.
他のストレージエンジンでは、
MySQL 5.1では、
TRUNCATE TABLEと
DELETE FROMでは以下の点が異なります.
・削除操作は、1行1行の削除行よりもかなり速く、テーブルをキャンセルして再作成します.
・削除操作は事務に対して安全であることを保証できない.トランザクションとテーブルのロック中に削除しようとすると、エラーが発生します.
・削除されたローの数が戻されない.
・テーブル定義ファイルのみ
tbl_name.frmは合法的であれば使用できます
TRUNCATE TABLEは、データまたはインデックスファイルが破壊された場合でも、テーブルを空のテーブルに再作成します.
・テーブルマネージャが最後に使用したことを覚えていない
AUTO_INCREMENT値ですが、最初からカウントします.たとえ
MyISAMと
InnoDBもそうです.
MyISAMと
InnoDBでは通常、シーケンス値は再使用されません.
・パーティション付きテーブルに使用する場合、
TRUNCATE TABLEはパーティションを保持します.つまり、パーティション定義(
.par)ファイルは影響を受けません.
TRUNCATE TABLEは
MySQLで採用されている
Oracle SQL拡張.
}
6).STORE PROCEDUREやUSER FUNCTIONが使えるとき.(
ROUTINEは常にサーバ側のオーバーヘッドを削減します)
7).1つのinsert文に複数のレコード挿入フォーマットを採用する.また、load data infileを用いる大量のデータを導入することは、単純なindertよりもはるかに速い.(
MYSQLでは、INSERT INTO TABLEQ VALUES(),(),...();)
(
また、MYISAMテーブルに大量のレコードを挿入する場合は、KEYSの後に無効にしてからKEYSを作成し、具体的な表現文を作成します.
ALTER TABLE TABLE1 DISABLE KEYS;ALTER TABLE TABLE1 ENABLE KEYS;
INNNODBテーブルについては挿入前にset autocommit=0にする.完了後:set autocommit=1;このほうが効率的です.
8).よくOPTIMIZE TABLEで破片を整理します.
9).また、dateタイプのデータは頻繁に比較する場合はunsigned intタイプに保存するのが早いです.
3.システムのボトルネック
1).ディスク検索
並列検索は、複数のディスクにデータを別々に格納ことで、検索時間を短縮することができる.
2).ディスク読み書き(IO)
複数のメディアから並列にデータを読み込むことができます.
3).CPUサイクル
データはメインメモリに格納.これにより,CPUの個数を増やしてこれらのデータを処理しなければならない.
4).メモリ帯域幅
CPUがより多くのデータをCPUのキャッシュに格納ようとすると、メモリの帯域幅がボトルネックとなる.
====
Another article more about tuning details:
[url]http://www.informit.com/articles/article.aspx?p=29406&seqNum=1[/url]