作者 | LemonCoder
一個典型的網際網路產品架構包含接入層、邏輯處理層以及存儲層，其中存儲層承載著數據落地和持久化的任務，同時給邏輯處理層提供數據查詢功能支持。說到存儲層就要說到資料庫，資料庫知識掌握程度也是面試考察的知識點。
典型服務架構
資料庫分為關係型資料庫和非關係型資料庫，也就是我們常說的 SQL 和 NoSQL，這兩個方向的資料庫代表產品分別是 MySQL 和 Redis ，這次我們主要以面試問答的形式，來學習下關係型資料庫 MySQL 基礎知識。
面試開始，準備接受面試官靈魂拷問吧！
關係型資料庫
什麼是關係型資料庫？
關係型資料庫，是指採用了關係模型來組織數據的資料庫，其以行和列的形式存儲數據，以便於用戶理解，關係型資料庫這一系列的行和列被稱為表，一組表組成了資料庫。用戶通過查詢來檢索資料庫中的數據，而查詢是一個用於限定資料庫中某些區域的執行代碼。
簡單來說，關係模式就是二維表格模型。
關係型資料庫有什麼優勢？
關係型資料庫的優勢：
易於理解。關係型二維表的結構非常貼近現實世界，二維表格，容易理解。支持複雜查詢。可以用 SQL 語句方便的在一個表以及多個表之間做非常複雜的數據查詢。支持事務。可靠的處理事務並且保持事務的完整性，使得對於安全性能很高的數據訪問要求得以實現。
MySQL 資料庫
什麼是 SQL
結構化查詢語言 (Structured Query Language) 簡稱 SQL，是一種特殊目的的程式語言，是一種資料庫查詢和程序設計語言程序設計語言，用於存取數據以及查詢、更新和管理關係資料庫系統。
什麼是MySQL？
MySQL 是一個關係型資料庫管理系統，MySQL 是最流行的關係型資料庫管理系統之一，常見的關係型資料庫還有 Oracle 、SQL Server、Access 等等。
MySQL在過去由於性能高、成本低、可靠性好，已經成為最流行的開源資料庫，廣泛地應用在 Internet 上的中小型網站中。
MySQL 和 MariaDB 傻傻分不清楚？
MySQL 最初由瑞典 MySQL AB 公司開發，MySQL 的創始人是烏爾夫·米卡埃爾·維德紐斯，常用暱稱蒙提（Monty）。
在被甲骨文公司收購後，現在屬於甲骨文公司（Oracle） 旗下產品。Oracle 大幅調漲 MySQL 商業版的售價，因此導致自由軟體社區們對於 Oracle 是否還會持續支持 MySQL 社區版有所隱憂。
MySQL 的創始人就是之前那個叫 Monty 的大佬以 MySQL 為基礎成立分支計劃 MariaDB。
MariaDB 打算保持與 MySQL 的高度兼容性，確保具有庫二進位奇偶校驗的直接替換功能，以及與 MySQL API （應用程式接口)和命令的精確匹配，而原先一些使用 MySQL 的開源軟體逐漸轉向 MariaDB 或其它的資料庫。
所以如果看到你公司用的是 MariaDB 不用懷疑，其實它骨子裡還是 MySQL，學會了MySQL 也就會了 MariaDB。
一個彩蛋
MariaDB 是以 Monty 的小女兒 Maria 命名的，就像 MySQL 是以他另一個女兒 My 命名的一樣，兩款鼎鼎大名的資料庫分別用兩個女兒的名字命名，老爺子厲害!
如何查看 MySQL 當前版本號？
在系統命令行下：mysql -V
連接上MySQL命令行輸入:
> status;
Server: MySQLServerversion: 5.5.45Protocolversion: 10
或 select version();
+------------------------+| version() |+------------------------+| 5.5.45-xxxxx |+------------------------+
基礎數據類型
MySQL 有哪些數據類型？
MySQL 數據類型非常豐富，常用類型簡單介紹如下：
整數類型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT。
浮點數類型：FLOAT、DOUBLE、DECIMAL。
字符串類型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB。
日期類型：Date、DateTime、TimeStamp、Time、Year。
其他數據類型：BINARY、VARBINARY、ENUM、SET...
CHAR 和 VARCHAR的區別？
CHAR 是固定長度的字符類型，VARCHAR 則是可變長度的字符類型，下面討論基於在 MySQL5.0 以上版本中。
共同點
CHAR(M) 和 VARCHAR(M) 都表示該列能存儲 M 個字符，注意不是字節！！
CHAR類型特點
CHAR 最多可以存儲 255 個字符 (注意不是字節)，字符有不同的編碼集，比如 UTF8 編碼 (3位元組)、GBK 編碼 (2位元組) 等。對於 CHAR(M) 如果實際存儲的數據長度小於M，則 MySQL 會自動會在它的右邊用空格字符補足，但是在檢索操作中那些填補出來的空格字符會被去掉。VARCHAR類型特點
VARCHAR 的最大長度為 65535 個字節。VARCHAR 存儲的是實際的字符串加1或2個字節用來記錄字符串實際長度，字符串長度小於255位元組用1位元組記錄，超過255就需要2位元組記錄。VARCHAR(50) 能存放幾個 UTF8 編碼的漢字？
存放的漢字個數與版本相關。
mysql 4.0 以下版本，varchar(50) 指的是 50 字節，如果存放 UTF8 格式編碼的漢字時（每個漢字3位元組），只能存放16 個。
mysql 5.0 以上版本，varchar(50) 指的是 50 字符，無論存放的是數字、字母還是 UTF8 編碼的漢字，都可以存放 50 個。
int(10) 和 bigint(10) 能存儲的數據大小一樣嗎？
不一樣，具體原因如下：
int 能存儲四字節有符號整數。bigint 能存儲八字節有符號整數。所以能存儲的數據大小不一樣，其中的數字 10 代表的只是數據的顯示寬度。[^13]
顯示寬度指明 Mysql 最大可能顯示的數字個數，數值的位數小於指定的寬度時數字左邊會用空格填充，空格不容易看出。如果插入了大於顯示寬度的值，只要該值不超過該類型的取值範圍，數值依然可以插入且能夠顯示出來。建表的時候指定 zerofill 選項，則不足顯示寬度的部分用 0 填充，如果是 1 會顯示成 0000000001。如果沒指定顯示寬度， bigint 默認寬度是 20 ，int 默認寬度 11。
存儲引擎相關
MySQL 存儲引擎類型有哪些？
常用的存儲引擎有 InnoDB 存儲引擎和 MyISAM 存儲引擎，InnoDB 是 MySQL 的默認事務引擎。
查看資料庫表當前支持的引擎，可以用下面查詢語句查看 ：
# 查詢結果表中的 Engine 欄位指示存儲引擎類型。showtablestatusfrom'your_db_name'wherename='your_table_name';
InnoDB 存儲引擎應用場景是什麼？
InnoDB 是 MySQL 的默認「事務引擎」，被設置用來處理大量短期（short-lived）事務，短期事務大部分情況是正常提交的，很少會回滾。
InnoDB存儲引擎特性有哪些？
採用多版本並發控制（MVCC，MultiVersion Concurrency Control）來支持高並發。並且實現了四個標準的隔離級別，通過間隙鎖 next-key locking 策略防止幻讀的出現。
引擎的表基於聚簇索引建立，聚簇索引對主鍵查詢有很高的性能。不過它的二級索引 secondary index 非主鍵索引中必須包含主鍵列，所以如果主鍵列很大的話，其他的所有索引都會很大。因此，若表上的索引較多的話，主鍵應當儘可能的小。另外 InnoDB 的存儲格式是平臺獨立。
InnoDB 做了很多優化，比如：磁碟讀取數據方式採用的可預測性預讀、自動在內存中創建 hash 索引以加速讀操作的自適應哈希索引（adaptive hash index)，以及能夠加速插入操作的插入緩衝區（insert buffer)等。
InnoDB 通過一些機制和工具支持真正的熱備份，MySQL 的其他存儲引擎不支持熱備份，要獲取一致性視圖需要停止對所有表的寫入，而在讀寫混合場景中，停止寫入可能也意味著停止讀取。
InnoDB 引擎的四大特性是什麼？
插入緩衝（Insert buffer)
Insert Buffer 用於非聚集索引的插入和更新操作。先判斷插入的非聚集索引是否在緩存池中，如果在則直接插入，否則插入到 Insert Buffer 對象裡。再以一定的頻率進行 Insert Buffer 和輔助索引葉子節點的 merge 操作，將多次插入合併到一個操作中，提高對非聚集索引的插入性能。
二次寫 (Double write)
Double Write 由兩部分組成，一部分是內存中的 double write buffer，大小為 2MB，另一部分是物理磁碟上共享表空間連續的 128 個頁，大小也為 2MB。在對緩衝池的髒頁進行刷新時，並不直接寫磁碟，而是通過 memcpy 函數將髒頁先複製到內存中的該區域，之後通過 doublewrite buffer 再分兩次，每次 1MB 順序地寫入共享表空間的物理磁碟上，然後馬上調用 fsync 函數，同步磁碟，避免作業系統緩衝寫帶來的問題。
自適應哈希索引 (Adaptive Hash Index)
InnoDB 會根據訪問的頻率和模式，為熱點頁建立哈希索引，來提高查詢效率。索引通過緩存池的 B+ 樹頁構造而來，因此建立速度很快，InnoDB 存儲引擎會監控對表上各個索引頁的查詢，如果觀察到建立哈希索引可以帶來速度上的提升，則建立哈希索引，所以叫做自適應哈希索引。
緩存池
為了提高資料庫的性能，引入緩存池的概念，通過參數 innodb_buffer_pool_size 可以設置緩存池的大小，參數 innodb_buffer_pool_instances 可以設置緩存池的實例個數。緩存池主要用於存儲以下內容：
緩衝池中緩存的數據頁類型有：索引頁、數據頁、undo頁、插入緩衝 (insert buffer)、自適應哈希索引(adaptive hash index)、InnoDB存儲的鎖信息 (lock info)和數據字典信息 (data dictionary)。
MyISAM存儲引擎應用場景有哪些？
MyISAM 是 MySQL 5.1 及之前的版本的默認的存儲引擎。MyISAM 提供了大量的特性，包括全文索引、壓縮、空間函數（GIS)等，但MyISAM 不「支持事務和行級鎖」，對於只讀數據，或者表比較小、可以容忍修復操作，依然可以使用它。
MyISAM存儲引擎特性有哪些？
MyISAM「不支持行級鎖而是對整張表加鎖」。讀取時會對需要讀到的所有表加共享鎖，寫入時則對表加排它鎖。但在表有讀取操作的同時，也可以往表中插入新的記錄，這被稱為並發插入。
MyISAM 表可以手工或者自動執行檢查和修復操作。但是和事務恢復以及崩潰恢復不同，可能導致一些「數據丟失」，而且修復操作是非常慢的。
對於 MyISAM 表，即使是 BLOB 和 TEXT 等長欄位，也可以基於其前 500 個字符創建索引，MyISAM 也支持「全文索引」，這是一種基於分詞創建的索引，可以支持複雜的查詢。
如果指定了 DELAY_KEY_WRITE 選項，在每次修改執行完成時，不會立即將修改的索引數據寫入磁碟，而是會寫到內存中的鍵緩衝區，只有在清理鍵緩衝區或者關閉表的時候才會將對應的索引塊寫入磁碟。這種方式可以極大的提升寫入性能，但是在資料庫或者主機崩潰時會造成「索引損壞」，需要執行修復操作。
MyISAM 與 InnoDB 存儲引擎 5 大區別
InnoDB 支持事物，而MyISAM不支持事物InnoDB 支持行級鎖，而MyISAM支持表級鎖InnoDB 支持MVCC, 而MyISAM不支持InnoDB 支持外鍵，而MyISAM不支持InnoDB 不支持全文索引，而MyISAM支持一張表簡單羅列兩種引擎的主要區別，如下圖：
mysql引擎對比
SELECT COUNT(*) 在哪個引擎執行更快？
SELECT COUNT(*) 常用於統計表的總行數，在 MyISAM 存儲引擎中執行更快，前提是不能加有任何WHERE條件。
這是因為 MyISAM 對於表的行數做了優化，內部用一個變量存儲了表的行數，如果查詢條件沒有 WHERE 條件則是查詢表中一共有多少條數據，MyISAM 可以迅速返回結果，如果加 WHERE 條件就不行。
InnoDB 的表也有一個存儲了表行數的變量，但這個值是一個估計值，所以並沒有太大實際意義。
MySQL 基礎知識
說一下資料庫設計三範式是什麼？
1範式：1NF 是對屬性的原子性約束，要求屬性具有原子性，不可再分解；(只要是關係型資料庫都滿足1NF)
2範式：2NF 是對記錄的惟一性約束，要求記錄有惟一標識，即實體的惟一性；
3範式：3NF 是對欄位冗餘性的約束，即任何欄位不能由其他欄位派生出來，它要求欄位沒有冗餘，沒有冗餘的資料庫設計可以做到。
但是，沒有冗餘的資料庫未必是最好的資料庫，有時為了提高運行效率，就必須降低範式標準，適當保留冗餘數據，具體做法是：在概念數據模型設計時遵守第三範式，降低範式標準的工作放到物理數據模型設計時考慮，降低範式就是增加欄位，允許冗餘。
SQL 語句有哪些分類？
DDL：數據定義語言（create alter drop）DML：數據操作語句（insert update delete）DTL：數據事務語句（commit collback savapoint）DCL：數據控制語句（grant revoke）資料庫刪除操作中的 delete、drop、 truncate 區別在哪？
當不再需要該表時可以用 drop 來刪除表;當仍要保留該表，但要刪除所有記錄時， 用 truncate 來刪除表中記錄。當要刪除部分記錄時（一般來說有 WHERE 子句約束） 用 delete 來刪除表中部分記錄。什麼是 MySql 視圖？
視圖是虛擬表，並不儲存數據，只包含定義時的語句的動態數據。
創建視圖語法：
CREATE [ORREPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = user] [SQLSECURITY { DEFINER | INVOKER }]VIEW view_name [(column_list)]AS select_statement [WITH [CASCADED | LOCAL] CHECKOPTION]
參數說明：
OR REPLACE：如果視圖存在，則替換已有視圖。ALGORITHM：視圖選擇算法，默認算法是 UNDEFINED(未定義的)由 MySQL 自動選擇要使用的算法。DEFINER：指定視圖創建者或定義者，如果不指定該選項，則創建視圖的用戶就是定義者。SQL SECURITY：SQL 安全性，默認為 DEFINER。select_statement：創建視圖的 SELECT 語句，可以從基表或其他視圖中選擇數據。WITH CHECK OPTION：表示視圖在更新時保證約束，默認是 CASCADED。使用 MySQL 視圖有何優點？
操作簡單方便。視圖用戶完全不需要關心視圖對應的表的結構、關聯條件和篩選條件，對用戶來說已經是過濾好的複合條件的結果集。數據更加安全。視圖用戶只能訪問視圖中的結果集，通過視圖可以把對表的訪問權限限制在某些行和列上面。數據隔離。屏蔽了源表結構變化對用戶帶來的影響，源表結構變化視圖結構不變。MySql 服務默認埠號是多少 ？
默認埠是 3306
查看埠命令：> show variables like 'port';
用 DISTINCT 過濾多列的規則？
DISTINCT 用於對選擇的數據去重，單列用法容易理解。比如有如下數據表 tamb：
name numberTencent 1 Alibaba 2 Bytedance 3 Meituan 3
查詢語句：SELECT DISTINCT name FROM table tamb 結果如下：
nameTencent Alibaba Bytedance Meituan
如果要求按 number 列去重同時顯示 name ，你可能會寫出查詢語句：
SELECT DISTINCT number, name FROM table tamb
多參數 DISTINCT 去重規則是：把 DISTINCT 之後的所有參數當做一個過濾條件，也就是說會對 (number, name)整體去重處理，只有當這個組合不同才會去重，結果如下：
number name1 Tencent2 Alibaba3 Bytedance3 Meituan
從結果來看好像並沒有達到我們想要的去重的效果，那要怎麼實現「按 number 列去重同時顯示 name」呢？可以用 Group By 語句：
SELECT number, name FROM table tamb GROUP BY number 輸出如下，正是我們想要的效果：
number name1 Tencent2 Alibaba3 Bytedance
什麼是存儲過程？
一條或多條 sql 語句集合，有以下一些特點：
存儲過程能實現較快的執行速度。存儲過程可以用流程控制語句編寫，有很強的靈活性，可以完成複雜的判斷和較複雜的運算。存儲過程可被作為一種安全機制來充分利用。存儲過程能夠減少網絡流量。 delimiter 分隔符create procedure|procproc_name()beginsql語句end 分隔符delimiter ； --還原分隔符，為了不影響後面的語句的使用--默認的分隔符是；但是為了能在整個存儲過程中重用，因此一般需要自定義分隔符（除\外）showprocedurestatuslike ""; --查詢存儲過程，可以不適用like進行過濾drop procedureifexists；--刪除存儲過程
存儲過程和函數好像差不多，你說說他們有什麼區別?
存儲過程和函數是事先經過編譯並存儲在資料庫中的一段 SQL 語句的集合，調用存儲過程和函數可以簡化應用開發人員的很多工作，減少數據在資料庫和應用伺服器之間的傳輸，對於提高數據處理的效率是有好處的。
相同點
存儲過程和函數都是為了可重複的執行操作資料庫的 SQL 語句的集合。存儲過程和函數都是一次編譯後緩存起來，下次使用就直接命中已經編譯好的 sql 語句，減少網絡交互提高了效率。不同點
標識符不同，函數的標識符是 function，存儲過程是 procedure。函數返回單個值或者表對象，而存儲過程沒有返回值，但是可以通過 OUT 參數返回多個值。函數限制比較多，比如不能用臨時表，只能用表變量，一些函數都不可用等，而存儲過程的限制相對就比較少。一般來說，存儲過程實現的功能要複雜一點，而函數的實現的功能針對性比較強。函數的參數只能是 IN 類型，存儲過程的參數可以是 IN OUT INOUT 三種類型。存儲函數使用 select 調用，存儲過程需要使用 call 調用。本文是 MySQL 系列的第二篇，以面試問答形式總結了一系列面試常見的基礎知識點，都是非常基礎的內容，但越是基礎越顯得重要，建議收藏作為知識點筆記（據說分享、在看效果更佳），時常拿出來複習溫故而知新，MySQL 系列知識點零散龐大，本文是基礎篇，更多MySQL系列文章敬請期待。
最近我把寫的所有技術文章都整理在 GtHub 上了，按模塊分門別類方便閱讀，歡迎關注和Star，項目地址：https://github.com/lemonchann/TechClass