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

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

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

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

關(guān)于B+樹

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

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

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

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

c、非葉子結(jié)點(diǎn)相當(dāng)于是葉子結(jié)點(diǎn)的索引(稀疏索引)，葉子結(jié)點(diǎn)相當(dāng)于是存儲(chǔ)(關(guān)鍵字)數(shù)據(jù)的數(shù)據(jù)層。

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

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

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

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

3、如果表使用自增主鍵

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

4、如果使用非自增主鍵（如果身份證號(hào)或?qū)W號(hào)等）

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

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

a、使用自增列(INT/BIGINT類型)做主鍵，這時(shí)候?qū)懭腠樞蚴亲栽龅模虰+數(shù)葉子節(jié)點(diǎn)分裂順序一致；

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

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

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

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

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

雙1：innodb_flush_log_at_trx_commit=1 and  sync_binlog=1

sync_binlog=n，當(dāng)每次提交N次事務(wù)提交之后，MySQL將進(jìn)行一次fsny之類的磁盤同步指令來將binlog_cache中的數(shù)據(jù)強(qiáng)制寫入磁盤。  在MySQL中sync_binlog=0，也就是不做任何強(qiáng)制性的磁盤刷新指令，這時(shí)候性能是最好的，但是風(fēng)險(xiǎn)也是最大的。因?yàn)橐坏┫到y(tǒng)crash，在binlog_cache中的所有binlog信息都會(huì)丟失。

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

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

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

3、有幾種binlog格式，區(qū)別是什么 ？

Row，Statement，Mixed=Row+Statement

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

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

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

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

優(yōu)點(diǎn)：在 statement 模式下，首先就是解決了 row 模式的缺點(diǎn)，不需要記錄每一行數(shù)據(jù)的變化，減少了 bin-log 日志量，節(jié)省 I/O 以及存儲(chǔ)資源，提高性能。因?yàn)樗恍枰涗浽?master 上所執(zhí)行的語句的細(xì)節(jié)，以及執(zhí)行語句時(shí)候的上下文的信息。

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

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

注意：

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

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

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