Mysql學習筆記二之索引基礎
一、索引結構
1 B-Tree索引
在談論索引的時候，沒有指明型別的時候，指的就是B-Tree索引，使用B-tree資料結構來儲存資料，大部分mysql儲存引擎都支援這種索引(archive儲存引擎除外，在5.1才開始支援自增列Auto_increment的索引)。
B-tree 索引是順序組織儲存的，所以很適合用來查詢範圍資料，此時效率會非常高。
B-tree索引支援:全值匹配，匹配最左字首、匹配列字首、匹配範圍值、精確匹配某一列並範圍匹配另外一列、只訪問索引的查詢，由於索引樹是有序的，所以除按值查詢之外，還可以用於查詢中的order by 的操作。
B-Tree 的限制：
1.如果不是按照索引的最左列開始查詢，則無法使用索引，例如建立了key(fast_name,last_name,date) 這個索引，那麼在對last_name 和date 進行精確查詢時(在不查詢fast_name的情況下)。
2.不能跳過索引中的列，如上面建立的索引來舉例，使用fast_name,date 但是沒有指定last_name,此時只使用date做為索引。
3.如果查詢中有某個列的範圍查詢，則其右邊所有的列都無法使用索引優化查詢，例如where fast_name='liutao' and last_name like '%'+'liu'+"%' and date='2017-6-25',這個查詢只使用fast_name,last_name這倆項作為索引列。
2 雜湊索引
雜湊索引基於雜湊表實現，只有精確搜尋匹配索引所有列的查詢才能生效如下表(只在mysql儲存引擎位Memory和NDB叢集引擎時才能使用)。
InnoDB引擎有一個特殊的功能叫做“自適應雜湊索引”(adaptive hash index)"，當InnoDB注意到某些索引值別使用的非常頻繁時，它會在記憶體中基於B-Tree 索引之上再建立一個雜湊索引，這式B-Tree 索引也具有一些雜湊索引的一些優點，
create table testhash( fname varchar(50) not null, lname varchar(50) not null, key using hash(fname) )engin=memory; ``` 雜湊表的限制： 1.雜湊索引只包含雜湊值和行指標，而不儲存欄位值，所以不能使用索引中的值來避免免讀取行。 2.雜湊索引資料並不是按照索引值順序儲存的，所有無法使用group by 排序。 3.雜湊也不支援分部索引列匹配查詢，因為雜湊索引始終是使用索引列的全部內容來計算雜湊值的，例如建立了(a,b,c)這各索引，當查詢只有資料列a時是不會使用索引的。 4.雜湊索引不支援範圍查詢 5.如果雜湊衝突很多的話，一些索引維護操作的代價也會很高。 ### 3 R-tree(空間資料索引) MyISAM儲存引擎支援空間索引，可以用作地理資料儲存。和B-Tree索引不同，這類索引無字首查詢。空間索引會從所有維度來索引資料。查詢時可以有效的使用任意維度來組合查詢。 ## 二、索引型別 ### 1.常見的索引類別有： - 主鍵索引: primary key ````java alter table [表名] add primary key ([列名]) ``` - 唯一索引: unique ````java alter table [表名] add unique([列名]) ``` - 普通索引:index ````java alter table [表名] add index [索引名稱] ([列名]); ``` - 組合索引:index ````java alter table [表名] add index [索引名稱] ([列名],[列名],[列名]....); ``` - 全文索引:fulltext ````java alter table [表名] add fulltext ([列名]); ``` ### 2.各索引的區別 >普通索引：最基本的索引，沒有任何限制 唯一索引：與”普通索引”類似，不同的就是：索引列的值必須唯一，但允許有空值。 主鍵索引：它 是一種特殊的唯一索引，不允許有空值。 全文索引：僅可用於 MyISAM 表，針對較大的資料，生成全文索引很耗時好空間。 組合索引：為了更多的提高mysql效率可建立組合索引，遵循”最左字首“原則。 ## 三、刪除索引 ````java drop index index_name on table_name; alter table table_name drop index index_name; alter table table_name drop primary key ``` 前倆條移除語句式等價的，第三條是單獨刪除主鍵的。