歡迎關注專欄： Java架構技術進階。裡面有大量batj面試題集錦，還有各種技術分享，如有好文章也歡迎投稿哦。   微信公眾號：慕容千語的架構筆記。歡迎關注一起進步。

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 database name; 進入名為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. Key buffersize：> * key buffersize 指定索引緩衝區的大小，它決定索引處理的速度，尤其是索引讀的速度。透過檢查狀態值Key readrequests 和 Key reads，可以知道 keybuffer size 設定是否合理。比例 keyreads /key readrequests 應該儘可能的低，至少是1:100，1:1000 更好（上述狀態值可以使用 SHOW STATUS LIKE‘key read%'獲得）。> * keybuffer size 只對 MyISAM 表起作用。即使你不使用MyISAM 表，但是內部的臨時磁碟表是 MyISAM 表，也要使用該值。可以使用檢查狀態值 createdtmp disktables 得知詳情。對於 1G 記憶體的機器，如果不使用 MyISAM表，推薦值是 16M（8-64M） > * key buffersize 設定注意事項 >>>1. 單個key buffer 的大小不能超過 4G，如果設定超過 4G，就有可能遇到下面 3 個bug: >>>>>   
   >>>>>   
   >>>>>   
   >>>2. 建議 keybuffer 設定為實體記憶體的 1/4(針對 MyISAM 引 擎)，甚至是實體記憶體的 30%~40%，如果key buffersize 設定太大，系統就會頻繁的換頁，降低系統效能。因為 MySQL 使用作業系統的快取來快取資料，所以我們得為系統留夠足夠的記憶體；在很多情況下資料要比索引大得多。>>>3. 如果機器效能優越，可以設定多個key buffer,分別讓不同的keybuffer 來快取專門的索引
2. innodb bufferpool_size >  表示緩衝池位元組大小，InnoDB 快取表和索引資料的記憶體區域。mysql 預設的值是 128M。最大值與你的CPU 體系結構有關，在 32 位作業系統，最大值是 4294967295(2^32-1) ，在 64 位作業系統，最大值為18446744073709551615 (2^64-1)。>  在 32 位作業系統中，CPU 和作業系統實用的最大大小低於設定的最大值。如果設定的緩衝池的大小大於 1G，設定innodbbufferpool instances 的值大於 1. >  資料讀寫在記憶體中非常快, innodbbufferpool size 減少了對磁碟的讀寫。當資料提交或滿足檢查點條件後才一次性將記憶體資料重新整理到磁碟中。然而記憶體還有作業系統或資料庫其他程式使用, 一般設定bufferpool 大小為總記憶體的 3/4 至 4/5。若設定不當, 記憶體使用可能浪費或者使用過多。對於繁忙的伺服器, buffer pool 將劃分為多個例項以提高系統併發性, 減少執行緒間讀寫快取的爭用。buffer pool 的大小首先受 innodbbuffer*pool_instances 影響,當然影響較小。
3. query cachesize > *當 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. read buffersize >是 MySQL 讀入緩衝區大小。對錶進行順序掃描的請求將分配一個讀入緩衝區，MySQL 會為它分配一段記憶體緩衝區。read buffersize 變數控制這一緩衝區的大小。如果對錶的順序掃描請求非常頻繁，並且你認為頻繁掃描進行得太慢，可以透過增加該變數值以及記憶體緩衝區大小提高其效能。

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 ;

您的關注轉發是我繼續前行的動力，感謝您的閱讀，讚賞。

歡迎關注專欄： Java架構技術進階。裡面有大量batj面試題集錦，還有各種技術分享，如有好文章也歡迎投稿哦。   微信公眾號：慕容千語的架構筆記。歡迎關注一起進步。