一巩趁、基本概念

1.主鍵、外鍵淳附、超鍵议慰、候選鍵

超鍵：在關(guān)系中能唯一標(biāo)識(shí)元組的屬性集稱為關(guān)系模式的超鍵。一個(gè)屬性可以為作為一個(gè)超鍵奴曙，多個(gè)屬性組合在一起也可以作為一個(gè)超鍵别凹。超鍵包含候選鍵和主鍵。

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

主鍵：數(shù)據(jù)庫(kù)表中對(duì)儲(chǔ)存數(shù)據(jù)對(duì)象予以唯一和完整標(biāo)識(shí)的數(shù)據(jù)列或?qū)傩缘慕M合。一個(gè)數(shù)據(jù)列只能有一個(gè)主鍵脊框，且主鍵的取值不能缺失颁督，即不能為空值（Null）。

外鍵：在一個(gè)表中存在的另一個(gè)表的主鍵稱此表的外鍵浇雹。

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

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

如果沒有顯式定義主鍵昭灵，則InnoDB會(huì)選擇第一個(gè)不包含有NULL值的唯一索引作為主鍵索引吠裆、

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

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

如果表使用自增主鍵距贷，那么每次插入新的記錄，記錄就會(huì)順序添加到當(dāng)前索引節(jié)點(diǎn)的后續(xù)位置吻谋，當(dāng)一頁(yè)寫滿忠蝗，就會(huì)自動(dòng)開辟一個(gè)新的頁(yè)

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

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

觸發(fā)器是一種特殊的存儲(chǔ)過程，主要是通過事件來觸發(fā)而被執(zhí)行的矾芙。它可以強(qiáng)化約束舍沙，來維護(hù)數(shù)據(jù)的完整性和一致性，可以跟蹤數(shù)據(jù)庫(kù)內(nèi)的操作從而不允許未經(jīng)許可的更新和變化剔宪》髡。可以聯(lián)級(jí)運(yùn)算。如葱绒，某表上的觸發(fā)器上包含對(duì)另一個(gè)表的數(shù)據(jù)操作感帅，而該操作又會(huì)導(dǎo)致該表觸發(fā)器被觸發(fā)。

4.什么是存儲(chǔ)過程地淀？用什么來調(diào)用失球？

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

調(diào)用：

1）可以用一個(gè)命令對(duì)象來調(diào)用存儲(chǔ)過程聪轿。

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

5.存儲(chǔ)過程的優(yōu)缺點(diǎn)陆错？

優(yōu)點(diǎn)：

1）存儲(chǔ)過程是預(yù)編譯過的，執(zhí)行效率高金赦。

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

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

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

缺點(diǎn)：移植性差

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

image

7.什么叫視圖磨确？游標(biāo)是什么沽甥？

視圖：

是一種虛擬的表，具有和物理表相同的功能乏奥“谥郏可以對(duì)視圖進(jìn)行增，改邓了，查恨诱，操作，試圖通常是有一個(gè)表或者多個(gè)表的行或列的子集骗炉。對(duì)視圖的修改會(huì)影響基本表照宝。它使得我們獲取數(shù)據(jù)更容易，相比多表查詢句葵。

游標(biāo)：

是對(duì)查詢出來的結(jié)果集作為一個(gè)單元來有效的處理厕鹃。游標(biāo)可以定在該單元中的特定行，從結(jié)果集的當(dāng)前行檢索一行或多行乍丈〖敛辏可以對(duì)結(jié)果集當(dāng)前行做修改。一般不使用游標(biāo)轻专，但是需要逐條處理數(shù)據(jù)的時(shí)候忆矛，游標(biāo)顯得十分重要。

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

優(yōu)點(diǎn)：

1對(duì)數(shù)據(jù)庫(kù)的訪問请垛，因?yàn)橐晥D可以有選擇性的選取數(shù)據(jù)庫(kù)里的一部分洪碳。

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

3)維護(hù)數(shù)據(jù)的獨(dú)立性，試圖可從多個(gè)表檢索數(shù)據(jù)瞳腌。

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

缺點(diǎn)：

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

9.drop挑宠、truncate菲盾、 delete區(qū)別

最基本：

• drop直接刪掉表。

• truncate刪除表中數(shù)據(jù)各淀，再插入時(shí)自增長(zhǎng)id又從1開始懒鉴。

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

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

（2） 表和索引所占空間抄课。當(dāng)表被TRUNCATE 后，這個(gè)表和索引所占用的空間會(huì)恢復(fù)到初始大小雳旅，而DELETE操作不會(huì)減少表或索引所占用的空間跟磨。drop語(yǔ)句將表所占用的空間全釋放掉。

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

（4） 應(yīng)用范圍吱晒。TRUNCATE 只能對(duì)TABLE；DELETE可以是table和view

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

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

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

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

（9） 在沒有備份情況下般妙，謹(jǐn)慎使用 drop 與 truncate。要?jiǎng)h除部分?jǐn)?shù)據(jù)行采用delete且注意結(jié)合where來約束影響范圍相速〉欤回滾段要足夠大。要?jiǎng)h除表用drop;若想保留表而將表中數(shù)據(jù)刪除突诬，如果于事務(wù)無(wú)關(guān)苫拍，用truncate即可實(shí)現(xiàn)。如果和事務(wù)有關(guān)旺隙，或老師想觸發(fā)trigger,還是用delete绒极。

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

（11） TRUNCATE TABLE 刪除表中的所有行速妖，但表結(jié)構(gòu)及其列、約束聪黎、索引等保持不變罕容。新行標(biāo)識(shí)所用的計(jì)數(shù)值重置為該列的種子。如果想保留標(biāo)識(shí)計(jì)數(shù)值稿饰，請(qǐng)改用 DELETE锦秒。如果要?jiǎng)h除表定義及其數(shù)據(jù)，請(qǐng)使用 DROP TABLE 語(yǔ)句喉镰。

（12） 對(duì)于由 FOREIGN KEY 約束引用的表旅择，不能使用 TRUNCATE TABLE，而應(yīng)使用不帶 WHERE 子句的 DELETE 語(yǔ)句侣姆。由于 TRUNCATE TABLE 不記錄在日志中生真，所以它不能激活觸發(fā)器。

10.什么是臨時(shí)表捺宗，臨時(shí)表什么時(shí)候刪除?

臨時(shí)表可以手動(dòng)刪除：
DROP TEMPORARY TABLE IF EXISTS temp_tb;

臨時(shí)表只在當(dāng)前連接可見柱蟀，當(dāng)關(guān)閉連接時(shí)，MySQL會(huì)自動(dòng)刪除表并釋放所有空間蚜厉。因此在不同的連接中可以創(chuàng)建同名的臨時(shí)表长已，并且操作屬于本連接的臨時(shí)表。
創(chuàng)建臨時(shí)表的語(yǔ)法與創(chuàng)建表語(yǔ)法類似，不同之處是增加關(guān)鍵字TEMPORARY术瓮，

如：

CREATE TEMPORARY TABLE tmp_table (

NAME VARCHAR (10) NOT NULL,

time date NOT NULL
);

select * from tmp_table;

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

非關(guān)系型數(shù)據(jù)庫(kù)的優(yōu)勢(shì)：

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

• 可擴(kuò)展性：同樣也是因?yàn)榛阪I值對(duì)撬讽，數(shù)據(jù)之間沒有耦合性蕊连，所以非常容易水平擴(kuò)展。

關(guān)系型數(shù)據(jù)庫(kù)的優(yōu)勢(shì)：

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

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

其他：

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

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

3.對(duì)于事務(wù)的支持也可以用一些系統(tǒng)級(jí)的原子操作來實(shí)現(xiàn)例如樂觀鎖之類的方法來曲線救國(guó)囚聚，比如Redis set nx。

12.數(shù)據(jù)庫(kù)范式标锄，根據(jù)某個(gè)場(chǎng)景設(shè)計(jì)數(shù)據(jù)表?

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

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

第二范式:(確保表中的每列都和主鍵相關(guān))在一個(gè)數(shù)據(jù)庫(kù)表中汗捡，一個(gè)表中只能保存一種數(shù)據(jù)淑际，不可以把多種數(shù)據(jù)保存在同一張數(shù)據(jù)庫(kù)表中畏纲。

第二范式在第一范式的基礎(chǔ)之上更進(jìn)一層。第二范式需要確保數(shù)據(jù)庫(kù)表中的每一列都和主鍵相關(guān)春缕，而不能只與主鍵的某一部分相關(guān)（主要針對(duì)聯(lián)合主鍵而言）盗胀。也就是說在一個(gè)數(shù)據(jù)庫(kù)表中，一個(gè)表中只能保存一種數(shù)據(jù)锄贼，不可以把多種數(shù)據(jù)保存在同一張數(shù)據(jù)庫(kù)表中票灰。
比如要設(shè)計(jì)一個(gè)訂單信息表，因?yàn)橛唵沃锌赡軙?huì)有多種商品宅荤，所以要將訂單編號(hào)和商品編號(hào)作為數(shù)據(jù)庫(kù)表的聯(lián)合主鍵屑迂。

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

第三范式需要確保數(shù)據(jù)表中的每一列數(shù)據(jù)都和主鍵直接相關(guān)惹盼，而不能間接相關(guān)。
比如在設(shè)計(jì)一個(gè)訂單數(shù)據(jù)表的時(shí)候惫确，可以將客戶編號(hào)作為一個(gè)外鍵和訂單表建立相應(yīng)的關(guān)系手报。而不可以在訂單表中添加關(guān)于客戶其它信息（比如姓名、所屬公司等）的字段改化。

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

若關(guān)系模式屬于第二范式揍鸟，且每個(gè)屬性都不傳遞依賴于鍵碼，則R屬于BC范式句旱。
通常BC范式的條件有多種等價(jià)的表述：每個(gè)非平凡依賴的左邊必須包含鍵碼阳藻；每個(gè)決定因素必須包含鍵碼。
BC范式既檢查非主屬性前翎，又檢查主屬性稚配。當(dāng)只檢查非主屬性時(shí)畅涂，就成了第三范式港华。滿足BC范式的關(guān)系都必然滿足第三范式。
還可以這么說：若一個(gè)關(guān)系達(dá)到了第三范式午衰，并且它只有一個(gè)候選碼立宜，或者它的每個(gè)候選碼都是單屬性，則該關(guān)系自然達(dá)到BC范式臊岸。
一般橙数，一個(gè)數(shù)據(jù)庫(kù)設(shè)計(jì)符合3NF或BCNF就可以了。

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

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

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

內(nèi)連接: 只連接匹配的行

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

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

例如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

全外連接: 包含左腻贰、右兩個(gè)表的全部行吁恍，不管另外一邊的表中是否存在與它們匹配的行。

交叉連接: 生成笛卡爾積－它不使用任何匹配或者選取條件播演，而是直接將一個(gè)數(shù)據(jù)源中的每個(gè)行與另一個(gè)數(shù)據(jù)源的每個(gè)行都一一匹配

例如：
SELECT type,pub_name FROM titles CROSS JOIN publishers ORDER BY type

注意：

很多公司都只是考察是否知道其概念冀瓦，但是也有很多公司需要不僅僅知道概念，還需要?jiǎng)邮謱憇ql,一般都是簡(jiǎn)單的連接查詢写烤，具體關(guān)于連接查詢的sql練習(xí)翼闽，參見以下鏈接：

牛客網(wǎng)數(shù)據(jù)庫(kù)SQL實(shí)戰(zhàn)

leetcode中文網(wǎng)站數(shù)據(jù)庫(kù)練習(xí)

我的另一篇文章顶霞，常用sql練習(xí)50題

14.varchar和char的使用場(chǎng)景?

1.char的長(zhǎng)度是不可變的肄程，而varchar的長(zhǎng)度是可變的。

定義一個(gè)char[10]和varchar[10]选浑。
如果存進(jìn)去的是‘csdn’,那么char所占的長(zhǎng)度依然為10蓝厌，除了字符‘csdn’外，后面跟六個(gè)空格古徒，varchar就立馬把長(zhǎng)度變?yōu)?了拓提，取數(shù)據(jù)的時(shí)候，char類型的要用trim()去掉多余的空格隧膘，而varchar是不需要的代态。

2.char的存取數(shù)度還是要比varchar要快得多糠雨，因?yàn)槠溟L(zhǎng)度固定甫菠，方便程序的存儲(chǔ)與查找。
char也為此付出的是空間的代價(jià)思犁，因?yàn)槠溟L(zhǎng)度固定萨驶，所以難免會(huì)有多余的空格占位符占據(jù)空間歉摧，可謂是以空間換取時(shí)間效率。
varchar是以空間效率為首位腔呜。

3.char的存儲(chǔ)方式是：對(duì)英文字符（ASCII）占用1個(gè)字節(jié)叁温，對(duì)一個(gè)漢字占用兩個(gè)字節(jié)。
varchar的存儲(chǔ)方式是：對(duì)每個(gè)英文字符占用2個(gè)字節(jié)核畴，漢字也占用2個(gè)字節(jié)膝但。

4.兩者的存儲(chǔ)數(shù)據(jù)都非unicode的字符數(shù)據(jù)。

15.SQL語(yǔ)言分類

SQL語(yǔ)言共分為四大類：

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

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

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

• 數(shù)據(jù)控制語(yǔ)言DCL谤草。

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

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

SELECT
FROM
WHERE

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

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

1. 插入：INSERT

2. 更新：UPDATE

3. 刪除：DELETE

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

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

表 視圖 索引 同義詞 簇

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

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

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

1. GRANT：授權(quán)古拴。

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

3. COMMIT [WORK]：提交盔然。

在數(shù)據(jù)庫(kù)的插入桅打、刪除和修改操作時(shí)，只有當(dāng)事務(wù)在提交到數(shù)據(jù)
庫(kù)時(shí)才算完成愈案。在事務(wù)提交前挺尾，只有操作數(shù)據(jù)庫(kù)的這個(gè)人才能有權(quán)看
到所做的事情，別人只有在最后提交完成后才可以看到站绪。
提交數(shù)據(jù)有三種類型：顯式提交遭铺、隱式提交及自動(dòng)提交。下面分
別說明這三種類型恢准。

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

(2) 隱式提交
用SQL命令間接完成的提交為隱式提交馁筐。這些命令是：
ALTER涂召，AUDIT，COMMENT敏沉，CONNECT果正，CREATE，DISCONNECT赦抖，DROP舱卡，
EXIT辅肾，GRANT队萤，NOAUDIT，QUIT矫钓，REVOKE要尔，RENAME舍杜。

(3) 自動(dòng)提交
若把AUTOCOMMIT設(shè)置為ON，則在插入赵辕、修改既绩、刪除語(yǔ)句執(zhí)行后，
系統(tǒng)將自動(dòng)進(jìn)行提交还惠，這就是自動(dòng)提交饲握。其格式為：
SQL>SET AUTOCOMMIT ON；

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

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

通配符的分類:

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

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

like操作符: LIKE作用是指示mysql后面的搜索模式是利用通配符而不是直接相等匹配進(jìn)行比較.

注意: 如果在使用like操作符時(shí),后面的沒有使用通用匹配符效果是和=一致的,SELECT * FROM products WHERE products.prod_name like '1000';
只能匹配的結(jié)果為1000,而不能匹配像JetPack 1000這樣的結(jié)果.

• %通配符使用: 匹配以"yves"開頭的記錄:(包括記錄"yves") SELECT FROM products WHERE products.prod_name like 'yves%';
  匹配包含"yves"的記錄(包括記錄"yves") SELECT FROM products WHERE products.prod_name like '%yves%';
  匹配以"yves"結(jié)尾的記錄(包括記錄"yves",不包括記錄"yves ",也就是yves后面有空格的記錄,這里需要注意) SELECT * FROM products WHERE products.prod_name like '%yves';

• 通配符使用: SELECT FROM products WHERE products.prod_name like '_yves'; 匹配結(jié)果為: 像"yyves"這樣記錄.
  SELECT FROM products WHERE products.prodname like 'yves**'; 匹配結(jié)果為: 像"yvesHe"這樣的記錄.(一個(gè)下劃線只能匹配一個(gè)字符,不能多也不能少)

注意事項(xiàng):

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

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

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

技巧與建議:

正如所見蚕键， MySQL的通配符很有用救欧。但這種功能是有代價(jià)的：通配符搜索的處理一般要比前面討論的其他搜索所花時(shí)間更長(zhǎng)。這里給出一些使用通配符要記住的技巧锣光。

• 不要過度使用通配符笆怠。如果其他操作符能達(dá)到相同的目的，應(yīng)該 使用其他操作符誊爹。

• 在確實(shí)需要使用通配符時(shí)蹬刷，除非絕對(duì)有必要，否則不要把它們用 在搜索模式的開始處频丘。把通配符置于搜索模式的開始處办成，搜索起 來是最慢的。

• 仔細(xì)注意通配符的位置搂漠。如果放錯(cuò)地方诈火，可能不會(huì)返回想要的數(shù).

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

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

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

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

• count()還有一種使用方式,count(1)這個(gè)用法和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(*)快。

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

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

18.最左前綴原則

多列索引：

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

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

注：在mysql中執(zhí)行查詢時(shí)，只能使用一個(gè)索引宿接，如果我們?cè)趌name,fname,age上分別建索引,執(zhí)行查詢時(shí)赘淮，只能使用一個(gè)索引辕录，mysql會(huì)選擇一個(gè)最嚴(yán)格(獲得結(jié)果集記錄數(shù)最少)的索引。

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

二蚣旱、索引

1.什么是索引？

何為索引：

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

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

2.索引的作用赋荆？它的優(yōu)點(diǎn)缺點(diǎn)是什么笋妥？

索引作用：

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

為表設(shè)置索引要付出代價(jià)的：

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

• 二是在插入和修改數(shù)據(jù)時(shí)要花費(fèi)較多的時(shí)間(因?yàn)樗饕惨S之變動(dòng))春宣。

#### **3.索引的優(yōu)缺點(diǎn)？**

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

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

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

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

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

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

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

1.創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間刑然，這種時(shí)間隨著數(shù)據(jù)量的增加而增加寺擂。

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

3.當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加爽雄、刪除和修改的時(shí)候，索引也要?jiǎng)討B(tài)的維護(hù)沐鼠，這樣就降低了數(shù)據(jù)的維護(hù)速度挚瘟。

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

索引是建立在數(shù)據(jù)庫(kù)表中的某些列的上面乘盖。在創(chuàng)建索引的時(shí)候，應(yīng)該考慮在哪些列上可以創(chuàng)建索引憔涉，在哪些列上不能創(chuàng)建索引订框。

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

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

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

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

（4）在經(jīng)常需要根據(jù)范圍進(jìn)行搜索的列上創(chuàng)建索引履羞，因?yàn)樗饕呀?jīng)排序，其指定的范圍是連續(xù)的屡久；

（5）在經(jīng)常需要排序的列上創(chuàng)建索引忆首，因?yàn)樗饕呀?jīng)排序，這樣查詢可以利用索引的排序被环，加快排序查詢時(shí)間雄卷；

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

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

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

這是因?yàn)椋热贿@些列很少使用到悴能，因此有索引或者無(wú)索引揣钦，并不能提高查詢速度。相反漠酿，由于增加了索引冯凹，反而降低了系統(tǒng)的維護(hù)速度和增大了空間需求。

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

這是因?yàn)橛钜Γ捎谶@些列的取值很少匈庭，例如人事表的性別列，在查詢的結(jié)果中浑劳，結(jié)果集的數(shù)據(jù)行占了表中數(shù)據(jù)行的很大比例阱持，即需要在表中搜索的數(shù)據(jù)行的比例很大。增加索引魔熏，并不能明顯加快檢索速度衷咽。

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

這是因?yàn)樗庹溃@些列的數(shù)據(jù)量要么相當(dāng)大镶骗，要么取值很少。

(4)當(dāng)修改性能遠(yuǎn)遠(yuǎn)大于檢索性能時(shí)躲雅，不應(yīng)該創(chuàng)建索引鼎姊。

這是因?yàn)椋薷男阅芎蜋z索性能是互相矛盾的相赁。當(dāng)增加索引時(shí)此蜈，會(huì)提高檢索性能，但是會(huì)降低修改性能噪生。當(dāng)減少索引時(shí)裆赵，會(huì)提高修改性能，降低檢索性能跺嗽。因此战授，當(dāng)修改性能遠(yuǎn)遠(yuǎn)大于檢索性能時(shí)，不應(yīng)該創(chuàng)建索引桨嫁。

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

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

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

Hash索引和B+樹索引的特點(diǎn)：

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

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

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

1. Hash索引僅僅能滿足"=","IN"和""查詢筒繁，不能使用范圍查詢,因?yàn)榻?jīng)過相應(yīng)的Hash算法處理之后的Hash值的大小關(guān)系，并不能保證和Hash運(yùn)算前完全一樣巴元；

1. Hash索引無(wú)法被用來避免數(shù)據(jù)的排序操作毡咏，因?yàn)镠ash值的大小關(guān)系并不一定和Hash運(yùn)算前的鍵值完全一樣；

1. Hash索引不能利用部分索引鍵查詢逮刨，對(duì)于組合索引呕缭，Hash索引在計(jì)算Hash值的時(shí)候是組合索引鍵合并后再一起計(jì)算Hash值，而不是單獨(dú)計(jì)算Hash值，所以通過組合索引的前面一個(gè)或幾個(gè)索引鍵進(jìn)行查詢的時(shí)候恢总，Hash索引也無(wú)法被利用迎罗；

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

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

補(bǔ)充：

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

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

3.如果是等值查詢球恤，那么哈希索引明顯有絕對(duì)優(yōu)勢(shì)，因?yàn)橹恍枰?jīng)過一次算法即可找到相應(yīng)的鍵值荸镊；當(dāng)然了咽斧，這個(gè)前提是，鍵值都是唯一的躬存。如果鍵值不是唯一的张惹，就需要先找到該鍵所在位置，然后再根據(jù)鏈表往后掃描岭洲，直到找到相應(yīng)的數(shù)據(jù)宛逗；

4.如果是范圍查詢檢索，這時(shí)候哈希索引就毫無(wú)用武之地了盾剩，因?yàn)樵仁怯行虻逆I值雷激，經(jīng)過哈希算法后，有可能變成不連續(xù)的了告私，就沒辦法再利用索引完成范圍查詢檢索侥锦；
同理，哈希索引沒辦法利用索引完成排序德挣，以及l(fā)ike ‘xxx%’ 這樣的部分模糊查詢（這種部分模糊查詢恭垦，其實(shí)本質(zhì)上也是范圍查詢）；

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

6.B+樹索引的關(guān)鍵字檢索效率比較平均番挺，不像B樹那樣波動(dòng)幅度大唠帝，在有大量重復(fù)鍵值情況下，哈希索引的效率也是極低的玄柏，因?yàn)榇嬖谒^的哈希碰撞問題襟衰。

7.在大多數(shù)場(chǎng)景下，都會(huì)有范圍查詢粪摘、排序瀑晒、分組等查詢特征，用B+樹索引就可以了徘意。

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

1. B樹苔悦，每個(gè)節(jié)點(diǎn)都存儲(chǔ)key和data，所有節(jié)點(diǎn)組成這棵樹椎咧，并且葉子節(jié)點(diǎn)指針為nul玖详，葉子結(jié)點(diǎn)不包含任何關(guān)鍵字信息

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

   
   
   image

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

1.B+的磁盤讀寫代價(jià)更低

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

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

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

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

聚合索引(clustered index):

聚集索引表記錄的排列順序和索引的排列順序一致匾竿，所以查詢效率快瓦宜，只要找到第一個(gè)索引值記錄，其余就連續(xù)性的記錄在物理也一樣連續(xù)存放岭妖。聚集索引對(duì)應(yīng)的缺點(diǎn)就是修改慢临庇，因?yàn)闉榱吮ＷC表中記錄的物理和索引順序一致反璃，在記錄插入的時(shí)候，會(huì)對(duì)數(shù)據(jù)頁(yè)重新排序假夺。
聚集索引類似于新華字典中用拼音去查找漢字淮蜈，拼音檢索表于書記順序都是按照a~z排列的，就像相同的邏輯順序于物理順序一樣已卷，當(dāng)你需要查找a,ai兩個(gè)讀音的字梧田，或是想一次尋找多個(gè)傻(sha)的同音字時(shí)，也許向后翻幾頁(yè)侧蘸，或緊接著下一行就得到結(jié)果了裁眯。

非聚合索引(nonclustered index):

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

根本區(qū)別：

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

三踩官、事務(wù)

1.什么是事務(wù)却桶？

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

2.事務(wù)四大特性（ACID）原子性颖系、一致性、隔離性辩越、持久性?

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

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

隔離性（Isolation）:
隔離性是當(dāng)多個(gè)用戶并發(fā)訪問數(shù)據(jù)庫(kù)時(shí)蛤签，比如操作同一張表時(shí)辞友，數(shù)據(jù)庫(kù)為每一個(gè)用戶開啟的事務(wù)，不能被其他事務(wù)的操作所干擾震肮，多個(gè)并發(fā)事務(wù)之間要相互隔離称龙。同一時(shí)間，只允許一個(gè)事務(wù)請(qǐng)求同一數(shù)據(jù)戳晌，不同的事務(wù)之間彼此沒有任何干擾鲫尊。比如A正在從一張銀行卡中取錢，在A取錢的過程結(jié)束前沦偎，B不能向這張卡轉(zhuǎn)賬疫向。

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

3.事務(wù)的并發(fā)?事務(wù)隔離級(jí)別，每個(gè)級(jí)別會(huì)引發(fā)什么問題侈询，MySQL默認(rèn)是哪個(gè)級(jí)別?

從理論上來說, 事務(wù)應(yīng)該彼此完全隔離, 以避免并發(fā)事務(wù)所導(dǎo)致的問題舌涨，然而, 那樣會(huì)對(duì)性能產(chǎn)生極大的影響, 因?yàn)槭聞?wù)必須按順序運(yùn)行， 在實(shí)際開發(fā)中, 為了提升性能, 事務(wù)會(huì)以較低的隔離級(jí)別運(yùn)行扔字， 事務(wù)的隔離級(jí)別可以通過隔離事務(wù)屬性指定囊嘉。
事務(wù)的并發(fā)問題

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

2、不可重復(fù)讀：事務(wù) A 多次讀取同一數(shù)據(jù)震檩，事務(wù) B 在事務(wù)A多次讀取的過程中琢蛤，對(duì)數(shù)據(jù)作了更新并提交，導(dǎo)致事務(wù)A多次讀取同一數(shù)據(jù)時(shí)恳蹲，結(jié)果因此本事務(wù)先后兩次讀到的數(shù)據(jù)結(jié)果會(huì)不一致虐块。

3史飞、幻讀：幻讀解決了不重復(fù)讀当凡，保證了同一個(gè)事務(wù)里衫画，查詢的結(jié)果都是事務(wù)開始時(shí)的狀態(tài)（一致性）叹洲。

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

事務(wù)的隔離級(jí)別

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

不可重復(fù)讀：事務(wù) A 多次讀取同一數(shù)據(jù)死遭，事務(wù) B 在事務(wù)A多次讀取的過程中广恢，對(duì)數(shù)據(jù)作了更新并提交，導(dǎo)致事務(wù)A多次讀取同一數(shù)據(jù)時(shí)呀潭，結(jié)果因此本事務(wù)先后兩次讀到的數(shù)據(jù)結(jié)果會(huì)不一致钉迷。

可重復(fù)讀：在同一個(gè)事務(wù)里，SELECT的結(jié)果是事務(wù)開始時(shí)時(shí)間點(diǎn)的狀態(tài)钠署，因此糠聪，同樣的SELECT操作讀到的結(jié)果會(huì)是一致的。但是踏幻，會(huì)有幻讀現(xiàn)象

串行化：最高的隔離級(jí)別枷颊，在這個(gè)隔離級(jí)別下戳杀，不會(huì)產(chǎn)生任何異常该面。并發(fā)的事務(wù)，就像事務(wù)是在一個(gè)個(gè)按照順序執(zhí)行一樣

特別注意：

MySQL默認(rèn)的事務(wù)隔離級(jí)別為repeatable-read

MySQL 支持 4 中事務(wù)隔離級(jí)別.

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

Oracle 支持的 2 種事務(wù)隔離級(jí)別：READ_COMMITED , SERIALIZABLE

SQL規(guī)范所規(guī)定的標(biāo)準(zhǔn)信卡，不同的數(shù)據(jù)庫(kù)具體的實(shí)現(xiàn)可能會(huì)有些差異

MySQL中默認(rèn)事務(wù)隔離級(jí)別是“可重復(fù)讀”時(shí)并不會(huì)鎖住讀取到的行

事務(wù)隔離級(jí)別：未提交讀時(shí)隔缀，寫數(shù)據(jù)只會(huì)鎖住相應(yīng)的行。

事務(wù)隔離級(jí)別為：可重復(fù)讀時(shí)傍菇，寫數(shù)據(jù)會(huì)鎖住整張表猾瘸。

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

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

4.事務(wù)傳播行為

1.PROPAGATION_REQUIRED：如果當(dāng)前沒有事務(wù)卢未，就創(chuàng)建一個(gè)新事務(wù)肪凛，如果當(dāng)前存在事務(wù)，就加入該事務(wù)辽社，該設(shè)置是最常用的設(shè)置显拜。

2.PROPAGATION_SUPPORTS：支持當(dāng)前事務(wù)，如果當(dāng)前存在事務(wù)爹袁，就加入該事務(wù)远荠，如果當(dāng)前不存在事務(wù)，就以非事務(wù)執(zhí)行失息。

3.PROPAGATION_MANDATORY：支持當(dāng)前事務(wù)譬淳，如果當(dāng)前存在事務(wù)，就加入該事務(wù)盹兢，如果當(dāng)前不存在事務(wù)邻梆，就拋出異常。

4.PROPAGATION_REQUIRES_NEW：創(chuàng)建新事務(wù)绎秒，無(wú)論當(dāng)前存不存在事務(wù)浦妄，都創(chuàng)建新事務(wù)。

5.PROPAGATION_NOT_SUPPORTED：以非事務(wù)方式執(zhí)行操作见芹，如果當(dāng)前存在事務(wù)剂娄，就把當(dāng)前事務(wù)掛起。

6.PROPAGATION_NEVER：以非事務(wù)方式執(zhí)行玄呛，如果當(dāng)前存在事務(wù)阅懦，則拋出異常。

7.PROPAGATION_NESTED：如果當(dāng)前存在事務(wù)徘铝，則在嵌套事務(wù)內(nèi)執(zhí)行耳胎。如果當(dāng)前沒有事務(wù)，則執(zhí)行與PROPAGATION_REQUIRED類似的操作惕它。

5.嵌套事務(wù)

什么是嵌套事務(wù)怕午？

嵌套是子事務(wù)套在父事務(wù)中執(zhí)行，子事務(wù)是父事務(wù)的一部分淹魄，在進(jìn)入子事務(wù)之前郁惜，父事務(wù)建立一個(gè)回滾點(diǎn)，叫save point揭北，然后執(zhí)行子事務(wù)扳炬，這個(gè)子事務(wù)的執(zhí)行也算是父事務(wù)的一部分吏颖，然后子事務(wù)執(zhí)行結(jié)束，父事務(wù)繼續(xù)執(zhí)行恨樟。重點(diǎn)就在于那個(gè)save point半醉。看幾個(gè)問題就明了了：

如果子事務(wù)回滾劝术，會(huì)發(fā)生什么缩多？

父事務(wù)會(huì)回滾到進(jìn)入子事務(wù)前建立的save point，然后嘗試其他的事務(wù)或者其他的業(yè)務(wù)邏輯养晋，父事務(wù)之前的操作不會(huì)受到影響衬吆，更不會(huì)自動(dòng)回滾。

如果父事務(wù)回滾绳泉，會(huì)發(fā)生什么逊抡？

父事務(wù)回滾，子事務(wù)也會(huì)跟著回滾零酪！為什么呢冒嫡，因?yàn)楦甘聞?wù)結(jié)束之前，子事務(wù)是不會(huì)提交的四苇，我們說子事務(wù)是父事務(wù)的一部分孝凌，正是這個(gè)道理。那么：

事務(wù)的提交月腋，是什么情況蟀架？

是父事務(wù)先提交，然后子事務(wù)提交榆骚，還是子事務(wù)先提交片拍，父事務(wù)再提交？答案是第二種情況寨躁，還是那句話穆碎，子事務(wù)是父事務(wù)的一部分，由父事務(wù)統(tǒng)一提交职恳。

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

四、存儲(chǔ)引擎

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

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

1.InnoDB支持事務(wù)，MyISAM不支持恭金， 這一點(diǎn)是非常之重要操禀。事務(wù)是一種高級(jí)的處理方式，如在一些列增刪改中只要哪個(gè)出錯(cuò)還可以回滾還原横腿，而MyISAM就不可以了颓屑。

2.MyISAM適合查詢以及插入為主的應(yīng)用斤寂。

3.InnoDB適合頻繁修改以及涉及到安全性較高的應(yīng)用。

4.InnoDB支持外鍵揪惦，MyISAM不支持遍搞。

5.從MySQL5.5.5以后，InnoDB是默認(rèn)引擎器腋。

6.InnoDB不支持FULLTEXT類型的索引溪猿。

7.InnoDB中不保存表的行數(shù)，如select count() from table時(shí)纫塌，InnoDB需要掃描一遍整個(gè)表來計(jì)算有多少行诊县，但是MyISAM只要簡(jiǎn)單的讀出保存好的行數(shù)即可。注意的是措左，當(dāng)count()語(yǔ)句包含where條件時(shí)MyISAM也需要掃描整個(gè)表依痊。

8.對(duì)于自增長(zhǎng)的字段，InnoDB中必須包含只有該字段的索引怎披，但是在MyISAM表中可以和其他字段一起建立聯(lián)合索引抗悍。

9.DELETE FROM table時(shí)，InnoDB不會(huì)重新建立表钳枕，而是一行一行的 刪除缴渊，效率非常慢。MyISAM則會(huì)重建表鱼炒。

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

2.MySQL存儲(chǔ)引擎MyISAM與InnoDB如何選擇

MySQL有多種存儲(chǔ)引擎昔瞧，每種存儲(chǔ)引擎有各自的優(yōu)缺點(diǎn)指蚁，可以擇優(yōu)選擇使用：MyISAM、InnoDB自晰、MERGE凝化、MEMORY(HEAP)、BDB(BerkeleyDB)酬荞、EXAMPLE搓劫、FEDERATED、ARCHIVE混巧、CSV枪向、BLACKHOLE。

雖然MySQL里的存儲(chǔ)引擎不只是MyISAM與InnoDB這兩個(gè)咧党，但常用的就是兩個(gè)秘蛔。
關(guān)于MySQL數(shù)據(jù)庫(kù)提供的兩種存儲(chǔ)引擎，MyISAM與InnoDB選擇使用：

• 1.INNODB會(huì)支持一些關(guān)系數(shù)據(jù)庫(kù)的高級(jí)功能，如事務(wù)功能和行級(jí)鎖深员，MyISAM不支持负蠕。

• 2.MyISAM的性能更優(yōu)，占用的存儲(chǔ)空間少倦畅，所以遮糖，選擇何種存儲(chǔ)引擎，視具體應(yīng)用而定滔迈。

如果你的應(yīng)用程序一定要使用事務(wù)止吁，毫無(wú)疑問你要選擇INNODB引擎。但要注意燎悍，INNODB的行級(jí)鎖是有條件的敬惦。在where條件沒有使用主鍵時(shí)，照樣會(huì)鎖全表谈山。比如DELETE FROM mytable這樣的刪除語(yǔ)句俄删。

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

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

現(xiàn)在一般都是選用innodb了，主要是MyISAM的全表鎖氛濒，讀寫串行問題产场，并發(fā)效率鎖表，效率低泼橘，MyISAM對(duì)于讀寫密集型應(yīng)用一般是不會(huì)去選用的涝动。
MEMORY存儲(chǔ)引擎

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

MEMORY默認(rèn)使用哈希索引馍惹。速度比使用B型樹索引快。當(dāng)然如果你想用B型樹索引玛界，可以在創(chuàng)建索引時(shí)指定万矾。

注意，MEMORY用到的很少慎框，因?yàn)樗前褦?shù)據(jù)存到內(nèi)存中良狈，如果內(nèi)存出現(xiàn)異常就會(huì)影響數(shù)據(jù)。如果重啟或者關(guān)機(jī)笨枯，所有數(shù)據(jù)都會(huì)消失薪丁。因此，基于MEMORY的表的生命周期很短猎醇，一般是一次性的窥突。

3.MySQL的MyISAM與InnoDB兩種存儲(chǔ)引擎在，事務(wù)硫嘶、鎖級(jí)別阻问，各自的適用場(chǎng)景?

事務(wù)處理上方面

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

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

鎖級(jí)別

• MyISAM：只支持表級(jí)鎖忆畅，用戶在操作MyISAM表時(shí)衡未，select，update，delete缓醋，insert語(yǔ)句都會(huì)給表自動(dòng)加鎖如失，如果加鎖以后的表滿足insert并發(fā)的情況下，可以在表的尾部插入新的數(shù)據(jù)送粱。

• InnoDB：支持事務(wù)和行級(jí)鎖褪贵，是innodb的最大特色。行鎖大幅度提高了多用戶并發(fā)操作的新能抗俄。但是InnoDB的行鎖脆丁，只是在WHERE的主鍵是有效的，非主鍵的WHERE都會(huì)鎖全表的动雹。

關(guān)于存儲(chǔ)引擎MyISAM和InnoDB的其他參考資料如下：

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

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

五槽卫、優(yōu)化

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

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

其中select和from是必須的丐怯，其他關(guān)鍵詞是可選的，這六個(gè)關(guān)鍵詞的執(zhí)行順序 與sql語(yǔ)句的書寫順序并不是一樣的翔横，而是按照下面的順序來執(zhí)行

from:需要從哪個(gè)數(shù)據(jù)表檢索數(shù)據(jù)

where:過濾表中數(shù)據(jù)的條件

group by:如何將上面過濾出的數(shù)據(jù)分組

having:對(duì)上面已經(jīng)分組的數(shù)據(jù)進(jìn)行過濾的條件

select:查看結(jié)果集中的哪個(gè)列读跷，或列的計(jì)算結(jié)果

order by :按照什么樣的順序來查看返回的數(shù)據(jù)

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

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

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

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

對(duì)于復(fù)雜丐枉、效率低的sql語(yǔ)句，我們通常是使用explain sql 來分析sql語(yǔ)句掘托，這個(gè)語(yǔ)句可以打印出瘦锹，語(yǔ)句的執(zhí)行。這樣方便我們分析闪盔，進(jìn)行優(yōu)化

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

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

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

index: index scan; index 和 all的區(qū)別在于index類型只遍歷索引头岔；

range：索引范圍掃描，對(duì)索引的掃描開始于某一點(diǎn)贴妻，返回匹配值的行切油，常見與between 蝙斜，等查詢名惩；

ref：非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值的所有行孕荠，常見于使用非唯一索引即唯一索引的非唯一前綴進(jìn)行查找娩鹉；

eq_ref：唯一性索引掃描，對(duì)于每個(gè)索引鍵稚伍，表中只有一條記錄與之匹配弯予，常用于主鍵或者唯一索引掃描；

const个曙，system：當(dāng)MySQL對(duì)某查詢某部分進(jìn)行優(yōu)化锈嫩，并轉(zhuǎn)為一個(gè)常量時(shí)，使用這些訪問類型垦搬。如果將主鍵置于where列表中呼寸，MySQL就能將該查詢轉(zhuǎn)化為一個(gè)常量。

possible_keys：顯示可能應(yīng)用在這張表中的索引猴贰。如果為空对雪，沒有可能的索引∶兹疲可以為相關(guān)的域從WHERE語(yǔ)句中選擇一個(gè)合適的語(yǔ)句

key： 實(shí)際使用的索引瑟捣。如果為NULL，則沒有使用索引栅干。很少的情況下迈套，MySQL會(huì)選擇優(yōu)化不足的索引。這種情況下碱鳞，可以在SELECT語(yǔ)句中使用USE INDEX（indexname）來強(qiáng)制使用一個(gè)索引或者用IGNORE INDEX（indexname）來強(qiáng)制MySQL忽略索引

key_len：使用的索引的長(zhǎng)度桑李。在不損失精確性的情況下，長(zhǎng)度越短越好

ref：顯示索引的哪一列被使用了劫笙，如果可能的話芙扎，是一個(gè)常數(shù)

rows：MySQL認(rèn)為必須檢查的用來返回請(qǐng)求數(shù)據(jù)的行數(shù)

Extra：關(guān)于MySQL如何解析查詢的額外信息。將在表4.3中討論填大，但這里可以看到的壞的例子是Using temporary和Using filesort戒洼，意思MySQL根本不能使用索引，結(jié)果是檢索會(huì)很慢允华。

3.MySQL慢查詢?cè)趺唇鉀Q?

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

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

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

六召耘、數(shù)據(jù)庫(kù)鎖

1.mysql都有什么鎖，死鎖判定原理和具體場(chǎng)景褐隆，死鎖怎么解決?

MySQL有三種鎖的級(jí)別：頁(yè)級(jí)污它、表級(jí)、行級(jí)庶弃。

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

• 行級(jí)鎖：開銷大缴守，加鎖慢葬毫；會(huì)出現(xiàn)死鎖；鎖定粒度最小屡穗，發(fā)生鎖沖突的概率最低,并發(fā)度也最高贴捡。

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

什么是死鎖？

死鎖: 是指兩個(gè)或兩個(gè)以上的進(jìn)程在執(zhí)行過程中箍镜。因爭(zhēng)奪資源而造成的一種互相等待的現(xiàn)象,若無(wú)外力作用,它們都將無(wú)法推進(jìn)下去源祈。此時(shí)稱系統(tǒng)處于死鎖狀態(tài)或系統(tǒng)產(chǎn)生了死鎖,這些永遠(yuǎn)在互相等竺的進(jìn)程稱為死鎖進(jìn)程。

表級(jí)鎖不會(huì)產(chǎn)生死鎖.所以解決死鎖主要還是針對(duì)于最常用的InnoDB色迂。

死鎖的關(guān)鍵在于：兩個(gè)(或以上)的Session加鎖的順序不一致香缺。

那么對(duì)應(yīng)的解決死鎖問題的關(guān)鍵就是：讓不同的session加鎖有次序。

死鎖的解決辦法?

1.查出的線程殺死 kill
SELECT trx_MySQL_thread_id FROM information_schema.INNODB_TRX;

2.設(shè)置鎖的超時(shí)時(shí)間
Innodb 行鎖的等待時(shí)間歇僧，單位秒图张。可在會(huì)話級(jí)別設(shè)置诈悍，RDS 實(shí)例該參數(shù)的默認(rèn)值為 50（秒）祸轮。

生產(chǎn)環(huán)境不推薦使用過大的 innodb_lock_wait_timeout參數(shù)值
該參數(shù)支持在會(huì)話級(jí)別修改，方便應(yīng)用在會(huì)話級(jí)別單獨(dú)設(shè)置某些特殊操作的行鎖等待超時(shí)時(shí)間侥钳，如下：
set innodb_lock_wait_timeout=1000; —設(shè)置當(dāng)前會(huì)話 Innodb 行鎖等待超時(shí)時(shí)間适袜，單位秒。

3.指定獲取鎖的順序

2.有哪些鎖（樂觀鎖悲觀鎖）舷夺，select 時(shí)怎么加排它鎖?

悲觀鎖（Pessimistic Lock）:

悲觀鎖特點(diǎn):先獲取鎖苦酱，再進(jìn)行業(yè)務(wù)操作售貌。

即“悲觀”的認(rèn)為獲取鎖是非常有可能失敗的，因此要先確保獲取鎖成功再進(jìn)行業(yè)務(wù)操作疫萤。通常所說的“一鎖二查三更新”即指的是使用悲觀鎖颂跨。通常來講在數(shù)據(jù)庫(kù)上的悲觀鎖需要數(shù)據(jù)庫(kù)本身提供支持，即通過常用的select … for update操作來實(shí)現(xiàn)悲觀鎖扯饶。當(dāng)數(shù)據(jù)庫(kù)執(zhí)行select for update時(shí)會(huì)獲取被select中的數(shù)據(jù)行的行鎖恒削，因此其他并發(fā)執(zhí)行的select for update如果試圖選中同一行則會(huì)發(fā)生排斥（需要等待行鎖被釋放），因此達(dá)到鎖的效果帝际。select for update獲取的行鎖會(huì)在當(dāng)前事務(wù)結(jié)束時(shí)自動(dòng)釋放蔓同，因此必須在事務(wù)中使用。

補(bǔ)充：
不同的數(shù)據(jù)庫(kù)對(duì)select for update的實(shí)現(xiàn)和支持都是有所區(qū)別的蹲诀，

• oracle支持select for update no wait，表示如果拿不到鎖立刻報(bào)錯(cuò)弃揽，而不是等待脯爪，MySQL就沒有no wait這個(gè)選項(xiàng)。

• MySQL還有個(gè)問題是select for update語(yǔ)句執(zhí)行中所有掃描過的行都會(huì)被鎖上矿微，這一點(diǎn)很容易造成問題痕慢。因此如果在MySQL中用悲觀鎖務(wù)必要確定走了索引，而不是全表掃描涌矢。

樂觀鎖（Optimistic Lock）:

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

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

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

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

樂觀鎖（給表加一個(gè)版本號(hào)字段） 這個(gè)并不是樂觀鎖的定義羔砾，給表加版本號(hào)负间，是數(shù)據(jù)庫(kù)實(shí)現(xiàn)樂觀鎖的一種方式偶妖。

1. SELECT data AS old_data, version AS old_version FROM …;

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

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

if (updated row > 0) {

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

} else {

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

}

注意：

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

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

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

• 響應(yīng)速度： 如果需要非常高的響應(yīng)速度科吭，建議采用樂觀鎖方案昏滴，成功就執(zhí)行，不成功就失敗对人，不需要等待其他并發(fā)去釋放鎖谣殊。'

• 沖突頻率： 如果沖突頻率非常高，建議采用悲觀鎖牺弄，保證成功率姻几，如果沖突頻率大，樂觀鎖會(huì)需要多次重試才能成功势告，代價(jià)比較大蛇捌。

• 重試代價(jià)： 如果重試代價(jià)大，建議采用悲觀鎖培慌。

七豁陆、其他

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

主從復(fù)制的幾種方式:

同步復(fù)制:

所謂的同步復(fù)制，意思是master的變化吵护，必須等待slave-1,slave-2,...,slave-n完成后才能返回盒音。這樣，顯然不可取馅而，也不是MySQL復(fù)制的默認(rèn)設(shè)置祥诽。比如，在WEB前端頁(yè)面上瓮恭，用戶增加了條記錄雄坪，需要等待很長(zhǎng)時(shí)間。

異步復(fù)制:

如同AJAX請(qǐng)求一樣屯蹦。master只需要完成自己的數(shù)據(jù)庫(kù)操作即可维哈。至于slaves是否收到二進(jìn)制日志绳姨，是否完成操作，不用關(guān)心,MySQL的默認(rèn)設(shè)置阔挠。

半同步復(fù)制:

master只保證slaves中的一個(gè)操作成功飘庄，就返回，其他slave不管购撼。這個(gè)功能跪削，是由google為MySQL引入的。

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

問題1：master的寫操作迂求，slaves被動(dòng)的進(jìn)行一樣的操作碾盐，保持?jǐn)?shù)據(jù)一致性，那么slave是否可以主動(dòng)的進(jìn)行寫操作揩局？

假設(shè)slave可以主動(dòng)的進(jìn)行寫操作毫玖，slave又無(wú)法通知master，這樣就導(dǎo)致了master和slave數(shù)據(jù)不一致了谐腰。因此slave不應(yīng)該進(jìn)行寫操作孕豹，至少是slave上涉及到復(fù)制的數(shù)據(jù)庫(kù)不可以寫。實(shí)際上十气，這里已經(jīng)揭示了讀寫分離的概念。

問題2：主從復(fù)制中春霍，可以有N個(gè)slave,可是這些slave又不能進(jìn)行寫操作砸西，要他們干嘛？

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

異地容災(zāi):比如master在北京鸳慈，地震掛了，那么在上海的slave還可以繼續(xù)喧伞。
主要用于實(shí)現(xiàn)scale out,分擔(dān)負(fù)載,可以將讀的任務(wù)分散到slaves上走芋。
【很可能的情況是，一個(gè)系統(tǒng)的讀操作遠(yuǎn)遠(yuǎn)多于寫操作潘鲫，因此寫操作發(fā)向master翁逞，讀操作發(fā)向slaves進(jìn)行操作】

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

我們?cè)趹?yīng)用程序中可以這樣溉仑，insert/delete/update這些更新數(shù)據(jù)庫(kù)的操作挖函，用connection(for master)進(jìn)行操作，

select用connection(for slaves)進(jìn)行操作浊竟。那我們的應(yīng)用程序還要完成怎么從slaves選擇一個(gè)來執(zhí)行select怨喘，例如使用簡(jiǎn)單的輪循算法津畸。

這樣的話，相當(dāng)于應(yīng)用程序完成了SQL語(yǔ)句的路由必怜，而且與MySQL的主從復(fù)制架構(gòu)非常關(guān)聯(lián)肉拓，一旦master掛了，某些slave掛了棚赔，那么應(yīng)用程序就要修改了帝簇。能不能讓應(yīng)用程序與MySQL的主從復(fù)制架構(gòu)沒有什么太多關(guān)系呢？
找一個(gè)組件靠益，application program只需要與它打交道丧肴，用它來完成MySQL的代理，實(shí)現(xiàn)SQL語(yǔ)句的路由胧后。
MySQL proxy并不負(fù)責(zé)芋浮，怎么從眾多的slaves挑一個(gè)？可以交給另一個(gè)組件(比如haproxy)來完成壳快。

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

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

總統(tǒng)一般都會(huì)弄個(gè)副總統(tǒng)瘤旨，以防不測(cè)。同樣的竖伯，可以給這些關(guān)鍵的節(jié)點(diǎn)來個(gè)備份存哲。

問題5：當(dāng)master的二進(jìn)制日志每產(chǎn)生一個(gè)事件，都需要發(fā)往slave七婴，如果我們有N個(gè)slave,那是發(fā)N次祟偷，還是只發(fā)一次？如果只發(fā)一次打厘，發(fā)給了slave-1修肠，那slave-2,slave-3,...它們?cè)趺崔k？

顯 然户盯，應(yīng)該發(fā)N次嵌施。實(shí)際上，在MySQL master內(nèi)部先舷，維護(hù)N個(gè)線程艰管，每一個(gè)線程負(fù)責(zé)將二進(jìn)制日志文件發(fā)往對(duì)應(yīng)的slave。master既要負(fù)責(zé)寫操作蒋川，還的維護(hù)N個(gè)線程牲芋，負(fù)擔(dān)會(huì)很重。可以這樣缸浦，slave-1是master的從夕冲，slave-1又是slave-2,slave-3,...的主，同時(shí)slave-1不再負(fù)責(zé)select裂逐。slave-1將master的復(fù)制線程的負(fù)擔(dān)歹鱼，轉(zhuǎn)移到自己的身上。這就是所謂的多級(jí)復(fù)制的概念卜高。

問題6：當(dāng)一個(gè)select發(fā)往MySQL proxy弥姻，可能這次由slave-2響應(yīng)，下次由slave-3響應(yīng)掺涛，這樣的話庭敦，就無(wú)法利用查詢緩存了。

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

問題7：隨著應(yīng)用的日益增長(zhǎng)拣帽，讀操作很多疼电，我們可以擴(kuò)展slave，但是如果master滿足不了寫操作了减拭，怎么辦呢蔽豺？

scale on ?更好的服務(wù)器？沒有最好的拧粪，只有更好的茫虽，太貴了。既们。。
scale out ? 主從復(fù)制架構(gòu)已經(jīng)滿足不了正什。
可以分庫(kù)【垂直拆分】啥纸，分表【水平拆分】。

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

MySQL 高并發(fā)環(huán)境解決方案： 分庫(kù) 分表 分布式 增加二級(jí)緩存婴氮。斯棒。。主经。荣暮。

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

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

集群方案：解決DB宕機(jī)帶來的單點(diǎn)DB不能訪問問題骏啰。

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

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

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

Undo Log:

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

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

之所以能同時(shí)保證原子性和持久化娶桦，是因?yàn)橐韵?strong>特點(diǎn)：

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

缺陷：每個(gè)事務(wù)提交前將數(shù)據(jù)和Undo Log寫入磁盤沮峡，這樣會(huì)導(dǎo)致大量的磁盤IO疚脐，因此性能很低。
如果能夠?qū)?shù)據(jù)緩存一段時(shí)間邢疙，就能減少IO提高性能棍弄。但是這樣就會(huì)喪失事務(wù)的持久性望薄。因此引入了另外一種機(jī)制來實(shí)現(xiàn)持久化，即Redo Log照卦。

Redo Log:

原理和Undo Log相反式矫，Redo Log記錄的是新數(shù)據(jù)的備份。在事務(wù)提交前役耕，只要將Redo Log持久化即可采转，不需要將數(shù)據(jù)持久化。當(dāng)系統(tǒng)崩潰時(shí)瞬痘，雖然數(shù)據(jù)沒有持久化故慈，但是Redo Log已經(jīng)持久化。系統(tǒng)可以根據(jù)Redo Log的內(nèi)容框全，將所有數(shù)據(jù)恢復(fù)到最新的狀態(tài)察绷。