共計 5774 個字符，預(yù)計需要花費 15 分鐘才能閱讀完成。

這篇文章主要為大家展示了“MySQL 中主鍵與 rowid 的使用陷阱有哪些”，內(nèi)容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓丸趣 TV 小編帶領(lǐng)大家一起研究并學(xué)習(xí)一下“MySQL 中主鍵與 rowid 的使用陷阱有哪些”這篇文章吧。

前言

大家在 MySQL 中我們可能聽到過 rowid 的概念，但是卻很難去測試實踐，不可避免會有一些疑惑，比如：

如何感受到 rowid 的存在；

rowid 和主鍵有什么關(guān)聯(lián)關(guān)系；

在主鍵的使用中存在哪些隱患；

如何來理解 rowid 的潛在瓶頸并調(diào)試驗證。

本文要和大家一起討論這幾個問題，測試的環(huán)境基于 MySQL 5.7.19 版本。

問題 1、如何感受到 rowid 的存在

我們不妨通過一個案例來進行說明。

記得有一天統(tǒng)計備份數(shù)據(jù)的時候，寫了一條 SQL，當看到執(zhí)行結(jié)果時才發(fā)現(xiàn) SQL 語句沒有寫完整，在完成統(tǒng)計工作之后，我準備分析下這條 SQL 語句。

mysql  select backup_date ,count(*) piece_no from redis_backup_result;
+-------------+----------+
| backup_date | piece_no |
+-------------+----------+
| 2018-08-14 | 40906 |
+-------------+----------+
1 row in set (0.03 sec)

根據(jù)業(yè)務(wù)特點，一天之內(nèi)肯定沒有這么多的記錄，明顯不對，到底是哪里出了問題呢。

自己仔細看了下 SQL，發(fā)現(xiàn)是沒有加 group by，我們隨機查出 10 條數(shù)據(jù)。

mysql  select backup_date from redis_backup_result limit 10;
+-------------+
| backup_date |
+-------------+
| 2018-08-14 |
| 2018-08-14 |
| 2018-08-14 |
| 2018-08-15 |
| 2018-08-15 |
| 2018-08-15 |
| 2018-08-15 |
| 2018-08-15 |
| 2018-08-15 |
| 2018-08-15 |
+-------------+
10 rows in set (0.00 sec)

在早期的版本中數(shù)據(jù)庫參數(shù) sql_mode 默認為空，不會校驗這個部分，從語法角度來說，是允許的；但是到了高版本，比如 5.7 版本之后是不支持的，所以解決方案很簡單，在添加 group by 之后，結(jié)果就符合預(yù)期了。

mysql  select backup_date ,count(*) piece_no from redis_backup_result group by backup_date;
+-------------+----------+
| backup_date | piece_no |
+-------------+----------+
| 2018-08-14 | 3 |
| 2018-08-15 | 121 |
| 2018-08-16 | 184 |
| 2018-08-17 | 3284 |
| 2018-08-18 | 7272 |
| 2018-08-19 | 7272 |
| 2018-08-20 | 7272 |
| 2018-08-21 | 7272 |
| 2018-08-22 | 8226 |
+-------------+----------+
9 rows in set (0.06 sec)

但是比較好奇這個解析的邏輯，看起來是 SQL 解析了第一行，然后輸出了 count(*) 的操作，顯然這是從執(zhí)行計劃中無法得到的信息。

我們換個思路，可以看到這個表有 4 萬多條的記錄。

mysql  select count(*)from redis_backup_result;
+----------+
| count(*) |
+----------+
| 40944 |
+----------+
1 row in set (0.01 sec)

為了驗證，我們可以使用_rowid 的方式來做初步的驗證。

InnoDB 表中在沒有默認主鍵的情況下會生成一個 6 字節(jié)空間的自動增長主鍵，可以用 select _rowid from table 來查詢，如下：

mysql  select _rowid from redis_backup_result limit 5;
+--------+
| _rowid |
+--------+
| 117 |
| 118 |
| 119 |
| 120 |
| 121 |
+--------+
5 rows in set (0.00 sec)

再可以實現(xiàn)一個初步的思路。

mysql  select _rowid,count(*)from redis_backup_result;
+--------+----------+
| _rowid | count(*) |
+--------+----------+
| 117 | 41036 |
+--------+----------+
1 row in set (0.03 sec)

然后繼續(xù)升華一些，借助 rownum 來實現(xiàn)，當然在 MySQL 中原生不支持這個特性，需要間接實現(xiàn)。

mysql  SELECT @rowno:=@rowno+1 as rowno,r._rowid from redis_backup_result
r ,(select @rowno:=0) t limit 20;
+-------+--------+
| rowno | _rowid |
+-------+--------+
| 1 | 117 |
| 2 | 118 |
| 3 | 119 |
| 4 | 120 |
| 5 | 121 |
| 6 | 122 |
| 7 | 123 |
| 8 | 124 |
| 9 | 125 |
| 10 | 126 |
| 11 | 127 |
| 12 | 128 |
| 13 | 129 |
| 14 | 130 |
| 15 | 131 |
| 16 | 132 |
| 17 | 133 |
| 18 | 134 |
| 19 | 135 |
| 20 | 136 |
+-------+--------+
20 rows in set (0.00 sec)

寫一個完整的語句，如下：

mysql  SELECT @rowno:=@rowno+1 as rowno,r._rowid ,backup_date,count(*)
from redis_backup_result r ,(select @rowno:=0) t ;
+-------+--------+-------------+----------+
| rowno | _rowid | backup_date | count(*) |
+-------+--------+-------------+----------+
| 1 | 117 | 2018-08-14 | 41061 |
+-------+--------+-------------+----------+
1 row in set (0.02 sec)

通過這個案例，可以很明顯發(fā)現(xiàn)是第 1 行的記錄，然后做了 count(*) 的操作。

當然我們的目標是要掌握 rowid 和主鍵的一些關(guān)聯(lián)關(guān)系，所以我們也復(fù)盤一下主鍵使用中的隱患問題。

問題 2、rowid 和主鍵有什么關(guān)聯(lián)關(guān)系

在學(xué)習(xí) MySQL 開發(fā)規(guī)范之索引規(guī)范的時候，強調(diào)過一個要點：每張表都建議有主鍵。我們在這里來簡單分析一下為什么？

除了規(guī)范，從存儲方式上來說，在 InnoDB 存儲引擎中，表都是按照主鍵的順序進行存放的，我們叫做聚簇索引表或者索引組織表（IOT），表中主鍵的參考依據(jù)如下：

顯式的創(chuàng)建主鍵 Primary key。

判斷表中是否有非空唯一索引，如果有，則為主鍵。

如果都不符合上述條件，則會生成 6 個字節(jié)的 bigint unsigned 值。

從以上可以看到，MySQL 對于主鍵有一套維護機制，而一些常見的索引也會產(chǎn)生相應(yīng)的影響，比如唯一性索引、非唯一性索引、覆蓋索引等都是輔助索引（secondary index，也叫二級索引），從存儲的角度來說，二級索引列中默認包含主鍵列，如果主鍵太長，也會使得二級索引很占空間。

問題 3、在主鍵的使用中存在哪些隱患

這就引出行業(yè)里非常普遍的主鍵性能問題，這不是一個單一的問題，需要 MySQL 方向持續(xù)改造的，將技術(shù)價值和業(yè)務(wù)價值結(jié)合起來。我看到很多業(yè)務(wù)中設(shè)置了自增列，但是大多數(shù)情況下，這種自增列卻沒有實際的業(yè)務(wù)含義，盡管是主鍵列保證了 ID 的唯一性，但是業(yè)務(wù)開發(fā)無法直接根據(jù)主鍵自增列來進行查詢，于是他們需要尋找新的業(yè)務(wù)屬性，添加一系列的唯一性索引，非唯一性索引等等，這樣一來我們堅持的規(guī)范和業(yè)務(wù)使用的方式就存在了偏差。

從另外一個維度來說，我們對于主鍵的理解是有偏差的，我們不能單一的認為主鍵就一定是從 1 開始的整數(shù)類型，我們需要結(jié)合業(yè)務(wù)場景來看待，比如我們的身份證其實就是一個不錯的例子，把證號分成了幾個區(qū)段，偏于檢索和維護；或者是外出就餐時得到的流水單號，它都有一定的業(yè)務(wù)屬性在里面，對于我們?nèi)ダ斫鈽I(yè)務(wù)的使用是一種不錯的借鑒。

問題 4、如何來理解 rowid 的潛在瓶頸并進行調(diào)試驗證

我們知道 rowid 只有 6 個字節(jié)，因此最大值是 2^48, 所以一旦 row_id 超過這個值還是會遞增，這種情況下是否存在隱患。

光說不練假把式，我們可以做一個測試來說明。

1）我們創(chuàng)建一張表 test_inc，不包含任何索引。

create table test_inc(id int) engine=innodb;

 2）通過 ps -ef|grep mysql 得到對應(yīng)的進程號，使用 gdb 來開始做下調(diào)試配置，切記！此處應(yīng)該是自己的測試環(huán)境。

[root@dev01 mysql]# gdb -p 3132 -ex  p dict_sys- row_id=1  -batch
[New LWP 3192]
[New LWP 3160]
[New LWP 3159]
[New LWP 3158]
[New LWP 3157]
[New LWP 3156]
[New LWP 3155]
[New LWP 3154]
[New LWP 3153]
[New LWP 3152]
[New LWP 3151]
[New LWP 3150]
[New LWP 3149]
[New LWP 3148]
[New LWP 3147]
[New LWP 3144]
[New LWP 3143]
[New LWP 3142]
[New LWP 3141]
[New LWP 3140]
[New LWP 3139]
[New LWP 3138]
[New LWP 3137]
[New LWP 3136]
[New LWP 3135]
[New LWP 3134]
[New LWP 3133]
[Thread debugging using libthread_db enabled]
0x00000031ed8df283 in poll () from /lib64/libc.so.6
$1 = 1

3）我們做下基本檢驗，得到建表語句，保證測試是預(yù)期的樣子。

mysql  show create table test_inc\G
*************************** 1. row ***************************
 Table: test_inc
Create Table: CREATE TABLE `test_inc` ( `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

4）插入一些數(shù)據(jù)，使得 rowid 持續(xù)自增。

mysql  insert into test_inc values(1),(2),(3);
Query OK, 3 rows affected (0.08 sec)
Records: 3 Duplicates: 0 Warnings: 0

5）我們對 rowid 進行重置，調(diào)整為 2^48

mysql  select power(2,48);
+-----------------+
| power(2,48) |
+-----------------+
| 281474976710656 |
+-----------------+
1 row in set (0.00 sec)
 
[root@dev01 mysql]# gdb -p 3132 -ex  p dict_sys- row_id=281474976710656  -batch
[Thread debugging using libthread_db enabled]
0x00000031ed8df283 in poll () from /lib64/libc.so.6
$1 = 281474976710656

6）繼續(xù)寫入一些數(shù)據(jù)，比如我們寫入 4,5,6 三行數(shù)據(jù)。

mysql  insert into test_inc values(4),(5),(6); 
Query OK, 3 rows affected (0.07 sec)
Records: 3 Duplicates: 0 Warnings: 0

7）查看數(shù)據(jù)結(jié)果，發(fā)現(xiàn) 1,2 兩行已經(jīng)被覆蓋了。

mysql  select *from test_inc;
+------+
| id |
+------+
| 4 |
| 5 |
| 6 |
| 3 |
+------+
4 rows in set (0.00 sec)

由此，我們可以看到 rowid 自增后，還是存在使用瓶頸，當然這個概率是很低的，需要自增列的值到 281 萬億，這是一個相當龐大的數(shù)值了，從功能上來說，應(yīng)該拋出寫入重復(fù)值的錯誤更為合理。

而有了主鍵之后，上面這個瓶頸似乎就不存在了。

以上是“MySQL 中主鍵與 rowid 的使用陷阱有哪些”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道！