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面試題即可獲取，

標籤：MySQL

上一篇：VFP 小白來報道， 想學習進行資料處理，（學生成績分析、考試安排作業需要）

下一篇：虛心請教，