主頁(yè) > 知識(shí)庫(kù) > MySQL中主鍵與rowid的使用陷阱總結(jié)

MySQL中主鍵與rowid的使用陷阱總結(jié)

熱門標(biāo)簽:萊西市地圖標(biāo)注 400電話申請(qǐng)信用卡 銷售語(yǔ)音電話機(jī)器人 外呼系統(tǒng)電銷受騙 巫師三血與酒地圖標(biāo)注 常州網(wǎng)絡(luò)外呼系統(tǒng)開(kāi)發(fā) 走過(guò)哪個(gè)省地圖標(biāo)注 在哪里申請(qǐng)400電話 安徽ai電話電銷機(jī)器人有效果嗎

前言

大家在MySQL中我們可能聽(tīng)到過(guò)rowid的概念,但是卻很難去測(cè)試實(shí)踐,不可避免會(huì)有一些疑惑,比如:

  • 如何感受到rowid的存在;
  • rowid和主鍵有什么關(guān)聯(lián)關(guān)系;
  • 在主鍵的使用中存在哪些隱患;
  • 如何來(lái)理解rowid的潛在瓶頸并調(diào)試驗(yàn)證。

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

問(wèn)題1、如何感受到rowid的存在

我們不妨通過(guò)一個(gè)案例來(lái)進(jìn)行說(shuō)明。

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

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)肯定沒(méi)有這么多的記錄,明顯不對(duì),到底是哪里出了問(wèn)題呢。

自己仔細(xì)看了下SQL,發(fā)現(xiàn)是沒(méi)有加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ù)庫(kù)參數(shù)sql_mode默認(rèn)為空,不會(huì)校驗(yàn)這個(gè)部分,從語(yǔ)法角度來(lái)說(shuō),是允許的;但是到了高版本,比如5.7版本之后是不支持的,所以解決方案很簡(jiǎn)單,在添加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è)解析的邏輯,看起來(lái)是SQL解析了第一行,然后輸出了count(*)的操作,顯然這是從執(zhí)行計(jì)劃中無(wú)法得到的信息。

我們換個(gè)思路,可以看到這個(gè)表有4萬(wàn)多條的記錄。

mysql> select count(*)from redis_backup_result;

+----------+

| count(*) |

+----------+

| 40944 |

+----------+

1 row in set (0.01 sec)

為了驗(yàn)證,我們可以使用_rowid的方式來(lái)做初步的驗(yàn)證。

InnoDB表中在沒(méi)有默認(rèn)主鍵的情況下會(huì)生成一個(gè)6字節(jié)空間的自動(dòng)增長(zhǎng)主鍵,可以用select _rowid from table來(lái)查詢,如下:

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來(lái)實(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è)完整的語(yǔ)句,如下:

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)

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

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

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

在學(xué)習(xí)MySQL開(kāi)發(fā)規(guī)范之索引規(guī)范的時(shí)候,強(qiáng)調(diào)過(guò)一個(gè)要點(diǎn):每張表都建議有主鍵。我們?cè)谶@里來(lái)簡(jiǎn)單分析一下為什么?

除了規(guī)范,從存儲(chǔ)方式上來(lái)說(shuō),在InnoDB存儲(chǔ)引擎中,表都是按照主鍵的順序進(jìn)行存放的,我們叫做聚簇索引表或者索引組織表(IOT),表中主鍵的參考依據(jù)如下:

  • 顯式的創(chuàng)建主鍵Primary key。
  • 判斷表中是否有非空唯一索引,如果有,則為主鍵。
  • 如果都不符合上述條件,則會(huì)生成6個(gè)字節(jié)的bigint unsigned值。

從以上可以看到,MySQL對(duì)于主鍵有一套維護(hù)機(jī)制,而一些常見(jiàn)的索引也會(huì)產(chǎn)生相應(yīng)的影響,比如唯一性索引、非唯一性索引、覆蓋索引等都是輔助索引(secondary index,也叫二級(jí)索引),從存儲(chǔ)的角度來(lái)說(shuō),二級(jí)索引列中默認(rèn)包含主鍵列,如果主鍵太長(zhǎng),也會(huì)使得二級(jí)索引很占空間。

問(wèn)題3、在主鍵的使用中存在哪些隱患

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

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

問(wèn)題4、如何來(lái)理解rowid的潛在瓶頸并進(jìn)行調(diào)試驗(yàn)證

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

光說(shuō)不練假把式,我們可以做一個(gè)測(cè)試來(lái)說(shuō)明。

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

create table test_inc(id int) engine=innodb;

 2)通過(guò)ps -ef|grep mysql得到對(duì)應(yīng)的進(jìn)程號(hào),使用gdb來(lái)開(kāi)始做下調(diào)試配置,切記!此處應(yīng)該是自己的測(cè)試環(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ǔ)句,保證測(cè)試是預(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)我們對(duì)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萬(wàn)億,這是一個(gè)相當(dāng)龐大的數(shù)值了,從功能上來(lái)說(shuō),應(yīng)該拋出寫入重復(fù)值的錯(cuò)誤更為合理。

而有了主鍵之后,上面這個(gè)瓶頸似乎就不存在了。

>>>> 參考資料

rowid調(diào)試參考了丁奇的博客

https://www.jb51.net/article/172262.htm

總結(jié)

以上就是這篇文章的全部?jī)?nèi)容了,希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值,謝謝大家對(duì)腳本之家的支持。

您可能感興趣的文章:
  • Mysql主鍵UUID和自增主鍵的區(qū)別及優(yōu)劣分析
  • 深入分析mysql為什么不推薦使用uuid或者雪花id作為主鍵
  • spring boot整合mybatis利用Mysql實(shí)現(xiàn)主鍵UUID的方法
  • Python3 操作 MySQL 插入一條數(shù)據(jù)并返回主鍵 id的實(shí)例
  • 當(dāng)Mysql行鎖遇到復(fù)合主鍵與多列索引詳解
  • 淺析MySQL 主鍵使用數(shù)字還是uuid查詢快

標(biāo)簽:鞍山 黃石 果洛 煙臺(tái) 河北 赤峰 來(lái)賓 陽(yáng)江

巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《MySQL中主鍵與rowid的使用陷阱總結(jié)》,本文關(guān)鍵詞  MySQL,中主,鍵,與,rowid,的,;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。
  • 相關(guān)文章
  • 下面列出與本文章《MySQL中主鍵與rowid的使用陷阱總結(jié)》相關(guān)的同類信息!
  • 本頁(yè)收集關(guān)于MySQL中主鍵與rowid的使用陷阱總結(jié)的相關(guān)信息資訊供網(wǎng)民參考!
  • 推薦文章