MySQL中主鍵與rowid的使用陷阱總結(jié)
前言
大家在MySQL中我們可能聽到過rowid的概念，但是卻很難去測試實(shí)踐，不可避免會有一些疑惑，比如：
- 如何感受到rowid的存在；
- rowid和主鍵有什么關(guān)聯(lián)關(guān)系；
- 在主鍵的使用中存在哪些隱患；
- 如何來理解rowid的潛在瓶頸并調(diào)試驗(yàn)證。
本文要和大家一起討論這幾個(gè)問題，測試的環(huán)境基于MySQL 5.7.19版本。
問題1、如何感受到rowid的存在
我們不妨通過一個(gè)案例來進(jìn)行說明。
記得有一天統(tǒng)計(jì)備份數(shù)據(jù)的時(shí)候，寫了一條SQL，當(dāng)看到執(zhí)行結(jié)果時(shí)才發(fā)現(xiàn)SQL語句沒有寫完整，在完成統(tǒng)計(jì)工作之后，我準(zhǔn)備分析下這條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ù)特點(diǎn)，一天之內(nèi)肯定沒有這么多的記錄，明顯不對，到底是哪里出了問題呢。
自己仔細(xì)看了下SQL，發(fā)現(xiàn)是沒有加group by，我們隨機(jī)查出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默認(rèn)為空，不會校驗(yàn)這個(gè)部分，從語法角度來說，是允許的；但是到了高版本，比如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)
但是比較好奇這個(gè)解析的邏輯，看起來是SQL解析了第一行，然后輸出了count(*)的操作，顯然這是從執(zhí)行計(jì)劃中無法得到的信息。
我們換個(gè)思路，可以看到這個(gè)表有4萬多條的記錄。
mysql> select count(*)from redis_backup_result; +----------+ | count(*) | +----------+ | 40944 | +----------+ 1 row in set (0.01 sec)
為了驗(yàn)證，我們可以使用_rowid的方式來做初步的驗(yàn)證。
InnoDB表中在沒有默認(rèn)主鍵的情況下會生成一個(gè)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)
再可以實(shí)現(xiàn)一個(gè)初步的思路。
mysql> select _rowid,count(*)from redis_backup_result; +--------+----------+ | _rowid | count(*) | +--------+----------+ | 117 | 41036 | +--------+----------+ 1 row in set (0.03 sec)
然后繼續(xù)升華一些，借助rownum來實(shí)現(xiàn)，當(dāng)然在MySQL中原生不支持這個(gè)特性，需要間接實(shí)現(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)
寫一個(gè)完整的語句，如下：
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)
通過這個(gè)案例，可以很明顯發(fā)現(xiàn)是第1行的記錄，然后做了count(*)的操作。
當(dāng)然我們的目標(biāo)是要掌握rowid和主鍵的一些關(guān)聯(lián)關(guān)系，所以我們也復(fù)盤一下主鍵使用中的隱患問題。
問題2、rowid和主鍵有什么關(guān)聯(lián)關(guān)系
在學(xué)習(xí)MySQL開發(fā)規(guī)范之索引規(guī)范的時(shí)候，強(qiáng)調(diào)過一個(gè)要點(diǎn)：每張表都建議有主鍵。我們在這里來簡單分析一下為什么？
除了規(guī)范，從存儲方式上來說，在InnoDB存儲引擎中，表都是按照主鍵的順序進(jìn)行存放的，我們叫做聚簇索引表或者索引組織表（IOT），表中主鍵的參考依據(jù)如下：
- 顯式的創(chuàng)建主鍵Primary key。
- 判斷表中是否有非空唯一索引，如果有，則為主鍵。
- 如果都不符合上述條件，則會生成6個(gè)字節(jié)的bigint unsigned值。
從以上可以看到，MySQL對于主鍵有一套維護(hù)機(jī)制，而一些常見的索引也會產(chǎn)生相應(yīng)的影響，比如唯一性索引、非唯一性索引、覆蓋索引等都是輔助索引（secondary index，也叫二級索引），從存儲的角度來說，二級索引列中默認(rèn)包含主鍵列，如果主鍵太長，也會使得二級索引很占空間。
問題3、在主鍵的使用中存在哪些隱患
這就引出行業(yè)里非常普遍的主鍵性能問題，這不是一個(gè)單一的問題，需要MySQL方向持續(xù)改造的，將技術(shù)價(jià)值和業(yè)務(wù)價(jià)值結(jié)合起來。我看到很多業(yè)務(wù)中設(shè)置了自增列，但是大多數(shù)情況下，這種自增列卻沒有實(shí)際的業(yè)務(wù)含義，盡管是主鍵列保證了ID的唯一性，但是業(yè)務(wù)開發(fā)無法直接根據(jù)主鍵自增列來進(jìn)行查詢，于是他們需要尋找新的業(yè)務(wù)屬性，添加一系列的唯一性索引，非唯一性索引等等，這樣一來我們堅(jiān)持的規(guī)范和業(yè)務(wù)使用的方式就存在了偏差。
從另外一個(gè)維度來說，我們對于主鍵的理解是有偏差的，我們不能單一的認(rèn)為主鍵就一定是從1開始的整數(shù)類型，我們需要結(jié)合業(yè)務(wù)場景來看待，比如我們的身份證其實(shí)就是一個(gè)不錯(cuò)的例子，把證號分成了幾個(gè)區(qū)段，偏于檢索和維護(hù)；或者是外出就餐時(shí)得到的流水單號，它都有一定的業(yè)務(wù)屬性在里面，對于我們?nèi)ダ斫鈽I(yè)務(wù)的使用是一種不錯(cuò)的借鑒。
問題4、如何來理解rowid的潛在瓶頸并進(jìn)行調(diào)試驗(yàn)證
我們知道rowid只有6個(gè)字節(jié)，因此最大值是2^48,所以一旦 row_id超過這個(gè)值還是會遞增，這種情況下是否存在隱患。
光說不練假把式，我們可以做一個(gè)測試來說明。
1）我們創(chuàng)建一張表test_inc，不包含任何索引。
create table test_inc(id int) engine=innodb;
2）通過ps -ef|grep mysql得到對應(yīng)的進(jìn)程號，使用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àn)，得到建表語句，保證測試是預(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進(jìn)行重置，調(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自增后，還是存在使用瓶頸，當(dāng)然這個(gè)概率是很低的，需要自增列的值到281萬億，這是一個(gè)相當(dāng)龐大的數(shù)值了，從功能上來說，應(yīng)該拋出寫入重復(fù)值的錯(cuò)誤更為合理。
而有了主鍵之后，上面這個(gè)瓶頸似乎就不存在了。
>>>> 參考資料
rowid調(diào)試參考了丁奇的博客
http://m.fzitv.net/article/172262.htm
總結(jié)
以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，謝謝大家對腳本之家的支持。
相關(guān)文章
MySQL存儲引擎中的MyISAM和InnoDB區(qū)別詳解
這篇文章主要介紹了MySQL存儲引擎中的MyISAM和InnoDB區(qū)別詳解,本文總結(jié)了MyISAM與InnoDB的11點(diǎn)區(qū)別,需要的朋友可以參考下2015-03-03
這篇文章主要介紹了解決MySQL8.0時(shí)區(qū)的問題步驟，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2020-12-12
MySQL中crash safe數(shù)據(jù)完整性機(jī)制面試精講
這篇文章主要為大家介紹了MySQL數(shù)據(jù)完整性crash safe特性面試精講,有需要的朋友可以借鑒參考下,希望能夠有所幫助,祝大家多多進(jìn)步,早日升職加薪2023-10-10
今天研究cms系統(tǒng)的時(shí)候發(fā)現(xiàn)，delete 語句后面有個(gè)limit，一直都是select查詢的時(shí)候才使用，不懂為什么要用這個(gè)，正好就百度一下為大家分享下delete中使用limit方法與有點(diǎn)2014-11-11
MySQL進(jìn)行表之間關(guān)聯(lián)更新的實(shí)現(xiàn)方法
在實(shí)際編程工作或運(yùn)維實(shí)踐中,對MySQL數(shù)據(jù)庫表進(jìn)行關(guān)聯(lián)更新是一種比較常見的應(yīng)用場景,針對這樣的業(yè)務(wù)場景,我們來看看有什么方法可以實(shí)現(xiàn)關(guān)聯(lián)更新,需要的朋友可以參考下2023-10-10
這篇文章主要介紹了MySQL5.x版本亂碼問題解決方案,文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,需要的朋友可以參考下2020-09-09
MySQL聯(lián)合查詢之輕松實(shí)現(xiàn)數(shù)據(jù)關(guān)聯(lián)詳解
MySQL中當(dāng)查詢數(shù)據(jù)來自多張表時(shí)需要用到關(guān)聯(lián)查詢,下面這篇文章主要給大家介紹了關(guān)于MySQL聯(lián)合查詢之輕松實(shí)現(xiàn)數(shù)據(jù)關(guān)聯(lián)的相關(guān)資料,文中通過實(shí)例代碼介紹的非常詳細(xì),需要的朋友可以參考下2023-06-06
這篇文章主要介紹了RHEL6.2編譯安裝MySQL 5.6.16過程分享,還包括了mysql_secure_installation腳本運(yùn)行指導(dǎo),需要的朋友可以參考下2014-07-07
這篇文章主要介紹了mysql添加索引反而速度變慢的問題,具有很好的參考價(jià)值,希望對大家有所幫助,如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2024-01-01
mysql如何設(shè)置表中字段為當(dāng)前時(shí)間
這篇文章主要介紹了mysql如何設(shè)置表中字段為當(dāng)前時(shí)間問題，具有很好的參考價(jià)值，希望對大家有所幫助。如有錯(cuò)誤或未考慮完全的地方，望不吝賜教2023-07-07