首頁  >  文章  >  資料庫  >  MySQL為什麼主鍵自增

MySQL為什麼主鍵自增

(*-*)浩
(*-*)浩原創
2019-05-07 15:27:196799瀏覽

mysql主鍵自增的原因:1、資料記錄本身被存於主索引的葉子節點上;2、mysql會根據其主鍵將其插入適當的節點和位置;3、表使用自增主鍵,在每次插入新的記錄時,記錄會順序新增到目前索引節點的後續位置。

MySQL為什麼主鍵自增

MySQL之所以要使用自增主鍵,是因為InnoDB表與它使用時十分方便,效率明顯提升。

推薦課程:MySQL教學

InnoDB引擎表的特性

1、InnoDB引擎表是基於B 樹的索引組織表(IOT)

關於B 樹

MySQL為什麼主鍵自增

B 樹的特徵:

所有關鍵字都出現在葉子結點的鍊錶中(稠密索引),且鍊錶中的關鍵字恰好是有序的;

不可能在非葉子結點命中;

非葉子結點相當於是葉子結點的索引(稀疏索引),葉子結點相當於是儲存(關鍵字)資料的資料層;

2、如果我們定義了主鍵(PRIMARY KEY),那麼InnoDB會選擇主鍵作為聚集索引、如果沒有明確定義主鍵,則InnoDB會選擇第一個不包含有NULL值的唯一索引作為主鍵索引、如果也沒有這樣的唯一索引,則InnoDB會選擇內建6位元組長的ROWID作為隱含的聚集索引(ROWID隨著行記錄的寫入而主鍵遞增,這個ROWID不像ORACLE的ROWID那麼可引用,是隱含的)。

3、資料記錄本身被存於主索引(一顆B Tree)的葉子節點上。這就要求同一個葉子節點內(大小為一個內存頁或磁碟頁)的各條資料記錄按主鍵順序存放,因此每當有一條新的記錄插入時,MySQL會根據其主鍵將其插入適當的節點和位置,如果頁面達到裝載因子(InnoDB預設為15/16),則開闢一個新的頁(節點)

4、如果表使用自增主鍵,那麼每次插入新的記錄,記錄就會順序加入到目前索引節點的後續位置,當一頁寫滿,就會自動開啟一個新的頁

#5、如果使用非自增主鍵(如果身分證號或學號等) ,由於每次插入主鍵的值近似於隨機,因此每次新紀錄都要被插到現有索引頁得中間某個位置,此時MySQL不得不為了將新記錄插到合適位置而移動數據,甚至目標頁面可能已經被回寫到磁碟上而從快取中清掉,此時又要從磁碟上讀回來,這增加了很多開銷,同時頻繁的移動、分頁操作造成了大量的碎片,得到了不夠緊湊的索引結構,後續不得不透過OPTIMIZE TABLE來重建表並優化填充頁面。

綜上總結,如果InnoDB表的資料寫入順序能和B 樹索引的葉子節點順序一致的話,這時候存取效率是最高的,也就是下面這幾種情況的​​存取效率最高:

使用自增列(INT/BIGINT類型)做主鍵,這時候寫入順序是自增的,和B 數葉子節點分裂順序一致;

該表不指定自增列做主鍵,同時也沒有可以被選為主鍵的唯一索引(上面的條件),這時候InnoDB會選擇內建的ROWID作為主鍵,寫入順序和ROWID成長順序一致;

#除此之外,如果一個InnoDB表又沒有顯示主鍵,又有可以被選擇為主鍵的唯一索引,但該唯一索引可能不是遞增關係時(例如字串、UUID、多字段聯合唯一索引的情況) ,該表的存取效率就會比較差。

以上是MySQL為什麼主鍵自增的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn