這篇文章主要是針對開發人員的,所以不涉及到MySQL的服務部署等操作,主要包括索引,事務,存儲引擎，優化等方面。

一、索引

關于MySQL的索引,之前實際上也介紹了很多次，這里主要介紹一些常用的面試題。

1. 什么是索引?索引是個什么樣的數據結構呢?

索引是一種數據結構,可以幫助我們快速的進行數據的查找。

索引的數據結構和具體存儲引擎的實現有關, 在MySQL中使用較多的索引有Hash索引,B+樹索引等,而我們經常使用的InnoDB存儲引擎的默認索引實現為:B+樹索引。

2. Hash索引和B+樹所有有什么區別或者說優劣呢?

首先要知道Hash索引和B+樹索引的底層實現原理：

hash索引底層就是hash表,進行查找時,調用一次hash函數就可以獲取到相應的鍵值,之后進行回表查詢獲得實際數據.B+樹底層實現是多路平衡查找樹.對于每一次的查詢都是從根節點出發,查找到葉子節點方可以獲得所查鍵值,然后根據查詢判斷是否需要回表查詢數據。

那么可以看出他們有以下的不同：

• hash索引進行等值查詢更快(一般情況下),但是卻無法進行范圍查詢，因為在hash索引中經過hash函數建立索引之后,索引的順序與原順序無法保持一致,不能支持范圍查詢.而B+樹的的所有節點皆遵循(左節點小于父節點,右節點大于父節點,多叉樹也類似),天然支持范圍.
• hash索引不支持使用索引進行排序,原理同上.
• hash索引不支持模糊查詢以及多列索引的最左前綴匹配.原理也是因為hash函數的不可預測.AAAA和AAAAB的索引沒有相關性.
• hash索引任何時候都避免不了回表查詢數據,而B+樹在符合某些條件(聚簇索引,覆蓋索引等)的時候可以只通過索引完成查詢.
• hash索引雖然在等值查詢上較快,但是不穩定.性能不可預測,當某個鍵值存在大量重復的時候,發生hash碰撞,此時效率可能極差.而B+樹的查詢效率比較穩定,對于所有的查詢都是從根節點到葉子節點,且樹的高度較低.

因此,在大多數情況下,直接選擇B+樹索引可以獲得穩定且較好的查詢速度.而不需要使用hash索引.

3. B+樹在滿足聚簇索引和覆蓋索引的時候不需要回表查詢數據,那么什么是聚簇索引?

在B+樹的索引中,葉子節點可能存儲了當前的key值,也可能存儲了當前的key值以及整行的數據,這就是聚簇索引和非聚簇索引. 在InnoDB中,只有主鍵索引是聚簇索引,如果沒有主鍵,則挑選一個唯一鍵建立聚簇索引.如果沒有唯一鍵,則隱式的生成一個鍵來建立聚簇索引.

當查詢使用聚簇索引時,在對應的葉子節點,可以獲取到整行數據,因此不用再次進行回表查詢.

4. 非聚簇索引一定會回表查詢嗎?

不一定,這涉及到查詢語句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再進行回表查詢.

舉個簡單的例子,假設在員工表的年齡上建立了索引,那么當進行select age from employee where age < 20的查詢時,在索引的葉子節點上,已經包含了age信息,不會再次進行回表查詢.

5. 在建立索引的時候,都有哪些需要考慮的因素呢?

建立索引的時候一般要考慮到字段的使用頻率,經常作為條件進行查詢的字段比較適合.如果需要建立聯合索引的話,還需要考慮聯合索引中的順序.此外也要考慮其他方面,比如防止過多的所有對表造成太大的壓力.這些都和實際的表結構以及查詢方式有關.

6. 聯合索引是什么?為什么需要注意聯合索引中的順序?

MySQL可以使用多個字段同時建立一個索引,叫做聯合索引.在聯合索引中,如果想要命中索引,需要按照建立索引時的字段順序挨個使用,否則無法命中索引.

具體原因為：

MySQL使用索引時需要索引有序,假設現在建立了"name,age,school"的聯合索引,那么索引的排序為: 先按照name排序,如果name相同,則按照age排序,如果age的值也相等,則按照school進行排序.

當進行查詢時,此時索引僅僅按照name嚴格有序,因此必須首先使用name字段進行等值查詢,之后對于匹配到的列而言,其按照age字段嚴格有序,此時可以使用age字段用做索引查找,,,以此類推.因此在建立聯合索引的時候應該注意索引列的順序,一般情況下,將查詢需求頻繁或者字段選擇性高的列放在前面.此外可以根據特例的查詢或者表結構進行單獨的調整.

7. 簡單描述mysql中，索引，主鍵，唯一索引，聯合索引的區別，對數據庫的性能有什么影響(從讀寫兩方面)

索引是一種特殊的文件(InnoDB數據表上的索引是表空間的一個組成部分)，它們包含著對數據表里所有記錄的引用指針。

普通索引(由關鍵字KEY或INDEX定義的索引)的唯一任務是加快對數據的訪問速度。

普通索引允許被索引的數據列包含重復的值。如果能確定某個數據列將只包含彼此各不相同的值，在為這個數據列創建索引的時候就應該用關鍵字UNIQUE把它定義為一個唯一索引。也就是說，唯一索引可以保證數據記錄的唯一性。

主鍵，是一種特殊的唯一索引，在一張表中只能定義一個主鍵索引，主鍵用于唯一標識一條記錄，使用關鍵字 PRIMARY KEY 來創建。

索引可以覆蓋多個數據列，如像INDEX(columnA, columnB)索引，這就是聯合索引。

索引可以極大的提高數據的查詢速度，但是會降低插入、刪除、更新表的速度，因為在執行這些寫操作時，還要操作索引文件。

二、事務

1. ACID是什么?

• A=Atomicity：原子性,就是上面說的,要么全部成功,要么全部失敗.不可能只執行一部分操作.
• C=Consistency：系統(數據庫)總是從一個一致性的狀態轉移到另一個一致性的狀態,不會存在中間狀態.
• I=Isolation：隔離性: 通常來說:一個事務在完全提交之前,對其他事務是不可見的.注意前面的通常來說加了紅色,意味著有例外情況.
• D=Durability：持久性,一旦事務提交,那么就永遠是這樣子了,哪怕系統崩潰也不會影響到這個事務的結果.

2. 同時有多個事務在進行會怎么樣呢?

多事務的并發進行一般會造成以下幾個問題：

• 臟讀：A事務讀取到了B事務未提交的內容,而B事務后面進行了回滾.
• 不可重復讀:：當設置A事務只能讀取B事務已經提交的部分,會造成在A事務內的兩次查詢,結果竟然不一樣,因為在此期間B事務進行了提交操作.
• 幻讀:：A事務讀取了一個范圍的內容,而同時B事務在此期間插入了一條數據.造成"幻覺".

3. 怎么解決這些問題呢?MySQL的事務隔離級別了解嗎?

MySQL的四種隔離級別如下：

(1) 未提交讀(READ UNCOMMITTED)

這就是上面所說的例外情況了,這個隔離級別下,其他事務可以看到本事務沒有提交的部分修改.因此會造成臟讀的問題(讀取到了其他事務未提交的部分,而之后該事務進行了回滾).

這個級別的性能沒有足夠大的優勢,但是又有很多的問題,因此很少使用.

(2) 已提交讀(READ COMMITTED)

其他事務只能讀取到本事務已經提交的部分.這個隔離級別有 不可重復讀的問題,在同一個事務內的兩次讀取,拿到的結果竟然不一樣,因為另外一個事務對數據進行了修改.

(3) REPEATABLE READ(可重復讀)

可重復讀隔離級別解決了上面不可重復讀的問題(看名字也知道),但是仍然有一個新問題,就是 幻讀,當你讀取id> 10 的數據行時,對涉及到的所有行加上了讀鎖,此時例外一個事務新插入了一條id=11的數據,因為是新插入的,所以不會觸發上面的鎖的排斥,那么進行本事務進行下一次的查詢時會發現有一條id=11的數據,而上次的查詢操作并沒有獲取到,再進行插入就會有主鍵沖突的問題.

(4) SERIALIZABLE(可串行化)

這是最高的隔離級別,可以解決上面提到的所有問題,因為他強制將所以的操作串行執行,這會導致并發性能極速下降,因此也不是很常用.

4. Innodb默認使用的是哪種隔離級別呢?

InnoDB默認使用的是可重復讀隔離級別.

5. MySQL都有哪些鎖呢?像上面那樣子進行鎖定豈不是有點阻礙并發效率了?

從鎖的類別上來講,有共享鎖和排他鎖。

• 共享鎖：又叫做讀鎖. 當用戶要進行數據的讀取時,對數據加上共享鎖.共享鎖可以同時加上多個.
• 排他鎖：又叫做寫鎖. 當用戶要進行數據的寫入時,對數據加上排他鎖.排他鎖只可以加一個,他和其他的排他鎖,共享鎖都相斥.

MyISAM支持表鎖，InnoDB支持表鎖和行鎖，默認為行鎖

• 表級鎖：開銷小，加鎖快，不會出現死鎖。鎖定粒度大，發生鎖沖突的概率最高，并發量最低
• 行級鎖：開銷大，加鎖慢，會出現死鎖。鎖力度小，發生鎖沖突的概率小，并發度最高

6. 鎖的優化策略

• 讀寫分離
• 分段加鎖
• 減少鎖持有的時間
• 多個線程盡量以相同的順序去獲取資源

不能將鎖的粒度過于細化，不然可能會出現線程的加鎖和釋放次數過多，反而效率不如一次加一把大鎖。

三、存儲引擎

1. MySQL支持哪些存儲引擎?

MySQL支持多種存儲引擎,比如InnoDB,MyISAM,Memory,Archive等等.在大多數的情況下,直接選擇使用InnoDB引擎都是最合適的,InnoDB也是MySQL的默認存儲引擎.

2. InnoDB和MyISAM有什么區別?

• InnoDB支持事務，而MyISAM不支持事務
• InnoDB支持行級鎖，而MyISAM支持表級鎖
• InnoDB支持MVCC, 而MyISAM不支持
• InnoDB支持外鍵，而MyISAM不支持
• InnoDB不支持全文索引，而MyISAM支持。

四、優化

1. 超大分頁怎么處理?

超大的分頁一般從兩個方向上來解決.

• 數據庫層面,這也是我們主要集中關注的(雖然收效沒那么大),類似于select * from table where age > 20 limit 1000000,10這種查詢其實也是有可以優化的余地的. 這條語句需要load1000000數據然后基本上全部丟棄,只取10條當然比較慢. 當時我們可以修改為select * from table where id in (select id from table where age > 20 limit 1000000,10).這樣雖然也load了一百萬的數據,但是由于索引覆蓋,要查詢的所有字段都在索引中,所以速度會很快. 同時如果ID連續的好,我們還可以select * from table where id > 1000000 limit 10,效率也是不錯的,優化的可能性有許多種,但是核心思想都一樣,就是減少load的數據.
• 從需求的角度減少這種請求….主要是不做類似的需求(直接跳轉到幾百萬頁之后的具體某一頁.只允許逐頁查看或者按照給定的路線走,這樣可預測,可緩存)以及防止ID泄漏且連續被人惡意攻擊.

解決超大分頁,其實主要是靠緩存,可預測性的提前查到內容,緩存至redis等k-V數據庫中,直接返回即可.

2. 有關注生產環境sql耗時嗎?統計過慢查詢嗎?對慢查詢都怎么優化過?

在業務系統中,除了使用主鍵進行的查詢,其他的都會在測試庫上測試其耗時,慢查詢的統計主要由運維在做,會定期將業務中的慢查詢反饋給我們.慢查詢的優化首先要搞明白慢的原因是什么? 是查詢條件沒有命中索引?是load了不需要的數據列?還是數據量太大?

所以優化也是針對這三個方向來的,

• 首先分析語句,看看是否load了額外的數據,可能是查詢了多余的行并且拋棄掉了,可能是加載了許多結果中并不需要的列,對語句進行分析以及重寫.
• 分析語句的執行計劃,然后獲得其使用索引的情況,之后修改語句或者修改索引,使得語句可以盡可能的命中索引.
• 如果對語句的優化已經無法進行,可以考慮表中的數據量是否太大,如果是的話可以進行橫向或者縱向的分表.

3. MySQL數據庫作發布系統的存儲，數據量增大的情況，怎么優化?

• 設計良好的數據庫結構，允許部分數據冗余，盡量避免join查詢，提高效率。
• 選擇合適的表字段數據類型和存儲引擎，適當的添加索引。
• mysql庫主從讀寫分離。
• 找規律分表，減少單表中的數據量提高查詢速度。
• 添加緩存機制，比如memcached，apc等。
• 不經常改動的頁面，生成靜態頁面。
• 書寫高效率的SQL。比如 SELECT * FROM TABEL 改為 SELECT field_1, field_2, field_3 FROM TABLE.

4. 實踐中如何優化MySQL

最好是按照以下順序優化：

• SQL語句及索引的優化
• 數據庫表結構的優化
• 系統配置的優化
• 硬件的優化

五、其他

1. MySQL中的varchar和char有什么區別.

char是一個定長字段,假如申請了char(10)的空間,那么無論實際存儲多少內容.該字段都占用10個字符,而varchar是變長的,也就是說申請的只是最大長度,占用的空間為實際字符長度+1,最后一個字符存儲使用了多長的空間.

在檢索效率上來講,char > varchar,因此在使用中,如果確定某個字段的值的長度,可以使用char,否則應該盡量使用varchar.例如存儲用戶MD5加密后的密碼,則應該使用char.

2. varchar(10)和int(10)代表什么含義?

varchar的10代表了申請的空間長度,也是可以存儲的數據的最大長度,而int的10只是代表了展示的長度,不足10位以0填充.也就是說,int(1)和int(10)所能存儲的數字大小以及占用的空間都是相同的,只是在展示時按照長度展示.

3. MySQL的binlog有有幾種錄入格式?分別有什么區別?

有三種格式,statement,row和mixed.

• statement模式下,記錄單元為語句.即每一個sql造成的影響會記錄.由于sql的執行是有上下文的,因此在保存的時候需要保存相關的信息,同時還有一些使用了函數之類的語句無法被記錄復制.
• row級別下,記錄單元為每一行的改動,基本是可以全部記下來但是由于很多操作,會導致大量行的改動(比如alter table),因此這種模式的文件保存的信息太多,日志量太大.
• mixed. 一種折中的方案,普通操作使用statement記錄,當無法使用statement的時候使用row.

此外,新版的MySQL中對row級別也做了一些優化,當表結構發生變化的時候,會記錄語句而不是逐行記錄.

4. 有了解橫向分表和縱向分表嗎?

橫向分表是按行分表.假設我們有一張用戶表,主鍵是自增ID且同時是用戶的ID.數據量較大,有1億多條,那么此時放在一張表里的查詢效果就不太理想.我們可以根據主鍵ID進行分表,無論是按尾號分,或者按ID的區間分都是可以的. 假設按照尾號0-99分為100個表,那么每張表中的數據就僅有100w.這時的查詢效率無疑是可以滿足要求的.

縱向分表是按列分表.假設我們現在有一張文章表.包含字段id-摘要-內容.而系統中的展示形式是刷新出一個列表,列表中僅包含標題和摘要,當用戶點擊某篇文章進入詳情時才需要正文內容.此時,如果數據量大,將內容這個很大且不經常使用的列放在一起會拖慢原表的查詢速度.我們可以將上面的表分為兩張.id-摘要,id-內容.當用戶點擊詳情,那主鍵再來取一次內容即可.而增加的存儲量只是很小的主鍵字段.代價很小.

當然,分表其實和業務的關聯度很高,在分表之前一定要做好調研以及benchmark.不要按照自己的猜想盲目操作.

5. 三個范式

• 第一范式：每個列都不可以再拆分.
• 第二范式：非主鍵列完全依賴于主鍵,而不能是依賴于主鍵的一部分.
• 第三范式：非主鍵列只依賴于主鍵,不依賴于其他非主鍵.

在設計數據庫結構的時候,要盡量遵守三范式,如果不遵守,必須有足夠的理由.比如性能. 事實上我們經常會為了性能而妥協數據庫的設計。