【Mysql最佳化】聚簇索引與非聚簇索引概念

標籤：mysq   特點   觀察   uniq   可謂   c99   uid   資料頁   物理   

首先明白兩句話: 

 　　innodb的次索引指向對主鍵的引用  (聚簇索引)

　　myisam的次索引和主索引   都指向物理行 (非聚簇索引)

 

 

　　聚簇索引是對磁碟上實際資料重新組織以按指定的一個或多個列的值排序的演算法。特點是儲存資料的順序和索引順序一致。一般情況下主鍵會預設建立聚簇索引，且一張表只允許存在一個聚簇索引（理由：資料一旦儲存，順序只能有一種）。

在《資料庫原理》一書中是這麼解釋聚簇索引和非聚簇索引的區別的：
　　聚簇索引的葉子節點就是資料節點，而非聚簇索引的葉子節點仍然是索引節點，只不過有指向對應資料區塊的指標。

 

INNODB和MYISAM的主鍵索引與二級索引的對比：

　　也就是InnoDB的主索引的節點與資料放在一起，次索引的節點存放的是主鍵的位置。

　　　　　　myisam的主索引和次索引都指向該資料在磁碟的位置。

 

InnoDB的的二級索引的葉子節點存放的是KEY欄位加主索引值。因此，通過二級索引查詢首先查到是主索引值，然後InnoDB再根據查到的主索引值通過主鍵索引找到相應的資料區塊。
而MyISAM的二級索引葉子節點存放的還是列值與行號的組合，葉子節點中儲存的是資料的物理地址。所以可以看出MYISAM的主鍵索引和二級索引沒有任何區別，主鍵索引僅僅只是一個叫做PRIMARY的唯一、非空的索引，且MYISAM引擎中可以不設主鍵

 

 

 

也可以用下面這幅圖理解:

 

首先是myisam的索引主次索引都指向物理行:

 

 

 

InnoDB的主索引葉子節點是主鍵和資料，次索引指向主鍵

 

 

 

 

 

 

 

innodb的主索引檔案上 直接存放該行資料,稱為聚簇索引,次索引指向對主鍵的引用

myisam中, 主索引和次索引,都指向物理行(磁碟位置).

 

注意: innodb來說,

　　1: 主鍵索引 既儲存索引值,又在葉子中儲存行的資料

　　2: 如果沒有主鍵, 則會Unique key做主鍵

　　3: 如果沒有unique,則系統產生一個內部的rowid做主鍵.

　　4: 像innodb中,主鍵的索引結構中,既儲存了主索引值,又儲存了行資料,這種結構稱為”聚簇索引”

 

 

 

1、聚簇索引
a) 一個索引項目直接對應實際資料記錄的儲存頁，可謂“直達”
b) 主鍵預設使用它
c) 索引項目的排序和資料行的儲存排序完全一致，利用這一點，想修改資料的儲存順序，可以通過改變主鍵的方法（撤銷原有主鍵，另找也能滿足主鍵要求的一個欄位或一組欄位，重建主鍵）
d) 一個表只能有一個聚簇索引（理由：資料一旦儲存，順序只能有一種）

2、非聚簇索引
a) 不能“直達”，可能鏈式地訪問多級頁表後，才能定位到資料頁
b) 一個表可以有多個非聚簇索引

-------------------------------------聚簇索引優勢劣勢；-----------------------------------

　　優勢: 根據主鍵查詢條目比較少時,不用回行(資料就在主鍵節點下)

　　劣勢: 如果碰到不規則資料插入時,造成頻繁的頁分裂.

 

聚簇索引的頁分裂過程

理解:  原來索引如下

 

　　此時插入一個8，需要將13,16,17移動之後插入8

 

對於myisam引擎：只需要儲存資料之後移動索引節點，
對於innoDb的聚簇索引：插入資料之後需要移動13,16,17.但是因為這三個節點上面有資料，也就造成了額外的開銷。相當於三個節點搬家的同時帶著資料搬家。　　




也可以用理解:

 

總結:

　　1: innodb的buffer_page 很強大.

　　2: 聚簇索引的主索引值,應盡量是連續增長的值,而不是要是隨機值,

　　　　　　(不要用隨機字串或UUID)

　　　　否則會造成大量的頁分裂與頁移動.

　　為了看出效果可以用Java向資料庫中按順序插入1000條資料與亂序插入一千條資料。看執行的時間即可看出效果。







如:Innodb_pages_written代表已經寫入的頁數，可以按順序插入1000條資料與亂序插入一千條資料觀察增長的變化量。

mysql> show status like ‘%page_%‘;+----------------------------------+-------+| Variable_name                    | Value |+----------------------------------+-------+| Innodb_buffer_pool_pages_data    | 256   || Innodb_buffer_pool_pages_dirty   | 0     || Innodb_buffer_pool_pages_flushed | 749   || Innodb_buffer_pool_pages_free    | 243   || Innodb_buffer_pool_pages_misc    | 13    || Innodb_buffer_pool_pages_total   | 512   || Innodb_dblwr_pages_written       | 628   || Innodb_page_size                 | 16384 || Innodb_pages_created             | 67    || Innodb_pages_read                | 736   || Innodb_pages_written             | 749   || Tc_log_max_pages_used            | 0     || Tc_log_page_size                 | 0     || Tc_log_page_waits                | 0     |+----------------------------------+-------+14 rows in set (0.00 sec)

 

 

　　聚簇索引與非聚簇索引的區別參考:http://www.cnblogs.com/qlqwjy/p/7770580.html

 

【Mysql最佳化】聚簇索引與非聚簇索引概念