在介紹mysql的多版本併發控制mvcc
的過程中,我們提到過mysql中存在一些隱藏列,例如行標識、事務ID、回滾指標等,不知道大家是否和我一樣好奇過,要怎樣才能實際地看到這些隱藏列的值呢?
本文我們就來重點討論一下諸多隱藏列中的行標識DB_ROW_ID
,實際上,將行標識稱為隱藏列並不準確,因為它並不是一個真實存在的列,DB_ROW_ID
實際上是一個非空唯一列的別名。在撥開它的神祕面紗之前,我們看一下官方文件的說明:
If a table has a
PRIMARY KEY
orUNIQUE NOT NULL
index that consists of a single column that has an integer type, you can use_rowid
to refer to the indexed column inSELECT
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 thePRIMARY KEY
column if there is aPRIMARY KEY
consisting of a single integer column. If there is aPRIMARY 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 firstUNIQUE NOT NULL
index if that index consists of a single integer column. If the firstUNIQUE 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
用盡後,之前的記錄就會被覆蓋。從這一角度也可以提醒大家,在建表時一定要建立主鍵,否則就有可能發生資料的覆蓋。
本文基於mysql 5.7.31 進行測試
官方文件:https://dev.mysql.com/doc/refman/5.7/en/create-index.html
歡迎關注公眾號【碼農參上】,回覆關鍵字“面試”、“架構”獲取電子書資料