MySQLデータベースの基礎知識の備蓄（整理まとめ）

この記事では、mysql データベースに関する関連知識を提供します。主に、インデックス、構文順序、実行順序、ストアド プロシージャ、およびその他の関連問題など、データベースのいくつかの基本的な知識ポイントを整理します。助けなければなりません。

#推奨学習:

mysql チュートリアル

1. データベース

1.1 トランザクション

1.1.1 4 つのトランザクション特性 (ACID)

アトミシティ (
• Aomicity) トランザクション全体で必要な操作すべてのコミットが成功するか、すべて失敗してロールバックされます。
  
一貫性 (
• Consistency) 操作の前後でデータベース内のデータの一貫性を確保します。 (たとえば、ユーザーの複数のアカウント間で送金するが、ユーザーの合計金額は変更されません)
  
分離 (
• 分離) 分離では、トランザクションを実行するためのトランザクションが必要です。データベース内のデータ 変更は、コミットされるまで他のトランザクションには表示されません。 (つまり、トランザクションはシリアルに実行する必要があります)
  
永続性 (
• 耐久性 ) 永続性とは、トランザクションが送信されると、データベース内のデータへの変更が永続的に維持されることを意味します。データベース システムに障害が発生した場合でも、トランザクションをコミットする操作は失われません。
  

#SQL

標準では 4 種類の分離が定義されています: (以下の分離は低から高の順であり、同時実行性は高から低の順です)

読み取りはコミットされていません。

最も低い分離レベル。他のトランザクションがコミットされていないデータを参照できるようになります。これにより、ダーティ リードが発生します。
• 
  送信済みの記事を読みました。
データベースは読み取りと書き込みが分離されているため、トランザクションの読み取り時に読み取りロックが取得されますが、読み取り後すぐに解放されます。読み取りロックが解放された後、他のトランザクションによってデータが変更される可能性があります。再度読み取りを行うと、を実行すると、前後でデータが読み取られたことがわかりますが、結果が異なるため、読み取りが再現できなくなります。 (読み取りロックはトランザクションのコミット後に解放する必要はありませんが、書き込みロックはトランザクションのコミット後に解放する必要があります。)
• 
  反復可能な読み取り。
• select
  によって取得されたすべてのデータは、トランザクションの前後で読み取りの不一致を避けるために変更できません。ただし、現時点では他のトランザクションは選択されたデータを変更できませんが、データを追加することはできるため、ファントム読み取りを制御する方法はありません。 はシリアル化できます。
ファントム読み取りを回避するために、すべてのトランザクションは次々に実行されます。ロックに基づいた同時実行制御を実装するデータベースの場合、シリアル化では、範囲クエリを実行するときに範囲ロックを取得する必要があります。同時実行がベースで実装されていない場合は、ロック、制御データベースでは、シリアル操作に違反するトランザクションが検出された場合、トランザクションをロールバックする必要があります。
• 
  
概要: 4 つのレベルは段階的に強化されており、各レベルで問題が解決されます。トランザクション レベルが高くなるほど、パフォーマンスは低下します。

分離レベル ダーティリード Non-repeatable Phantom read

Read uncommitted 可 可 可

Read Committed 不可 可 おそらく Repeatable read read） 不可 不可 可
Serializable 不可 不可不可能

##要約: コミットされていない読み取りによりダーティ リードが発生します—> コミットされた読み取りによりダーティ リードは解決されますが、反復不可能な読み取りが発生します -> 反復可能な読み取りにより、一貫性のない読み取り結果の問題は解決されますが、ファントム読み取りが発生します (そうではありません)以前はありましたが、現在は) -> シリアル化可能によりファントム読み取りは解決されますが、多くの範囲ロックが追加され、ロック タイムアウトが発生する可能性があります;

1.1.2 ダーティ リード、非反復読み取り、およびファントム リード

• ダーティ リード (ロールバック操作用): トランザクション T1 がレコードの内容の行を更新しましたトランザクション T2 が更新された行を読み取り、T1 がロールバック操作を実行して、行ったばかりの変更をキャンセルしました。 T2 によって読み取られた行の数が無効になりました (1 つのトランザクションが別のトランザクションを読み取りました);
• Non-repeatable read (変更された操作の場合): トランザクション T1 がレコードの行を読み取り、その後 T2 がそれを変更しました T1 は読み取りだけでしたレコードの行を読み取った後、T1 がこのレコードの行を再度読み取ったところ、結果が今読み取った結果と異なることがわかりました。
• ファントム読み取り (更新操作の場合): トランザクション T1 は、指定された where 句によって返された結果セットを読み取り、トランザクション T2 は、T1 条件で使用されるクエリを正確に満たす新しいレコード行を挿入します。次に、T1 はテーブルを再度取得しますが、T2 によって挿入されたデータが再度表示されます。 (1 回目は見ませんでしたが、2 回目で見ました)

2. インデックス

2.1 インデックスの機能

1. データベースの取得を高速化できます。
2. はビューではなくテーブルにのみ作成できます。
3. は直接または間接的に作成できます。
4. は、実行中に非表示にできます。最適化 インデックスの使用;
5. クエリ プロセッサを使用して SQL ステートメントを実行します。テーブルでは、一度に 1 つのインデックスのみ使用できます。

2.1.1 インデックスの利点

1. 一意のインデックスを作成して、データベース テーブル内のデータの各行の一意性を確保します;
2. インデックスを作成する主な理由であるデータ取得を大幅に高速化します;
3. 特にデータベース参照の整合性を達成する際に、データベース テーブル間のリンクを高速化します;
4. グループ化と並べ替えを使用する場合取得用の句を使用すると、クエリ内のグループ化と並べ替えにかかる時間も大幅に短縮できます。
5. インデックスを使用すると、クエリ内で最適化ハイダーを使用してシステム パフォーマンスを向上させることができます。

2.1.2 インデックスの欠点

1. インデックスの作成と維持には時間がかかり、数が増えるとその時間も長くなります。
2. インデックス作成には物理的なスペースを占有する必要があります。データ テーブルが占有するデータ領域に加えて、各インデックスも一定量の物理領域を占有します。クラスタ化インデックスが確立されている場合、必要な領域はさらに大きくなります。
3. テーブル内のデータを比較する場合追加、削除、変更を行うとインデックスもメンテナンスする必要があり、データのメンテナンス速度が低下します。

2.2 インデックスの分類

(1) 通常のインデックス(制限はありません。)
(2) 一意のインデックス (インデックス列の値は一意である必要がありますが、null 値も許可されます。)
(3) 主キー インデックス (特別な一意のインデックス) (null 値は許可されません) 。通常、主キー インデックスはテーブルの作成時に同時に作成されます。)
(4) 結合インデックス
(5) クラスター化インデックスは、次に従って B ツリーを構築します。各テーブルの主キー、およびリーフ ノードにはテーブル レコード データ全体が格納されるため、クラスター化インデックスのリーフ ノードはデータ ページになります。
(6) 非クラスター化インデックス (補助インデックス) (ページ ノードはレコードの行全体を保存しません)。

2.3 インデックスの無効化

(1) 条件内に or がある場合、インデックス付きの条件があっても使用されません（使用またはできるだけ少なくしてください);
(2) like クエリは % で始まります。たとえば、SELECT * FROM mytable WHEREt Name like'�min';
(3) 列の型が文字列の場合は、次のようにする必要があります。条件内で引用符で囲む必要があります。そうでない場合、インデックスは使用されません。

2.4 各エンジンはインデックス

MyISAM,## をサポートします。 #InnoDB,Memory 一般的に使用される 3 つの MySQL エンジン タイプの比較: 3 myisam インデックス InnoDB インデックス Memory インデックス
B ツリー インデックスのサポート サポート
ハッシュインデックスはサポートなしではサポートされません
R-Tree Index サポートあり サポートされていない サポートされていない
全文インデックス サポートされていない まだサポートされていない サポートされていない

2.5 データベース内のインデックス構造

バイナリ ツリーを使用する場合、バイナリ ツリーの深さが大きすぎるため、I/O の読み取りと書き込みが頻繁になり、クエリ効率が低下します。したがって、マルチウェイ ツリー構造を使用すると、B ツリーに対するさまざまな操作によって B ツリーの高さを低く抑えることができます。

B ツリーは平衡型マルチパス探索ツリーとも呼ばれ、m 次の B ツリーの特徴は次のとおりです。

• 1. ツリー内の各ノードには最大で m 個の子が含まれます (m>=2);
• 2. ルート ノードとリーフ ノードを除き、他の各ノードには少なくとも (ceil (m/2)) 子 (ceil (x) は上限を取る関数です);
• 3. ルート ノードには少なくとも 2 つの子があります (B ツリーにノードが 1 つしか含まれていない場合:ルート ノード);
• 4. すべてのリーフ ノードは同じレイヤーに表示され、リーフ ノードにはキーワード情報が含まれません (外部ノードまたはクエリが失敗したノードと見なすことができ、これらのノードを指します。 (注: リーフ ノードには子と子へのポインタがないだけです。これらのノードも存在し、要素を持ちます。赤黒ツリーと同様に、各 null ポインタはリーフ ノードとみなされますが、 (1)よく出てくるキーワード order by、group by、distinct に従ってフィールドにインデックスを作成する;
  (2) Union などの集合演算の結果セット フィールドにインデックスを作成する インデックスを作成する目的は同じです。上記と同様; (3) 選択したフィールドをクエリしてインデックスを作成するためによく使用されます;
  (4) テーブル リンクとしてよく使用される属性にインデックスを作成します; (5) インデックス カバレッジの使用を検討してくださいデータがめったに更新されないテーブルの場合、ユーザーが少数のフィールドのみを頻繁にクエリする場合は、これらのフィールドにインデックスを作成して、テーブル スキャンをインデックス スキャンに変更することを検討できます。
  3. Mysql 構文の順序
  
  つまり、次のキーワードが SQL に存在する場合、この順序を維持する必要があります:
  
  
  select[distinct ] 、from、join (左結合など)、on、where、group
  by、having、union、order by、limit;

4. Mysql の実行順序

つまり、SQL は実行中に次の順序で実行されます:

from、on、join、where、group by、having、select、distinct、union、order by
group by and集計関数は一緒に使用されます。

例:

select a.Customer,sum(a.OrderPrice) from orders a where a.Customer=’Bush’ or a.Customer = ‘Adams’ group by a.Customer;

複数テーブルのクエリ (内部結合) を実現

select u.uname,a.addr from lm_user u inner join lm_addr a on u.uid = a.uid;

## を使用しても同じことが実現できます。 #select from where

select u.uname,a.addr from lm_user u, lm_addr a where u.uid = a.uid;

5. ストアド プロシージャ

delimiter $$
create procedure procedure_bill()
comment '查询所有销售情况'
begin
select billid, tx_time, amt from lm_bill;
end $$
delimiter ;

呼び出しストアド プロシージャ

call procedure_bill();

表示ストアド プロシージャ

show procedure status like 'procedure_bill';

6. 多くのストアド プロシージャを確立します。 -対多のデー​​タ テーブルの関係 データベース内で、2 つのテーブル間の関係が「学生テーブルとコース スケジュール」などの多対多の関係である場合、学生は複数のコースを選択できます。 、およびコースは複数の学生によって選択することもできます。選択; データベースの設計原則に従って、3 番目の関連テーブルを形成する必要があります。

ステップ 1: Student、Course、Stu_Cour の 3 つのデータ テーブルを作成します

/**学生表*/
CREATE TABLE Student (
stu_id INT AUTO_INCREMENT,
NAME VARCHAR(30),
age INT ,
class VARCHAR(50),
address VARCHAR(100),
PRIMARY KEY(stu_id)
)
/*学生课程表*/
CREATE TABLE Course(
cour_id INT AUTO_INCREMENT,
NAME VARCHAR(50),
CODE VARCHAR(30),
PRIMARY KEY(cour_id)
)
/**学生课程关联表*/
CREATE TABLE Stu_Cour(
sc_id INT AUTO_INCREMENT,
stu_id INT ,
cour_id INT,
PRIMARY KEY(sc_id)
)

ステップ 2: Stu_Cour 関連テーブルに外部キーを追加します

/*添加外键约束*/
ALTER TABLE Stu_Cour ADD CONSTRAINT stu_FK1 FOREIGN KEY(stu_id) REFERENCES Student(stu_id);
ALTER TABLE Stu_Cour ADD CONSTRAINT cour_FK2 FOREIGN KEY(cour_id) REFERENCES Course(cour_id);

作成を完了します。

注: すでに追加されているデータ テーブルに外部キーを追加します:

- 構文:

alter table table name addconstraint FK_ID 外部キー (外部キー フィールド名) REFERENCES 外観 Table name (対応するテーブルの主キーフィールド名)
;

例:

alter table tb_active addconstraint FK_ID 外部キー(user_id) REFERENCES tb_user(id);

• 7. データベース エンジン (ストレージ エンジン)
  データベースにアクセスするときは、手動アクセスでもプログラム アクセスでも、データベース ファイルを直接読み書きするのではなく、データベース エンジンのドキュメント。
リレーショナル データベースを例に挙げると、SQL ステートメントをデータベース エンジンに送信します。データベース エンジンは SQL ステートメントを解釈し、必要なデータを抽出して返します。したがって、訪問者にとって、データベース エンジンは SQL ステートメントのインタープリターです。

7.1 MYISAM エンジンと InnoDB エンジンの違い

主な違い:

##MYISAM

は非トランザクション的に安全です。また、

InnoDB

はトランザクション セーフであり、

NYISAM

ロックの粒度はテーブル レベルのロックですが、

InnoDB

は行レベルのロックをサポートしています。
• #MYISAM はフルテキスト インデックス作成をサポートしますが、InnoDB はフルテキスト インデックス作成をサポートしません。
• MYISAM は比較的単純です。 InnoDB、小規模なアプリケーションでは、
MYISAM
• ;MYISAM の使用を検討できます。
MYISAM
• ストレージは多くの手間を省きます; (6) InnoDB テーブルは MYISAM# よりも安全です## テーブル。データ損失が発生した場合に、非トランザクション テーブルをトランザクション テーブルに切り替えることができます。
• アプリケーション シナリオ:<ul> <li> <code>MYISAM は非トランザクション テーブルを管理し、高速なストレージと取得、および全文検索機能を提供します。 select クエリを使用する場合は、 MYISAM がより適切な選択です。
• InnoDB はトランザクション処理アプリケーションに使用され、ACID トランザクション サポートなどの多数の機能を備えています。アプリケーションで多数の insert または update 操作を実行する必要がある場合は、マルチユーザー同時操作のパフォーマンスを向上させる innodb を使用する必要があります。

8. データベース パラダイム

現在、リレーショナル データベースには 6 つのパラダイムがあります: 第 1 正規形 {1NF}、第 2 正規形 {2NF}、第 3 正規形 {3NF}、Bath - コード正規形 {BCNF}、第 4 正規形 {4NF}、第 5 正規形 {5NF、完全正規形とも呼ばれます}。最小要件を満たすパラダイムは、第 1 正規形です。第 1 正規形に基づいて、より多くの標準要件を満たすものを第 2 正規形 {2NF} と呼び、他のパラダイムもこれに続きます。一般に、データベースは第 3 正規形 (3NF) を満たす必要があるだけです。 。

パラダイム:

1NF: 各列がアトミックのままであることを確認します;
• 2NF: テーブル内の各列が主キー (結合主キー) に関連付けられていることを確認します。
• 3NF: テーブル内の各列が主キー (外部キー) に直接関連付けられていることを確認します;
• BCNF: 1NF に基づいて、非主属性は外部キーに依存できません。主キーのサブセット (3NF に基づいて主キー サブセットへの依存を排除​​) メイン コード サブセットへの依存);
• 4NF: 同じテーブル内の多対多リレーションシップの削除要求;
• 5NF: 最終構造から元の構造を再構築します;

推奨される学習:

mysql ビデオ チュートリアル

以上がMySQLデータベースの基礎知識の備蓄（整理まとめ）の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。