寫(xiě)在前面

本文主要側(cè)重于索引、事務(wù)、優(yōu)化等方面的面試問(wèn)題。
原文鏈接：https://mp.weixin.qq.com/s/qmJ2kkK1gHPu9NwQYu4Sag

索引

1.什么是索引？
??索引是一種數(shù)據(jù)結(jié)構(gòu)、可以幫助我們快速的進(jìn)行數(shù)據(jù)查找。

2.索引是什么樣的數(shù)據(jù)結(jié)構(gòu)？
??索引根據(jù)數(shù)據(jù)引擎的不同主要可分為Hash索引、B+樹(shù)索引。
??常用的InnoDB引擎采用B+樹(shù)索引

3.Hash索引和B+樹(shù)索引分別存儲(chǔ)了什么
??Hash索引：存儲(chǔ)Hash值
??B+樹(shù)索引：非葉子節(jié)點(diǎn)存儲(chǔ)其子樹(shù)的最大(或最小)關(guān)鍵字，可以看成索引；葉子節(jié)點(diǎn)存儲(chǔ)全部關(guān)鍵字以及指向相應(yīng)記錄的指針，而且葉子節(jié)點(diǎn)的關(guān)鍵字按大小順序排序，相鄰葉子節(jié)點(diǎn)用指針鏈接

4.Hash索引和B+樹(shù)索引區(qū)別或者說(shuō)優(yōu)劣？
??Hash索引：底層實(shí)現(xiàn)為Hash表，進(jìn)行查詢時(shí)，調(diào)用hash函數(shù)獲取到相應(yīng)的鍵值，之后進(jìn)行回表查詢獲得實(shí)際數(shù)據(jù)。
??B+樹(shù)索引：底層實(shí)現(xiàn)為多路平衡查找樹(shù)，每次查找都是從根節(jié)點(diǎn)出發(fā)，查找到葉子節(jié)點(diǎn)方可以獲得查找鍵值，然后根據(jù)查詢判斷是否需要回表查詢數(shù)據(jù)。
??優(yōu)缺點(diǎn)：
???? ★ Hash索引進(jìn)行等值查詢更快，卻無(wú)法進(jìn)行范圍查詢。因?yàn)镠ash索引中經(jīng)過(guò)Hash函數(shù)建立索引之后，索引順序于原順序無(wú)法保持一致。
????★ B+樹(shù)索引天然支持范圍查詢。
????★ Hash索引不支持使用索引進(jìn)行排序，原理同上
????★ hash索引任何時(shí)候都避免不了回表查詢，而B(niǎo)+樹(shù)在符合某些條件(聚簇索引、覆蓋索引)的時(shí)候可以只通過(guò)索引完成查詢。
????★ Hash索引雖然在等值查詢上較快，但是不穩(wěn)定，性能不可預(yù)測(cè)，當(dāng)某個(gè)鍵值存在大量重復(fù)的時(shí)候,發(fā)生hash碰撞,此時(shí)效率可能極差.而B(niǎo)+樹(shù)的查詢效率比較穩(wěn)定,對(duì)于所有的查詢都是從根節(jié)點(diǎn)到葉子節(jié)點(diǎn),且樹(shù)的高度較低.

5.什么是聚簇索引？
??聚簇索引確定表中數(shù)據(jù)的物理順序，InnoDB中只有主鍵引擎是聚簇索引，如果沒(méi)有主鍵，則挑選一個(gè)唯一鍵建立聚簇索引，如果沒(méi)有唯一鍵，則隱式的生成一個(gè)鍵來(lái)建立聚簇索引。當(dāng)查詢使用聚簇索引時(shí)，在對(duì)應(yīng)的葉子節(jié)點(diǎn)，可以獲取到整行數(shù)據(jù)，因此不用再次進(jìn)行回表查詢。

6.非聚簇索引一定會(huì)回表查詢么？
??不一定，這涉及到查詢語(yǔ)句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再進(jìn)行回表查詢。

7. 建立索引的時(shí)候，需要考慮那些因素？
??字段的使用頻率，經(jīng)常作為i套件進(jìn)行查詢的字段比較適合，如果是聯(lián)合索引，還要考慮聯(lián)合索引中的順序。
??過(guò)多的索引會(huì)對(duì)表造成太大的壓力

8.聯(lián)合索引是什么？為什么需要注意聯(lián)合索引中順序？
??使用多個(gè)字段同時(shí)建立一個(gè)索引，叫做聯(lián)合索引。在聯(lián)合索引中如果想要命中索引，需要按照建立索引時(shí)的字段順序來(lái)挨個(gè)使用，否則無(wú)法命中索引

9.如何查看索引是否被用到？或者說(shuō)如何知道這條語(yǔ)句運(yùn)行很慢的原因
??expain

10.什么情況下會(huì)發(fā)生創(chuàng)建了索引卻未被使用，如何避免全表查詢？
?? ★ 最佳左前綴法則，是指聯(lián)合索引的查詢需要從索引的最左前列開(kāi)始并且不跳過(guò)索引中的列。(不必時(shí)全部索引參數(shù)，但是中間不能跳過(guò)，必須按照索引順序挨個(gè)使用)
?? ★ 不在索引列上做任何操作(計(jì)算、函數(shù)、(自動(dòng)or手動(dòng))類型轉(zhuǎn)換)，會(huì)導(dǎo)致索引失效而轉(zhuǎn)向全表掃描。
?? ★ 存儲(chǔ)引擎不能使用索引范圍條件右邊的列。即在范圍條件(rang > <等)右邊的條件是自動(dòng)失去索引的。如 pos的索引不起作用。

EXPLAIN select * from staffs where name='July' AND age>25 and pos='dev';

?? ★ 盡量使用覆蓋索引（只訪問(wèn)索引的查詢（索引列和查詢列一致）），減少select *
?? ★ 使用不等號(hào)(!= 或者<>)、is null、is not null時(shí)無(wú)法使用索引
?? ★ like以通配符開(kāi)頭('%abc')索引失效
?? ★ 字符串不加單引號(hào)索引失效(底層會(huì)進(jìn)行隱式轉(zhuǎn)換)
?? ★ or 會(huì)導(dǎo)致索引失效

事務(wù)

Mysql中僅有Innodb數(shù)據(jù)庫(kù)引擎支持事務(wù)。事務(wù)主要是用于處理操作量大、復(fù)雜度高的數(shù)據(jù)
事務(wù)可用來(lái)維護(hù)數(shù)據(jù)庫(kù)的完整性，保證批量SQL要么全部執(zhí)行，要么全部不執(zhí)行。

四大特征(ACID)
??A（原子性）：要么全部成功，要么全部失敗，不會(huì)結(jié)束在中間某個(gè)環(huán)節(jié)。事務(wù)執(zhí)行發(fā)生錯(cuò)誤會(huì)執(zhí)行回滾。
??C（一致性）：
??I（隔離性）：允許多個(gè)并發(fā)事務(wù)同時(shí)進(jìn)行讀寫(xiě)，隔離性可以防止多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)由于交叉執(zhí)行而導(dǎo)致數(shù)據(jù)的不一致。事務(wù)的隔離性可分為四種：讀未提交、讀提交、可重復(fù)度、串行化
??D（持久性）：事務(wù)處理結(jié)束后，對(duì)數(shù)據(jù)的修改是永久的，即便系統(tǒng)故障也不會(huì)丟失。

基本操作
??★ BEGIN/START TRANSACTION：顯式的開(kāi)啟一個(gè)事務(wù)；
??★ COMMIT：提交事務(wù)，并使已對(duì)數(shù)據(jù)庫(kù)進(jìn)行的所有修改成為永久性的；
??★ ROLLBACK：回滾，并撤銷正在進(jìn)行的所有未提交的修改；
??★ SAVEPOINT identifier：允許在事務(wù)中創(chuàng)建一個(gè)保存點(diǎn)，一個(gè)事務(wù)中可以有多個(gè)SAVEOINT；
??★ RELEASE SAVEPOINT identifier：刪除一個(gè)事務(wù)的保存點(diǎn)，當(dāng)沒(méi)有指定保存點(diǎn)時(shí)，執(zhí)行該語(yǔ)句會(huì)拋出一個(gè)異常；
??★ ROLLBACK TO identifier：把事務(wù)回滾到標(biāo)記點(diǎn)；
??★ SET TRANSACTION：用來(lái)設(shè)置事務(wù)的隔離級(jí)別；

注：事務(wù)在commit之后，無(wú)法再回滾。

事務(wù)的隔離特性
??★讀未提交（Read uncommitted）：該級(jí)別僅存于理論中，無(wú)人使用
??★讀提交（Read committed）：Oracle默認(rèn)隔離級(jí)別
??★可重復(fù)讀（repeatable read）：MySql默認(rèn)隔離級(jí)別
??★串行化（Serializable）:很少使用，事務(wù)操作需要排隊(duì)，吞吐量太低，用戶體驗(yàn)差。串行而不是并發(fā)。

臟讀：指一個(gè)事務(wù)正在訪問(wèn)數(shù)據(jù)，并且對(duì)數(shù)據(jù)進(jìn)行了修改，而這種修改還沒(méi)有提交到數(shù)據(jù)庫(kù)中，這時(shí)，另一個(gè)事務(wù)也訪問(wèn)這個(gè)數(shù)據(jù)，然后使用了這個(gè)數(shù)據(jù)。
不可重復(fù)讀：在事務(wù)執(zhí)行過(guò)程中讀取了其他事務(wù)更改的數(shù)據(jù)，針對(duì)update操作。
??解決：使用行級(jí)鎖，事務(wù)在多次讀取操作完成后才釋放該鎖，才能允許其他事務(wù)更改數(shù)據(jù)。
幻讀：在事務(wù)執(zhí)行過(guò)程中讀取了其他事務(wù)新增的數(shù)據(jù)，針對(duì)的是insert和delete操作。
??解決：使用表鎖，鎖定整張表，事務(wù)多次讀取數(shù)據(jù)完成后釋放該鎖，這個(gè)時(shí)候才允許其他事務(wù)新增數(shù)據(jù)。
注：幻讀和不可重復(fù)讀都是指的一個(gè)事務(wù)范圍內(nèi)的操作受到其他事務(wù)的影響了。只不過(guò)幻讀的重點(diǎn)在插入和刪除，不可重復(fù)讀的重點(diǎn)在修改。
事務(wù)實(shí)現(xiàn)原理
日志文件、鎖技術(shù)、MVCC(多版本并發(fā)控制)
參考博文：http://www.itdecent.cn/p/081a3e208e32

事務(wù)執(zhí)行過(guò)程斷電會(huì)如何？
??客戶端斷電：已提交服務(wù)器正常執(zhí)行，未提交則回滾
??服務(wù)器斷電：未提交回滾。已提交的事務(wù)會(huì)現(xiàn)在日志里面存放，通電后，已提交但未寫(xiě)入硬盤(pán)的會(huì)繼續(xù)寫(xiě)入，未提交的會(huì)在日志中回滾。
??服務(wù)器斷電后鎖全部取消(鎖存儲(chǔ)在內(nèi)存中)
參考博文：https://q.cnblogs.com/q/65483/

MYSQL 事務(wù)處理的兩種主要方法
??1.BEGIN、ROLLBACK、COMMIT實(shí)現(xiàn)
????★ BEGIN 開(kāi)啟事務(wù)
????★ ROLLBACK 事務(wù)回滾
????★ COMMIT 事務(wù)確認(rèn)
??2.直接用SET來(lái)改變MySQL的自動(dòng)提交模式
????★ SET AUTOCOMMIT=0 禁止自動(dòng)提交
????★ SET AUTOCOMMIT=1 開(kāi)啟自動(dòng)提交
案例

begin;   // 開(kāi)啟事務(wù)
insert into XXXXX
update XXXX
commit;  // 提交

存儲(chǔ)過(guò)程

定義：存儲(chǔ)過(guò)程是一種在數(shù)據(jù)庫(kù)中存儲(chǔ)復(fù)雜過(guò)程，以便外部程序調(diào)用的一種數(shù)據(jù)庫(kù)對(duì)象。存儲(chǔ)過(guò)程是為了完成特定功能的SQL語(yǔ)句集，經(jīng)編譯創(chuàng)建并保存在數(shù)據(jù)庫(kù)中。
存儲(chǔ)過(guò)程思想上很簡(jiǎn)單，就是數(shù)據(jù)庫(kù)SQL語(yǔ)言層面的代碼封裝與重用。
優(yōu)點(diǎn)：
??★ 可封裝并隱藏復(fù)雜的邏輯。
??★ 不需要反復(fù)建立一系列的處理步驟，因而保證了數(shù)據(jù)的一致性。
??★ 簡(jiǎn)化了對(duì)變動(dòng)的管理，這一點(diǎn)的延伸就是安全性。
??★ 可存儲(chǔ)過(guò)程通常以編譯過(guò)的形式存儲(chǔ)，提高了性能。
缺點(diǎn)
??★ 可移植性差，往往定制化與特定的數(shù)據(jù)庫(kù)上，切換數(shù)據(jù)庫(kù)存儲(chǔ)過(guò)程可能需要重寫(xiě)。
??★ 存儲(chǔ)過(guò)程性能調(diào)教與撰寫(xiě)，受限于各種數(shù)據(jù)庫(kù)系統(tǒng)。
JAVA開(kāi)發(fā)手冊(cè):
??強(qiáng)制禁止使用存儲(chǔ)過(guò)程，存儲(chǔ)過(guò)程難以調(diào)試和擴(kuò)展，更沒(méi)有移植性。
創(chuàng)建與調(diào)用
參考博文：https://www.cnblogs.com/chenliyang/p/6553068.html

CREATE PROCEDURE `name` (IN | OUT | INOUT params_name type , ...)  //定義存儲(chǔ)過(guò)程

DECLARE kind int unsigned default 0;   //定義變量
DECLARE l_numeric number(8,2) DEFAULT 9.95;  
DECLARE l_date date DEFAULT '1999-12-31';  

BEGIN   // 存儲(chǔ)過(guò)程開(kāi)始

IF kind=1 THEN // if 條件語(yǔ)句
    SELECT AVG(item_price) INTO Pavg FROM order_items;//  INTO 將查詢值賦給輸出變量
  ELSEIF kind=2 THEN
    SELECT MAX(item_price) INTO Pmax FROM order_items;
  ELSE
    SELECT MIN(item_price) INTO Pmin FROM order_items; 
END IF;  // IF結(jié)束

CASE kind  // case 選擇語(yǔ)句
   WHEN 0 THEN
      update order_items set item_price=0;
   WHEN 1 THEN
      update order_items set item_price=1;
   ELSE
      update order_items set item_price=2;
END CASE; // case 結(jié)束

WHILE kind < 6  // while循環(huán)  先檢查結(jié)果后執(zhí)行操作
  DO  
      INSERT INTO order_items (item_price) VALUES (kind);
      SET kind = kind+1;
END WHILE; // while循環(huán)結(jié)束

REPEAT    //repeat 循環(huán)  先執(zhí)行操作后檢查結(jié)果
        INSERT INTO order_items (item_price) VALUES (kind);
        SET kind = kind + 1;
   UNTIL kind >=5;
END REPEAT;  repeat循環(huán)結(jié)束

LOOP_LABLE: loop  // loop循環(huán)開(kāi)始
       INSERT INTO order_items (item_price) VALUES (kind);
       SET kind = kind + 1;
       if kind >= 5 then
          leave LOOP_LABLE;
       end if
END LOOP;  

END;    //存儲(chǔ)過(guò)程結(jié)束

參數(shù)說(shuō)明：
??★ name：存儲(chǔ)過(guò)程名
??★ params_name：自定義參數(shù)名
??★ type：參數(shù)類型
??★ IN：輸入?yún)?shù)，表示調(diào)用者想過(guò)程傳入值（傳入值可以是字面量或變量）
??★ OUT：輸出參數(shù)，表示過(guò)程向調(diào)用者傳出值（傳出值只能是變量）
??★ INOUT：輸入輸出參數(shù),既表示調(diào)用者向過(guò)程傳入值，又表示過(guò)程向調(diào)用者傳出值（值只能是變量）
定義變量
??存儲(chǔ)過(guò)程可定義變量，但變量聲明一定要放在存儲(chǔ)過(guò)程的開(kāi)始
**
調(diào)用
??CALL name(@a,@b,@c)