天天看點
傳回

面試常考知識點—資料庫

1.存儲過程

是什麼？

我們常用的關系型資料庫是MySQL，操作資料庫的語言一般為SQL語句，SQL在執行的時候需要要先編譯，然後執行，而存儲過程（Stored Procedure）是一組為了完成某種特定功能的SQL語句集，經編譯後存儲在資料庫中，使用者通過指定存儲過程的名字并給定參數（如果該存儲過程帶有參數）來調用執行它。

一個存儲過程是一個可程式設計的函數，它在資料庫中建立并儲存。它可以有SQL語句和一些特殊的控制結構組成。當希望在不同的應用程式或平台上執行相同的函數，或者封裝特定功能時，存儲過程是非常有用的。資料庫中的存儲過程可以看做是對面向對象方法的模拟，它允許控制資料的通路方式。

優點

(1)存儲過程增強了SQL語言的功能和靈活性：存儲過程可以用流控制語句編寫，有很強的靈活性，可以完成複雜的判斷和較複雜的運算。

(2)存儲過程允許标準元件式程式設計：存儲過程被建立後，可以在程式中被多次調用，而不必重新編寫該存儲過程的SQL語句。而且可以随時對存儲過程進行修改，對應用程式源代碼毫無影響。

(3)存儲過程能實作較快的執行速度：如果某一操作包含大量的Transaction-SQL代碼或分别被多次執行，那麼存儲過程要比批處理的執行速度快很多。因為存儲過程是預編譯的。在首次運作一個存儲過程時，優化器對其進行分析優化，并且給出最終被存儲在系統表中的執行計劃。而批處理的Transaction-SQL語句在每次運作時都要進行編譯和優化，速度相對要慢一些。

(4)存儲過程能減少網絡流量：針對同一個資料庫對象的操作（如查詢、修改），如果這一操作所涉及的Transaction-SQL語句被組織成存儲過程，那麼當在客戶計算機上調用該存儲過程時，網絡中傳送的隻是該調用語句，進而大大增加了網絡流量并降低了網絡負載。

(5)存儲過程可被作為一種安全機制來充分利用：系統管理者通過執行某一存儲過程的權限進行限制，能夠實作對相應的資料的通路權限的限制，避免了非授權使用者對資料的通路，保證了資料的安全。

2.索引

是什麼？

索引（Index）是幫助MySQL高效擷取資料的資料結構；在資料之外，資料庫系統還維護着滿足特定查找算法的資料結構，這些資料結構以某種方式引用（指向）資料，可以在這些資料結構上實作進階查找算法，提高查詢速度，這種資料結構，就是索引。

索引存儲分類

索引是在MySQL的存儲引擎層中實作的，而不是在服務層實作的。是以各種存儲引擎支援的索引并不相同，MySQL目前提供了以下4種索引。

B-Tree 索引：最常見的索引類型，大部分引擎都支援B樹索引。

HASH 索引：隻有Memory引擎支援，使用場景簡單。

R-Tree 索引(空間索引)：空間索引是MyISAM的一種特殊索引類型，主要用于地理空間資料類型。

Full-text (全文索引)：全文索引也是MyISAM的一種特殊索引類型，主要用于全文索引，InnoDB從MySQL5.6版本提供對全文索引的支援。

B-TREE索引類型

普通索引

這是最基本的索引類型，而且它沒有唯一性之類的限制，可以通過以下幾種方式建立：

（1）建立索引: CREATE INDEX 索引名 ON 表名(列名1，列名2,…);

（2）修改表: ALTER TABLE 表名 ADD INDEX 索引名 (列名1，列名2,…);

（3）建立表時指定索引：CREATE TABLE 表名 ( […], INDEX 索引名 (列名1，列名 2,…) );

UNIQUE索引

表示唯一的，不允許重複的索引，若某一字段的資訊不能重複（例如身份證号），可以将該字段的索引設定為unique：

（1）建立索引：CREATE UNIQUE INDEX 索引名 ON 表名(列名1，列名2,…);

（2）修改表：ALTER TABLE 表名ADD UNIQUE 索引名 (列名1，列名2,…);

（3）建立表時指定索引：CREATE TABLE 表名( […], UNIQUE 索引名 (列名1，列名2,…));

主鍵：PRIMARY KEY索引

主鍵是一種唯一性索引，但它必須指定為“PRIMARY KEY”。可以将其了解為 索引名固定為 PRIMARY KEY 的 UNIQUE索引。

（1）主鍵一般在建立表的時候指定：“CREATE TABLE 表名( […], PRIMARY KEY (列的清單) ); ”。

（2）但是，我們也可以通過修改表的方式加入主鍵：“ALTER TABLE 表名 ADD PRIMARY KEY (列的清單); ”。

每個表隻能有一個主鍵。 （主鍵相當于聚合索引，是查找最快的索引）

注：不能用CREATE INDEX語句建立PRIMARY KEY索引

常用文法

設定索引

在執行CREATE TABLE語句時可以建立索引，也可以單獨用CREATE INDEX或ALTER TABLE來為資料表增加索引。

1．ALTER TABLE - ALTER TABLE可以用來建立普通索引、UNIQUE索引或PRIMARY KEY索引。

ALTER TABLE table_name ADD INDEX index_name (column_list)

ALTER TABLE table_name ADD UNIQUE index_name (column_list)

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

2．CREATE INDEX - CREATE INDEX可對表增加普通索引或UNIQUE索引。

CREATE INDEX index_name ON table_name (column_list)

CREATE UNIQUE INDEX index_name ON table_name (column_list)

删除索引

可利用ALTER TABLE或DROP INDEX語句來删除索引。類似于CREATE INDEX語句，DROP INDEX可以在ALTER TABLE内部作為一條語句處理，文法如下。

DROP INDEX index_name ON talbe_name

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY

其中，前兩條語句是等價的，删除掉table_name中名為index_name的索引。

第3條語句隻在删除PRIMARY KEY索引時使用，因為一個表隻可能有一個PRIMARY KEY索引，是以不需要指定索引名。如果沒有建立PRIMARY KEY索引，但表具有一個或多個UNIQUE索引，則MySQL将删除第一個UNIQUE索引。

如果從表中删除了某列，則索引會受到影響。對于多列組合的索引，如果删除其中的某列，則該列也會從索引中删除。如果删除組成索引的所有列，則整個索引将被删除。

檢視索引

mysql> show index from tblname;

設定索引的原則

  1. 較頻繁的作為查詢條件的字段應該建立索引
  2. 唯一性太差的字段不适合單獨建立索引，即使頻繁作為查詢條件
  3. 更新非常頻繁的字段不适合建立索引
  4. 不會出現在 WHERE 子句中的字段不該建立索引

  5. 索引的選擇性較低不宜建索引

    注：所謂索引的選擇性（Selectivity），是指不重複的索引值（也叫基數，Cardinality）與表記錄數的比值，顯然選擇性的取值範圍為(0, 1]：

索引的弊端

索引是有代價的：索引檔案本身要消耗存儲空間，同時索引會加重插入、删除和修改記錄時的負擔，另外，MySQL在運作時也要消耗資源維護索引，是以索引并不是越多越好。

參考1

3.B+ 樹

面試常考知識點—資料庫

如上圖，是一顆b+樹，淺藍色的塊我們稱之為一個磁盤塊，可以看到每個磁盤塊包含幾個資料項（深藍色所示）和指針（黃色所示），如磁盤塊1包含資料項17和35，包含指針P1、P2、P3，P1表示小于17的磁盤塊，P2表示在17和35之間的磁盤塊，P3表示大于35的磁盤塊。真實的資料存在于葉子節點即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非葉子節點不存儲真實的資料，隻存儲指引搜尋方向的資料項，如17、35并不真實存在于資料表中。

b+樹的查找過程

如圖所示，如果要查找資料項29，那麼首先會把磁盤塊1由磁盤加載到記憶體，此時發生一次IO，在記憶體中用二分查找确定29在17和35之間，鎖定磁盤塊1的P2指針，記憶體時間因為非常短（相比磁盤的IO）可以忽略不計，通過磁盤塊1的P2指針的磁盤位址把磁盤塊3由磁盤加載到記憶體，發生第二次IO，29在26和30之間，鎖定磁盤塊3的P2指針，通過指針加載磁盤塊8到記憶體，發生第三次IO，同時記憶體中做二分查找找到29，結束查詢，總計三次IO。真實的情況是，3層的b+樹可以表示上百萬的資料，如果上百萬的資料查找隻需要三次IO，性能提高将是巨大的，如果沒有索引，每個資料項都要發生一次IO，那麼總共需要百萬次的IO，顯然成本非常非常高。

b+樹性質

1.通過上面的分析，我們知道IO次數取決于b+樹的高度h，假設目前資料表的資料量為N，每個磁盤塊的資料項的數量是m，則有h=㏒(m+1)N，當資料量N一定的情況下，m越大，h越小；而m = 磁盤塊的大小 / 資料項的大小，磁盤塊的大小也就是一個資料頁的大小，是固定的，如果資料項占的空間越小，資料項的數量越多，樹的高度越低。這就是為什麼每個資料項，即索引字段要盡量的小，比如int占4位元組，要比bigint8位元組少一半。這也是為什麼b+樹要求把真實的資料放到葉子節點而不是内層節點，一旦放到内層節點，磁盤塊的資料項會大幅度下降，導緻樹增高。當資料項等于1時将會退化成線性表。

2.當b+樹的資料項是複合的資料結構的時候，比如(name,age,sex)，b+樹是按照從左到右的順序來建立搜尋樹的，比如當(張三,20,F)這樣的資料來檢索的時候，b+樹會優先比較name來确定下一步的所搜方向，如果name相同再依次比較age和sex，最後得到檢索的資料；但當(20,F)這樣的沒有name的資料來的時候，b+樹就不知道下一步該查哪個節點，因為建立搜尋樹的時候name就是第一個比較因子，必須要先根據name來搜尋才能知道下一步去哪裡查詢。比如當(張三,F)這樣的資料來檢索時，b+樹可以用name來指定搜尋方向，但下一個字段age的缺失，是以隻能把名字等于張三的資料都找到，然後再比對性别是F的資料了， 這個是非常重要的性質，即索引的最左比對特性。

參考2

4.事務

是什麼？

事務（Transaction）是并發控制的基本機關。所謂的事務，它是一個操作序列，由一條或者多條sql語句組成，這些操作要麼都執行，要麼都不執行，它是一個不可分割的工作機關。

ACID特性

事務應該具有4個屬性：原子性、一緻性、隔離性、持久性。

原子性（Atomicity）：指整個資料庫事務是不可分割的工作機關。隻有事務中所有的資料庫操作都執行成功，整個事務的執行才算成功。事務中任何一個sql語句執行失敗，那麼已經執行成功的sql語句也必須撤銷，資料庫狀态應該退回到執行事務前的狀态。

一緻性（Consistency）：事務應確定資料庫的狀态從一個一緻狀态轉變為另一個一緻狀态。一緻狀态的含義是資料庫中的資料應滿足完整性限制，也就是說在事務開始之前和事務結束以後，資料庫的完整性限制沒有被破壞

隔離性（Isolation）：隔離性也叫做并發控制、可串行化或者鎖。事務的隔離性要求每個讀寫事務的對象與其它事務的操作對象能互相分離，即該事務送出前對其它事務都不可見，這通常使用鎖來實作多個事務并發執行時，一個事務的執行不應影響其他事務的執行。

持久性（Durability）：表示事務一旦送出了，其結果就是永久性的，也就是資料就已經寫入到資料庫了，如果發生了當機等事故，資料庫也能将資料恢複。

事務的分類

事務分為一下5類：

  1. 扁平事務；
  2. 帶有儲存點的扁平事務；
  3. 鍊事務；
  4. 嵌套事務；
  5. 分布式事務。

扁平事務

扁平事務是最簡單的一種，也是實際開發中使用的最多的一種事務。在這種事務中，所有操作都處于同一層次，最常見的方式如下：

BEGIN WORK
     Operation 
     Operation 
     Operation 
     ...
     Operation N
 COMMIT WORK

或者：

BEGIN WORK
     Operation 
     Operation 
     Operation 
     ...
     Operation N
     (Error Occured)
 ROLLBACK WORK

扁平事務很簡單，但有一個主要缺點是不能送出或復原事務的某一部分，或者分幾個獨立的步驟去送出。

比如有這樣的一個例子，我從呼和浩特去深圳，為了便宜，我可能這麼幹：

BEGIN WORK
     Operation1:呼和浩特---火車--->北京
     Operation2:北京---飛機--->深圳
 ROLLBACK WORK

但是，如果在Operation1中，從呼和浩特到北京的火車晚點了，錯過了航班，怎麼辦？

因為扁平事務的特性，那我就需要復原，我需要再回到呼和浩特，這樣做的成本太高，是以就有了下面的第二種事務——帶有儲存點的扁平事務。

帶有儲存點的扁平事務

這種事務除了支援扁平事務支援的操作外，允許在事務執行過程中復原到同一事務中較早的一個狀态，這是因為可能某些事務在執行過程中出現的錯誤并不會對所有的操作都無效，放棄整個事務不合乎要求，開銷也太大。儲存點用來通知系統應該記住事務目前的狀态，以便以後發生錯誤時，事務能回到該狀态。

鍊事務

鍊事務，就是指復原時，隻能恢複到最近一個儲存點；而帶有儲存點的扁平事務則可以復原到任意正确的儲存點。

嵌套事務

通過下面執行個體來說明什麼叫嵌套事務

BEGIN WORK
     SubTransaction1:
             BEGIN WORK
                 SubOperationX
             COMMIT WORK
     SubTransaction2:
             BEGIN WORK
                 SubOperationY
             COMMIT WORK
     ...
     SubTransactionN:
             BEGIN WORK
                 SubOperationN
             COMMIT WORK
 COMMIT WORK

這就是嵌套事務，在事務中再嵌套事務，位于根節點的事務稱為頂層事務。事務的前驅稱為父事務，事務的下一層稱為子事務。

子事務既可以送出也可以復原，但是它的送出操作并不馬上生效，除非由其父事務送出。是以就可以确定，任何子事務都在頂層事務送出後才真正的被送出了。同理，任意一個事務的復原都會引起它的所有子事務一同復原。

分布式事務

分布式事務通常是指在一個分布式環境下運作的扁平事務，是以需要根據資料所在位置通路網絡中的不同節點，比如：通過建設銀行向招商銀行轉賬，建設銀行和招商銀行肯定用的不是同一個資料庫，同時二者的資料庫也不在一個網絡節點上，那麼當使用者跨行轉賬，就是通過分布式事務來保證資料的ACID的。

在MySQL中使用事務

在MySQL指令行的預設設定下，事務都是自動送出的，即執行SQL語句後就會馬上執行COMMIT操作。是以要顯示地開啟一個事務須使用指令BEGIN或START TRANSACTION，或者執行指令SET AUTOCOMMIT=0，用來禁止使用目前會話的自動送出。

來看看我們可以使用哪些事務控制語句。

  • BEGIN或START TRANSACTION；顯示地開啟一個事務；
  • COMMIT；也可以使用COMMIT WORK，不過二者是等價的。COMMIT會送出事務，并使已對資料庫進行的所有修改稱為永久性的；
  • ROLLBACK；有可以使用ROLLBACK WORK，不過二者是等價的。復原會結束使用者的事務，并撤銷正在進行的所有未送出的修改；
  • SAVEPOINT identifier；SAVEPOINT允許在事務中建立一個儲存點，一個事務中可以有多個SAVEPOINT；
  • RELEASE SAVEPOINT identifier；删除一個事務的儲存點，當沒有指定的儲存點時，執行該語句會抛出一個異常；
  • ROLLBACK TO identifier；把事務復原到标記點；
  • SET TRANSACTION；用來設定事務的隔離級别。

事務的隔離級别

在資料庫操作中，為了有效保證并發讀取資料的正确性，提出的事務隔離級别。

InnoDB存儲引擎提供事務的隔離級别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ和SERIALIZABLE。這些隔離級别之間的差別如下：

面試常考知識點—資料庫

髒讀：一個事務讀取到了另外一個事務沒有送出的資料；

比如：事務T1更新了一行記錄的内容，但是并沒有送出所做的修改。事務T2讀取到了T1更新後的行，然後T1執行復原操作，取消了剛才所做的修改。現在T2所讀取的行就無效了；

不可重複讀：在同一事務中，兩次讀取同一資料，得到内容不同；

比如：事務T1讀取一行記錄，緊接着事務T2修改了T1剛才讀取的那一行記錄。然後T1又再次讀取這行記錄，發現與剛才讀取的結果不同。這就稱為“不可重複”讀，因為T1原來讀取的那行記錄已經發生了變化；

幻讀：同一事務中，用同樣的操作讀取兩次，得到的記錄數不相同；

比如：事務T1讀取一條指定的WHERE子句所傳回的結果集。然後事務T2新插入 一行記錄，這行記錄恰好可以滿足T1所使用的查詢條件中的WHERE子句的條件。然後T1又使用相同的查詢再次對表進行檢索，但是此時卻看到了事務T2剛才插入的新行。這個新行就稱為“幻像”，因為對T1來說這一行就像突然出現的一樣。

隔離級别越低，事務請求的鎖越少或保持鎖的時間就越短。InnoDB存儲引擎預設的支援隔離級别是REPEATABLE READ；在這種預設的事務隔離級别下已經能完全保證事務的隔離性要求，即達到SQL标準的SERIALIZABLE級别隔離。

我們可以可以用SET TRANSACTION語句改變單個會話或者所有新進連接配接的隔離級别。它的文法如下：

5.視圖

視圖是一種虛拟的表，具有和實體表相同的功能，可以對視圖進行增，改，查操作，視圖通常是有一個表或者多個表的行或列的子集，對視圖的修改不影響基本表，它使得我們擷取資料更容易，相比多表查詢。

6.超鍵 候選鍵 主鍵 外鍵

超鍵：在關系中能唯一辨別元組(資料庫中的一條記錄)的屬性集稱為關系模式的超鍵。一個屬性可以為作為一個超鍵，多個屬性組合在一起也可以作為一個超鍵。超鍵包含候選鍵和主鍵。

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

主鍵：資料庫表中對儲存資料對象予以唯一和完整辨別的資料列或屬性的組合，使用者選作元組辨別的一個侯選鍵稱為主鍵。一個資料列隻能有一個主鍵，且主鍵的取值不能缺失，即不能為空值（Null）。

外鍵：在一個表中存在的另一個表的主鍵稱此表的外鍵，外鍵主要是用來描述兩個表的關系。

7.三個範式

第一範式（1NF）：資料庫表中的字段都是單一屬性的，不可再分。這個單一屬性由基本類型構成，包括整型、實數、字元型、邏輯型、日期型等。

第二範式（2NF）：資料庫表中不存在非關鍵字段對任一候選關鍵字段的部分函數依賴（部分函數依賴指的是存在組合關鍵字中的某些字段決定非關鍵字段的情況），也即所有非關鍵字段都完全依賴于任意一組候選關鍵字。

第三範式（3NF）：在第二範式的基礎上，資料表中如果不存在非關鍵字段對任一候選關鍵字段的傳遞函數依賴則符合第三範式。所謂傳遞函數依賴，指的是如 果存在”A → B → C”的決定關系，則C傳遞函數依賴于A。是以，滿足第三範式的資料庫表應該不存在如下依賴關系： 關鍵字段 → 非關鍵字段 x → 非關鍵字段y。

8.E-R圖

是什麼？

E-R圖也稱實體-聯系圖(Entity Relationship Diagram)，提供了表示實體類型、屬性和聯系的方法，用來描述現實世界的概念模型。

E-R方法是“實體-聯系方法”（Entity-Relationship Approach）的簡稱。它是描述現實世界概念結構模型的有效方法，是表示概念模型的一種方式，用矩形表示實體型，矩形框内寫明實體名；用橢圓表示實體的屬性，并用無向邊将其與相應的實體型連接配接起來；用菱形表示實體型之間的聯系，在菱形框内寫明聯系名，并用無向邊分别與有關實體型連接配接起來，同時在無向邊旁标上聯系的類型（1:1,1:n或m:n）。

構成

在ER圖中有如下四個成分：

矩形框：表示實體，在框中記入實體名。

菱形框：表示聯系，在框中記入聯系名。

橢圓形框：表示實體或聯系的屬性，将屬性名記入框中。對于主屬性名，則在其名稱下劃一下劃線。

連線：實體與屬性之間；實體與聯系之間；聯系與屬性之間用直線相連，并在直線上标注聯系的類型。（對于一對一聯系，要在兩個實體連線方向各寫1； 對于一對多聯系，要在一的一方寫1，多的一方寫N；對于多對多關系，則要在兩個實體連線方向各寫N,M。)

實體型(Entity)：具有相同屬性的實體具有相同的特征和性質，用實體名及其屬性名集合來抽象和刻畫同類實體;在E-R圖中用矩形表示，矩形框内寫明實體名；比如學生張三豐、學生李尋歡都是實體。如果是弱實體的話，在矩形外面再套實線矩形。

屬性(Attribute)：實體所具有的某一特性，一個實體可由若幹個屬性來刻畫。在E-R圖中用橢圓形表示，并用無向邊将其與相應的實體連接配接起來；比如學生的姓名、學号、性别、都是屬性。如果是多值屬性的話，在橢圓形外面再套實線橢圓，如果是派生屬性則用虛線橢圓表示。

聯系(Relationship)：聯系也稱關系，資訊世界中反映實體内部或實體之間的聯系。實體内部的聯系通常是指組成實體的各屬性之間的聯系；實體之間的聯系通常是指不同實體集之間的聯系。在E-R圖中用菱形表示，菱形框内寫明聯系名，并用無向邊分别與有關實體連接配接起來，同時在無向邊旁标上聯系的類型（1 : 1，1 : n或m : n），比如老師給學生授課存在授課關系，學生選課存在選課關系。如果是弱實體的聯系則在菱形外面再套菱形。

聯系可分為以下 3 種類型：

(1) 一對一聯系(1 ∶1)

例如，一個班級有一個班長，而每個班長隻在一個班級任職，則班級與班長的聯系是一對一的。

(2) 一對多聯系(1 ∶N)

例如，某校教師與課程之間存在一對多的聯系“教”，即每位教師可以教多門課程，但是每門課程隻能由一位教師來教。

(3) 多對多聯系(M ∶N)

例如，學生與課程間的聯系(“學 ”)是多對多的，即一個學生可以學多門課程，而每門課程可以有多個學生來學。聯系也可能有屬性。例如，學生“ 學” 某門課程所取得的成績，既不是學生的屬性也不是課程的屬性。由于“ 成績” 既依賴于某名特定的學生又依賴于某門特定的課程，是以它是學生與課程之間的聯系“ 學”的屬性。

作圖步驟

⑴确定所有的實體集合

⑵選擇實體集應包含的屬性

⑶确定實體集之間的聯系

⑷确定實體集的關鍵字，用下劃線在屬性上表明關鍵字的屬性組合

⑸确定聯系的類型，在用線将表示聯系的菱形框聯系到實體集時，線上旁注明聯系的類型。

執行個體：

面試常考知識點—資料庫

參考3

參考4

9.處理重複記錄的常用操作

(1)查找表中多餘的重複記錄，重複記錄是根據單個字段(column_name)來判斷。

(2)删除表中多餘的重複記錄，重複記錄是根據單個字段(column_name)來判斷，隻留有id最小的記錄。

(3)查找表中多餘的重複記錄（多個字段）。

(4)删除表中多餘的重複記錄（多個字段），隻留有rowid最小的記錄 。

10.批處理

MySQL 支援以批處理的方式執行一批SQL語句，例如：

create table test(id int,name varchar());
insert into test values(,'watson');

batchfile.txt裡包含下面的一些SQL 語句，此檔案在linux系統中的路徑/home/wming/batchfile.txt：

insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;
insert into test select * from test;

執行上面的檔案，test 表在 MyZone 資料庫中：

mysql -uroot -p -D MyZone < /home/wming/batchfile.txt

上面的例子通過批處理執行插入語句，下面的例子是通過批處理執行查詢語句：

此時的batchfile2.txt裡含有query的資訊：

select * from test limit ;
insert into test select * from test;
insert into test select * from test;

下面的mysql0716.out就記錄了 select * from test limit 200 查詢語句的結果集。

mysql -uroot -p -D MyZone < /home/wming/batchfile2.txt >/home/wming/mysql0716.out

11.MyISAM與InnoDB的差別是什麼？

1、存儲結構

MyISAM：每個MyISAM表在磁盤上存儲成三個檔案，檔案的名字以表的名字開始，擴充名指出檔案類型：.frm檔案存儲表定義；資料檔案的擴充名為.MYD (MYData)；索引檔案的擴充名是.MYI (MYIndex)。

InnoDB：所有的表都儲存在同一個資料檔案中（也可能是多個檔案，或者是獨立的表空間檔案），InnoDB表的大小隻受限于作業系統檔案的大小，一般為2GB。

2、存儲空間

MyISAM：可被壓縮，存儲空間較小。支援三種不同的存儲格式：靜态表(預設，但是注意資料末尾不能有空格，會被去掉)、動态表、壓縮表。

InnoDB：需要更多的記憶體和存儲，它會在主記憶體中建立其專用的緩沖池用于高速緩沖資料和索引。

3、可移植性、備份及恢複

MyISAM：資料是以檔案的形式存儲，是以在跨平台的資料轉移中會很友善。在備份和恢複時可單獨針對某個表進行操作。

InnoDB：免費的方案可以是拷貝資料檔案、備份 binlog，或者用 mysqldump，在資料量達到幾十G的時候就相對痛苦了。

4、事務支援

MyISAM：強調的是性能，每次查詢具有原子性,其執行數度比InnoDB類型更快，但是不提供事務支援。

InnoDB：支援事務，外部鍵等進階資料庫功能。 具有事務(commit)、復原(rollback)和崩潰修複能力(crash recovery capabilities)的事務安全(transaction-safe (ACID compliant))型表。

這一點是非常重要。事務是一種進階的處理方式，如在一些列增删改中隻要哪個出錯還可以復原還原，而MyISAM就不可以了。

5、AUTO_INCREMENT

MyISAM:可以和其他字段一起建立聯合索引。引擎的自動增長列必須是索引，如果是組合索引，自動增長可以不是第一列，他可以根據前面幾列進行排序後遞增。

InnoDB: InnoDB中必須包含隻有該字段的索引。引擎的自動增長列必須是索引，如果是組合索引也必須是組合索引的第一列。

6、表鎖差異

MyISAM：隻支援表級鎖，使用者在操作myisam表時，select，update，delete，insert語句都會給表自動加鎖，如果加鎖以後的表滿足insert并發的情況下，可以在表的尾部插入新的資料。

InnoDB：支援事務和行級鎖，是innodb的最大特色。行鎖大幅度提高了多使用者并發操作的性能。但是InnoDB的行鎖，隻是在WHERE的主鍵是有效的，非主鍵的WHERE都會鎖全表的。

7、 全文索引

MyISAM：支援 FULLTEXT類型的全文索引。

InnoDB：不支援FULLTEXT類型的全文索引，但是innodb可以使用sphinx插件支援全文索引，并且效果更好。

8、表主鍵

MyISAM：允許沒有任何索引和主鍵的表存在，索引都是儲存行的位址。

InnoDB：如果沒有設定主鍵或者非空唯一索引，就會自動生成一個6位元組的主鍵(使用者不可見)，資料是主索引的一部分，附加索引儲存的是主索引的值。

9、 表的具體行數

MyISAM：儲存有表的總行數，如果select count() from table;會直接取出出該值。

InnoDB：沒有儲存表的總行數，如果使用select count() from table；就會周遊整個表，消耗相當大，但是在加了wehre條件後，myisam和innodb處理的方式都一樣。

10、CURD操作

MyISAM：如果執行大量的SELECT，MyISAM是更好的選擇。

InnoDB：如果你的資料執行大量的INSERT或UPDATE，出于性能方面的考慮，應該使用InnoDB表。DELETE 從性能上InnoDB更優，但DELETE FROM table時，InnoDB不會重建立立表，而是一行一行的删除，在innodb上如果要清空儲存有大量資料的表，最好使用truncate table這個指令。

11、 外鍵

MyISAM：不支援

InnoDB：支援

通過上述的分析，基本上可以考慮使用InnoDB來替代MyISAM引擎了，原因是InnoDB自身很多良好的特點，比如事務支援、存儲 過程、視圖、行級鎖定等等，在并發很多的情況下，相信InnoDB的表現肯定要比MyISAM強很多。另外，任何一種表都不是萬能的，隻用恰當的針對業務類型來選擇合适的表類型，才能最大的發揮MySQL的性能優勢。如果不是很複雜的Web應用，非關鍵應用，還是可以繼續考慮MyISAM的，這個具體視情況而定。

參考

12. 樂觀鎖 與 悲觀鎖

資料庫管理系統（DBMS）中的并發控制的任務是確定在多個事務同時存取資料庫中同一資料時不破壞事務的隔離性和一緻性以及資料庫的統一性。

樂觀并發控制(樂觀鎖)和悲觀并發控制（悲觀鎖）是并發控制采用的主要技術手段。

無論是悲觀鎖還是樂觀鎖，都是人們定義出來的概念，可以認為是一種思想。其實不僅僅是關系型資料庫系統中有樂觀鎖和悲觀鎖的概念，像memcache、hibernate、tair等都有類似的概念。

針對不同的業務場景，應該選用不同的并發控制方式。是以，不要把樂觀并發控制和悲觀并發控制狹義的了解為僅在DBMS中存在的概念，更不要把他們和資料庫中提供的鎖機制（行鎖、表鎖、排他鎖、共享鎖）混為一談。其實，在DBMS中，悲觀鎖正是利用資料庫本身提供的鎖機制來實作的。

悲觀鎖

在關系資料庫管理系統裡，悲觀并發控制（又名“悲觀鎖”，Pessimistic Concurrency Control，縮寫“PCC”）是一種并發控制的方法。它可以阻止一個事務以影響其他使用者的方式來修改資料。如果一個事務執行的操作在某行資料上應用了鎖，那隻有當這個事務把鎖釋放，其他事務才能夠執行與該鎖沖突的操作。

悲觀并發控制主要用于資料争用激烈的環境，以及發生并發沖突時使用鎖保護資料的成本要低于復原事務的成本的環境中。

悲觀鎖：正如其名，它指的是對資料被外界（包括本系統目前的其他事務，以及來自外部系統的事務處理）修改持保守态度(悲觀)，是以，在整個資料處理過程中，将資料處于鎖定狀态。 悲觀鎖的實作，往往依靠資料庫提供的鎖機制 （也隻有資料庫層提供的鎖機制才能真正保證資料通路的排他性，否則，即使在本系統中實作了加鎖機制，也無法保證外部系統不會修改資料）。

悲觀鎖的流程：

1.在對某一記錄進行修改前，先嘗試為該記錄加上排他鎖（exclusive locking）。

2.如果加鎖失敗，說明該記錄正在被修改，那麼目前操作可能要等待或者抛出異常， 具體響應方式由開發者根據實際情況決定。

3.如果成功加鎖，那麼就可以對記錄做修改，事務完成後就會解鎖了。

4.其間如果有其他事務要對該記錄做修改或加排他鎖，都會等待該事務将該記錄解鎖或直接抛出異常。

MySQL InnoDB中使用悲觀鎖

注意：要使用悲觀鎖，必須先關閉mysql資料庫的自動送出功能，因為MySQL預設使用autocommit模式，也就是說，當你執行一個更新操作後，MySQL會立刻将結果進行送出。

//0.開始事務
begin;/begin work;/start transaction; (三者選一就可以)
//1.查詢出商品資訊
select status from t_goods where id= for update;
//2.根據商品資訊生成訂單
insert into t_orders (id,goods_id) values (null,);
//3.修改商品status為2
update t_goods set status=;
//4.送出事務
commit;/commit work;

上面的查詢語句中，我們使用了select…for update的方式，這樣就通過開啟排他鎖的方式實作了悲觀鎖。此時在t_goods表中，id為1的 那條資料就被我們鎖定了，其它事務必須等本次事務送出之後才能對該記錄進行操作。這樣我們可以保證目前的資料不會被其它事務修改。

注意：上面提到，使用select…for update會把資料給鎖住，不過我們需要注意一下鎖的級别，MySQL InnoDB預設為行級鎖。行級鎖都是基于索引的，如果一條SQL語句沒有用到索引是不會使用行級鎖的，會使用表級鎖把整張表鎖住，這點需要注意。

優點與不足：

優點：悲觀并發控制實際上是采用“先取鎖再通路”的保守政策，為資料處理的安全性提供了保證；

缺點：在效率方面，處理加鎖的機制會讓資料庫産生額外的開銷，同時會增加産生死鎖的機率；另外，在隻讀型事務中由于不會産生沖突，也沒必要使用鎖，這樣做隻會增加系統負載；還會降低并行性，一個事務如果鎖定了某行資料，其他事務就必須等待該事務處理完才可以處理那行數

樂觀鎖

在關系資料庫管理系統裡，樂觀并發控制（又名“樂觀鎖”，Optimistic Concurrency Control，縮寫“OCC”）是一種并發控制的方法。它假設多使用者并發的事務在處理資料時不會彼此互相影響，各事務能夠在不産生鎖的情況下處理各自影響的那部分資料。在送出資料更新之前，每個事務會先檢查在該事務讀取資料後，有沒有其他事務對該資料做過修改。如果其他事務更新過該資料的話，正在送出的事務會進行復原。樂觀事務控制最早是由孔祥重（H.T.Kung）教授提出。

樂觀鎖（ Optimistic Locking ）是相對悲觀鎖而言，樂觀鎖假設資料一般情況下不會造成沖突，是以在事務對資料進行送出更新的時候，才會正式對資料的沖突與否進行檢測，如果發現沖突了，則傳回錯誤資訊，讓使用者決定如何去做。

相對于悲觀鎖，在對資料庫進行處理的時候，樂觀鎖并不會使用資料庫提供的鎖機制，一般用記錄資料版本的方式實作樂觀鎖。

資料版本：為資料增加的一個版本辨別。當讀取資料時，将版本辨別的值一同讀出，資料每更新一次，便對版本辨別進行一次更新。當事務送出更新的時候，需要判斷資料庫表對應記錄的目前版本資訊與第一次取出來的版本辨別是否一緻，如果資料庫表目前版本号與第一次取出來的版本辨別值相等，則予以更新，否則認為是過期資料。

實作資料版本有兩種方式，第一種是使用版本号，第二種是使用時間戳。

使用版本号實作樂觀鎖

使用版本号時，可以在資料初始化時指定一個版本号，每次對資料的更新操作都對版本号執行+1操作。并判斷目前版本号是不是該資料的最新的版本号。

查詢出商品資訊
select (status,status,version) from t_goods where id=#{id}
根據商品資訊生成訂單
修改商品status為
update t_goods 
set status=,version=version+
where id=#{id} and version=#{version};

樂觀并發控制假設事務之間的資料競争(data race)機率比較小，是以盡可能直接做下去，直到送出的時候才去鎖定，是以不會産生任何鎖和死鎖。但如果直接簡單這麼做，還是有可能會遇到不可預期的結果，例如兩個事務都讀取了資料庫的某一行，經過修改以後寫回資料庫，這時就遇到了問題。

13.左 右 連接配接 全連接配接 内連接配接

可參考

本文參考自網絡，知識在于分享！

面試知識點 面試題 資料庫
上一篇: 永遠的坐票
下一篇: 三級資料庫常考知識點強調

繼續閱讀