ホームページ > 記事 > データベース > MySQLデータベースの基礎知識の備蓄（整理まとめ）

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

WBOY転載: 2022-03-02 17:43:292946ブラウズ

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

#推奨学習:

mysql チュートリアル

1. データベース

1.1 トランザクション

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

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

#SQL

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

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

送信済みの記事を読みました。
反復可能な読み取り。
select
によって取得されたすべてのデータは、トランザクションの前後で読み取りの不一致を避けるために変更できません。ただし、現時点では他のトランザクションは選択されたデータを変更できませんが、データを追加することはできるため、ファントム読み取りを制御する方法はありません。はシリアル化できます。

分離レベルダーティリード 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 インデックスの機能

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

2.1.1 インデックスの利点

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

2.1.2 インデックスの欠点

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

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. データベースエンジン (ストレージエンジン)
データベースにアクセスするときは、手動アクセスでもプログラムアクセスでも、データベースファイルを直接読み書きするのではなく、データベースエンジンのドキュメント。

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

主な違い:

##MYISAM

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

InnoDB

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

NYISAM

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

InnoDB

#MYISAM はフルテキストインデックス作成をサポートしますが、InnoDB はフルテキストインデックス作成をサポートしません。
MYISAM は比較的単純です。 InnoDB、小規模なアプリケーションでは、
;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) を満たす必要があるだけです。。

パラダイム:

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

推奨される学習:

mysql ビデオチュートリアル

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

sql mysql NULL select 字符串 union 指针并发 table r-tree 数据库

声明：

この記事はcsdn.netで複製されています。侵害がある場合は、admin@php.cn までご連絡ください。

前の記事：mysql ビューにはインデックスがありますか?次の記事：mysql ビューにはインデックスがありますか?

続きを見る

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

1.1 トランザクション

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

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

2. インデックス

2.1 インデックスの機能

2.1.1 インデックスの利点

2.1.2 インデックスの欠点

2.2 インデックスの分類

2.3 インデックスの無効化

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

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

4. Mysql の実行順序

例:

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

主な違い:

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

関連記事

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

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

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