學習SQL知識優化篇之範式設計及索引優化

首先在設計資料庫之前，就應該選好工具，在工具完備的情況下， 再進行邏輯查詢優化以及實體查詢優化，必要的情況下，還需要找外援（緩存資料庫）提高資料庫效率。

範式設計

資料表的範式有哪些？

一共有五大範式，分别為1NF，2NF，3NF，DCNF，5NF。

就是第一範式、第二範式、第三範式、巴斯科德範式、第五範式（又稱完美範式）

範式的等級越高，資料表的備援就越少。并且高範式會包含低範式的所有内容。

1NF：每個DBMS都支援的範式，保證資料表屬性的原子性，即不可分割的字段。

2NF：目前表的非主屬性都要與表中的候選鍵，有完全依賴關系。即，表中的非主屬性，不允許依賴于兩個候選鍵。

例如：球員表中有球員編号，姓名，年齡，比賽編号，比賽地點，得分

這張表就有兩個依賴關系，不滿足2NF範式要求：

球員編号（姓名、年齡）

比賽編号（地點、得分）

應該将改變分為兩張表↓

球員表（球員編号、球員姓名、球員年齡、比賽編号）

比賽表（比賽編号、比賽地點、得分）

1.減少資料備援（若一個球員要參加M場比賽，那麼球員的姓名與年齡字段就備援了M-1條資料。若一場比賽有N個球員要參加，那麼比賽場地與比賽得分就備援了N-1條資料。）

2.避免插入異常（若插入表時，還沒有比賽要進行，就會不允許插入）

3.避免删除異常（若比賽期間有個人員不參加比賽，若沒有儲存比賽表就會将比賽資訊也删除。）

4.避免更新異常（若比賽的時間發生了改變，那麼所有參加這場比賽的所有資料都需要進行修改，否則就會出現比賽資訊不一緻。）

3NF：不允許表中有非主屬性傳遞依賴于候選鍵。即：有一張表中，有球員編号、球員名稱、球隊名稱、球隊教練，這其中球隊名稱依賴于球員球員編号，而球隊教練又依賴于球隊名稱。那麼球隊教練與球員編号就存在傳遞依賴。

這就需要将表拆成以下的表↓

球員表（球員編号、球員名稱、球隊編号）

球隊表（球隊編号、球隊教練）

那麼第三範式還有哪些問題呢？

若有一張倉庫管理表（倉庫編号、倉庫名、倉庫管理者、産品名稱、産品數量）

這張表中沒有字段能拆分，保證字段原子性，滿足第一範式（1NF）。非主屬性（倉庫名、倉庫管理者、産品名、産品數量）都完全依賴于候選鍵（倉庫編号），滿足第二範式（2NF）。這張表中，非主屬性與候選鍵之間沒有傳遞依賴關系，滿足第三範式（3NF）。

但是還是存在了問題。

1.若插入一張倉庫表時，還沒有産品進行入庫，那麼插入就會産生異常。

2.若倉庫管理者需要修改，那麼就需要修改N條資料。

3.若倉庫中的所有商品都賣出去了，那麼就會将倉庫資訊都删除。

要解決這些問題就需要使用巴斯-科德範式（BCNF），滿足3NF的基礎上，消除了候選鍵對非主屬性的傳遞依賴或部分依賴。

需要将倉庫管理表拆分為：

倉庫表（倉庫編号、倉庫名稱、倉庫管理者）

商品表（倉庫編碼、商品名稱、商品數量）

反範式

因為範式設計會降低資料表中的備援字段，那麼可能在某些情況下，會使得查詢的效率變低，因為若要查詢一張報表，需要關聯N張表的資料就肯定會導緻查詢速度變慢。

比如：若需要查詢商品評論表的資料，那就需要關聯商品表，擷取商品名稱，需要關聯使用者表，擷取使用者名稱。資料量小的時候影響不大，那麼資料量達到百萬級别的時候呢？

這邊使用存儲過程批量新增百萬個商品、百萬個使用者、百萬條商品評論。

CREATE DEFINER=`root`@`localhost` PROCEDURE `batchInsert`(in start int,in max_sum int)
BEGIN
	DECLARE  p_id VARCHAR(36);
	DECLARE  pc_id VARCHAR(36);
	DECLARE  p_comment VARCHAR(255);
	DECLARE  user_id VARCHAR(36);
	DECLARE  user_name VARCHAR(36);
	DECLARE  p_name VARCHAR(12);
	DECLARE  p_standard VARCHAR(36);
	DECLARE  p_time DATETIME;
	DECLARE  age VARCHAR(36);
	DECLARE  i INT DEFAULT 1;
	SET autocommit = 0;
	REPEAT
	   SET i = i + 1;
		 SET user_name = CONCAT("USN",i);
		 SET p_comment = SUBSTR(MD5(RAND()),1,20);
		 SET p_name = CONCAT("pname",i);
		 SET p_standard = CONCAT("S",i);
		 SET user_id = CONCAT("u",i);
		 SET pc_id = CONCAT("pc",i);
		 SET p_id = CONCAT("p",i);
		 SET age = RAND(20);
		 SET p_time = CURRENT_TIMESTAMP();
		 
		 
		 INSERT INTO users(id,user_name,age,sex) values(user_id,user_name,age,1);
		 INSERT INTO product(id,product_name,product_standard) values(p_id,p_name,p_standard);
		 INSERT INTO product_comment(id,p_comment,p_user,p_time,p_product) values(pc_id,p_comment,user_id,p_time,p_id);
		 
		 
	UNTIL i = max_sum END REPEAT;
	COMMIT;
END
           

使用關聯查詢的語句

SELECT
	p.product_name,
	u.user_name,
	pc.p_comment,
	pc.p_time 
FROM
	product_comment AS pc
	LEFT JOIN users u ON pc.p_user = u.id
	LEFT JOIN product AS p ON pc.p_product = p.id;
           

查詢的時間為11.491s，這是非常慢的。

這就可能需要将表設計為反範式。運作一定資料的容易，以提高查詢效率。以空間換時間。

就将商品評論表改為（商品名、使用者名、商品評論、評論時間）允許商品名與使用者名的備援，以提高查詢的速度。

速度提升了10秒左右，由于本機實體裝置不好，這已經是最高效率了。

索引

什麼是索引？

索引相當于書本的目錄，能直接告訴你，你想要的東西在哪裡。提升查詢的效率，但是呢，若查詢資料量小（小于1000條資料）或者字段重複度大于10%的不建議添加索引，比如說性别。

口說無憑，來測試一下。

測試1：資料量小的情況（39條資料）

這是沒有添加唯一索引的查詢。（0.026s）

這是添加了唯一索引的查詢（0.030s）

由此可知，若資料量小的時候，加索引與沒有加索引是沒有什麼差別，可能速度還會更慢。

測試2：給性别設定索引，這是一種特殊情況，背景在女兒國中，100w人有10個人是男人，隻有總人口的10w分之1。

沒有設定索引的情況（0.391s)

給性别設定了普通索引（0.036s）

通過這就可以看出，索引的價值是幫助我們快速定位。若設定索引的資料有很多，那麼就沒有必要設定索引。

索引不是萬能的，有時候加了索引反而會使查詢速度變慢。

索引的種類

索引有四個類型，普通索引、限制索引、唯一索引、全文索引。

普通索引：沒有任何所屬條件的索引。

限制索引：在普通索引上增加了限制條件。

唯一索引：在限制索引上增加了唯一性限制，即主鍵索引。

全文索引：不常用，可以使用全文搜尋引擎，ElasticSearch和Solr代替。

前三種（普通索引、限制索引、唯一索引）統稱為一類索引，隻是對資料的限制性做逐漸的提升。

按實體分類的話，索引可分為聚集索引和非聚集索引。

聚集索引：按照主鍵進行排序來存儲，這樣查詢的資料行會很有效。直接可以找到索引的位置，資料行就跟在索引之後。

非聚集索引：在資料庫系統中，單獨存儲索引，沒有将資料等放到索引之後。是以要查詢到資料就需要兩步，第一步先找到索引，第二步再通過索引找到對應的資料。

聚集索引查詢行的效率高，但是增、删、改的效率就沒有非聚集索引高。因為聚集索引是使用順序存儲，那麼增、删、改的時候順序可能是随機的，那麼就需要修改資料後，重新再排序。

聯合索引為多個列組合而成的索引。建立聯合索引的時候，要注意列的排序。比如：建立的時候順序是（x,y,z）那我們使用的時候确實（y,z,x）那麼該索引就會失效。

聯合索引是左側比對原則，從左側開始進行比對，若沒有則所有就會失效。

索引是一種資料結構

我們先判斷索引的資料結構的好壞，因為索引是儲存在磁盤上的（為了防止資料丢失），那麼就需要進行磁盤的I/O操作。若I/O要操作的很多，就說明這資料結構不咋樣。

二叉樹結構：二分查找法，是一種高效的資料檢索方式。時間複雜度為 O（logn）

二叉樹分布，我們先給定一個根節點，然後判斷小于根節點的放左邊，大于根節點的放右邊。

我們将（30,28,18,7,12,38,88,99,33）生成一個二叉樹

可能會出現極端的情況，深度非常大

(2,3,4,7,11,14）

上圖也是二分樹，但是退化成了一條連結清單了，時間複雜度就為O(n)。為了解決這個問題，大神們提出了平衡二叉樹，就是給二叉樹增加了限制（左右樹的高度差不超過1）。

若平衡保持了5個節點，每進入一個節點都需要進行一次I/O操作，那麼就需要進行5次操作。雖然平衡二叉樹比較的效率高，但是同樣的深度也高。若深度為1000，則有可能需要進行1000次I/O操作，那堆磁盤的影響是不可想象的。

B樹

二叉樹，會有很深的深度，可能會有很多次對磁盤的I/O操作，效率就很低。那麼就不能僅僅隻是分兩個叉，就需要分出M個叉出來，這就是B樹。

B樹又稱為（Balance Tree），就是平衡的多路搜尋樹。提高了樹的廣度，降低了樹的深度進而減少了I/O流的操作，提高了搜尋的效率。

B樹的特點：每個根節點有M個子節點（中間節點），中間節點的範圍又[2,M]之間。若每個中間節點有K個關鍵字，那麼對應的有K+1個指針，則每個中間節點就有K+1的孩子節點（葉子節點），同時B樹葉子節點與非葉子節點都會存儲索引以及存儲資料。

以上圖為例，若要查找關鍵字31，根節點判斷31<38，取得P1指針指向磁盤1，然後判斷31>30獲得P3指針，指向磁盤6，磁盤6中有31,34。就取得了31關鍵字。

B+樹

B+樹對B樹做了改進，與B樹有以下的不同處。

1.每個節點中有關鍵字K，則對應的子節點也是K個。B樹是K+1個。

2.每個子節點都有一個關鍵字（最大或者最小）在父節點中。

3.非葉子節點不存儲索引或資料結構，隻有葉子節點才存儲。B樹中非葉子節點也存儲。

4.所有關鍵字都在葉子節點中，葉子節點後有指針指向另一個葉子節點，進而變成了連結清單。

根節點中有（4,20,38）對應的三個中間節點分别為（4,10,15）（20,28,33）（38,45,54）這三個中間節點分别有三個子節點（4,5,7）（10,13,14）（15,17,19）、（20,25,27）（28,30,31）（33,35,37）、（38,41,44）（45,47,53）（54,57,58）。這可以看出，每個父節點都是由子節點中的的最小關鍵字組合而成，葉子節點中就包含了所有的關鍵字。

那麼此時若要查找關鍵字28

1.在根節點中判斷20<28<38，則取到P2指針，P2指針指向磁盤2

2.在磁盤2中查找，判斷28=28，則取得P2指針，P2指針指向磁盤8

3.磁盤8中包含，28,30,31關鍵字，則取得28關鍵字。

B+樹與B樹對比

首先，B+樹的查詢效率更穩定，因為B+樹一定會在葉子節點中查到索引，而B樹中可能在非葉子節點就查到了索引。

其次，B+樹的查詢效率更高，因為B+樹比B樹更矮胖，即廣度更廣，深度更淺。

MySQL的Hash索引

鍵值Key通過Hash映射找到對應的桶，桶中存放的是記憶體指針，這就可以找到鍵值Key對應的指針，然後取得指針指向的資料行。Hash檢索的效率是非常高的，往往隻需要一次檢索就可以找到對應的資料。但是若是有hash沖突的話，就會在桶中進行鍵值key的檢索，這往往會降低檢索的效率。因為若要解決Hash沖突，就需要使用連結清單方式，一個一個檢索，知道檢索到對的資料行，直接将原來的時間複雜度O(1)程式設計了O(n)。

對比與B+樹來說，檢索的效率是更高效的。因為B+樹需要從根節點一直檢索到葉子節點，才能檢索到對應的資料。

但是Hash索引同樣也有限制：

1.不支援範圍查詢例如：where price>2000。隻支援等值比較查詢（IN 、= 、<>）

2.聯合索引中不支援左側比對原則，即聯合索引中取其中一個或者幾個來進行查詢，索引不會生效，隻能使用全部的列來查詢。

3.Hash索引不是按照索引值來排序的， 是以也不能進行排序。

4.Hash索引中不儲存資料，隻儲存了索引，是以不能取索引值來避免讀取行。

5.Hash檢索的效率是高效的， 除非遇到Hash相同造成Hash沖突，這就會由Hash表變為了連結清單，一個一個查詢直到查詢出所有符合的資料行。若hash沖突很多的話，維護索引的代價就比較高。

同時InnoDB支援自适應Hash索引，這也是InnoDB的三個特色之一。

自适應索引就是在B+樹上，就是當某個索引值使用得很頻繁的時候，就會在B+樹上建一個Hash索引，同時享用B+樹和Hash索引的優點。

什麼時候使用索引？

1. 字段的數值有唯一性限制，比如ID。
2. 經常需要在Where語句中進行查詢的字段。
3. 需要經常使用GROUP BY 的列
4. UPDATE、DELETE的where查詢條件的字段也需要添加索引。
5. DISTINCT 字段也可以添加索引
6. JOIN 連表查詢的字段，但是要注意的是，兩個表要進行連接配接的字段若要建立索引，那麼類型就必須一緻。

測試1：使用where條件查詢，沒有設定索引

之前通過存儲過程建立一張百萬級别的表資料。

沒有給p_product字段設計索引，那麼找到這條資料花費的時間是：1.309s

如果p_product設定了普通索引，查詢時間為0.051s效率提升了25/1

測試2：使用UPDATE将某個使用者的評論時間改為為目前時間。沒有設計索引。花費時間1.676s

将p_user設計索引。隻花了0.042s，效率提升了将近40/1

測試3：若我們将所有使用者的評論去重，沒有設計索引，花費時間2.959s

将p_user設計索引，花費時間為0.676s，效率提升4/1

那麼什麼情況下，索引會失效？

1. 索引列使用算數表達式例如：price+1 > 2000這樣price的所有就會失效。
2. 使用函數，例如：DATE（p_time）那麼p_time的索引就會失效。
3. 聯合函數，沒有遵守最左比對原則，那麼索引失效。

如果索引失效了，要使得索引繼續生效，就需要重寫SQL語句。

測試1：給評論釋出時間設計索引，然後使用函數讓索引失效。

CREATE INDEX product_time ON product_comment(p_time);

SELECT * FROM product_comment WHERE DATE(p_time) BETWEEN '2020-10-01 00:00:00' AND '2020-10-08 20:00:00';
           

查詢 2020-10-01 00:00:00到2020-10-08 20:00:00之間的所有評論，查詢時間為1.435s修改一下語句

重寫了SQL語句後，查詢時間為0.038s，速度提升了37/1

測試2：使用聯合索引，但是順序錯亂。

首先建立一個聯合索引（id,user_name）

查詢時沒有參照最左比對原則，而是直接使用user_name來查詢，就會導緻索引失效。

參照最左比對原則，查詢時間為0.030s，效率提升40/1。

SELECT * FROM users where id='st100590';
SELECT * FROM users where id='st100590' and user_name = 'US100590';

           

在極客時間SQL必知必會專欄裡，學到了很多，也把學到的一些知識，按自己的了解轉成了上述的文字。要在有限的日子裡，一直學習無限的知識！加油！