MySQL面試總結

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`存盤引擎

# 什么是視圖，游標是什么？

視圖：視圖是一種虛擬表，具有和物理表相同的功能，可以對視圖表進行增刪改查操作，視圖通常是有一個表或者多個表的子集，對視圖的修改不會影響基本表，

- 【使得我們獲取資料更容易，相比多表查詢】

游標：是對查詢出來的結果集作為一個單元來有效的處理，游標可以定在該單元的特定行，從結果集的當前行檢索一行或多行，可以對結果集當前行進行修改，

- 【一般不會使用，但需要逐條處理資料的時候，游標顯得十分重要】

標籤：MySQL

上一篇：Mysql基礎02-約束

下一篇：MySql安裝配置