Mysql面試題

• 如何設(shè)計一個關(guān)系型數(shù)據(jù)庫

1. 設(shè)計一個數(shù)據(jù)庫,首先數(shù)據(jù)庫是一個存儲數(shù)據(jù)的軟件,所以首先要有一個存儲模塊,負(fù)責(zé)存儲我們的數(shù)據(jù),存儲在機(jī)械硬盤或者我們的固態(tài)硬盤里面
2. 還要有一個組織和管理數(shù)據(jù)的實例,就是存儲管理
3. 還要考慮存儲的效率,要把數(shù)據(jù)提取到內(nèi)存里面,減少IO,所以就需要緩存機(jī)制,當(dāng)你訪問一個數(shù)據(jù),也就意味著你相鄰的數(shù)據(jù)也有可能會被訪問到
4. 還需要一個SQL解析器,通過指令來讀取數(shù)據(jù)
5. 數(shù)據(jù)庫也有可能出錯,那么我們就需要查錯恢復(fù),就需要一個日志管理,異常機(jī)制
6. 數(shù)據(jù)庫也需要一些權(quán)限,不能人人都能讀取數(shù)據(jù),需要權(quán)限管理
7. 為了進(jìn)一步提升查詢數(shù)據(jù)的速率,讓數(shù)據(jù)庫支持并發(fā),就需要索引和鎖結(jié)構(gòu).

• 為什么要使用索引

1. 如果我們使用最簡單的方式查詢數(shù)據(jù),全盤掃描,即將全表的數(shù)據(jù)分批次,加載到內(nèi)存里面,但是實際的開發(fā)過程中,用戶量非常大,表數(shù)據(jù)特別多,極少數(shù)會用到查詢所有的數(shù)據(jù),都是有一些限制條件,你這樣無疑查詢起來會非常慢,所以我們要避免全表掃描,就引出了索引的結(jié)構(gòu).通過索引可以快速查詢數(shù)據(jù).
2. 什么是索引呢?索引就是能把數(shù)據(jù)限制在一定查找范圍的一種結(jié)構(gòu),一般用主鍵作為索引

• Mysql的基本架構(gòu)?

1. Mysql架構(gòu)可以分為三層
2. 第一層為客戶端,向數(shù)據(jù)庫發(fā)送請求,jdbc
3. 第二層為服務(wù)器,分析器,連接器,緩存,優(yōu)化器等等
4. 第三層為存儲引擎,InnoDB,MyISAM,

• Mysql索引的底層是什么?為什么不采用二叉樹和紅黑樹和B樹?

1. Mysql索引的底層是B+樹
2. 如果采用二叉樹,在一些極端的情況下會變成一個鏈表,他會按照你的插入的順序,比前一個大的放在右邊,小的放在左邊,如果你插入的順序是依次遞增的,那么就會變成一個鏈表.
3. 紅黑樹是一個平衡二叉樹,雖然每次查找的時間復(fù)雜度都是O(logn),但是隨著你數(shù)據(jù)越多,紅黑樹的高度就會越高.IO次數(shù)就越多,也會影響查詢效率.
4. B樹相對于紅黑樹來說,就是每層的節(jié)點樹不再是1,而是會有很多,且每層都有一個data域和指向下一層的指針,這樣它每次查找的效率都是O(logn),當(dāng)有新的節(jié)點插入,他會分裂或者上下節(jié)點移動,保證是一個平衡的數(shù)結(jié)構(gòu)
5. 其實B+樹就是B樹的一種變形,對B樹進(jìn)行了以下改進(jìn),B樹的所有節(jié)點都要存儲一個data,而B+樹只有每個葉子節(jié)點才會存放data,其余節(jié)點用來索引,磁盤讀寫代價更低,這就決定了B+樹更適合用來存儲外部數(shù)據(jù),即磁盤數(shù)據(jù).
6. B+樹葉子節(jié)點用指針連接,能夠跨區(qū)域范圍統(tǒng)計,提高了區(qū)間訪問的性能.
7. B+樹的查詢效率穩(wěn)定.查找每個數(shù)據(jù)的時間是相同的O(logn)

• InnoDB和MySIAM引擎區(qū)別?

1. Innodb存儲引擎使用的是聚集索引,即B+樹葉子節(jié)點包含了完整的數(shù)據(jù)記錄;MySIAM存儲引擎使用的是非聚集索引,B+樹的葉子節(jié)點只有一個數(shù)據(jù)的地址,然后通過地址去尋找數(shù)據(jù)
2. Innodb支持行級鎖表級鎖;MyISAM只有表級鎖,當(dāng)你進(jìn)行一個查詢語句的時候,同時你也要執(zhí)行一條增刪改語句,那么增刪改語句一定會等到你查詢結(jié)果出來之后才會進(jìn)行,因為查詢語句會為數(shù)據(jù)庫加一個讀鎖.當(dāng)讀鎖沒有釋放,不能加寫鎖.當(dāng)表數(shù)據(jù)量特別大的時候,就會出現(xiàn)問題,
3. Innodb支持事務(wù),MyISAM不支持事務(wù).
4. InnoDB支持外鍵,MyISAM不支持外鍵
5. InnoDB支持MVCC,應(yīng)對高并發(fā)事務(wù),MVCC比單純的加鎖更高效,MVCC只在提交讀和重復(fù)讀兩個隔離級別下才工作;MVCC可以使用樂觀鎖和悲觀鎖實現(xiàn)

• 稠密索引和稀疏索引的區(qū)別?

1. 稠密索引是每個索引項都對應(yīng)一條數(shù)據(jù)記錄
2. 稀疏索引是每個索引項對應(yīng)著多條數(shù)據(jù)

• 為什么InnoDB表必須要有主鍵,并且推薦使用整型的自增主鍵?

1. 首先Innodb是根據(jù)主鍵進(jìn)行索引,有了主鍵效率更加高,如果沒有主鍵,Innodb會默認(rèn)給你加一個主鍵.
2. B+樹在查找的過程中每一層的索引要進(jìn)行比較,整型的比較快.
3. 自增主鍵:B+樹中葉子節(jié)點有一個指向相鄰節(jié)點的一個指針,又因為葉子節(jié)點從左到右是依次遞增的,這樣在范圍查找的時候更加方便,根據(jù)這個指針順藤摸瓜~
4. 自增主鍵: 防止B+樹分裂,然后樹平衡,使用自增,永遠(yuǎn)是在葉子節(jié)點后面添加元素,而不會在某兩個葉子節(jié)點中間,有效的防止樹分裂,減少開銷.

• 如果定位并優(yōu)化慢查詢SQL

1. 根據(jù)慢日志定位慢查詢SQL;如果一個SQL語句超過了指定的時間限制,這條SQL語句會被自動放在一個慢日志里面，可以進(jìn)入這個日志里面查詢具體是那個SQL語句執(zhí)行那么慢．
2. 使用explain等工具分析SQL撬呢，explain放在select語句前面,type字段表示的是mysql找到指定行的方式,如果是index或者all說明是全表掃描,證明語句就需要優(yōu)化;extra字段表示的是
3. 修改SQL盡量讓SQL走索引

• 聯(lián)合索引最左匹配原則的成因?

1. 聯(lián)合索引由多列組成的索引
2. 最左匹配原則:比如說兩個索引是AB,我們在where語句中調(diào)用where A = ? and B = ?會使用索引,where A = ?也會走索引,但是當(dāng)走where B = ?不會走索引,就會走全表掃描.
3. 最左前綴匹配原則,就是mysql會一直向右匹配直到遇到范圍查找就會停止匹配.
4. 聯(lián)合索引的B+樹是根據(jù)第一個數(shù)字進(jìn)行排序的,比如說B+樹的結(jié)構(gòu),最上邊是(2,4),那么左下邊可能是(1,1)(1,2)(2,1),右下邊可能是(3,1)(3,3)(4,1),可以發(fā)現(xiàn)第一個索引是有序的,但是第二個索引是無序的,

• 索引是越多越好嗎?

1. 物極必反,數(shù)據(jù)量小的表不需要建立索引,建立會增加額外的索引開銷.
2. 數(shù)據(jù)變更需要維護(hù)索引,增加維護(hù)成本,
3. 更多的索引也會更多的空間.

• Mysql的分庫分表

1. 分庫分表就是隨著你業(yè)務(wù)的發(fā)展,用戶增多,數(shù)據(jù)訪問量大,表單數(shù)據(jù)就會太大,會極大影響你sql的執(zhí)行性能,這時候一張表就不夠用了,就可以分表.分庫是將一個庫的數(shù)據(jù)拆分到多個庫中,訪問的時候就訪問一個就好了.
2. 分庫分表分為水平拆分和垂直拆分
3. 水平拆分:將一個表的數(shù)據(jù)拆分到多個庫的多個表,每個表的表結(jié)構(gòu)都一樣,將數(shù)據(jù)均勻的放在更多的庫里面,然后用多個庫杠更多的并發(fā)量.一般就是按時間范圍來分,或者h(yuǎn)ash分表
4. 垂直拆分:把一個有很多字段的表拆分多個表,每個表的表結(jié)構(gòu)不一樣

• 為什么不使用Hash索引而使用b+樹?

1. 不支持范圍查詢,只能使用"=","in"
2. 哈希索引數(shù)據(jù)并不是按照索引值順序存儲的,無法應(yīng)用于排序
3. 會出現(xiàn)哈希沖突,必須遍歷鏈表中所有的行指針,逐行進(jìn)行比較.
4. 但是InnoDB有一個特殊的功能自適應(yīng)哈希索引,當(dāng)某一些索引值使用的非常頻繁,他會在內(nèi)存中基于B+樹再創(chuàng)建一個哈希索引.
5. B+樹索引大大減少了服務(wù)器需要掃描的數(shù)據(jù)量,索引幫助服務(wù)器避免排序和臨時表,將隨機(jī)IO轉(zhuǎn)換為順序IO.

• Mysql的其他索引方式?

1. 全文索引: MyISAM存儲引擎支持全文索引,用于查找文本中的關(guān)鍵詞,而不是直接比較是否相等,全文索引更類似于是搜索引擎做的事情.
2. 空間數(shù)據(jù)索引:MyISAM存儲引擎支持空間數(shù)據(jù)索引,可以用于地理數(shù)據(jù)存儲,空間數(shù)據(jù)索引會從所有維度來索引數(shù)據(jù)

• 什么是事務(wù)?

1. 事務(wù)就是保證一個業(yè)務(wù)的多個數(shù)據(jù)庫操作要么一起完成,要么一起失敗,典型的例子就是轉(zhuǎn)賬.

• 事務(wù)的特點?事務(wù)的實現(xiàn)原理

1. 原子性:通過Undo log實現(xiàn),在操作任何的數(shù)據(jù)之前,首先會將數(shù)據(jù)備份到一個地方,這個地方稱為Undo log,系統(tǒng)可以通過Undo log中的備份將數(shù)據(jù)恢復(fù)到事務(wù)開啟之前的狀態(tài);Undo log是一個邏輯日志,當(dāng)你delete一條記錄,undo log記錄一個insert記錄;當(dāng)insert一條記錄,Undo log記錄一個delete記錄;當(dāng)update一條記錄,記錄一條相反的update記錄.
2. 持久性:通過Redo log保證,他和Undo log相反,在事務(wù)提交之前,只要將redo log持久化即可,不需要將數(shù)據(jù)持久化.
3. 隔離性
4. 一致性

• 事務(wù)的每個隔離級別會發(fā)生什么問題?Mysql的默認(rèn)級別

1. 未提交讀,他會產(chǎn)生臟讀現(xiàn)象,當(dāng)一個進(jìn)程開啟事務(wù),修改表數(shù)據(jù),但是還沒有提交,另一個進(jìn)程開啟事務(wù),可以查看前一個進(jìn)程已經(jīng)修改了的數(shù)據(jù).這就是臟讀.
2. 可提交讀,他會產(chǎn)生不可重復(fù)度現(xiàn)象,當(dāng)一個進(jìn)程開啟了事務(wù),修改表結(jié)構(gòu),但是沒有提交,但是另一個進(jìn)程開啟事務(wù),此時看到的是未修改的數(shù)據(jù),此時前一個事務(wù)提交,此時后一個事務(wù)查看數(shù)據(jù),發(fā)現(xiàn)數(shù)據(jù)是修改了的.在一個事務(wù)中,沒有進(jìn)行任何的修改操作,兩次查詢得到的結(jié)果是不一樣的.
3. 重復(fù)讀:他會產(chǎn)生幻讀現(xiàn)象,當(dāng)一個進(jìn)程開啟事務(wù),另一個進(jìn)程也開啟事務(wù),前一個進(jìn)程插入一個數(shù)據(jù)提交,后一個進(jìn)程查詢數(shù)據(jù),還是原來的數(shù)據(jù),但是當(dāng)你插入同一個數(shù)據(jù)的時候,會報錯.
4. 串行化

• Mysql中有那些鎖?

1. 共享鎖:是將對象數(shù)據(jù)變成只讀形式,不能進(jìn)行更新,所以也被稱為只讀鎖
2. 排他鎖,當(dāng)執(zhí)行insert,delete.update的時候,其他事務(wù)不能讀取該數(shù)據(jù).

• 數(shù)據(jù)庫崩潰時事務(wù)的恢復(fù)機(jī)制?
• 什么是聚簇索引和非聚簇索引

1. 聚簇索引: 葉子節(jié)點data域記錄著完整的數(shù)據(jù)記錄,這種索引方式被稱為聚簇索引,因為無法把數(shù)據(jù)行存放在兩個不同的地方,所以一個表中只能有一個聚簇索引
2. 非聚簇索引,葉子節(jié)點的data域記錄著主鍵的值,因此進(jìn)行查找時,先查找到主鍵值,然后再到主索引中進(jìn)行查找

• 查詢優(yōu)化?怎么優(yōu)化

1. 需要查詢優(yōu)化其實最基本的原因就是訪問的數(shù)據(jù)太多,我們可以通過減少訪問的數(shù)據(jù)量的方式進(jìn)行優(yōu)化,Mysql查詢的過程是,先返回全部結(jié)果集,再進(jìn)行計算,只返回必要的行,使用limit來限制,盡量少使用select *;或者是將訪問很多次的數(shù)據(jù)放進(jìn)緩存里面
2. Mysql查詢只返回需要的數(shù)據(jù)后,也有可能為了結(jié)果集而掃描了過多的數(shù)據(jù),可以通過一些命令查看掃描的行數(shù)和返回的行數(shù),判斷性能
3. 重構(gòu)查詢方式,可以將一個復(fù)雜的查詢分為索格簡單查詢,如果一個大查詢一次性執(zhí)行的話,可能依次所著很多數(shù)據(jù),占滿整個事務(wù)日志,高金系統(tǒng)資源,阻塞很多小的但是重要的數(shù)據(jù)
4. 重構(gòu)查詢方式,可以將一個大連接查詢分解成對每一個表進(jìn)行依次單表查詢

• 一個查詢語句的執(zhí)行過程?

1. 客戶端發(fā)送一個查詢語句給服務(wù)器
2. 服務(wù)器先檢查緩存,如果有直接返回否則下一步
3. 服務(wù)器解析SQL,預(yù)處理,由優(yōu)化器生成對應(yīng)的執(zhí)行計劃
4. 調(diào)用存儲引擎API執(zhí)行計劃
5. 返回給客戶端

• SQL的視圖,游標(biāo),存儲過程,觸發(fā)器
• 視圖是一個或幾個表中導(dǎo)出的虛擬的表
• 游標(biāo)是一種從包含多條數(shù)據(jù)記錄的結(jié)果集中每次提取一條記錄的機(jī)制
• 存儲過程:對一系列SQL的預(yù)處理,預(yù)編譯SQL語句
• 觸發(fā)器:執(zhí)行完SQL語句后有什么操作。

• 數(shù)據(jù)庫的三范式

1. 第一范式,就是數(shù)據(jù)庫表的每一列都是不可分割的基本數(shù)據(jù)線,同一個列不能有多個值,比如說數(shù)據(jù)庫中有一項是用戶信息,這只能作為一個列,不能在對他進(jìn)行分成姓名和電話兩個屬性,滿足第一范式才符合關(guān)系型數(shù)據(jù)庫
2. 第二范式,滿足1范式后,要求表中的所有列,都必須依賴主鍵,而不能部分依賴.
3. 第三范式,滿足2范式后,表中的每一列只與主鍵直接相關(guān),而不能間接相關(guān).不能存在傳遞依賴,