一起看看MySQL中的隱藏列

語言: CN / TW / HK
摘要:在mysql的多版本併發控制 mvcc中,我們知道mysql中存在一些隱藏列,例如 標識、事務ID、回滾指標等,不知道大家是否和我一樣好奇過,要怎樣才能實際地看到這些隱藏列的值呢?

本文分享自華為雲社群《眼見為實,看看MySQL中的隱藏列!》,作者:碼農參上。

在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用盡後,之前的記錄就會被覆蓋。從這一角度也可以提醒大家,在建表時一定要建立主鍵,否則就有可能發生資料的覆蓋。

本文基於mysql 5.7.31 進行測試
官方文件:https://dev.mysql.com/doc/refman/5.7/en/create-index.html

 

點選關注,第一時間瞭解華為雲新鮮技術~