1 mysql架構(gòu)和歷史

1.3 事務(wù) ACID

• 原子性：一個事務(wù)被視為不可分割的最小工作單元
• 一致性：數(shù)據(jù)庫總是從一個一致性的狀態(tài)轉(zhuǎn)為另一個一致性的狀態(tài)
• 隔離性：事務(wù)之間的隔離，后面會寫到隔離級別
• 持久性：一旦事務(wù)提交，所做的修改會永久保存到數(shù)據(jù)庫中

1.3.1 隔離級別

• READ UNCOMMITTED
  事務(wù)中的修改，即使沒有提交，對其他事務(wù)也都是可見的。事務(wù)可以讀取未提交的數(shù)據(jù)，被稱為臟讀。（寫的時候別的事務(wù)可以讀）
• READ COMMITED
  一個事務(wù)開始時，只能看見已經(jīng)提交事務(wù)所做的修改。也叫不可重復(fù)讀。（寫的時候別人不能讀了，但是讀的時候別的事務(wù)可以寫，在多次讀的時候，其他事務(wù)修改了數(shù)據(jù)會導(dǎo)致2次讀的結(jié)果不一致）
• REPEATABLE READ
  解決了上面問題，但可能會出現(xiàn)幻讀（指的是某個事務(wù)在讀取某個范圍內(nèi)的記錄時，另外一個事務(wù)又在該范圍內(nèi)插入了新的記錄，之前事務(wù)再讀取該記錄范圍內(nèi)時，會出現(xiàn)幻行。InnoDB通過多版本并發(fā)控制mvcc解決了幻讀）
• SERIALIZABLE
  強制事務(wù)串行執(zhí)行。他會給讀取的每一行都加上鎖，會導(dǎo)致大量超時和鎖爭用問題。

1.3.2 死鎖

死鎖是指2個或以上事務(wù)在同一資源相互占用，并請求鎖定對方占用的資源，從而導(dǎo)致惡性循環(huán)。

#事務(wù)1
START TRANSACTION;
UPDATE Stockprice SET close=45.50 WHERE stock_id=4 and date='2002-0501';
UPDATE Stockprice SET close=19.80 WHERE stock_id=3 and date='2002-0502';
COMMIT;
#事務(wù)2
START TRANSACTION;
UPDATE Stockprice SET high=15.50 WHERE stock_id=3 and date='2002-0502';
UPDATE Stockprice SET high=47.50 WHERE stock_id=4 and date='2002-0501';
COMMIT;

碰巧2個事務(wù)都執(zhí)行了第一條update語句，鎖定了行數(shù)據(jù)，2個事務(wù)都嘗試執(zhí)行第二條update語句，卻發(fā)現(xiàn)該行都被對方鎖定了，陷入死循環(huán)。InnoDB目前處理方式是將持有最少行級排他鎖的事務(wù)進行回滾。

1.4 多版本并發(fā)控制 MVCC

InnoDB是通過在每行記錄的后面保存2個隱藏的列來實現(xiàn)。這2個列一個保存了行的創(chuàng)建時間，一個保存行的過期時間（刪除時間）。存儲的不是真的時間還是系統(tǒng)版本號。每開始一個新的事務(wù)，系統(tǒng)版本號就會自動遞增。下面詳解在REPEATABLE READ隔離級別下 MVCC的具體操作

• SELECT
  InnoDB會根據(jù)下面2個條件檢查每行記錄
  • InnoDB只查找早于當(dāng)前事務(wù)版本的數(shù)據(jù)行（行的系統(tǒng)版本號小于或等于事務(wù)的系統(tǒng)版本號），這樣可以確保事務(wù)讀取的行，要么是在事務(wù)開始前就存在的，要么是事務(wù)自身插入或修改過的
  • 行的刪除版本要么未定義，要么大于當(dāng)前事務(wù)版本號?？梢源_保事務(wù)讀取到的行在事務(wù)開始前還沒有被刪除。
• INSERT
  InnoDB為插入的每一行保存當(dāng)前系統(tǒng)版本號作為行版本號
• DELETE
  InnoDB為刪除的每一行保存當(dāng)前系統(tǒng)版本號作為刪除標(biāo)識
• UPDATE
  InnoDB為插入一行新紀(jì)錄，保存當(dāng)前系統(tǒng)版本號作為行版本號，同時保存當(dāng)前系統(tǒng)版本號到原來的行作為行刪除標(biāo)識
  MVCC只能在REPEATABLE READ和READ COMMITED下工作。缺點是每行記錄需要額外的存儲空間，需要做更多的行檢查，以及額外一些維護工作。

1.5 存儲引擎

1.5.1 InnoDB

InnoDB是mysql默認(rèn)事務(wù)型引擎。
InnoDB數(shù)據(jù)存儲在表空間中。mysql4.1之后InnoDB將每個表的數(shù)據(jù)和索引放在單獨的文件中（意思也可以放在一個文件）
InnoDB用MVCC支持高并發(fā)，并實現(xiàn)了四個標(biāo)準(zhǔn)的隔離級別，并通過MVCC和間隙鎖(next-key locking)策略解決幻讀。
InnoDB表基于聚簇索引，后面章節(jié)會詳解。

1.5.2 MyISAM

MyISAM不支持事務(wù)和行級鎖（用的是表鎖），崩潰后也無法恢復(fù)。

存儲

MyISAM將表存儲在2個文件中：數(shù)據(jù)文件和索引文件，分別以.MYD和.MYI為擴展名
MyISAM的索引都是保存行的地址

5 創(chuàng)建高性能索引

B-Tree索引的抽象表示

當(dāng)一個索引包含多個列時key(last_name, first_name)
索引只對下面類型查詢有效：

• 全值匹配
  全值匹配指的是和索引中的所有列進行匹配。比如查找last_name是ZHUANG,first_name是ALAN的人
• 匹配最左前綴
  只使用索引的第一列，查找last_name是ZHUANG的人
• 匹配列前綴
  匹配某一列的值的開頭部分，查找last_name是ZH開頭的人，要使用索引的第一列，不能匹配first_name是ZH開頭的人
• 匹配范圍值
  第一列范圍，查找last_name從ZHANG到ZHUANG的人。
• 精確匹配第一列并范圍匹配另一列
  查找last_name是ZHUANG，first_name是A開頭的人。第一列必須全匹配第二列才可以范圍匹配
• 只訪問索引無需訪問數(shù)據(jù)行

哈希索引

基于哈希表實現(xiàn)。對于每一行數(shù)據(jù)，存儲引擎會對所有的索引列計算一個哈希碼。哈希索引將所有的哈希碼存儲在引擎中，同時在哈希表中保存指向每個數(shù)據(jù)行的指針

哈希索引查找特別快，因為它自身只需要存儲對應(yīng)的哈希值，所以索引的結(jié)構(gòu)十分緊湊。不過他也有以下限制：

• 哈希索引只包含索引和行指針，而不存儲字段值。所以不能使用索引的值來避免讀取行
• 哈希索引數(shù)據(jù)并不是按照索引值順序排序的 所以不能用于排序
• 哈希索引不支持索引列部分匹配，必須索引列全部內(nèi)容來計算哈希碼。
• 只支持等值查詢 不支持范圍查詢
• 訪問哈希索引速度非?？?除非很多哈希沖突
• 哈希沖突很多的時候，索引維護代價也很高

高性能的索引策略

5.3.1 獨立的列

select actor_id FROM sakila.actor where actor_id+1= 5
這句查詢語句會導(dǎo)致索引失效。我們應(yīng)養(yǎng)成簡化WHERE條件的習(xí)慣，始終將索引單獨放在比較符號的一側(cè)

5.3.2 前綴索引和索引選擇性

有時候需要索引很長的字符列，這會讓索引變得大且慢。一個策略是前面提到過的模擬哈希索引。但有時候這樣還不夠，還可以做些什么呢？
通?？梢运饕_始的部分字符，這樣可以大大節(jié)約索引空間，從而提高索引效率。
SELECT COUNT(*) AS cnt, LEFT(city, 3) AS pref FROM sakila.city_demo GROUP BY pref ORDER BY cnt Desc LIMIT 10;

5.3.5 聚簇索引

當(dāng)表有聚簇索引時，他的數(shù)據(jù)實際上存放在索引的葉子頁中。聚簇表示數(shù)據(jù)行和相鄰的鍵值緊湊的存儲在一起。因為無法把數(shù)據(jù)存放在兩個不同的地方，所以一個表只能有一個聚簇索引。

聚簇索引數(shù)據(jù)分布

InnoDB通過主鍵聚集數(shù)據(jù)，這也就是說圖中被索引的列是主鍵列。如果沒有定義主鍵，InnoDB會選擇一個唯一的非空索引代替。如果沒有這樣的索引，InnoDB會隱式定義一個主鍵來作為聚簇索引，InnoDB只能聚集在用一個頁面中的記錄。包含相鄰鍵值的頁面可能會相距甚遠(yuǎn)。
聚簇索引會加快數(shù)據(jù)訪問。不過也會帶來問題：會導(dǎo)致二級索引比想象的大，因為在二級索引的葉子節(jié)點包含了引用行的主鍵列。二級索引訪問需要兩次索引查找。
二級索引中葉子結(jié)點保存的不是指向行的物理位置的指針而是行的主鍵值。這意味著通過二級索引查找行會先獲得對應(yīng)的主鍵值，然后再去聚簇索引找到對應(yīng)的行。進行了2次B-tree查找。自適應(yīng)哈希能夠減少這樣的重復(fù)工作（InnoDB會自動生成。）

InnoDB和MyISAM的數(shù)據(jù)分布對比

測試表數(shù)據(jù).png

MyiISAM

MyISAM的主鍵索引和非主鍵索引長得一模一樣。（最下面最黑的其實是數(shù)據(jù)的行號）

InnoDB

在InnoDB中，聚簇就是表，所以不像MyISAM那樣需要獨立的行存儲。
聚簇索引葉子結(jié)點包含了主鍵值，事務(wù)id，用于事務(wù)和MVCC的回滾指針以及剩余列。
InnoDB二級索引葉子結(jié)點存儲的是key（查找的二級索引的具體值）+主鍵值

二級索引.png

5.3.6 覆蓋索引

如果一個索引包含或者是覆蓋所有需要查詢的字段的值，我們就稱之為“覆蓋索引”（比如有一個表有二級索引key(name,age),且查詢語句為SELECT name,age FROM t1;那么二級索引中葉子節(jié)點就包含了查詢所需要的數(shù)據(jù)，就不需要拿著主鍵值再去聚簇索引中查找行數(shù)據(jù)了）

7 MySQL高級特性

7.2 視圖

視圖本身是一個虛擬表，不存放任何數(shù)據(jù)。在使用SQL語句訪問視圖的時候，它返回的數(shù)據(jù)是從其他表生成的。
實現(xiàn)視圖最簡單的方法就是將select語句的結(jié)果存放到臨時表中。當(dāng)需要訪問視圖的時候，直接訪問這個臨時表就可以了。

CREATE VIEW Oceania AS SELECT * FROM Country WHERE Continent = 'Oceania' WITH CHECK OPTION;

SELECT Code FROM Oceania WHERE Name = 'Australia';

實現(xiàn)視圖更好的方式是重寫含有視圖的查詢，將視圖定義的SQL直接包含進查詢的SQL中。下面展現(xiàn)的是將視圖定義的sql合并進查詢sql后的樣子：
SELECT Code From Country WHERE Continent = 'Oceania' AND Name ='Australia';
Mysql可以使用這兩種方法中的任何一種來處理視圖。這兩種算法分別叫做臨時表算法（TEMPTABLE）和合并算法（MERGE），最好使用合并算法。

視圖的兩種實現(xiàn)

視圖主要作用

• 簡化復(fù)雜查詢
  可能要經(jīng)常使用具有多個連接的復(fù)雜的SQL語句，創(chuàng)建視圖以后只要在視圖上進行簡單查詢就可以
• 提高安全性
  可以將你想展現(xiàn)的數(shù)據(jù)通過視圖給用戶看，同時隱藏了原來表里面的敏感數(shù)據(jù)
• 向后兼容
  假設(shè)原來有一個表要被拆分成2個不同的表，那么之前對這個表的操作可能會失敗，現(xiàn)在只需要創(chuàng)建和原來表名字相同的視圖就行了

7.2.3 視圖限制

mysql視圖不支持物化視圖（物化視圖將視圖結(jié)果數(shù)據(jù)存放在一個可以查看的表中，并定期從原始表刷新數(shù)據(jù)到這個表）。
也不支持視圖中創(chuàng)建索引