在介紹mysql的多版本併發控制
MVCC
的過程中,我們提到過mysql中存在一些隱藏列,例如
行標識
、
事務ID
、
回滾指標
等,不知道大家是否和我一樣好奇過,要怎樣才能實際地看到這些隱藏列的值呢?
本文我們就來重點討論一下諸多隱藏列中的行標識
DB_ROW_ID
,實際上,將行標識稱為隱藏
列
並不準確,因為它並不是一個真實存在的列,
DB_ROW_ID
實際上是一個非空唯一列的
別名
。在撥開它的神秘面紗之前,我們看一下官方文件的說明:
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
簡單翻譯一下,如果在表中存在主鍵或非空唯一索引,並且僅由一個整數型別的列構成,那麼就可以使用
SELECT
語句直接查詢
_rowid
,並且這個
_rowid
的值會引用該索引列的值。
著重看一下文件中提到的幾個關鍵字,
主鍵
、
唯一索引
、
非空
、
單獨一列
、
數值型別
,接下來我們就要從這些角度入手,探究一下神秘的隱藏欄位
_rowid
。
1、存在主鍵
先看設定了主鍵且是數值型別的情況,使用下面的語句建表:
CREATE TABLE `table1` ( `id` bigint(20) NOT NULL PRIMARY KEY , `name` varchar(32) DEFAULT NULL) ENGINE=InnoDB;
插入三條測試資料後,執行下面的查詢語句,在
select
查詢語句中直接查詢
_rowid
:
select *,_rowid from table1
檢視執行結果,
_rowid
可以被正常查詢:
可以看到在設定了主鍵,並且主鍵欄位是數值型別的情況下,
_rowid
直接引用了主鍵欄位的值。對於這種可以被
select
語句查詢到的的情況,可以將其稱為
顯式
的
rowid
。
回顧一下前面提到的文件中的幾個關鍵字,再考慮其他情況。由於主鍵必定是非空欄位,下面來看一下主鍵是非數值型別欄位的情況,建表如下:
CREATE TABLE `table2` ( `id` varchar(20) NOT NULL PRIMARY KEY , `name` varchar(32) DEFAULT NULL) ENGINE=InnoDB;
在
table2
執行上面相同的查詢,結果報錯無法查詢
_rowid
,也就證明了如果主鍵欄位是非數值型別,那麼將無法直接查詢
_rowid
。
2、無主鍵,存在唯一索引
上面對兩種型別的主鍵進行了測試後,接下來我們看一下當表中沒有主鍵、但存在唯一索引的情況。首先測試非空唯一索引加在數值型別欄位的情況,建表如下:
CREATE TABLE `table3` ( `id` bigint(20) NOT NULL UNIQUE KEY, `name` varchar(32)) ENGINE=InnoDB;
查詢可以正常執行,並且
_rowid
引用了唯一索引所在列的值:
唯一索引與主鍵不同的是,唯一索引所在的欄位可以為
NULL
。在上面的
table3
中,在唯一索引所在的列上添加了
NOT NULL
非空約束,如果我們把這個非空約束刪除掉,還能顯式地查詢到
_rowid
嗎?下面再建立一個表,不同是在唯一索引所在的列上,不新增非空約束:
CREATE TABLE `table4` ( `id` bigint(20) UNIQUE KEY, `name` varchar(32)) ENGINE=InnoDB;
執行查詢語句,在這種情況下,無法顯式地查詢到
_rowid
:
和主鍵類似的,我們再對唯一索引被加在非數值型別的欄位的情況進行測試。下面在建表時將唯一索引新增在字元型別的欄位上,並新增非空約束:
CREATE TABLE `table5` ( `id` bigint(20), `name` varchar(32) NOT NULL UNIQUE KEY) ENGINE=InnoDB;
同樣無法顯示的查詢到
_rowid
:
針對上面三種情況的測試結果,可以得出結論,當沒有主鍵、但存在唯一索引的情況下,只有該唯一索引被新增在數值型別的欄位上,且該欄位添加了非空約束時,才能夠顯式地查詢到
_rowid
,並且
_rowid
引用了這個唯一索引欄位的值。
3、存在聯合主鍵或聯合唯一索引
在上面的測試中,我們都是將主鍵或唯一索引作用在單獨的一列上,那麼如果使用了聯合主鍵或聯合唯一索引時,結果會如何呢?還是先看一下官方文件中的說明:
_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。
簡單來說就是,如果主鍵存在、且僅由數值型別的一列構成,那麼
_rowid
的值會引用主鍵。如果主鍵是由多列構成,那麼
_rowid
將不可用。
根據這一描述,我們測試一下聯合主鍵的情況,下面將兩列數值型別欄位作為聯合主鍵建表:
CREATE TABLE `table6` ( `id` bigint(20) NOT NULL, `no` bigint(20) NOT NULL, `name` varchar(32), PRIMARY KEY(`id`,`no`)) ENGINE=InnoDB;
執行結果無法顯示的查詢到
_rowid
:
同樣,這一理論也可以作用於唯一索引,如果非空唯一索引不是由單獨一列構成,那麼也無法直接查詢得到
_rowid
。這一測試過程省略,有興趣的小夥伴可以自己動手試試。
4、存在多個唯一索引
在mysql中,每張表只能存在一個主鍵,但是可以存在多個唯一索引。那麼如果同時存在多個符合規則的唯一索引,會引用哪個作為
_rowid
的值呢?老規矩,還是看官方文件的解答:
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。
簡單翻譯一下,如果表中的第一個非空唯一索引僅由一個整數型別欄位構成,那麼
_rowid
會引用這個欄位的值。否則,如果第一個非空唯一索引不滿足這種情況,那麼
_rowid
將不可用。
在下面的表中,建立兩個都符合規則的唯一索引:
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;
看一下執行查詢語句的結果:
可以看到
_rowid
的值與
no
這一列的值相同,證明了
_rowid
會嚴格地選取第一個建立的唯一索引作為它的引用。
那麼,如果表中建立的第一個唯一索引不符合
_rowid
的引用規則,第二個唯一索引滿足規則,這種情況下,
_rowid
可以被顯示地查詢嗎?針對這種情況我們建表如下,表中的第一個索引是聯合唯一索引,第二個索引才是單列的唯一索引情況,再來進行一下測試:
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;
進行查詢,可以看到雖然存在一個單列的非空唯一索引,但是因為順序選取的第一個不滿足要求,因此仍然不能直接查詢
_rowid
:
如果將上面建立唯一索引的語句順序調換,那麼將可以正常顯式的查詢到
_rowid
。
5、同時存在主鍵與唯一索引
從上面的例子中,可以看到唯一索引的
定義順序
會決定將哪一個索引應用
_rowid
,那麼當同時存在主鍵和唯一索引時,定義順序會對其引用造成影響嗎?
按照下面的語句建立兩個表,只有建立主鍵和唯一索引的順序不同:
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;
檢視執行結果:
可以得出結論,當同時存在符合條件的主鍵和唯一索引時,無論建立順序如何,
_rowid
都會優先引用主鍵欄位的值。
6、無符合條件的主鍵與唯一索引
上面,我們把能夠直接透過
select
語句查詢到的稱為顯式的
_rowid
,在其他情況下雖然
_rowid
不能被顯式查詢,但是它也是一直存在的,這種情況我們可以將其稱為隱式的
_rowid
。
實際上,
innoDB
在沒有預設主鍵的情況下會生成一個6位元組長度的無符號數作為自動增長的
_rowid
,因此最大為
2^48-1
,到達最大值後會從0開始計算。下面,我們建立一個沒有主鍵與唯一索引的表,在這張表的基礎上,探究一下隱式的
_rowid
。
CREATE TABLE `table10` ( `id` bigint(20), `name` varchar(32)) ENGINE=InnoDB;
首先,我們需要先查詢到mysql的程序
pid
:
ps -ef | grep mysqld
可以看到,mysql的程序
pid
是2068:
在開始動手前,還需要做一點鋪墊, 在
innoDB
中其實維護了一個全域性變數
dictsys。row_id
,沒有定義主鍵的表都會共享使用這個
row_id
,在插入資料時會把這個全域性
row_id
當作自己的主鍵,然後再將這個全域性變數加 1。
接下來我們需要用到
gdb
除錯的相關技術,
gdb
是一個在Linux下的除錯工具,可以用來除錯可執行檔案。在伺服器上,先透過
yum install gdb
安裝,安裝完成後,透過下面的
gdb
命令 把
row_id
修改為 1:
gdb -p 2068 -ex ‘p dict_sys->row_id=1’ -batch
命令執行結果:
在空表中插入3行資料:
INSERT INTO table10 VALUES (100000001, ‘Hydra’);INSERT INTO table10 VALUES (100000002, ‘Trunks’);INSERT INTO table10 VALUES (100000003, ‘Susan’);
查看錶中的資料,此時對應的
_rowid
理論上是1~3:
然後透過
gdb
命令把
row_id
改為最大值
2^48
,此時已超過
dictsys。row_id
最大值:
gdb -p 2068 -ex ‘p dict_sys->row_id=281474976710656’ -batch
命令執行結果:
再向表中插入三條資料:
INSERT INTO table10 VALUES (100000004, ‘King’);INSERT INTO table10 VALUES (100000005, ‘Queen’);INSERT INTO table10 VALUES (100000006, ‘Jack’);
查看錶中的全部資料,可以看到第一次插入的三條資料中,有兩條資料被覆蓋了:
為什麼會出現資料覆蓋的情況呢,我們對這一結果進行分析。首先,在第一次插入資料前
_rowid
為1,插入的三條資料對應的
_rowid
為1、2、3。如下圖所示:
當手動設定
_rowid
超過最大值後,下一次插入資料時,插入的
_rowid
重新從0開始,因此第二次插入的三條資料的
_rowid
應該為0、1、2。這時準備被插入的資料如下所示:
當出現相同
_rowid
的情況下,新插入的資料會根據
_rowid
覆蓋掉原有的資料,過程如圖所示:
所以當表中的主鍵或唯一索引不滿足我們前面提到的要求時,
innoDB
使用的隱式的
_rowid
是存在一定風險的,雖然說
2^48
這個值很大,但還是有可能被用盡的,當
_rowid
用盡後,之前的記錄就會被覆蓋。從這一角度也可以提醒大家,在建表時一定要建立主鍵,否則就有可能發生資料的覆蓋。