データベースの基本理論の詳細な紹介

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

最初のパラダイム: 列は分割できません。例: [連絡先] (名前、性別、電話番号)、連絡先には自宅の電話番号があります。と会社の電話番号の場合、このテーブル構造設計は 1NF に達しません;

第 2 正規形: 主キーがあり、完全な依存関係が保証されます。例: 注文詳細テーブル [OrderDetail] (OrderID、ProductID、UnitPrice、Discount、Quantity、ProductName)、Discount (割引)、Quantity (数量) は主キー (OderID、ProductID) に完全に依存しますが、UnitPrice、ProductName のみに依存します。 on ProductID の場合、2NF に準拠しません;

第 3 正規形: 推移的な依存関係なし (非主キー列 A は非主キー列 B に依存し、非主キー列 B は主キー列に依存します)キー)、例: 注文テーブル [Order] (OrderID、OrderDate、CustomerID、CustomerName、CustomerAddr、CustomerCity) 主キーは、(OrderID)、CustomerName、CustomerAddr、CustomerCity ではなく、CustomerID (非主キー列) に直接依存します。主キーに直接依存する場合は、 を渡すことで主キーに依存するため、3NF には準拠しません。

2. アンチパターンとは何ですか?

Paradigm を使用すると、データの冗長性を回避し、データベース領域を削減し、データの整合性を維持する手間を軽減できます。

ただし、データベースの正規化された設計により、より多くのテーブルがデータベース ビジネスに関与することになり、関与するビジネス テーブルは複数のテーブル接続を通じてクエリする必要がある場合があり、これによりパフォーマンスの低下につながり、データベースのシャーディングには適していません。したがって、パフォーマンスを優先するために、データベース構造でアンチパターン設計を使用する必要がある場合があります。つまり、時間とスペースを交換し、テーブル間の関連するクエリを回避するためにデータの冗長性を採用する必要があります。データの一貫性の問題に関しては、強力なデータの一貫性を満たすことは困難であるため、一般に、システムが短期間で自己回復および修正後に最終的に一貫性を確保できるように、保存されたデータはユーザー間で可能な限り一貫している必要があります。期間。

データベースを設計するときは、アンチパターンを慎重に使用する必要があります。一般に、正規化されたデータベース設計により製品の柔軟性が高まり、データベース層でのデータの整合性が維持されるため、できる限り正規化されたデータベース設計を使用してください。

場合によっては、パフォーマンスを向上させる最善の方法は、冗長データを同じテーブルに保存することです。少量のダーティ データが許容できる場合は、完全に独立したサマリー テーブルまたはキャッシュ テーブルを作成することは非常に良い方法です。 。たとえば、ダウンロード数情報をキャッシュする「ダウンロード数テーブル」を設計すると、大規模なデータの場合に合計情報のクエリを高速化できます。

別の典型的なシナリオでは、スケーラビリティを考慮して、BLOB および TEXT 型の列を使用して JSON 構造化データを保存できます。この利点は、テーブルを変更せずに、いつでも新しい属性をこのフィールドに追加できることです。構造。ただし、この設計の欠点も明らかです。指定された属性を取得するには、フィールドの内容全体を取得してデコードする必要があり、インデックス付け、並べ替え、集計などの操作を実行できません。したがって、より複雑な使用シナリオを考慮する必要がある場合は、MongoDB などのドキュメント データベースを使用することをお勧めします。

3. データベース トランザクション

トランザクションは、分割できない一連のデータベース操作であり、データベースの同時実行制御の基本単位です。データベース ある整合性状態から別の整合性状態に変更します。

(1). トランザクションの特性

原子性: トランザクションに含まれる一連のデータベース操作はすべて正常に実行されるか、すべてロールバックされます;

一貫性: トランザクションの実行結果により、データベースはある整合性状態から別の整合性状態に移行する必要があります;

分離: 同時に実行されたトランザクションは相互に影響を与えることはできません;

耐久性: トランザクションがコミットされると、 、データベース内のデータへの変更は永続的です。

(2). トランザクションの同時実行によって引き起こされる問題

ダーティ リード: 1 つのトランザクションが別のトランザクションのコミットされていないデータを読み取る;

Non-repeatable read: 反復不可能読み取りは変更です。同じ条件下で 2 回の読み取りの結果は異なります。つまり、読み取りデータは他のトランザクションによって変更される可能性があります。

ファントム読み取り: ファントム読み取りの焦点は、追加または削除です。 . 同じ条件で2回読み出したレコード数が異なります。

(3). 分離レベル

分離レベルは、あるセッションのトランザクションが別のセッションのトランザクションに与える影響を決定します。

ANSI 標準では、MySQL の InnoDB でサポートされる 4 つの分離レベルが定義されています。つまり、次のとおりです。

READ UNCOMMITTED (非コミット読み取り): 最低レベルの分離で、通常はダーティ リードと呼ばれます。これにより、1 つのトランザクションが許可されます。別のトランザクションによってコミットされていないデータを読み取るため、パフォーマンスが向上する可能性がありますが、ダーティ リードの問題が発生します。

READ COMMITTED (コミットされた読み取り): トランザクション内では他のトランザクションのみが許可されます。この分離レベルでは、反復不可能な読み取りの問題を回避できません。

REPEATABLE READ (反復可能読み取り): トランザクションの開始後、他のトランザクションによるデータベースへの変更は、このトランザクションが完了するまで、このトランザクションでは表示されません。コミットまたはロールバックされます。ただし、他のトランザクションの挿入/削除操作はこのトランザクションから認識されるため、この分離レベルではファントム読み取りの問題を回避できません。トランザクション内で選択を繰り返した場合、このトランザクション内でデータベースが更新されない限り、結果は同じになります。

SERIALIZABLE（可串列化​​）：最高層級的隔離，只允許交易串列執行。

MySQL預設的隔離等級是REPEATABLE READ。

4、什麼是預存程序？有哪些優缺點？

預存程序是事先經過編譯並儲存在資料庫中的一段SQL語句的集合。進一步地說，預存程序是由一些T-SQL語句組成的程式碼區塊，這些T-SQL語句程式碼像一個方法一樣實作一些功能（對單表或多表的增刪改查），然後再給這個程式碼區塊取一個名字，用到這個功能的時候呼叫他就行了。預存程序具有以下特點：

4.1、預存程序只在創建時進行編譯，以後每次執行預存程序都不需再重新編譯，而一般SQL 語句每執行一次就編譯一次，所以使用存儲流程可提高資料庫執行效率；

4.2、當SQL語句有變動時，可以只修改資料庫中的預存程序而不必修改程式碼；

4.3、減少網路傳輸，在客戶端呼叫一個預存程序當然比執行一串SQL傳輸的資料量要小；

4.4、透過預存程序能夠使沒有權限的使用者在控制之下間接地存取資料庫，從而確保資料的安全。

5、簡單說一下drop、delete、truncate的差異

SQL中的drop、delete、truncate都表示刪除，但三者有一些差別：

Delete用來刪除表的全部或一部分資料行，執行delete之後，使用者需要提交(commmit)或回滾(rollback)來執行刪除或撤銷刪除， delete指令會觸發這個表上所有的delete觸發器；

Truncate刪除表中的所有數據，這個操作不能回滾，也不會觸發這個表上的觸發器，TRUNCATE比delete更快，佔用的空間更小；

Drop指令從資料庫中刪除表，所有的資料行，索引和權限也會被刪除，所有的DML觸發器也不會被觸發，這個指令也不能回滾。

因此，在不再需要一張表的時候，用drop；在想刪除部分資料行時候，用delete；在保留表而刪除所有資料的時候用truncate。

6、什麼叫視圖？遊標是什麼？

視圖是一種虛擬的表，通常是有一個表或多個表的行或列的子集，具有和物理表相同的功能，可以對視圖進行增，刪，改，查等操作。特別地，對視圖的修改不影響基本表。相比多表查詢，它使得我們獲取資料更容易。

遊標是對查詢出來的結果集合作為一個單元來有效的處理。遊標可以定在該單元中的特定行，從結果集的目前行檢索一行或多行。可以對結果集目前行做​​修改。一般不使用遊標，但是需要逐條處理資料的時候，遊標顯得十分重要。

在操作mysql的時候，我們知道MySQL檢索操作傳回一組稱為結果集的行。這組傳回的行都是與 SQL語句相符的行（零行或多行）。使用簡單的 SELECT語句，例如，沒有辦法得到第一行、下一行或前 10行，也不存在每次一行地處理所有行的簡單方法（相對於成批地處理它們）。有時，需要在檢索出來的行中前進或後退一行或多行。這就是使用遊標的原因。遊標（cursor）是一個儲存在MySQL伺服器上的資料庫查詢，它不是一條 SELECT語句，而是被該語句檢索出來的結果集。在儲存了遊標之後，應用程式可以根據需要捲動或瀏覽其中的資料。遊標主要用於互動式應用，其中用戶需要滾動螢幕上的數據，並對數據進行瀏覽或做出更改。

7、什麼是觸發器？

觸發器是與表相關的資料庫對象，在滿足定義條件時觸發，並執行觸發器中定義的語句集合。觸發器的這種特性可以協助應用在資料庫端確保資料庫的完整性。

8、超鍵、候選鍵、主鍵、外鍵

超鍵：在關係中能唯一標識元組的屬性集稱為關係模式的超鍵。一個屬性可以為作為一個超鍵，多個屬性組合在一起也可以作為一個超鍵。超鍵包含候選鍵和主鍵。

候選鍵：是最小超鍵，即沒有冗餘元素的超鍵。

主鍵：資料庫表中儲存資料物件給予唯一且完整標識的資料列或屬性的組合。一個資料列只能有一個主鍵，且主鍵的取值不能缺失，即不能為空值（Null）。

外鍵：在一個表中存在的另一個表的主鍵稱此表的外鍵。

9、什麼是事務？什麼是鎖？

事務：就是被綁定在一起作為一個邏輯工作單元的SQL 語句分組，如果任何一個語句操作失敗那麼整個操作就被失敗，以後操作就會回滾到操作前狀態，或者是上有個節點。為了確保要么執行，要么不執行，就可以使用事務。要將有組語句作為事務考慮，就需要通過 ACID 測試，即原子性，一致性，隔離性和持久性。

鎖定：在所以的 DBMS 中，鎖定是實現事務的關鍵，鎖定可以保證事務的完整性和並發性。就像現實生活中鎖一樣，它可以使某些資料的擁有者，在某段時間內不能使用某些資料或資料結構。當然鎖還分級別的。

10、資料庫鎖定機制

資料庫鎖定機制簡單來說就是資料庫為了保證資料的一致性而使各種共享資源在被並發訪問，存取變得有序所設計的一種規則。 MySQL各儲存引擎使用了三種類型（等級）的鎖定機制：行級鎖定，頁級鎖定和表級鎖定。

表單層級鎖定（table-level）：表格層級的鎖定是MySQL各儲存引擎中最大顆粒度的鎖定機制。此鎖定機制最大的特點是實現邏輯非常簡單，帶來的系統負面影響最小。所以取得鎖和釋放鎖的速度很快。由於表級鎖一次會將整個表鎖定，所以可以很好的避免困擾我們的死鎖問題。當然，鎖定顆粒度大所帶來最大的負面影響就是出現鎖定資源爭用的機率也會最高，致使並大度大打折扣。表級鎖分為讀鎖和寫入鎖。頁級鎖定（page-level）：頁級鎖定的特徵是鎖定顆粒度介於行級鎖定與表級鎖之間，所以獲取鎖定所需的資源開銷，以及所能提供的並發處理能力也同樣是介於上面二者之間。另外，頁級鎖定和行級鎖定一樣，會發生死鎖。行級鎖定（row-level）：行級鎖定最大的特性就是鎖定物件的顆粒度很小，也是目前各大資料庫管理軟體所實現的鎖定顆粒度最小的。由於鎖定顆粒度很小，所以發生鎖定資源爭用的機率也最小，能夠給予應用程式盡可能大的並發處理能力而提高一些需要高並發應用系統的整體性能。雖然能夠在並發處理能力上面有較大的優勢，但是行級鎖定也因此帶來了不少弊端。由於鎖定資源的顆粒度很小，所以每次取得鎖和釋放鎖需要做的事情也更多，帶來的消耗自然也更大了。此外，行級鎖定也最容易發生死鎖。 InnoDB的行級鎖同樣分為兩種，共享鎖和排他鎖，同樣InnoDB也引入了意向鎖（表級鎖）的概念，所以也就有了意向共享鎖和意向排他鎖，所以InnoDB其實有四種鎖，分別是共享鎖（S）、排他鎖（X）、意圖共享鎖（IS）、意圖排他鎖（IX）；

在MySQL資料庫中，使用表級鎖定的主要是MyISAM ，Memory，CSV等一些非事務性儲存引擎，而使用行級鎖定的主要是Innodb儲存引擎和NDBCluster儲存引擎，頁級鎖定主要是BerkeleyDB儲存引擎的鎖定方式。

而意向鎖的作用就是當一個事務在需要獲取資源鎖定的時候，如果遇到自己需要的資源已經被排他鎖佔用的時候，該事務可以需要鎖定行的表上面添加一個合適的意向鎖。如果自己需要一個共享鎖，那麼就在表上面加上一個意向共享鎖。而如果自己需要的是某行（或某些行）上面加上一個排他鎖的話，則先在表上面加上一個意向排他鎖。意向共享鎖可以同時並存多個，但是意向排他鎖同時只能有一個存在。

想了解更多相關內容請造訪PHP中文網：mysql影片教學

以上がデータベースの基本理論の詳細な紹介の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。