B+樹索引的使用什麼時候使用B+樹索引

   并不是在所有的查詢條件下出現的列都需要添加索引。對于什麼時候添加B+樹索引，我的經驗是通路表中很少一部分時，使用B+樹索引才有意義。對于性别字段，地區字段，類型字段，它們可取值的範圍很小，即低選着性。如：

   select * from student WHERE sex = 'M'

對于性别，可取值的範圍隻有'M','F'。對上述SQL語句得到的結果可能是該表的50%的資料，這時添加B+樹索引時完全沒有必要的。相反，如果某個字段的取值範圍很廣，幾乎沒有重複，即高選擇性，即此時使用B+樹索引時做合适的，例如姓名字段，基本上在一個應用中都不允許重名的出現。

   是以，當通路高選擇性字段并從表中取出很少一部分時，對這個字段添加B+樹索引是非常有必要的。但是如果出現了通路字段是高選擇性的，但是取出的行資料占用表中大部分的資料時，這時MySQL資料庫就不會使用B+樹索引了，我們先來看一個例子：

mysql> show index from info\G;

*************************** 1. row ***************************

        Table: info

   Non_unique: 0

     Key_name: PRIMARY

 Seq_in_index: 1

  Column_name: id

    Collation: A

  Cardinality: 356639

     Sub_part: NULL

       Packed: NULL

         Null: 

   Index_type: BTREE

      Comment: 

Index_comment: 

*************************** 2. row ***************************

   Non_unique: 1

     Key_name: index_link_family

  Column_name: link_family

  Cardinality: 9385

     Sub_part: 255

         Null: YES

*************************** 3. row ***************************

     Key_name: index_date

  Column_name: date

      表info大約有50萬行資料。info表上的date字段，該字段是日期類型，字段上有一個index_date的非唯一索引。我們來看下面兩條SQL的執行：

mysql> explain  select * from info where date = '2006-07-26 15:56:01'\G;

           id: 1

  select_type: SIMPLE

        table: info

         type: ref

possible_keys: index_date

          key: index_date

      key_len: 8

          ref: const

         rows: 2

        Extra: 

1 row in set (0.00 sec)

ERROR: 

No query specified

可以看到使用了index_date這個索引，這也符合我們前面提到的高選擇性，選取表中很少行的原則。但是如果執行下面這條語句：

mysql> explain  select * from info where date > '2006-07-26 15:56:01'\G;

         type: ALL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 356639

        Extra: Using where

可以看到possible_keys依然是index_date，但是實際優化器使用的索引key顯示的是NULL。為什麼？因為這不符合我們前面說的原則，雖然date這個字段的值是高選擇性的，但是我們取出的行占用了表中很大一部分。

mysql>   select @a:=count(id) from info where date > '2006-07-26 15:56:01';

+---------------+

| @a:=count(id) |

|        452549 |

1 row in set (0.18 sec)

mysql> select @b:=count(id) from info ;

| @b:=count(id) |

|        452554 |

1 row in set (0.11 sec)

mysql> select @a/@b;

+--------+

| @a/@b  |

| 1.0000 |

可以看到我們将取出行的數大概是表的100%的行，是以優化器沒有選擇使用索引。Mysql資料庫的優化器會通過EXPLAIN的rows字段預估查詢可能得到的行，如果大于某一個值，則B+樹會選擇全表的掃描。至于這個值，根據我的經驗一般在20%。即當取出的資料量超過表中資料的20%，優化器就不會使用索引，而是進行全表的掃表。

但是預估的傳回行數的值是不準确的，可以看到優化器判斷日期小于2006-07-26的行為356639，而實際的是452549 。

有時優化器的選擇并不完全是正确的，有時你更應該相信自己的判斷（可以通過force index（index_name）來執行判斷兩條語句執行的時間差别）。

本文轉自 kuchuli 51CTO部落格，原文連結：http://blog.51cto.com/lgdvsehome/1174516，如需轉載請自行聯系原作者