資料庫常見知識點

一、資料庫基本概念

1.主鍵、外鍵、超鍵、候選鍵

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

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

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

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

2.為什麼用自增列作為主鍵

如果我們定義了主鍵(PRIMARY KEY)，那麼InnoDB會選擇主鍵作為聚集索引、

如果沒有顯式定義主鍵，則InnoDB會選擇第一個不包含有NULL值的唯一索引作為主鍵索引、

如果也沒有這樣的唯一索引，則InnoDB會選擇内置6位元組長的ROWID作為隐含的聚集索引(ROWID随着行記錄的寫入而主鍵遞增，這個ROWID不像ORACLE的ROWID那樣可引用，是隐含的)。

資料記錄本身被存于主索引（一顆B+Tree）的葉子節點上。這就要求同一個葉子節點内（大小為一個記憶體頁或磁盤頁）的各條資料記錄按主鍵順序存放，是以每當有一條新的記錄插入時，MySQL會根據其主鍵将其插入适當的節點和位置，如果頁面達到裝載因子（InnoDB預設為15/16），則開辟一個新的頁（節點）

如果表使用自增主鍵，那麼每次插入新的記錄，記錄就會順序添加到目前索引節點的後續位置，當一頁寫滿，就會自動開辟一個新的頁

如果使用非自增主鍵（如果身份證号或學号等），由于每次插入主鍵的值近似于随機，是以每次新紀錄都要被插到現有索引頁得中間某個位置，此時MySQL不得不為了将新記錄插到合适位置而移動資料，甚至目标頁面可能已經被回寫到磁盤上而從緩存中清掉，此時又要從磁盤上讀回來，這增加了很多開銷，同時頻繁的移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結構，後續不得不通過OPTIMIZE TABLE來重建表并優化填充頁面。

3.觸發器的作用？

觸發器是一種特殊的存儲過程，主要是通過事件來觸發而被執行的。它可以強化限制，來維護資料的完整性和一緻性，可以跟蹤資料庫内的操作進而不允許未經許可的更新和變化。可以聯級運算。如，某表上的觸發器上包含對另一個表的資料操作，而該操作又會導緻該表觸發器被觸發。

4.什麼是存儲過程？用什麼來調用？

存儲過程是一個預編譯的SQL語句，優點是允許子產品化的設計，就是說隻需建立一次，以後在該程式中就可以調用多次。如果某次操作需要執行多次SQL，使用存儲過程比單純SQL語句執行要快。

調用：

1）可以用一個指令對象來調用存儲過程。

2）可以供外部程式調用，比如：java程式。

5.存儲過程的優缺點？

優點：

1）存儲過程是預編譯過的，執行效率高。

2）存儲過程的代碼直接存放于資料庫中，通過存儲過程名直接調用，減少網絡通訊。

3）安全性高，執行存儲過程需要有一定權限的使用者。

4）存儲過程可以重複使用，可減少資料庫開發人員的工作量。

缺點：移植性差

6.存儲過程與函數的差別

7.什麼叫視圖？遊标是什麼？

視圖：

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

遊标：

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

8.視圖的優缺點

1對資料庫的通路，因為視圖可以有選擇性的選取資料庫裡的一部分。

2)使用者通過簡單的查詢可以從複雜查詢中得到結果。

3)維護資料的獨立性，試圖可從多個表檢索資料。

4)對于相同的資料可産生不同的視圖。

缺點：

性能：查詢視圖時，必須把視圖的查詢轉化成對基本表的查詢，如果這個視圖是由一個複雜的多表查詢所定義，那麼，那麼就無法更改資料

9.drop、truncate、 delete差別

最基本：

• drop直接删掉表。
• truncate删除表中資料，再插入時自增長id又從1開始。
• delete删除表中資料，可以加where字句。

（1） DELETE語句執行删除的過程是每次從表中删除一行，并且同時将該行的删除操作作為事務記錄在日志中儲存以便進行進行復原操作。TRUNCATE TABLE 則一次性地從表中删除所有的資料并不把單獨的删除操作記錄記入日志儲存，删除行是不能恢複的。并且在删除的過程中不會激活與表有關的删除觸發器。執行速度快。

（2） 表和索引所占空間。當表被TRUNCATE 後，這個表和索引所占用的空間會恢複到初始大小，而DELETE操作不會減少表或索引所占用的空間。drop語句将表所占用的空間全釋放掉。

（3） 一般而言，drop > truncate > delete

（4） 應用範圍。TRUNCATE 隻能對TABLE；DELETE可以是table和view

（5） TRUNCATE 和DELETE隻删除資料，而DROP則删除整個表（結構和資料）。

（6） truncate與不帶where的delete ：隻删除資料，而不删除表的結構（定義）drop語句将删除表的結構被依賴的限制（constrain),觸發器（trigger)索引（index);依賴于該表的存儲過程/函數将被保留，但其狀态會變為：invalid。

（7） delete語句為DML（data maintain Language),這個操作會被放到 rollback segment中,事務送出後才生效。如果有相應的 tigger,執行的時候将被觸發。

（8） truncate、drop是DLL（data define language),操作立即生效，原資料不放到 rollback segment中，不能復原。

（9） 在沒有備份情況下，謹慎使用 drop 與 truncate。要删除部分資料行采用delete且注意結合where來限制影響範圍。復原段要足夠大。要删除表用drop;若想保留表而将表中資料删除，如果于事務無關，用truncate即可實作。如果和事務有關，或老師想觸發trigger,還是用delete。

（10） Truncate table 表名 速度快,而且效率高,因為:?truncate table 在功能上與不帶 WHERE 子句的 DELETE 語句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統和事務日志資源少。DELETE 語句每次删除一行，并在事務日志中為所删除的每行記錄一項。TRUNCATE TABLE 通過釋放存儲表資料所用的資料頁來删除資料，并且隻在事務日志中記錄頁的釋放。

（11） TRUNCATE TABLE 删除表中的所有行，但表結構及其列、限制、索引等保持不變。新行辨別所用的計數值重置為該列的種子。如果想保留辨別計數值，請改用 DELETE。如果要删除表定義及其資料，請使用 DROP TABLE 語句。

（12） 對于由 FOREIGN KEY 限制引用的表，不能使用 TRUNCATE TABLE，而應使用不帶 WHERE 子句的 DELETE 語句。由于 TRUNCATE TABLE 不記錄在日志中，是以它不能激活觸發器。

10.什麼是臨時表，臨時表什麼時候删除?

臨時表可以手動删除：

DROP TEMPORARY TABLE IF EXISTS temp_tb;

臨時表隻在目前連接配接可見，當關閉連接配接時，MySQL會自動删除表并釋放所有空間。是以在不同的連接配接中可以建立同名的臨時表，并且操作屬于本連接配接的臨時表。

建立臨時表的文法與建立表文法類似，不同之處是增加關鍵字TEMPORARY，

如：

CREATE TEMPORARY TABLE tmp_table (

NAME VARCHAR (10) NOT NULL,

time date NOT NULL

);

select * from tmp_table;

11.非關系型資料庫和關系型資料庫差別，優勢比較?

非關系型資料庫的優勢：

• 性能：NOSQL是基于鍵值對的，可以想象成表中的主鍵和值的對應關系，而且不需要經過SQL層的解析，是以性能非常高。
• 可擴充性：同樣也是因為基于鍵值對，資料之間沒有耦合性，是以非常容易水準擴充。

關系型資料庫的優勢：

• 複雜查詢：可以用SQL語句友善的在一個表以及多個表之間做非常複雜的資料查詢。
• 事務支援：使得對于安全性能很高的資料通路要求得以實作。

其他：

1.對于這兩類資料庫，對方的優勢就是自己的弱勢，反之亦然。

2.NOSQL資料庫慢慢開始具備SQL資料庫的一些複雜查詢功能，比如MongoDB。

3.對于事務的支援也可以用一些系統級的原子操作來實作例如樂觀鎖之類的方法來曲線救國，比如Redis set nx。

12.資料庫範式，根據某個場景設計資料表?

第一範式:(確定每列保持原子性)所有字段值都是不可分解的原子值。

第一範式是最基本的範式。如果資料庫表中的所有字段值都是不可分解的原子值，就說明該資料庫表滿足了第一範式。

第一範式的合理遵循需要根據系統的實際需求來定。比如某些資料庫系統中需要用到“位址”這個屬性，本來直接将“位址”屬性設計成一個資料庫表的字段就行。但是如果系統經常會通路“位址”屬性中的“城市”部分，那麼就非要将“位址”這個屬性重新拆分為省份、城市、詳細位址等多個部分進行存儲，這樣在對位址中某一部分操作的時候将非常友善。這樣設計才算滿足了資料庫的第一範式，如下表所示。

上表所示的使用者資訊遵循了第一範式的要求，這樣在對使用者使用城市進行分類的時候就非常友善，也提高了資料庫的性能。

第二範式:(確定表中的每列都和主鍵相關)在一個資料庫表中，一個表中隻能儲存一種資料，不可以把多種資料儲存在同一張資料庫表中。

第二範式在第一範式的基礎之上更進一層。第二範式需要確定資料庫表中的每一列都和主鍵相關，而不能隻與主鍵的某一部分相關（主要針對聯合主鍵而言）。也就是說在一個資料庫表中，一個表中隻能儲存一種資料，不可以把多種資料儲存在同一張資料庫表中。

比如要設計一個訂單資訊表，因為訂單中可能會有多種商品，是以要将訂單編号和商品編号作為資料庫表的聯合主鍵。

第三範式:(確定每列都和主鍵列直接相關,而不是間接相關) 資料表中的每一列資料都和主鍵直接相關，而不能間接相關。

第三範式需要確定資料表中的每一列資料都和主鍵直接相關，而不能間接相關。

比如在設計一個訂單資料表的時候，可以将客戶編号作為一個外鍵和訂單表建立相應的關系。而不可以在訂單表中添加關于客戶其它資訊（比如姓名、所屬公司等）的字段。

BCNF:符合3NF，并且，主屬性不依賴于主屬性。

若關系模式屬于第二範式，且每個屬性都不傳遞依賴于鍵碼，則R屬于BC範式。

通常BC範式的條件有多種等價的表述：每個非平凡依賴的左邊必須包含鍵碼；每個決定因素必須包含鍵碼。

BC範式既檢查非主屬性，又檢查主屬性。當隻檢查非主屬性時，就成了第三範式。滿足BC範式的關系都必然滿足第三範式。

還可以這麼說：若一個關系達到了第三範式，并且它隻有一個候選碼，或者它的每個候選碼都是單屬性，則該關系自然達到BC範式。

一般，一個資料庫設計符合3NF或BCNF就可以了。

第四範式:要求把同一表内的多對多關系删除。

第五範式:從最終結構重建立立原始結構。

13.什麼是 内連接配接、外連接配接、交叉連接配接、笛卡爾積等?

内連接配接: 隻連接配接比對的行

左外連接配接: 包含左邊表的全部行（不管右邊的表中是否存在與它們比對的行），以及右邊表中全部比對的行

右外連接配接: 包含右邊表的全部行（不管左邊的表中是否存在與它們比對的行），以及左邊表中全部比對的行

例如1：

SELECT a.,b. FROM luntan LEFT JOIN usertable as b ON a.username=b.username

例如2：

SELECT a.,b. FROM city as a FULL OUTER JOIN user as b ON a.username=b.username

全外連接配接: 包含左、右兩個表的全部行，不管另外一邊的表中是否存在與它們比對的行。

交叉連接配接: 生成笛卡爾積－它不使用任何比對或者選取條件，而是直接将一個資料源中的每個行與另一個資料源的每個行都一一比對

例如：

SELECT type,pub_name FROM titles CROSS JOIN publishers ORDER BY type

注意：

很多公司都隻是考察是否知道其概念，但是也有很多公司需要不僅僅知道概念，還需要動手寫sql,一般都是簡單的連接配接查詢，具體關于連接配接查詢的sql練習，參見以下連結：

牛客網資料庫SQL實戰

leetcode中文網站資料庫練習

我的另一篇文章，常用sql練習50題

14.varchar和char的使用場景?

1.char的長度是不可變的，而varchar的長度是可變的。

定義一個char[10]和varchar[10]。

如果存進去的是‘csdn’,那麼char所占的長度依然為10，除了字元‘csdn’外，後面跟六個空格，varchar就立馬把長度變為4了，取資料的時候，char類型的要用trim()去掉多餘的空格，而varchar是不需要的。

2.char的存取數度還是要比varchar要快得多，因為其長度固定，友善程式的存儲與查找。

char也為此付出的是空間的代價，因為其長度固定，是以難免會有多餘的空格占位符占據空間，可謂是以空間換取時間效率。

varchar是以空間效率為首位。

3.char的存儲方式是：對英文字元（ASCII）占用1個位元組，對一個漢字占用兩個位元組。

varchar的存儲方式是：對每個英文字元占用2個位元組，漢字也占用2個位元組。

4.兩者的存儲資料都非unicode的字元資料。

15.SQL語言分類

SQL語言共分為四大類：

• 資料查詢語言DQL
• 資料操縱語言DML
• 資料定義語言DDL
• 資料控制語言DCL。

1. 資料查詢語言DQL

資料查詢語言DQL基本結構是由SELECT子句，FROM子句，WHERE子句組成的查詢塊：

SELECT

FROM

WHERE

2 .資料操縱語言DML

資料操縱語言DML主要有三種形式：

1) 插入：INSERT

2) 更新：UPDATE

3) 删除：DELETE

3. 資料定義語言DDL

資料定義語言DDL用來建立資料庫中的各種對象-----表、視圖、索引、同義詞、聚簇等如：

CREATE TABLE/VIEW/INDEX/SYN/CLUSTER

表 視圖 索引 同義詞 簇

DDL操作是隐性送出的！不能rollback

4. 資料控制語言DCL

資料控制語言DCL用來授予或回收通路資料庫的某種特權，并控制資料庫操縱事務發生的時間及效果，對資料庫實行監視等。如：

1) GRANT：授權。

2) ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一點。復原---ROLLBACK；復原指令使資料庫狀态回到上次最後送出的狀态。其格式為：

SQL>ROLLBACK;

3) COMMIT [WORK]：送出。

在資料庫的插入、删除和修改操作時，隻有當事務在送出到資料

庫時才算完成。在事務送出前，隻有操作資料庫的這個人才能有權看

到所做的事情，别人隻有在最後送出完成後才可以看到。

送出資料有三種類型：顯式送出、隐式送出及自動送出。下面分

别說明這三種類型。

(1) 顯式送出

用COMMIT指令直接完成的送出為顯式送出。其格式為：

SQL>COMMIT；

(2) 隐式送出

用SQL指令間接完成的送出為隐式送出。這些指令是：

ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，

EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

(3) 自動送出

若把AUTOCOMMIT設定為ON，則在插入、修改、删除語句執行後，

系統将自動進行送出，這就是自動送出。其格式為：

SQL>SET AUTOCOMMIT ON；

16.like %和-的差別

通配符的分類:

%百分号通配符:表示任何字元出現任意次數(可以是0次).

_下劃線通配符:表示隻能比對單個字元,不能多也不能少,就是一個字元.

like操作符: LIKE作用是訓示mysql後面的搜尋模式是利用通配符而不是直接相等比對進行比較.

注意: 如果在使用like操作符時,後面的沒有使用通用比對符效果是和=一緻的,SELECT * FROM products WHERE products.prod_name like '1000';

隻能比對的結果為1000,而不能比對像JetPack 1000這樣的結果.

• %通配符使用: 比對以"yves"開頭的記錄:(包括記錄"yves") SELECT FROM products WHERE products.prod_name like 'yves%';

  比對包含"yves"的記錄(包括記錄"yves") SELECT FROM products WHERE products.prod_name like '%yves%';

  比對以"yves"結尾的記錄(包括記錄"yves",不包括記錄"yves ",也就是yves後面有空格的記錄,這裡需要注意) SELECT * FROM products WHERE products.prod_name like '%yves';

• 通配符使用: SELECT FROM products WHERE products.prod_name like '_yves'; 比對結果為: 像"yyves"這樣記錄.

  SELECT FROM products WHERE products.prod*name like 'yves*'; 比對結果為: 像"yvesHe"這樣的記錄.(一個下劃線隻能比對一個字元,不能多也不能少)

注意事項:

• 注意大小寫,在使用模糊比對時,也就是比對文本時,mysql是可能區分大小的,也可能是不區分大小寫的,這個結果是取決于使用者對MySQL的配置方式.如果是區分大小寫,那麼像YvesHe這樣記錄是不能被"yves__"這樣的比對條件比對的.
• 注意尾部空格,"%yves"是不能比對"heyves "這樣的記錄的.
• 注意NULL,%通配符可以比對任意字元,但是不能比對NULL,也就是說SELECT * FROM products WHERE products.prod_name like '%;是比對不到products.prod_name為NULL的的記錄.

技巧與建議:

正如所見， MySQL的通配符很有用。但這種功能是有代價的：通配符搜尋的處理一般要比前面讨論的其他搜尋所花時間更長。這裡給出一些使用通配符要記住的技巧。

• 不要過度使用通配符。如果其他操作符能達到相同的目的，應該 使用其他操作符。
• 在确實需要使用通配符時，除非絕對有必要，否則不要把它們用 在搜尋模式的開始處。把通配符置于搜尋模式的開始處，搜尋起 來是最慢的。
• 仔細注意通配符的位置。如果放錯地方，可能不會傳回想要的數.

17.count(*)、count(1)、count(column)的差別

• count(*)對行的數目進行計算,包含NULL

• count(column)對特定的列的值具有的行數進行計算,不包含NULL值。

• count()還有一種使用方式,count(1)這個用法和count(*)的結果是一樣的。

性能問題:

1.任何情況下SELECT COUNT(*) FROM tablename是最優選擇;

2.盡量減少SELECT COUNT(*) FROM tablename WHERE COL = ‘value’ 這種查詢;

3.杜絕SELECT COUNT(COL) FROM tablename WHERE COL2 = ‘value’ 的出現。

• 如果表沒有主鍵,那麼count(1)比count(*)快。

• 如果有主鍵,那麼count(主鍵,聯合主鍵)比count(*)快。

• 如果表隻有一個字段,count(*)最快。

count(1)跟count(主鍵)一樣,隻掃描主鍵。count(*)跟count(非主鍵)一樣,掃描整個表。明顯前者更快一些。

18.最左字首原則

多列索引：

ALTER TABLE people ADD INDEX lname_fname_age (lame,fname,age);

為了提高搜尋效率，我們需要考慮運用多列索引,由于索引檔案以B－Tree格式儲存，是以我們不用掃描任何記錄，即可得到最終結果。

注：在mysql中執行查詢時，隻能使用一個索引，如果我們在lname,fname,age上分别建索引,執行查詢時，隻能使用一個索引，mysql會選擇一個最嚴格(獲得結果集記錄數最少)的索引。

最左字首原則：顧名思義，就是最左優先，上例中我們建立了lname_fname_age多列索引,相當于建立了(lname)單列索引，(lname,fname)組合索引以及(lname,fname,age)組合索引。

二、索引

1.什麼是索引？

何為索引：

資料庫索引，是資料庫管理系統中一個排序的資料結構，索引的實作通常使用B樹及其變種B+樹。

在資料之外，資料庫系統還維護着滿足特定查找算法的資料結構，這些資料結構以某種方式引用（指向）資料，這樣就可以在這些資料結構上實作進階查找算法。這種資料結構，就是索引。

2.索引的作用？它的優點缺點是什麼？

索引作用：

協助快速查詢、更新資料庫表中資料。

為表設定索引要付出代價的：

• 一是增加了資料庫的存儲空間

• 二是在插入和修改資料時要花費較多的時間(因為索引也要随之變動)。

  3.索引的優缺點？

建立索引可以大大提高系統的性能（優點）：

1.通過建立唯一性索引，可以保證資料庫表中每一行資料的唯一性。

2.可以大大加快資料的檢索速度，這也是建立索引的最主要的原因。

3.可以加速表和表之間的連接配接，特别是在實作資料的參考完整性方面特别有意義。

4.在使用分組和排序子句進行資料檢索時，同樣可以顯著減少查詢中分組和排序的時間。

5.通過使用索引，可以在查詢的過程中，使用優化隐藏器，提高系統的性能。

增加索引也有許多不利的方面(缺點)：

1.建立索引和維護索引要耗費時間，這種時間随着資料量的增加而增加。

2.索引需要占實體空間，除了資料表占資料空間之外，每一個索引還要占一定的實體空間，如果要建立聚簇索引，那麼需要的空間就會更大。

3.當對表中的資料進行增加、删除和修改的時候，索引也要動态的維護，這樣就降低了資料的維護速度。

4.哪些列适合建立索引、哪些不适合建索引？

索引是建立在資料庫表中的某些列的上面。在建立索引的時候，應該考慮在哪些列上可以建立索引，在哪些列上不能建立索引。

一般來說，應該在這些列上建立索引：

（1）在經常需要搜尋的列上，可以加快搜尋的速度；

（2）在作為主鍵的列上，強制該列的唯一性群組織表中資料的排列結構；

（3）在經常用在連接配接的列上，這些列主要是一些外鍵，可以加快連接配接的速度；

（4）在經常需要根據範圍進行搜尋的列上建立索引，因為索引已經排序，其指定的範圍是連續的；

（5）在經常需要排序的列上建立索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；

（6）在經常使用在WHERE子句中的列上面建立索引，加快條件的判斷速度。

對于有些列不應該建立索引：

（1）對于那些在查詢中很少使用或者參考的列不應該建立索引。

這是因為，既然這些列很少使用到，是以有索引或者無索引，并不能提高查詢速度。相反，由于增加了索引，反而降低了系統的維護速度和增大了空間需求。

（2）對于那些隻有很少資料值的列也不應該增加索引。

這是因為，由于這些列的取值很少，例如人事表的性别列，在查詢的結果中，結果集的資料行占了表中資料行的很大比例，即需要在表中搜尋的資料行的比例很大。增加索引，并不能明顯加快檢索速度。

（3）對于那些定義為text, image和bit資料類型的列不應該增加索引。

這是因為，這些列的資料量要麼相當大，要麼取值很少。

(4)當修改性能遠遠大于檢索性能時，不應該建立索引。

這是因為，修改性能和檢索性能是互相沖突的。當增加索引時，會提高檢索性能，但是會降低修改性能。當減少索引時，會提高修改性能，降低檢索性能。是以，當修改性能遠遠大于檢索性能時，不應該建立索引。

5.什麼樣的字段适合建索引

唯一、不為空、經常被查詢的字段

6.MySQL B+Tree索引和Hash索引的差別?

Hash索引和B+樹索引的特點：

• Hash索引結構的特殊性，其檢索效率非常高，索引的檢索可以一次定位;

• B+樹索引需要從根節點到枝節點，最後才能通路到頁節點這樣多次的IO通路;

為什麼不都用Hash索引而使用B+樹索引？

1. Hash索引僅僅能滿足"=","IN"和""查詢，不能使用範圍查詢,因為經過相應的Hash算法處理之後的Hash值的大小關系，并不能保證和Hash運算前完全一樣；

1. Hash索引無法被用來避免資料的排序操作，因為Hash值的大小關系并不一定和Hash運算前的鍵值完全一樣；

1. Hash索引不能利用部分索引鍵查詢，對于組合索引，Hash索引在計算Hash值的時候是組合索引鍵合并後再一起計算Hash值，而不是單獨計算Hash值，是以通過組合索引的前面一個或幾個索引鍵進行查詢的時候，Hash索引也無法被利用；

1. Hash索引在任何時候都不能避免表掃描，由于不同索引鍵存在相同Hash值，是以即使取滿足某個Hash鍵值的資料的記錄條數，也無法從Hash索引中直接完成查詢，還是要回表查詢資料；

1. Hash索引遇到大量Hash值相等的情況後性能并不一定就會比B+樹索引高。

補充：

1.MySQL中，隻有HEAP/MEMORY引擎才顯示支援Hash索引。

2.常用的InnoDB引擎中預設使用的是B+樹索引，它會實時監控表上索引的使用情況，如果認為建立哈希索引可以提高查詢效率，則自動在記憶體中的“自适應哈希索引緩沖區”建立哈希索引（在InnoDB中預設開啟自适應哈希索引），通過觀察搜尋模式，MySQL會利用index key的字首建立哈希索引，如果一個表幾乎大部分都在緩沖池中，那麼建立一個哈希索引能夠加快等值查詢。

B+樹索引和哈希索引的明顯差別是：

3.如果是等值查詢，那麼哈希索引明顯有絕對優勢，因為隻需要經過一次算法即可找到相應的鍵值；當然了，這個前提是，鍵值都是唯一的。如果鍵值不是唯一的，就需要先找到該鍵所在位置，然後再根據連結清單往後掃描，直到找到相應的資料；

4.如果是範圍查詢檢索，這時候哈希索引就毫無用武之地了，因為原先是有序的鍵值，經過雜湊演算法後，有可能變成不連續的了，就沒辦法再利用索引完成範圍查詢檢索；

同理，哈希索引沒辦法利用索引完成排序，以及like ‘xxx%’ 這樣的部分模糊查詢（這種部分模糊查詢，其實本質上也是範圍查詢）；

5.哈希索引也不支援多列聯合索引的最左比對規則；

6.B+樹索引的關鍵字檢索效率比較平均，不像B樹那樣波動幅度大，在有大量重複鍵值情況下，哈希索引的效率也是極低的，因為存在所謂的哈希碰撞問題。

7.在大多數場景下，都會有範圍查詢、排序、分組等查詢特征，用B+樹索引就可以了。

7.B樹和B+樹的差別

1. B樹，每個節點都存儲key和data，所有節點組成這棵樹，并且葉子節點指針為nul，葉子結點不包含任何關鍵字資訊
2. B+樹，所有的葉子結點中包含了全部關鍵字的資訊，及指向含有這些關鍵字記錄的指針，且葉子結點本身依關鍵字的大小自小而大的順序連結，所有的非終端結點可以看成是索引部分，結點中僅含有其子樹根結點中最大（或最小）關鍵字。(而B 樹的非終節點也包含需要查找的有效資訊)

   

8.為什麼說B+比B樹更适合實際應用中作業系統的檔案索引和資料庫索引？

1.B+的磁盤讀寫代價更低

B+的内部結點并沒有指向關鍵字具體資訊的指針。是以其内部結點相對B樹更小。如果把所有同一内部結點的關鍵字存放在同一盤塊中，那麼盤塊所能容納的關鍵字數量也越多。一次性讀入記憶體中的需要查找的關鍵字也就越多。相對來說IO讀寫次數也就降低了。

2.B+tree的查詢效率更加穩定

由于非終結點并不是最終指向檔案内容的結點，而隻是葉子結點中關鍵字的索引。是以任何關鍵字的查找必須走一條從根結點到葉子結點的路。所有關鍵字查詢的路徑長度相同，導緻每一個資料的查詢效率相當。

9.聚集索引和非聚集索引差別?

聚合索引(clustered index):

聚集索引表記錄的排列順序和索引的排列順序一緻，是以查詢效率快，隻要找到第一個索引值記錄，其餘就連續性的記錄在實體也一樣連續存放。聚集索引對應的缺點就是修改慢，因為為了保證表中記錄的實體和索引順序一緻，在記錄插入的時候，會對資料頁重新排序。

聚集索引類似于新華字典中用拼音去查找漢字，拼音檢索表于書記順序都是按照a~z排列的，就像相同的邏輯順序于實體順序一樣，當你需要查找a,ai兩個讀音的字，或是想一次尋找多個傻(sha)的同音字時，也許向後翻幾頁，或緊接着下一行就得到結果了。

非聚合索引(nonclustered index):

非聚集索引指定了表中記錄的邏輯順序，但是記錄的實體和索引不一定一緻，兩種索引都采用B+樹結構，非聚集索引的葉子層并不和實際資料頁相重疊，而采用葉子層包含一個指向表中的記錄在資料頁中的指針方式。非聚集索引層次多，不會造成資料重排。

非聚集索引類似在新華字典上通過偏旁部首來查詢漢字，檢索表也許是按照橫、豎、撇來排列的，但是由于正文中是a~z的拼音順序，是以就類似于邏輯位址于實體位址的不對應。同時适用的情況就在于分組，大數目的不同值，頻繁更新的列中，這些情況即不适合聚集索引。

根本差別：

聚集索引和非聚集索引的根本差別是表記錄的排列順序和與索引的排列順序是否一緻。

三、事務

1.什麼是事務？

事務是對資料庫中一系列操作進行統一的復原或者送出的操作，主要用來保證資料的完整性和一緻性。

2.事務四大特性（ACID）原子性、一緻性、隔離性、持久性?

原子性（Atomicity）:

原子性是指事務包含的所有操作要麼全部成功，要麼全部失敗復原，是以事務的操作如果成功就必須要完全應用到資料庫，如果操作失敗則不能對資料庫有任何影響。

一緻性（Consistency）:

事務開始前和結束後，資料庫的完整性限制沒有被破壞。比如A向B轉賬，不可能A扣了錢，B卻沒收到。

隔離性（Isolation）:

隔離性是當多個使用者并發通路資料庫時，比如操作同一張表時，資料庫為每一個使用者開啟的事務，不能被其他事務的操作所幹擾，多個并發事務之間要互相隔離。同一時間，隻允許一個事務請求同一資料，不同的事務之間彼此沒有任何幹擾。比如A正在從一張銀行卡中取錢，在A取錢的過程結束前，B不能向這張卡轉賬。

持久性（Durability）:

持久性是指一個事務一旦被送出了，那麼對資料庫中的資料的改變就是永久性的，即便是在資料庫系統遇到故障的情況下也不會丢失送出事務的操作。

3.事務的并發?事務隔離級别，每個級别會引發什麼問題，MySQL預設是哪個級别?

從理論上來說, 事務應該彼此完全隔離, 以避免并發事務所導緻的問題，然而, 那樣會對性能産生極大的影響, 因為事務必須按順序運作， 在實際開發中, 為了提升性能, 事務會以較低的隔離級别運作， 事務的隔離級别可以通過隔離事務屬性指定。

事務的并發問題

1、髒讀：事務A讀取了事務B更新的資料，然後B復原操作，那麼A讀取到的資料是髒資料

2、不可重複讀：事務 A 多次讀取同一資料，事務 B 在事務A多次讀取的過程中，對資料作了更新并送出，導緻事務A多次讀取同一資料時，結果是以本事務先後兩次讀到的資料結果會不一緻。

3、幻讀：幻讀解決了不重複讀，保證了同一個事務裡，查詢的結果都是事務開始時的狀态（一緻性）。

例如：事務T1對一個表中所有的行的某個資料項做了從“1”修改為“2”的操作 這時事務T2又對這個表中插入了一行資料項，而這個資料項的數值還是為“1”并且送出給資料庫。而操作事務T1的使用者如果再檢視剛剛修改的資料，會發現還有跟沒有修改一樣，其實這行是從事務T2中添加的，就好像産生幻覺一樣，這就是發生了幻讀。

小結：不可重複讀的和幻讀很容易混淆，不可重複讀側重于修改，幻讀側重于新增或删除。解決不可重複讀的問題隻需鎖住滿足條件的行，解決幻讀需要鎖表。

事務的隔離級别

讀未送出：另一個事務修改了資料，但尚未送出，而本事務中的SELECT會讀到這些未被送出的資料髒讀

不可重複讀：事務 A 多次讀取同一資料，事務 B 在事務A多次讀取的過程中，對資料作了更新并送出，導緻事務A多次讀取同一資料時，結果是以本事務先後兩次讀到的資料結果會不一緻。

可重複讀：在同一個事務裡，SELECT的結果是事務開始時時間點的狀态，是以，同樣的SELECT操作讀到的結果會是一緻的。但是，會有幻讀現象

串行化：最高的隔離級别，在這個隔離級别下，不會産生任何異常。并發的事務，就像事務是在一個個按照順序執行一樣

特别注意：

MySQL預設的事務隔離級别為repeatable-read

MySQL 支援 4 中事務隔離級别.

事務的隔離級别要得到底層資料庫引擎的支援, 而不是應用程式或者架構的支援.

Oracle 支援的 2 種事務隔離級别：READ_COMMITED , SERIALIZABLE

SQL規範所規定的标準，不同的資料庫具體的實作可能會有些差異

MySQL中預設事務隔離級别是“可重複讀”時并不會鎖住讀取到的行

事務隔離級别：未送出讀時，寫資料隻會鎖住相應的行。

事務隔離級别為：可重複讀時，寫資料會鎖住整張表。

事務隔離級别為：串行化時，讀寫資料都會鎖住整張表。

隔離級别越高，越能保證資料的完整性和一緻性，但是對并發性能的影響也越大，魚和熊掌不可兼得啊。對于多數應用程式，可以優先考慮把資料庫系統的隔離級别設為Read Committed，它能夠避免髒讀取，而且具有較好的并發性能。盡管它會導緻不可重複讀、幻讀這些并發問題，在可能出現這類問題的個别場合，可以由應用程式采用悲觀鎖或樂觀鎖來控制。

4.事務傳播行為

1.PROPAGATION_REQUIRED：如果目前沒有事務，就建立一個新事務，如果目前存在事務，就加入該事務，該設定是最常用的設定。

2.PROPAGATION_SUPPORTS：支援目前事務，如果目前存在事務，就加入該事務，如果目前不存在事務，就以非事務執行。

3.PROPAGATION_MANDATORY：支援目前事務，如果目前存在事務，就加入該事務，如果目前不存在事務，就抛出異常。

4.PROPAGATION_REQUIRES_NEW：建立新事務，無論目前存不存在事務，都建立新事務。

5.PROPAGATION_NOT_SUPPORTED：以非事務方式執行操作，如果目前存在事務，就把目前事務挂起。

6.PROPAGATION_NEVER：以非事務方式執行，如果目前存在事務，則抛出異常。

7.PROPAGATION_NESTED：如果目前存在事務，則在嵌套事務内執行。如果目前沒有事務，則執行與PROPAGATION_REQUIRED類似的操作。

5.嵌套事務

什麼是嵌套事務？

嵌套是子事務套在父事務中執行，子事務是父事務的一部分，在進入子事務之前，父事務建立一個復原點，叫save point，然後執行子事務，這個子事務的執行也算是父事務的一部分，然後子事務執行結束，父事務繼續執行。重點就在于那個save point。看幾個問題就明了了：

如果子事務復原，會發生什麼？

父事務會復原到進入子事務前建立的save point，然後嘗試其他的事務或者其他的業務邏輯，父事務之前的操作不會受到影響，更不會自動復原。

如果父事務復原，會發生什麼？

父事務復原，子事務也會跟着復原！為什麼呢，因為父事務結束之前，子事務是不會送出的，我們說子事務是父事務的一部分，正是這個道理。那麼：

事務的送出，是什麼情況？

是父事務先送出，然後子事務送出，還是子事務先送出，父事務再送出？答案是第二種情況，還是那句話，子事務是父事務的一部分，由父事務統一送出。

四、存儲引擎

1.MySQL常見的三種存儲引擎（InnoDB、MyISAM、MEMORY）的差別?

兩種存儲引擎的大緻差別表現在：

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

2.MyISAM适合查詢以及插入為主的應用。

3.InnoDB适合頻繁修改以及涉及到安全性較高的應用。

4.InnoDB支援外鍵，MyISAM不支援。

5.從MySQL5.5.5以後，InnoDB是預設引擎。

6.InnoDB不支援FULLTEXT類型的索引。

7.InnoDB中不儲存表的行數，如select count() from table時，InnoDB需要掃描一遍整個表來計算有多少行，但是MyISAM隻要簡單的讀出儲存好的行數即可。注意的是，當count()語句包含where條件時MyISAM也需要掃描整個表。

8.對于自增長的字段，InnoDB中必須包含隻有該字段的索引，但是在MyISAM表中可以和其他字段一起建立聯合索引。

9.DELETE FROM table時，InnoDB不會重建立立表，而是一行一行的 删除，效率非常慢。MyISAM則會重建表。

10.InnoDB支援行鎖（某些情況下還是鎖整表，如 update table set a=1 where user like '%lee%'。

2.MySQL存儲引擎MyISAM與InnoDB如何選擇

MySQL有多種存儲引擎，每種存儲引擎有各自的優缺點，可以擇優選擇使用：MyISAM、InnoDB、MERGE、MEMORY(HEAP)、BDB(BerkeleyDB)、EXAMPLE、FEDERATED、ARCHIVE、CSV、BLACKHOLE。

雖然MySQL裡的存儲引擎不隻是MyISAM與InnoDB這兩個，但常用的就是兩個。

關于MySQL資料庫提供的兩種存儲引擎，MyISAM與InnoDB選擇使用：

• 1.INNODB會支援一些關系資料庫的進階功能，如事務功能和行級鎖，MyISAM不支援。
• 2.MyISAM的性能更優，占用的存儲空間少，是以，選擇何種存儲引擎，視具體應用而定。

如果你的應用程式一定要使用事務，毫無疑問你要選擇INNODB引擎。但要注意，INNODB的行級鎖是有條件的。在where條件沒有使用主鍵時，照樣會鎖全表。比如DELETE FROM mytable這樣的删除語句。

如果你的應用程式對查詢性能要求較高，就要使用MyISAM了。MyISAM索引和資料是分開的，而且其索引是壓縮的，可以更好地利用記憶體。是以它的查詢性能明顯優于INNODB。壓縮後的索引也能節約一些磁盤空間。MyISAM擁有全文索引的功能，這可以極大地優化LIKE查詢的效率。

有人說MyISAM隻能用于小型應用，其實這隻是一種偏見。如果資料量比較大，這是需要通過更新架構來解決，比如分表分庫，而不是單純地依賴存儲引擎。

現在一般都是選用innodb了，主要是MyISAM的全表鎖，讀寫串行問題，并發效率鎖表，效率低，MyISAM對于讀寫密集型應用一般是不會去選用的。

MEMORY存儲引擎

MEMORY是MySQL中一類特殊的存儲引擎。它使用存儲在記憶體中的内容來建立表，而且資料全部放在記憶體中。這些特性與前面的兩個很不同。

每個基于MEMORY存儲引擎的表實際對應一個磁盤檔案。該檔案的檔案名與表名相同，類型為frm類型。該檔案中隻存儲表的結構。而其資料檔案，都是存儲在記憶體中，這樣有利于資料的快速處理，提高整個表的效率。值得注意的是，伺服器需要有足夠的記憶體來維持MEMORY存儲引擎的表的使用。如果不需要了，可以釋放記憶體，甚至删除不需要的表。

MEMORY預設使用哈希索引。速度比使用B型樹索引快。當然如果你想用B型樹索引，可以在建立索引時指定。

注意，MEMORY用到的很少，因為它是把資料存到記憶體中，如果記憶體出現異常就會影響資料。如果重新開機或者關機，所有資料都會消失。是以，基于MEMORY的表的生命周期很短，一般是一次性的。

3.MySQL的MyISAM與InnoDB兩種存儲引擎在，事務、鎖級别，各自的适用場景?

事務處理上方面

• MyISAM：強調的是性能，每次查詢具有原子性,其執行數度比InnoDB類型更快，但是不提供事務支援。
• InnoDB：提供事務支援事務，外部鍵等進階資料庫功能。具有事務(commit)、復原(rollback)和崩潰修複能力(crash recovery capabilities)的事務安全(transaction-safe (ACID compliant))型表。

鎖級别

• MyISAM：隻支援表級鎖，使用者在操作MyISAM表時，select，update，delete，insert語句都會給表自動加鎖，如果加鎖以後的表滿足insert并發的情況下，可以在表的尾部插入新的資料。
• InnoDB：支援事務和行級鎖，是innodb的最大特色。行鎖大幅度提高了多使用者并發操作的新能。但是InnoDB的行鎖，隻是在WHERE的主鍵是有效的，非主鍵的WHERE都會鎖全表的。

關于存儲引擎MyISAM和InnoDB的其他參考資料如下：

MySQL存儲引擎中的MyISAM和InnoDB差別詳解

MySQL存儲引擎之MyISAM和Innodb總結性梳理

五、優化

1.查詢語句不同元素（where、jion、limit、group by、having等等）執行先後順序?

• 1.查詢中用到的關鍵詞主要包含六個，并且他們的順序依次為 select--from--where--group by--having--order by

其中select和from是必須的，其他關鍵詞是可選的，這六個關鍵詞的執行順序 與sql語句的書寫順序并不是一樣的，而是按照下面的順序來執行

from:需要從哪個資料表檢索資料

where:過濾表中資料的條件

group by:如何将上面過濾出的資料分組

having:對上面已經分組的資料進行過濾的條件

select:檢視結果集中的哪個列，或列的計算結果

order by :按照什麼樣的順序來檢視傳回的資料

• 2.from後面的表關聯，是自右向左解析 而where條件的解析順序是自下而上的。

也就是說，在寫SQL語句的時候，盡量把資料量小的表放在最右邊來進行關聯（用小表去比對大表），而把能篩選出小量資料的條件放在where語句的最左邊 （用小表去比對大表）

2.使用explain優化sql和索引?

對于複雜、效率低的sql語句，我們通常是使用explain sql 來分析sql語句，這個語句可以列印出，語句的執行。這樣友善我們分析，進行優化

table：顯示這一行的資料是關于哪張表的

type：這是重要的列，顯示連接配接使用了何種類型。從最好到最差的連接配接類型為const、eq_reg、ref、range、index和ALL

all:full table scan ;MySQL将周遊全表以找到比對的行；

index: index scan; index 和 all的差別在于index類型隻周遊索引；

range：索引範圍掃描，對索引的掃描開始于某一點，傳回比對值的行，常見與between ，等查詢；

ref：非唯一性索引掃描，傳回比對某個單獨值的所有行，常見于使用非唯一索引即唯一索引的非唯一字首進行查找；

eq_ref：唯一性索引掃描，對于每個索引鍵，表中隻有一條記錄與之比對，常用于主鍵或者唯一索引掃描；

const，system：當MySQL對某查詢某部分進行優化，并轉為一個常量時，使用這些通路類型。如果将主鍵置于where清單中，MySQL就能将該查詢轉化為一個常量。

possible_keys：顯示可能應用在這張表中的索引。如果為空，沒有可能的索引。可以為相關的域從WHERE語句中選擇一個合适的語句

key： 實際使用的索引。如果為NULL，則沒有使用索引。很少的情況下，MySQL會選擇優化不足的索引。這種情況下，可以在SELECT語句中使用USE INDEX（indexname）來強制使用一個索引或者用IGNORE INDEX（indexname）來強制MySQL忽略索引

key_len：使用的索引的長度。在不損失精确性的情況下，長度越短越好

ref：顯示索引的哪一列被使用了，如果可能的話，是一個常數

rows：MySQL認為必須檢查的用來傳回請求資料的行數

Extra：關于MySQL如何解析查詢的額外資訊。将在表4.3中讨論，但這裡可以看到的壞的例子是Using temporary和Using filesort，意思MySQL根本不能使用索引，結果是檢索會很慢。

3.MySQL慢查詢怎麼解決?

• slow_query_log 慢查詢開啟狀态。

• slow_query_log_file 慢查詢日志存放的位置（這個目錄需要MySQL的運作帳号的可寫權限，一般設定為MySQL的資料存放目錄）。

• long_query_time 查詢超過多少秒才記錄。

六、資料庫鎖

1.mysql都有什麼鎖，死鎖判定原理和具體場景，死鎖怎麼解決?

MySQL有三種鎖的級别：頁級、表級、行級。

• 表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的機率最高,并發度最低。
• 行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的機率最低,并發度也最高。

• 頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般

  什麼情況下會造成死鎖?

什麼是死鎖？

死鎖: 是指兩個或兩個以上的程序在執行過程中。因争奪資源而造成的一種互相等待的現象,若無外力作用,它們都将無法推進下去。此時稱系統處于死鎖狀态或系統産生了死鎖,這些永遠在互相等竺的程序稱為死鎖程序。

表級鎖不會産生死鎖.是以解決死鎖主要還是針對于最常用的InnoDB。

死鎖的關鍵在于：兩個(或以上)的Session加鎖的順序不一緻。

那麼對應的解決死鎖問題的關鍵就是：讓不同的session加鎖有次序。

死鎖的解決辦法?

1.查出的線程殺死 kill

SELECT trx_MySQL_thread_id FROM information_schema.INNODB_TRX;

2.設定鎖的逾時時間

Innodb 行鎖的等待時間，機關秒。可在會話級别設定，RDS 執行個體該參數的預設值為 50（秒）。

生産環境不推薦使用過大的 innodb_lock_wait_timeout參數值

該參數支援在會話級别修改，友善應用在會話級别單獨設定某些特殊操作的行鎖等待逾時時間，如下：

set innodb_lock_wait_timeout=1000; —設定目前會話 Innodb 行鎖等待逾時時間，機關秒。

3.指定擷取鎖的順序

2.有哪些鎖（樂觀鎖悲觀鎖），select 時怎麼加排它鎖?

悲觀鎖（Pessimistic Lock）:

悲觀鎖特點:先擷取鎖，再進行業務操作。

即“悲觀”的認為擷取鎖是非常有可能失敗的，是以要先確定擷取鎖成功再進行業務操作。通常所說的“一鎖二查三更新”即指的是使用悲觀鎖。通常來講在資料庫上的悲觀鎖需要資料庫本身提供支援，即通過常用的select … for update操作來實作悲觀鎖。當資料庫執行select for update時會擷取被select中的資料行的行鎖，是以其他并發執行的select for update如果試圖選中同一行則會發生排斥（需要等待行鎖被釋放），是以達到鎖的效果。select for update擷取的行鎖會在目前事務結束時自動釋放，是以必須在事務中使用。

不同的資料庫對select for update的實作和支援都是有所差別的，

• oracle支援select for update no wait，表示如果拿不到鎖立刻報錯，而不是等待，MySQL就沒有no wait這個選項。
• MySQL還有個問題是select for update語句執行中所有掃描過的行都會被鎖上，這一點很容易造成問題。是以如果在MySQL中用悲觀鎖務必要确定走了索引，而不是全表掃描。

樂觀鎖（Optimistic Lock）:

1.樂觀鎖，也叫樂觀并發控制，它假設多使用者并發的事務在處理時不會彼此互相影響，各事務能夠在不産生鎖的情況下處理各自影響的那部分資料。在送出資料更新之前，每個事務會先檢查在該事務讀取資料後，有沒有其他事務又修改了該資料。如果其他事務有更新的話，那麼目前正在送出的事務會進行復原。

2.**樂觀鎖的特點先進行業務操作，不到萬不得已不去拿鎖。**即“樂觀”的認為拿鎖多半是會成功的，是以在進行完業務操作需要實際更新資料的最後一步再去拿一下鎖就好。

樂觀鎖在資料庫上的實作完全是邏輯的，不需要資料庫提供特殊的支援。

3.一般的做法是在需要鎖的資料上增加一個版本号，或者時間戳，

實作方式舉例如下：

樂觀鎖（給表加一個版本号字段） 這個并不是樂觀鎖的定義，給表加版本号，是資料庫實作樂觀鎖的一種方式。

1. SELECT data AS old_data, version AS old_version FROM …;
2. 根據擷取的資料進行業務操作，得到new_data和new_version
3. UPDATE SET data = new_data, version = new_version WHERE version = old_version

if (updated row > 0) {

// 樂觀鎖擷取成功，操作完成

} else {

// 樂觀鎖擷取失敗，復原并重試

}

• 樂觀鎖在不發生取鎖失敗的情況下開銷比悲觀鎖小，但是一旦發生失敗復原開銷則比較大，是以适合用在取鎖失敗機率比較小的場景，可以提升系統并發性能
• 樂觀鎖還适用于一些比較特殊的場景，例如在業務操作過程中無法和資料庫保持連接配接等悲觀鎖無法适用的地方。

總結：

悲觀鎖和樂觀鎖是資料庫用來保證資料并發安全防止更新丢失的兩種方法，例子在select ... for update前加個事務就可以防止更新丢失。悲觀鎖和樂觀鎖大部分場景下差異不大，一些獨特場景下有一些差别，一般我們可以從如下幾個方面來判斷。

• 響應速度： 如果需要非常高的響應速度，建議采用樂觀鎖方案，成功就執行，不成功就失敗，不需要等待其他并發去釋放鎖。'

• 沖突頻率： 如果沖突頻率非常高，建議采用悲觀鎖，保證成功率，如果沖突頻率大，樂觀鎖會需要多次重試才能成功，代價比較大。

• 重試代價： 如果重試代價大，建議采用悲觀鎖。

七、其他

1.資料庫的主從複制

主從複制的幾種方式:

同步複制:

所謂的同步複制，意思是master的變化，必須等待slave-1,slave-2,...,slave-n完成後才能傳回。這樣，顯然不可取，也不是MySQL複制的預設設定。比如，在WEB前端頁面上，使用者增加了條記錄，需要等待很長時間。

異步複制:

如同AJAX請求一樣。master隻需要完成自己的資料庫操作即可。至于slaves是否收到二進制日志，是否完成操作，不用關心,MySQL的預設設定。

半同步複制:

master隻保證slaves中的一個操作成功，就傳回，其他slave不管。這個功能，是由google為MySQL引入的。

2.資料庫主從複制分析的 7 個問題?

問題1：master的寫操作，slaves被動的進行一樣的操作，保持資料一緻性，那麼slave是否可以主動的進行寫操作？

假設slave可以主動的進行寫操作，slave又無法通知master，這樣就導緻了master和slave資料不一緻了。是以slave不應該進行寫操作，至少是slave上涉及到複制的資料庫不可以寫。實際上，這裡已經揭示了讀寫分離的概念。

問題2：主從複制中，可以有N個slave,可是這些slave又不能進行寫操作，要他們幹嘛？

實作資料備份:

類似于高可用的功能，一旦master挂了，可以讓slave頂上去，同時slave提升為master。

異地容災:比如master在北京，地震挂了，那麼在上海的slave還可以繼續。

主要用于實作scale out,分擔負載,可以将讀的任務分散到slaves上。

【很可能的情況是，一個系統的讀操作遠遠多于寫操作，是以寫操作發向master，讀操作發向slaves進行操作】

問題3：主從複制中有master,slave1,slave2,...等等這麼多MySQL資料庫，那比如一個JAVA WEB應用到底應該連接配接哪個資料庫?

我們在應用程式中可以這樣，insert/delete/update這些更新資料庫的操作，用connection(for master)進行操作，

select用connection(for slaves)進行操作。那我們的應用程式還要完成怎麼從slaves選擇一個來執行select，例如使用簡單的輪循算法。

這樣的話，相當于應用程式完成了SQL語句的路由，而且與MySQL的主從複制架構非常關聯，一旦master挂了，某些slave挂了，那麼應用程式就要修改了。能不能讓應用程式與MySQL的主從複制架構沒有什麼太多關系呢？

找一個元件，application program隻需要與它打交道，用它來完成MySQL的代理，實作SQL語句的路由。

MySQL proxy并不負責，怎麼從衆多的slaves挑一個？可以交給另一個元件(比如haproxy)來完成。

這就是所謂的MySQL READ WRITE SPLITE，MySQL的讀寫分離。

問題4：如果MySQL proxy , direct , master他們中的某些挂了怎麼辦？

總統一般都會弄個副總統，以防不測。同樣的，可以給這些關鍵的節點來個備份。

問題5：當master的二進制日志每産生一個事件，都需要發往slave，如果我們有N個slave,那是發N次，還是隻發一次？如果隻發一次，發給了slave-1，那slave-2,slave-3,...它們怎麼辦？

顯 然，應該發N次。實際上，在MySQL master内部，維護N個線程，每一個線程負責将二進制日志檔案發往對應的slave。master既要負責寫操作，還的維護N個線程，負擔會很重。可以這樣，slave-1是master的從，slave-1又是slave-2,slave-3,...的主，同時slave-1不再負責select。slave-1将master的複制線程的負擔，轉移到自己的身上。這就是所謂的多級複制的概念。

問題6：當一個select發往MySQL proxy，可能這次由slave-2響應，下次由slave-3響應，這樣的話，就無法利用查詢緩存了。

應該找一個共享式的緩存，比如memcache來解決。将slave-2,slave-3,...這些查詢的結果都緩存至mamcache中。

問題7：随着應用的日益增長，讀操作很多，我們可以擴充slave，但是如果master滿足不了寫操作了，怎麼辦呢？

scale on ?更好的伺服器？沒有最好的，隻有更好的，太貴了。。。

scale out ? 主從複制架構已經滿足不了。

可以分庫【垂直拆分】，分表【水準拆分】。

3.mysql 高并發環境解決方案?

MySQL 高并發環境解決方案： 分庫 分表 分布式 增加二級緩存。。。。。

需求分析：網際網路機關 每天大量資料讀取，寫入，并發性高。

現有解決方式：水準分庫分表，由單點分布到多點資料庫中，進而降低單點資料庫壓力。

叢集方案：解決DB當機帶來的單點DB不能通路問題。

讀寫分離政策：極大限度提高了應用中Read資料的速度和并發量。無法解決高寫入壓力。

4.資料庫崩潰時事務的恢複機制（REDO日志和UNDO日志）?

Undo Log:

Undo Log是為了實作事務的原子性，在MySQL資料庫InnoDB存儲引擎中，還用了Undo Log來實作多版本并發控制(簡稱：MVCC)。

事務的原子性(Atomicity)事務中的所有操作，要麼全部完成，要麼不做任何操作，不能隻做部分操作。如果在執行的過程中發生了錯誤，要復原(Rollback)到事務開始前的狀态，就像這個事務從來沒有執行過。

原理Undo Log的原理很簡單，為了滿足事務的原子性，在操作任何資料之前，首先将資料備份到一個地方（這個存儲資料備份的地方稱為UndoLog）。然後進行資料的修改。如果出現了錯誤或者使用者執行了ROLLBACK語句，系統可以利用Undo Log中的備份将資料恢複到事務開始之前的狀态。

之是以能同時保證原子性和持久化，是因為以下特點：

更新資料前記錄Undo log。

為了保證持久性，必須将資料在事務送出前寫到磁盤。隻要事務成功送出，資料必然已經持久化。

Undo log必須先于資料持久化到磁盤。如果在G,H之間系統崩潰，undo log是完整的， 可以用來復原事務。

如果在A-F之間系統崩潰,因為資料沒有持久化到磁盤。是以磁盤上的資料還是保持在事務開始前的狀态。