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 快很多，是以，如果可以确認合并的兩個結

果集中不包含重複資料且不需要排序時的話，那麼就使用 UNION

ALL。 >>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 mysql

status，在 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 maximum

display width (最大顯示寬度)for integer types. The maximum

legal 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 不會經常更新，以讀為2

為主，将該字段拆成子表好處是什麼？

如果字段裡面有大字段（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 <br

/> >>>>> http://bugs.mysql.com/bug.php?id=29419 <br

/> >>>>> http://bugs.mysql.com/bug.php?id=5731 <br

/> >>>2. 建議 keybuffer 設定為實體記憶體的 1/4(針對 MyISAM 引

擎)，甚至是實體記憶體的 30%~40%，如果 keybuffersize 設定太大，系統就會頻繁的換頁，降低系統性能。因為 MySQL 使用作業系統的緩

存來緩存資料，是以我們得為系統留夠足夠的記憶體；在很多情況下資料

要比索引大得多。 >>>3. 如果機器性能優越，可以設定多個

key*buffer,*分别讓不同的 keybuffer 來緩存專門的索引

\2. innodbbufferpool_size > *表示緩沖池位元組大小，*InnoDB 緩存

表和索引資料的記憶體區域。mysql 預設的值是 128M。最大值與你的

CPU 體系結構有關，在 32 位作業系統，最大值是 4294967295

(2^32-1) ，在 64 位作業系統，最大值為

18446744073709551615 (2^64-1)**。 > 在 32 位作業系統中，

CPU 和作業系統實用的最大大小低于設定的最大值。如果設定的緩沖池

的大小大于 1G，設定 innodbbufferpoolinstances 的值大于 *1. > **

資料讀寫在記憶體中非常快*, innodbbufferpool*size 減少了對磁盤的讀

寫。 當資料送出或滿足檢查點條件後才一次性将記憶體資料重新整理到磁盤

中。然而記憶體還有作業系統或資料庫其他程序使用, 一般設定 buffer

pool 大小為總記憶體的 3/4 至 4/5。 若設定不當, 記憶體使用可能浪費

或者使用過多。 對于繁忙的伺服器, buffer pool 将劃分為多個執行個體以

提高系統并發性, 減少線程間讀寫緩存的争用。buffer pool 的大小首

先受 innodbbufferpool_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 KEY

UPDATE c=c+1;

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

`SQL insert into student (stuid,stuname,deptid) select 10,‘xzm’,3

from student where stuid > 8;

update student a inner join student b on b.stuID=10 set

a.stuname=concat(b.stuname, b.stuID) where a.stuID=10 ; `