MySQL面試題

標籤：關係型資料庫   儲存   多個   逾時   欄位   created   中繼   進程   shel   

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的預設交易隔離等級，它確保同一事務的多個執行個體在並發讀取資料時，會看到同樣的資料行。不過理論上，這會導致另一個棘手的問題：幻讀（Phantom Read）。簡單的說，幻讀指當使用者讀取某一範圍的資料行時，另一個事務又在該範圍插入入了新行，當使用者再讀取該範圍的資料行時，會發現有新的“幻影” 行。InnoDB和Falcon儲存引擎通過多版本並發控制（MVCC，Multiversion Concurrency Control 間隙鎖）機制解決了該問題。註：其實多版本只是解決不可重複讀取問題，而加上間隙鎖（也就是它這裡所謂的並發控制）才解決了幻讀問題。
4. Serializable（可序列化） >> 這是最高的隔離等級，它通過強制事務排序，使之不可能相互衝突，從而解決幻讀問題。簡言之，它是在每個讀的資料行上加上共用鎖定。在這個層級，可能導致大量的逾時現象和鎖競爭。 <table> <thead> <tr> <th align="left">隔離等級</th> <th align="left">髒讀（Dirty Read）</th> <th align="left">不可重複讀取（NonRepeatable Read）</th> <th align="left">幻讀（Phantom Read）</th> </tr> </thead> <tbody><tr> <td align="left">未提交讀（Read uncommitted）</td> <td align="left">可能</td> <td align="left">可能</td> <td align="left">可能</td> </tr> <tr> <td align="left">已提交讀（Read committed）</td> <td align="left">不可能</td> <td align="left">可能</td> <td align="left">可能</td> </tr> <tr> <td align="left">可重複讀（Repeatable read）</td> <td align="left">不可能</td> <td align="left">不可能</td> <td align="left">可能</td> </tr> <tr> <td align="left">可序列化（SERIALIZABLE）</td> <td align="left">不可能</td> <td align="left">不可能</td> <td align="left">不可能</td> </tr> </tbody></table>

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

如果欄位裡面有大欄位（text,blob)類型的，而且這些欄位的訪問並不多，這時候放在一起就變成缺點了。 MYSQL資料庫的記錄儲存是按行儲存的，資料區塊大小又是固定的（16K），每條記錄越小，相同的Block Storage的記錄就越多。此時應該把大欄位拆走，這樣應付大部分小欄位的查詢時，就能提高效率。當需要查詢大欄位時，此時的關聯查詢是不可避免的，但也是值得的。拆分開後，對欄位的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. 如果機器效能優越，可以設定多個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 減少了對磁碟的讀寫。 當資料提交或滿足檢查點條件後才一次性將記憶體資料重新整理到磁碟中。然而記憶體還有作業系統或資料庫其他進程使用, 一般設定 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 ; `

MySQL面試題