mysql為什么InnoDB表最好要有自增列做主鍵

本篇內容介紹了“mysql為什么InnoDB表最好要有自增列做主鍵”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

1、為什么InnoDB表最好要有自增列做主鍵？

InnoDB引擎表是基于B+樹的索引組織表(IOT)

關于B+樹

（圖片來源于網上）

B+ 樹的特點：

a、所有關鍵字都出現在葉子結點的鏈表中(稠密索引)，且鏈表中的關鍵字恰好是有序的;

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

c、非葉子結點相當于是葉子結點的索引(稀疏索引)，葉子結點相當于是存儲(關鍵字)數據的數據層。

1、如果我們定義了主鍵(PRIMARY KEY)

那么InnoDB會選擇主鍵作為聚集索引、如果沒有顯式定義主鍵，則InnoDB會選擇第一個不包含有NULL值的唯一索引作為主鍵索引、如果也沒有這樣的唯一索引，則InnoDB會選擇內置6字節長的ROWID作為隱含的聚集索引(ROWID隨著行記錄的寫入而主鍵遞增，這個ROWID不像ORACLE的ROWID那樣可引用，是隱含的)。

2、數據記錄本身被存于主索引（一顆B+Tree）的葉子節點上

這就要求同一個葉子節點內（大小為一個內存頁或磁盤頁）的各條數據記錄按主鍵順序存放，因此每當有一條新的記錄插入時，MySQL會根據其主鍵將其插入適當的節點和位置，如果頁面達到裝載因子（InnoDB默認為15/16），則開辟一個新的頁（節點）

3、如果表使用自增主鍵

那么每次插入新的記錄，記錄就會順序添加到當前索引節點的后續位置，當一頁寫滿，就會自動開辟一個新的頁

4、如果使用非自增主鍵（如果身份證號或學號等）

由于每次插入主鍵的值近似于隨機，因此每次新紀錄都要被插到現有索引頁得中間某個位置，此時MySQL不得不為了將新記錄插到合適位置而移動數據，甚至目標頁面可能已經被回寫到磁盤上而從緩存中清掉，此時又要從磁盤上讀回來，這增加了很多開銷，同時頻繁的移動、分頁操作造成了大量的碎片，得到了不夠緊湊的索引結構，后續不得不通過OPTIMIZE TABLE來重建表并優化填充頁面。

總結：如果InnoDB表的數據寫入順序能和B+樹索引的葉子節點順序一致的話，這時候存取效率是最高的，也就是下面這幾種情況的存取效率最高：

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

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

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

一下是來自《高性能MySQL》中的原話

引用鏈接：https://segmentfault.com/q/1010000003856705

2、為什么需要設置雙1才能保證主從數據的一致性？

雙1：innodb_flush_log_at_trx_commit=1 and  sync_binlog=1

sync_binlog=n，當每次提交N次事務提交之后，MySQL將進行一次fsny之類的磁盤同步指令來將binlog_cache中的數據強制寫入磁盤。  在MySQL中sync_binlog=0，也就是不做任何強制性的磁盤刷新指令，這時候性能是最好的，但是風險也是最大的。因為一旦系統crash，在binlog_cache中的所有binlog信息都會丟失。

innodb_flush_log_at_trx_commit=1 是每一次事務提交或事務的指令都需要把日志寫入（flush）硬盤，這是很費時的，在使用電池供電緩存（Battery backed up cache）時。

innodb_flush_log_at_trx_commit=2 是不寫入硬盤而是寫入系統緩存，日志仍然會每秒flush到硬盤，所以一般不會丟失超過1-2秒的更新，系統掛了時才可能丟數據

innodb_flush_log_at_trx_commit=0 會更快一些，安全性比較差，即使mysql掛了可能會丟失事務的數據

3、有幾種binlog格式，區別是什么 ？

Row，Statement，Mixed=Row+Statement

1. Row
日志中會記錄成每一行數據被修改的形式，然后在 slave 端再對相同的數據進行修改。

優點：在 row 模式下，bin-log 中可以不記錄執行的 SQL 語句的上下文相關的信息，僅僅只需要記錄那一條記錄被修改了，修改成什么樣了。所以 row 的日志內容會非常清楚的記錄下每一行數據修改的細節，非常容易理解。而且不會出現某些特定情況下的存儲過程或 function ，以及 trigger 的調用和觸發無法被正確復制的問題。

缺點：在 row 模式下，所有的執行的語句當記錄到日志中的時候，都將以每行記錄的修改來記錄，這樣可能會產生大量的日志內容。

2. Statement
每一條會修改數據的 SQL 都會記錄到 master 的 bin-log 中。slave 在復制的時候 SQL 進程會解析成和原來 master 端執行過的相同的 SQL 再次執行。

優點：在 statement 模式下，首先就是解決了 row 模式的缺點，不需要記錄每一行數據的變化，減少了 bin-log 日志量，節省 I/O 以及存儲資源，提高性能。因為他只需要記錄在 master 上所執行的語句的細節，以及執行語句時候的上下文的信息。

缺點：在 statement 模式下，由于他是記錄的執行語句，所以，為了讓這些語句在 slave 端也能正確執行，那么他還必須記錄每條語句在執行的時候的一些相關信息，也就是上下文信息，以保證所有語句在 slave 端杯執行的時候能夠得到和在 master 端執行時候相同的結果。另外就是，由于 MySQL 現在發展比較快，很多的新功能不斷的加入，使 MySQL 的復制遇到了不小的挑戰，自然復制的時候涉及到越復雜的內容，bug 也就越容易出現。在 statement 中，目前已經發現的就有不少情況會造成 MySQL 的復制出現問題，主要是修改數據的時候使用了某些特定的函數或者功能的時候會出現，比如：sleep() 函數在有些版本中就不能被正確復制，在存儲過程中使用了 last_insert_id() 函數，可能會使 slave 和 master 上得到不一致的 id 等等。由于 row 是基于每一行來記錄的變化，所以不會出現類似的問題。

3. Mixed
從官方文檔中看到，之前的 MySQL 一直都只有基于 statement 的復制模式，直到 5.1.5 版本的 MySQL 才開始支持 row 復制。從 5.0 開始，MySQL 的復制已經解決了大量老版本中出現的無法正確復制的問題。但是由于存儲過程的出現，給 MySQL Replication 又帶來了更大的新挑戰。另外，看到官方文檔說，從 5.1.8 版本開始，MySQL 提供了除 Statement 和 Row 之外的第三種復制模式：Mixed，實際上就是前兩種模式的結合。在 Mixed 模式下，MySQL 會根據執行的每一條具體的 SQL 語句來區分對待記錄的日志形式，也就是在 statement 和 row 之間選擇一種。新版本中的 statment 還是和以前一樣，僅僅記錄執行的語句。而新版本的 MySQL 中對 row 模式也被做了優化，并不是所有的修改都會以 row 模式來記錄，比如遇到表結構變更的時候就會以 statement 模式來記錄，如果 SQL 語句確實就是 update 或者 delete 等修改數據的語句，那么還是會記錄所有行的變更。

注意：

條件1：當binlog format設置為mixed時，普通復制不會有問題，但是級聯復制在特殊情況下會binlog丟失.
條件2：當出現大量數據（400W左右）掃描的更新，刪除，插入的時候，且有不確定dml語句（如：delete from table where data<’N’ limit ）的時候.
當條件1 和 條件2 同時滿足時，會導致主從復制數據丟失問題的發生.只能設置binlog_format=Row

引用：http://tshare365.com/archives/2054.html

“mysql為什么InnoDB表最好要有自增列做主鍵”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！