MySQL

資料庫指定哪些索引，都說說

普通索引，唯一索引，主鍵索引，全文索引

使用索引的優點

• 提高資料的搜尋速度
• 加快表與表之間的連線速度
• 在資訊檢索過程中，若使用分組及排序子句進行時，通過建立索引能有效的減少檢索過程中所需的分組及排序時間，提高檢索效率。

使用索引的缺點

• 在我們建立資料庫的時候，需要花費的時間去建立和維護索引，而且隨著資料量的增加，需要維護它的時間也會增加。
• 在建立索引的時候會佔用儲存空間
• 在我們需要修改表中的資料時，索引還需進行動態的維護，所以對資料庫的維護帶來了一定的麻煩。

唯一索引: 在建立唯一索引時要不能給具有相同的索引值
主鍵索引： 在我們給一個欄位設定主鍵的時候，它就會自動建立主鍵索引，用來確保每一個值都是唯一的
聚集索引: 我們在表中新增資料的順序，與我們建立索引值相同，而且一個表中只能有一個聚集索引
普通索引: 它的結構主要以B+樹和雜湊索引為主，主要是對資料表中的資料進行精確查詢
全文索引： 它的作用是搜尋資料表中的欄位不是包含我們的關鍵字，就像索引引擎中的模糊查詢。

---

什麼是回表知道嗎

比如一個資料庫索引有一級索引和二級索引，二級索引葉子節點儲存的是主鍵，一級索引儲存的是主鍵和資料，通過二級索引查詢到主鍵後再到一級索引通過已知主鍵查詢資料，需要查詢兩次，這就是索引。

---

非聚集索引一定會回表查詢嗎？

不一定，這涉及到查詢語句所要求的欄位是否命中了索引，如果全部命中了索引，那麼就不必要再進行回表查詢。
舉個簡單的例子：假設我們在員工表的年齡上建立了索引，那麼當進行 select age from employee where age < 20 的查詢時，在索引的葉子節點上，已經包含了age資訊，不會再次進行回表查詢

---

知道聯合索引為什麼要注意索引的順序嗎？

MySQL使用索引時需要索引有序，假設現在建立了”name,age,school“的聯合索引，那麼索引的排序為：先按照name排序，如果name相同，則按照age排序，如果age的值相等，則按照school進行排序，當進行查詢時，此時索引僅僅按照嚴格有序，因此必須首先使用name欄位進行等值查詢，之後對於匹配到的列而言，其按照age欄位嚴格有序，此時可以使用age欄位做索引查詢…以此類推，因此建立聯合索引的時候應該注意索引列的順序，一般情況下，將查詢需求頻繁或者欄位高的列放在前面，此外可以根據帶列的查詢或者表結構進行單獨的調整。

---

資料庫事務的四大特性

A=Atomicity
原子性，一個事務要麼全部成功 ，要麼全部失敗。不可能只執行一部分操作
C=Consistency
一致性，系統（資料庫）總是從一個一致性狀態移到另一個一致性狀態，不會存在中間態
所有事務對資料的讀取都是一致性的。
I=Isolation
隔離性：通常來說，一個事務未完全提交之前，對其他事務是不可見的。
D=Durability
永續性，一旦事務提交，那麼就永遠是這樣子，那麼資料庫奔潰，寫入磁碟中

---

多個事務同時進行時會造成什麼問題？

髒讀：A事務讀取到了B事務未提交的內容，而B事務後面進行了回滾
不可重複讀：當設定A事務只能讀取B事務已經提交的部分，會造成A事務內的兩次查詢，結果竟然不一樣，因為在此期間B事務進行了操作
幻讀：A事務讀取了一個範圍的內容，而同時B事務在此期間插入了一條資料，造成幻覺

---

如何解決以上的問題呢

MySQL的四種隔離級別如下:
未提交讀(READ UNCOMMITTED)

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

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

已提交讀(READ COMMITTED)

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

REPEATABLE READ(可重複讀)

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

SERIALIZABLE(可序列化)

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

對MySQL有了解嗎？

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

• 共享鎖: 又叫做讀鎖. 當使用者要進行資料的讀取時,對資料加上共享鎖.共享鎖可以同時加上多個.
• 排他鎖: 又叫做寫鎖. 當使用者要進行資料的寫入時,對資料加上排他鎖.排他鎖只可以加一個,他和其他的排他鎖,共享鎖都相斥.
  用上面的例子來說就是使用者的行為有兩種,一種是來看房,多個使用者一起看房是可以接受的. 一種是真正的入住一晚,在這期間,無論是想入住的還是想看房的都不可以.
  鎖的粒度取決於具體的儲存引擎,InnoDB實現了行級鎖,頁級鎖,表級鎖.
  他們的加鎖開銷從大大小,併發能力也是從大到小.

---

為什麼不使用UUID？

因為在InnoDB儲存引擎中,主鍵索引是作為聚簇索引存在的,也就是說,主鍵索引的B+樹葉子節點上儲存了主鍵索引以及全部的資料(按照順序),如果主鍵索引是自增ID,那麼只需要不斷向後排列即可,如果是UUID,由於到來的ID與原來的大小不確定,會造成非常多的資料插入,資料移動,然後導致產生很多的記憶體碎片,進而造成插入效能的下降.

---

固定長度的字串比如身份證號應該用什麼欄位儲存好呢，說說原因。

使用者身份證號等固定長度的字串應該使用char而不是varchar來儲存,這樣可以節省空間且提高檢索效率.

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

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

---

InnoDB和MyISAM的區別？

• InnoDB支援事物，而MyISAM不支援事物
• InnoDB支援行級鎖，而MyISAM支援表級鎖
• InnoDB支援MVCC, 而MyISAM不支援
• InnoDB支援外來鍵，而MyISAM不支援
• InnoDB不支援全文索引，而MyISAM支援

---

關心過業務系統裡面的sql耗時嗎?一般你會怎麼考慮優化？

在業務系統中,除了使用主鍵進行的查詢,其他的我都會在測試庫上測試其耗時,慢查詢的統計主要由運維在做,會定期將業務中的慢查詢反饋給我們.
慢查詢的優化首先要搞明白慢的原因是什麼? 是查詢條件沒有命中索引?是load了不需要的資料列?還是資料量太大?
所以優化也是針對這三個方向來的：
首先分析語句,看看是否load了額外的資料,可能是查詢了多餘的行並且拋棄掉了,可能是載入了許多結果中並不需要的列,對語句進行分析以及重寫.
分析語句的執行計劃,然後獲得其使用索引的情況,之後修改語句或者修改索引,使得語句可以儘可能的命中索引.
如果對語句的優化已經無法進行,可以考慮表中的資料量是否太大,如果是的話可以進行橫向或者縱向的分表.

---

表設計的時候遵循正規化，說一說三個正規化。

第一正規化: 每個列都不可以再拆分. 第二正規化: 非主鍵列完全依賴於主鍵,而不能是依賴於主鍵的一部分. 第三正規化: 非主鍵列只依賴於主鍵,不依賴於其他非主鍵.

  在設計資料庫結構的時候,要儘量遵守三正規化,如果不遵守,必須有足夠的理由.比如效能. 事實上我們經常會為了效能而妥協資料庫的設計.