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

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

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

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

關(guān)于B+樹(shù)

（圖片來(lái)源于網(wǎng)上）

B+ 樹(shù)的特點(diǎn)：

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

b、不可能在非葉子結點(diǎn)命中;

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

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

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

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

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

3、如果表使用自增主鍵

那么每次插入新的記錄，記錄就會(huì )順序添加到當前索引節點(diǎn)的后續位置，當一頁(yè)寫(xiě)滿(mǎn)，就會(huì )自動(dòng)開(kāi)辟一個(gè)新的頁(yè)

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

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

總結：如果InnoDB表的數據寫(xiě)入順序能和B+樹(shù)索引的葉子節點(diǎn)順序一致的話(huà)，這時(shí)候存取效率是最高的，也就是下面這幾種情況的存取效率最高：

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

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

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

一下是來(lái)自《高性能MySQL》中的原話(huà)

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

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

雙1：innodb_flush_log_at_trx_commit=1 and  sync_binlog=1

sync_binlog=n，當每次提交N次事務(wù)提交之后，MySQL將進(jìn)行一次fsny之類(lèi)的磁盤(pán)同步指令來(lái)將binlog_cache中的數據強制寫(xiě)入磁盤(pán)。  在MySQL中sync_binlog=0，也就是不做任何強制性的磁盤(pán)刷新指令，這時(shí)候性能是最好的，但是風(fēng)險也是最大的。因為一旦系統crash，在binlog_cache中的所有binlog信息都會(huì )丟失。

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

innodb_flush_log_at_trx_commit=2 是不寫(xiě)入硬盤(pán)而是寫(xiě)入系統緩存，日志仍然會(huì )每秒flush到硬盤(pán)，所以一般不會(huì )丟失超過(guò)1-2秒的更新，系統掛了時(shí)才可能丟數據

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

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

Row，Statement，Mixed=Row+Statement

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

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

缺點(diǎn)：在 row 模式下，所有的執行的語(yǔ)句當記錄到日志中的時(shí)候，都將以每行記錄的修改來(lái)記錄，這樣可能會(huì )產(chǎn)生大量的日志內容。

2. Statement
每一條會(huì )修改數據的 SQL 都會(huì )記錄到 master 的 bin-log 中。slave 在復制的時(shí)候 SQL 進(jìn)程會(huì )解析成和原來(lái) master 端執行過(guò)的相同的 SQL 再次執行。

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

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

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

注意：

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

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