MySQL面試總結 # MySQL的存儲引擎 `MyISAM`（預設表類型）：非事務的存儲引擎，基於傳統的`ISAM`（有索引的順序訪問方法）類型，是存儲記錄和文件的標準方法，不是事務安全，不支持外鍵，適用於頻繁的查詢。表鎖，不會出現死鎖，適合小數據和小併發。 - 為什麼不會出死鎖？（沒有事務就不會 ...
MySQL面試總結
# MySQL的存儲引擎
`MyISAM`（預設表類型）：非事務的存儲引擎，基於傳統的`ISAM`（有索引的順序訪問方法）類型，是存儲記錄和文件的標準方法，不是事務安全，不支持外鍵，適用於頻繁的查詢。表鎖，不會出現死鎖，適合小數據和小併發。
- 為什麼不會出死鎖？（沒有事務就不會繼續持有鎖）
答：因為`MyISAM`再查詢的時候，會同時鎖定這個`sql`裡面所有用到的表（獲取鎖的順序是一致的），不局限與一張表，再寫鎖又重疊時，就得等待。
**註意：【`MySQL5.5`之前預設的是`MyISAM`引擎了，5.5之後的版本預設都是`innodb`作為存儲引擎】**
`innodb`：支持事務安全的存儲引擎，適用於插入和更新，支持外鍵，行鎖，事務。適合大數據，大併發。特別是針對多個併發和`QPS`較高的情況。
- `QPS：`就是每秒查詢率，`QPS`是對一個特定伺服器再規定時間內能處理多少流量的衡量標準。
- `TPS：`就是每秒傳輸處理的事務個數。
- `innodb`的行鎖模式：共用鎖，排他鎖，意向共用鎖（表鎖），意向排他鎖（表鎖），間隙鎖。（註意：如果`sql`語句沒有使用索引，`innodb`不能確定操作的行時，使用意向鎖（表鎖））。
- 死鎖問題
- 什麼是死鎖？
死鎖就是當倆個事務都需要獲取對方持有的排他鎖才能完成事務的時候，就導致了迴圈鎖等待，常見的死鎖類型。
- 解決辦法
1. 資料庫參數
2. 儘量約定程式讀取表的順序
3. 在處理一個表時，儘量對處理的順序排序
4. 調整事務隔離級別（避免倆個事務同時操作一行不存在的數據，容易發生死鎖）
存儲引擎還有：
- `MERGE：`將多個類似的`MyISAM`表分組為一個表，可以處理非事務性表，預設情況下包括這些表。
- `MEMORY：`提供記憶體中的表，以前稱為堆。它在RAM中處理所有數據，以便比在磁碟上存儲數據更快地訪問。用於快速查找引用和其他相同的數據。
- `EXAMPLE：`可以使用此引擎創建表，但不能存儲或獲取數據。這樣做的目的是教開發人員如何編寫新的存儲引擎。
- `ARCHIVE：`用於存儲大量數據，不支持索引。
- `CSV：`在文本文件中以逗號分隔值格式存儲數據。
- `BLACKHOLE：`受要存儲的數據，但始終返回空。
- `FEDERATED：`將數據存儲在遠程資料庫中。
# 數據表的類型
`MyISAM`，`InnoDB`，`MEMORY`，`HEAP`，`BOB`，`ARCHIVE`，`CSV`等
- `MYISAM：`成熟穩定，易於管理，快速讀取。表級鎖。
- `Innodb：`數據行鎖。占用空間大，不支持全文索引。
# `MySQL`作為發佈系統的儲存，一天五萬條以上的增量，怎麼優化？
1. 設計良好的資料庫結構，允許部分數據冗餘，儘量避免join查詢，提高效率。
2. 選擇合適的表欄位類型和存儲引擎，適當添加索引。
3. `MySQL`庫主從讀寫分離。
4. 找規律分表，減少表單中的數據量，提高查詢速度。
5. 添加緩存機制。可以使用`Redis`緩存。
6. 不經常改動的頁面，生成靜態頁面。
7. 寫高效率的`sql`語句。如：`SELECT * FROM TABEL 改為 SELECT field_1, field_2, field_3 FROM TABLE`。
為什麼要避免使用join查詢?
答：減少消耗。
# 對於大流量網站，如何解決各頁面統計訪問量問題？
1. 確認伺服器是否能支撐當前訪問量。
2. 優化資料庫訪問。
3. 禁止外部訪問，如圖片盜鏈。
4. 控制文件下載。
5. 使用不同主機進行分流。
6. 使用瀏覽統計軟體，瞭解訪問量，有針對性的進行優化。
# 如何進行`SQL`優化？
1. 選擇正確的存儲引擎。
每個引擎都有利有弊，比如`MyISAM`，適用於大量查詢，對大量寫操作並不是很好，`update`一個欄位都會把整個表鎖起來，而I`nnodb`，對一些小的應用，它比`MyISAM`慢，但它支持行鎖，再寫操作的時候，很優秀，它還支持更多的高級應用。
2. 優化欄位的數據類型
一個原則，越小的越快，如果一個表只有幾列，那我們就不用用`INT`來做主鍵，可以使用`MEDIUMINT`，`SMALLINT`或是更小的`TINYINT`會更經濟一些，如果不需要記錄時間，使用`DATE`要比`DATETIME`好的多，也要留夠足夠的空間進行擴展。
3. 為搜索欄位添加索引
索引不一定只添加給主鍵或唯一的欄位，如果在表中有某個欄位經常用來做搜索，那就為它建立索引，如果要搜索的欄位是大的文本欄位，那應該為它建立全文索引。
4. 避免使用`select *`因為從資料庫讀出的數據越多，那麼查詢就會越慢。如果資料庫服務和WEB伺服器在不同的機器上的話，還會增加網路傳輸的負載。即使要查詢表的所有欄位，也儘量不要用`*`通配符。
5. 使用`ENUM`而不是`VARCHAR`
`ENUM`類型是非常快和緊湊的，它保存的是`TINYINT`，但外表上顯示的是字元串，做一些選項列表很好，比如：性別，民族，部門，狀態之類的欄位，取值有限而且固定。
6. 儘可能使用`NOT NULL`
`NULL`其實也需要額外空間的，在進行比較的時候，程式也會變得複雜，並不是並不可以用`NULL`，在現實的複雜情況下，依然會有些情況需要使用`NULL`值。
7. 固定長度的表會更快
如果表中的所有欄位都是固定長度的，那整個表會被認為是`“static”`或“`fixed-lenght”`。例如表中沒有`VARCHAR`，`TEXT`，`BLOB`，只要表中其中一個欄位是這些類型，那麼這個表就不是“固定長度靜態表”了，這樣的話`MySQL`引擎會用另一種方法來處理。
固定長度的表也容易被緩存和重建，唯一的副作用就是，固定長度的欄位會浪費一些空間，因為固定長度的欄位無論用不用，都會分配那麼多的空間。
# 如何設計一個高併發的系統
1. 資料庫優化，喝的事務隔離級別，`SQL`語句，索引優化。
2. 使用緩存，儘量減少資料庫`IO`操作。
3. 分散式資料庫，分散式緩存。
4. 伺服器負載均衡。
# 什麼情況下設置了索引卻無法使用
1. 以%開頭`LIKE`，模糊匹配。
2. `OR`語句前後沒有同時使用索引。
3. 數據類型出現隱式轉化，如`varchar`不加單引號可能會轉換為`int`型。
# `SQL`註入的主要特點
1. 變種極多，攻擊簡單，危害極大。
2. 未經授權操作資料庫的數據。
3. 惡意篡改網頁。
4. 網頁掛木馬。
5. 私自添加系統賬號或是資料庫使用者賬號。
# 優化資料庫的方法
1. 選取最適合的欄位屬性，儘可能減少定義欄位寬度，儘量把欄位設成`NOT NULL`。
2. 使用`exists`替代`in`，用`not exists`替代`not in`。
3. 使用連接`（JOIN）`來替代子查詢。
4. 適用聯合`（NUION）`來代替手動創建的臨時表。
5. 事務處理。
6. 鎖定表，優化事務處理。
7. 適當用外鍵，優化鎖定表。
8. 建立索引。
9. 優化查詢語句。
# 資料庫中的事務是什麼
事務作為一個單元的一組有序的數據操作，如果組中的所有操作都完成，則認定事務成功，即使只有一個失敗，事務也不成功。如果所有操作完成，事務則進行提交，其修改將作用於所有其他資料庫進程。如果一個操作失敗，則事務將回滾，該事務所有的操作的影響都會取消。
- `ACID`四大特性
- 原子性：不可分割，事務要麼全部被執行，要麼全部不執行。
- 一致性：事務的執行使得資料庫從一種正確的狀態轉換成另一種正確的狀態。
- 隔離性：在事務正確提交前，不允許把該事務對數據的任何改變提供給任何其他事務。
- 持久性：事務正確提交後，將結果永久保存到資料庫中，即使在事務提交後，有了其他故障，事務處理結果也會得到保存。
# 索引的目的是什麼？
1. 快速訪問數據表中特定信息，提高檢索速度。
2. 創建唯一性索引，保證每一行數據的唯一性。
3. 加速表和表之間的連接。
4. 使用分組和排序子句進行數據檢索時，可顯著的減少分組和排序的時間。
# 索引對資料庫系統的負面影響是什麼？
創建索引和維護索引需要消耗時間，這個時間會隨著數據量的增加而增加，索引需要占用物理空間。當對錶進行增刪改查的時候索引也需要動態維護，這樣就降低了數據的維護速度。
# 為數據表建立索引的原則
1. 頻繁使用的，用以縮小查詢範圍的欄位上建立索引。
2. 頻繁使用的，需要排序的欄位上建立索引。
# 什麼情況下不宜建立索引
對於查詢中涉及很少的列，或是重覆值較多的列，不宜建立索引。
一些特殊的數據類型，不宜就建立索引。如`text`文本欄位。
# 左連接和右連接的區別
左連接：
- 左連接會讀取左表中的全部數據，即使右表中沒有對應的數據（如果倆個表有相同的數據，只會顯示一個），用`NULL`填充。
右連接：
- 右連接會讀取右表的全部數據，即使左表中沒有對應的數據（如果倆個表有相同的數據，只會顯示一個），用`NULL`填充。
# 什麼是鎖？
資料庫是一個多用戶使用的共用資源，當多個用戶併發的存取數據時，在資料庫中就會產生多個事務同時存取同一個數據的情況，若對併發操作不加控制可能就會讀取和儲存不正確的數據，破壞資料庫的一致性。
# 什麼是存儲過程，用什麼來調用？
存儲過程就是一個預編譯的`SQL`語句，優點是允許模塊化設計，只需要創建一次，就可以在該程式中多次調用，如果某次操作需要執行多次`SQL`，使用存儲過程比單純的`SQL`語句要快。可以使用一個命令對象進行調用。
# 索引的作用，和它的優缺點
索引就是一種特殊的查詢表，資料庫引擎可以用它加速對數據的檢索，索引是唯一的，在創建時可以以指定單個列或是多個列。缺點是它減慢了數據錄入的速度，同時也增加了資料庫的尺寸大小。
# 主鍵，外鍵，索引的區別？
主鍵：
- 唯一標識一條記錄，不可重覆，不可為`NULL`。
- 用來保證數據的完整性。
- 只能有一個。
外鍵：
- 表的外鍵是另一個表的主鍵，外鍵可以重覆，可以為空。
- 用來和其他表建立聯繫。
- 一個表可以有多個外鍵。
索引：
- 該欄位沒有重覆值，可以有一個是空值。
- 提高查詢效率排序速度。
- 一個表可以有多個唯一索引。
# 對`SQL`語句的優化方法
1. 避免在索引列上使用計算。
2. 避免在索引列上使用`IS NULL`和`IS NOT NULL`。
3. 對查詢進行優化，儘量避免全表掃描，首先因該考慮在`where`和`order by`涉及的列上建立索引。
4. 避免在`where`子句對欄位進行null值判斷，這件導致引擎放棄使用索引而進行全表掃描。
5. 避免在`where`子句中對欄位進行表達式操作，也會導致引擎放棄使用索引而進行全表掃描。
# `SQL`語句中“相關子查詢”和“非相關子查詢”有什麼區別
如果你想載入一篇你寫過的.md文件，在上方工具欄可以選擇導入功能進行對應擴展名的文件導入，
繼續你的創作。
子查詢：嵌套在其他查詢中的查詢。
非相關子查詢：
- 非相關子查詢是獨立於外部查詢的子查詢，子查詢總共執行一次，執行完畢後將值傳遞給外部的查詢。
相關子查詢：
- 相關子查詢的執行依賴於外部的查詢數據，外部查詢執行一次，子查詢就會執行一次。
【所以非相關子查詢比相關子查詢效率高】
# `char`和`varchar`的區別
- char`類型的數據列里，每個值都占`M`個位元組，如果長度小於`M`，就會在它的右邊用空格字元進行補足（在檢索操作中填補出來的空格符將會被去掉）。
- `vachar`類型的數據列里，每個值只占用剛好夠用的位元組再加上一個用來記錄長度的位元組，所以總長度為`L+1`位元組。
# `SQL`問題
- 臟讀
- 在一個事務處理過程中讀取到了另一個未提交事務中的數據。
【例子】
A在一個轉賬事務中，轉了100給B，此時B讀到了這個轉賬的數據，然後做了一些操作（給A發貨，或是其他），可是這個時候A的事務並沒有提交，如果A回滾了事務，那這就是臟讀。
- 不可重覆讀
- 對資料庫中的某個數據，一個事務範圍內多次查詢卻返回了不同的數據值，是由於在查詢間隔，被另一個事務修改並提交了。
【例子】
事務A在讀取某一數據，而事務B立馬修改了這個數據並且提交了事務到資料庫，事務A再次讀取就得到了不同的結果。發生了不重覆讀。
- 幻讀
- 事務非獨立執行時發生的一種現象。
【例子】
事務A對一個表中所有的行的某個數據項做了從“1”修改為“2”的操作，這時事務B又對這個表中插入了一行數據項，這個數據的數值還是“1”並且提給了資料庫，如果事務A查看剛剛修改的數據，會發現還有一數據沒有修改，而這行數據時事務B中添加的，就像產生的幻覺一樣。發生了幻讀。
# `MySQL`事務隔離級別
1. `read uncmmited`：讀到未提交數據
- 最低級別，無法保證任情況
2. `read commited`：讀已提交
- 可避免臟讀
3. `repeatable read`：可重覆讀
- 可避免臟讀、不可重覆讀
4. `serializable`：串列事務
- 可避免臟讀、不可重覆讀、幻讀
**【`MySQL`預設事務隔離級別為`Repeatable Read`（可重覆讀）】**
# `MySQL`臨時表
什麼是臨時表：臨時表是`MySQL`用於存儲中間結果集的表，臨時表只在當前連接可看，當連接關閉時會自動刪除表並釋放所有空間。
為什麼會產生臨時表：一般是因為複雜的`SQL`導致臨時表被大量創建
- 進行`union`查詢時
- 用到`temptable`演算法或者是`union`查詢中的視圖
- `ORDER BY`和`GROUP BY`的子句不一樣時
- 表連接中，`ORDER BY`的列不是驅動表中的
- `DISTINCT`查詢並且加上`ORDER BY`時
- `SQL`中用到`SLQ_SMALL_RESULT`選項時
- `RROM`中的子查詢
臨時表分為倆種：
- 記憶體臨時表
- 採用的是`memory`存儲引擎
- 磁碟臨時表
- 菜用的是`myisam`存儲引擎
# 什麼是視圖，游標是什麼？
視圖：視圖是一種虛擬表，具有和物理表相同的功能。可以對視圖表進行增刪改查操作，視圖通常是有一個表或者多個表的子集。對視圖的修改不會影響基本表。
- 【使得我們獲取數據更容易，相比多表查詢】
游標：是對查詢出來的結果集作為一個單元來有效的處理。游標可以定在該單元的特定行，從結果集的當前行檢索一行或多行。可以對結果集當前行進行修改。
- 【一般不會使用，但需要逐條處理數據的時候，游標顯得十分重要】
