Mysql面試複習總結

標籤：失敗   兩個指標   組成   最簡   5.0   row   預設   結果   效能最佳化   

三範式

三範式定義（範式和反範式）

1NF：每個資料項目都是最小單元，不可分割，確定行列之後只能對應一個資料。

2NF：每一個非主屬性完全依賴於候選碼（屬性群組的值能唯一的標識一個元組，但是其子集不可以）。?

3NF：每一個非主屬性既不傳遞依賴於碼，也不部分依賴於碼（主碼=候選碼為多個市，從中選出一個作為主碼）。

BCNF：主屬性（候選碼中的某一個屬性）內部也不能部分或傳遞依賴於碼。

4NF ：沒有多值依賴。

資料類型

MySQL資料類型-菜鳥教程 

MYSQL中資料類型介紹

整數： int(m)裡的m是表示資料顯示寬度，浮點數，定點數。

字串：char（n）4.0 n 代表位元組，5.0 n 代表字元 （UTF-8=3zj，GBK=2zj）

 char 固定的字元數，空格補上；檢索速度快。

 varchar 字元數+1個位元組（n<=255）或2個位元組(n>255)

 text 字元數+2個位元組；不能有預設值；索引要指定前多少個字元；文本方式儲存

 blob 二進位方式儲存

儲存引擎

各種儲存引擎的區別與聯絡     （儲存資料技術和策略，儲存機制、索引技巧、鎖定水平等）

資料庫儲存引擎     show table status 顯示表的相關資訊

InnoDB與MyISAM的比較（從5.7開始innodb儲存引擎成為預設的儲存引擎。）

 鎖機制：行級鎖，表級鎖

 事務操作：事務安全，不支援

InnoDB (1)可靠性要求比較高，要求事務；(2)表更新和查詢都相當的頻繁，並且行鎖定的機會比較大的情況。

 MySQL4.1之後每個表的資料和索引儲存在一個檔案裡。

 InnoDB 採用了MVCC來支援高並發，並且實現了四個標準的隔離等級。其預設層級是REPEATABLE READ(可重複讀) ，行級鎖。

 自動災難恢複。與其它儲存引擎不同，InnoDB表能夠自動從災難中恢複。

 外鍵約束。MySQL支援外鍵的儲存引擎只有InnoDB。

 支援自動增加列AUTO_INCREMENT屬性。

MyIsam  (1)做很多count 的計算；(2)插入不頻繁，查詢非常頻繁；(3)沒有事務。

 表格儲存體在兩個檔案中，資料檔案（MYD）和索引檔案（MYI）

 表級鎖，讀=共用鎖定，寫=排它鎖。

 適合選擇密集型的表，插入密集型的表。

資料庫ACID

資料庫的ACID

資料庫事務介紹

原子性（Atomicity）一個事務必須被視為一個不可分割的最小工作單元，整個事務中的所有操作要麼全部提交成功，要麼全部失敗復原，對於一個事務來說，不可能只執行其中的一部分操作。

一致性（Consistency）資料庫總是從一個一致性的狀態轉換到另一個一致性的狀態。

隔離性（Isolation）一個事務所做的修改在最終提交以前，對其他事務是不可見的。

持久性（Durability）一旦事務提交，則其所做的修改不會永久儲存到資料庫。

4 種隔離等級

MVVC的簡單介紹

READ UNCOMMITTED（未提交讀）髒讀：事務中的修改，即使沒有提交，對其他事務也都是可見的。

READ COMMITTED（提交讀）不可重複讀取：事務從開始直到提交之前，所做的任何修改對其他事務都是不可見的。

REPEATABLE READ(可重複讀)：幻讀：一個事務按相同的查詢條件讀取以前檢索過的資料，其他事務插入了滿足其查詢條件的新資料。產生幻行。

SERIALIZABLE（可序列化） 強制事務串列執行

MVVC是個行級鎖的變種，它在普通讀情況下避免了加鎖操作，自特定情況下加鎖。

Mysql死結問題

Mysql悲觀鎖總結和實踐

Mysql樂觀鎖總結和實踐

SELECT ... LOCK IN SHARE MODE SELECT ... FOR UPDATE：（LOCK IN SHARE MODE 在有一方事務要Update 同一個表單時很容易造成死結）

樂觀鎖：取鎖失敗，產生回溯時影響效率。

 取資料時認為其他線程不會對資料進行修改。

 更新時判斷是否對資料進行修改，版本號碼機制或CAS操作。

悲觀鎖：每次取資料都會加鎖。

innodb_lock_wait_timeout 等待鎖逾時復原事務：  【逾時法】

直觀方法是在兩個事務相互等待時，當一個等待時間超過設定的某一閥值時，對其中一個事務進行復原，另一個事務就能繼續執行。在innodb中，參數innodb_lock_wait_timeout用來設定逾時時間。

wait-for graph演算法來主動進行死結檢測：  【等待圖法】

innodb還提供了wait-for graph演算法來主動進行死結檢測，每當加鎖請求無法立即滿足需要並進入等待時，wait-for graph演算法都會被觸發。

索引

索引（儲存引擎 快速找到記錄的一種資料結構，索引的準系統）

什麼是B-Tree

MySQL索引背後的資料結構及演算法原理

MySQL效能最佳化-慢查詢分析、最佳化索引和配置

索引類型：

 B-Tree索引 索引列的順序影響者是否使用索引。

 雜湊索引

 無法用於排序。

 只支援全部匹配。

 只支援等值比較。

 有很多雜湊衝突時，效率不太高。

 空間資料索引（R-Tree）無需首碼查詢，從所有維度查詢資料。

 全文檢索索引 尋找文本中的關鍵詞，類似於搜尋引擎做的事情。

 具體類型介紹：

單列索引：不允許為空白

 普通索引 不允許有空值

 唯一索引

 主鍵索引 在 InnoDB 引擎中很重要

組合引擎：多個欄位上建立的索引，複合索引時遵循最左首碼原則。

 查詢中某個列有範圍查詢，則其右邊的所有列都無法使用查詢

全文索引：

空間索引：

參考：細說mysql索引、我的MYSQL學習心得（九） 索引

MySQL索引詳解 （ 一般使用磁碟I/O次數評價索引結構的優劣。 ）

 磁碟存取原理

 局部性原理與磁碟預讀

M 階 B-Tree

 

 根節點至少有2個子樹。

 每個非葉子節點由n-1個key和n個指標組成。

 分支節點至少擁有m/2顆子樹，最多擁有m個子樹。（除根節點和葉子結點外）

 所有分葉節點具有相同的深度，等於樹高 h。

 每個葉子節點最少包含一個key和兩個指標，最多包含2d-1個key和2d個指標。

B+ Tree

 內節點不儲存data，只儲存key。

 葉子節點不儲存指標。

MySQL 索引實現

 MyISAM 索引檔案和資料檔案是分離,非叢集索引。

 InnoDB 分葉節點包含了完整的資料記錄，叢集索引。根據主鍵聚集。

EXPLAIN 欄位介紹

 possible_keys：顯示可能應用在這張表中的索引。

 key：實際使用的索引。

 key_len：使用的索引的長度，越短越好。

 ref：顯示索引的哪一列被使用了。

 rows：MySQL認為必須檢索的用來返回請求資料的行數。

 type：使用了何種類型。從最好到最差的連線類型為system、const（常量）、eq_ref、ref、range、index（索引全表掃描）和ALL（全表掃描）。

視圖  

MySQL資料庫檢視

MySQL - 視圖演算法

視圖最簡單的實現方法是把select語句的結果存放到暫存資料表中。具有效能問題，最佳化器很難最佳化暫存資料表上的查詢。

 合并演算法 ：select語句與外部查詢檢視的select語句進行合并，然後執行。

 暫存資料表演算法 ：先執行視圖的select語句，後執行外部查詢的語句。

視圖在某些情況下可以提升效能，並和其他提升效能的方式疊加使用。

 視圖不可以跨表進行修改資料，

 建立有條件限制的視圖時，加上“WITH CHECK OPTION”命令。

觸發器

 觸發器的觸發事件 ， 可以是 INSERT 、UPDATE 或者 DELETE 。

 觸發時間 ， 可以是 BEFORE 或者 AFTER。

 同一個表相同觸發時間的相同觸發事件 ， 只能定義一個觸發器，只支援基於行觸發。

 觸發器的原子性，InnoDB支援事務，MyISAM不支援。

事件

     類似於Linux的定時任務，某個時間或者每隔一段時間執行一段SQL代碼。

備份

資料備份（深入淺出Mysql 27章 備份與恢複）

 全備份與增量備份的比較。

 確保 MySQL 開啟 log-bin 選項，有了 BINLOG，MySQL 才可以在必要的時候做完 整恢複，或基於時間點的恢複，或基於位置的恢複。

邏輯備份（將資料庫中的資料備份為一個文字檔，備份的檔案可以被查 看和編輯。）

物理備份

 冷備份：cp移動資料檔案的方法。

 恢複：移動資料檔案，使用 mysqlbinlog 工具恢複自備份以來的所有 BINLOG。

 熱備份：（將要備份的表加讀鎖，然後再 cp 資料檔案到備份目錄。）

 MyISAM：mysqlhotcopy工具。

 ibbackup 是 Innobase 公司（www.innodb.com）的一個熱備份工具。

恢複

     完全恢複

 將備份作為輸入執行。

 將備份後執行的日誌進行重做。

     不完全恢複（跳過誤動作陳述式，再恢複後 面執行的語句，完成我們的恢複。）

 基於時間點的操作。跳過故障發生時間。

 基於位置的恢複。找到出錯語句的位置號，並跳過位置區間。

 

日誌

錯誤記錄檔：記錄了當 mysqld 啟動和停止時，以及伺服器在 運行過程中發生任何嚴重錯誤時的相關資訊。

二進位檔案：記錄了所有的 DDL（資料定義語言 (Data Definition Language)）語句和 DML（資料操縱語言） 語句，不包括資料查詢語句。語句以“事件”的形式儲存，它描述了資料的更改過程。（定期刪除日誌，預設關閉）。

查詢日誌：記錄了用戶端的所有語句，格式為純文字格式，可以直接進行讀取。（log 日誌中記錄了所有資料庫的操作，對於訪問頻繁的系統，此日誌對系統效能的影響較大，建議關閉，預設關閉）。

慢查詢日誌：慢查詢日誌記錄了包含所有執行時間超過參數long_query_time（單位：秒）所設定值的 SQL 陳述式的日誌。（純文字格式）MySQL記錄檔之錯誤記錄檔和慢查詢日誌詳解。

記錄檔小結：

 系統故障時，建議首先查看錯誤記錄檔，以協助使用者迅速定位故障原因。

 記錄資料的變更、資料的備份、資料的複製等操作時，開啟二進位日誌。預設不記錄此日誌，建議通過--log-bin 選項將此日誌開啟。

 如果希望記錄資料庫發生的任何操作，包括 SELECT，則需要用--log 將查詢日誌開啟， 此日誌預設關閉，一般情況下建議不要開啟此日誌，以免影響系統整體效能。

 查看系統的效能問題， 希望找到有效能問題的SQL語 句，需要 用 --log-slow-queries 開啟慢查詢日誌。對於大量的慢查詢日誌，建議使用 mysqldumpslow 工具 來進行匯總查看。

Mysql面試複習總結