数据库知识点梳理
數據庫面試常考題
文章目錄
- 數據庫面試常考題
- **一、 left join， right join， inner join區別？**
- **二、 存儲引擎MyIsam和Innodb區別？**
- **三、 mysql的優化手段有哪些？**
- **四、 如何查看Mysql執行計劃？**
- **五、 索引是什么？ 有什么用？ 如何建立？ 索引的底層實現是什么？ 什么情況下適合建立索引， 什么情況下不適合建立索引？**
- **六、 什么是存儲過程？有什么好處？**
- **七、 樂觀鎖和悲觀鎖是什么？**
- **八、 表鎖、頁鎖、行鎖的區別？**
- **九、 having和where的區別？**
- **十一、 事務的特點有哪些？**
- **十二、 觸發器是什么？**
- **十三、 為什么大部分數據庫索引的實現使用B+樹而不是哈希表或紅黑樹之類的？**
一、 left join， right join， inner join區別？
- left join(左聯接) 返回包括左表中的所有記錄和右表中聯結字段相等的記錄
- right join(右聯接) 返回包括右表中的所有記錄和左表中聯結字段相等的記錄
- inner join(等值連接) 只返回兩個表中聯結字段相等的行
注意：在某些數據庫中left join等同于left outer join
舉個例子：
表A記錄如下
| 1 | a20050111 |
| 2 | a20050112 |
| 3 | a20050113 |
| 4 | a20050114 |
| 5 | a20050115 |
表B記錄如下 |bID| bName | |:--|:--| | 1 | 2006032401 | | 2 | 2006032402 | | 3 | 2006032403 | | 4 | 2006032404 | | 8 | 2006032408 |
1、sql語句如下： select * from A left join B on A.aID = B.bID
結果如下： |aID| aNum |bID| bName | |:--|:--|:--|:--| | 1 | a20050111 | 1 | 2006032401 | | 2 | a20050112 | 2 | 2006032402 | | 3 | a20050113 | 3 | 2006032403 | | 4 | a20050114 | 4 | 2006032404 | | 5 | a20050115 | NULL| NULL |
（所影響的行數為5行） **結果說明：** left join是以A表的記錄為基礎的，A可以看成左表，B可以看成右表，left join是以左表為準的。換句話說，左表（A）的記錄將會全部表示出來，而右表（B）只會顯示符合搜索條件的記錄（例子中為：A.aID = B.bID）。B表記錄不足的地方均為NULL。
2、sql語句如下： select * from A right join B on A.aID =B.bID
結果如下： |aID| aNum |bID| bName | |:--|:--|:--|:--| | 1 | a20050111 | 1 | 2006032401 | | 2 | a20050112 | 2 | 2006032402 | | 3 | a20050113 | 3 | 2006032403 | | 4 | a20050114 | 4 | 2006032404 | | NULL | NULL | 8| 2006032408 |
（所影響的行數為5行） **結果說明**： 仔細觀察一下，就會發現，和left join的結果剛好相反，這次是以右表（B）為基礎的，A表不足的地方用NULL填充。
3、sql語句如下： ``` select * from A inner join B on A.aID = B.bID ```
結果如下： | aID | aNum | bID | bName | |:--|:--|:--|:--| | 1 | a20050111 | 1 | 2006032401 | | 2 | a20050112 | 2 | 2006032402 | | 3 | a20050113 | 3 | 2006032403 | | 4 | a20050114 | 4 | 2006032404 |
結果說明： 很明顯，這里只顯示出了A.aID = B.bID的記錄，這說明inner join并不以誰為基礎，它只顯示符合條件的記錄。
二、 存儲引擎MyIsam和Innodb區別？
（1）MyIsam類型不支持事務處理等高級處理，而Innodb類型支持
事務處理是指原子性操作。例如，支持事務處理的Innodb表中，你發了一個帖子執行insert語句，來插入帖子內容，插入后要執行一個update語句來增加你的積分。假設一種特殊情況突然發生，insert成功了，update操作卻沒有被執行。也就是說你發了帖子卻沒有增加相應的積分。這就會造成用戶不滿。如果使用了事務處理，insert和update都放入到事務中去執行，這個時候，只有當insert和update兩條語句都執行生成的時候才會將數據更新、寫入到表中。如果其中任何一條語句失敗，那么就會回滾為初始狀態，不執行寫入。這樣就保證了insert和update肯定是一同執行的。
（2）MyIsam表不支持外鍵。innodb支持外鍵
（3）在執行數據寫入的操作（insert,update,delete）的時候，MyIsam表會鎖表，而innodb表會鎖行。
通俗的講，就是你執行一個update語句，那么MyIsam表會將整個表都鎖住，其他的insert和delete、update都會被拒之門外，等到這個update語句執行完成后才會被依次執行。而鎖行，就是說，你執行update語句時，只會將這一條記錄進行鎖定，只有針對這條記錄的其他寫入、更新操作會被阻塞并等待這條update語句執行完畢后再執行，針對其他記錄的寫入操作不會有影響。但是innodb表的行鎖也不是絕對的，如果在執行一個sql語句時，mysql不能確定要掃描的范圍，innodb表同樣會鎖全表，例如update table set num =1 where name like “%aaa%”
（4）表的具體行數
select count(*) from table , MyIsam只要簡單的讀出保存好的行數，注意的是，當count(*)語句包含where條件時，兩種表的操作是一樣的。
innodb中不保存表的具體行數，也就是說，執行select count(*) from table時，innodb要掃描一遍整個表來計算有多少行。
總結：
因此，當你的數據庫有大量的寫入、更新操作而查詢比較少或者數據完整性要求比較高的時候就選擇innodb表。當你的數據庫主要以查詢為主，相比較而言更新和寫入比較少，并且業務方面數據完整性要求不那么嚴格，就選擇myisam表。因為MyIsam表的查詢操作效率和速度都比innodb要快。
InnoDB的設計目標是處理大容量數據庫系統，它的CPU利用率是其它基于磁盤的關系數據庫引擎所不能比的。
我覺得使用InnoDB可以應對更為復雜的情況，特別是對并發的處理要比MyISAM高效。同時結合memcache也可以緩存SELECT來減少SELECT查詢，從而提高整體性能。
使用以下mysql sql語句，可以給表設定數據庫引擎：
ALTER TABLE `wp_posts` ENGINE = MyISAM;查看當前數據庫的引擎，可看出默認是InnoDB類型。
三、 mysql的優化手段有哪些？
MYSQL數據庫優化的八種方式：
（1）選取最適用的字段屬性
MYSQL可以很好的支持大數據量的存取，但是一般來說，數據庫中的表越小，在它上面執行的查詢也就會越快。因此，在創建表的時候，為了獲得更好的性能，我們可以將表中字段的寬度設得盡可能小。
例如，在定義郵政編碼這個字段時，如果將其設置為CHAR(255)，顯然給數據庫增加了不必要的空間，甚至使用VARCHAR這種類型也是多余的，因為CHAR（6）就可以很好的完成任務了。同樣的，如果可以的話，我們應該使用MEDIUMINT而不是BIGINT來定義整型字段。
另外一個提高效率的方法是在可能的情況下，應該盡量把字段設置為NOT NULL，這樣在將來執行查詢的時候，數據庫不用去比較NULL值。
對于某些文本字段，例如“省份”或者“性別”，我們可以將它們定義為ENUM類型。因為在MYSQL中，ENUM類型被當做數值型數據來處理，而數值型數據被處理起來的速度要比文本類型快得多。這樣，我們又可以提高數據庫的性能。
（2）使用連接（JOIN）來代替子查詢（Sub-Queries）
例如：我們要將客戶基本信息表中沒有任何訂單的客戶刪除掉，就可以利用子查詢先從銷售信息表中將所有發出訂單的客戶ID取出來，然后將結果傳遞給主查詢，如下所示：
如果使用連接（JOIN）來完成這個查詢工作，速度將會快很多。尤其是當salesinfo表中對CustomerID建有索引的話，性能將會更好。查詢如下：
SELECT * FROM customerinfo
LEFT JOIN salesinfo ON customerinfo.customerid = salesinfo.customerif
Where salesinfo.customerid is NULL
連接（JOIN）之所以更有效率一些，是因為MYSQL不需要在內存中創建臨時表來完成這個邏輯上的需要兩個步驟的查詢工作。
（3）使用聯合（UNION）來代替手動創建的臨時表
UNION查詢可以把需要使用臨時表的兩條或更多的select查詢合并在一個查詢中。在客戶端的查詢會話結束的時候，臨時表會被自動刪除，從而保證數據庫整齊、高效。使用union來創建查詢的時候，我們只需要用union作為關鍵字把多個select語句連接起來就可以了，要注意的是所有select語句中的字段數目要相同。
例子：
（4）事務
盡管我們可以使用子查詢、連接（join）和聯合（union）來創建各種各樣的查詢，但不是所有的數據庫操作都可以只用一條或少數幾條sql語句就可以完成的。更多的時候是需要用到一系列的語句來完成某種工作。但是如果這個語句塊中的某一條語句運行出錯的時候，整個語句塊的操作就會變得不確定起來。因此要盡量使用事務，它的作用：要么語句塊中每條語句都操作成功，要么都失敗。換句話說，就是可以保持數據庫中數據的一致性和完整性。事務以BEGIN關鍵字開始，COMMIT關鍵字結束。在這之間的一條SQL操作失敗，那么，rollback命令就可以把數據庫恢復到BEGIN開始之前的狀態。
例子：
事務的另一個重要作用是當多個用戶同時使用相同的數據源時，它可以利用鎖定數據庫的方法來為用戶提供一種安全的訪問方式，這樣可以保證用戶的操作不被其他的用戶所干擾。
（5）鎖定表
通過鎖定表來防止其他的訪問對我們正在操作的表進行插入、更新或者刪除的操作。
例子：
這里，我們用一個select語句取出初始數據，通過一些計算，用update語句將新值更新到表中。包含有WRITE關鍵字的LOCKTABLE語句可以保證在UNLOCKTABLES命令被執行之前，不會有其他的訪問來對inventory進行插入、更新或者刪除的操作。
（6）使用外鍵
鎖定表的方法可以維護數據的完整性，但是它卻不能保證數據的關聯性。這個時候我們就可以使用外鍵。
（7）使用索引
索引是提高數據庫性能的常用方法，它可以令數據庫服務器以比沒有索引快得多的速度檢索特定的行，尤其是在查詢語句當中包含有MAX()，MIN()和ORDERBY這些命令的時候，性能提高更為明顯。
對哪些字段建立索引？
索引應建立在那些將用于JOIN，WHERE判斷和ORDERBY排序的字段上。
全文索引在MYSQL中是一個FULLTEXT類型索引，但僅能用于MYISAM類型的表。對于一個大的數據庫，將數據裝載到一個沒有FULLTEXT索引的表中，然后再使用ALTERTABLE或CREATEINDEX創建索引，將是非常快的。但如果將數據裝載到一個已經有FULLTEXT索引的表中，執行過程將會非常慢。
（8）優化查詢語句
1、最好在相同類型的字段間進行比較的操作
2、在建有索引的字段上盡量不要使用函數進行操作。
例如，在一個DATE類型的字段上使用YEAR（）函數時，將會使索引不能發揮應有的作用。所以下面的兩個查詢雖然返回的結果一樣，但后者要比前者快的多。
3、在搜索字符型字段時，我們有時會使用LIKE關鍵字和通配符，這種做法雖然簡單，但卻也是以犧牲系統性能為代價的。
例如下面的查詢將會比較表中的每一條記錄
但是如果換用下面的查詢，返回的結果一樣，但速度就要快上很多
SELECT * FROM books WEHRE name>=“MYSQL” and name < “MYSQM”最后，應該注意避免在查詢中讓MYSQL進行自動類型轉換，因為轉換過程也會使索引變得不起作用。
四、 如何查看Mysql執行計劃？
mysql的查看執行計劃的語句：explain+你要執行的sql語句
例如：
id是一組數字，表示查詢中執行select子句或操作表的順序。id如果相同，則可以認為是一組，從上往下順序執行，所有組中，id越高，優先級越高，越容易執行。
select_type有simple,primary,subquery,derived(衍生),union,unionresult.
simple表示查詢中不包含子查詢或者union。
當查詢中包含任何復雜的子部分，最外層的查詢被標記成primary。在select或where列表中包含了子查詢，則子查詢被標記成subquery。在from的列表中包含的子查詢被標記成derived。…省略
參考網址：
http://www.cnblogs.com/ggjucheng/archive/2012/11/11/2765237.html
mysql執行計劃的局限
五、 索引是什么？ 有什么用？ 如何建立？ 索引的底層實現是什么？ 什么情況下適合建立索引， 什么情況下不適合建立索引？
（1）什么索引？
數據庫索引，是幫助數據庫系統高效獲取數據的數據結構，以協助快速查詢、更新數據庫表中數據。
（2）建立索引的目的？
在數據庫系統中建立索引主要有以下作用：
（3）缺點
1. 索引需要占物理內存
2. 當對表中的數據進行增加、刪除和修改的時候，索引也要動態的維護，降低了數據的維護速度
（4）mysql是使用B+樹實現其索引結構
（5）創建索引的辦法：
例如使用create index語句或者使用創建索引向導
例如在表中定義主鍵約束或者唯一性鍵約束時，同時也創建了索引。
當在表中定義主鍵或者唯一性鍵約束時，如果表中已經有了使用create index語句創建的標準索引時，那么主鍵約束或者唯一性鍵約束創建的索引覆蓋以前創建的標準索引。也就是說，主鍵約束或者唯一性鍵約束創建的索引的優先級高于使用create index語句創建的索引。
（6）幾種索引類型的比較
聚集索引：物理存儲按照索引排序（表中行的物理順序與鍵值的索引順序相同），葉子結點即存儲了真實的數據行。
非聚集索引：物理存儲位置不按照索引排序，葉子結點包含索引字段值及指向數據頁數據行的邏輯指針。
一張表中只能創建一個聚集索引，但表中的每一列都可以有自己的非聚集索引。
唯一性索引：這一列數據不重復，只能一個為NULL
主鍵索引：主鍵索引是唯一索引的特定類型。不重復，不允許為空。主鍵只能有一個。
普通索引：create index等建立的索引，alter tablename add index …
(7) 外鍵約束的要求：
六、 什么是存儲過程？有什么好處？
（1）什么是存儲過程？
存儲過程是一個被定義并保存在數據庫服務器中的sql語句集，是一種介于應用程序和數據庫間的編程接口，也是封裝重復性工作的一種有效方法，它支持用戶變量、條件執行及其它的編程功能。
（2）存儲過程的語法
CREATE PROCEDURE procedure_name([paramters[,...]])[attributes] BEGINbody_statement END;注：
procedure_name：存儲過程的名字
paramters：存儲過程的過程參數，包含：IN、OUT及INOUT。IN代表輸入或傳入值，在存儲過程中被修改，但不返回；OUT代表輸出或傳出值，在存儲過程中被修改，并返回；INOUT代表輸入輸出，在存儲過程中被修改，并返回；body_statement：存儲過程體，這里可以放入sql集，也可以內嵌存儲過程。
好處：
相對于直接使用sql語句，在應用程序中直接調用存儲過程有以下好處：
（1）減少網絡通信量。調用一個行數不多的存儲過程與直接調用sql語句的網絡通信量可能不會有很大的差別，可是如果存儲過程包含上百行sql語句，那么其性能絕對比一條一條的調用sql語句要高的多。
（2）執行速度更快。有兩個原因：首先，在存儲過程創建的時候，數據庫已經對其進行了一次解析和優化。其次，存儲過程一旦執行，在內存中就會保留一份這個存儲過程，這樣下次再執行同樣的存儲過程時，可以從內存中直接調用。
（3）更強的適應性：由于存儲過程對數據庫的訪問時通過存儲過程來進行的，因此數據庫開發人員可以在不改動存儲過程接口的情況下對數據庫進行任何改動，而這些改動不會對應用程序造成影響。
（4）布式工作：應用程序和數據庫的編碼工作可以分別獨立進行，而不會相互壓制。
七、 樂觀鎖和悲觀鎖是什么？
樂觀鎖和悲觀鎖是兩種并發控制機制。
首先了解一下為什么需要鎖（并發控制）？
在多用戶環境中，在同一時間可能會有多個用戶更新相同的記錄，這會產生沖突。這就是著名的并發性問題。
典型的沖突有：
（1）丟失更新：一個事務的更新覆蓋了其他事務的更新結果，這就是所謂的更新丟失。例如：用戶A把值從6改為2，用戶B把值從2改為6，則用戶A丟失了他的更新。
（2）臟讀：又稱無效數據的讀出，是指在數據庫訪問中，事務T1將某一值修改，然后事務T2讀取該值，此后T1因為某種原因撤銷對該值的修改，這就導致了T2所讀取到到的數據是無效的。臟讀就是指當一個事務正在訪問數據，并且對數據進行了修改，而這種修改還沒有提交到數據庫中，這時，另外一個事務也訪問這個數據，然后使用了這個數據。
并發控制機制：
最常用的處理多用戶并發訪問的方法是加鎖。當一個用戶鎖住數據庫中的某個對象時，其他用戶就不能再訪問該對象。加鎖對并發訪問的影響體現在鎖的粒度上。比如，放在一個表上的鎖限制對整個表的并發訪問；放在數據頁上的鎖限制了對整個數據頁的訪問；放在行上的鎖只限制對該行的并發訪問。可見行鎖粒度最小，并發訪問最好，頁鎖粒度最大，表鎖介于兩者之間。
悲觀鎖：假定會發生并發沖突，屏蔽一切可能違反數據完整性的操作。
悲觀鎖假定其他用戶企圖訪問或者改變你正在訪問、更改的對象的概率是很高的，因此在悲觀鎖的環境中，在你開始改變此對象之前就將該對象鎖住，并且直到你提交了所作的更改之后才釋放鎖。悲觀的缺陷是不論是頁鎖還是行鎖，加鎖的時間可能會很長，這樣可能會長時間的限制其他用戶的訪問，也就是說悲觀鎖的并發訪問性不好。
樂觀鎖：假設不會發生并發沖突，只在提交操作時檢查是否違反數據完整性。
樂觀鎖不能解決臟讀的問題。樂觀鎖則認為其他用戶企圖改變你正在更改的對象的概率是很小的，因此樂觀鎖直到你準備提交所作的更改時才將對象鎖住，當你讀取以及改變該對象時并不加鎖。可見樂觀鎖加鎖的時間要比悲觀鎖短，樂觀鎖可以用較大的鎖粒度獲得較好的并發訪問性能。但是如果第二個用戶恰好在第一個用戶提交更改之前讀取了該對象，那么當他完成了自己的更改進行提交時，數據庫就會發現該對象已經變化了，這樣，第二個用戶不得不重新讀取該對象并作出更改。這說明在樂觀鎖環境中，會增加并發用戶讀取對象的次數。
樂觀鎖的應用：
（1）使用自增長的整數表示數據版本號。更新時檢查版本號是否一致，比如數據庫中數據版本為6，更新提交時version=6+1，使用該version值（=7）與數據庫version+1(=7)作比較，如果相等，則可以更新，如果不等則有可能其他程序已更新該記錄，所以返回錯誤。
（2）使用時間戳來實現。
悲觀鎖應用：
需要使用數據庫的鎖機制，比如SQL server的TABLOCKX(排它表鎖)此選項被選中時，SQL server將在整個表上置排它鎖直至該命令或事務結束。這將防止其他進程讀取或修改表中的數據。
總結：
在實際生產環境里邊，如果并發量不大且不允許臟讀，可以使用悲觀鎖解決并發問題；但如果系統的并發非常大的話，悲觀鎖定會帶來非常大的性能問題，所以我們就要選擇樂觀鎖定的方法。
八、 表鎖、頁鎖、行鎖的區別？
行級鎖是mysql中鎖定粒度最細的一種鎖，表示只針對當前操作的行進行加鎖。行級鎖能大大減少數據庫操作的沖突。其加鎖粒度最小，但加鎖的開銷也最大。行級鎖分為共享鎖和排他鎖。
特點：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，并發度也最高。
表級鎖是mysql中鎖定粒度最大的一種鎖，表示對當前操作的整張表加鎖，它實現簡單，資源消耗較少，被大部分Mysql引擎支持。最常使用的myisam和innodb都支持表級鎖定。表級鎖定分為表共享讀鎖（共享鎖）和表獨占寫鎖（排他鎖）。
特點：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發出鎖沖突的概率最高，并發度最低。
頁級鎖是mysql鐘鎖定粒度介于行級鎖和表級鎖中間的一種鎖。表級鎖速度快，但沖突多，行級沖突少，但速度慢。所以取了折中的頁級，一次鎖定相鄰的一組記錄。BDB支持頁級表。
特點：開銷和加鎖時間介于表鎖和行鎖之間；會出現死鎖；鎖定粒度介于表鎖和行鎖之間，并發度一般。
上述三種鎖的特性可大致歸納如下：
1） 表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的概率最高，并發度最低。
2） 行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低，并發度也最高。
3） 頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般。
九、 having和where的區別？
where語句在group by語句之前，sql會在分組之前計算where語句。
having語句在group by語句之后，sql會在分組之后計算having語句。
where是一個約束聲明，使用where約束來自數據庫的數據，where是在結果返回之前起作用的，where中不能使用聚合函數。having是一個過濾聲明，是在查詢返回結果集以后對查詢結果進行的過濾操作，在having中可以使用聚合函數。
在查詢過程中聚合語句（sum,min,max,avg,count）要比having子句優先執行。而where子句在查詢過程中執行優先級高于聚合語句。
例一：要查找平均工資大于3000的部門，則sql語句應為：
select department, avg(salary) as average from salary_info group by department having average>3000此時只能使用having，而不能使用where。一來，我們要使用聚合語句avg；二來，我們要對聚合后的結果進行篩選（average>3000），因此使用where會被告知sql有誤。
例二：要查詢每個部門工資大于3000的員工個數
select department, count(*) as c from salary_info where salary>3000 group by department.此處的where不可用having進行替換，因為是直接對庫中的數據進行篩選，而非對結果集進行篩選。
where和having的執行級別不同
在查詢過程中聚合語句(sum,min,max,avg,count)要比having子句優先執行.而where子句在查詢過程中執行優先級別優先于聚合語句(sum,min,max,avg,count)。
having就是來彌補where在分組數據判斷時的不足。因為where執行優先級別要快于聚合語句。
##十、 事務的隔離級別？
| 未提交讀 | 可能 | 可能 | 可能 |
| 已提交讀 | 不可能 | 可能 | 可能 |
| 可重復讀 | 不可能 | 不可能 | 可能 |
| 可串行化 | 不可能 | 不可能 | 不可能 |
**未提交讀**：允許臟讀，也就是可能讀取到其他會話中未提交事務修改的數據。 **已提交讀**：只能讀取到已經提交的數據。Oracle等多數數據庫默認是該級別（不重復讀）。 **可重復讀**：在同一個事務內的查詢都是事務開始時刻一致的，innodb默認級別。在sql標準中，該隔離級別消除了不可重復讀，但是還存在幻象讀。 **串行讀**：完全串行化的讀，每次讀都需要獲得表級共享鎖，讀寫相互都會阻塞。
四個級別逐漸增強，每個級別解決一個問題。事務級別越高,性能越差
臟讀：又稱無效數據的讀出，是指在數據庫訪問中，事務T1將某一值修改，然后事務T2讀取該值，此后T1因為某種原因撤銷對該值的修改，這就導致了T2所讀取到到的數據是無效的。臟讀就是指當一個事務正在訪問數據，并且對數據進行了修改，而這種修改還沒有提交到數據庫中，這時，另外一個事務也訪問這個數據，然后使用了這個數據。
不可重復讀：是指在一個事務中，多次讀同一數據。在這個事務還沒有結束時，另外一個事務也訪問該同一數據。那么，在第一個事務中的兩次讀數據之間，由于第二個事務的修改，那么第一個事務兩次讀到的數據可能是不一樣的。這樣就發生了在一個事務內兩次讀到的數據是不一樣的，因此稱為是不可重復讀。
可重復讀：第一個事務兩次讀到的數據是一樣的。
幻讀：第一個事務對一個表中的數據進行了修改，這種修改涉及到表中的全部數據行。同時，第二個事務也修改了這個表中的數據，這種修改是向表中插入一行新數據。那么，以后就會發生操作第一個事務的用戶發現表中還有沒有修改的數據行，就好像發生了幻覺一樣。
本事務中第一次讀取出一行，做了一次更新后，另一個事務里提交的數據就出現了。也可以看做是一種幻讀。
一道筆試題：
小桔A在事務1中第一次讀取年齡20歲的員工總數為1000人，之后小桔B在事務2中增加了100名年齡20歲的新員工，之后小桔A在事務1中再次讀取年齡20歲的員工數發現總數變為1100人，屬于（）？
A、臟讀
B、不可重復讀
C、幻讀
選C
幻讀的重點在于新增或者刪除
同樣的條件，第一次和第二次讀出來的記錄數不一樣
例子：
目前工資為1000的員工有10人
事務1，讀取所有工資為1000的員工，共讀取10條記錄。
這時另一個事務向employee表插入了一條員工記錄。
事務1再次讀取所有工資為1000的員工共讀取到了11條記錄，這就產生了幻像讀
不可重復讀的重點是修改
同樣的條件，你讀取過的數據，再次讀取出來發現值不一樣了
例如：
在事務1中，Mary讀取了自己的工資為1000，操作并沒有完成。
在事務2中，這時財務人員修改了Mary的工資為2000，并提交了事務。
在事務1中，Mary再次讀取自己的工資時，工資變味了2000。
臟讀：
臟讀就是指當一個事務正在訪問數據，并且對數據進行了修改，而這種修改還沒有提交到數據庫中，這時，另外一個事務也訪問這個數據，然后使用了這個數據。
十一、 事務的特點有哪些？
ACID含義：
原子性（Atomicity）：一個事務要么發生，要么不發生。
例如故障發生在write(A)和read(B)之間，則將有可能造成賬戶A的余額已經減少50元錢，而賬戶B的余額卻沒有改變，憑空就少了50元錢。
一致性（Consistency）：數據庫中數據的完整性，保證他們的正確性。
隔離性（Isolation）：多個事務并發（同時）執行，互相不影響
持久性（Durability）：每個事務成功執行后對數據庫的修改是永久的。即使系統出現故障也不受影響。
十二、 觸發器是什么？
觸發器是SQL server提供給程序員和數據分析員來保證數據完整性的一種方法，它是與表事件相關的特殊存儲過程，它的執行不是由程序調用，也不是手工啟動，而是由事件觸發。比如當對一個表進行操作（insert，delete，update）時就會激活它執行。觸發器經常用于加強數據的完整性約束和業務規則等。
在mysql中，創建觸發器語法如下：
Create trigger trigger_name trigger_time trigger_event on tbl_name for each row trigger_stmt其中：
Trigger_name：標識觸發器名稱，用戶自行指定
trigger_time：標識觸發時機，取值為before或after
Trigger_event：標識觸發事件，取值為insert,update或delete
Tbl_name：標識建立觸發器的表名，即在哪張表上建立觸發器
Trigger_stmt：觸發器程序體，可以是一句sql語句，或者用begin和end包含的多條語句。
由此可見，可以建立6種觸發器，即：before insert、before update、before delete、after insert、after update、after delete。
注意：不能同時在一個表上建立2個相同類型的觸發器，因此在一個表上最多建立6個觸發器。
SQL Server包括三種常規類型的觸發器：DML觸發器、DDL觸發器和登錄觸發器
DML觸發器
當數據庫中表中的數據發生變化時，包括insert，update，delete任意操作，如果我們對該表寫了對應的DML觸發器，那么該觸發器自動執行。DML觸發器的主要作用在于強制執行業務規則，以及擴展sql server約束，默認值等。因為我們知道約束只能約束同一個表中的數據，而觸發器中則可以執行任意sql命令。
DDL觸發器
主要用于審核與規范對數據庫中表，觸發器，視圖等結構上的操作。比如在修改表，修改列，新增表，新增列等。它在數據庫結構發生變化時執行，我們主要用它來記錄數據庫的修改過程，以及限制程序員對數據庫的修改，比如不允許刪除某些指定表等。
登錄觸發器
登錄觸發器將為響應LOGIN事件而激發存儲過程。與SQL server實例建立用戶會話時將引發此事件。登陸觸發器將在登錄的身份驗證階段完成之后且用戶會話實際建立之前激發。因此，來自觸發器內部且通常將到達用戶的所有消息會傳送到SQL Server錯誤日志。如果身份驗證失敗，將不激發登錄觸發器。
觸發器的作用：
1、可在寫入數據表前，強制檢驗或轉換數據
2、觸發器發生錯誤時，異動的結果會被撤銷
3、部分數據庫管理系統可以針對數據定義語言（DDL）使用觸發器，稱為DDL觸發器
4、可依照特定的情況，替換異動的指令（INSTEAD OF）
DML觸發器又可分為After觸發器和Instead Of觸發器
（1）After觸發器：這類觸發器是在記錄已經改變完之后（after），才會被激活執行，它主要是用于記錄變更后的處理或檢查，一旦發現錯誤，也可以用Rollback Transaction語句來回滾本次的操作。
（2）Instead of觸發器：這類觸發器一般是用來取代原本的操作，在記錄變更之前發生的，它并不去執行原來SQL語句里的操作（insert、update、delete），而去執行觸發器本身所定義的操作。
在SQL Server里，每個DML觸發器都分配有兩個特殊的表，一個是Inserted表，一個是Deleted表。它們兩個存在于數據庫服務器的內存中，是由系統管理的邏輯表，是兩個臨時表，而不是真正存儲在數據庫中的物理表。用戶對這兩個表只有讀取的權限，沒有修改的權限。
這兩個表的結構（主外鍵、字段、數據類型等）與觸發器所在數據表的結構是完全一致的，當觸發器的工作完成之后，這兩個表也將會從內存中刪除。
Inserted和Deleted兩個表的作用：
Inserted：對于插入記錄操作來說，插入表里存放的是要插入的數據；對于更新記錄操作來說，插入表里存放的是要更新的記錄。
Deleted：對于更新記錄操作來說，刪除表里存放的是被更新記錄；對于刪除記錄操作來說，刪除表里存入的是被刪除的舊記錄。
網上找的一張圖，可以幫助理解一下過程。
**注意事項：** （1）只有表才可以支持觸發器，視圖和臨時表都不支持觸發器 （2）每個表的每個事件只支持一個觸發器，因此每個表最多支持6個觸發器（觸發器應該相應的行動insert,delete,update，觸發器何時執行before,after，2*3=6種） （3）單一觸發器不能與多個操作相關 （4）觸發器不能更新和覆蓋，如果想更新一個觸發器必須先刪除，再創建。
十三、 為什么大部分數據庫索引的實現使用B+樹而不是哈希表或紅黑樹之類的？
前言
動態查找樹主要有：二叉查找樹，平衡二叉查找樹，紅黑樹，B- tree/B+ tree/B* tree。前三者是典型的二叉查找樹結構，其查詢的時間復雜度都與樹的深度相關，那么降低樹的深度自然會提高查找效率。
磁盤存儲的知識
計算機存儲設備一般分為兩種：內存儲器(main memory)和外存儲器(external memory)。內存存取速度快，但容量小，價格昂貴，而且不能長期保存數據（在不通電情況下數據會消失）
索引一般以文件形式存儲在磁盤上，索引檢索需要磁盤I/O操作。與主存不同，磁盤I/O存在機械運動耗費，因此磁盤I/O的時間消耗是巨大的。從網上找了一張磁盤構造圖如下所示：
除了最頂端和最底端的外側面不存儲數據之外，一共有4個面可以用來保存信息。當磁盤驅動器執行讀/寫功能時，盤片裝在一個主軸上，并繞主軸高速旋轉，當磁盤在讀/寫頭（又叫磁頭）下通過時，就可以進行數據的讀/寫了。
盤片被劃分成一系列同心環，圓心是盤片中心，每個同心環叫做一個磁道，所有半徑相同的磁道組成一個柱面。磁道被沿半徑線劃分成一個個小的段，每個段叫做一個扇區，每個扇區是磁盤的最小存儲單元。
當需要從磁盤讀取數據時，系統會將數據邏輯地址傳給磁盤，磁盤的控制電路按照尋址邏輯地址翻譯成物理地址，即確定要讀的數據在哪個磁道，哪個扇區。為了讀取這個扇區的數據，需要將磁頭放到這個扇區上方，為了實現這一點，磁頭需要移動對準相應磁道，這個過程叫做尋道，所耗費時間叫做尋道時間，然后磁盤旋轉將目標扇區旋轉到磁頭下，這個過程耗費的時間叫做旋轉時間。
**讀取數據順序：**先找到磁道（尋道時間）、然后旋轉到目標扇區（旋轉時間）、然后讀取數據
局部性原理與磁盤預讀
由于存儲介質的特性，磁盤本身存取就比主存慢很多，再加上機械運動耗費，磁盤的存取速度往往是主存的幾百分之一，因此為了提高效率，要盡量減少磁盤I/O。為了達到這個目的，磁盤往往不是嚴格按需讀取，而是每次都會預讀，即使只需要一個字節，磁盤也會從這個位置開始，順序向后讀取一定長度的數據放入內存。這樣做的理論依據是計算機科學中著名的局部性原理。
所謂的局部性原理是指：當一個數據被用到時，其附近的數據也通常會馬上被使用。程序運行期間所需要的數據通常比較集中。由于磁盤順序讀取的效率很高（不需要尋道時間，只需很少的旋轉時間），因此對于具有局部性的程序來說，預讀可以提高I/O效率。
預讀的長度一般為頁的整倍數。頁式計算機管理存儲器的邏輯塊，硬件及操作系統往往將主存和磁盤存儲區分割為連續的大小相等的塊，每個存儲塊稱為一頁（在許多操作系統中，頁的大小通常為4K），主存和磁盤以頁為單位交換數據。當程序要讀取的數據不在主存中時，會觸發一個缺頁異常，此時系統會向磁盤發出讀盤信號，磁盤會找到數據的起始位置并向后連續讀取一頁或幾頁載入內存中，然后異常返回，程序繼續運行。
為什么目前大部分數據庫系統及文件系統都采用B樹或B+樹作為索引結構？
1、紅黑樹
紅黑樹的定義：一種自平衡二叉查找樹，但是每個結點上增加一個存儲位表示結點的顏色，通過對任何一條從根到葉子的路徑上各個結點著色方式的限制，紅黑樹確保沒有一條路徑會比其他路徑長出兩倍，因而是接近平衡的。
紅黑樹的特性：
首先，作為一棵二叉查找樹，具備二叉查找樹的特性：
1、若任意結點的左子樹不空，則左子樹上所有結點的值均小于它的根節點的值。
2、若任意結點的右子樹不空，則右子樹上所有結點的值均大于它的根節點的值。
3、任意結點的左、右子樹也分別為二叉查找樹。
4、沒有鍵值相等的結點
紅黑樹的另外5個特性，保證了一棵n個結點的紅黑樹的高度始終保持在logn，也就解釋了“紅黑樹的查找、插入、刪除的時間復雜度最壞是o(logn)”
1、每個結點要么是紅的要么是黑的
2、根節點是黑色的
3、每個葉節點（葉節點即樹尾端NIL指針或NULL結點）都是黑的
4、如果一個結點時紅的，那么它的兩個兒子都是黑的
5、對于任意結點而言，其到葉節點樹尾端NIL指針的每條路徑都包含相同數目的黑結點。
2、B樹
B樹與紅黑樹最大的不同在于，B樹的結點可以有許多子女，從幾個到幾千個。為什么又說B樹與紅黑樹很相似呢？因為與紅黑樹一樣，一棵含n個結點的B樹的高度也為o(lgn)，但可能比一棵紅黑樹的高度小許多，因為它的分支因子比較大。所以，B樹可以在o(logn)時間內，實現各種如插入、刪除等動態集合操作。
這是一棵B樹，一顆關鍵字為英語中輔音字母的B樹，現在要從樹中查找字母R，一個內結點x若含有n[x]個關鍵字，那么x將含有n[x]+1個子女，例如2個關鍵字D H的內結點有3個子女，而含有3個關鍵字Q T X的內結點有4個子女。所有的葉結點都處于相同的深度，背景為白色的結點為查找字母R時要檢查的結點。
一棵m階的B樹的特性如下：
1、每個結點至多有m棵子樹
2、除根節點外，其他每個分支結點至少有ceil(m/2)棵子樹（ceil是一個向上取整的函數）
3、根節點至少有兩棵子樹（除非B樹只包含一個結點）
4、所有葉結點在同一層上。B樹的葉節點可以看成一種外部結點，不包含任何信息。
5、有j個孩子的非葉結點恰好有j-1個關鍵碼，關鍵碼按遞增次序排列。
3、B+樹
一棵m階的B+樹和m階的B樹的異同點在于：
1、B+樹：有n棵子樹的結點含有n個關鍵字，每個關鍵字不保存數據，只用來索引，所有數據都保存在葉子結點
B樹：有n棵子樹的結點還有n-1個關鍵字。
2、B+樹：所有的葉子結點中包含了全部關鍵字的信息，及指向含有這些關鍵字記錄的指針，且葉子結點本身依關鍵字的大小自小而大的順序鏈接。
3、B+樹：所有的非終端結點可以看成是索引部分，結點中僅含有其子樹根節點中最大（或最小）的關鍵字。
通常在B+樹上有兩個頭指針，一個指向根節點，一個指向關鍵字最小的葉子結點
為什么說B+樹比B樹更適合實際應用中操作系統的文件索引和數據庫索引？
（1）B+樹的磁盤讀寫代碼更低
B+樹的內部結點并沒有指向關鍵字具體信息的指針，因此其內部結點相比B樹更小。如果把所有同一內部結點的關鍵字存放在同一盤塊中，那么盤塊所能容納的關鍵字數量也越多。一次性讀入內存中的需要查找的關鍵字也就越多。相對來說IO讀寫次數也就降低了。
（2）B+樹的查詢效率更加穩定
由于非終結點并不是最終指向文件內容的結點，而只是葉子結點中關鍵字的索引。所以任何關鍵字的查找必須走一條從根節點到葉子結點的路。所有關鍵字查詢的路徑長度相同，導致每一個數據的查詢效率相當。
總結
- 上一篇： Labview实现画板
- 下一篇： 考研复试数据库知识总结