三範式 三範式定義(範式和反範式) 1NF:每個數據項都是最小單元,不可分割,確定行列之後只能對應一個數據。 2NF:每一個非主屬性完全依賴於候選碼(屬性組的值能唯一的標識一個元組,但是其子集不可以)。 3NF:每一個非主屬性既不傳遞依賴於碼,也不部分依賴於碼(主碼=候選碼為多個市,從中選出一個作 ...
三範式
三範式定義(範式和反範式)
1NF:每個數據項都是最小單元,不可分割,確定行列之後只能對應一個數據。
2NF:每一個非主屬性完全依賴於候選碼(屬性組的值能唯一的標識一個元組,但是其子集不可以)。
3NF:每一個非主屬性既不傳遞依賴於碼,也不部分依賴於碼(主碼=候選碼為多個市,從中選出一個作為主碼)。
BCNF:主屬性(候選碼中的某一個屬性)內部也不能部分或傳遞依賴於碼。
4NF :沒有多值依賴。
數據類型
整數: 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
原子性(Atomicity)一個事務必須被視為一個不可分割的最小工作單元,整個事務中的所有操作要麼全部提交成功,要麼全部失敗回滾,對於一個事務來說,不可能只執行其中的一部分操作。
一致性(Consistency)資料庫總是從一個一致性的狀態轉換到另一個一致性的狀態。
隔離性(Isolation)一個事務所做的修改在最終提交以前,對其他事務是不可見的。
持久性(Durability)一旦事務提交,則其所做的修改不會永久保存到資料庫。
4 種隔離級別
READ UNCOMMITTED(未提交讀)臟讀:事務中的修改,即使沒有提交,對其他事務也都是可見的。
READ COMMITTED(提交讀)不可重覆讀:事務從開始直到提交之前,所做的任何修改對其他事務都是不可見的。
REPEATABLE READ(可重覆讀):幻讀:一個事務按相同的查詢條件讀取以前檢索過的數據,其他事務插入了滿足其查詢條件的新數據。產生幻行。
SERIALIZABLE(可串列化) 強制事務串列執行
MVVC是個行級鎖的變種,它在普通讀情況下避免了加鎖操作,自特定情況下加鎖。
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索引 索引列的順序影響者是否使用索引。
哈希索引
無法用於排序。
只支持全部匹配。
只支持等值比較。
有很多哈希衝突時,效率不太高。
空間數據索引(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(全表掃描)。
視圖
視圖最簡單的實現方法是把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(數據定義語言)語句和 DML(數據操縱語言) 語句,不包括數據查詢語句。語句以“事件”的形式保存,它描述了數據的更改過程。(定期刪除日誌,預設關閉)。
查詢日誌:記錄了客戶端的所有語句,格式為純文本格式,可以直接進行讀取。(log 日誌中記錄了所有資料庫的操作,對於訪問頻繁的系統,此日誌對系統性能的影響較大,建議關閉,預設關閉)。
慢查詢日誌:慢查詢日誌記錄了包含所有執行時間超過參數long_query_time(單位:秒)所設置值的 SQL 語句的日誌。(純文本格式)MySQL日誌文件之錯誤日誌和慢查詢日誌詳解。
日誌文件小結:
系統故障時,建議首先查看錯誤日誌,以幫助用戶迅速定位故障原因。
記錄數據的變更、數據的備份、數據的複製等操作時,打開二進位日誌。預設不記錄此日誌,建議通過--log-bin 選項將此日誌打開。
如果希望記錄資料庫發生的任何操作,包括 SELECT,則需要用--log 將查詢日誌打開, 此日誌預設關閉,一般情況下建議不要打開此日誌,以免影響系統整體性能。
查看系統的性能問題, 希望找到有性能問題的SQL語 句,需要 用 --log-slow-queries 打開慢查詢日誌。對於大量的慢查詢日誌,建議使用 mysqldumpslow 工具 來進行彙總查看。