共計(jì) 3453 個字符，預(yù)計(jì)需要花費(fèi) 9 分鐘才能閱讀完成。

本篇內(nèi)容介紹了“mysql 為什么 InnoDB 表最好要有自增列做主鍵”的有關(guān)知識，在實(shí)際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓丸趣 TV 小編帶領(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)于是存儲(關(guān)鍵字) 數(shù)據(jù)的數(shù)據(jù)層。

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

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

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

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

3、如果表使用自增主鍵

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

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

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

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

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

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

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

一下是來自《高性能 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)制性的磁盤刷新指令，這時候性能是最好的，但是風(fēng)險也是最大的。因?yàn)橐坏┫到y(tǒng) crash，在 binlog_cache 中的所有 binlog 信息都會丟失。

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

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

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

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

Row，Statement，Mixed=Row+Statement

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

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

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

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

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

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

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

注意：

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

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

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