mysqlデータベースの最適化

mysql|データ|データベース|最適化

私は Oracle を学び始めることにしました。私は長い間 MySQL を使用してきました。以下は MySQL の使用に関する私の個人的な経験です。おそらく、多くのバグがあります。



まず、システムを高速化するために最も重要な部分は基本設計ですが、現状では克服できないことがいくつかあります。システムのボトルネック

私が考えることができるのは次のとおりです:

1: 高速ハードディスク (7200 rpm) では、理論的には 1 秒あたり 7200 回シークできます。これを変更する方法はありません。最適化方法は -- 複数のハード ドライブを使用するか、データを分散して保存します。

2: ハード ドライブの読み取りおよび書き込み速度。この速度は非常に高速です (私の限られた知識に限定されているため、これがそうであることだけがわかります)。 1 秒あたり数十、さらには数百 MB) これは解決が簡単です - 複数のハードドライブから並行して読み書きできます。これは、テーブルが存在する場合に最も一般的な制限要因です。

4: メモリの制限。CPU が CPU キャッシュに適した量を超えるデータを必要とする場合、キャッシュ帯域幅がメモリのボトルネックになります。しかし、メモリが驚くほど大きいため、この問題は一般的に発生します。

ステップ 2:

(学校 Web サイトの Linux プラットフォーム (Linux ADVX.Mandrakesoft.com 2.4.3-19mdk) を使用しています)

1: サーバー パラメーターを調整します

シェル>mysqld を使用します。 -help コマンドを使用して、すべての mysql オプションと利用可能なオプションのリストを生成します。 構成変数のテーブルを生成します。次の情報を出力します:

option--set-variable(-o) に使用できる変数は次のとおりです:

back_log current value:5 // mysql に必要な接続数 back_log は、mysql が接続の受け入れを一時停止していることを示します。一定期間内にスタックに保存できる接続リクエストの数

connect_timeout current value:5 //mysql サーバーが待機する時間。不正なハンドシェイクで応答する前に接続する(変換が容易ではない)

layed_insert_timeout 現在の値:200 //1 挿入遅延が終了する前に挿入を待機する時間

layed_insert_limit 現在の値:50 //挿入遅延プロセッサは、実行されていない選択ステートメントがある場合は、続行する前にこれらのステートメントを実行します

layed_queue_size 現在の値:1000 //挿入に割り当てられるキューの大きさです遅延

flush_time 現在の値:0 //に設定されている場合0 以外の場合、flush_time 時間ごとにすべてのテーブルが閉じられます

interactive_timeout current value:28800 //サーバーは閉じる前に対話します接続を待機する時間

join_buffer_size current value:131072 //すべての接続にバッファ サイズを使用します

key_buffer_size current value:1048540 //インデックスブロックのバッファーのサイズ。増やすとインデックスをより適切に処理できるようになります

lower_case_table_names current value:0 //

long_query_time current value:10 //クエリに時間がかかる場合今回は、slow_queried 数が増加します

max_allowed_pa​​cket 現在の値:1048576 //パケットのサイズ

max_connections 現在の値:300 //許可される同時接続数

max_connect_errors 現在の値:10 //それ以上の場合この中断された接続数を超えると、それ以上の接続はブロックされますが、これはホストをフラッシュすることで解決できます

max_layed_threads 現在の値:15 //開始できる処理遅延挿入の数

max_heap_table_size 現在の値:16777216 //

max_join_size 現在の値:4294967295 //読み取りが許可されている接続の数

max_sort_length 現在の値:1024 //BLOB またはテキストを並べ替えるときに使用されるバイト数

max_tmp_tables 現在の値:32 //オープンされる一時テーブルの数接続によって同時に実行します

max_write_lock_count current value:4294967295 //一定数の書き込みロックの後に読み取りロックが発生するように、mysqld を開始する値を指定します

net_buffer_length current value:16384 //通信バッファ -- クエリ時にこのサイズにリセットされます

query_buffer_size current value:0 //クエリ時のバッファ サイズ

record_buffer current value:131072 //各オーダー 各テーブルのスキャンされた接続によって割り当てられるバッファのサイズスキャンします

sort_buffer current value:2097116 //ソートされた各接続によって割り当てられるバッファのサイズ

table_cache current value:64 //すべての接続に対してオープンテーブルの数

thread_concurrency current value:10 //

tmp_table_size現在の値:1048576 //一時テーブルのサイズ

thread_stack 現在の値:131072 //各スレッドのサイズ

wait_timeout 現在の値:28800 //サーバー 接続を閉じるまでの待機時間 3



必要に応じて上記の情報を構成すると役に立ちます。



3 番目:

1: データベースに多数のテーブルを作成する場合、(テーブルを) 開く、閉じる、作成するという操作が行われます。非常に遅いです。

2: mysql はメモリを使用します

a: キー バッファー (key_buffer_size) はすべてのスレッドで共有されます

b: 各接続は、スタック (デフォルトは 64k、変数 thread_stack)、接続バッファー (変数 net_buffer_length)、および結果バッファー領域 (net_buffer_length) を使用します。 ) 特定の状況下では、接続バッファと結果バッファが max_allowed_pa​​cket に動的に拡張されます。

c: すべてのスレッドがベース メモリを共有します。

d: メモリ マッピングはありません。

e: 順次スキャンの各リクエストに読み取りバッファ (record_buffer) を割り当てます。 )

f: すべての結合は 1 つのパスで完了し、ほとんどの結合は一時テーブルなしでも完了できます。最も一時的なテーブルはメモリベース (ヒープ) テーブルです。

g: ソート要求は 1 つのソートバッファーと 2 つの一時テーブルを割り当てます。テーブル

h: すべての解析と計算はローカル メモリで行われます

i: 各インデックス ファイルは 1 回だけ開かれ、データ ファイルは同時実行スレッドごとに 1 回開かれます

j: テーブルの BLOB 列ごとに、バッファーは BLOB 値を読み取るために動的に拡張されます

k: 使用中のすべてのテーブルのテーブル ハンドラーはバッファーに保存され、FIFO として管理されます

l: mysqladmin flash-tables コマンドは、no であるすべてのテーブルを閉じます。使用期間が長くなり、現在の実行スレッドが終了すると、使用中のすべてのテーブルが閉じられるようにマークされます

3: mysql ロック テーブル

mysql 内のすべてのロックはデッドロックになりません

ロックの原理:

mysql のロック原理。 : a: テーブルがロックされていない場合はロックします。 b それ以外の場合は、ロック要求を書き込みロック キューに入れます。

読み取りロック:

mysql のロック原理: a: テーブルがロックされていない場合は、ロックします。 ; b それ以外の場合は、ロック要求を読み取りロック キューに入れます



テーブル内で多くの選択操作と挿入操作が行われる場合があります。一時テーブルに行を挿入し、場合によっては一時テーブルを使用することができます 実際のテーブルへの更新を記録します

a: low_priority 属性を使用して、特定の挿入、更新、または削除に低い優先順位を与えます

b: max_write_lock_count は、mysqld を開始する値 (通常は小さい) を指定します。これにより、一定数の書き込みロックがその後に表示されます

c: set sql_low_priority_updates=1 を使用すると、特定のスレッドからすべての変更をより低い優先順位で行うように指定できます

d: high_priority で select を指定します

e: insert.. ..select.... を使用する場合問題がある場合は、myisam テーブルを使用してください。これは同時選択と挿入をサポートしているためです。

4: 最も基本的な最適化は、インデックスがハード ディスク上のデータによって占有されるスペースを最小限に抑えることです。最小の列では、インデックスも最小になります。

a: 可能な限り最小のデータ型を使用します。

b: 可能であれば、テーブルの列を NOT NULL として宣言します。

c: 可能であれば、変数を使用します。 varchar などの統合データ型 (ただし、速度はある程度影響を受けます)

d: 各テーブルにはできるだけ短いプライマリ インデックスが必要です

e: 本当に必要なインデックスを作成します

f: Ifインデックスは最初の数文字にあります 文字に一意のプレフィックスがある場合は、このプレフィックスのみをインデックスします----mysql は文字列の一部に対するインデックス作成をサポートします

g: テーブルが頻繁にスキャンされる場合は、テーブルを分割してみてくださいより多くのテーブルに




ステップ 4

1: インデックスの使用については説明しません。インデックスの重要性やその機能については説明しません。

まず第一に。すべての mysql インデックス (プライマリ、ユニーク、インデックス) が B ツリーに保存されることを明確にする必要があります。 インデックスの主な用語:

a: 指定された条件でレコードをすばやく検索します。

b: 結合を実行するとき、他のテーブルから行を取得します

c: 特定のインデックス列の max() 値と min() 値を検索します

d: 使用可能なキーのプレフィックスを並べ替えまたはグループ化する場合、テーブルを並べ替えまたはグループ化します

e: クエリはデータ ファイルにアクセスせずに値の取得を最適化するために使用されます。一部のテーブルの列が数値であり、たまたま特定の列のプレフィックスである場合、高速化するために、インデックス ツリーから値を取り出すことができます

2: データの保存または更新のクエリ速度

補助金の執行により若干効率が低下します。

MySQL 関数は高度に最適化する必要があります。ベンチマーク (loop_count、expression) を使用して、クエリに問題があるかどうかを確認できます

クエリ速度の選択: 選択を行う場合。 ． ．どこ。 ． ．より速く、私が思いつくのはインデックス作成だけです。テーブルに対して myisamchk--analyze を実行すると、クエリをより適切に最適化できます。 myisamchk--sort-index--sort-records=1 を使用して、インデックスとデータを並べ替えるためのインデックスを設定できます。

３: mysql の最適化 where 句

3.1: 不要な括弧を削除する:

((a AND b) AND c OR (((a AND b) AND (a AND d)))> (a AND b AND c) OR (a AND b AND c AND d)

3.2: 定数を使用します

(a b>5 AND b=c AND a= 5

3.3:定数条件を削除します

(b>=5 AND b=5) OR (b=6 AND 5=5) OR (b=100 AND 2=3) > b=5 OR b=6

3.4: 定数インデックスで使用される式は 1 回だけ評価されます

3.5: あるテーブルには、テーブルから直接情報を取得するための where count(*) はありません

3.6: 他のテーブルのクエリ内のすべての定数 以前読んだ

3.7:あらゆる可能性を試した結果、外部接続テーブルに最適な接続の組み合わせが見つかります

3.8: order by 句と別の group by 句がある場合、または order by または group by に結合の最初のテーブルからのものではない列が含まれている場合は、一時テーブルを作成します

3.9: sql_small_result が使用されている場合は、msyqlインメモリテーブルを使用する

3.10: 各テーブルにクエリのインデックスを与え、行の 30% 未満にまたがるインデックスを使用します。

3.11 各レコードが出力される前に、having句に一致しない行をスキップします



４: 左結合を最適化します

mysqlでは、左結合bは次のように実装されています

a: テーブルbは依存しますtable a

b: テーブル a は leftjoin 条件で使用されるすべてのテーブルに依存します (b を除く)

c: すべての leftjoin 条件が where 句に移動されます

d: 合計 1 つのテーブルを除いてすべての結合の最適化が実行されます依存するすべてのテーブルの後で読み取ります。循環依存関係がある場合、エラーが発生します

e: すべての標準の where 最適化を実行します

f: a の where 句に一致する行があるが、b に一致する leftjoin 条件がない場合、 in b で生成され、NULL に設定されているすべての行

g: leftjoin を使用して一部のテーブルに存在しない行を検索し、where 部分に column_name IS NULL テストがある場合 (column_name は NOT NULL 列)。次に、左の結合条件に一致する行が見つかった後、mysql はそれ以上の行の検索を停止します

５: 制限を最適化します

a: mysql がテーブル全体をスキャンする必要があるときに、制限を使用して 1 つの行のみを選択する場合, その関数はインデックスと同等です

b: order by で limit # を使用する場合、mysql はテーブル全体をソートせずに # 行を見つけた場合にソートを終了します

c: limit # と unique を組み合わせる場合、mysql # 番目の行が見つかった場合、停止します

d: mysql が最初の # 行をクライアントに送信している限り、mysql はクエリを放棄します

e: 制限 0 は常に空のコレクションをすぐに返します。

f: 一時テーブルのサイズは、クエリを解決するために必要なスペースの量を計算するために制限 # を使用します

６: 挿入の最適化

レコードの挿入は以下で構成されます:

a: 接続 (３)

b: クエリをサーバーに送信 (2)

c: 分析クエリ (2)

d: レコードを挿入 (1*レコード サイズ)

e: インデックスを挿入 (1*インデックス)

f: 閉じる (1)

上記の数字は合計時間に比例することがわかります

挿入速度を向上させるいくつかの方法:

6.1: 1 つの接続から同時に多くの行を挿入する場合は、複数の値を指定して挿入を使用します。複数のステートメントを使用する

6.2: 別のステートメントから挿入する場合、多くの行を接続して挿入するには、遅延挿入ステートメントを使用する方が高速です

6.3: myisam を使用すると、テーブルに削除された行がない場合、その間に行を挿入できますselect:s を実行しています

6.4: テキストファイルを読み込む場合テーブルを使用する場合は、ファイル内のデータを読み込むを使用します。これは通常、insert

6.5 より 20

倍高速です。テーブルをロックしてから挿入できます。速度の主な違いは、すべての Insert ステートメントが完了した後、インデックス バッファーがハードディスクに 1 回だけ保存されることです。一般に、異なる挿入ステートメントを使用するよりも、複数回保存する方が高速です。すべての行を 1 つのステートメントで挿入できる場合、ロックは必要ありません。ロックすると、全体的な接続時間も短縮されます。ただし、一部のスレッドでは最大待機時間が増加します。例:

スレッド 1 は 1000 回の挿入を実行します

スレッド 2、3 および 4 は 1 回の挿入を実行します

スレッド 5 は 1000 回の挿入を実行します

ロックが使用されていない場合、2、3、4 は 1 と 5 より前に完了します。ロックが使用されている場合、2、3、4 はおそらく 1 と 5 の後に完了します。ただし、全体的な時間は 40% 短縮されるはずです。 MySQL では挿入、更新、および削除の操作が非常に高速であるため、行の連続した挿入または更新を約 5 回以上ロックすると、全体的なパフォーマンスが向上します。 1 行の挿入を大量に実行する場合は、テーブルのロックを実行し、その後時々 (約 1,000 行ごとに) テーブルのロックを解除して、他のスレッドがテーブルにアクセスできるようにすることができます。これでも良好なパフォーマンスが得られます。データの読み込みに関しては、load data infile は依然として非常に高速です。

ファイル内のデータのロードと挿入の速度を上げるには、キーワード バッファを拡張します。

７更新速度を最適化します

その速度は、更新されるデータのサイズと更新されるインデックスの数によって異なります

更新を高速化するもう 1 つの方法は、変更を延期し、多くの変更を 1 行ずつ行うことです。テーブルがロックされている場合、多くの変更を一度に 1 つずつ行うよりも行ごとに行う方が高速です

８削除速度を最適化します

レコードの削除にかかる時間はインデックスの数に比例します。レコードをより速く削除するには、テーブルからすべての行を削除する方が、テーブルの大部分を削除するよりもはるかに速くなります。 1.1 static myisam
を選択します。この形式は最もシンプルで安全な形式であり、ディスク形式の中で最も高速です。速度は、ディスク上でデータをいかに簡単に見つけられるかによって決まります。インデックスと静的形式を持つものをロックする場合、行の長さに数値を掛けるだけで簡単にロックできます。また、テーブルをスキャンするときは、ディスク読み取りを使用して毎回一定数のレコードを読み取るのが簡単です。このセキュリティは、静的 myisam ファイルへの書き込み中にコンピュータがクラッシュした場合、myisamchk は各行の開始位置と終了位置を簡単に把握できるため、通常は部分的に書き込まれたレコードを除くすべてのレコードを回復できるという事実から生まれます。 mysql 内のすべてのインデックスはいつでも再構築できます

1.2 動的 myisam

この形式では、各行にはその長さを示すヘッダーが必要です。変更中にレコードの長さが長くなると、複数の位置になる可能性があります。 optimize tablename または myisamchk を使用してテーブルを整理できます。同じテーブル内の varchar 列や blob 列のように、アクセス/変更される静的データがある場合は、断片化を避けるために動的列を別のテーブルに移動します。

1.2.1 オプションの myisampack ツールで生成された圧縮 myisam

1.2.2 メモリ

この形式は小規模/中規模のテーブルに役立ちます。共通のルックアップ テーブルを外部ヒープ テーブルにコピー/作成することで、複数のテーブルの結合を高速化することができます。これにより、同じデータを使用すると数倍高速になる可能性があります。

select tablename.a,tablename2.a from tablename,tablanem2,tablename3 where

tablaneme.a=tablename2.a、tablename2.a=tablename3.a、tablename2.c!=0;



高速化するには, 同じ列 (tablename1.a) を検索に使用するため、tablename2 と tablename3 の結合を使用して一時テーブルを作成できます。

\CREAATE ＴＥＭＰＯＲＡＲＹＴＡＢＬＥ test TYPE=HEAP

SELECT

tablename2.a as a2,tablename3.a as a3

FROM

tablenam2,tablename3

WHERE

tablename 2.a=テーブル名3.a および c=0;

SELECT tablename.a,test.a3 from tablename,test where tablename.a=test.a1;

SELECT tablename.a,test,a3,from tablename,test where tablename.a=test.a1 and ... .;



1.3 静的テーブルの機能

1.3.1デフォルトの形式。テーブルに varchar、blob、text 列が含まれていない場合に使用されます

1.3.2 すべての char、numeric、および 10 進数の列は列幅まで埋められます

1.3.3 非常に高速です

1.3.4 バッファリングが簡単です

1.3。 5 レコードが固定の場所にあるため、ダウン後の再構築が簡単

1.3.6 は、膨大な数のレコードが削除され、ストレージサイズを最適化しない限り、(myisamchk を使用して) 再編成する必要はありません

1.3.7 は通常、動的テーブル以上のものを必要としますストレージスペース



1.4 動的テーブルの機能

1.4.1 テーブルに varchar、blob、text 列が含まれる場合は、この形式を使用します

1.4.2 すべての文字列列は動的です

1.4.3 それぞれのレコードは先頭に 1 ビットが付加されます。

1.4.4 通常、固定長テーブルよりも多くのディスク領域が必要です。

1.4.5 各レコードは必要な領域のみを使用します。レコードが大きくなると、必要に応じて多くのセグメントに分割され、レコードの断片化が発生します。

1.4.6 行の長さを超える情報で行が更新されると、行は断片化されます。

1.4.7 レコードが複数のセグメントになる可能性があるため、システムダウン後にテーブルを再構築するのは困難です

1.4.8 動的サイズのレコードの予想される行の長さは、3＋（列数＋７）／８＋（数値
）
char列の数)+数値列のパックサイズ+文字列の長さ+(

NULL列の数+7)/8

各接続には6バイトのペナルティがあります。変更によりレコードのサイズが増大するたびに、動的レコードが結合されます。新しい接続はそれぞれ少なくとも 20 バイトであるため、次の増加は同じ接続内で行われる可能性があります。そうでない場合は、別の接続が存在します。 myisamchk - vicious を使用して、接続数を確認できます。すべての接続は myisamchk -r で削除できます。



1.5 圧縮テーブルの特徴

1.5.1 myisampack ユーティリティで作成された読み取り専用テーブル。

1.5.2 myisampack を使用しない接続で myisampack で圧縮されたテーブルを読み取れるようにするための解凍コードがすべての mysql ディストリビューションに存在します

1.5.3 占有するディスク領域は非常にわずかです

1.5.4 各レコードは個別に圧縮されます。レコードのヘッダーは、テーブルの最大レコード数に応じて固定長 (1 ～ 3 バイト) になります。各列は異なる方法で圧縮されます。よく使用される圧縮タイプは次のとおりです:

a: 通常、列ごとに異なるハフマン テーブル

b: 接尾辞の空白圧縮

c: 接頭辞の空白圧縮

d: 値 0 の数値を格納するために 1 ビットを使用します

e:整数列の値の範囲が狭い場合、その列は可能な限り最小の型を使用して格納されます。例: すべての値が 0 ～ 255 の場合、bigint は tinyint として保存できます

g: 列に取り得る値のセットが少数しかない場合、列の型は enum に変換されます

h:列は上記の圧縮方法の組み合わせを使用して保存できます

1.5.5は固定長または動的長のレコードを処理できますが、BLOBまたはテキスト列は処理できません

1.5.6はmyisamchkで解凍できます

MySQL はさまざまなインデックス タイプをサポートできますが、一般的なタイプは isam です。これは B ツリー インデックスであり、インデックス ファイルのサイズは (key_length+4)*0.67 (すべてのキーの合計) として大まかに計算できます。

文字列インデックスは空白が圧縮されます。最初のインデックスが文字列の場合、文字列列の末尾に多くの空白がある場合、または完全長の varchar 列である場合は、空白を圧縮するとインデックス ファイルが小さくなります。多くの文字列が同じプレフィックスを持つ場合。

1.6メモリテーブルの特徴

mysql内のヒープテーブルは、オーバーフローごとに100%動的ハッシュを使用しており、削除に関連する問題はありません。方程式を使用してアクセスできるのは、ヒープ テーブルのインデックスを使用する場合のみです (通常は ＇＝＇ 演算子を使用します)

ヒープ テーブルの欠点は次のとおりです:

1.6.1 同時に使用したいすべてのヒープ テーブルが必要です。十分な追加メモリが必要

1.6.2 インデックスの一部で検索できない

1.6.3 次のエントリを順番に検索できない (つまり、このインデックスを使用して順序付けを行う)

1.6。 4 MySQL は 2 つの値の間を計算できません スペースにはおよそ何行ありますか?これはオプティマイザがどのインデックスを使用するかを決定するために使用されますが、その一方で、ディスク シークも必要ありません