經(jīng)典MySQL面試題有哪些

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

1.數(shù)據(jù)庫三范式是什么?

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

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

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

2.有哪些數(shù)據(jù)庫優(yōu)化方面的經(jīng)驗?

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

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

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

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

1. 普通索引: 即針對數(shù)據(jù)庫表創(chuàng)建索引

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

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

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

4.以及在 mysql 數(shù)據(jù)庫中索引的工作機(jī)制是什么？

• 數(shù)據(jù)庫索引，是數(shù)據(jù)庫管理系統(tǒng)中一個排序的數(shù)據(jù)結(jié)構(gòu)，以協(xié)助快速查詢、更新數(shù)據(jù)庫表中數(shù)據(jù)。索引的實現(xiàn)通常使用 B 樹及其變種 B+樹

5.MySQL 的基礎(chǔ)操作命令:

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

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

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

4. 列出所有數(shù)據(jù)庫:運行命令 show databases;

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

6. 列出某個數(shù)據(jù)庫內(nèi)所有表: show tables;

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

6.mysql 的復(fù)制原理以及流程。

Mysql 內(nèi)建的復(fù)制功能是構(gòu)建大型，高性能應(yīng)用程序的基礎(chǔ)。將 Mysql 的數(shù)據(jù)分布到多個系統(tǒng)上去，這種分布的機(jī)制，是通過將 Mysql 的某一臺主機(jī)的數(shù)據(jù)復(fù)制到其它主機(jī)（slaves）上，并重新執(zhí)行一遍來實現(xiàn)的。* 復(fù)制過程中一個服務(wù)器充當(dāng)主服務(wù)器，而一個或多個其它服務(wù)器充當(dāng)從服務(wù)器。

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

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

1. 主服務(wù)器把更新記錄到二進(jìn)制日志文件中。

2. 從服務(wù)器把主服務(wù)器的二進(jìn)制日志拷貝到自己的中繼日志（replay log）中。3. 從服務(wù)器重做中繼日志中的時間，把更新應(yīng)用到自己的數(shù)據(jù)庫上。

7.mysql 支持的復(fù)制類型?

1. 基于語句的復(fù)制：在主服務(wù)器上執(zhí)行的 SQL 語句，在從服務(wù)器上執(zhí)行同樣的語句。MySQL 默認(rèn)采用基于語句的復(fù)制，效率比較高。一旦發(fā)現(xiàn)沒法精確復(fù)制時，會自動選著基于行的復(fù)制。

2. 基于行的復(fù)制：把改變的內(nèi)容復(fù)制過去，而不是把命令在從服務(wù)器上執(zhí)行一遍. 從mysql5.0 開始支持

3. 混合類型的復(fù)制: 默認(rèn)采用基于語句的復(fù)制，一旦發(fā)現(xiàn)基于語句的無法精確的復(fù)制時，就會采用基于行的復(fù)制。

8.mysql 中 myisam 與 innodb 的區(qū)別？

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Read Uncommitted（讀取未提交內(nèi)容） >> 在該隔離級別，所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果。本隔離級別很少用于實際應(yīng)用，因為它的性能也不比其他級別好多少。讀取未提交的數(shù)據(jù)，也被稱之為臟讀（Dirty Read）。

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

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

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

讓眼睛休息休息，再繼續(xù)吧！

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

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

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

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

13.MySQL 中控制內(nèi)存分配的全局參數(shù)，有哪些？

1. Keybuffersize：> * keybuffersize 指定索引緩沖區(qū)的大小，它決定索引處理的速度，尤其是索引讀的速度。通過檢查狀態(tài)值Keyreadrequests 和 Keyreads，可以知道 keybuffersize 設(shè)置是否合理。比例 keyreads /keyreadrequests 應(yīng)該盡可能的低，至少是1:100，1:1000 更好（上述狀態(tài)值可以使用 SHOW STATUS LIKE‘keyread%'獲得）。> * keybuffersize 只對 MyISAM 表起作用。即使你不使用MyISAM 表，但是內(nèi)部的臨時磁盤表是 MyISAM 表，也要使用該值?？梢允褂脵z查狀態(tài)值 createdtmpdisktables 得知詳情。對于 1G 內(nèi)存的機(jī)器，如果不使用 MyISAM表，推薦值是 16M（8-64M） > * keybuffersize 設(shè)置注意事項 >>>1. 單個keybuffer 的大小不能超過 4G，如果設(shè)置超過 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 設(shè)置為物理內(nèi)存的 1/4(針對 MyISAM 引 擎)，甚至是物理內(nèi)存的 30%~40%，如果keybuffersize 設(shè)置太大，系統(tǒng)就會頻繁的換頁，降低系統(tǒng)性能。因為 MySQL 使用操作系統(tǒng)的緩存來緩存數(shù)據(jù)，所以我們得為系統(tǒng)留夠足夠的內(nèi)存；在很多情況下數(shù)據(jù)要比索引大得多。>>>3. 如果機(jī)器性能優(yōu)越，可以設(shè)置多個keybuffer,分別讓不同的keybuffer 來緩存專門的索引

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

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

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

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

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

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

1. 前者要解析數(shù)據(jù)字典，后者不需要

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

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

4. 后者可以建立索引進(jìn)行優(yōu)化，前者無法優(yōu)化

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

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

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

2. 影響結(jié)果范圍：where 從表讀出數(shù)據(jù)的行數(shù)，having 返回客戶端的行數(shù)

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

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

17.MySQL 當(dāng)記錄不存在時 insert,當(dāng)記錄存在時 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 ;

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