索引

創建索引的目的是減小查詢時間，提高查詢效率。減少磁盤的讀取次數

1. 索引的含義

? 索引是一個排序的列表，在這個列表中存儲著索引的值和包含這個值的數據所在行的物理地址，在數據十分龐大的時候，索引可以大大加快查詢的速度，這是因為使用索引后可以不用掃描全表來定位某行的數據，而是先通過索引表找到該行數據對應的物理地址然后訪問相應的數據。

2.索引的原理

? 索引一般以文件的形式存儲在磁盤中，索引的存儲原理是以空間換取時間。未添加索引時查詢是全局掃描，有多少數據就要掃描多少條數據。添加索引后，會將建立的索引的key添加到B-Tree上；B樹的特點就是適合在磁盤等直接存儲設備上組織動態查找表，每次以索引進行條件查詢時，會去樹上根據key值直接進行搜索。

3.索引數據結構 ：

二叉樹、 紅黑樹、 Hash表、 B-Tree。

不同的存儲引擎索引的數據結構不同

4.與索引數據結構相關的計算機原理

? 兩種類型的存儲：計算機系統一般包含兩種類型的存儲，計算機主存（RAM）和外部存儲器（如硬盤、CD、SSD等）。主存的讀取速度快，內存小。相對于主存，外部磁盤的數據讀取速率要比主從慢好幾個數量級，內存大。實際數據庫中數據都是存儲到外部存儲器的。

? 主存存儲原理：當系統需要讀取主存時，則將地址信號放到地址總線上傳給主存，主存讀到地址信號后，解析信號并定位到指定存儲單元，然后將此存儲單元數據放到數據總線上，供其它部件讀取。寫主存的過程類似，系統將要寫入單元地址和數據分別放在地址總線和數據總線上，主存讀取兩個總線的內容，做相應的寫操作。

? 磁盤存儲原理：索引一般以文件形式存儲在磁盤上，索引檢索需要磁盤I/O操作。與主存不同，磁盤I/O存在機械運動耗費，因此磁盤I/O的時間消耗是巨大的。磁盤讀取數據靠的是機械運動，當需要從磁盤讀取數據時，系統會將數據邏輯地址傳給磁盤，磁盤的控制電路按照尋址邏輯將邏輯地址翻譯成物理地址，即確定要讀的數據在哪個磁道，哪個扇區。為了讀取這個扇區的數據，需要將磁頭放到這個扇區上方，為了實現這一點，磁頭需要移動對準相應磁道，這個過程叫做尋道，所耗費時間叫做尋道時間，然后磁盤旋轉將目標扇區旋轉到磁頭下，這個過程耗費的時間叫做旋轉時間，最后便是對讀取數據的傳輸。 所以每次讀取數據花費的時間可以分為尋道時間、旋轉延遲、傳輸時間三個部分。

? 局部性原理和磁盤預讀：磁盤的存取本身比主存慢，加上磁盤的機械運動消耗使得磁盤的存取更慢。所以為了提高查詢速度就要減少減少磁盤I/O。為了達到這個目的，磁盤往往不是嚴格按需讀取，而是每次都會預讀，即使只需要一個字節，磁盤也會從這個位置開始，順序向后讀取一定長度的數據放入內存。這樣做的理論依據是計算機科學中著名的局部性原理：當一個數據被用到時，其附近的數據也通常會馬上被使用。程序運行期間所需要的數據通常比較集中。

? 由于磁盤順序讀取的效率很高（不需要尋道時間，只需很少的旋轉時間），因此對于具有局部性的程序來說，預讀可以提高I/O效率。預讀的長度一般為頁（page）的整倍數。頁是計算機管理存儲器的邏輯塊，硬件及操作系統往往將主存和磁盤存儲區分割為連續的大小相等的塊，每個存儲塊稱為一頁（在許多操作系統中，頁得大小通常為4k），主存和磁盤以頁為單位交換數據。當程序要讀取的數據不在主存中時，會觸發一個缺頁異常，此時系統會向磁盤發出讀盤信號，磁盤會找到數據的起始位置并向后連續讀取一頁或幾頁載入內存中，然后異常返回，程序繼續運行。

5.MySQL的存儲引擎

? MySQl默認的存儲引擎是InnoDB: InnoDB 底層索引的數據結構為B+Tree；(B+Tree是B-Tree的變種)

6.B-Tree和B+Tree，Hash的區別

? B-Tree

? 每個節點都存儲key和data，所有節點組成這棵樹，并且葉子節點指針為null。

B+Tree

	只有葉子節點存儲data，葉子節點包含了這棵樹的所有鍵值，葉子節點存儲一個指向相鄰葉子節點的指針。

Hash

Hash 索引結構的特殊性，其檢索效率非常高，索引的檢索可以一次定位，不像B-Tree 索引需要從根節點到枝節點，最后才能訪問到頁節點這樣多次的IO訪問，所以 Hash 索引的查詢效率要遠高于 B-Tree 索引。

（1）Hash 索引僅僅能滿足"=","IN"和"<=>"查詢，不能使用范圍查詢。

由于 Hash 索引比較的是進行 Hash 運算之后的 Hash 值，所以它只能用于等值的過濾，不能用于基于范圍的過濾，因為經過相應的 Hash 算法處理之后的 Hash 值的大小關系，并不能保證和Hash運算前完全一樣。

（2）Hash 索引無法被用來避免數據的排序操作。

由于 Hash 索引中存放的是經過 Hash 計算之后的 Hash 值，而且Hash值的大小關系并不一定和 Hash 運算前的鍵值完全一樣，所以數據庫無法利用索引的數據來避免任何排序運算；

（3）Hash 索引不能利用部分索引鍵查詢。

對于組合索引，Hash 索引在計算 Hash 值的時候是組合索引鍵合并后再一起計算 Hash 值，而不是單獨計算 Hash 值，所以通過組合索引的前面一個或幾個索引鍵進行查詢的時候，Hash 索引也無法被利用。

（4）Hash 索引在任何時候都不能避免表掃描。

前面已經知道，Hash 索引是將索引鍵通過 Hash 運算之后，將 Hash運算結果的 Hash 值和所對應的行指針信息存放于一個 Hash 表中，由于不同索引鍵存在相同 Hash 值，所以即使取滿足某個 Hash 鍵值的數據的記錄條數，也無法從 Hash 索引中直接完成查詢，還是要通過訪問表中的實際數據進行相應的比較，并得到相應的結果。

（5）Hash 索引遇到大量Hash值相等的情況后性能并不一定就會比B-Tree索引高。

對于選擇性比較低的索引鍵，如果創建 Hash 索引，那么將會存在大量記錄指針信息存于同一個 Hash 值相關聯。這樣要定位某一條記錄時就會非常麻煩，會浪費多次表數據的訪問，而造成整體性能低下

7.explain

explain模擬執行SQL語句,對SQL語句進行分析并輸出詳細信息，便于開發人員進行優化操作。

explain的詳細信息：

• id：執行順序（先執行id值大的，相同id值先執行前面的）

• select_type:查詢的類型

  • SIMPLE, 表示此查詢不包含 UNION 查詢或子查詢
  • PRIMARY, 表示此查詢是最外層的查詢
  • UNION, 表示此查詢是 UNION 的第二或隨后的查詢
  • DEPENDENT UNION, UNION 中的第二個或后面的查詢語句, 取決于外面的查詢
  • UNION RESULT, UNION 的結果
  • SUBQUERY, 子查詢中的第一個 SELECT
  • DEPENDENT SUBQUERY: 子查詢中的第一個 SELECT, 取決于外面的查詢. 即子查詢依賴于外層查詢的結果.

• table:查詢的表名

• partitions：查詢進行匹配分區

• type：判斷查詢是否高效的重要依據

  • system: 表中只有一條數據. 這個類型是特殊的 const 類型.
  • const: 針對主鍵或唯一索引的等值查詢掃描, 最多只返回一行數據. const 查詢速度非常快, 因為它僅僅讀取一次即可.
  • eq_ref: 此類型通常出現在多表的 join 查詢, 表示對于前表的每一個結果, 都只能匹配到后表的一行結果. 并且查詢的比較操作通常是 =, 查詢效率較高.
  • ref: 此類型通常出現在多表的 join 查詢, 針對于非唯一或非主鍵索引, 或者是使用了 最左前綴 規則索引的查詢.
  • range: 表示使用索引范圍查詢, 通過索引字段范圍獲取表中部分數據記錄. 這個類型通常出現在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.
  • index: 表示全索引掃描(full index scan), 和 ALL 類型類似, 只不過 ALL 類型是全表掃描, 而 index 類型則僅僅掃描所有的索引, 而不掃描數據.
  • ALL: 表示全表掃描, 這個類型的查詢是性能最差的查詢之一. 通常來說, 我們的查詢不應該出現 ALL 類型的查詢, 因為這樣的查詢在數據量大的情況下, 對數據庫的性能是巨大的災難. 如一個查詢是 ALL 類型查詢, 那么一般來說可以對相應的字段添加索引來避免.

• possible_keys:能夠使用到的索引

• key:實際用到的索引

• key_len:查看索引是否全部被使用（值越大越好）

• rows:掃描的數據行數（值越小越好）

• Extra:額外信息

  • Using filesort：當 Extra 中有 Using filesort 時, 表示 MySQL 需額外的排序操作, 不能通過索引順序達到排序效果. 一般有 Using filesort, 都建議優化去掉, 因為這樣的查詢 CPU 資源消耗大.

  • Using index
    "覆蓋索引掃描", 表示查詢在索引樹中就可查找所需數據, 不用掃描表數據文件, 往往說明性能不錯

  • Using temporary
    查詢有使用臨時表, 一般出現于排序, 分組和多表 join 的情況, 查詢效率不高, 建議優化.

8.索引的類型

• 普通索引：單列索引是最基本的索引，它沒有任何限制。
• 組合索引：組合索引是在多個字段上創建的索引。組合索引遵守“最左前綴”原則，即在查詢條件中使用了組合索引的第一個字段，索引才會被使用。
• 唯一索引:唯一索引和普通索引類似，主要的區別在于，唯一索引限制列的值必須唯一，但允許存在空值（只允許存在一條空值)。
• 主鍵索引:主鍵索引是一種特殊的唯一索引，一個表只能有一個主鍵，不允許有空值。
• 全文索引:全文索引主要用來查找文本中的關鍵字，而不是直接與索引中的值相比較。fulltext索引跟其它索引大不相同，它更像是一個搜索引擎，而不是簡單的where語句的參數匹配。fulltext索引配合match against操作使用，而不是一般的where語句加like。目前只有char、varchar，text 列上可以創建全文索引。

創建索引的語句

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name [USING index_type]

on tbl_nam(index_col_name,……)

刪除索引

DROP INDEX index_name ON tbl_name

9.設置索引的規則

1.最適合索引的列是出現在where子句中的列，或連接子句中指定的列，而不是出現在select關鍵字后的選擇列表中的列。
2.索引的列的基數越大，索引的效果越好。
3.盡量使用短索引。能夠節省大量索引空間，也可能使查詢更快。
4.不要過度索引。索引都要占用額外的磁盤空間，并降低寫操作的性能。在修改表的內容時，索引必須進行更新，有時可能需要重構。

10.注意事項

1.索引不會包含有null值的列

只要列中包含有null值都將不會被包含在索引中，復合索引中只要有一列含有null值，那么這一列對于此復合索引就是無效的。所以我們在數據庫設計時不要讓字段的默認值為null。

2.使用短索引

對串列進行索引，如果可能應該指定一個前綴長度。例如，如果有一個char(255)的列，如果在前10個或20個字符內，多數值是惟一的，那么就不要對整個列進行索引。短索引不僅可以提高查詢速度而且可以節省磁盤空間和I/O操作。

3.索引列排序

查詢只使用一個索引，因此如果where子句中已經使用了索引的話，那么order by中的列是不會使用索引的。因此數據庫默認排序可以符合要求的情況下不要使用排序操作；盡量不要包含多個列的排序，如果需要最好給這些列創建復合索引。

4.like語句操作

一般情況下不推薦使用like操作，如果非使用不可，如何使用也是一個問題。like “%aaa%” 不會使用索引而like “aaa%”可以使用索引。

5.不要在列上進行運算

這將導致索引失效而進行全表掃描

6.不使用not in和<>操作

11.索引的優缺點

索引的優缺點：可以快速檢索，減少I/O次數，加快檢索速度；根據索引分組和排序，可以加快分組和排序；

索引本身也是表，因此會占用存儲空間，一般來說，索引表占用的空間的數據表的1.5倍；索引表的維護和創建需要時間成本，這個成本隨著數據量增大而增大；構建索引會降低數據表的修改操作（刪除，添加，修改）的效率，因為在修改數據表的同時還需要修改索引表；