Mysql大廠高頻面試題

前言

• 前幾天有讀者找到我，說想要一套全面的Mysql面試題，今天陳某特地為她寫了一篇。
• 文章的目錄如下：
• 

Mysql面試題

什麼是SQL？

• 結構化查詢語言(Structured Query Language)簡稱SQL，是一種資料庫查詢語言。
• 作用：用於存取數據、查詢、更新和管理關係資料庫系統。

什麼是MySQL?

• MySQL是一個關係型資料庫管理系統，由瑞典MySQL AB 公司開發，屬於 Oracle 旗下產品。MySQL 是最流行的關係型資料庫管理系統之一，在 WEB 應用方面，MySQL是最好的 RDBMS (Relational Database Management System，關係資料庫管理系統) 應用軟體之一。在Java企業級開發中非常常用，因為 MySQL 是開源免費的，並且方便擴展。

資料庫三大範式是什麼？

• 第一範式：每個列都不可以再拆分。
• 第二範式：在第一範式的基礎上，非主鍵列完全依賴於主鍵，而不能是依賴於主鍵的一部分。
• 第三範式：在第二範式的基礎上，非主鍵列只依賴於主鍵，不依賴於其他非主鍵。
• 在設計資料庫結構的時候，要儘量遵守三範式，如果不遵守，必須有足夠的理由。比如性能。事實上我們經常會為了性能而妥協資料庫的設計。

mysql有關許可權的表都有哪幾個？

• MySQL伺服器通過許可權表來控制用戶對資料庫的訪問，許可權表存放在mysql資料庫里，由mysql_install_db腳本初始化。這些許可權表分別user，db，table_priv，columns_priv和host。下麵分別介紹一下這些表的結構和內容：
  1. user許可權表：記錄允許連接到伺服器的用戶帳號信息，裡面的許可權是全局級的。
  2. db許可權表：記錄各個帳號在各個資料庫上的操作許可權。
  3. table_priv許可權表：記錄數據表級的操作許可權。
  4. columns_priv許可權表：記錄數據列級的操作許可權。
  5. host許可權表：配合db許可權表對給定主機上資料庫級操作許可權作更細緻的控制。這個許可權表不受GRANT和REVOKE語句的影響。

MySQL的binlog有有幾種錄入格式？分別有什麼區別？

• 有三種格式，statement，row和mixed。
  • statement模式下，每一條會修改數據的sql都會記錄在binlog中。不需要記錄每一行的變化，減少了binlog日誌量，節約了IO，提高性能。由於sql的執行是有上下文的，因此在保存的時候需要保存相關的信息，同時還有一些使用了函數之類的語句無法被記錄複製。
  • row級別下，不記錄sql語句上下文相關信息，僅保存哪條記錄被修改。記錄單元為每一行的改動，基本是可以全部記下來但是由於很多操作，會導致大量行的改動(比如alter table)，因此這種模式的文件保存的信息太多，日誌量太大。
  • mixed，一種折中的方案，普通操作使用statement記錄，當無法使用statement的時候使用row。
• 此外，新版的MySQL中對row級別也做了一些優化，當表結構發生變化的時候，會記錄語句而不是逐行記錄。

mysql有哪些數據類型？

• 1、整數類型，包括TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，分別表示1位元組、2位元組、3位元組、4位元組、8位元組整數。任何整數類型都可以加上UNSIGNED屬性，表示數據是無符號的，即非負整數。
  • 長度：整數類型可以被指定長度，例如：INT(11)表示長度為11的INT類型。長度在大多數場景是沒有意義的，它不會限制值的合法範圍，只會影響顯示字元的個數，而且需要和UNSIGNED ZEROFILL屬性配合使用才有意義。
  • 例子：假定類型設定為INT(5)，屬性為UNSIGNED ZEROFILL，如果用戶插入的數據為12的話，那麼資料庫實際存儲數據為00012。
• 2、實數類型，包括FLOAT、DOUBLE、DECIMAL。DECIMAL可以用於存儲比BIGINT還大的整型，能存儲精確的小數。而FLOAT和DOUBLE是有取值範圍的，並支持使用標準的浮點進行近似計算。計算時FLOAT和DOUBLE相比DECIMAL效率更高一些，DECIMAL你可以理解成是用字元串進行處理。
• 3、字元串類型，包括VARCHAR、CHAR、TEXT、BLOB
  • VARCHAR用於存儲可變長字元串，它比定長類型更節省空間。
  • VARCHAR使用額外1或2個位元組存儲字元串長度。列長度小於255位元組時，使用1位元組表示，否則使用2位元組表示。
  • VARCHAR存儲的內容超出設置的長度時，內容會被截斷。
  • CHAR是定長的，根據定義的字元串長度分配足夠的空間。
  • CHAR會根據需要使用空格進行填充方便比較。
  • CHAR適合存儲很短的字元串，或者所有值都接近同一個長度。
  • CHAR存儲的內容超出設置的長度時，內容同樣會被截斷。
• 4、枚舉類型（ENUM），把不重覆的數據存儲為一個預定義的集合。
  • 有時可以使用ENUM代替常用的字元串類型。
  • ENUM存儲非常緊湊，會把列表值壓縮到一個或兩個位元組。
  • ENUM在內部存儲時，其實存的是整數。
  • 儘量避免使用數字作為ENUM枚舉的常量，因為容易混亂。
  • 排序是按照內部存儲的整數
• 5、日期和時間類型，儘量使用timestamp，空間效率高於datetime，
  • 用整數保存時間戳通常不方便處理。
  • 如果需要存儲微妙，可以使用bigint存儲。
  • 看到這裡，這道真題是不是就比較容易回答了。

MyISAM索引與InnoDB索引的區別？

• InnoDB索引是聚簇索引，MyISAM索引是非聚簇索引。
• InnoDB的主鍵索引的葉子節點存儲著行數據，因此主鍵索引非常高效。
• MyISAM索引的葉子節點存儲的是行數據地址，需要再定址一次才能得到數據。
• InnoDB非主鍵索引的葉子節點存儲的是主鍵和其他帶索引的列數據，因此查詢時做到覆蓋索引會非常高效。

InnoDB引擎的4大特性

• 插入緩衝（insert buffer)
• 二次寫(double write)
• 自適應哈希索引(ahi)
• 預讀(read ahead)

什麼是索引？

• 索引是一種特殊的文件(InnoDB數據表上的索引是表空間的一個組成部分)，它們包含著對數據表裡所有記錄的引用指針。
• 索引是一種數據結構。資料庫索引，是資料庫管理系統中一個排序的數據結構，以協助快速查詢、更新資料庫表中數據。索引的實現通常使用B樹及其變種B+樹。
• 更通俗的說，索引就相當於目錄。為了方便查找書中的內容，通過對內容建立索引形成目錄。索引是一個文件，它是要占據物理空間的。

索引有哪些優缺點？

• 索引的優點：
  • 可以大大加快數據的檢索速度，這也是創建索引的最主要的原因。
  • 通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的性能。
• 索引的缺點：
  • 時間方面：創建索引和維護索引要耗費時間，具體地，當對錶中的數據進行增加、刪除和修改的時候，索引也要動態的維護，會降低增/改/刪的執行效率；
  • 空間方面：索引需要占物理空間。

索引有哪幾種類型？

• 主鍵索引: 數據列不允許重覆，不允許為NULL，一個表只能有一個主鍵。
• 唯一索引: 數據列不允許重覆，允許為NULL值，一個表允許多個列創建唯一索引。
  • 可以通過 ALTER TABLE table_name ADD UNIQUE (column); 創建唯一索引
  • 可以通過 ALTER TABLE table_name ADD UNIQUE (column1,column2); 創建唯一組合索引
• 普通索引: 基本的索引類型，沒有唯一性的限制，允許為NULL值。
  • 可以通過ALTER TABLE table_name ADD INDEX index_name (column);創建普通索引
  • 可以通過ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);創建組合索引。
• 全文索引： 是目前搜索引擎使用的一種關鍵技術。
  • 可以通過ALTER TABLE table_name ADD FULLTEXT (column);創建全文索引

索引的數據結構（b樹，hash）

• 索引的數據結構和具體存儲引擎的實現有關，在MySQL中使用較多的索引有Hash索引，B+樹索引等，而我們經常使用的InnoDB存儲引擎的預設索引實現為：B+樹索引。對於哈希索引來說，底層的數據結構就是哈希表，因此在絕大多數需求為單條記錄查詢的時候，可以選擇哈希索引，查詢性能最快；其餘大部分場景，建議選擇BTree索引。

1. B樹索引

• mysql通過存儲引擎取數據，基本上90%的人用的就是InnoDB了，按照實現方式分，InnoDB的索引類型目前只有兩種：BTREE（B樹）索引和HASH索引。B樹索引是Mysql資料庫中使用最頻繁的索引類型，基本所有存儲引擎都支持BTree索引。通常我們說的索引不出意外指的就是（B樹）索引（實際是用B+樹實現的，因為在查看表索引時，mysql一律列印BTREE，所以簡稱為B樹索引）
• 

2. B+tree性質

• n棵子tree的節點包含n個關鍵字，不用來保存數據而是保存數據的索引。
• 所有的葉子結點中包含了全部關鍵字的信息，及指向含這些關鍵字記錄的指針，且葉子結點本身依關鍵字的大小自小而大順序鏈接。
• 所有的非終端結點可以看成是索引部分，結點中僅含其子樹中的最大（或最小）關鍵字。
• B+ 樹中，數據對象的插入和刪除僅在葉節點上進行。
• B+樹有2個頭指針，一個是樹的根節點，一個是最小關鍵碼的葉節點。

3. 哈希索引

• 簡要說下，類似於數據結構中簡單實現的HASH表（散列表）一樣，當我們在mysql中用哈希索引時，主要就是通過Hash演算法（常見的Hash演算法有直接定址法、平方取中法、摺疊法、除數取餘法、隨機數法），將資料庫欄位數據轉換成定長的Hash值，與這條數據的行指針一併存入Hash表的對應位置；如果發生Hash碰撞（兩個不同關鍵字的Hash值相同），則在對應Hash鍵下以鏈表形式存儲。當然這隻是簡略模擬圖。
• 

索引的基本原理

• 索引用來快速地尋找那些具有特定值的記錄。如果沒有索引，一般來說執行查詢時遍歷整張表。
• 索引的原理很簡單，就是把無序的數據變成有序的查詢
  1. 把創建了索引的列的內容進行排序
  2. 對排序結果生成倒排表
  3. 在倒排表內容上拼上數據地址鏈
  4. 在查詢的時候，先拿到倒排表內容，再取出數據地址鏈，從而拿到具體數據

索引演算法有哪些？

• 索引演算法有 BTree演算法和Hash演算法

1. BTree演算法

• BTree是最常用的mysql資料庫索引演算法，也是mysql預設的演算法。因為它不僅可以被用在=,>,>=,<,<=和between這些比較操作符上，而且還可以用於like操作符，只要它的查詢條件是一個不以通配符開頭的常量。

2. Hash演算法

• Hash Hash索引只能用於對等比較，例如=,<=>（相當於=）操作符。由於是一次定位數據，不像BTree索引需要從根節點到枝節點，最後才能訪問到頁節點這樣多次IO訪問，所以檢索效率遠高於BTree索引。

索引設計的原則？

• 適合索引的列是出現在where子句中的列，或者連接子句中指定的列。
• 基數較小的類，索引效果較差，沒有必要在此列建立索引
• 使用短索引，如果對長字元串列進行索引，應該指定一個首碼長度，這樣能夠節省大量索引空間
• 不要過度索引。索引需要額外的磁碟空間，並降低寫操作的性能。在修改表內容的時候，索引會進行更新甚至重構，索引列越多，這個時間就會越長。所以只保持需要的索引有利於查詢即可。

創建索引的原則

• 索引雖好，但也不是無限制的使用，最好符合一下幾個原則
  • 最左首碼匹配原則，組合索引非常重要的原則，mysql會一直向右匹配直到遇到範圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。
  • 較頻繁作為查詢條件的欄位才去創建索引
  • 更新頻繁欄位不適合創建索引
  • 若是不能有效區分數據的列不適合做索引列(如性別，男女未知，最多也就三種，區分度實在太低)
  • 儘量的擴展索引，不要新建索引。比如表中已經有a的索引，現在要加(a,b)的索引，那麼只需要修改原來的索引即可。
  • 定義有外鍵的數據列一定要建立索引。
  • 對於那些查詢中很少涉及的列，重覆值比較多的列不要建立索引。
  • 對於定義為text、image和bit的數據類型的列不要建立索引。

創建索引時需要註意什麼？

• 非空欄位：應該指定列為NOT NULL，除非你想存儲NULL。在mysql中，含有空值的列很難進行查詢優化，因為它們使得索引、索引的統計信息以及比較運算更加複雜。你應該用0、一個特殊的值或者一個空串代替空值；
• 取值離散大的欄位：（變數各個取值之間的差異程度）的列放到聯合索引的前面，可以通過count()函數查看欄位的差異值，返回值越大說明欄位的唯一值越多欄位的離散程度高；
• 索引欄位越小越好：資料庫的數據存儲以頁為單位一頁存儲的數據越多一次IO操作獲取的數據越大效率越高。

使用索引查詢一定能提高查詢的性能嗎？

• 通常，通過索引查詢數據比全表掃描要快。但是我們也必須註意到它的代價。
• 索引需要空間來存儲，也需要定期維護， 每當有記錄在表中增減或索引列被修改時，索引本身也會被修改。 這意味著每條記錄的INSERT，DELETE，UPDATE將為此多付出4，5 次的磁碟I/O。 因為索引需要額外的存儲空間和處理，那些不必要的索引反而會使查詢反應時間變慢。使用索引查詢不一定能提高查詢性能，索引範圍查詢(INDEX RANGE SCAN)適用於兩種情況:
• 基於一個範圍的檢索，一般查詢返回結果集小於表中記錄數的30%
• 基於非唯一性索引的檢索

百萬級別或以上的數據如何刪除？

• 關於索引：由於索引需要額外的維護成本，因為索引文件是單獨存在的文件,所以當我們對數據的增加,修改,刪除,都會產生額外的對索引文件的操作,這些操作需要消耗額外的IO,會降低增/改/刪的執行效率。所以，在我們刪除資料庫百萬級別數據的時候，查詢MySQL官方手冊得知刪除數據的速度和創建的索引數量是成正比的。
  1. 所以我們想要刪除百萬數據的時候可以先刪除索引（此時大概耗時三分多鐘）
  2. 然後刪除其中無用數據（此過程需要不到兩分鐘）
  3. 刪除完成後重新創建索引(此時數據較少了)創建索引也非常快，約十分鐘左右。
  4. 與之前的直接刪除絕對是要快速很多，更別說萬一刪除中斷,一切刪除會回滾。那更是坑了。

什麼是最左首碼原則？什麼是最左匹配原則？

• 顧名思義，就是最左優先，在創建多列索引時，要根據業務需求，where子句中使用最頻繁的一列放在最左邊。
• 最左首碼匹配原則，非常重要的原則，mysql會一直向右匹配直到遇到範圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。
• =和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢優化器會幫你優化成索引可以識別的形式

B樹和B+樹的區別

• 在B樹中，你可以將鍵和值存放在內部節點和葉子節點；但在B+樹中，內部節點都是鍵，沒有值，葉子節點同時存放鍵和值。
• B+樹的葉子節點有一條鏈相連，而B樹的葉子節點各自獨立。

使用B樹的好處

• B樹可以在內部節點同時存儲鍵和值，因此，把頻繁訪問的數據放在靠近根節點的地方將會大大提高熱點數據的查詢效率。這種特性使得B樹在特定數據重覆多次查詢的場景中更加高效。

使用B+樹的好處

• 由於B+樹的內部節點只存放鍵，不存放值，因此，一次讀取，可以在記憶體頁中獲取更多的鍵，有利於更快地縮小查找範圍。 B+樹的葉節點由一條鏈相連，因此，當需要進行一次全數據遍歷的時候，B+樹只需要使用O(logN)時間找到最小的一個節點，然後通過鏈進行O(N)的順序遍歷即可。而B樹則需要對樹的每一層進行遍歷，這會需要更多的記憶體置換次數，因此也就需要花費更多的時間

什麼是聚簇索引？何時使用聚簇索引與非聚簇索引？

• 聚簇索引：將數據存儲與索引放到了一塊，找到索引也就找到了數據
• 非聚簇索引：將數據存儲於索引分開結構，索引結構的葉子節點指向了數據的對應行，myisam通過key_buffer把索引先緩存到記憶體中，當需要訪問數據時（通過索引訪問數據），在記憶體中直接搜索索引，然後通過索引找到磁碟相應數據，這也就是為什麼索引不在key buffer命中時，速度慢的原因。

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

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

聯合索引是什麼？為什麼需要註意聯合索引中的順序？

• MySQL可以使用多個欄位同時建立一個索引，叫做聯合索引。在聯合索引中，如果想要命中索引，需要按照建立索引時的欄位順序挨個使用，否則無法命中索引。
• MySQL使用索引時需要索引有序，假設現在建立了"name，age，school"的聯合索引，那麼索引的排序為: 先按照name排序，如果name相同，則按照age排序，如果age的值也相等，則按照school進行排序。
• 當進行查詢時，此時索引僅僅按照name嚴格有序，因此必須首先使用name欄位進行等值查詢，之後對於匹配到的列而言，其按照age欄位嚴格有序，此時可以使用age欄位用做索引查找，以此類推。因此在建立聯合索引的時候應該註意索引列的順序，一般情況下，將查詢需求頻繁或者欄位選擇性高的列放在前面。此外可以根據特例的查詢或者表結構進行單獨的調整。

什麼是資料庫事務？

• 事務是一個不可分割的資料庫操作序列，也是資料庫併發控制的基本單位，其執行的結果必須使資料庫從一種一致性狀態變到另一種一致性狀態。事務是邏輯上的一組操作，要麼都執行，要麼都不執行。

事物的四大特性(ACID)介紹一下?

• 原子性： 事務是最小的執行單位，不允許分割。事務的原子性確保動作要麼全部完成，要麼完全不起作用；
• 一致性： 執行事務前後，數據保持一致，多個事務對同一個數據讀取的結果是相同的；
• 隔離性： 併發訪問資料庫時，一個用戶的事務不被其他事務所干擾，各併發事務之間資料庫是獨立的；
• 持久性： 一個事務被提交之後。它對資料庫中數據的改變是持久的，即使資料庫發生故障也不應該對其有任何影響。

什麼是臟讀？幻讀？不可重覆讀？

• 臟讀(Drity Read)：某個事務已更新一份數據，另一個事務在此時讀取了同一份數據，由於某些原因，前一個RollBack了操作，則後一個事務所讀取的數據就會是不正確的。
• 不可重覆讀(Non-repeatable read):在一個事務的兩次查詢之中數據不一致，這可能是兩次查詢過程中間插入了一個事務更新的原有的數據。
• 幻讀(Phantom Read):在一個事務的兩次查詢中數據筆數不一致，例如有一個事務查詢了幾列(Row)數據，而另一個事務卻在此時插入了新的幾列數據，先前的事務在接下來的查詢中，就會發現有幾列數據是它先前所沒有的。

什麼是事務的隔離級別？MySQL的預設隔離級別是什麼？

• 為了達到事務的四大特性，資料庫定義了4種不同的事務隔離級別，由低到高依次為Read uncommitted、Read committed、Repeatable read、Serializable，這四個級別可以逐個解決臟讀、不可重覆讀、幻讀這幾類問題。
• SQL 標准定義了四個隔離級別：
  • READ-UNCOMMITTED(讀取未提交)： 最低的隔離級別，允許讀取尚未提交的數據變更，可能會導致臟讀、幻讀或不可重覆讀。
  • READ-COMMITTED(讀取已提交)： 允許讀取併發事務已經提交的數據，可以阻止臟讀，但是幻讀或不可重覆讀仍有可能發生。
  • REPEATABLE-READ(可重覆讀)： 對同一欄位的多次讀取結果都是一致的，除非數據是被本身事務自己所修改，可以阻止臟讀和不可重覆讀，但幻讀仍有可能發生。
  • SERIALIZABLE(可串列化)： 最高的隔離級別，完全服從ACID的隔離級別。所有的事務依次逐個執行，這樣事務之間就完全不可能產生干擾，也就是說，該級別可以防止臟讀、不可重覆讀以及幻讀。
• Mysql 預設採用的 REPEATABLE_READ隔離級別 Oracle 預設採用的 READ_COMMITTED隔離級別

隔離級別與鎖的關係

• 在Read Uncommitted級別下，讀取數據不需要加共用鎖，這樣就不會跟被修改的數據上的排他鎖衝突
• 在Read Committed級別下，讀操作需要加共用鎖，但是在語句執行完以後釋放共用鎖；
• 在Repeatable Read級別下，讀操作需要加共用鎖，但是在事務提交之前並不釋放共用鎖，也就是必須等待事務執行完畢以後才釋放共用鎖。
• SERIALIZABLE 是限制性最強的隔離級別，因為該級別鎖定整個範圍的鍵，並一直持有鎖，直到事務完成。

按照鎖的粒度分資料庫鎖有哪些？

• 行級鎖:行級鎖是Mysql中鎖定粒度最細的一種鎖，表示只針對當前操作的行進行加鎖。行級鎖能大大減少資料庫操作的衝突。其加鎖粒度最小，但加鎖的開銷也最大。行級鎖分為共用鎖 和 排他鎖。特點：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，併發度也最高。
• 表級鎖: 表級鎖是MySQL中鎖定粒度最大的一種鎖，表示對當前操作的整張表加鎖，它實現簡單，資源消耗較少，被大部分MySQL引擎支持。最常使用的MYISAM與INNODB都支持表級鎖定。表級鎖定分為表共用讀鎖（共用鎖）與表獨占寫鎖（排他鎖）。特點：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發出鎖衝突的概率最高，併發度最低。
• 頁級鎖:頁級鎖是MySQL中鎖定粒度介於行級鎖和表級鎖中間的一種鎖。表級鎖速度快，但衝突多，行級衝突少，但速度慢。所以取了折衷的頁級，一次鎖定相鄰的一組記錄。

從鎖的類別上分MySQL都有哪些鎖呢？

• 從鎖的類別上來講，有共用鎖和排他鎖。
  • 共用鎖: 又叫做讀鎖。 當用戶要進行數據的讀取時，對數據加上共用鎖。共用鎖可以同時加上多個。
  • 排他鎖: 又叫做寫鎖。 當用戶要進行數據的寫入時，對數據加上排他鎖。排他鎖只可以加一個，他和其他的排他鎖，共用鎖都相斥。

InnoDB存儲引擎的鎖的演算法有哪三種？

• Record lock：單個行記錄上的鎖
• Gap lock：間隙鎖，鎖定一個範圍，不包括記錄本身
• Next-key lock：record+gap 鎖定一個範圍，包含記錄本身

什麼是死鎖？怎麼解決？

• 死鎖是指兩個或多個事務在同一資源上相互占用，並請求鎖定對方的資源，從而導致惡性迴圈的現象。
• 常見的解決死鎖的方法
  1. 如果不同程式會併發存取多個表，儘量約定以相同的順序訪問表，可以大大降低死鎖機會。
  2. 在同一個事務中，儘可能做到一次鎖定所需要的所有資源，減少死鎖產生概率；
  3. 對於非常容易產生死鎖的業務部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死鎖產生的概率；
• 如果業務處理不好可以用分散式事務鎖或者使用樂觀鎖

資料庫的樂觀鎖和悲觀鎖是什麼？怎麼實現的？

• 資料庫管理系統（DBMS）中的併發控制的任務是確保在多個事務同時存取資料庫中同一數據時不破壞事務的隔離性和統一性以及資料庫的統一性。樂觀併發控制（樂觀鎖）和悲觀併發控制（悲觀鎖）是併發控制主要採用的技術手段。
• 悲觀鎖：假定會發生併發衝突，屏蔽一切可能違反數據完整性的操作。在查詢完數據的時候就把事務鎖起來，直到提交事務。實現方式：使用資料庫中的鎖機制
• 樂觀鎖：假設不會發生併發衝突，只在提交操作時檢查是否違反數據完整性。在修改數據的時候把事務鎖起來，通過version的方式來進行鎖定。實現方式：樂一般會使用版本號機制或CAS演算法實現。

大表數據查詢，怎麼優化？

• 優化shema、sql語句+索引；
• 第二加緩存，memcached, redis；
• 主從複製，讀寫分離；
• 垂直拆分，根據你模塊的耦合度，將一個大的系統分為多個小的系統，也就是分散式系統
• 水平切分，針對數據量大的表，這一步最麻煩，最能考驗技術水平，要選擇一個合理的sharding key, 為了有好的查詢效率，表結構也要改動，做一定的冗餘，應用也要改，sql中儘量帶sharding key，將數據定位到限定的表上去查，而不是掃描全部的表

超大分頁怎麼處理？

• 超大的分頁一般從兩個方向上來解決:
  • 資料庫層面,這也是我們主要集中關註的(雖然收效沒那麼大),類似於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泄漏且連續被人惡意攻擊

為什麼要儘量設定一個主鍵？

• 主鍵是資料庫確保數據行在整張表唯一性的保障，即使業務上本張表沒有主鍵，也建議添加一個自增長的ID列作為主鍵。設定了主鍵之後，在後續的刪改查的時候可能更加快速以及確保操作數據範圍安全。

主鍵使用自增ID還是UUID？

• 推薦使用自增ID，不要使用UUID。
• 因為在InnoDB存儲引擎中，主鍵索引是作為聚簇索引存在的，也就是說，主鍵索引的B+樹葉子節點上存儲了主鍵索引以及全部的數據(按照順序)，如果主鍵索引是自增ID，那麼只需要不斷向後排列即可，如果是UUID，由於到來的ID與原來的大小不確定，會造成非常多的數據插入，數據移動，然後導致產生很多的記憶體碎片，進而造成插入性能的下降。
• 總之，在數據量大一些的情況下，用自增主鍵性能會好一些。
• 關於主鍵是聚簇索引，如果沒有主鍵，InnoDB會選擇一個唯一鍵來作為聚簇索引，如果沒有唯一鍵，會生成一個隱式的主鍵。

欄位為什麼要求定義為not null？

• null值會占用更多的位元組，且會在程式中造成很多與預期不符的情況。

如果要存儲用戶的密碼散列，應該使用什麼欄位進行存儲？

• 密碼散列，鹽，用戶身份證號等固定長度的字元串應該使用char而不是varchar來存儲，這樣可以節省空間且提高檢索效率。

資料庫結構優化？

• 一個好的資料庫設計方案對於資料庫的性能往往會起到事半功倍的效果。
• 需要考慮數據冗餘、查詢和更新的速度、欄位的數據類型是否合理等多方面的內容。
• 將欄位很多的表分解成多個表：對於欄位較多的表，如果有些欄位的使用頻率很低，可以將這些欄位分離出來形成新表。因為當一個表的數據量很大時，會由於使用頻率低的欄位的存在而變慢。
• 增加中間表：對於需要經常聯合查詢的表，可以建立中間表以提高查詢效率。通過建立中間表，將需要通過聯合查詢的數據插入到中間表中，然後將原來的聯合查詢改為對中間表的查詢。
• 增加冗餘欄位：設計數據表時應儘量遵循範式理論的規約，儘可能的減少冗餘欄位，讓資料庫設計看起來精緻、優雅。但是，合理的加入冗餘欄位可以提高查詢速度。表的規範化程度越高，表和表之間的關係越多，需要連接查詢的情況也就越多，性能也就越差。

MySQL資料庫cpu飆升到500%的話他怎麼處理？

• 當 cpu 飆升到 500%時，先用操作系統命令 top 命令觀察是不是 mysqld 占用導致的，如果不是，找出占用高的進程，併進行相關處理。
• 如果是 mysqld 造成的， show processlist，看看裡面跑的 session 情況，是不是有消耗資源的 sql 在運行。找出消耗高的 sql，看看執行計劃是否準確， index 是否缺失，或者實在是數據量太大造成。
• 一般來說，肯定要 kill 掉這些線程(同時觀察 cpu 使用率是否下降)，等進行相應的調整(比如說加索引、改 sql、改記憶體參數)之後，再重新跑這些 SQL。
• 也有可能是每個 sql 消耗資源並不多，但是突然之間，有大量的 session 連進來導致 cpu 飆升，這種情況就需要跟應用一起來分析為何連接數會激增，再做出相應的調整，比如說限制連接數等。

主從複製的作用？

• 主資料庫出現問題，可以切換到從資料庫。
• 可以進行資料庫層面的讀寫分離。
• 可以在從資料庫上進行日常備份。

MySQL主從複製解決的問題？

• 數據分佈：隨意開始或停止複製，併在不同地理位置分佈數據備份
• 負載均衡：降低單個伺服器的壓力
• 高可用和故障切換：幫助應用程式避免單點失敗
• 升級測試：可以用更高版本的MySQL作為從庫

MySQL主從複製工作原理？

• 在主庫上把數據更高記錄到二進位日誌
• 從庫將主庫的日誌複製到自己的中繼日誌
• 從庫讀取中繼日誌的事件，將其重放到從庫數據中。

小福利

• 由於文章篇幅較長，陳某將其轉換為PDF文檔，老規矩，回覆關鍵詞Mysql面試題即可獲取。