深入理解MySQL系列之索引

索引

查找一條數據的過程

先看下InnoDB的邏輯存儲結構：

  1. 表空間：可以看做是InnoDB存儲引擎邏輯結構的最高層，所有的數據都存放在表空間中。默認有個共享表空間ibdata1。如果啟用innodb_file_per_table參數，需要注意每張表的表空間記憶體放的只是數據、索引和插入緩衝Bitmap頁，其他類的數據，如回滾資訊、插入緩衝索引頁、系統事務資訊、二次寫緩衝等還是存放在原來共享表空間中。

  2. 段：
    表空間是由各個段組成，常見的段有數據段、索引段、回滾段等。數據段即為B+樹葉子節點(Leaf node segment)，索引段即為B+樹非葉子節點(Non-leaf node segment)
    區

  3. 區：是由連續頁組成的空間，在任何情況下每個區大小都為1MB。默認情況下，存儲引擎頁的大小為16KB，即一個區中一共有連續64個連續的頁。而為保證頁的連續性，InnoDB存儲引擎一次從磁碟申請4-5個區。

  4. 頁：
    頁(也可以稱塊)，是InnoDB磁碟管理的最小單位。默認每個頁大小16KB。1.2x版本後也可以通過參數innodb_page_size設置為4k、8k、16k

如查一條數據：select * from user where id=5;

這裡id是主鍵,我們通過這棵B+樹來查找，首先會去找到根頁，每張表的根頁位置在表空間文件中是固定的；找到根頁後通過二分查找法，定位到id=5的數據應該在指針P5指向的頁中，那麼進一步去page number=5的頁中查找，同樣通過二分查詢法即可找到id=5的記錄：

計算一棵B+樹可以存放多少行數據

也可以通過命令查看InnoDB每頁默認16KB：

show variables like ‘innodb_page_size’;

先計算非葉子節點， 假設主鍵ID為bigint類型，長度為8位元組，而指針大小在InnoDB源碼中設置為6位元組，這樣一共14位元組

而一個頁中能存放多少這樣的單元，其實就代表有多少指針，即16384/14=1170。

那麼可以算出一棵高度為2的B+樹，能存放1170*16=18720條這樣的數據記錄。

根據同樣的原理我們可以算出一個高度為3的B+樹可以存放：1170117016=21902400條這樣的記錄。

所以在InnoDB中B+樹高度一般為1-3層，它就能滿足千萬級的數據存儲。

索引一些概念
  1. 聚簇索引(clustered index): 就是將索引和數據放到一起，找到索引也就找到了數據；如下圖葉子節點存放一行所有數據。

  1. 輔助索引(Secondary Index或非聚簇索引): 就是將數據和索引分開，查找時需要先查找到索引，然後通過索引回表找到相應的數據。
    回表：先通過資料庫索引掃描出數據所在的行，再通過行主鍵id取出索引中未提供的數據，即基於非主鍵索引的查詢需要多掃描一棵索引樹。

如下圖，輔助索引查找後，會再回表到聚簇索引，最後找到數據。

InnoDB有且只有一個聚簇索引，而MyISAM中都是非聚簇索引。

  1. 聯合索引：指對錶上多個列進行索引。

聯合索引的最左前綴匹配原則： 對多個欄位同時建立的組合索引(有順序，ABC，ACB是完全不同的兩種聯合索引) 以聯合索引(a,b,c)為例，建立這樣的索引相當於建立了索引a、ab、abc三個索引。另外組合索引實際還是一個索引，並非真的創建了多個索引，只是產生的效果等價於產生多個索引。

  1. 覆蓋索引: 即從輔助索引中就可以得到查詢的記錄，而不需要查詢聚簇索引中的記錄。

使用覆蓋好處：

  • 輔助索引不包含整行記錄的所有資訊，故其大小要遠小於聚簇索引，減少大量IO操作。
  • 對某些統計(如count(id))並不會通過查詢聚簇索引來進行統計，減少IO操作

  1. 唯一索引：以唯一列生成的索引，該列不允許有重複值，但允許有空值(NULL)

  2. 索引下推：MySQL 5.6引入了索引下推優化，可以在索引遍歷過程中，對索引中包含的欄位先做判斷，過濾掉不符合條件的記錄，減少回表字數。

為什麼選B+樹，而不是B樹

B樹不管葉子節點還是非葉子節點，都會保存數據，這樣導致在非葉子節點中能保存的指針數量變少

指針少的情況下要保存大量數據，只能增加樹的高度，導致IO操作變多，查詢性能變低；

為什麼InnoDB只有一個聚簇索引，而不將所有索引都使用聚簇索引？

因為聚簇索引是將索引和數據都存放在葉子節點中，如果所有的索引都用聚簇索引，則每一個索引都將保存一份數據，會造成數據的冗餘，在數據量很大的情況下，這種數據冗餘是很消耗資源的。

什麼情況下會發生明明創建了索引，但是執行的時候並沒有通過索引呢？

查詢優化器。

一條SQL語句的查詢，可以有不同的執行方案，至於最終選擇哪種方案，需要通過優化器進行選擇，選擇執行成本最低的方案。

優化過程大致如下：

  • 1、根據搜索條件，找出所有可能使用的索引
  • 2、計算全表掃描的代價
  • 3、計算使用不同索引執行查詢的代價
  • 4、對比各種執行方案的代價，找出成本最低的那一個 。
索引的優缺點

索引的優點如下：

  • 1、唯一索引可以保證每一行數據的唯一性
  • 2、提高查詢速度
  • 3、加速表與表的連接
  • 4、顯著的減少查詢中分組和排序的時間
  • 5、通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的性能。

索引的缺點如下：

  • 創建索引時，需要對錶加鎖，在鎖表的同時，可能會影響到其他的數據操作
  • 雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行 INSERT、UPDATE 和 DELETE。因為更新表時，MySQL 不僅要保存數據，還要保存索引文件。
  • 建立索引會佔用磁碟空間的索引文件。一般情況這個問題不算嚴重，但如果你在一個大表上創建了多種組合索引，且伴隨大量數據量插入，索引文件大小也會快速膨脹。
  • 如果某個數據列包含許多重複的內容，為它建立索引就沒有太大的實際效果。
  • 對於非常小的表，大部分情況下簡單的全表掃描更高效。
使用索引時的注意事項

原則：
不應該

1、索引不是越多越好。索引太多，維護索引需要時間跟空間
2、 頻繁更新的數據，不宜建索引。
3、數據量小的表沒必要建立索引。

應該

1、重複率小的列建議生成索引。因為重複數據少，索引樹查詢更有效率，等價基數越大越好。
2、數據具有唯一性，建議生成唯一性索引。在資料庫的層面，保證數據正確性
3、頻繁group by、order by的列建議生成索引。可以大幅提高分組和排序效率
4、經常用於查詢條件的欄位建議生成索引。通過索引查詢，速度更快

索引失效的場景

1、模糊搜索：左模糊或全模糊都會導致索引失效，比如’%a’和’%a%’。但是右模糊是可以利用索引的，比如’a%’
2、隱式類型轉換：比如select * from t where name = xxx , name是字元串類型，但是沒有加引號，所以是由MySQL隱式轉換的，所以會讓索引失效
3、當語句中帶有or的時候：比如select * from t where name=『sw』 or age=14
4、不符合聯合索引的最左前綴匹配：(A,B,C)的聯合索引，你只where了C或B或只有B,C

其他注意事項：

  1. 索引不會包含有 null 值的列，只要列中包含有 null值都將不會被包含在索引中。
  2. 使用短索引。短索引不僅可以提高查詢速度而且可以節省磁碟空間和 I/O 操作
  3. 索引列排序。查詢只使用一個索引，因此如果 where 子句中已經使用了索引的話，那麼 order by 中的列是不會使用索引的。因此資料庫默認排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列創建複合索引。
  4. 不要在列上進行運算，這將導致索引失效而進行全表掃描
  5. 不使用 not in 和 <> 操作，這不屬於支援的範圍查詢條件，不會使用索引。

《MySQL技術內幕》
//mp.weixin.qq.com/s/6j64s9W6ogs5Y8BbhhkgnA
//mp.weixin.qq.com/s/KB73550tKpNccW-WKxT7-A
//mp.weixin.qq.com/s/ovMx9Dv9NCFxSsFM98uYFw

Tags:

VirMach 便宜 VPS

QNews

QNews