經典MySQL面試題有哪些

這篇文章主要講解了“經典MySQL面試題有哪些”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學習“經典MySQL面試題有哪些”吧！

1.數據庫三范式是什么?

1. 第一范式（1NF）：字段具有原子性,不可再分。(所有關系型數據庫系統都滿足第一范式數據庫表中的字段都是單一屬性的，不可再分)

2. 第二范式（2NF）是在第一范式（1NF）的基礎上建立起來的，即滿足第二范式（2NF）必須先滿足第一范式（1NF）。要求數據庫表中的每個實例或行必須可以被惟一地區分。通常需要為表加上一個列，以存儲各個實例的惟一標識。這個惟一屬性列被稱為主關鍵字或主鍵。

3. 滿足第三范式（3NF）必須先滿足第二范式（2NF）。簡而言之，第三范式（3NF）要求一個數據庫表中不包含已在其它表中已包含的非主關鍵字信息。>所以第三范式具有如下特征：>>1. 每一列只有一個值 >>2. 每一行都能區分。>>3. 每一個表都不包含其他表已經包含的非主關鍵字信息。

2.有哪些數據庫優化方面的經驗?

1. 用 PreparedStatement， 一般來說比 Statement 性能高：一個 sql發給服務器去執行，涉及步驟：語法檢查、語義分析， 編譯，緩存。

2. 有外鍵約束會影響插入和刪除性能，如果程序能夠保證數據的完整性，那在設計數據庫時就去掉外鍵。

3. 表中允許適當冗余，譬如，主題帖的回復數量和最后回復時間等4. UNION ALL 要比UNION 快很多，所以，如果可以確認合并的兩個結果集中不包含重復數據且不需要排序時的話，那么就使用 UNIONALL。>>UNION 和 UNION ALL 關鍵字都是將兩個結果集合并為一個，但這兩者從使用和效率上來說都有所不同。>1. 對重復結果的處理：UNION 在進行表鏈接后會篩選掉重復的記錄，Union All 不會去除重復記錄。>2. 對排序的處理：Union 將會按照字段的順序進行排序；UNION ALL 只是簡單的將兩個結果合并后就返回。

3.請簡述常用的索引有哪些種類?

1. 普通索引: 即針對數據庫表創建索引

2. 唯一索引: 與普通索引類似，不同的就是：MySQL 數據庫索引列的值必須唯一，但允許有空值

3. 主鍵索引: 它是一種特殊的唯一索引，不允許有空值。一般是在建表的時候同時創建主鍵索引

4. 組合索引: 為了進一步榨取 MySQL 的效率，就要考慮建立組合索引。即將數據庫表中的多個字段聯合起來作為一個組合索引。

4.以及在 mysql 數據庫中索引的工作機制是什么？

• 數據庫索引，是數據庫管理系統中一個排序的數據結構，以協助快速查詢、更新數據庫表中數據。索引的實現通常使用 B 樹及其變種 B+樹

5.MySQL 的基礎操作命令:

1. MySQL 是否處于運行狀態:Debian 上運行命令 service mysqlstatus，在 RedHat上運行命令 service mysqld status

2. 開啟或停止 MySQL 服務 :運行命令 service mysqld start 開啟服務；運行命令service mysqld stop 停止服務

3. Shell 登入 MySQL: 運行命令 mysql -u root -p

4. 列出所有數據庫:運行命令 show databases;

5. 切換到某個數據庫并在上面工作:運行命令 use databasename; 進入名為databasename 的數據庫

6. 列出某個數據庫內所有表: show tables;

7. 獲取表內所有 Field 對象的名稱和類型 :describe table_name;

6.mysql 的復制原理以及流程。

Mysql 內建的復制功能是構建大型，高性能應用程序的基礎。將 Mysql 的數據分布到多個系統上去，這種分布的機制，是通過將 Mysql 的某一臺主機的數據復制到其它主機（slaves）上，并重新執行一遍來實現的。* 復制過程中一個服務器充當主服務器，而一個或多個其它服務器充當從服務器。

主服務器將更新寫入二進制日志文件，并維護文件的一個索引以跟蹤日志循環。這些日志可以記錄發送到從服務器的更新。當一個從服務器連接主服務器時，它通知主服務器在日志中讀取的最后一次成功更新的位置。

從服務器接收從那時起發生的任何更新，然后封鎖并等待主服務器通知新的更新。過程如下

1. 主服務器把更新記錄到二進制日志文件中。

2. 從服務器把主服務器的二進制日志拷貝到自己的中繼日志（replay log）中。3. 從服務器重做中繼日志中的時間，把更新應用到自己的數據庫上。

7.mysql 支持的復制類型?

1. 基于語句的復制：在主服務器上執行的 SQL 語句，在從服務器上執行同樣的語句。MySQL 默認采用基于語句的復制，效率比較高。一旦發現沒法精確復制時，會自動選著基于行的復制。

2. 基于行的復制：把改變的內容復制過去，而不是把命令在從服務器上執行一遍. 從mysql5.0 開始支持

3. 混合類型的復制: 默認采用基于語句的復制，一旦發現基于語句的無法精確的復制時，就會采用基于行的復制。

8.mysql 中 myisam 與 innodb 的區別？

1. 事務支持 > *MyISAM：強調的是性能，每次查詢具有原子性,其執行速度比 InnoDB 類型更快，但是不提供事務支持。> *InnoDB：提供事務支持事務，外部鍵等高級數據庫功能。具有事務(commit)、回滾(rollback)和崩潰修復能力(crash recovery capabilities)的事務安全(transaction-safe (ACID compliant))型表。

2. InnoDB 支持行級鎖，而 MyISAM 支持表級鎖. >> 用戶在操作myisam 表時，select，update，delete，insert 語句都會給表自動加鎖，如果加鎖以后的表滿足insert 并發的情況下，可以在表的尾部插入新的數據。

3. InnoDB 支持 MVCC, 而 MyISAM 不支持

4. InnoDB 支持外鍵，而 MyISAM 不支持

5. 表主鍵 > *MyISAM：允許沒有任何索引和主鍵的表存在，索引都是保存行的地址。> *InnoDB：如果沒有設定主鍵或者非空唯一索引，就會自動生成一個6 字節的主鍵(用戶不可見)，數據是主索引的一部分，附加索引保存的是主索引的值。

6. InnoDB 不支持全文索引，而 MyISAM 支持。

7. 可移植性、備份及恢復 > *MyISAM：數據是以文件的形式存儲，所以在跨平臺的數據轉移中會很方便。在備份和恢復時可單獨針對某個表進行操作。> *InnoDB：免費的方案可以是拷貝數據文件、備份binlog，或者用 mysqldump，在數據量達到幾十 G 的時候就相對痛苦了

8. 存儲結構 > *MyISAM：每個 MyISAM 在磁盤上存儲成三個文件。第一個文件的名字以表的名字開始，擴展名指出文件類型。.frm 文件存儲表定義。數據文件的擴展名為.MYD (MYData)。索引文件的擴展名是.MYI (MYIndex)。> *InnoDB：所有的表都保存在同一個數據文件中（也可能是多個文件，或者是獨立的表空間文件），InnoDB表的大小只受限于操作系統文件的大小，一般為 2GB。

9.mysql 中 varchar 與 char 的區別以及 varchar(50)中的 50 代表的涵義？

1. varchar 與 char 的區別: char 是一種固定長度的類型，varchar 則是一種可變長度的類型.

2. varchar(50)中 50 的涵義 : 最多存放 50 個字節

3. int（20）中 20 的涵義: int(M)中的 M indicates the maximumdisplay width (最大顯示寬度)for integer types. The maximumlegal display width is 255.

10.MySQL 中 InnoDB 支持的四種事務隔離級別名稱，以及逐級之間的區別？

1. Read Uncommitted（讀取未提交內容） >> 在該隔離級別，所有事務都可以看到其他未提交事務的執行結果。本隔離級別很少用于實際應用，因為它的性能也不比其他級別好多少。讀取未提交的數據，也被稱之為臟讀（Dirty Read）。

2. Read Committed（讀取提交內容） >> 這是大多數數據庫系統的默認隔離級別（但不是 MySQL 默認的）。它滿足了隔離的簡單定義：一個事務只能看見已經提交事務所做的改變。這種隔離級別也支持所謂的不可重復讀（Nonrepeatable Read），因為同一事務的其他實例在該實例處理其間可能會有新的 commit，所以同一 select 可能返回不同結果。

3. Repeatable Read（可重讀） >> 這是 MySQL 的默認事務隔離級別，它確保同一事務的多個實例在并發讀取數據時，會看到同樣的數據行。不過理論上，這會導致另一個棘手的問題：幻讀（PhantomRead）。簡單的說，幻讀指當用戶讀取某一范圍的數據行時，另一個事務又在該范圍內插入了新行，當用戶再讀取該范圍的數據行時，會發現有新的“幻影” 行。InnoDB 和 Falcon 存儲引擎通過多版本并發控制（MVCC，Multiversion Concurrency Control 間隙鎖）機制解決了該問題。注：其實多版本只是解決不可重復讀問題，而加上間隙鎖（也就是它這里所謂的并發控制）才解決了幻讀問題。

4. Serializable（可串行化） >> 這是最高的隔離級別，它通過強制事務排序，使之不可能相互沖突，從而解決幻讀問題。簡言之，它是在每個讀的數據行上加上共享鎖。在這個級別，可能導致大量的超時現象和鎖競爭。

讓眼睛休息休息，再繼續吧！

11.表中有大字段 X（例如：text 類型），且字段 X 不會經常更新，以讀為為主，將該字段拆成子表好處是什么？

如果字段里面有大字段（text,blob)類型的，而且這些字段的訪問并不多，這時候放在一起就變成缺點了。MYSQL 數據庫的記錄存儲是按行存儲的，數據塊大小又是固定的（16K），每條記錄越小，相同的塊存儲的記錄就越多。此時應該把大字段拆走，這樣應付大部分小字段的查詢時，就能提高效率。當需要查詢大字段時，此時的關聯查詢是不可避免的，但也是值得的。拆分開后，對字段的 UPDAE 就要 UPDATE 多個表了

12.MySQL 中 InnoDB 引擎的行鎖是通過加在什么上完成（或稱實現）的？

InnoDB 行鎖是通過給索引上的索引項加鎖來實現的，這一點 MySQL 與Oracle 不同，后者是通過在數據塊中對相應數據行加鎖來實現的。InnoDB 這種行鎖實現特點意味著：只有通過索引條件檢索數據，InnoDB 才使用行級鎖，否則，InnoDB 將使用表鎖！

13.MySQL 中控制內存分配的全局參數，有哪些？

1. Keybuffersize：> * keybuffersize 指定索引緩沖區的大小，它決定索引處理的速度，尤其是索引讀的速度。通過檢查狀態值Keyreadrequests 和 Keyreads，可以知道 keybuffersize 設置是否合理。比例 keyreads /keyreadrequests 應該盡可能的低，至少是1:100，1:1000 更好（上述狀態值可以使用 SHOW STATUS LIKE‘keyread%'獲得）。> * keybuffersize 只對 MyISAM 表起作用。即使你不使用MyISAM 表，但是內部的臨時磁盤表是 MyISAM 表，也要使用該值。可以使用檢查狀態值 createdtmpdisktables 得知詳情。對于 1G 內存的機器，如果不使用 MyISAM表，推薦值是 16M（8-64M） > * keybuffersize 設置注意事項 >>>1. 單個keybuffer 的大小不能超過 4G，如果設置超過 4G，就有可能遇到下面 3 個bug: >>>>>  http://bugs.mysql.com/bug.php?id=29446 
   >>>>>  http://bugs.mysql.com/bug.php?id=29419 
   >>>>>  http://bugs.mysql.com/bug.php?id=5731 
   >>>2. 建議 keybuffer 設置為物理內存的 1/4(針對 MyISAM 引 擎)，甚至是物理內存的 30%~40%，如果keybuffersize 設置太大，系統就會頻繁的換頁，降低系統性能。因為 MySQL 使用操作系統的緩存來緩存數據，所以我們得為系統留夠足夠的內存；在很多情況下數據要比索引大得多。>>>3. 如果機器性能優越，可以設置多個keybuffer,分別讓不同的keybuffer 來緩存專門的索引

2. innodbbufferpool_size > 表示緩沖池字節大小，InnoDB 緩存表和索引數據的內存區域。mysql 默認的值是 128M。最大值與你的CPU 體系結構有關，在 32 位操作系統，最大值是 4294967295(2^32-1) ，在 64 位操作系統，最大值為18446744073709551615 (2^64-1)。> 在 32 位操作系統中，CPU 和操作系統實用的最大大小低于設置的最大值。如果設定的緩沖池的大小大于 1G，設置innodbbufferpoolinstances 的值大于 1. > 數據讀寫在內存中非常快, innodbbufferpoolsize 減少了對磁盤的讀寫。當數據提交或滿足檢查點條件后才一次性將內存數據刷新到磁盤中。然而內存還有操作系統或數據庫其他進程使用, 一般設置bufferpool 大小為總內存的 3/4 至 4/5。若設置不當, 內存使用可能浪費或者使用過多。對于繁忙的服務器, buffer pool 將劃分為多個實例以提高系統并發性, 減少線程間讀寫緩存的爭用。buffer pool 的大小首先受 innodbbuffer*pool_instances 影響,當然影響較小。

3. querycachesize > *當 mysql 接收到一條 select 類型的 query時，mysql 會對這條query 進行 hash 計算而得到一個 hash 值，然后通過該 hash 值到 query cache 中去匹配，如果沒有匹配中，則將這個hash 值存放在一個 hash 鏈表中，同時將 query 的結果集存放進cache 中，存放 hash 值的鏈表的每一個 hash 節點存放了相應query結果集在 cache 中的地址，以及該 query 所涉及到的一些 table 的相關信息；如果通過 hash 值匹配到了一樣的 query，則直接將 cache 中相應的 query 結果集返回給客戶端。如果 mysql 任何一個表中的任何一條數據發生了變化，便會通知query cache 需要與該 table 相關的query 的 cache 全部失效，并釋放占用的內存地址。> *query cache優缺點 >> 1. query 語句的 hash 計算和 hash 查找帶來的資源消耗。mysql 會對每條接收到的 select 類型的 query 進行 hash 計算然后查找該query 的 cache 是否存在，雖然 hash 計算和查找的效率已經足夠高了，一條query 所帶來的消耗可以忽略，但一旦涉及到高并發，有成千上萬條 query 時，hash計算和查找所帶來的開銷就的重視了；>> 2. query cache 的失效問題。如果表變更比較頻繁，則會造成 query cache 的失效率非常高。表變更不僅僅指表中的數據發生變化，還包括結構或者索引的任何變化；>> 3. 對于不同 sql 但同一結果集的 query都會被緩存，這樣便會造成內存資源的過渡消耗。sql 的字符大小寫、空格或者注釋的不同，緩存都是認為是不同的 sql（因為他們的 hash 值會不同）；>> 4. 相關參數設置不合理會造成大量內存碎片，相關的參數設置會稍后介紹。

4. readbuffersize >是 MySQL 讀入緩沖區大小。對表進行順序掃描的請求將分配一個讀入緩沖區，MySQL 會為它分配一段內存緩沖區。readbuffersize 變量控制這一緩沖區的大小。如果對表的順序掃描請求非常頻繁，并且你認為頻繁掃描進行得太慢，可以通過增加該變量值以及內存緩沖區大小提高其性能。

14.若一張表中只有一個字段 VARCHAR(N)類型，utf8 編碼，則 N 最大值為多少(精確到數量級即可)?

由于 utf8 的每個字符最多占用 3 個字節。而 MySQL 定義行的長度不能超過65535，因此 N 的最大值計算方法為：(65535-1-2)/3。減去 1 的原因是實際存儲從第二個字節開始，減去 2 的原因是因為要在列表長度存儲實際的字符長度，除以 3 是因為utf8 限制：每個字符最多占用 3 個字節。

*15. [SELECT ] 和[SELECT 全部字段]的 2 種寫法有何優缺點?

1. 前者要解析數據字典，后者不需要

2. 結果輸出順序，前者與建表列順序相同，后者按指定字段順序。

3. 表字段改名，前者不需要修改，后者需要改

4. 后者可以建立索引進行優化，前者無法優化

5. 后者的可讀性比前者要高

16.HAVNG 子句 和 WHERE 的異同點?

1. 語法上：where 用表中列名，having 用 select 結果別名

2. 影響結果范圍：where 從表讀出數據的行數，having 返回客戶端的行數

3. 索引：where 可以使用索引，having 不能使用索引，只能在臨時結果集操作

4. where 后面不能使用聚集函數，having 是專門使用聚集函數的。

17.MySQL 當記錄不存在時 insert,當記錄存在時 update，語句怎么寫？

INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEYUPDATE c=c+1;

18.MySQL 的 insert 和 update 的 select 語句語法

SQL insert into student (stuid,stuname,deptid) select 10,'xzm',3from student where stuid > 8;
update student a inner join student b on b.stuID=10 seta.
stuname=concat(b.stuname, b.stuID) where a.stuID=10 ;

感謝各位的閱讀，以上就是“經典MySQL面試題有哪些”的內容了，經過本文的學習后，相信大家對經典MySQL面試題有哪些這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關知識點的文章，歡迎關注！