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

檸檬橙1024發表於2020-09-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)(https://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 語句有哪些分類？

  1. DDL：資料定義語言（create alter drop）
  2. DML：資料操作語句（insert update delete）
  3. DTL：資料事務語句（commit collback savapoint）
  4. 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. 操作簡單方便。檢視使用者完全不需要關心檢視對應的表的結構、關聯條件和篩選條件，對使用者來說已經是過濾好的複合條件的結果集。

  2. 資料更加安全。檢視使用者只能訪問檢視中的結果集，通過檢視可以把對錶的訪問許可權限制在某些行和列上面。

  3. 資料隔離。遮蔽了源表結構變化對使用者帶來的影響，源表結構變化檢視結構不變。[^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本計算機電子書，回覆「進群」拉你進百人讀者技術交流群，文章每週持續更新，我們下期見！

相關文章