テクノロジーの進歩と発展に伴い、面接官が面接対象者に求める条件はますます高くなっており、バックエンド開発職である限り面接は確実に行われます。データベースについて尋ねる。関連する知識、および mysql は現在最も人気のある無料のリレーショナル データベース管理テクノロジです。面接でそれに関連した質問がされるのは当然のことです。そこで今回は、面接での質問と回答を集めました。見てみましょう。
#推奨チュートリアル: MySQL 入門ビデオ
## 1. 主キー スーパーキー 候補キー 外部キー主キー:
ペアを格納します。データベース テーブル データ オブジェクトを一意かつ完全に識別するデータ列または属性の組み合わせ。データ列には主キーを 1 つだけ持つことができ、主キーの値を欠落させることはできません。つまり、主キーの値を null にすることはできません。
スーパー キー:
リレーションシップ内のタプルを一意に識別できる属性のセットは、スーパー キーと呼ばれます。関係パターンのキー。属性はスーパーキーとして使用でき、複数の属性を組み合わせてスーパーキーとして使用することもできます。スーパーキーには候補キーと主キーが含まれます。
候補キー:
は最小のスーパーキー、つまり冗長な要素のないスーパーキーです。
外部キー:
あるテーブル内に存在する別のテーブルの主キーは、外部キーと呼ばれます。このテーブル。
#2. データベーストランザクションの 4 つの特徴と意味
データベーストランザクショントランザクションを正しく実行するための 4 つの基本要素。 ACID、原子性、対応性、分離性、耐久性。
アトミック性:
トランザクション全体のすべての操作は完了するか完了しないかのどちらかであり、中間リンクで停滞することはあり得ません。トランザクションの実行中にエラーが発生した場合は、トランザクションがまったく実行されなかったかのように、トランザクションが開始される前の状態にロールバックされます。
一貫性:トランザクションの開始前および終了後、データベースの整合性制約に違反しません。
分離:トランザクションの状態実行を分離して、特定の時点でシステムによって実行される唯一の操作であるようにします。同時に実行され、同じ機能を実行する 2 つのトランザクションがある場合、トランザクション分離により、システム内の各トランザクションは、そのトランザクションのみがシステムを使用していると認識されます。このプロパティはシリアル化と呼ばれることもあります。トランザクション操作間の混乱を避けるために、同じデータに対するリクエストが同時に 1 つだけになるように、リクエストをシリアル化する必要があります。
永続性:トランザクションが完了すると、トランザクションによってデータベースに加えられた変更はデータベースに永続的に保存され、保存されなくなります。ロールバックされる。
3. ビューの役割 ビューは変更できますか?ビューは仮想テーブルです。データを含むテーブルとは異なり、ビューには使用時にデータを動的に取得するクエリのみが含まれ、列やデータは含まれません。ビューを使用すると、複雑な SQL 操作を簡素化し、特定の詳細を非表示にし、データを保護できます。ビューを作成すると、テーブルと同じように使用できます。
ビューにインデックスを付けることも、トリガーやデフォルト値を関連付けることもできません。ビュー自体に order by がある場合、ビュー上の order by が再度上書きされます。
ビューの作成: ビューの作成 更新可能であり、ビューを更新するとベース テーブルも更新されます。ただし、ビューは主に、更新ではなく、取得を簡素化し、データを保護するために使用されます。 、ほとんどのビューは更新できません。
4. ドロップ、削除、トランケートの違い
ドロップはテーブルを直接削除し、トランケートはデータを削除します再度挿入するとIDが自動的に増加し、deleteは1から始まりテーブル内のデータが削除されます。where句を追加することもできます。
(1) DELETE ステートメントは、テーブルから一度に 1 行を削除し、同時にロールバック操作のために行の削除操作をトランザクション レコードとしてログに保存します。 TRUNCATE TABLE は、テーブルからすべてのデータを一度に削除し、個々の削除操作の記録をログに記録しません。削除された行は復元できません。また、テーブルに関連する削除トリガーは、削除プロセス中にアクティブ化されません。実行速度が速い。
(2) テーブルとインデックスが占める領域。テーブルが TRUNCATE されると、テーブルとインデックスが占有する領域は元のサイズに復元され、DELETE 操作ではテーブルまたはインデックスが占有する領域は減りません。 Drop ステートメントは、テーブルによって占有されているすべてのスペースを解放します。
(3) 一般的には、drop > truncate > delete
(4) 適用範囲。 TRUNCATE は TABLE にのみ使用でき、DELETE はテーブルとビューに使用できます
(5) TRUNCATE と DELETE はデータのみを削除し、DROP はテーブル全体 (構造とデータ) を削除します。
(6) where を指定せずに切り詰めて削除する: データのみを削除しますが、テーブルの構造 (定義) は削除しません。drop ステートメントは、構造が持つ制約 (制約) を削除します。テーブルの依存関係: 、トリガー インデックス (インデックス); テーブルに依存するストアド プロシージャ/関数は保持されますが、ステータスは無効になります。
(7) 削除ステートメントは DML (データ保守言語) であり、この操作はロールバック セグメントに配置され、トランザクションが送信された後にのみ有効になります。対応するティガーがある場合、実行中にトリガーされます。
# (8) Truncate と Drop は DLL (データ定義言語) であり、操作はすぐに有効になります。元のデータはロールバック セグメントに配置されず、ロールバックできません
(9) バックアップがない場合は、drop と truncate を慎重に使用してください。一部のデータ行を削除するには、delete を使用し、影響範囲を制限する場所と組み合わせます。ロールバック セグメントは十分な大きさである必要があります。テーブルを削除するには、drop を使用します。テーブルは保持し、テーブル内のデータを削除する場合、トランザクションと関係がない場合は、truncate を使用します。トランザクションに関連している場合、または教師がトリガーをトリガーしたい場合は、引き続き削除を使用します。
(10) Truncate table table name は、次の理由により高速かつ効率的です。truncate table は、WHERE 句のない DELETE ステートメントと機能的に同じです。両方とも、テーブル内のすべての行を削除します。 。ただし、TRUNCATE TABLE は DELETE よりも高速で、使用するシステム リソースとトランザクション ログ リソースが少なくなります。 DELETE ステートメントは一度に 1 行を削除し、削除された行ごとにトランザクション ログにエントリを記録します。 TRUNCATE TABLE は、テーブル データの保存に使用されるデータ ページを解放することによってデータを削除し、解放されたページのみをトランザクション ログに記録します。
(11) TRUNCATE TABLE はテーブル内のすべての行を削除しますが、テーブル構造とその列、制約、インデックスなどは変更されません。新しい行を識別するために使用されるカウントは、その列のシードにリセットされます。 ID カウント値を保持したい場合は、代わりに DELETE を使用してください。テーブル定義とそのデータを削除する場合は、DROP TABLE ステートメントを使用します。
(12) FOREIGN KEY 制約によって参照されるテーブルの場合、TRUNCATE TABLE は使用できませんが、WHERE 句のない DELETE ステートメントを使用する必要があります。 TRUNCATE TABLE はログに記録されないため、トリガーをアクティブ化できません。
5. インデックスの動作原理と種類
##データベース インデックスはデータベース A です。管理システム内のソートされたデータ構造は、データベース テーブル内のデータの迅速なクエリと更新を支援します。インデックスの実装には通常、B ツリーとそのバリアント B ツリーが使用されます。
データに加えて、データベース システムは、特定の検索アルゴリズムを満たすデータ構造も維持します。これらのデータ構造は、何らかの方法でデータを参照 (ポイント) するため、これらのデータ構造は高度な検索アルゴリズムを実装します。このデータ構造がインデックスです。テーブルにインデックスを設定するにはコストがかかります。まず、データベースのストレージ領域が増加します。次に、データの挿入と変更に時間がかかります。インデックスも随時更新する必要があります)。変更されます)。
#
図は、考えられるインデックス付け方法を示しています。左側は合計 2 列と 7 レコードからなるデータ テーブルで、一番左はデータ レコードの物理アドレスです (論理的に隣接するレコードがディスク上で物理的に隣接しているとは限らないことに注意してください)。 Col2 の検索を高速化するために、右に示すように二分探索ツリーを維持できます。各ノードには、インデックス キー値と、対応するデータ レコードの物理アドレスへのポインタが含まれています。このようにして、次のように使用できます。 O(log2n) の二分探索 計算量内で対応するデータが得られます。
インデックスを作成すると、システムのパフォーマンスが大幅に向上します。
まず、一意のインデックスを作成することで、データベース テーブル内のデータの各行の一意性を保証できます。
次に、データの取得を大幅に高速化できます。これがインデックスを作成する主な理由でもあります。
3 番目に、テーブル間の接続を高速化できます。これは、データの参照整合性を達成する上で特に意味があります。
第 4 に、データ取得にグループ化句と並べ替え句を使用すると、クエリでのグループ化と並べ替えにかかる時間も大幅に短縮できます。
5 番目に、インデックスを使用すると、クエリ プロセス中に最適化非表示機能を使用してシステムのパフォーマンスを向上させることができます。
「インデックスを追加すると多くの利点があるのに、テーブル内のすべての列にインデックスを作成しないのはなぜですか?」と疑問に思う人もいるかもしれません。なぜなら、インデックスの追加には多くのデメリットもあるためです。
まず、インデックスの作成と維持には時間がかかり、データ量が増えるとこの時間も長くなります。
次に、インデックスは物理スペースを占有する必要があります。データ テーブルが占有するデータ スペースに加えて、各インデックスも一定量の物理スペースを占有します。クラスター化インデックスの場合は、スペースが大きくなります。
3 番目に、テーブル内のデータを追加、削除、変更する場合、インデックスを動的に維持する必要があるため、データのメンテナンス速度が低下します。
インデックスは、データベース テーブル内の特定の列に基づいて構築されます。インデックスを作成するときは、どの列にインデックスを作成できるか、どの列にインデックスを作成できないかを考慮する必要があります。一般に、インデックスは次の列に作成する必要があります。頻繁に検索される列では、検索を高速化できます。主キーとして機能する列では、列の一意性が強化され、テーブル内のデータの配置構造が整理されます。 ; 頻繁に検索される列では、接続された列で使用されます。これらの列は主に外部キーであり、接続を高速化できます。インデックスはソートされているため、範囲に基づいて頻繁に検索する必要がある列にインデックスを作成します。指定された範囲は連続している; 頻繁に必要になる インデックスはソートされているため、ソートされた列にインデックスを作成し、クエリでインデックスのソートを使用してソート クエリの時間を短縮できるようにします。条件の判断を高速化するために WHERE 句でよく使用されます。
同様に、一部の列にはインデックスを作成しないでください。一般に、インデックスを作成すべきではない列には次のような特徴があります。
まず、クエリでほとんど使用または参照されない列にはインデックスを作成しないでください。これは、これらの列がほとんど使用されないため、インデックスを作成してもしなくてもクエリ速度は向上しないためです。逆に、インデックスの追加により、システムのメンテナンス速度が低下し、必要なスペースが増加します。
次に、データ値がほとんどない列にはインデックスを追加しないでください。これは、クエリ結果ではこれらの列 (人事テーブルの性別列など) の値が非常に少ないため、結果セット内のデータ行がテーブル内のデータ行の大部分を占めるためです。テーブル内で検索する必要があるデータ 行の割合が膨大です。インデックスを増やしても、検索が大幅に高速化されるわけではありません。
第三に、テキスト、イメージ、およびビットのデータ型として定義された列にはインデックスを追加しないでください。これは、これらの列のデータ量が非常に大きいか、値が非常に少ないためです。
第 4 に、変更パフォーマンスが検索パフォーマンスよりはるかに大きい場合、インデックスを作成すべきではありません。修正性能と検索性能は相反するものだからである。インデックスを追加すると、検索パフォーマンスは向上しますが、変更パフォーマンスは低下します。インデックスを減らすと、変更パフォーマンスは向上しますが、検索パフォーマンスは低下します。したがって、変更パフォーマンスが検索パフォーマンスよりもはるかに高い場合は、インデックスを作成しないでください。
データベースの機能に基づいて、データベース デザイナーで一意インデックス、主キー インデックス、クラスター化インデックスの 3 種類のインデックスを作成できます。
一意のインデックス
一意のインデックスは、2 つの行が同じインデックス値を持つことを許可しないインデックスです。
ほとんどのデータベースでは、既存のデータに重複するキー値がある場合、新しく作成した一意のインデックスをテーブルに保存することはできません。データベースは、テーブル内に重複するキー値を作成する新しいデータの追加を防ぐこともあります。たとえば、従業員テーブル内の従業員の姓 (lname) に一意のインデックスが作成されている場合、2 人の従業員が同じ姓を持つことはできません。主キー インデックス データベース テーブルには、多くの場合、テーブル内の各行を一意に識別する値を持つ 1 つの列または複数の列の組み合わせがあります。この列はテーブルの主キーと呼ばれます。データベース ダイアグラム内のテーブルの主キーを定義すると、特定のタイプの一意のインデックスである主キー インデックスが自動的に作成されます。インデックスでは、主キーの各値が一意である必要があります。また、クエリで主キー インデックスを使用する場合、データへの高速アクセスも可能になります。クラスター化インデックス クラスター化インデックスでは、テーブル内の行の物理的な順序は、キー値の論理 (インデックス) 順序と同じです。テーブルにはクラスター化インデックスを 1 つだけ含めることができます。
インデックスがクラスター化インデックスではない場合、テーブル内の行の物理的な順序はキー値の論理的な順序と一致しません。一般に、クラスター化インデックスは非クラスター化インデックスよりも高速なデータ アクセスを提供します。
ローカリティ原則とディスク先読み
記憶媒体の特性上、ディスク自体がアクセスします。メイン ディスクよりも遅いです。ストレージははるかに遅いです。機械的移動のコストと相まって、ディスクのアクセス速度は、多くの場合、メイン メモリの 100 分の 1 です。したがって、効率を向上させるには、ディスク I/O を最小限に抑える必要があります。 。この目標を達成するために、ディスクは厳密にオンデマンドで読み取るのではなく、毎回事前に読み取ります。たとえ 1 バイトしか必要でない場合でも、ディスクはこの位置から開始して、一定の長さのデータを順番に後方に読み取ります。メモリ。この理論的根拠は、コンピュータ サイエンスにおけるよく知られた局所性原理です。つまり、データの一部が使用されると、通常は近くのデータがすぐに使用されます。通常、プログラム実行中に必要なデータは集中しています。
ディスクの順次読み取りは非常に効率的であるため (シーク時間が不要で、スピン時間が非常に短い)、局所性のあるプログラムに先読みを使用でき、I/O 効率が向上します。
通常、先読みの長さはページの整数倍です。ページは、コンピュータが管理するメモリの論理ブロックです。ハードウェアとオペレーティング システムは、多くの場合、メイン メモリとディスク ストレージ領域を連続する同じサイズのブロックに分割します。各ストレージ ブロックはページと呼ばれます (多くのオペレーティング システムでは、ページ サイズは通常 4k)。メインメモリとディスクはページ単位でデータを交換します。プログラムが読み込むデータがメインメモリにない場合、ページフォールト例外が発生し、システムはディスクに読み取り信号を送り、ディスクはデータの開始位置を見つけます。 1 つまたは複数のページを逆方向に読み取り、メモリにロードしてから異常終了し、プログラムは実行を続けます。
B-/ツリー インデックスのパフォーマンス分析
この時点で、最終的に B- のパフォーマンスを分析できるようになります。 /ツリーインデックス。
前述したように、インデックス構造の品質を評価するために、ディスク I/O の数が一般的に使用されます。 B ツリーの定義によると、1 回の検索で最大 h 個のノードを訪問する必要があることがわかります。データベース システムの設計者は、ディスク先読みの原理を巧みに利用し、ノードのサイズを 1 ページに等しくなるように設定しました。これにより、各ノードは 1 つの I/O だけで完全にロードできるようになります。この目標を達成するには、B ツリーの実際の実装で次のテクニックを使用する必要があります:
新しいノードが作成されるたびに、スペースのページが直接適用されます。これにより、ノードが物理的にページに格納され、コンピュータのストレージ割り当てがページに合わせて配置されるため、ノードに必要な I/O は 1 つだけになります。
B ツリーでの取得には最大でも h-1 個の I/O (ルート ノード常駐メモリ) が必要で、漸近複雑度は O(h)=O(logdN) です。一般的な実際のアプリケーションでは、出次数 d は非常に大きな数 (通常は 100 を超える) であるため、h は非常に小さくなります (通常は 3 以下)。
赤黒ツリー構造に関しては、h は明らかにはるかに深いです。論理的に近いノード (親と子) は物理的に遠く離れている可能性があり、局所性を利用できないため、赤黒ツリーの I/O 漸近複雑度も O(h) であり、効率は明らかにそれよりもはるかに悪くなります。 B ツリーの。
要約すると、B ツリーをインデックス構造として使用すると、非常に効率的になります。
#6. 接続のタイプ
クエリ アナライザーで実行:
--テーブル table1、table2 を作成します:
1 2 3 4 5 6 7 8 |
|
たとえば、テーブル
1 2 3 4 5 6 7 8 |
|
以下はすべてです。クエリ分析で プロセッサで実行
#1. 外部結合##1. 概念: 左外部結合、右外部結合、または完全外部結合を含む
2.左连接：left join 或 left outer join
(1)左向外联接的结果集包括 LEFT OUTER 子句中指定的左表的所有行，而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行，则在相关联的结果集行中右表的所有选择列表列均为空值(null)。
(2)sql 语句
1 |
|
1 2 3 4 5 6 7 |
|
注释：包含table1的所有子句，根据指定条件返回table2相应的字段，不符合的以null显示
3.右连接：right join 或 right outer join
(1)右向外联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行，则将为左表返回空值。
(2)sql 语句
1 |
|
1 2 3 4 5 6 7 |
|
注释：包含table2的所有子句，根据指定条件返回table1相应的字段，不符合的以null显示
4.完整外部联接:full join 或 full outer join
(1)完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时，则另一个表的选择列表列包含空值。如果表之间有匹配行，则整个结果集行包含基表的数据值。
(2)sql 语句
1 |
|
1 2 3 4 5 6 7 8 |
|
注释：返回左右连接的和（见上左、右连接）
二、内连接
1.概念：内联接是用比较运算符比较要联接列的值的联接
2.内连接：join 或 inner join
3.sql 语句
1 |
|
1 2 3 4 5 6 |
|
注释：只返回符合条件的table1和table2的列
4.等价（与下列执行效果相同）
A:select a.*,b.* from table1 a,table2 b where a.id=b.id
B:select * from table1 cross join table2 where table1.id=table2.id (注：cross join后加条件只能用where,不能用on)
三、交叉连接(完全)
1.概念：没有 WHERE 子句的交叉联接将产生联接所涉及的表的笛卡尔积。第一个表的行数乘以第二个表的行数等于笛卡尔积结果集的大小。（table1和table2交叉连接产生3*3=9条记录）
2.交叉连接：cross join (不带条件where...)
3.sql语句
1 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
|
注释：返回3*3=9条记录，即笛卡尔积
4.等价（与下列执行效果相同）
A:select * from table1,table2
7、数据库范式
1） 第一范式（1NF）
在任何一个关系数据库中，第一范式（1NF）是对关系模式的基本要求，不满足第一范式（1NF）的数据库就不是关系数据库。
所谓第一范式（1NF）是指数据库表的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性。如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。在第一范式（1NF）中表的每一行只包含一个实例的信息。简而言之，第一范式就是无重复的列。
2 ）第二范式（2NF）
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键、主码。
第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字。
3 ）第三范式（3NF）
满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。简而言之，第三范式就是属性不依赖于其它非主属性。（我的理解是消除冗余）
8、数据库优化的思路
这个我借鉴了慕课上关于数据库优化的课程。
一、SQL语句优化
1）应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
2）应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
1 |
|
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
1 |
|
3）很多时候用 exists 代替 in 是一个好的选择
4）用Where子句替换HAVING 子句 因为HAVING 只会在检索出所有记录之后才对结果集进行过滤
二、索引优化
看上文索引
三、数据库结构优化
1）范式优化： 比如消除冗余（节省空间。。）
2）反范式优化：比如适当加冗余等（减少join）
3）拆分表： 分区将数据在物理上分隔开，不同分区的数据可以制定保存在处于不同磁盘上的数据文件里。这样，当对这个表进行查询时，只需要在表分区中进行扫描，而不必进行全表扫描，明显缩短了查询时间，另外处于不同磁盘的分区也将对这个表的数据传输分散在不同的磁盘I/O，一个精心设置的分区可以将数据传输对磁盘I/O竞争均匀地分散开。对数据量大的时时表可采取此方法。可按月自动建表分区。
4）拆分其实又分垂直拆分和水平拆分： 案例： 简单购物系统暂设涉及如下表： 1.产品表（数据量10w，稳定） 2.订单表（数据量200w，且有增长趋势） 3.用户表 （数据量100w，且有增长趋势） 以mysql为例讲述下水平拆分和垂直拆分，mysql能容忍的数量级在百万静态数据可以到千万 垂直拆分：解决问题：表与表之间的io竞争 不解决问题：单表中数据量增长出现的压力 方案： 把产品表和用户表放到一个server上 订单表单独放到一个server上 水平拆分： 解决问题：单表中数据量增长出现的压力 不解决问题：表与表之间的io争夺
方案： 用户表通过性别拆分为男用户表和女用户表 订单表通过已完成和完成中拆分为已完成订单和未完成订单 产品表 未完成订单放一个server上 已完成订单表盒男用户表放一个server上 女用户表放一个server上(女的爱购物 哈哈)
四、服务器硬件优化
这个么多花钱咯！
9、存储过程与触发器的区别
觸發器與預存程序非常相似，觸發器也是SQL語句集，兩者唯一的區別是觸發器不能用EXECUTE語句調用，而是在使用者執行Transact-SQL語句時自動觸發（啟動）執行。觸發器是在一個修改了指定表中的資料時執行的預存程序。通常透過建立觸發器來強制實現不同表中的邏輯相關資料的參考完整性和一致性。由於使用者不能繞過觸發器，所以可以用它來強制實施複雜的業務規則，以確保資料的完整性。觸發器不同於預存程序，觸發器主要是透過事件執行觸發而被執行的，而預存程序可以透過預存程序名稱名字而直接呼叫。當某一表進行諸如UPDATE、INSERT、DELETE這些操作時，SQLSERVER就會自動執行觸發器所定義的SQL語句，從而確保資料的處理必須符合這些SQL語句所定義的規則。
原文網址：https://www.cnblogs.com/frankielf0921/p/5930743.html
以上がMySQL の面接の質問と回答 - 2019 年の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。
![[Web フロントエンド] Node.js クイック スタート](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
