MySQL索引篇

innodb索引概念

總結記錄下innodb的索引概念，以備檢視

innodb索引分類：

聚簇索引(clustered index)

    1)  有主鍵時，根據主鍵建立聚簇索引

    2)  沒有主鍵時，會用一個唯一且不為空的索引列做為主鍵，成為此表的聚簇索引

    3) 如果以上兩個都不滿足那innodb自己建立一個虛拟的聚集索引

輔助索引(secondary index)

   非聚簇索引都是輔助索引，像複合索引、字首索引、唯一索引

myisam索引：因為myisam的索引和資料是分開存儲存儲的，myisam通過key_buffer把索引先緩存到記憶體中，當需要通路資料時（通過索引通路資料），在記憶體中直接搜尋

     索引，然後通過索引找到磁盤相應資料，這也就是為什麼索引不在key buffer命中時，速度慢的原因

innodb索引：innodb的資料和索引放在一起，當找到索引也就找到了資料

自适應哈希索引：innodb會監控表上的索引使用情況，如果觀察到建立哈希索引可以帶來速度的提升，那就建立哈希索引，自

适應哈希索引通過緩沖池的B+樹構造而來，

           是以建立的速度很快，不需要将整個表都建哈希索引，InnoDB

存儲引擎會自動根據通路的頻率和模式來為某些頁建立哈希索引。自适應哈希索引不需要

           存儲磁盤的，當停庫内容會丢失，起來會自己建立，慢慢維護索引。    

聚簇索引：

MySQL

InnoDB一定會建立聚簇索引，把實際資料行和相關的鍵值儲存在一塊，這也決定了一個表隻能有一個聚簇索引，即MySQL不會一次把資料行儲存在二個地方。

     1)  InnoDB通常根據主鍵值(primary key)進行聚簇

     2) 如果沒有建立主鍵，則會用一個唯一且不為空的索引列做為主鍵，成為此表的聚簇索引

     3) 上面二個條件都不滿足，InnoDB會自己建立一個虛拟的聚集索引

優點：

聚簇索引的優點,就是提高資料通路性能。聚簇索引把索引和資料都儲存到同一棵B+樹資料結構中，并且同時将索引列與相關資料行儲存在一起。這意味着，當你通路同一資料頁不同行記錄時，已經把頁加載到了Buffer中，再次通路的時候，會在記憶體中完成通路，不必通路磁盤。不同于MyISAM引擎，它将索引和資料沒有放在一塊，放在不同的實體檔案中，索引檔案是緩存在key_buffer中，索引對應的是磁盤位置，不得不通過磁盤位置通路磁盤資料。

缺點：

1) 維護索引很昂貴，特别是插入新行或者主鍵被更新導至要分頁(page

split)的時候。建議在大量插入新行後，選在負載較低的時間段，通過OPTIMIZE

TABLE優化表，因為必須被移動的行資料可能造成碎片。使用獨享表空間可以弱化碎片

2)

表因為使用UUId作為主鍵，使資料存儲稀疏，這就會出現聚簇索引有可能有比全表掃面更慢，是以建議使用int的auto_increment作為主鍵 

3)

如果主鍵比較大的話，那輔助索引将會變的更大，因為輔助索引的葉子存儲的是主鍵值；過長的主鍵值，會導緻非葉子節點占用占用更多的實體空間 

輔助索引

在聚簇索引之上建立的索引稱之為輔助索引，輔助索引通路資料總是需要二次查找。輔助索引葉子節點存儲的不再是行的實體位置，而是主鍵值。通過輔助索引首先找到的是主鍵值，再通過主鍵值找到資料行的資料葉，再通過資料葉中的Page

Directory找到資料行。

複合索引

由多列建立的索引稱為符合索引，在符合索引中的前導列必須出現在where條件中，索引才會被使用

ALTER TABLE `test`.`users` ADD INDEX `idx_users_id_name` (`name`(10) ASC,

`id` ASC) ;

字首索引

當索引的字元串列很大時，建立的索引也就變得很大，為了減小索引體積，提高索引的掃描速度，就用索引的前部分字串索引，這樣索引占用的空間就會大大減少，并且索引的選擇性也不會降低很多。而且是對BLOB和TEXT列進行索引，或者非常長的VARCHAR列，就必須使用字首索引，因為MySQL不允許索引它們的全部長度。

使用：

列的字首的長度選擇很重要，又要節約索引空間，又要保證字首索引的選擇性要和索引全長度選擇性接近。

唯一索引

唯一索引比較好了解，就是索引值必須唯一，這樣的索引選擇性是最好的

主鍵索引

主鍵索引就是唯一索引，不過主鍵索引是在建立表時就建立了，唯一索引可以随時建立。

說明

主鍵和唯一索引差別

     1) 主鍵是主鍵限制+唯一索引

     2) 主鍵一定包含一個唯一索引，但唯一索引不是主鍵

     3) 唯一索引列允許空值，但主鍵列不允許空值

     4) 一個表隻能有一個主鍵，但可以有多個唯一索引

索引掃描方式：

緊湊索引掃描(dense index)：

在最初，為了定位資料需要做權表掃描，為了提高掃描速度，把索引鍵值單獨放在獨立的資料的資料塊裡，并且每個鍵值都有個指向原資料塊的指針，因為索引比較小，掃描索引的速度就比掃描全表快，這種需要掃描所有鍵值的方式就稱為緊湊索引掃描

松散索引掃描(sparse index)：

為了提高緊湊索引掃描效率，通過把索引排序和查找算法（B+trre），發現隻需要和每個資料塊的第一行鍵值比對，就可以判斷下一個資料塊的位置或方向，是以有效資料就是每個資料塊的第一行資料，如果把每個資料塊的第一行資料建立索引，這樣在這個新建立的索引上折半查找，資料定位速度将更快。這種索引掃描方式就稱為松散索引掃描。

覆寫索引掃描(covering index)：

包含所有滿足查詢需要的資料的索引稱為覆寫索引，即利用索引傳回select清單中的字段，而不必根據索引再次讀取資料檔案

索引相關常用指令：

1) 建立主鍵

 CREATE TABLE `pk_tab2` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `a1` varchar(45) DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2) 建立唯一索引

create unique index indexname on tablename(columnname); 

alter table tablename add unique index indexname(columnname);

3) 建立單列一般索引

create index indexname on tablename(columnname);

alter table tablename add index indexname(columnname);

4) 建立單列字首索引

create index indexname on tablename(columnname(10));  

 //單列的前10個字元建立字首索引

alter table tablename add index indexname(columnname(10));

//單列的前10個字元建立字首索引

5) 建立複合索引

create index indexname on tablename(columnname1，columnname2);  

 //多列的複合索引

create index indexname on tablename(columnname1，columnname2(10));  

 //多列的包含字首的複合索引

alter table tablename add index indexname(columnname1，columnname2);

//多列的複合索引

alter table tablename add index indexname(columnname1，columnname(10));

//多列的包含字首的複合索引

6) 删除索引

drop index indexname on tablename;;

alter table tablename drop  index indexname;

7) 檢視索引

show index from tablename;

show create table pk_tab2;