mysql 儲存引擎 innodb 學習複習 之 索引

innodb中有兩種索引，B+樹索引和雜湊索引，其中hash索引是自適應性的，儲存引擎會根據表的使用方式自動建立雜湊索引，不能人為的幹涉

1.  叢集索引

叢集索引，innodb儲存引擎表是索引組織表，即表中的資料是按照主鍵順序存放，而叢集索引是按照每張表的主鍵構造出一顆B+樹，並且葉子節點存放的是整張表的行記錄資料，因此，葉子節點是資料節點，並且因為B+樹的特點，資料節點通過一個雙向的鏈表串連起來，使得尋找更加快速，由於實際的資料頁只能按照一顆B+樹來存放，所以，一張表只能擁有一個叢集索引，在多數情況下，查詢最佳化工具傾向於採用叢集索引。

實驗

先建立一張表 t ，這裡以人為的方式讓其每個頁只能存放兩行資料

 create table t (a int not null primary key,b varchar(8000));

然後插入3條資料

insert into test.t select 2,repeat('a',7000);insert into test.t select 3,repeat('a',7000);insert into test.t select 4,repeat('a',7000);

　　然後使用書中的分析，利用py_innodb_info.py工具分析，

這裡建立的是一顆如下的B+樹

叢集索引並不是按照物理順序儲存的，只是邏輯上的連續

叢集索引的好處： 對於主鍵的排序尋找和範圍尋找速度非常的快  例如：要尋找一個註冊使用者表中最近註冊的10個使用者，可以通過尋找最後一個資料頁，並取出10條記錄，範圍尋找：如果要尋找某一個範圍的資料，通過頁節點上層的中間節點就可以得到頁的範圍，之後讀取資料頁。

2. 非叢集索引

對於非叢集索引，頁層級上不包含行的全部資料，頁節點除了包含索引值之外，每個頁層級的索引中還包含一個書籤，該書籤可以用來告訴儲存引擎，在哪裡可以找到與索引相對應的行資料，因為innodb儲存引擎是索引組織表，所以書籤就是相應行資料的叢集索引鍵。

相比於叢集索引，一個資料庫表中可以建立多個非叢集索引，當通過非叢集索引來查詢資料時，innodb會遍曆非叢集索引並通過葉子節點獲得一個指向主鍵索引（叢集索引）的一個指標，然後通過主鍵索引尋找到相應的行資料，這比叢集索引需要更多的IO。

在上面的實驗中的表 中添加一個欄位int c

   然後執行：update t set c = 0-a;

添加索引 alter table t add key idx_c(c);

這是得到的是一個叢集索引和非叢集索引結合的樹

還可以建立一個列的開頭部分：alter table t add key idx_b(b(100));

還可以添加一個聯合索引 ：alter table t add key idx_a_b (a,b);

可以使用 drop index idx_a_b on  t;  刪除索引

可以使用 show index from t;  查看點錢的index

B+樹索引的使用

在訪問表中很少一部分行的情況下使用B+樹索引才有意義，對於像性別欄位只有男女兩種情況的，完全沒有必要，對於取之範圍廣的像姓名，使用B+樹索引可以達到很好的效果

3. 順序讀，隨機讀和預讀取

順序讀是指順序的讀取磁碟上的block，隨機讀指訪問磁碟上的塊是不連續的，需要磁碟的磁頭的不斷轉動，在資料庫中，順序讀指的是根據索引的葉子節點資料就能順序的讀到所需的行資料，隨機讀是指需要訪問非叢集索引和叢集索引兩個共同才能讀到所需的資料

為了提高讀取的效能，innodb中引入了預讀取技術，通過一次的io請求將多個頁讀到緩衝池中，有兩種方式，隨機預讀取和線性預讀取，但預讀取在實際測試中效果不是很好，從innodb plugin 1.0.4後，隨機訪問只保留了 線性預讀取，並且加入了innodb_read_ahead_threshold

4. 輔助索引（非叢集索引）最佳化

因為輔助索引的頁節點包含有主鍵，但是輔助索引的頁中並不會包含完整的行資訊，innodb總是會先通過輔助索引看是否能找到所需的資料

建立表：

         create table t2 (a int not null,b varchar(20),primary key(a),key(b));

插入4行資料：

insert into t2 select 1,'hello';

insert into t2 select 2,'world';

insert into t2 select 3,'what';

insert into t2 select 4,'areyou';

然後使用 select * from t2; 查詢的結果是：

4	areyou
1	hello
3	what
2	world

這是因為輔助索引中包含了主鍵a的值，因此訪問b列上的輔助索引就能得到a的值，然後就得到了整張表中的資料，並且通常情況下，輔助索引能夠存放的資料比主鍵頁上存放的更多，因此最佳化器選擇了輔助索引

當 解釋這條 sql語句時：

explain select * from t2;得到

使用的是索引b，那麼如果想對a排序，可以對a進行order by或者強制使用 主鍵來得到結果

explain  select * from t2 order by a;    或者 select * from t2 force index (primary);

使用的是 主鍵

 explain select * from t2 force index (primary);但其實它未使用主鍵，沒有使用！

5. 聯合索引

聯合索引指的是可以在多個列上添加索引

alter table t add index idx_a_b(a,b);

聯合索引的好處是： 1）對於類似select * from t where a = 1 and b = 'aaaaaaaaaaaaaaaaaaaaaaaaa...'的查詢和select * from t where a = 1這樣的也可以使用（a，b）索引，但是對於b就不能使用了 2） 可以對第2索引值進行排序

實驗

建立表

create table buy_log (userid int unsigned not null,buy_date date);

插入資料

insert into buy_log values(1,'2009-01-01');insert into buy_log values(2,'2009-01-01');insert into buy_log values(3,'2009-01-01');insert into buy_log values(1,'2009-02-01');insert into buy_log values(2,'2009-02-01');insert into buy_log values(3,'2009-03-01');insert into buy_log values(1,'2009-04-01');

　　

添加索引：alter table buy_log add key (userid);   alter table buy_log add key (userid,buy_date);

如果只對 userid 查詢， explain select * from buy_log where userid = 2;

在我的電腦上使用的是userid_2： 我想 應該是os的關係，可能在win7中是這樣的，與上面的輔助索引類似

在Linux中（ubuntu）中得到的是書中所說的結果：

對於查詢explain select * from buy_log where userid = 1 order by buy_date limit 3;

使用的也是 userid_2;因為在這次最佳化中buy_date已經拍好序了