MySQL(1)---索引

標籤：等於   條件   資料庫設計   顯示   ash   SQ   nbsp   執行   info   

索引

 

 索引的基本概念

      您可以把索引理解為一種特殊的目錄，它的存在就是方便我們快速查詢資料用的。

一.索引的分類

       MySQL主要的幾種索引類型：1.普通索引、2.唯一索引、3.主鍵索引、4.複合式索引、5.全文索引。 

     1.普通索引
         是最基本的索引，它沒有任何限制。

     2.唯一索引
        與普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是複合式索引，則列值的組合必須唯一

     3.主鍵索引
        是一種特殊的唯一索引，一個表只能有一個主鍵，不允許有空值。

 主鍵索引和唯一索引的區別：
        主鍵必唯一，但是唯一索引不一定是主鍵；
 一張表上只能有一個主鍵，但是可以有一個或多個唯一索引。 

    4.複合式索引
        一個索引包含多個列，實際開發中推薦使用複合索引。

複合索引主要特點：
       如果我們建立了(name, age,xb)的複合索引，那麼其實相當於建立了(name, age,xb)、(name, age)、(name)三個索引，這被稱為最佳左首碼
特性。因此我們在建立複合索引時應該將最常用作限制條件的列放在最左邊，依次遞減。

     MySQL INNodb建立複合索引 a,b,c；那麼 查詢條件 where a =xxx and c= xxx 能用到索引嘛？
回答：可以。

   注意事項:
    1、對於複合索引,在查詢使用時,最好將條件順序按找索引的順序,這樣效率最高;
          select * from table1 where col1=A AND col2=B AND col3=D
    2、如果使用 where col2=B AND col1=A 或者 where col2=B 將不會使用索引

   5.全文索引
   全文檢索搜尋的索引。
    FULLTEXT 用於搜尋很長一篇文章的時候，效果最好。用在比較短的文本，如果就一兩行字的，普通的 INDEX 也可以。

綜合小案例理解：

比如你在為某商場做一個會員卡的系統。
這個系統有一個會員表
有下欄欄位：

會員編號 INT
會員姓名 VARCHAR(10)
會員社會安全號碼碼 VARCHAR(18)
會員電話 VARCHAR(10)
會員住址 VARCHAR(50)
會員備忘資訊 TEXT

那麼這個 會員編號，作為主鍵，使用 PRIMARY
會員姓名 如果要建索引的話，那麼就是普通的 INDEX
會員社會安全號碼碼 如果要建索引的話，那麼可以選擇 UNIQUE （唯一的，不允許重複）
會員備忘資訊 ， 如果需要建索引的話，可以選擇 FULLTEXT，全文檢索搜尋。
不過 FULLTEXT 用於搜尋很長一篇文章的時候，效果最好。
用在比較短的文本，如果就一兩行字的，普通的 INDEX 也可以。

 

二.索引的優點缺點

優點：

第一，通過建立唯一性索引，可以保證資料庫表中每一行資料的唯一性。
第二，可以大大加快資料的檢索速度，這也是建立索引的最主要的原因。
第三，可以加速表和表之間的串連，特別是在實現資料的參考完整性方面特別有意義。
第四，在使用分組和排序 子句進行資料檢索時，同樣可以顯著減少查詢中分組和排序的時間。
第五，通過使用索引，可以在查詢的過程中，使用最佳化隱藏器，提高系統的效能。

缺點：

（1）當對錶中的資料進行增加、刪除和修改的時候，索引也要動態維護，這樣就降低了資料的維護速度。
（2）索引需要佔物理空間，除了資料表占資料空間之外，每一個索引還要佔一定的物理空間，如果要建立聚簇索引，那麼需要的空間就會更大

 

三、什麼時候用索引

1：如何判定是否須要建立索引

    （1. 較頻繁的作為查詢條件的欄位應該建立索引 需要經常GROUP BY和ORDER BY的列。

    （2. 唯一性太差的欄位不適合單獨建立索引，即使頻繁作為查詢條件 比如性別，民族，政治面貌
唯一性太差的欄位主要是指哪些呢？如狀態欄位、類型欄位等這些欄位中存放的資料可能總共就是那麼幾個或幾十個值重複使用，每個值都會存在於成千上萬 或更多的記錄中。對於這類欄位，完全沒有必要建立單獨的索引
   （3. 更新非常頻繁的欄位不適合建立索引

 

四、索引的注意事項（最佳化）

1.盡量少使用模糊查詢，如果要使用那麼，萬用字元%可以出現在結尾，不能在開頭。
    如：name like ‘張%’ ，索引有效
    而：name like ‘%張’ ,索引無效，全表查詢

2：or 會引起全表掃描

3：不要使用NOT、!=、NOT IN、NOT LIKE等

4.盡量少使用select*，而是根據需求來選擇需要顯示的欄位

5.索引不會包含有null值的列
只要列中包含有null值都將不會被包含在索引中，複合索引中只要有一列含有null值，那麼這一列對於此複合索引就是無效的。所以我們在資料庫設計時不要讓欄位的預設值為null。

6.不要在列上進行運算，這將導致索引失效而進行全表掃描

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

8、union並不絕對比or的執行效率高

我們前面已經談到了在where子句中使用or會引起全表掃描，一般的，我所見過的資料都是推薦這裡用union來代替or。事實證明，這種說法對於大部分都是適用的。
有一點不適用:如果or兩邊的查詢列是一樣的話，那麼用union則反倒和用or的執行速度差很多，雖然這裡union掃描的是索引，而or掃描的是全表。
1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=‘‘2004-9-16‘‘ or fariqi=‘‘2004-2-5‘‘
用時：6423毫秒。掃描計數 2，邏輯讀 14726 次，物理讀 1 次，預讀 7176 次。

 

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=‘‘2004-9-16‘‘
union
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=‘‘2004-2-5‘‘
用時：11640毫秒。掃描計數 8，邏輯讀 14806 次，物理讀 108 次，預讀 1144 次。

 

五、索引方式（方法）（儲存引擎）

     mysql有兩種所以方式：Hash和BTree。

Hash索引
所謂Hash索引，當我們要給某張表某列增加索引時，將這張表的這一列進行雜湊演算法計算，得到雜湊值，排序在雜湊數組上。所以Hash索引可以一次定位，其效率很高，而Btree索引需要經過多次的磁碟IO。
因為Hash索引比較的是經過Hash計算的值，所以在= in <=>(安全等於的時候）塔的效率是非常，但我們開發一般會選擇Btree，Hash會存在如下一些缺點。

（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索引高。

BTREE
B-Tree 索引是 MySQL 資料庫中使用最為頻繁的索引類型。簡單理解，塔就像一棵樹，B-Tree索引需要從根節點到枝節點，就能才能訪問到頁節點的具體資料。
btree索引能夠加快訪問資料的速度，因為儲存引擎不再需要進行全表掃描來擷取需要的資料，取而代之的是從索引的根節點開始進行搜尋，根節點的槽中存放了指向子節點的指標，儲存引擎根據這些指標向下層尋找,通過比較節點頁的值和要尋找的值可以找到合適的指標進入下一層子節點，這些指標實際上定義了子節點頁中值的上限和下限，最終儲存引擎要麼是找到對應的值，要麼是該記錄不存在。

B-tree 索引可以用於使用 =, >, >=, <, <= 或者 BETWEEN 運算子的列比較。如果 LIKE 的參數是一個沒有以萬用字元起始的常量字串的話也可以使用這種索引。

 

另外有關叢集索引和非叢集索引，這裡收藏兩篇文章：

          1、快速理解叢集索引和非叢集索引

          2、主鍵就是叢集索引嗎？

 

    想太多，做太少，中間的落差就是煩惱。想沒有煩惱，要麼別想，要麼多做。上尉【14】

 

MySQL(1)---索引