mysql之資料庫特性認識

標籤：

最近面試經常被面試官問道關於資料庫方面的知識，於是總結一下面試官問的題已經逐漸對資料庫的認識

1、之前百度面試官問了我一個特別基礎的sql問題：如何清除表的所有記錄，以前在學校做項目開發的時候有使用過，但是面試的時候一下沒有想到，下來才想到該怎麼寫sql，我都不得不吐槽一下自己

　　這個問題的sql有兩種寫法：1、truncate table tablename        truncate table命令將快速刪除資料表中的所有記錄，但保留資料表結構，資料不可恢複（自增主鍵從頭開始）

　　　　　　　　　　　　　　 2、delete from tablename where 1=1 獲得delete from tablename 該刪除操作記錄在系統復原段中，資料可恢複

 

2、資料庫的索引：主要是InnoDB和MYISAM索引，他們的區別主要體現在檔案結構，鎖，以及操作方面

　　1、檔案結構：InnoDB是索引檔案和資料檔案分離，MYIASAM所以和資料檔案在同一個檔案中

　　2、鎖：InnoDB採用的是行鎖，實現了分段鎖，就行並發寫的時候，每個事務只對相應的行進行加鎖，MYISAM採用的是表鎖，因此會並發寫的時候就會有瓶頸

　　3、操作方面：InnoDB支援事務，MUISAM不支援事務，但是count(*)使用MyISAM要比InnoDB快得多。因為MyISAM內建了一個計數器

 

3、事務的特性：

　　1、原子性：事務要麼成功，要麼失敗，mysql實現事務的原子性和undo.log日誌有關係

　　2、一致性:事務執行的結果必須是使資料庫從一個一致性狀態變到另一個一致性狀態；比如銀行轉賬的問題；兩個帳號version0(A=100,B=0);A給B轉賬100會經曆如下步驟：1、讀取A的金額，查看是否有100元，

　　　　如果有就對A-100，此時就會有一個中間態，定義為version1（A=0,B=0），2、B+100，資料庫狀態變成了version2（A=0,B=100）；資料庫的一致性要求使用者看到的資料要麼在version0的狀態，要麼在version2的狀態，不能讓使用者看到version1這個中間態，資料庫的一致性與原子性是密切相關的

　　3、隔離性：我個人的理解：隔離性是在權衡資料一致性以及效能所做出的一個折衷方案，主要有四種隔離等級：(下面我通過A、B兩個事務來進行講解)

　　　　1、Serializable：該隔離等級是使用了悲觀鎖---排它鎖，通過讀寫鎖分離實現強一種性，雖然資料實現了強一致性，但是並行度不高

　　　　2、讀未提交：假設有A、B兩個事務，A事務修改了資料data，在B事務中就能夠馬上看到資料的修改，這感覺想JAVA裡面的volatile變數一樣

　　　　3、讀已提交：假設有A、B兩個事務，A事務修改了資料data，並且提交了該事務，B事務就能夠讀到修改的資料,這是oracle的預設隔離等級。該層級存在不可重複讀取的問題（MYSQL預設隔離等級）

　　　　4、可重複的：假設有A、B兩個事務，A事務修改了資料data，並且提交了該事務，B事務看不到剛才修改的資料，在該事務中多次執行select讀出的資料是一致的

　　　　5、上面的的隔離等級是SQL92中定義的，MVCC可以看作是對該標準的一種擴充；MVCC是一種樂觀鎖，讓資料多版本化，同時為每個事務一個事務ID來實現資料的一致性，這是一種通過空間換時間的策略

 

4、索引：關於索引的知識主要有：索引的資料結構，索引的使用以及最佳化

　　1、不管是InnoDB還是MYISAM索引引擎都是採用的B-Tree的變形---B+Tree：B-Tree和B+Tree的區別：B-Tree的節點上儲存了資料，而B+Tree的只有在葉子節點上才儲存資料。在Mysql中，定義每個節點是頁（頁是電腦管理儲存空間的邏輯塊）的整數倍，這樣就可以使用作業系統的局部預讀的特性，減少磁碟的I/O次數。

　　2、建立索引的時候需要考慮到如下幾方面的問題：

　　　　1、我們寫的sql的where條件中是否會經常用到該列，如果經常用到才考慮建索引（只是考慮，還要主要下面的問題）

　　　　2、最左首碼原理：mysql會一直向右匹配直到遇到範圍查詢(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)順序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引則都可以用到，a,b,d的順序可以任意調整。=和in可以亂序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意順序，mysql的查詢最佳化工具會幫你最佳化成索引可以識別的形式

　　　　3、索引選擇性與首碼索引：索引檔案本身要消耗儲存空間，同時索引會加重插入、刪除和修改記錄時的負擔，另外，MySQL在運行時也要消耗資源維護索引，因此索引並不是越多越好；對某列建立索引要考慮到該列資料的選擇性，同時有一種與索引選擇性有關的索引最佳化策略叫做首碼索引，就是用列的首碼代替整個列作為索引key，當前置長度合適時，可以做到既使得首碼索引的選擇性接近全列索引，同時因為索引key變短而減少了索引檔案的大小和維護開銷

　　　　4、Explain的使用及最佳化（rows是核心指標）

　　　　　　1、注意Extra列：如果是useing filesort 或者
　　　　　　2、看有沒有使用到預期的索引，掃描的rows是不是很多，如果是很多，說明索引不好，我們可能要修改索引

5、簡單的sql最佳化

　　1、選擇正確的儲存引擎

 

　　2、建立索引，索引的建立參考上面

　　3、避免使用select*，只查我們需要的資料，減少網路資料的傳輸

　　4、為每張表設計一個自增ID

　　5、盡量使用NOT NULL，NULL需要額外的空間，並且，在你進行比較的時候，你的程式會更複雜

　　6、拆分大的DELETE和INSERT/避免大事務

　　7、每個欄位盡量小,對於大多數的資料庫引擎來說，硬碟操作可能是最重大的瓶頸。所以，把你的資料變得緊湊可以減少磁碟訪問

　　8、分庫分表（分散式資料庫中介軟體）

mysql之資料庫特性認識