兩萬字的數(shù)據(jù)庫面試題，不看絕對后悔

一、基本概念

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

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

泰興ssl適用于網(wǎng)站、小程序/APP、API接口等需要進行數(shù)據(jù)傳輸應用場景，ssl證書未來市場廣闊！成為創(chuàng)新互聯(lián)的ssl證書銷售渠道，可以享受市場價格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：13518219792（備注：SSL證書合作）期待與您的合作！

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

主鍵：數(shù)據(jù)庫表中對儲存數(shù)據(jù)對象予以唯一和完整標識的數(shù)據(jù)列或?qū)傩缘慕M合。一個數(shù)據(jù)列只能有一個主鍵，且主鍵的取值不能缺失，即不能為空值（Null）。

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

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

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

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

如果也沒有這樣的唯一索引，則InnoDB會選擇內(nèi)置6字節(jié)長的ROWID作為隱含的聚集索引(ROWID隨著行記錄的寫入而主鍵遞增，這個ROWID不像ORACLE的ROWID那樣可引用，是隱含的)。

數(shù)據(jù)記錄本身被存于主索引（一顆B+Tree）的葉子節(jié)點上。這就要求同一個葉子節(jié)點內(nèi)（大小為一個內(nèi)存頁或磁盤頁）的各條數(shù)據(jù)記錄按主鍵順序存放，因此每當有一條新的記錄插入時，MySQL會根據(jù)其主鍵將其插入適當?shù)墓?jié)點和位置，如果頁面達到裝載因子（InnoDB默認為15/16），則開辟一個新的頁（節(jié)點）

如果表使用自增主鍵，那么每次插入新的記錄，記錄就會順序添加到當前索引節(jié)點的后續(xù)位置，當一頁寫滿，就會自動開辟一個新的頁

如果使用非自增主鍵（如果身份證號或?qū)W號等），由于每次插入主鍵的值近似于隨機，因此每次新紀錄都要被插到現(xiàn)有索引頁得中間某個位置，此時MySQL不得不為了將新記錄插到合適位置而移動數(shù)據(jù)，甚至目標頁面可能已經(jīng)被回寫到磁盤上而從緩存中清掉，此時又要從磁盤上讀回來，這增加了很多開銷，同時頻繁的移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結(jié)構(gòu)，后續(xù)不得不通過OPTIMIZE TABLE來重建表并優(yōu)化填充頁面。

3.觸發(fā)器的作用？

觸發(fā)器是一種特殊的存儲過程，主要是通過事件來觸發(fā)而被執(zhí)行的。它可以強化約束，來維護數(shù)據(jù)的完整性和一致性，可以跟蹤數(shù)據(jù)庫內(nèi)的操作從而不允許未經(jīng)許可的更新和變化?？梢月?lián)級運算。如，某表上的觸發(fā)器上包含對另一個表的數(shù)據(jù)操作，而該操作又會導致該表觸發(fā)器被觸發(fā)。

4.什么是存儲過程？用什么來調(diào)用？

存儲過程是一個預編譯的SQL語句，優(yōu)點是允許模塊化的設計，就是說只需創(chuàng)建一次，以后在該程序中就可以調(diào)用多次。如果某次操作需要執(zhí)行多次SQL，使用存儲過程比單純SQL語句執(zhí)行要快。

調(diào)用：

1）可以用一個命令對象來調(diào)用存儲過程。

2）可以供外部程序調(diào)用，比如：java程序。

5.存儲過程的優(yōu)缺點？

優(yōu)點：

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

2）存儲過程的代碼直接存放于數(shù)據(jù)庫中，通過存儲過程名直接調(diào)用，減少網(wǎng)絡通訊。

3）安全性高，執(zhí)行存儲過程需要有一定權(quán)限的用戶。

4）存儲過程可以重復使用，可減少數(shù)據(jù)庫開發(fā)人員的工作量。

缺點：移植性差

6.存儲過程與函數(shù)的區(qū)別

7.什么叫視圖？游標是什么？

視圖：

是一種虛擬的表，具有和物理表相同的功能?？梢詫σ晥D進行增，改，查，操作，試圖通常是有一個表或者多個表的行或列的子集。對視圖的修改會影響基本表。它使得我們獲取數(shù)據(jù)更容易，相比多表查詢。

游標：

是對查詢出來的結(jié)果集作為一個單元來有效的處理。游標可以定在該單元中的特定行，從結(jié)果集的當前行檢索一行或多行?？梢詫Y(jié)果集當前行做修改。一般不使用游標，但是需要逐條處理數(shù)據(jù)的時候，游標顯得十分重要。

8.視圖的優(yōu)缺點

優(yōu)點：

1對數(shù)據(jù)庫的訪問，因為視圖可以有選擇性的選取數(shù)據(jù)庫里的一部分。

2)用戶通過簡單的查詢可以從復雜查詢中得到結(jié)果。

3)維護數(shù)據(jù)的獨立性，試圖可從多個表檢索數(shù)據(jù)。

4)對于相同的數(shù)據(jù)可產(chǎn)生不同的視圖。

缺點：

性能：查詢視圖時，必須把視圖的查詢轉(zhuǎn)化成對基本表的查詢，如果這個視圖是由一個復雜的多表查詢所定義，那么，那么就無法更改數(shù)據(jù)

9.drop、truncate、 delete區(qū)別

最基本：

• drop直接刪掉表。

• truncate刪除表中數(shù)據(jù)，再插入時自增長id又從1開始。

• delete刪除表中數(shù)據(jù)，可以加where字句。

（1） DELETE語句執(zhí)行刪除的過程是每次從表中刪除一行，并且同時將該行的刪除操作作為事務記錄在日志中保存以便進行進行回滾操作。TRUNCATE TABLE 則一次性地從表中刪除所有的數(shù)據(jù)并不把單獨的刪除操作記錄記入日志保存，刪除行是不能恢復的。并且在刪除的過程中不會激活與表有關的刪除觸發(fā)器。執(zhí)行速度快。

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

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

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

（5） TRUNCATE 和DELETE只刪除數(shù)據(jù)，而DROP則刪除整個表（結(jié)構(gòu)和數(shù)據(jù)）。

（6） truncate與不帶where的delete ：只刪除數(shù)據(jù)，而不刪除表的結(jié)構(gòu)（定義）drop語句將刪除表的結(jié)構(gòu)被依賴的約束（constrain),觸發(fā)器（trigger)索引（index);依賴于該表的存儲過程/函數(shù)將被保留，但其狀態(tài)會變?yōu)椋篿nvalid。

（7） delete語句為DML（data maintain Language),這個操作會被放到 rollback segment中,事務提交后才生效。如果有相應的 tigger,執(zhí)行的時候?qū)⒈挥|發(fā)。

（8） truncate、drop是DLL（data define language),操作立即生效，原數(shù)據(jù)不放到 rollback segment中，不能回滾。

（9） 在沒有備份情況下，謹慎使用 drop 與 truncate。要刪除部分數(shù)據(jù)行采用delete且注意結(jié)合where來約束影響范圍。回滾段要足夠大。要刪除表用drop;若想保留表而將表中數(shù)據(jù)刪除，如果于事務無關，用truncate即可實現(xiàn)。如果和事務有關，或老師想觸發(fā)trigger,還是用delete。

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

（11） TRUNCATE TABLE 刪除表中的所有行，但表結(jié)構(gòu)及其列、約束、索引等保持不變。新行標識所用的計數(shù)值重置為該列的種子。如果想保留標識計數(shù)值，請改用 DELETE。如果要刪除表定義及其數(shù)據(jù)，請使用 DROP TABLE 語句。

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

10.什么是臨時表，臨時表什么時候刪除?

臨時表可以手動刪除：
DROP TEMPORARY TABLE IF EXISTS temp_tb;

臨時表只在當前連接可見，當關閉連接時，MySQL會自動刪除表并釋放所有空間。因此在不同的連接中可以創(chuàng)建同名的臨時表，并且操作屬于本連接的臨時表。
創(chuàng)建臨時表的語法與創(chuàng)建表語法類似，不同之處是增加關鍵字TEMPORARY，

如：

CREATE TEMPORARY TABLE tmp_table (

NAME VARCHAR (10) NOT NULL,

time date NOT NULL
);

select * from tmp_table;

11.非關系型數(shù)據(jù)庫和關系型數(shù)據(jù)庫區(qū)別，優(yōu)勢比較?

非關系型數(shù)據(jù)庫的優(yōu)勢：

• 性能：NOSQL是基于鍵值對的，可以想象成表中的主鍵和值的對應關系，而且不需要經(jīng)過SQL層的解析，所以性能非常高。

• 可擴展性：同樣也是因為基于鍵值對，數(shù)據(jù)之間沒有耦合性，所以非常容易水平擴展。

關系型數(shù)據(jù)庫的優(yōu)勢：

• 復雜查詢：可以用SQL語句方便的在一個表以及多個表之間做非常復雜的數(shù)據(jù)查詢。

• 事務支持：使得對于安全性能很高的數(shù)據(jù)訪問要求得以實現(xiàn)。

其他：

1.對于這兩類數(shù)據(jù)庫，對方的優(yōu)勢就是自己的弱勢，反之亦然。

2.NoSql數(shù)據(jù)庫慢慢開始具備SQL數(shù)據(jù)庫的一些復雜查詢功能，比如MongoDB。

3.對于事務的支持也可以用一些系統(tǒng)級的原子操作來實現(xiàn)例如樂觀鎖之類的方法來曲線救國，比如redis set nx。

12.數(shù)據(jù)庫范式，根據(jù)某個場景設計數(shù)據(jù)表?

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

第一范式是最基本的范式。如果數(shù)據(jù)庫表中的所有字段值都是不可分解的原子值，就說明該數(shù)據(jù)庫表滿足了第一范式。
第一范式的合理遵循需要根據(jù)系統(tǒng)的實際需求來定。比如某些數(shù)據(jù)庫系統(tǒng)中需要用到“地址”這個屬性，本來直接將“地址”屬性設計成一個數(shù)據(jù)庫表的字段就行。但是如果系統(tǒng)經(jīng)常會訪問“地址”屬性中的“城市”部分，那么就非要將“地址”這個屬性重新拆分為省份、城市、詳細地址等多個部分進行存儲，這樣在對地址中某一部分操作的時候?qū)⒎浅７奖?。這樣設計才算滿足了數(shù)據(jù)庫的第一范式，如下表所示。
上表所示的用戶信息遵循了第一范式的要求，這樣在對用戶使用城市進行分類的時候就非常方便，也提高了數(shù)據(jù)庫的性能。

第二范式:(確保表中的每列都和主鍵相關)在一個數(shù)據(jù)庫表中，一個表中只能保存一種數(shù)據(jù)，不可以把多種數(shù)據(jù)保存在同一張數(shù)據(jù)庫表中。

第二范式在第一范式的基礎之上更進一層。第二范式需要確保數(shù)據(jù)庫表中的每一列都和主鍵相關，而不能只與主鍵的某一部分相關（主要針對聯(lián)合主鍵而言）。也就是說在一個數(shù)據(jù)庫表中，一個表中只能保存一種數(shù)據(jù)，不可以把多種數(shù)據(jù)保存在同一張數(shù)據(jù)庫表中。
比如要設計一個訂單信息表，因為訂單中可能會有多種商品，所以要將訂單編號和商品編號作為數(shù)據(jù)庫表的聯(lián)合主鍵。

第三范式:(確保每列都和主鍵列直接相關,而不是間接相關) 數(shù)據(jù)表中的每一列數(shù)據(jù)都和主鍵直接相關，而不能間接相關。

第三范式需要確保數(shù)據(jù)表中的每一列數(shù)據(jù)都和主鍵直接相關，而不能間接相關。
比如在設計一個訂單數(shù)據(jù)表的時候，可以將客戶編號作為一個外鍵和訂單表建立相應的關系。而不可以在訂單表中添加關于客戶其它信息（比如姓名、所屬公司等）的字段。

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

若關系模式屬于第二范式，且每個屬性都不傳遞依賴于鍵碼，則R屬于BC范式。
通常BC范式的條件有多種等價的表述：每個非平凡依賴的左邊必須包含鍵碼；每個決定因素必須包含鍵碼。
BC范式既檢查非主屬性，又檢查主屬性。當只檢查非主屬性時，就成了第三范式。滿足BC范式的關系都必然滿足第三范式。
還可以這么說：若一個關系達到了第三范式，并且它只有一個候選碼，或者它的每個候選碼都是單屬性，則該關系自然達到BC范式。
一般，一個數(shù)據(jù)庫設計符合3NF或BCNF就可以了。

第四范式:要求把同一表內(nèi)的多對多關系刪除。

第五范式:從最終結(jié)構(gòu)重新建立原始結(jié)構(gòu)。

13.什么是 內(nèi)連接、外連接、交叉連接、笛卡爾積等?

內(nèi)連接:

數(shù)據(jù)查詢語言DQL

數(shù)據(jù)操縱語言DML

數(shù)據(jù)定義語言DDL

數(shù)據(jù)控制語言DCL。

1. 數(shù)據(jù)查詢語言DQL

數(shù)據(jù)查詢語言DQL基本結(jié)構(gòu)是由SELECT子句，F(xiàn)ROM子句，WHERE子句組成的查詢塊：

SELECT
FROM
WHERE

2 .數(shù)據(jù)操縱語言DML

數(shù)據(jù)操縱語言DML主要有三種形式：

1) 插入：INSERT

2) 更新：UPDATE

3) 刪除：DELETE

3. 數(shù)據(jù)定義語言DDL

數(shù)據(jù)定義語言DDL用來創(chuàng)建數(shù)據(jù)庫中的各種對象-----表、視圖、索引、同義詞、聚簇等如：
CREATE TABLE/VIEW/INDEX/SYN/CLUSTER

表 視圖 索引 同義詞 簇

DDL操作是隱性提交的！不能rollback

4. 數(shù)據(jù)控制語言DCL

數(shù)據(jù)控制語言DCL用來授予或回收訪問數(shù)據(jù)庫的某種特權(quán)，并控制數(shù)據(jù)庫操縱事務發(fā)生的時間及效果，對數(shù)據(jù)庫實行監(jiān)視等。如：

1) GRANT：授權(quán)。

2) ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一點。回滾---ROLLBACK；回滾命令使數(shù)據(jù)庫狀態(tài)回到上次最后提交的狀態(tài)。其格式為：
SQL>ROLLBACK;

3) COMMIT [WORK]：提交。

在數(shù)據(jù)庫的插入、刪除和修改操作時，只有當事務在提交到數(shù)據(jù)
庫時才算完成。在事務提交前，只有操作數(shù)據(jù)庫的這個人才能有權(quán)看
到所做的事情，別人只有在最后提交完成后才可以看到。
提交數(shù)據(jù)有三種類型：顯式提交、隱式提交及自動提交。下面分
別說明這三種類型。

(1) 顯式提交
用COMMIT命令直接完成的提交為顯式提交。其格式為：
SQL>COMMIT；

(2) 隱式提交
用SQL命令間接完成的提交為隱式提交。這些命令是：
ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，
EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

(3) 自動提交
若把AUTOCOMMIT設置為ON，則在插入、修改、刪除語句執(zhí)行后，
系統(tǒng)將自動進行提交，這就是自動提交。其格式為：
SQL>SET AUTOCOMMIT ON；

參考文章：
https://www.cnblogs.com/study-s/p/5287529.html

16.like %和-的區(qū)別

通配符的分類:

%百分號通配符:表示任何字符出現(xiàn)任意次數(shù)(可以是0次).

_下劃線通配符:表示只能匹配單個字符,不能多也不能少,就是一個字符.

like操作符:

%通配符使用: 匹配以"yves"開頭的記錄:(包括記錄"yves") SELECT

通配符使用: SELECT

注意大小寫,在使用模糊匹配時,也就是匹配文本時,mysql是可能區(qū)分大小的,也可能是不區(qū)分大小寫的,這個結(jié)果是取決于用戶對MySQL的配置方式.如果是區(qū)分大小寫,那么像YvesHe這樣記錄是不能被"yves__"這樣的匹配條件匹配的.

注意尾部空格,"%yves"是不能匹配"heyves "這樣的記錄的.

注意NULL,%通配符可以匹配任意字符,但是不能匹配NULL,也就是說SELECT * FROM products WHERE products.prod_name like '%;是匹配不到products.prod_name為NULL的的記錄.

技巧與建議:

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

• 不要過度使用通配符。如果其他操作符能達到相同的目的，應該 使用其他操作符。

• 在確實需要使用通配符時，除非絕對有必要，否則不要把它們用 在搜索模式的開始處。把通配符置于搜索模式的開始處，搜索起 來是最慢的。

• 仔細注意通配符的位置。如果放錯地方，可能不會返回想要的數(shù).

參考博文：https://blog.csdn.net/u011479200/article/details/78513632

17.count(*)、count(1)、count(column)的區(qū)別

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

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

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

性能問題:

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

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

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

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

• 如果有主鍵,那么count(主鍵,聯(lián)合主鍵)比count(*)快。

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

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

18.最左前綴原則

多列索引：

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

為了提高搜索效率，我們需要考慮運用多列索引,由于索引文件以B－Tree格式保存，所以我們不用掃描任何記錄，即可得到最終結(jié)果。

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

最左前綴原則：顧名思義，就是最左優(yōu)先，上例中我們創(chuàng)建了lname_fname_age多列索引,相當于創(chuàng)建了(lname)單列索引，(lname,fname)組合索引以及(lname,fname,age)組合索引。

二、索引

1.什么是索引？

何為索引：

數(shù)據(jù)庫索引，是數(shù)據(jù)庫管理系統(tǒng)中一個排序的數(shù)據(jù)結(jié)構(gòu)，索引的實現(xiàn)通常使用B樹及其變種B+樹。

在數(shù)據(jù)之外，數(shù)據(jù)庫系統(tǒng)還維護著滿足特定查找算法的數(shù)據(jù)結(jié)構(gòu)，這些數(shù)據(jù)結(jié)構(gòu)以某種方式引用（指向）數(shù)據(jù)，這樣就可以在這些數(shù)據(jù)結(jié)構(gòu)上實現(xiàn)高級查找算法。這種數(shù)據(jù)結(jié)構(gòu)，就是索引。

2.索引的作用？它的優(yōu)點缺點是什么？

索引作用：

協(xié)助快速查詢、更新數(shù)據(jù)庫表中數(shù)據(jù)。

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

• 一是增加了數(shù)據(jù)庫的存儲空間

• 二是在插入和修改數(shù)據(jù)時要花費較多的時間(因為索引也要隨之變動)。

  3.索引的優(yōu)缺點？

創(chuàng)建索引可以大大提高系統(tǒng)的性能（優(yōu)點）：

1.通過創(chuàng)建唯一性索引，可以保證數(shù)據(jù)庫表中每一行數(shù)據(jù)的唯一性。

2.可以大大加快數(shù)據(jù)的檢索速度，這也是創(chuàng)建索引的最主要的原因。

3.可以加速表和表之間的連接，特別是在實現(xiàn)數(shù)據(jù)的參考完整性方面特別有意義。

4.在使用分組和排序子句進行數(shù)據(jù)檢索時，同樣可以顯著減少查詢中分組和排序的時間。

5.通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。

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

1.創(chuàng)建索引和維護索引要耗費時間，這種時間隨著數(shù)據(jù)量的增加而增加。

2.索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會更大。

3.當對表中的數(shù)據(jù)進行增加、刪除和修改的時候，索引也要動態(tài)的維護，這樣就降低了數(shù)據(jù)的維護速度。

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

索引是建立在數(shù)據(jù)庫表中的某些列的上面。在創(chuàng)建索引的時候，應該考慮在哪些列上可以創(chuàng)建索引，在哪些列上不能創(chuàng)建索引。

一般來說，應該在這些列上創(chuàng)建索引：

（1）在經(jīng)常需要搜索的列上，可以加快搜索的速度；

（2）在作為主鍵的列上，強制該列的唯一性和組織表中數(shù)據(jù)的排列結(jié)構(gòu)；

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

（4）在經(jīng)常需要根據(jù)范圍進行搜索的列上創(chuàng)建索引，因為索引已經(jīng)排序，其指定的范圍是連續(xù)的；

（5）在經(jīng)常需要排序的列上創(chuàng)建索引，因為索引已經(jīng)排序，這樣查詢可以利用索引的排序，加快排序查詢時間；

（6）在經(jīng)常使用在WHERE子句中的列上面創(chuàng)建索引，加快條件的判斷速度。

對于有些列不應該創(chuàng)建索引：

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

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

（2）對于那些只有很少數(shù)據(jù)值的列也不應該增加索引。

這是因為，由于這些列的取值很少，例如人事表的性別列，在查詢的結(jié)果中，結(jié)果集的數(shù)據(jù)行占了表中數(shù)據(jù)行的很大比例，即需要在表中搜索的數(shù)據(jù)行的比例很大。增加索引，并不能明顯加快檢索速度。

（3）對于那些定義為text, image和bit數(shù)據(jù)類型的列不應該增加索引。

這是因為，這些列的數(shù)據(jù)量要么相當大，要么取值很少。

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

這是因為，修改性能和檢索性能是互相矛盾的。當增加索引時，會提高檢索性能，但是會降低修改性能。當減少索引時，會提高修改性能，降低檢索性能。因此，當修改性能遠遠大于檢索性能時，不應該創(chuàng)建索引。

5.什么樣的字段適合建索引

唯一、不為空、經(jīng)常被查詢的字段

6.MySQL B+Tree索引和Hash索引的區(qū)別?

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

• Hash索引結(jié)構(gòu)的特殊性，其檢索效率非常高，索引的檢索可以一次定位;

• B+樹索引需要從根節(jié)點到枝節(jié)點，最后才能訪問到頁節(jié)點這樣多次的IO訪問;

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

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

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

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

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

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

補充：

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

2.常用的InnoDB引擎中默認使用的是B+樹索引，它會實時監(jiān)控表上索引的使用情況，如果認為建立哈希索引可以提高查詢效率，則自動在內(nèi)存中的“自適應哈希索引緩沖區(qū)”建立哈希索引（在InnoDB中默認開啟自適應哈希索引），通過觀察搜索模式，MySQL會利用index key的前綴建立哈希索引，如果一個表幾乎大部分都在緩沖池中，那么建立一個哈希索引能夠加快等值查詢。
B+樹索引和哈希索引的明顯區(qū)別是：

3.如果是等值查詢，那么哈希索引明顯有絕對優(yōu)勢，因為只需要經(jīng)過一次算法即可找到相應的鍵值；當然了，這個前提是，鍵值都是唯一的。如果鍵值不是唯一的，就需要先找到該鍵所在位置，然后再根據(jù)鏈表往后掃描，直到找到相應的數(shù)據(jù)；

4.如果是范圍查詢檢索，這時候哈希索引就毫無用武之地了，因為原先是有序的鍵值，經(jīng)過哈希算法后，有可能變成不連續(xù)的了，就沒辦法再利用索引完成范圍查詢檢索；
同理，哈希索引沒辦法利用索引完成排序，以及l(fā)ike ‘xxx%’ 這樣的部分模糊查詢（這種部分模糊查詢，其實本質(zhì)上也是范圍查詢）；

5.哈希索引也不支持多列聯(lián)合索引的最左匹配規(guī)則；

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

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

7.B樹和B+樹的區(qū)別

1. B樹，每個節(jié)點都存儲key和data，所有節(jié)點組成這棵樹，并且葉子節(jié)點指針為nul，葉子結(jié)點不包含任何關鍵字信息

2. B+樹，所有的葉子結(jié)點中包含了全部關鍵字的信息，及指向含有這些關鍵字記錄的指針，且葉子結(jié)點本身依關鍵字的大小自小而大的順序鏈接，所有的非終端結(jié)點可以看成是索引部分，結(jié)點中僅含有其子樹根結(jié)點中最大（或最小）關鍵字。(而B 樹的非終節(jié)點也包含需要查找的有效信息)
   

8.為什么說B+比B樹更適合實際應用中操作系統(tǒng)的文件索引和數(shù)據(jù)庫索引？

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

B+的內(nèi)部結(jié)點并沒有指向關鍵字具體信息的指針。因此其內(nèi)部結(jié)點相對B樹更小。如果把所有同一內(nèi)部結(jié)點的關鍵字存放在同一盤塊中，那么盤塊所能容納的關鍵字數(shù)量也越多。一次性讀入內(nèi)存中的需要查找的關鍵字也就越多。相對來說IO讀寫次數(shù)也就降低了。

2.B+tree的查詢效率更加穩(wěn)定

由于非終結(jié)點并不是最終指向文件內(nèi)容的結(jié)點，而只是葉子結(jié)點中關鍵字的索引。所以任何關鍵字的查找必須走一條從根結(jié)點到葉子結(jié)點的路。所有關鍵字查詢的路徑長度相同，導致每一個數(shù)據(jù)的查詢效率相當。

9.聚集索引和非聚集索引區(qū)別?

聚合索引(clustered index):

聚集索引表記錄的排列順序和索引的排列順序一致，所以查詢效率快，只要找到第一個索引值記錄，其余就連續(xù)性的記錄在物理也一樣連續(xù)存放。聚集索引對應的缺點就是修改慢，因為為了保證表中記錄的物理和索引順序一致，在記錄插入的時候，會對數(shù)據(jù)頁重新排序。
聚集索引類似于新華字典中用拼音去查找漢字，拼音檢索表于書記順序都是按照a~z排列的，就像相同的邏輯順序于物理順序一樣，當你需要查找a,ai兩個讀音的字，或是想一次尋找多個傻(sha)的同音字時，也許向后翻幾頁，或緊接著下一行就得到結(jié)果了。

非聚合索引(nonclustered index):

非聚集索引指定了表中記錄的邏輯順序，但是記錄的物理和索引不一定一致，兩種索引都采用B+樹結(jié)構(gòu)，非聚集索引的葉子層并不和實際數(shù)據(jù)頁相重疊，而采用葉子層包含一個指向表中的記錄在數(shù)據(jù)頁中的指針方式。非聚集索引層次多，不會造成數(shù)據(jù)重排。
非聚集索引類似在新華字典上通過偏旁部首來查詢漢字，檢索表也許是按照橫、豎、撇來排列的，但是由于正文中是a~z的拼音順序，所以就類似于邏輯地址于物理地址的不對應。同時適用的情況就在于分組，大數(shù)目的不同值，頻繁更新的列中，這些情況即不適合聚集索引。

根本區(qū)別：

聚集索引和非聚集索引的根本區(qū)別是表記錄的排列順序和與索引的排列順序是否一致。

三、事務

1.什么是事務？

事務是對數(shù)據(jù)庫中一系列操作進行統(tǒng)一的回滾或者提交的操作，主要用來保證數(shù)據(jù)的完整性和一致性。

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

原子性（Atomicity）:
原子性是指事務包含的所有操作要么全部成功，要么全部失敗回滾，因此事務的操作如果成功就必須要完全應用到數(shù)據(jù)庫，如果操作失敗則不能對數(shù)據(jù)庫有任何影響。

一致性（Consistency）:
事務開始前和結(jié)束后，數(shù)據(jù)庫的完整性約束沒有被破壞。比如A向B轉(zhuǎn)賬，不可能A扣了錢，B卻沒收到。

隔離性（Isolation）:
隔離性是當多個用戶并發(fā)訪問數(shù)據(jù)庫時，比如操作同一張表時，數(shù)據(jù)庫為每一個用戶開啟的事務，不能被其他事務的操作所干擾，多個并發(fā)事務之間要相互隔離。同一時間，只允許一個事務請求同一數(shù)據(jù)，不同的事務之間彼此沒有任何干擾。比如A正在從一張銀行卡中取錢，在A取錢的過程結(jié)束前，B不能向這張卡轉(zhuǎn)賬。

持久性（Durability）:
持久性是指一個事務一旦被提交了，那么對數(shù)據(jù)庫中的數(shù)據(jù)的改變就是永久性的，即便是在數(shù)據(jù)庫系統(tǒng)遇到故障的情況下也不會丟失提交事務的操作。

3.事務的并發(fā)?事務隔離級別，每個級別會引發(fā)什么問題，MySQL默認是哪個級別?

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

1、臟讀：事務A讀取了事務B更新的數(shù)據(jù)，然后B回滾操作，那么A讀取到的數(shù)據(jù)是臟數(shù)據(jù)

2、不可重復讀：事務 A 多次讀取同一數(shù)據(jù)，事務 B 在事務A多次讀取的過程中，對數(shù)據(jù)作了更新并提交，導致事務A多次讀取同一數(shù)據(jù)時，結(jié)果因此本事務先后兩次讀到的數(shù)據(jù)結(jié)果會不一致。

3、幻讀：幻讀解決了不重復讀，保證了同一個事務里，查詢的結(jié)果都是事務開始時的狀態(tài)（一致性）。

例如：事務T1對一個表中所有的行的某個數(shù)據(jù)項做了從“1”修改為“2”的操作 這時事務T2又對這個表中插入了一行數(shù)據(jù)項，而這個數(shù)據(jù)項的數(shù)值還是為“1”并且提交給數(shù)據(jù)庫。而操作事務T1的用戶如果再查看剛剛修改的數(shù)據(jù)，會發(fā)現(xiàn)還有跟沒有修改一樣，其實這行是從事務T2中添加的，就好像產(chǎn)生幻覺一樣，這就是發(fā)生了幻讀。
小結(jié)：不可重復讀的和幻讀很容易混淆，不可重復讀側(cè)重于修改，幻讀側(cè)重于新增或刪除。解決不可重復讀的問題只需鎖住滿足條件的行，解決幻讀需要鎖表。

事務的隔離級別

讀未提交：另一個事務修改了數(shù)據(jù)，但尚未提交，而本事務中的SELECT會讀到這些未被提交的數(shù)據(jù)臟讀

不可重復讀：事務 A 多次讀取同一數(shù)據(jù)，事務 B 在事務A多次讀取的過程中，對數(shù)據(jù)作了更新并提交，導致事務A多次讀取同一數(shù)據(jù)時，結(jié)果因此本事務先后兩次讀到的數(shù)據(jù)結(jié)果會不一致。

可重復讀：在同一個事務里，SELECT的結(jié)果是事務開始時時間點的狀態(tài)，因此，同樣的SELECT操作讀到的結(jié)果會是一致的。但是，會有幻讀現(xiàn)象

串行化：最高的隔離級別，在這個隔離級別下，不會產(chǎn)生任何異常。并發(fā)的事務，就像事務是在一個個按照順序執(zhí)行一樣

特別注意：

MySQL默認的事務隔離級別為repeatable-read

MySQL 支持 4 中事務隔離級別.

事務的隔離級別要得到底層數(shù)據(jù)庫引擎的支持, 而不是應用程序或者框架的支持.

Oracle 支持的 2 種事務隔離級別：READ_COMMITED , SERIALIZABLE

SQL規(guī)范所規(guī)定的標準，不同的數(shù)據(jù)庫具體的實現(xiàn)可能會有些差異

MySQL中默認事務隔離級別是“可重復讀”時并不會鎖住讀取到的行

事務隔離級別：未提交讀時，寫數(shù)據(jù)只會鎖住相應的行。

事務隔離級別為：可重復讀時，寫數(shù)據(jù)會鎖住整張表。

事務隔離級別為：串行化時，讀寫數(shù)據(jù)都會鎖住整張表。

隔離級別越高，越能保證數(shù)據(jù)的完整性和一致性，但是對并發(fā)性能的影響也越大，魚和熊掌不可兼得啊。對于多數(shù)應用程序，可以優(yōu)先考慮把數(shù)據(jù)庫系統(tǒng)的隔離級別設為Read Committed，它能夠避免臟讀取，而且具有較好的并發(fā)性能。盡管它會導致不可重復讀、幻讀這些并發(fā)問題，在可能出現(xiàn)這類問題的個別場合，可以由應用程序采用悲觀鎖或樂觀鎖來控制。

4.事務傳播行為

1.PROPAGATION_REQUIRED：如果當前沒有事務，就創(chuàng)建一個新事務，如果當前存在事務，就加入該事務，該設置是最常用的設置。

2.PROPAGATION_SUPPORTS：支持當前事務，如果當前存在事務，就加入該事務，如果當前不存在事務，就以非事務執(zhí)行。

3.PROPAGATION_MANDATORY：支持當前事務，如果當前存在事務，就加入該事務，如果當前不存在事務，就拋出異常。

4.PROPAGATION_REQUIRES_NEW：創(chuàng)建新事務，無論當前存不存在事務，都創(chuàng)建新事務。

5.PROPAGATION_NOT_SUPPORTED：以非事務方式執(zhí)行操作，如果當前存在事務，就把當前事務掛起。

6.PROPAGATION_NEVER：以非事務方式執(zhí)行，如果當前存在事務，則拋出異常。

7.PROPAGATION_NESTED：如果當前存在事務，則在嵌套事務內(nèi)執(zhí)行。如果當前沒有事務，則執(zhí)行與PROPAGATION_REQUIRED類似的操作。

5.嵌套事務

什么是嵌套事務？

嵌套是子事務套在父事務中執(zhí)行，子事務是父事務的一部分，在進入子事務之前，父事務建立一個回滾點，叫save point，然后執(zhí)行子事務，這個子事務的執(zhí)行也算是父事務的一部分，然后子事務執(zhí)行結(jié)束，父事務繼續(xù)執(zhí)行。重點就在于那個save point?？磶讉€問題就明了了：

如果子事務回滾，會發(fā)生什么？

父事務會回滾到進入子事務前建立的save point，然后嘗試其他的事務或者其他的業(yè)務邏輯，父事務之前的操作不會受到影響，更不會自動回滾。

如果父事務回滾，會發(fā)生什么？

父事務回滾，子事務也會跟著回滾！為什么呢，因為父事務結(jié)束之前，子事務是不會提交的，我們說子事務是父事務的一部分，正是這個道理。那么：

事務的提交，是什么情況？

是父事務先提交，然后子事務提交，還是子事務先提交，父事務再提交？答案是第二種情況，還是那句話，子事務是父事務的一部分，由父事務統(tǒng)一提交。

參考文章：https://blog.csdn.net/liangxw1/article/details/51197560

四、存儲引擎

1.MySQL常見的三種存儲引擎（InnoDB、MyISAM、MEMORY）的區(qū)別?

兩種存儲引擎的大致區(qū)別表現(xiàn)在：

1.InnoDB支持事務，MyISAM不支持，

1.INNODB會支持一些關系數(shù)據(jù)庫的高級功能，如事務功能和行級鎖，MyISAM不支持。

2.MyISAM的性能更優(yōu)，占用的存儲空間少，所以，選擇何種存儲引擎，視具體應用而定。

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

如果你的應用程序?qū)Σ樵冃阅芤筝^高，就要使用MyISAM了。MyISAM索引和數(shù)據(jù)是分開的，而且其索引是壓縮的，可以更好地利用內(nèi)存。所以它的查詢性能明顯優(yōu)于INNODB。壓縮后的索引也能節(jié)約一些磁盤空間。MyISAM擁有全文索引的功能，這可以極大地優(yōu)化LIKE查詢的效率。

有人說MyISAM只能用于小型應用，其實這只是一種偏見。如果數(shù)據(jù)量比較大，這是需要通過升級架構(gòu)來解決，比如分表分庫，而不是單純地依賴存儲引擎。

現(xiàn)在一般都是選用innodb了，主要是MyISAM的全表鎖，讀寫串行問題，并發(fā)效率鎖表，效率低，MyISAM對于讀寫密集型應用一般是不會去選用的。
MEMORY存儲引擎

MEMORY是MySQL中一類特殊的存儲引擎。它使用存儲在內(nèi)存中的內(nèi)容來創(chuàng)建表，而且數(shù)據(jù)全部放在內(nèi)存中。這些特性與前面的兩個很不同。
每個基于MEMORY存儲引擎的表實際對應一個磁盤文件。該文件的文件名與表名相同，類型為frm類型。該文件中只存儲表的結(jié)構(gòu)。而其數(shù)據(jù)文件，都是存儲在內(nèi)存中，這樣有利于數(shù)據(jù)的快速處理，提高整個表的效率。值得注意的是，服務器需要有足夠的內(nèi)存來維持MEMORY存儲引擎的表的使用。如果不需要了，可以釋放內(nèi)存，甚至刪除不需要的表。

MEMORY默認使用哈希索引。速度比使用B型樹索引快。當然如果你想用B型樹索引，可以在創(chuàng)建索引時指定。

注意，MEMORY用到的很少，因為它是把數(shù)據(jù)存到內(nèi)存中，如果內(nèi)存出現(xiàn)異常就會影響數(shù)據(jù)。如果重啟或者關機，所有數(shù)據(jù)都會消失。因此，基于MEMORY的表的生命周期很短，一般是一次性的。

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

事務處理上方面

• MyISAM：強調(diào)的是性能，每次查詢具有原子性,其執(zhí)行數(shù)度比InnoDB類型更快，但是不提供事務支持。

• InnoDB：提供事務支持事務，外部鍵等高級數(shù)據(jù)庫功能。具有事務(commit)、回滾(rollback)和崩潰修復能力(crash recovery capabilities)的事務安全(transaction-safe (ACID compliant))型表。

鎖級別

• MyISAM：只支持表級鎖，用戶在操作MyISAM表時，select，update，delete，insert語句都會給表自動加鎖，如果加鎖以后的表滿足insert并發(fā)的情況下，可以在表的尾部插入新的數(shù)據(jù)。

• InnoDB：支持事務和行級鎖，是innodb的最大特色。行鎖大幅度提高了多用戶并發(fā)操作的新能。但是InnoDB的行鎖，只是在WHERE的主鍵是有效的，非主鍵的WHERE都會鎖全表的。

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

MySQL存儲引擎中的MyISAM和InnoDB區(qū)別詳解

MySQL存儲引擎之MyISAM和Innodb總結(jié)性梳理

五、優(yōu)化

---

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

• 1.查詢中用到的關鍵詞主要包含六個，并且他們的順序依次為

• 2.from后面的表關聯(lián)，是自右向左解析 而where條件的解析順序是自下而上的。

也就是說，在寫SQL語句的時候，盡量把數(shù)據(jù)量小的表放在最右邊來進行關聯(lián)（用小表去匹配大表），而把能篩選出小量數(shù)據(jù)的條件放在where語句的最左邊 （用小表去匹配大表）

其他參考資源：
http://www.cnblogs.com/huminxxl/p/3149097.html

2.使用explain優(yōu)化sql和索引?

對于復雜、效率低的sql語句，我們通常是使用explain sql 來分析sql語句，這個語句可以打印出，語句的執(zhí)行。這樣方便我們分析，進行優(yōu)化

table：顯示這一行的數(shù)據(jù)是關于哪張表的

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

all:full table scan ;MySQL將遍歷全表以找到匹配的行；

index:

slow_query_log 慢查詢開啟狀態(tài)。

• slow_query_log_file 慢查詢?nèi)罩敬娣诺奈恢茫ㄟ@個目錄需要MySQL的運行帳號的可寫權(quán)限，一般設置為MySQL的數(shù)據(jù)存放目錄）。

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

六、數(shù)據(jù)庫鎖

---

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

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

• 表級鎖：開銷小，加鎖快；不會出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高,并發(fā)度最低。

• 行級鎖：開銷大，加鎖慢；會出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低,并發(fā)度也最高。

• 頁面鎖：開銷和加鎖時間界于表鎖和行鎖之間；會出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般
  什么情況下會造成死鎖?

什么是死鎖？

死鎖:

oracle支持select for update no wait，表示如果拿不到鎖立刻報錯，而不是等待，MySQL就沒有no wait這個選項。

MySQL還有個問題是select for update語句執(zhí)行中所有掃描過的行都會被鎖上，這一點很容易造成問題。因此如果在MySQL中用悲觀鎖務必要確定走了索引，而不是全表掃描。

樂觀鎖（Optimistic Lock）:

1.樂觀鎖，也叫樂觀并發(fā)控制，它假設多用戶并發(fā)的事務在處理時不會彼此互相影響，各事務能夠在不產(chǎn)生鎖的情況下處理各自影響的那部分數(shù)據(jù)。在提交數(shù)據(jù)更新之前，每個事務會先檢查在該事務讀取數(shù)據(jù)后，有沒有其他事務又修改了該數(shù)據(jù)。如果其他事務有更新的話，那么當前正在提交的事務會進行回滾。

2.**樂觀鎖的特點先進行業(yè)務操作，不到萬不得已不去拿鎖。**即“樂觀”的認為拿鎖多半是會成功的，因此在進行完業(yè)務操作需要實際更新數(shù)據(jù)的最后一步再去拿一下鎖就好。
樂觀鎖在數(shù)據(jù)庫上的實現(xiàn)完全是邏輯的，不需要數(shù)據(jù)庫提供特殊的支持。

3.一般的做法是在需要鎖的數(shù)據(jù)上增加一個版本號，或者時間戳，

實現(xiàn)方式舉例如下：

樂觀鎖（給表加一個版本號字段）

SELECT data AS old_data, version AS old_version FROM …;

根據(jù)獲取的數(shù)據(jù)進行業(yè)務操作，得到new_data和new_version

UPDATE SET data = new_data, version = new_version WHERE version = old_version

if (updated row > 0) {

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

} else {

// 樂觀鎖獲取失敗，回滾并重試

}

注意：

• 樂觀鎖在不發(fā)生取鎖失敗的情況下開銷比悲觀鎖小，但是一旦發(fā)生失敗回滾開銷則比較大，因此適合用在取鎖失敗概率比較小的場景，可以提升系統(tǒng)并發(fā)性能

• 樂觀鎖還適用于一些比較特殊的場景，例如在業(yè)務操作過程中無法和數(shù)據(jù)庫保持連接等悲觀鎖無法適用的地方。

總結(jié)：
悲觀鎖和樂觀鎖是數(shù)據(jù)庫用來保證數(shù)據(jù)并發(fā)安全防止更新丟失的兩種方法，例子在select ... for update前加個事務就可以防止更新丟失。悲觀鎖和樂觀鎖大部分場景下差異不大，一些獨特場景下有一些差別，一般我們可以從如下幾個方面來判斷。

• 響應速度：

• 沖突頻率：

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

七、其他

---

1.數(shù)據(jù)庫的主從復制

主從復制的幾種方式:

同步復制:

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

異步復制:

如同AJAX請求一樣。master只需要完成自己的數(shù)據(jù)庫操作即可。至于slaves是否收到二進制日志，是否完成操作，不用關心,MySQL的默認設置。

半同步復制:

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

2.數(shù)據(jù)庫主從復制分析的 7 個問題?

問題1：master的寫操作，slaves被動的進行一樣的操作，保持數(shù)據(jù)一致性，那么slave是否可以主動的進行寫操作？

假設slave可以主動的進行寫操作，slave又無法通知master，這樣就導致了master和slave數(shù)據(jù)不一致了。因此slave不應該進行寫操作，至少是slave上涉及到復制的數(shù)據(jù)庫不可以寫。實際上，這里已經(jīng)揭示了讀寫分離的概念。

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

實現(xiàn)數(shù)據(jù)備份:
類似于高可用的功能，一旦master掛了，可以讓slave頂上去，同時slave提升為master。

異地容災:比如master在北京，地震掛了，那么在上海的slave還可以繼續(xù)。
主要用于實現(xiàn)scale out,分擔負載,可以將讀的任務分散到slaves上。
【很可能的情況是，一個系統(tǒng)的讀操作遠遠多于寫操作，因此寫操作發(fā)向master，讀操作發(fā)向slaves進行操作】

問題3：主從復制中有master,slave1,slave2,...等等這么多MySQL數(shù)據(jù)庫，那比如一個JAVA WEB應用到底應該連接哪個數(shù)據(jù)庫?

我們在應用程序中可以這樣，insert/delete/update這些更新數(shù)據(jù)庫的操作，用connection(for master)進行操作，

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

這樣的話，相當于應用程序完成了SQL語句的路由，而且與MySQL的主從復制架構(gòu)非常關聯(lián)，一旦master掛了，某些slave掛了，那么應用程序就要修改了。能不能讓應用程序與MySQL的主從復制架構(gòu)沒有什么太多關系呢？
找一個組件，application program只需要與它打交道，用它來完成MySQL的代理，實現(xiàn)SQL語句的路由。
MySQL proxy并不負責，怎么從眾多的slaves挑一個？可以交給另一個組件(比如haproxy)來完成。

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

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

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

問題5：當master的二進制日志每產(chǎn)生一個事件，都需要發(fā)往slave，如果我們有N個slave,那是發(fā)N次，還是只發(fā)一次？如果只發(fā)一次，發(fā)給了slave-1，那slave-2,slave-3,...它們怎么辦？

顯 然，應該發(fā)N次。實際上，在MySQL master內(nèi)部，維護N個線程，每一個線程負責將二進制日志文件發(fā)往對應的slave。master既要負責寫操作，還的維護N個線程，負擔會很重?？梢赃@樣，slave-1是master的從，slave-1又是slave-2,slave-3,...的主，同時slave-1不再負責select。slave-1將master的復制線程的負擔，轉(zhuǎn)移到自己的身上。這就是所謂的多級復制的概念。

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

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

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

scale on ?更好的服務器？沒有最好的，只有更好的，太貴了。。。
scale out ? 主從復制架構(gòu)已經(jīng)滿足不了。
可以分庫【垂直拆分】，分表【水平拆分】。

3.mysql 高并發(fā)環(huán)境解決方案?

MySQL 高并發(fā)環(huán)境解決方案： 分庫 分表 分布式 增加二級緩存。。。。。

需求分析：互聯(lián)網(wǎng)單位 每天大量數(shù)據(jù)讀取，寫入，并發(fā)性高。

現(xiàn)有解決方式：水平分庫分表，由單點分布到多點數(shù)據(jù)庫中，從而降低單點數(shù)據(jù)庫壓力。

集群方案：解決DB宕機帶來的單點DB不能訪問問題。

讀寫分離策略：極大限度提高了應用中Read數(shù)據(jù)的速度和并發(fā)量。無法解決高寫入壓力。

4.數(shù)據(jù)庫崩潰時事務的恢復機制（REDO日志和UNDO日志）?

轉(zhuǎn)載：MySQL REDO日志和UNDO日志

Undo Log:

Undo Log是為了實現(xiàn)事務的原子性，在MySQL數(shù)據(jù)庫InnoDB存儲引擎中，還用了Undo Log來實現(xiàn)多版本并發(fā)控制(簡稱：MVCC)。

事務的原子性(Atomicity)事務中的所有操作，要么全部完成，要么不做任何操作，不能只做部分操作。如果在執(zhí)行的過程中發(fā)生了錯誤，要回滾(Rollback)到事務開始前的狀態(tài)，就像這個事務從來沒有執(zhí)行過。
原理Undo Log的原理很簡單，為了滿足事務的原子性，在操作任何數(shù)據(jù)之前，首先將數(shù)據(jù)備份到一個地方（這個存儲數(shù)據(jù)備份的地方稱為UndoLog）。然后進行數(shù)據(jù)的修改。如果出現(xiàn)了錯誤或者用戶執(zhí)行了ROLLBACK語句，系統(tǒng)可以利用Undo Log中的備份將數(shù)據(jù)恢復到事務開始之前的狀態(tài)。

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

更新數(shù)據(jù)前記錄Undo log。
為了保證持久性，必須將數(shù)據(jù)在事務提交前寫到磁盤。只要事務成功提交，數(shù)據(jù)必然已經(jīng)持久化。
Undo log必須先于數(shù)據(jù)持久化到磁盤。如果在G,H之間系統(tǒng)崩潰，undo log是完整的， 可以用來回滾事務。
如果在A-F之間系統(tǒng)崩潰,因為數(shù)據(jù)沒有持久化到磁盤。所以磁盤上的數(shù)據(jù)還是保持在事務開始前的狀態(tài)。

缺陷：每個事務提交前將數(shù)據(jù)和Undo Log寫入磁盤，這樣會導致大量的磁盤IO，因此性能很低。
如果能夠?qū)?shù)據(jù)緩存一段時間，就能減少IO提高性能。但是這樣就會喪失事務的持久性。因此引入了另外一種機制來實現(xiàn)持久化，即Redo Log。

Redo Log:

原理和Undo Log相反，Redo Log記錄的是新數(shù)據(jù)的備份。在事務提交前，只要將Redo Log持久化即可，不需要將數(shù)據(jù)持久化。當系統(tǒng)崩潰時，雖然數(shù)據(jù)沒有持久化，但是Redo Log已經(jīng)持久化。系統(tǒng)可以根據(jù)Redo Log的內(nèi)容，將所有數(shù)據(jù)恢復到最新的狀態(tài)。

八、整理時參考的資料

---

java團長數(shù)據(jù)庫整理

20個數(shù)據(jù)庫常見面試題講解 - 鵬磊 - 開源中國"

34個數(shù)據(jù)庫常見面試題講解

漫談數(shù)據(jù)庫索引知識庫博客園

Mysql| 使用通配符進行模糊查詢(like,%,_)

MySQL存儲引擎中的MyISAM和InnoDB區(qū)別詳解

MySQL存儲引擎之MyISAM和Innodb總結(jié)性梳理

https://blog.csdn.net/liangxw1/article/details/51197560

本文名稱：兩萬字的數(shù)據(jù)庫面試題，不看絕對后悔
本文網(wǎng)址：http://bm7419.com/article34/jceipe.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供微信公眾號、標簽優(yōu)化、域名注冊、企業(yè)建站、App開發(fā)、營銷型網(wǎng)站建設

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)