聽我的，看完這30道MySQL基礎題再去面試

2020 年 9 月 26 日
筆記
MySQL, 後端開發

可以微信搜索公眾號「後端技術學堂」回復「1024」獲取50本電腦電子書，回復「進群」拉你進讀者技術交流群，文章每周持續更新，我們下期見！

一個典型的互聯網產品架構包含接入層、邏輯處理層以及存儲層，其中存儲層承載著數據落地和持久化的任務，同時給邏輯處理層提供數據查詢功能支援。說到存儲層就要說到資料庫，資料庫知識掌握程度也是面試考察的知識點。

資料庫分為關係型資料庫和非關係型資料庫，也就是我們常說的 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:			MySQL
Server version:		5.5.45
Protocol version:	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位元組記錄。[^12 ]

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 欄位指示存儲引擎類型。
show table status from 'your_db_name' where name='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支援

一張表簡單羅列兩種引擎的主要區別，如下圖：

[外鏈圖片轉存失敗,源站可能有防盜鏈機制,建議將圖片保存下來直接上傳(img-BqMqUg9Y-1597597062145)(//github.com/lemonchann/images/raw/master/database/mysql/mysql_shi_wu/mysql事務-引擎對比.png)]

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
    [OR REPLACE]
    [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    [DEFINER = user]
    [SQL SECURITY { DEFINER | INVOKER }]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

參數說明：

OR REPLACE：如果視圖存在，則替換已有視圖。
ALGORITHM：視圖選擇演算法，默認演算法是 UNDEFINED(未定義的)由 MySQL自動選擇要使用的演算法。
DEFINER：指定視圖創建者或定義者，如果不指定該選項，則創建視圖的用戶就是定義者。
SQL SECURITY：SQL安全性，默認為DEFINER。
select_statement：創建視圖的 SELECT語句，可以從基表或其他視圖中選擇數據。
WITH CHECK OPTION：表示視圖在更新時保證約束，默認是 CASCADED。

使用 MySQL 視圖有何優點？

操作簡單方便。視圖用戶完全不需要關心視圖對應的表的結構、關聯條件和篩選條件，對用戶來說已經是過濾好的複合條件的結果集。
數據更加安全。視圖用戶只能訪問視圖中的結果集，通過視圖可以把對錶的訪問許可權限制在某些行和列上面。
數據隔離。屏蔽了源表結構變化對用戶帶來的影響，源表結構變化視圖結構不變。[^1]

MySql服務默認埠號是多少？

默認埠是 3306

查看埠命令：> show variables like 'port';

用 DISTINCT 過濾多列的規則？

DISTINCT 用於對選擇的數據去重，單列用法容易理解。比如有如下數據表 tamb：

   name    	   number
   Tencent      1
   Alibaba      2
   Bytedance    3
   Meituan      3

查詢語句：SELECT DISTINCT name FROM table tamb 結果如下：

   name    	  
   Tencent   
   Alibaba    
   Bytedance 
   Meituan

如果要求按 number 列去重同時顯示 name ，你可能會寫出查詢語句：

SELECT DISTINCT number, name FROM table tamb

多參數 DISTINCT 去重規則是：把 DISTINCT 之後的所有參數當做一個過濾條件，也就是說會對 (number, name) 整體去重處理，只有當這個組合不同才會去重，結果如下：

      	number   name
         1  Tencent
         2  Alibaba
         3  Bytedance
         3  Meituan

從結果來看好像並沒有達到我們想要的去重的效果，那要怎麼實現「按 number 列去重同時顯示 name」呢？可以用 Group By 語句：

SELECT number, name FROM table tamb GROUP BY number 輸出如下，正是我們想要的效果：

      	number   name
         1  Tencent
         2  Alibaba
         3  Bytedance

什麼是存儲過程？

一條或多條sql語句集合，有以下一些特點：

存儲過程能實現較快的執行速度。
存儲過程可以用流程式控制制語句編寫，有很強的靈活性，可以完成複雜的判斷和較複雜的運算。
存儲過程可被作為一種安全機制來充分利用。
存儲過程能夠減少網路流量

delimiter 分隔符
create procedure|proc proc_name()
begin
    sql語句
end 分隔符
delimiter ；    --還原分隔符，為了不影響後面的語句的使用
--默認的分隔符是；但是為了能在整個存儲過程中重用，因此一般需要自定義分隔符（除\外）

show procedure status like ""; --查詢存儲過程，可以不適用like進行過濾
drop procedure if exists；--刪除存儲過程

存儲過程和函數好像差不多，你說說他們有什麼區別?

存儲過程和函數是事先經過編譯並存儲在資料庫中的一段 SQL 語句的集合，調用存儲過程和函數可以簡化應用開發人員的很多工作，減少數據在資料庫和應用伺服器之間的傳輸，對於提高數據處理的效率是有好處的。

相同點

存儲過程和函數都是為了可重複的執行操作資料庫的 SQL 語句的集合。
存儲過程和函數都是一次編譯後快取起來，下次使用就直接命中已經編譯好的 sql 語句，減少網路交互提高了效率。

不同點

標識符不同，函數的標識符是 function，存儲過程是 procedure。
函數返回單個值或者表對象，而存儲過程沒有返回值，但是可以通過OUT參數返回多個值。
函數限制比較多，比如不能用臨時表，只能用表變數，一些函數都不可用等，而存儲過程的限制相對就比較少。
一般來說，存儲過程實現的功能要複雜一點，而函數的實現的功能針對性比較強
函數的參數只能是 IN 類型，存儲過程的參數可以是IN OUT INOUT三種類型。
存儲函數使用 select 調用，存儲過程需要使用 call 調用。

總結一下

本文是 MySQL 系列的第二篇，以面試問答形式總結了一系列面試常見的基礎知識點，都是非常基礎的內容，但越是基礎越顯得重要，建議收藏作為知識點筆記（據說分享、在看效果更佳），時常拿出來複習溫故而知新，MySQL 系列知識點零散龐大，本文是基礎篇，更多MySQL系列文章敬請期待。

再嘮幾句

細心的讀者可能發現這次更文距離上次已經有一個多星期了，這一個多星期並沒有偷懶不寫文章，上周出差了一趟實在是太忙了，一般來說技術人員是不用出差的，這次比較特殊作為唯一的技術人員跟著出差了。

回來的那天晚上深圳下雨航班延誤，落地深圳接近晚上十點鐘，又是冒雨打車趕回住處，回來打算寫寫文章吧，發現電腦打不開了，懷疑是不是安檢的時候被紅外輻射了？？？搞得我又重裝了電腦，還好之前寫了一半的文章有放在 github上了。哦，對了說到這，最近把我寫的所有技術文章都整理在 github 上了，按模組分門別類方便閱讀，歡迎關注和 star

項目地址

如果文章對你有幫助，答應我不要白piao好嗎？「點贊、評論、轉發」激勵我持續創作。

可以微信搜索公眾號「後端技術學堂」回復「1024」獲取50本電腦電子書，回復「進群」拉你進百人讀者技術交流群，文章每周持續更新，我們下期見！

Tags: MySQL 後端開發