MySQL中的主鍵和rowid,看似簡單，其實有一些使用陷阱需要注意
大家在MySQL中我們可能聽到過rowid的概念，但是卻很難去測試實踐，不可避免會有一些疑惑，比如：
1）如何感受到rowid的存在
2）rowid和主鍵有什么關聯關系
3）在主鍵的使用中存在哪些隱患
4）如何來理解rowid的潛在瓶頸并調試驗證
今天要和大家一起討論這幾個問題，測試的環境基于MySQL 5.7.19版本。
問題1：如何感受到rowid的存在
我們不妨通過一個案例來進行說明。
記得有一天統計備份數據的時候，寫了一條SQL，當看到執行結果時才發現SQL語句沒有寫完整，在完成統計工作之后，我準備分析下這條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)
根據業務特點，一天之內肯定沒有這么多的記錄，明顯不對，到底是哪里出了問題呢。
自己仔細看了下SQL，發現是沒有加group by，我們隨機查出10條數據。
- 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)
在早期的版本中數據庫參數sql_mode默認為空，不會校驗這個部分，從語法角度來說，是允許的；但是到了高版本，比如5.7版本之后是不支持的，所以解決方案很簡單，在添加group by之后，結果就符合預期了。
- 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(*)的操作，顯然這是從執行計劃中無法得到的信息。
我們換個思路，可以看到這個表有4萬多條的記錄。
- mysql> select count(*)from redis_backup_result;
- +----------+
- | count(*) |
- +----------+
- | 40944 |
- +----------+
- 1 row in set (0.01 sec)
為了驗證，我們可以使用_rowid的方式來做初步的驗證。
InnoDB表中在沒有默認主鍵的情況下會生成一個6字節空間的自動增長主鍵，可以用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)
再可以實現一個初步的思路。
- mysql> select _rowid,count(*)from redis_backup_result;
- +--------+----------+
- | _rowid | count(*) |
- +--------+----------+
- | 117 | 41036 |
- +--------+----------+
- 1 row in set (0.03 sec)
然后繼續升華一些，借助rownum來實現，當然在MySQL中原生不支持這個特性，需要間接實現。
- mysql> SELECT @rowno:=@rowno+1 as rowno,r._rowid from redis_backup_resultr ,(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)
通過這個案例，可以很明顯發現是第1行的記錄，然后做了count(*)的操作。
當然我們的目標是要掌握rowid和主鍵的一些關聯關系，所以我們也復盤一下主鍵使用中的隱患問題。
問題2：rowid和主鍵有什么關聯關系
在學習MySQL開發規范之索引規范的時候，強調過一個要點：每張表都建議有主鍵。我們在這里來簡單分析一下為什么？
除了規范，從存儲方式上來說，在InnoDB存儲引擎中，表都是按照主鍵的順序進行存放的，我們叫做聚簇索引表或者索引組織表（IOT），表中主鍵的參考依據如下：
（1）顯式的創建主鍵Primary key。
（2）判斷表中是否有非空唯一索引，如果有，則為主鍵。
（3）如果都不符合上述條件，則會生成UUID的一個隱式主鍵（6字節大）。
從以上可以看到，MySQL對于主鍵有一套維護機制，而一些常見的索引也會產生相應的影響，比如唯一性索引、非唯一性索引、覆蓋索引等都是輔助索引（secondary index，也叫二級索引），從存儲的角度來說，二級索引列中默認包含主鍵列，如果主鍵太長，也會使得二級索引很占空間。
問題3：在主鍵的使用中存在哪些隱患
這就引出行業里非常普遍的主鍵性能問題，這不是一個單一的問題，需要MySQL方向持續改造的，將技術價值和業務價值結合起來。我看到很多業務中設置了自增列，但是大多數情況下，這種自增列卻沒有實際的業務含義，盡管是主鍵列保證了ID的唯一性，但是業務開發無法直接根據主鍵自增列來進行查詢，于是他們需要尋找新的業務屬性，添加一系列的唯一性索引，非唯一性索引等等，這樣一來我們堅持的規范和業務使用的方式就存在了偏差。
從另外一個維度來說，我們對于主鍵的理解是有偏差的，我們不能單一的認為主鍵就一定是從1開始的整數類型，我們需要結合業務場景來看待，比如我們的身份證其實就是一個不錯的例子，把證號分成了幾個區段，偏于檢索和維護；或者是外出就餐時得到的流水單號，它都有一定的業務屬性在里面，對于我們去理解業務的使用是一種不錯的借鑒。
問題4：如何來理解rowid的潛在瓶頸并進行調試驗證
我們知道rowid只有6個字節，因此最大值是2^48,所以一旦 row_id超過這個值還是會遞增，這種情況下是否存在隱患。
光說不練假把式，我們可以做一個測試來說明。
1）我們創建一張表test_inc，不包含任何索引。
create table test_inc(id int) engine=innodb;
2）通過ps -ef|grep mysql得到對應的進程號，使用gdb來開始做下調試配置，切記！此處應該是自己的測試環境。
- [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）我們做下基本檢驗，得到建表語句，保證測試是預期的樣子。
- mysql> show create table test_inc\G
- *************************** 1. row ***************************
- Table: test_inc
- Create Table: CREATE TABLE `test_inc` (
- `id` int(11) DEFAULT
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8
- 1 row in set (0.00 sec)
4）插入一些數據，使得rowid持續自增。
- 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進行重置，調整為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）繼續寫入一些數據，比如我們寫入4,5,6三行數據
- mysql> insert into test_inc values(4),(5),(6);
- Query OK, 3 rows affected (0.07 sec)
- Records: 3 Duplicates: 0 Warnings: 0
7）查看數據結果，發現1,2兩行已經被覆蓋了。
- mysql> select *from test_inc;
- +------+
- | id |
- +------+
- | 4 |
- | 5 |
- | 6 |
- | 3 |
- +------+
- 4 rows in set (0.00 sec)
由此，我們可以看到rowid自增后，還是存在使用瓶頸，當然這個概率是很低的，需要自增列的值到281萬億，這是一個相當龐大的數值了，從功能上來說，應該拋出寫入重復值的錯誤更為合理。
而有了主鍵之后，上面這個瓶頸似乎就不存在了。
