MySQLのインデックスとトランザクションの理解
10547 ワード
索引
1)インデックスとは?一般的なアプリケーションシステムでは、読み書きの割合は10:1程度であり、挿入操作と一般的な更新操作ではパフォーマンスの問題は少なく、本番環境では、最も多くの問題に直面し、最も問題が発生しやすいのか、複雑なクエリー操作なのか、クエリー文の最適化が重要であることは明らかです.クエリーの高速化といえば、インデックスに言及せざるを得ません.2)インデックスがあるのはなぜですか.インデックスはMySQLでは「キー」とも呼ばれ、ストレージエンジンがレコードをすばやく見つけるためのデータ構造です.インデックスは、特にテーブル内のデータ量がますます大きくなると、パフォーマンスに与えるインデックスの影響がますます重要になります.インデックス最適化は、クエリーのパフォーマンス最適化に最も有効な手段であるはずです.インデックスを使用すると、クエリーのパフォーマンスが数桁容易に向上します.インデックスは辞書の音順表に相当し、ある字を調べる場合、音順表を使用しない場合は、数百ページからページごとに調べる必要があります.3)インデックスの優劣:インデックスの優勢:迅速に検索でき、I/O回数を減らし、検索速度を速める;インデックスのグループ化とソートに基づいて、グループ化とソートを高速化できます.インデックスの劣勢:インデックス自体もテーブルなので、ストレージスペースを占有します.一般的に、インデックステーブルが占有するスペースはデータテーブルの1.5倍です.インデックステーブルのメンテナンスと作成には時間コストがかかり、このコストはデータ量が増加するにつれて増加します.インデックスを構築すると、データ・テーブルを変更すると同時にインデックス・テーブルを変更する必要があるため、データ・テーブルの変更(削除、追加、変更)の効率が低下します.2、MySQLでのインデックス作成のためのインデックスコードは以下の通りです.
索引の作成
索引の削除
索引の表示
expalinコマンドによるインデックスの使用状況の表示
索引の使用規則インデックス分類で一般的なインデックスタイプは、プライマリ・キー・インデックス、一意インデックス、一般インデックス、全文インデックス、組合せインデックスです.プライマリ・キー・インデックス:プライマリ・インデックス、プライマリ・キーpk_によるclolum(length)はインデックスを確立し、重複を許さず、空の値を許さない.≪一意のインデックス｜Unique Index｜oem_src≫:インデックスを作成するカラムの値は一意でなければなりません.NULL値を許可します.≪一般索引|General Index|emdw≫:表の一般列で構築された索引で、制限はありません.全文インデックス:大きなテキストオブジェクトのカラムで構築されたインデックス;≪コンポジット・インデックス｜Composite Index｜oem_src≫:複数の列を組み合わせて構築されたインデックスです.この複数の列の値にはNULL値は許可されません. インデックスを確立するのに適している場合、プライマリ・キーは自動的に一意のインデックスを確立する.よくクエリ条件としてWHEREまたはORDER BY文に現れる列にインデックスを設定します.ソートされたカラムとしてインデックスを作成します.クエリー内の他のテーブルに関連付けられたフィールドで、外部キー関係がインデックスを作成します.高同時条件下でインデックスを組み合わせる傾向がある.集約関数のカラムには、max(column_1)またはcount(column_1)を使用した場合のcolumn_などのインデックスを作成できます.1インデックスを作成する必要があります. インデックスを作成するのに適していない場合5よく削除された列はインデックスを作成しないでください.インデックスを確立しないカラムが多数重複しています.テーブルレコードが少なすぎてインデックスを作成しないでください.データベースに十分なテストデータがある場合にのみ、パフォーマンステストの結果に実際の参考価値があります.テスト・データベースに数百件のデータ・レコードしかない場合、最初のクエリー・コマンドが実行された後にメモリにすべてロードされることが多く、インデックスが使用されているかどうかにかかわらず、後続のクエリー・コマンドが非常に速く実行されます.データベースのパフォーマンステストの結果は、データベースのレコードが1000個を超え、データの合計がMySQLサーバのメモリの合計を超えた場合にのみ意味があります. インデックスが失効した場合、コンビネーションインデックスにカラムの値がNULLにならない場合、ある場合、このカラムはコンビネーションインデックスに対して無効です.1つのSELECT文では、インデックスは1回しか使用できません.WHEREで使用した場合、ORDER BYでは使用しません.LIKE操作では、'%aaa%'はインデックスを使用しません.つまり、インデックスは失効しますが、「aaa%」はインデックスを使用できます.インデックスのカラムに式または関数を使用すると、インデックスが無効になります.シンボルと!=を含むクエリ条件で等しくないものを使用します.インデックスが無効になります.クエリ条件でIS NULLまたはIS NOT NULLを使用すると、インデックスが無効になります.文字列に単一引用符を付けないと、インデックスが無効になります.クエリ条件でORを使用して複数の条件を接続すると、ORリンクの各条件にインデックスが追加されない限り、インデックスが失効します.ソートされたフィールドにインデックスが使用されている場合、selectのフィールドもインデックスフィールドであり、そうでない場合、インデックスは失効します.複数のカラムのソートはできるだけ含まないでください.必要に応じて、このキューの組合せインデックスを構築したほうがいいです.
二、事務
1、トランザクションの概要トランザクション(Transaction):最小の再分割できないワークユニット;通常、1つのトランザクションは、銀行口座振替ビジネスなどの完全なビジネスに対応します.このビジネスは、最小のワークユニットです.トランザクションオープントランザクションを開く構文は、 です.トランザクションコミットトランザクションをコミットする構文は、 です.ロールバックトランザクションの構文は、 です.
手動でトランザクションを開き、t_actテーブルにレコードを挿入し、トランザクションをコミットします.コードは次のとおりです.
手動でトランザクションを開き、t_actテーブルにレコードを挿入し、トランザクションをロールバックします.コードは次のとおりです.
3、トランザクションの特徴トランザクション四大特徴(ACID):原子性(A):トランザクションは最小単位で、再分割できない;コンシステンシ©:トランザクションがすべてのDML文の操作を要求する場合、同時に成功するか、同時に失敗することを保証しなければならない.隔離性(I):同じ時間に、1つのトランザクションが同じデータを要求することを許可し、異なるトランザクション間で互いに干渉しない.永続性(D):トランザクションの保証であり、トランザクションの終了フラグ(メモリのデータはハードディスクファイルに永続します).4、トランザクションの独立性レベルトランザクションの独立性レベルは4つあります:リード・コミット(read uncommitted):トランザクションAとトランザクションB、トランザクションAがコミットしていないデータ、トランザクションBは読み取ることができて、ここで読み取ったデータは“汚いデータ”と呼ばれます.この独立性レベルは最も低く、一般的に理論的に存在し、データベースの独立性レベルは一般的にこのレベルより高い.コミット済み(read committed):トランザクションAとトランザクションB、トランザクションAがコミットしたデータを読み込むと、トランザクションBが読み込まれます.この独立性レベルは、読み取り未送信よりも高く、「汚いデータ」を回避できます.しかし、トランザクションAが同じデータを複数回読み出し、トランザクションBがトランザクションAが複数回読み出している間にデータを更新してコミットした場合、トランザクションAが複数回同じデータを読み出した場合、結果が一致しない現象を「繰り返し不可読み出し」と呼ぶ.繰り返し読み取り可能(repeatable read):トランザクションAとトランザクションB、トランザクションAがコミットされた後のデータ、トランザクションBがトランザクションAがコミットしたデータを読み込めません.この独立性レベルは、コミットされたリードよりも高いですが、トランザクションAがデータの変更中(たとえば、すべてのレコードステータスを1に設定)に、トランザクションBが同じテーブルに新しいレコード(ステータス0)を挿入した場合、トランザクションAがコミットされた後にテーブルを再問合せすると、1つのレコードステータスが1に変更されていないことがわかり、幻覚が発生したようです.これを「幻読み」と呼びます.繰り返し読み取り可能はMySQLのデフォルトの独立性レベルです.シリアル化(serializable):トランザクションAとトランザクションB、トランザクションAがデータベースを操作するとき、トランザクションBはキューに並んで待つしかありません.このレベルでは、「幻読み」を回避できます.毎回読み込まれるのは、データベースに実際に存在するデータであり、トランザクションAはトランザクションBとシリアルであり、同時ではありません.
3、錠
ロックは、コンピュータが複数のプロセスまたはスレッドを調整してリソースに同時にアクセスするメカニズムです.1、楽観的にテーブルにロックされたデータを操作する場合(更新)、まずデータテーブルにバージョン(version)フィールドを追加し、操作ごとにその記録されたバージョン番号を1追加する.すなわち、まずそのレコードをクエリーしてversionフィールドを取得し、そのレコードを操作(更新)する場合は、現在のversionの値がクエリーしたばかりのversionの値と等しいかどうかを判断し、等しい場合は、この間、他のプログラムが操作していないことを説明し、更新を実行し、versionフィールドの値を1加算することができる.更新時に現在のバージョン値が取り出したばかりの19からのバージョン値と等しくないことが判明した場合、この期間に他のプログラムが操作していたことを示す.更新操作2、悲観ロック共有ロック(S):リードロックとも呼ばれ、トランザクションが1行のデータを読み取ることができる.例えば、トランザクションTがデータオブジェクトAにSロックをかけると、トランザクションTはAを読むことができるが、Aを変更することはできず、他のトランザクションはAにSロックを加えるだけで、TがAのSロックを解放するまでXロックを加えることはできない.これにより、他のトランザクションはAを読むことができるが、TがAのSロックを解除するまでAを変更することはできない.排他ロック(X):書き込みロックとも呼ばれ、トランザクションが1行のデータを削除または更新できるようにします.たとえば、トランザクションTがデータオブジェクトAにXロックをかけると、トランザクションTはAを読むことも、Aを変更することもできます.他のトランザクションは、TがAのロック2を解放するまで、Aのロックの粒度ロックの粒度がロックの範囲の大きさになるまで、Aにロックを加えることはできません.ロックを使用する場合、ロックされているものが少ないほど、他の競合他社への影響が小さくなり、このものの大きさがロック粒度になります.例えばトイレの洗面台、便器、バスタブは、別々に仕切れば(3つのエリアがそれぞれロックされ、粒度が小さい)、3人で同時に使用できます.お風呂のドアを閉めて、他の人は手を洗うことができます.しかし、トイレのエリアが隔てられていなければ、一人でトイレに入ってドアを閉めて、他の人は入れません.ロックの粒度が小さいほど、ロックされたリソースが少なくなり、他のユーザーへの影響が小さくなり、もちろんシステムへのオーバーヘッドも大きくなります.ロックの粒度分類:表レベルロック:オーバーヘッドが小さく、ロックが速く、デッドロックは発生しません.ロックの粒度が最も大きく、クエリーを主とするのに適しており、インデックス条件でデータを更新する操作はわずかです.欠点は資源の競合確率が高いことである.行レベルロック:オーバーヘッドが大きく、ロックが遅く、デッドロックが発生します.ロックの粒度は最小限で、インデックス条件に基づいて少量の異なるデータを同時に大量に更新するのに適しており、同時に同時クエリーの操作もあります.行レベルロックを使用するのは主にInnoDBストレージエンジンである.≪ページ・ロック|Page Locks|emdw≫:表ロックと行ロックの間にオーバーヘッドが発生すると、デッドロックが発生します.
1)インデックスとは?一般的なアプリケーションシステムでは、読み書きの割合は10:1程度であり、挿入操作と一般的な更新操作ではパフォーマンスの問題は少なく、本番環境では、最も多くの問題に直面し、最も問題が発生しやすいのか、複雑なクエリー操作なのか、クエリー文の最適化が重要であることは明らかです.クエリーの高速化といえば、インデックスに言及せざるを得ません.2)インデックスがあるのはなぜですか.インデックスはMySQLでは「キー」とも呼ばれ、ストレージエンジンがレコードをすばやく見つけるためのデータ構造です.インデックスは、特にテーブル内のデータ量がますます大きくなると、パフォーマンスに与えるインデックスの影響がますます重要になります.インデックス最適化は、クエリーのパフォーマンス最適化に最も有効な手段であるはずです.インデックスを使用すると、クエリーのパフォーマンスが数桁容易に向上します.インデックスは辞書の音順表に相当し、ある字を調べる場合、音順表を使用しない場合は、数百ページからページごとに調べる必要があります.3)インデックスの優劣:インデックスの優勢:迅速に検索でき、I/O回数を減らし、検索速度を速める;インデックスのグループ化とソートに基づいて、グループ化とソートを高速化できます.インデックスの劣勢:インデックス自体もテーブルなので、ストレージスペースを占有します.一般的に、インデックステーブルが占有するスペースはデータテーブルの1.5倍です.インデックステーブルのメンテナンスと作成には時間コストがかかり、このコストはデータ量が増加するにつれて増加します.インデックスを構築すると、データ・テーブルを変更すると同時にインデックス・テーブルを変更する必要があるため、データ・テーブルの変更(削除、追加、変更)の効率が低下します.2、MySQLでのインデックス作成のためのインデックスコードは以下の通りです.
CREATE TABLE mytable(--
ID INT NOT NULL, username VARCHAR(16) NOT NULL,
INDEX myindex (username(16))--index ，myindex
);
索引の作成
CREATE INDEX myindex ON mytable(username(16));
ALTER table mytable ADD INDEX myindex(usern
索引の削除
DROP INDEX myindex ON mytable;
ALTER TABLE mytable DROP INDEX
索引の表示
SHOW INDEX FROM mytable
expalinコマンドによるインデックスの使用状況の表示
explain select * from mytable where username = 'jack'
索引の使用規則
二、事務
1、トランザクションの概要トランザクション(Transaction):最小の再分割できないワークユニット;通常、1つのトランザクションは、銀行口座振替ビジネスなどの完全なビジネスに対応します.このビジネスは、最小のワークユニットです.
start transaction;
begin transaction;
commit;
rollback;
手動でトランザクションを開き、t_actテーブルにレコードを挿入し、トランザクションをコミットします.コードは次のとおりです.
start transaction;--
insert into t_act values(3,300); --
commit;-- ，commit
select * from t_act;
手動でトランザクションを開き、t_actテーブルにレコードを挿入し、トランザクションをロールバックします.コードは次のとおりです.
start transaction;--
insert into t_act values(4,200); --
rollback;--
select * from t_act;
3、トランザクションの特徴トランザクション四大特徴(ACID):原子性(A):トランザクションは最小単位で、再分割できない;コンシステンシ©:トランザクションがすべてのDML文の操作を要求する場合、同時に成功するか、同時に失敗することを保証しなければならない.隔離性(I):同じ時間に、1つのトランザクションが同じデータを要求することを許可し、異なるトランザクション間で互いに干渉しない.永続性(D):トランザクションの保証であり、トランザクションの終了フラグ(メモリのデータはハードディスクファイルに永続します).4、トランザクションの独立性レベルトランザクションの独立性レベルは4つあります:リード・コミット(read uncommitted):トランザクションAとトランザクションB、トランザクションAがコミットしていないデータ、トランザクションBは読み取ることができて、ここで読み取ったデータは“汚いデータ”と呼ばれます.この独立性レベルは最も低く、一般的に理論的に存在し、データベースの独立性レベルは一般的にこのレベルより高い.コミット済み(read committed):トランザクションAとトランザクションB、トランザクションAがコミットしたデータを読み込むと、トランザクションBが読み込まれます.この独立性レベルは、読み取り未送信よりも高く、「汚いデータ」を回避できます.しかし、トランザクションAが同じデータを複数回読み出し、トランザクションBがトランザクションAが複数回読み出している間にデータを更新してコミットした場合、トランザクションAが複数回同じデータを読み出した場合、結果が一致しない現象を「繰り返し不可読み出し」と呼ぶ.繰り返し読み取り可能(repeatable read):トランザクションAとトランザクションB、トランザクションAがコミットされた後のデータ、トランザクションBがトランザクションAがコミットしたデータを読み込めません.この独立性レベルは、コミットされたリードよりも高いですが、トランザクションAがデータの変更中(たとえば、すべてのレコードステータスを1に設定)に、トランザクションBが同じテーブルに新しいレコード(ステータス0)を挿入した場合、トランザクションAがコミットされた後にテーブルを再問合せすると、1つのレコードステータスが1に変更されていないことがわかり、幻覚が発生したようです.これを「幻読み」と呼びます.繰り返し読み取り可能はMySQLのデフォルトの独立性レベルです.シリアル化(serializable):トランザクションAとトランザクションB、トランザクションAがデータベースを操作するとき、トランザクションBはキューに並んで待つしかありません.このレベルでは、「幻読み」を回避できます.毎回読み込まれるのは、データベースに実際に存在するデータであり、トランザクションAはトランザクションBとシリアルであり、同時ではありません.
3、錠
ロックは、コンピュータが複数のプロセスまたはスレッドを調整してリソースに同時にアクセスするメカニズムです.1、楽観的にテーブルにロックされたデータを操作する場合(更新)、まずデータテーブルにバージョン(version)フィールドを追加し、操作ごとにその記録されたバージョン番号を1追加する.すなわち、まずそのレコードをクエリーしてversionフィールドを取得し、そのレコードを操作(更新)する場合は、現在のversionの値がクエリーしたばかりのversionの値と等しいかどうかを判断し、等しい場合は、この間、他のプログラムが操作していないことを説明し、更新を実行し、versionフィールドの値を1加算することができる.更新時に現在のバージョン値が取り出したばかりの19からのバージョン値と等しくないことが判明した場合、この期間に他のプログラムが操作していたことを示す.更新操作2、悲観ロック共有ロック(S):リードロックとも呼ばれ、トランザクションが1行のデータを読み取ることができる.例えば、トランザクションTがデータオブジェクトAにSロックをかけると、トランザクションTはAを読むことができるが、Aを変更することはできず、他のトランザクションはAにSロックを加えるだけで、TがAのSロックを解放するまでXロックを加えることはできない.これにより、他のトランザクションはAを読むことができるが、TがAのSロックを解除するまでAを変更することはできない.排他ロック(X):書き込みロックとも呼ばれ、トランザクションが1行のデータを削除または更新できるようにします.たとえば、トランザクションTがデータオブジェクトAにXロックをかけると、トランザクションTはAを読むことも、Aを変更することもできます.他のトランザクションは、TがAのロック2を解放するまで、Aのロックの粒度ロックの粒度がロックの範囲の大きさになるまで、Aにロックを加えることはできません.ロックを使用する場合、ロックされているものが少ないほど、他の競合他社への影響が小さくなり、このものの大きさがロック粒度になります.例えばトイレの洗面台、便器、バスタブは、別々に仕切れば(3つのエリアがそれぞれロックされ、粒度が小さい)、3人で同時に使用できます.お風呂のドアを閉めて、他の人は手を洗うことができます.しかし、トイレのエリアが隔てられていなければ、一人でトイレに入ってドアを閉めて、他の人は入れません.ロックの粒度が小さいほど、ロックされたリソースが少なくなり、他のユーザーへの影響が小さくなり、もちろんシステムへのオーバーヘッドも大きくなります.ロックの粒度分類:表レベルロック:オーバーヘッドが小さく、ロックが速く、デッドロックは発生しません.ロックの粒度が最も大きく、クエリーを主とするのに適しており、インデックス条件でデータを更新する操作はわずかです.欠点は資源の競合確率が高いことである.行レベルロック:オーバーヘッドが大きく、ロックが遅く、デッドロックが発生します.ロックの粒度は最小限で、インデックス条件に基づいて少量の異なるデータを同時に大量に更新するのに適しており、同時に同時クエリーの操作もあります.行レベルロックを使用するのは主にInnoDBストレージエンジンである.≪ページ・ロック|Page Locks|emdw≫:表ロックと行ロックの間にオーバーヘッドが発生すると、デッドロックが発生します.