眼見為實(shí)，來瞧瞧MySQL中的隱藏列！

點(diǎn)擊關(guān)注公眾號，Java干貨及時送達(dá)

在介紹mysql的多版本并發(fā)控制MVCC的過程中，我們提到過mysql中存在一些隱藏列，例如行標(biāo)識、事務(wù)ID、回滾指針等，不知道大家是否和我一樣好奇過，要怎樣才能實(shí)際地看到這些隱藏列的值呢？

本文我們就來重點(diǎn)討論一下諸多隱藏列中的行標(biāo)識DB_ROW_ID，實(shí)際上，將行標(biāo)識稱為隱藏列并不準(zhǔn)確，因?yàn)樗⒉皇且粋€真實(shí)存在的列，DB_ROW_ID實(shí)際上是一個非空唯一列的別名。在撥開它的神秘面紗之前，我們看一下官方文檔的說明：

If a table has a PRIMARY KEY or UNIQUE NOT NULL index that consists of a single column that has an integer type, you can use _rowid to refer to the indexed column in SELECT statements

簡單翻譯一下，如果在表中存在主鍵或非空唯一索引，并且僅由一個整數(shù)類型的列構(gòu)成，那么就可以使用SELECT語句直接查詢_rowid，并且這個_rowid的值會引用該索引列的值。

著重看一下文檔中提到的幾個關(guān)鍵字，主鍵、唯一索引、非空、單獨(dú)一列、數(shù)值類型，接下來我們就要從這些角度入手，探究一下神秘的隱藏字段_rowid。

1、存在主鍵

先看設(shè)置了主鍵且是數(shù)值類型的情況，使用下面的語句建表：

CREATE?TABLE?`table1`?(
??`id`?bigint(20)?NOT?NULL?PRIMARY?KEY?,
??`name`?varchar(32)?DEFAULT?NULL
)?ENGINE=InnoDB;

插入三條測試數(shù)據(jù)后，執(zhí)行下面的查詢語句，在select查詢語句中直接查詢_rowid：

select?*,_rowid?from?table1

查看執(zhí)行結(jié)果，_rowid可以被正常查詢：

可以看到在設(shè)置了主鍵，并且主鍵字段是數(shù)值類型的情況下，_rowid直接引用了主鍵字段的值。對于這種可以被select語句查詢到的的情況，可以將其稱為顯式的rowid。

回顧一下前面提到的文檔中的幾個關(guān)鍵字，再考慮其他情況。由于主鍵必定是非空字段，下面來看一下主鍵是非數(shù)值類型字段的情況，建表如下：

CREATE?TABLE?`table2`?(
??`id`?varchar(20)?NOT?NULL?PRIMARY?KEY?,
??`name`?varchar(32)?DEFAULT?NULL
)?ENGINE=InnoDB;

在table2執(zhí)行上面相同的查詢，結(jié)果報錯無法查詢_rowid，也就證明了如果主鍵字段是非數(shù)值類型，那么將無法直接查詢_rowid。

2、無主鍵，存在唯一索引

上面對兩種類型的主鍵進(jìn)行了測試后，接下來我們看一下當(dāng)表中沒有主鍵、但存在唯一索引的情況。首先測試非空唯一索引加在數(shù)值類型字段的情況，建表如下：

CREATE?TABLE?`table3`?(
??`id`?bigint(20)?NOT?NULL?UNIQUE?KEY,
??`name`?varchar(32)
)?ENGINE=InnoDB;

查詢可以正常執(zhí)行，并且_rowid引用了唯一索引所在列的值：

唯一索引與主鍵不同的是，唯一索引所在的字段可以為NULL。在上面的table3中，在唯一索引所在的列上添加了NOT NULL非空約束，如果我們把這個非空約束刪除掉，還能顯式地查詢到_rowid嗎？下面再創(chuàng)建一個表，不同是在唯一索引所在的列上，不添加非空約束：

CREATE?TABLE?`table4`?(
??`id`?bigint(20)?UNIQUE?KEY,
??`name`?varchar(32)
)?ENGINE=InnoDB;

執(zhí)行查詢語句，在這種情況下，無法顯式地查詢到_rowid：

和主鍵類似的，我們再對唯一索引被加在非數(shù)值類型的字段的情況進(jìn)行測試。下面在建表時將唯一索引添加在字符類型的字段上，并添加非空約束：

CREATE?TABLE?`table5`?(
??`id`?bigint(20),
??`name`?varchar(32)?NOT?NULL?UNIQUE?KEY
)?ENGINE=InnoDB;

同樣無法顯示的查詢到_rowid：

針對上面三種情況的測試結(jié)果，可以得出結(jié)論，當(dāng)沒有主鍵、但存在唯一索引的情況下，只有該唯一索引被添加在數(shù)值類型的字段上，且該字段添加了非空約束時，才能夠顯式地查詢到_rowid，并且_rowid引用了這個唯一索引字段的值。

3、存在聯(lián)合主鍵或聯(lián)合唯一索引

在上面的測試中，我們都是將主鍵或唯一索引作用在單獨(dú)的一列上，那么如果使用了聯(lián)合主鍵或聯(lián)合唯一索引時，結(jié)果會如何呢？還是先看一下官方文檔中的說明：

_rowid refers to the PRIMARY KEY column if there is a PRIMARY KEY consisting of a single integer column. If there is a PRIMARY KEY but it does not consist of a single integer column, _rowid cannot be used.

簡單來說就是，如果主鍵存在、且僅由數(shù)值類型的一列構(gòu)成，那么_rowid的值會引用主鍵。如果主鍵是由多列構(gòu)成，那么_rowid將不可用。

根據(jù)這一描述，我們測試一下聯(lián)合主鍵的情況，下面將兩列數(shù)值類型字段作為聯(lián)合主鍵建表：

CREATE?TABLE?`table6`?(
??`id`?bigint(20)?NOT?NULL,
??`no`?bigint(20)?NOT?NULL,
??`name`?varchar(32),
??PRIMARY?KEY(`id`,`no`)
)?ENGINE=InnoDB;

執(zhí)行結(jié)果無法顯示的查詢到_rowid：

同樣，這一理論也可以作用于唯一索引，如果非空唯一索引不是由單獨(dú)一列構(gòu)成，那么也無法直接查詢得到_rowid。這一測試過程省略，有興趣的小伙伴可以自己動手試試。

4、存在多個唯一索引

在mysql中，每張表只能存在一個主鍵，但是可以存在多個唯一索引。那么如果同時存在多個符合規(guī)則的唯一索引，會引用哪個作為_rowid的值呢？老規(guī)矩，還是看官方文檔的解答：

Otherwise, _rowid refers to the column in the first UNIQUE NOT NULL index if that index consists of a single integer column. If the first UNIQUE NOT NULL index does not consist of a single integer column, _rowid cannot be used.

簡單翻譯一下，如果表中的第一個非空唯一索引僅由一個整數(shù)類型字段構(gòu)成，那么_rowid會引用這個字段的值。否則，如果第一個非空唯一索引不滿足這種情況，那么_rowid將不可用。

在下面的表中，創(chuàng)建兩個都符合規(guī)則的唯一索引：

CREATE?TABLE?`table8_2`?(
??`id`?bigint(20)?NOT?NULL,
??`no`?bigint(20)?NOT?NULL,
??`name`?varchar(32),
??UNIQUE?KEY(no),
??UNIQUE?KEY(id)
)?ENGINE=InnoDB;

看一下執(zhí)行查詢語句的結(jié)果：

可以看到_rowid的值與no這一列的值相同，證明了_rowid會嚴(yán)格地選取第一個創(chuàng)建的唯一索引作為它的引用。

那么，如果表中創(chuàng)建的第一個唯一索引不符合_rowid的引用規(guī)則，第二個唯一索引滿足規(guī)則，這種情況下，_rowid可以被顯示地查詢嗎？針對這種情況我們建表如下，表中的第一個索引是聯(lián)合唯一索引，第二個索引才是單列的唯一索引情況，再來進(jìn)行一下測試：

CREATE?TABLE?`table9`?(
??`id`?bigint(20)?NOT?NULL,
??`no`?bigint(20)?NOT?NULL,
??`name`?varchar(32),
??UNIQUE?KEY?`index1`(`id`,`no`),
??UNIQUE?KEY?`index2`(`id`)
)?ENGINE=InnoDB;

進(jìn)行查詢，可以看到雖然存在一個單列的非空唯一索引，但是因?yàn)轫樞蜻x取的第一個不滿足要求，因此仍然不能直接查詢_rowid：

如果將上面創(chuàng)建唯一索引的語句順序調(diào)換，那么將可以正常顯式的查詢到_rowid。

5、同時存在主鍵與唯一索引

從上面的例子中，可以看到唯一索引的定義順序會決定將哪一個索引應(yīng)用_rowid，那么當(dāng)同時存在主鍵和唯一索引時，定義順序會對其引用造成影響嗎？

按照下面的語句創(chuàng)建兩個表，只有創(chuàng)建主鍵和唯一索引的順序不同：

CREATE?TABLE?`table11`?(
??`id`?bigint(20)?NOT?NULL,
??`no`?bigint(20)?NOT?NULL,
??PRIMARY?KEY(id),
??UNIQUE?KEY(no)
)?ENGINE=InnoDB;

CREATE?TABLE?`table12`?(
??`id`?bigint(20)?NOT?NULL,
??`no`?bigint(20)?NOT?NULL,
??UNIQUE?KEY(id),
??PRIMARY?KEY(no)
)?ENGINE=InnoDB;

查看運(yùn)行結(jié)果：

可以得出結(jié)論，當(dāng)同時存在符合條件的主鍵和唯一索引時，無論創(chuàng)建順序如何，_rowid都會優(yōu)先引用主鍵字段的值。

6、無符合條件的主鍵與唯一索引

上面，我們把能夠直接通過select語句查詢到的稱為顯式的_rowid，在其他情況下雖然_rowid不能被顯式查詢，但是它也是一直存在的，這種情況我們可以將其稱為隱式的_rowid。

實(shí)際上，innoDB在沒有默認(rèn)主鍵的情況下會生成一個6字節(jié)長度的無符號數(shù)作為自動增長的_rowid，因此最大為2^48-1，到達(dá)最大值后會從0開始計算。下面，我們創(chuàng)建一個沒有主鍵與唯一索引的表，在這張表的基礎(chǔ)上，探究一下隱式的_rowid。

CREATE?TABLE?`table10`?(
??`id`?bigint(20),
??`name`?varchar(32)
)?ENGINE=InnoDB;

首先，我們需要先查找到mysql的進(jìn)程pid：

ps?-ef?|?grep?mysqld

可以看到，mysql的進(jìn)程pid是2068：

在開始動手前，還需要做一點(diǎn)鋪墊， 在innoDB中其實(shí)維護(hù)了一個全局變量dictsys.row_id，沒有定義主鍵的表都會共享使用這個row_id，在插入數(shù)據(jù)時會把這個全局row_id當(dāng)作自己的主鍵，然后再將這個全局變量加 1。

接下來我們需要用到gdb調(diào)試的相關(guān)技術(shù)，gdb是一個在Linux下的調(diào)試工具，可以用來調(diào)試可執(zhí)行文件。在服務(wù)器上，先通過yum install gdb安裝，安裝完成后，通過下面的gdb命令 把 row_id 修改為 1：

gdb?-p?2068?-ex?'p?dict_sys->row_id=1'?-batch

命令執(zhí)行結(jié)果：

在空表中插入3行數(shù)據(jù)：

INSERT?INTO?table10?VALUES?(100000001,?'Hydra');
INSERT?INTO?table10?VALUES?(100000002,?'Trunks');
INSERT?INTO?table10?VALUES?(100000003,?'Susan');

查看表中的數(shù)據(jù)，此時對應(yīng)的_rowid理論上是1~3：

然后通過gdb命令把row_id改為最大值2^48，此時已超過dictsys.row_id最大值：

gdb?-p?2068?-ex?'p?dict_sys->row_id=281474976710656'?-batch

命令執(zhí)行結(jié)果：

再向表中插入三條數(shù)據(jù)：

INSERT?INTO?table10?VALUES?(100000004,?'King');
INSERT?INTO?table10?VALUES?(100000005,?'Queen');
INSERT?INTO?table10?VALUES?(100000006,?'Jack');

查看表中的全部數(shù)據(jù)，可以看到第一次插入的三條數(shù)據(jù)中，有兩條數(shù)據(jù)被覆蓋了：

為什么會出現(xiàn)數(shù)據(jù)覆蓋的情況呢，我們對這一結(jié)果進(jìn)行分析。首先，在第一次插入數(shù)據(jù)前_rowid為1，插入的三條數(shù)據(jù)對應(yīng)的_rowid為1、2、3。如下圖所示：

當(dāng)手動設(shè)置_rowid超過最大值后，下一次插入數(shù)據(jù)時，插入的_rowid重新從0開始，因此第二次插入的三條數(shù)據(jù)的_rowid應(yīng)該為0、1、2。這時準(zhǔn)備被插入的數(shù)據(jù)如下所示：

當(dāng)出現(xiàn)相同_rowid的情況下，新插入的數(shù)據(jù)會根據(jù)_rowid覆蓋掉原有的數(shù)據(jù)，過程如圖所示：

所以當(dāng)表中的主鍵或唯一索引不滿足我們前面提到的要求時，innoDB使用的隱式的_rowid是存在一定風(fēng)險的，雖然說2^48這個值很大，但還是有可能被用盡的，當(dāng)_rowid用盡后，之前的記錄就會被覆蓋。從這一角度也可以提醒大家，在建表時一定要創(chuàng)建主鍵，否則就有可能發(fā)生數(shù)據(jù)的覆蓋。

本文基于mysql 5.7.31 進(jìn)行測試

官方文檔：https://dev.mysql.com/doc/refman/5.7/en/create-index.html