MySQL面試重點(diǎn)篇27問(wèn)27答

26、數(shù)據(jù)庫(kù)三大范式精講

第一范式

在任何一個(gè)關(guān)系數(shù)據(jù)庫(kù)中，第一范式（1NF）是對(duì)關(guān)系模式的基本要求，不滿足第一范式（1NF）的數(shù)據(jù)庫(kù)就不是關(guān)系數(shù)據(jù)庫(kù)。所謂第一范式（1NF）是指數(shù)據(jù)庫(kù)表的每一列都是不可分割的基本數(shù)據(jù)項(xiàng)，同一列中不能有多個(gè)值，即實(shí)體中的某個(gè)屬性不能有多個(gè)值或者不能有重復(fù)的屬性。

如果出現(xiàn)重復(fù)的屬性，就可能需要定義一個(gè)新的實(shí)體，新的實(shí)體由重復(fù)的屬性構(gòu)成，新實(shí)體與原實(shí)體之間為一對(duì)多關(guān)系。在第一范式（1NF）中表的每一行只包含一個(gè)實(shí)例的信息。

簡(jiǎn)而言之，第一范式就是無(wú)重復(fù)的列。

第二范式

第二范式（2NF）是在第一范式（1NF）的基礎(chǔ)上建立起來(lái)的，即滿足第二范式（2NF）必須先滿足第一范式（1NF）。第二范式（2NF）要求數(shù)據(jù)庫(kù)表中的每個(gè)實(shí)例或行必須可以被惟一地區(qū)分。

為實(shí)現(xiàn)區(qū)分通常需要為表加上一個(gè)列，以存儲(chǔ)各個(gè)實(shí)例的惟一標(biāo)識(shí)。這個(gè)惟一屬性列被稱為主關(guān)鍵字或主鍵、主碼。第二范式（2NF）要求實(shí)體的屬性完全依賴于主關(guān)鍵字。

所謂完全依賴是指不能存在僅依賴主關(guān)鍵字一部分的屬性，如果存在，那么這個(gè)屬性和主關(guān)鍵字的這一部分應(yīng)該分離出來(lái)形成一個(gè)新的實(shí)體，新實(shí)體與原實(shí)體之間是一對(duì)多的關(guān)系。為實(shí)現(xiàn)區(qū)分通常需要為表加上一個(gè)列，以存儲(chǔ)各個(gè)實(shí)例的惟一標(biāo)識(shí)。

簡(jiǎn)而言之，第二范式就是非主屬性非部分依賴于主關(guān)鍵字。

第三范式

滿足第三范式（3NF）必須先滿足第二范式（2NF）。簡(jiǎn)而言之，第三范式（3NF）要求一個(gè)數(shù)據(jù)庫(kù)表中不包含已在其它表中已包含的非主關(guān)鍵字信息。

例如，存在一個(gè)部門信息表，其中每個(gè)部門有部門編號(hào)（dept_id）、部門名稱、部門簡(jiǎn)介等信息。那么在員工信息表中列出部門編號(hào)后就不能再將部門名稱、部門簡(jiǎn)介等與部門有關(guān)的信息再加入員工信息表中。如果不存在部門信息表，則根據(jù)第三范式（3NF）也應(yīng)該構(gòu)建它，否則就會(huì)有大量的數(shù)據(jù)冗余。

簡(jiǎn)而言之，第三范式就是屬性不依賴于其它非主屬性。

27、數(shù)據(jù)庫(kù)三大范式精要總結(jié)

（1）簡(jiǎn)單歸納：

第一范式（1NF）：字段不可分；

第二范式（2NF）：有主鍵，非主鍵字段依賴主鍵；

第三范式（3NF）：非主鍵字段不能相互依賴。

（2）解釋：

1NF：原子性。字段不可再分,否則就不是關(guān)系數(shù)據(jù)庫(kù);；

2NF：唯一性 。一個(gè)表只說(shuō)明一個(gè)事物；

3NF：每列都與主鍵有直接關(guān)系，不存在傳遞依賴。

28、MySQL常見的存儲(chǔ)引擎InnoDB、MyISAM的區(qū)別？適用場(chǎng)景分別是？

1）事務(wù)：MyISAM不支持，InnoDB支持

2）鎖級(jí)別：MyISAM 表級(jí)鎖，InnoDB 行級(jí)鎖及外鍵約束

3）MyISAM存儲(chǔ)表的總行數(shù)；InnoDB不存儲(chǔ)總行數(shù)；

4）MyISAM采用非聚集索引，B+樹葉子存儲(chǔ)指向數(shù)據(jù)文件的指針。InnoDB主鍵索引采用聚集索引，B+樹葉子存儲(chǔ)數(shù)據(jù)

適用場(chǎng)景：

MyISAM適合：插入不頻繁，查詢非常頻繁，如果執(zhí)行大量的SELECT，MyISAM是更好的選擇， 沒(méi)有事務(wù)。

InnoDB適合：可靠性要求比較高，或者要求事務(wù)；表更新和查詢都相當(dāng)?shù)念l繁， 大量的INSERT或UPDATE

29、事務(wù)四大特性（ACID）原子性、一致性、隔離性、持久性？

第一種回答

原子性：一個(gè)事務(wù)（transaction）中的所有操作，要么全部完成，要么全部不完成，不會(huì)結(jié)束在中間某個(gè)環(huán)節(jié)。。事務(wù)在執(zhí)行過(guò)程中發(fā)生錯(cuò)誤，會(huì)被恢復(fù)（Rollback）到事務(wù)開始前的狀態(tài)，就像這個(gè)事務(wù)從來(lái)沒(méi)有執(zhí)行過(guò)一樣。一致性：在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫(kù)的完整性沒(méi)有被破壞。這表示寫入的資料必須完全符合所有的預(yù)設(shè)規(guī)則，這包含資料的精確度、串聯(lián)性以及后續(xù)數(shù)據(jù)庫(kù)可以自發(fā)性地完成預(yù)定的工作。隔離性：數(shù)據(jù)庫(kù)允許多個(gè)并發(fā)事務(wù)同時(shí)對(duì)其數(shù)據(jù)進(jìn)行讀寫和修改的能力，隔離性可以防止多個(gè)事務(wù)并發(fā)執(zhí)行時(shí)由于交叉執(zhí)行而導(dǎo)致數(shù)據(jù)的不一致。事務(wù)隔離分為不同級(jí)別，包括讀未提交（Read uncommitted）、讀提交（read committed）、可重復(fù)讀（repeatable read）和串行化（Serializable）。持久性：事務(wù)處理結(jié)束后，對(duì)數(shù)據(jù)的修改就是永久的，即便系統(tǒng)故障也不會(huì)丟失。

第二種回答

原子性（Atomicity）

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

一致性（Consistency）

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

隔離性（Isolation）

• 隔離性是當(dāng)多個(gè)用戶并發(fā)訪問(wèn)數(shù)據(jù)庫(kù)時(shí)，比如操作同一張表時(shí)，數(shù)據(jù)庫(kù)為每一個(gè)用戶開啟的事務(wù)，不能被其他事務(wù)的操作所干擾，多個(gè)并發(fā)事務(wù)之間要相互隔離。

同一時(shí)間，只允許一個(gè)事務(wù)請(qǐng)求同一數(shù)據(jù)，不同的事務(wù)之間彼此沒(méi)有任何干擾。比如A正在從一張銀行卡中取錢，在A取錢的過(guò)程結(jié)束前，B不能向這張卡轉(zhuǎn)賬。關(guān)于事務(wù)的隔離性數(shù)據(jù)庫(kù)提供了多種隔離級(jí)別，稍后會(huì)介紹到。

持久性（Durability）

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

30、SQL中的NOW()和CURRENT_DATE()兩個(gè)函數(shù)有什么區(qū)別？

NOW（）命令用于顯示當(dāng)前年份，月份，日期，小時(shí)，分鐘和秒。CURRENT_DATE（）僅顯示當(dāng)前年份，月份和日期。

31、什么是聚合索引 ？

聚簇索引就是按照拼音查詢，非聚簇索引就是按照偏旁等來(lái)進(jìn)行查詢。

其實(shí)，我們的漢語(yǔ)字典的正文本身就是一個(gè)聚集索引。比如，我們要查"安"字，就會(huì)很自然地翻開字典的前幾頁(yè)，因?yàn)?安"的拼音是"an"，而按照拼音排序 漢字的字典是以英文字母"a"開頭并以"z"結(jié)尾的，那么"安"字就自然地排在字典的前部。如果您翻完了所有以"a"開頭的部分仍然找不到這個(gè)字，那么就 說(shuō)明您的字典中沒(méi)有這個(gè)字；同樣的，如果查"張"字，那您也會(huì)將您的字典翻到最后部分，因?yàn)?張"的拼音是"zhang"。也就是說(shuō)，字典的正文部分本身 就是一個(gè)目錄，您不需要再去查其他目錄來(lái)找到您需要找的內(nèi)容。

我們把這種正文內(nèi)容本身就是一種按照一定規(guī)則排列的目錄稱為"聚集索引"

32、什么是非聚合索引?

如果您認(rèn)識(shí)某個(gè)字，您可以快速地從自動(dòng)中查到這個(gè)字。但您也可能會(huì)遇到您不認(rèn)識(shí)的字，不知道它的發(fā)音，這時(shí)候，您就不能按照剛才的方法找到您要查的字，而 需要去根據(jù)"偏旁部首"查到您要找的字，然后根據(jù)這個(gè)字后的頁(yè)碼直接翻到某頁(yè)來(lái)找到您要找的字。

但您結(jié)合"部首目錄"和"檢字表"而查到的字的排序并不是 真正的正文的排序方法，比如您查"張"字，我們可以看到在查部首之后的檢字表中"張"的頁(yè)碼是672頁(yè)，檢字表中"張"的上面是"馳"字，但頁(yè)碼卻是63 頁(yè)，"張"的下面是"弩"字，頁(yè)面是390頁(yè)。

很顯然，這些字并不是真正的分別位于"張"字的上下方，現(xiàn)在您看到的連續(xù)的"馳、張、弩"三字實(shí)際上就是他 們?cè)诜蔷奂饕械呐判?，是字典正文中的字在非聚集索引中的映射?/span>

我們可以通過(guò)這種方式來(lái)找到您所需要的字，但它需要兩個(gè)過(guò)程，先找到目錄中的結(jié)果，然后 再翻到您所需要的頁(yè)碼。

我們把這種目錄純粹是目錄，正文純粹是正文的排序方式稱為"非聚集索引"。

33、聚集索引與非聚集索引的區(qū)別是什么?

非聚集索引和聚集索引的區(qū)別在于， 通過(guò)聚集索引可以查到需要查找的數(shù)據(jù)， 而通過(guò)非聚集索引可以查到記錄對(duì)應(yīng)的主鍵值 ， 再使用主鍵的值通過(guò)聚集索引查找到需要的數(shù)據(jù)。聚集索引和非聚集索引的根本區(qū)別是表記錄的排列順序和與索引的排列順序是否一致。

聚集索引（Innodb）的葉節(jié)點(diǎn)就是數(shù)據(jù)節(jié)點(diǎn)，而非聚集索引(MyisAM)的葉節(jié)點(diǎn)仍然是索引節(jié)點(diǎn)，只不過(guò)其包含一個(gè)指向?qū)?yīng)數(shù)據(jù)塊的指針。

34、創(chuàng)建索引時(shí)需要注意什么？

非空字段：應(yīng)該指定列為NOT NULL，除非你想存儲(chǔ)NULL。在 MySQL 中，含有空值的列很難進(jìn)行查詢優(yōu)化，因?yàn)樗鼈兪沟盟饕⑺饕慕y(tǒng)計(jì)信息以及比較運(yùn)算更加復(fù)雜。你應(yīng)該用0、一個(gè)特殊的值或者一個(gè)空串代替空值；

取值離散大的字段：（變量各個(gè)取值之間的差異程度）的列放到聯(lián)合索引的前面，可以通過(guò)count()函數(shù)查看字段的差異值，返回值越大說(shuō)明字段的唯一值越多字段的離散程度高；

索引字段越小越好：數(shù)據(jù)庫(kù)的數(shù)據(jù)存儲(chǔ)以頁(yè)為單位一頁(yè)存儲(chǔ)的數(shù)據(jù)越多一次IO操作獲取的數(shù)據(jù)越大效率越高。唯一、不為空、經(jīng)常被查詢的字段 的字段適合建索引

35、MySQL中CHAR和VARCHAR的區(qū)別有哪些？

• char的長(zhǎng)度是不可變的，用空格填充到指定長(zhǎng)度大小，而varchar的長(zhǎng)度是可變的。
• char的存取數(shù)度還是要比varchar要快得多
• char的存儲(chǔ)方式是：對(duì)英文字符（ASCII）占用1個(gè)字節(jié)，對(duì)一個(gè)漢字占用兩個(gè)字節(jié)。varchar的存儲(chǔ)方式是：對(duì)每個(gè)英文字符占用2個(gè)字節(jié)，漢字也占用2個(gè)字節(jié)。

36、MySQL 索引使用的注意事項(xiàng)

MySQL 索引通常是被用于提高 WHERE 條件的數(shù)據(jù)行匹配時(shí)的搜索速度，在索引的使用過(guò)程中，存在一些使用細(xì)節(jié)和注意事項(xiàng)。

函數(shù)，運(yùn)算，否定操作符，連接條件，多個(gè)單列索引，最左前綴原則，范圍查詢，不會(huì)包含有NULL值的列，like 語(yǔ)句不要在列上使用函數(shù)和進(jìn)行運(yùn)算

1）不要在列上使用函數(shù)，這將導(dǎo)致索引失效而進(jìn)行全表掃描。

select * from news where year(publish_time) < 2017

為了使用索引，防止執(zhí)行全表掃描，可以進(jìn)行改造。

select * from news where publish_time < '2017-01-01' 

還有一個(gè)建議，不要在列上進(jìn)行運(yùn)算，這也將導(dǎo)致索引失效而進(jìn)行全表掃描。

select * from news where id / 100 = 1

為了使用索引，防止執(zhí)行全表掃描，可以進(jìn)行改造。

select * from news where id = 1 * 100

2）盡量避免使用 != 或 not in或 <> 等否定操作符

應(yīng)該盡量避免在 where 子句中使用 != 或 not in 或 <> 操作符，因?yàn)檫@幾個(gè)操作符都會(huì)導(dǎo)致索引失效而進(jìn)行全表掃描。盡量避免使用 or 來(lái)連接條件 應(yīng)該盡量避免在 where 子句中使用 or 來(lái)連接條件，因?yàn)檫@會(huì)導(dǎo)致索引失效而進(jìn)行全表掃描。

select * from news where id = 1 or id = 2

3）多個(gè)單列索引并不是最佳選擇

MySQL 只能使用一個(gè)索引，會(huì)從多個(gè)索引中選擇一個(gè)限制最為嚴(yán)格的索引，因此，為多個(gè)列創(chuàng)建單列索引，并不能提高 MySQL 的查詢性能。假設(shè)，有兩個(gè)單列索引，分別為 news_year_idx(news_year) 和 news_month_idx(news_month)。現(xiàn)在，有一個(gè)場(chǎng)景需要針對(duì)資訊的年份和月份進(jìn)行查詢，那么，SQL 語(yǔ)句可以寫成：

select * from news where news_year = 2017 and news_month = 1

事實(shí)上，MySQL 只能使用一個(gè)單列索引。為了提高性能，可以使用復(fù)合索引 news_year_month_idx(news_year, news_month) 保證 news_year 和 news_month 兩個(gè)列都被索引覆蓋。

4）復(fù)合索引的最左前綴原則

復(fù)合索引遵守“最左前綴”原則，即在查詢條件中使用了復(fù)合索引的第一個(gè)字段，索引才會(huì)被使用。因此，在復(fù)合索引中索引列的順序至關(guān)重要。如果不是按照索引的最左列開始查找，則無(wú)法使用索引。假設(shè)，有一個(gè)場(chǎng)景只需要針對(duì)資訊的月份進(jìn)行查詢，那么，SQL 語(yǔ)句可以寫成：

select * from news where news_month = 1

此時(shí)，無(wú)法使用 news_year_month_idx(news_year, news_month) 索引，因?yàn)樽袷亍白钭笄熬Y”原則，在查詢條件中沒(méi)有使用復(fù)合索引的第一個(gè)字段，索引是不會(huì)被使用的。

5）覆蓋索引的好處

如果一個(gè)索引包含所有需要的查詢的字段的值，直接根據(jù)索引的查詢結(jié)果返回?cái)?shù)據(jù)，而無(wú)需讀表，能夠極大的提高性能。因此，可以定義一個(gè)讓索引包含的額外的列，即使這個(gè)列對(duì)于索引而言是無(wú)用的。

6）范圍查詢對(duì)多列查詢的影響

查詢中的某個(gè)列有范圍查詢，則其右邊所有列都無(wú)法使用索引優(yōu)化查找。舉個(gè)例子，假設(shè)有一個(gè)場(chǎng)景需要查詢本周發(fā)布的資訊文章，其中的條件是必須是啟用狀態(tài)，且發(fā)布時(shí)間在這周內(nèi)。那么，SQL 語(yǔ)句可以寫成：

select * from news where publish_time >= '2017-01-02' and publish_time <= '2017-01-08' and enable = 1

這種情況下，因?yàn)榉秶樵儗?duì)多列查詢的影響，將導(dǎo)致 news_publish_idx(publish_time, enable) 索引中 publish_time 右邊所有列都無(wú)法使用索引優(yōu)化查找。換句話說(shuō)，news_publish_idx(publish_time, enable) 索引等價(jià)于 news_publish_idx(publish_time) 。對(duì)于這種情況，我的建議：對(duì)于范圍查詢，務(wù)必要注意它帶來(lái)的副作用，并且盡量少用范圍查詢，可以通過(guò)曲線救國(guó)的方式滿足業(yè)務(wù)場(chǎng)景。例如，上面案例的需求是查詢本周發(fā)布的資訊文章，因此可以創(chuàng)建一個(gè)news_weekth 字段用來(lái)存儲(chǔ)資訊文章的周信息，使得范圍查詢變成普通的查詢，SQL 可以改寫成：

select * from news where news_weekth = 1 and enable = 1

然而，并不是所有的范圍查詢都可以進(jìn)行改造，對(duì)于必須使用范圍查詢但無(wú)法改造的情況，我的建議：不必試圖用 SQL 來(lái)解決所有問(wèn)題，可以使用其他數(shù)據(jù)存儲(chǔ)技術(shù)控制時(shí)間軸，例如 Redis 的 SortedSet 有序集合保存時(shí)間，或者通過(guò)緩存方式緩存查詢結(jié)果從而提高性能。

7）索引不會(huì)包含有NULL值的列

只要列中包含有 NULL 值都將不會(huì)被包含在索引中，復(fù)合索引中只要有一列含有 NULL值，那么這一列對(duì)于此復(fù)合索引就是無(wú)效的。因此，在數(shù)據(jù)庫(kù)設(shè)計(jì)時(shí)，除非有一個(gè)很特別的原因使用 NULL 值，不然盡量不要讓字段的默認(rèn)值為 NULL。

8）隱式轉(zhuǎn)換的影響

當(dāng)查詢條件左右兩側(cè)類型不匹配的時(shí)候會(huì)發(fā)生隱式轉(zhuǎn)換，隱式轉(zhuǎn)換帶來(lái)的影響就是可能導(dǎo)致索引失效而進(jìn)行全表掃描。下面的案例中，date_str 是字符串，然而匹配的是整數(shù)類型，從而發(fā)生隱式轉(zhuǎn)換。

select * from news where date_str = 201701

因此，要謹(jǐn)記隱式轉(zhuǎn)換的危害，時(shí)刻注意通過(guò)同類型進(jìn)行比較。

9）like 語(yǔ)句的索引失效問(wèn)題

like 的方式進(jìn)行查詢，在 like “value%” 可以使用索引，但是對(duì)于 like “%value%” 這樣的方式，執(zhí)行全表查詢，這在數(shù)據(jù)量小的表，不存在性能問(wèn)題，但是對(duì)于海量數(shù)據(jù)，全表掃描是非?？膳碌氖虑椤Ｋ?，根據(jù)業(yè)務(wù)需求，考慮使用 ElasticSearch 或 Solr 是個(gè)不錯(cuò)的方案。

37、MySQL中有哪些索引？有什么特點(diǎn)？

• 普通索引：僅加速查詢
• 唯一索引：加速查詢 + 列值唯一（可以有null）
• 主鍵索引：加速查詢 + 列值唯一（不可以有null）+ 表中只有一個(gè)
• 組合索引：多列值組成一個(gè)索引，專門用于組合搜索，其效率大于索引合并
• 全文索引：對(duì)文本的內(nèi)容進(jìn)行分詞，進(jìn)行搜索
• 索引合并：使用多個(gè)單列索引組合搜索
• 覆蓋索引：select的數(shù)據(jù)列只用從索引中就能夠取得，不必讀取數(shù)據(jù)行，換句話說(shuō)查詢列要被所建的索引覆蓋
• 聚簇索引：表數(shù)據(jù)是和主鍵一起存儲(chǔ)的，主鍵索引的葉結(jié)點(diǎn)存儲(chǔ)行數(shù)據(jù)(包含了主鍵值)，二級(jí)索引的葉結(jié)點(diǎn)存儲(chǔ)行的主鍵值。使用的是B+樹作為索引的存儲(chǔ)結(jié)構(gòu)，非葉子節(jié)點(diǎn)都是索引關(guān)鍵字，但非葉子節(jié)點(diǎn)中的關(guān)鍵字中不存儲(chǔ)對(duì)應(yīng)記錄的具體內(nèi)容或內(nèi)容地址。葉子節(jié)點(diǎn)上的數(shù)據(jù)是主鍵與具體記錄(數(shù)據(jù)內(nèi)容)

38、既然索引有那么多優(yōu)點(diǎn)，為什么不對(duì)表總的每一列創(chuàng)建一個(gè)索引呢？

• 當(dāng)對(duì)表中的數(shù)據(jù)進(jìn)行增加、刪除和修改的時(shí)候， 索引也要?jiǎng)討B(tài)的維護(hù)，這樣就降低了數(shù)據(jù)的維護(hù)速度。
• 索引需要占物理空間，除了數(shù)據(jù)表占數(shù)據(jù)空間之外，每一個(gè)索引還要占一定的物理空間，如果要建立簇索引，那么需要的空間就會(huì)更大。
• 創(chuàng)建索引和維護(hù)索引要耗費(fèi)時(shí)間，這種時(shí)間隨著數(shù)據(jù)量的增加而增加

39、索引如何提高查詢速度的

將無(wú)序的數(shù)據(jù)變成相對(duì)有序的數(shù)據(jù)（就像查有目的一樣）

40、使用索引的注意事項(xiàng)

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

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

• 將打算加索引的列設(shè)置為NOT NULL，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描

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

• 避免where子句中對(duì)字段施加函數(shù)，這會(huì)造成無(wú)法命中索引

• 在中到大型表索引都是非常有效的，但是特大型表的維護(hù)開銷會(huì)很大，不適合建索引，建立用邏輯索引

• 在經(jīng)常用到連續(xù)的列上，這些列主要是由一些外鍵，可以加快連接的速度

• 與業(yè)務(wù)無(wú)關(guān)時(shí)多使用邏輯主鍵，也就是自增主鍵在使用InnoDB時(shí)使用與業(yè)務(wù)無(wú)關(guān)的自增主鍵作為主鍵，即使用邏輯主鍵，而不要使用業(yè)務(wù)主鍵。

• 刪除長(zhǎng)期未使用的索引，不用的索引的存在會(huì)造成不必要的性能損耗

• 在使用limit offset查詢緩存時(shí)，可以借助索引來(lái)提高性能。

41、增加B+樹的路數(shù)可以降低樹的高度，那么無(wú)限增加樹的路數(shù)是不是可以有最優(yōu)的查找效率？

不可以。因?yàn)檫@樣會(huì)形成一個(gè)有序數(shù)組，文件系統(tǒng)和數(shù)據(jù)庫(kù)的索引都是存在硬盤上的，并且如果數(shù)據(jù)量大的話，不一定能一次性加載到內(nèi)存中。有序數(shù)組沒(méi)法一次性加載進(jìn)內(nèi)存，這時(shí)候B+樹的多路存儲(chǔ)威力就出來(lái)了，可以每次加載B+樹的一個(gè)結(jié)點(diǎn)，然后一步步往下找，

42、說(shuō)一下數(shù)據(jù)庫(kù)表鎖和行鎖吧

表鎖

不會(huì)出現(xiàn)死鎖，發(fā)生鎖沖突幾率高，并發(fā)低。

MyISAM在執(zhí)行查詢語(yǔ)句（select）前，會(huì)自動(dòng)給涉及的所有表加讀鎖，在執(zhí)行增刪改操作前，會(huì)自動(dòng)給涉及的表加寫鎖。

MySQL的表級(jí)鎖有兩種模式：表共享讀鎖和表獨(dú)占寫鎖。

讀鎖會(huì)阻塞寫，寫鎖會(huì)阻塞讀和寫

• 對(duì)MyISAM表的讀操作，不會(huì)阻塞其它進(jìn)程對(duì)同一表的讀請(qǐng)求，但會(huì)阻塞對(duì)同一表的寫請(qǐng)求。只有當(dāng)讀鎖釋放后，才會(huì)執(zhí)行其它進(jìn)程的寫操作。
• 對(duì)MyISAM表的寫操作，會(huì)阻塞其它進(jìn)程對(duì)同一表的讀和寫操作，只有當(dāng)寫鎖釋放后，才會(huì)執(zhí)行其它進(jìn)程的讀寫操作。

MyISAM不適合做寫為主表的引擎，因?yàn)閷戞i后，其它線程不能做任何操作，大量的更新會(huì)使查詢很難得到鎖，從而造成永遠(yuǎn)阻塞。

行鎖

會(huì)出現(xiàn)死鎖，發(fā)生鎖沖突幾率低，并發(fā)高。

在MySQL的InnoDB引擎支持行鎖，與Oracle不同，MySQL的行鎖是通過(guò)索引加載的，也就是說(shuō)，行鎖是加在索引響應(yīng)的行上的，要是對(duì)應(yīng)的SQL語(yǔ)句沒(méi)有走索引，則會(huì)全表掃描，行鎖則無(wú)法實(shí)現(xiàn)，取而代之的是表鎖，此時(shí)其它事務(wù)無(wú)法對(duì)當(dāng)前表進(jìn)行更新或插入操作。

行鎖的實(shí)現(xiàn)需要注意：

• 行鎖必須有索引才能實(shí)現(xiàn)，否則會(huì)自動(dòng)鎖全表，那么就不是行鎖了。
• 兩個(gè)事務(wù)不能鎖同一個(gè)索引。
• insert，delete，update在事務(wù)中都會(huì)自動(dòng)默認(rèn)加上排它鎖。

行鎖的適用場(chǎng)景：

A用戶消費(fèi)，service層先查詢?cè)撚脩舻馁~戶余額，若余額足夠，則進(jìn)行后續(xù)的扣款操作；這種情況查詢的時(shí)候應(yīng)該對(duì)該記錄進(jìn)行加鎖。

否則，B用戶在A用戶查詢后消費(fèi)前先一步將A用戶賬號(hào)上的錢轉(zhuǎn)走，而此時(shí)A用戶已經(jīng)進(jìn)行了用戶余額是否足夠的判斷，則可能會(huì)出現(xiàn)余額已經(jīng)不足但卻扣款成功的情況。

為了避免此情況，需要在A用戶操作該記錄的時(shí)候進(jìn)行for update加鎖

43、SQL語(yǔ)法中內(nèi)連接、自連接、外連接（左、右、全）、交叉連接的區(qū)別分別是什么？

內(nèi)連接：只有兩個(gè)元素表相匹配的才能在結(jié)果集中顯示。

外連接：左外連接: 左邊為驅(qū)動(dòng)表，驅(qū)動(dòng)表的數(shù)據(jù)全部顯示，匹配表的不匹配的不會(huì)顯示。

右外連接:右邊為驅(qū)動(dòng)表，驅(qū)動(dòng)表的數(shù)據(jù)全部顯示，匹配表的不匹配的不會(huì)顯示。全外連接：連接的表中不匹配的數(shù)據(jù)全部會(huì)顯示出來(lái)。

交叉連接：笛卡爾效應(yīng)，顯示的結(jié)果是鏈接表數(shù)的乘積。

44、你知道哪些數(shù)據(jù)庫(kù)結(jié)構(gòu)優(yōu)化的手段？

• 范式優(yōu)化：比如消除冗余（節(jié)省空間。。）
• 反范式優(yōu)化：比如適當(dāng)加冗余等（減少join）
• 限定數(shù)據(jù)的范圍：務(wù)必禁止不帶任何限制數(shù)據(jù)范圍條件的查詢語(yǔ)句。比如：我們當(dāng)用戶在查詢訂單歷史的時(shí)候，我們可以控制在一個(gè)月的范圍內(nèi)。
• 讀/寫分離：經(jīng)典的數(shù)據(jù)庫(kù)拆分方案，主庫(kù)負(fù)責(zé)寫，從庫(kù)負(fù)責(zé)讀；
• 拆分表：分區(qū)將數(shù)據(jù)在物理上分隔開，不同分區(qū)的數(shù)據(jù)可以制定保存在處于不同磁盤上的數(shù)據(jù)文件里。這樣，當(dāng)對(duì)這個(gè)表進(jìn)行查詢時(shí)，只需要在表分區(qū)中進(jìn)行掃描，而不必進(jìn)行全表掃描，明顯縮短了查詢時(shí)間，另外處于不同磁盤的分區(qū)也將對(duì)這個(gè)表的數(shù)據(jù)傳輸分散在不同的磁盤I/O，一個(gè)精心設(shè)置的分區(qū)可以將數(shù)據(jù)傳輸對(duì)磁盤I/O競(jìng)爭(zhēng)均勻地分散開。對(duì)數(shù)據(jù)量大的時(shí)時(shí)表可采取此方法?？砂丛伦詣?dòng)建表分區(qū)。

45、數(shù)據(jù)庫(kù)優(yōu)化中有一個(gè)比較常用的手段就是把數(shù)據(jù)表進(jìn)行拆分，關(guān)于拆分?jǐn)?shù)據(jù)表你了解哪些？

拆分其實(shí)又分垂直拆分和水平拆分

案例：簡(jiǎn)單購(gòu)物系統(tǒng)暫設(shè)涉及如下表：

1.產(chǎn)品表（數(shù)據(jù)量10w，穩(wěn)定）

2.訂單表（數(shù)據(jù)量200w，且有增長(zhǎng)趨勢(shì)）

3.用戶表 （數(shù)據(jù)量100w，且有增長(zhǎng)趨勢(shì)）

以 MySQL 為例講述下水平拆分和垂直拆分，MySQL能容忍的數(shù)量級(jí)在百萬(wàn)靜態(tài)數(shù)據(jù)可以到千萬(wàn)

垂直拆分

解決問(wèn)題：表與表之間的io競(jìng)爭(zhēng)

不解決問(wèn)題：?jiǎn)伪碇袛?shù)據(jù)量增長(zhǎng)出現(xiàn)的壓力

方案：把產(chǎn)品表和用戶表放到一個(gè)server上 訂單表單獨(dú)放到一個(gè)server上

水平拆分

解決問(wèn)題：?jiǎn)伪碇袛?shù)據(jù)量增長(zhǎng)出現(xiàn)的壓力

不解決問(wèn)題：表與表之間的io爭(zhēng)奪

方案：用戶表 通過(guò)性別拆分為男用戶表和女用戶表，訂單表 通過(guò)已完成和完成中拆分為已完成訂單和未完成訂單，產(chǎn)品表 未完成訂單放一個(gè)server上，已完成訂單表盒男用戶表放一個(gè)server上，女用戶表放一個(gè)server上(女的愛購(gòu)物 哈哈)。

46、為什么MySQL索引要使用B+樹，而不是B樹或者紅黑樹？

我們?cè)贛ySQL中的數(shù)據(jù)一般是放在磁盤中的，讀取數(shù)據(jù)的時(shí)候肯定會(huì)有訪問(wèn)磁盤的操作，磁盤中有兩個(gè)機(jī)械運(yùn)動(dòng)的部分，分別是盤片旋轉(zhuǎn)和磁臂移動(dòng)。盤片旋轉(zhuǎn)就是我們市面上所提到的多少轉(zhuǎn)每分鐘，而磁盤移動(dòng)則是在盤片旋轉(zhuǎn)到指定位置以后，移動(dòng)磁臂后開始進(jìn)行數(shù)據(jù)的讀寫。那么這就存在一個(gè)定位到磁盤中的塊的過(guò)程，而定位是磁盤的存取中花費(fèi)時(shí)間比較大的一塊，畢竟機(jī)械運(yùn)動(dòng)花費(fèi)的時(shí)候要遠(yuǎn)遠(yuǎn)大于電子運(yùn)動(dòng)的時(shí)間。當(dāng)大規(guī)模數(shù)據(jù)存儲(chǔ)到磁盤中的時(shí)候，顯然定位是一個(gè)非?；ㄙM(fèi)時(shí)間的過(guò)程，但是我們可以通過(guò)B樹進(jìn)行優(yōu)化，提高磁盤讀取時(shí)定位的效率。

為什么B類樹可以進(jìn)行優(yōu)化呢？我們可以根據(jù)B類樹的特點(diǎn)，構(gòu)造一個(gè)多階的B類樹，然后在盡量多的在結(jié)點(diǎn)上存儲(chǔ)相關(guān)的信息，保證層數(shù)（樹的高度）盡量的少，以便后面我們可以更快的找到信息，磁盤的I/O操作也少一些，而且B類樹是平衡樹，每個(gè)結(jié)點(diǎn)到葉子結(jié)點(diǎn)的高度都是相同，這也保證了每個(gè)查詢是穩(wěn)定的。

特別地：只有B-樹和B+樹，這里的B-樹是叫B樹，不是B減樹，沒(méi)有B減樹的說(shuō)法。

47、為什么MySQL索引采用B+樹而不用hash表和B樹？

• 利用Hash需要把數(shù)據(jù)全部 加載到內(nèi)存中，如果數(shù)據(jù)量大，是一件很 消耗內(nèi)存的事，而采用B+樹，是基于 按照節(jié)點(diǎn)分段加載，由此減少內(nèi)存消耗。
• 和業(yè)務(wù)場(chǎng)景有段， 對(duì)于唯一查找（查找一個(gè)值），Hash確實(shí)更快， 但數(shù)據(jù)庫(kù)中經(jīng)常查詢多條數(shù)據(jù)，這時(shí)候由于B+數(shù)據(jù)的有序性，與葉子節(jié)點(diǎn)又有鏈表相連，他的查詢效率會(huì)比Hash快的多。
• b+樹的 非葉子節(jié)點(diǎn)不保存數(shù)據(jù)， 只保存子樹的臨界值（最大或者最?。酝瑯哟笮〉墓?jié)點(diǎn)， b+樹相對(duì)于b樹能夠有更多的分支，使得這棵樹更加矮胖，查詢時(shí)做的IO操作次數(shù)也更少。

48、MySQL中存儲(chǔ)索引用到的數(shù)據(jù)結(jié)構(gòu)是B+樹，B+樹的查詢時(shí)間跟樹的高度有關(guān)，是log(n)，如果用hash存儲(chǔ)，那么查詢時(shí)間是O(1)。既然hash比B+樹更快，為什么MySQL用B+樹來(lái)存儲(chǔ)索引呢？

一、從內(nèi)存角度上說(shuō)，數(shù)據(jù)庫(kù)中的索引一般是在磁盤上，數(shù)據(jù)量大的情況可能無(wú)法一次性裝入內(nèi)存，B+樹的設(shè)計(jì)可以允許數(shù)據(jù)分批加載。

二、從業(yè)務(wù)場(chǎng)景上說(shuō)，如果只選擇一個(gè)數(shù)據(jù)那確實(shí)是hash更快，但是數(shù)據(jù)庫(kù)中經(jīng)常會(huì)選中多條，這時(shí)候由于B+樹索引有序，并且又有鏈表相連，它的查詢效率比hash就快很多了。