亚洲乱码中文字幕综合,中国熟女仑乱hd,亚洲精品乱拍国产一区二区三区,一本大道卡一卡二卡三乱码全集资源,又粗又黄又硬又爽的免费视频

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

 更新時間：2019年10月18日 09:38:25   作者：楊建榮  
這篇文章主要給大家總結(jié)介紹了關(guān)于MySQL中主鍵與rowid的使用陷阱，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧

前言

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

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

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

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

我們不妨通過一個案例來進(jìn)行說明。

記得有一天統(tǒng)計備份數(shù)據(jù)的時候，寫了一條SQL，當(dāng)看到執(zhí)行結(jié)果時才發(fā)現(xiàn)SQL語句沒有寫完整，在完成統(tǒng)計工作之后，我準(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ù)特點，一天之內(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)為空，不會校驗這個部分，從語法角度來說，是允許的；但是到了高版本，比如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表中在沒有默認(rèn)主鍵的情況下會生成一個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)，當(dāng)然在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(*)的操作。

當(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ī)范的時候，強(qiáng)調(diào)過一個要點：每張表都建議有主鍵。我們在這里來簡單分析一下為什么？

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

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

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

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

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

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

問題4、如何來理解rowid的潛在瓶頸并進(jìn)行調(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)的進(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ù)期的樣子。

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)然這個概率是很低的，需要自增列的值到281萬億，這是一個相當(dāng)龐大的數(shù)值了，從功能上來說，應(yīng)該拋出寫入重復(fù)值的錯誤更為合理。

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

>>>> 參考資料

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

http://chabaoo.cn/article/172262.htm

總結(jié)

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

相關(guān)文章

  • sql語句示例之case?when作為where條件

    sql語句示例之case?when作為where條件

    這篇文章主要給大家介紹了關(guān)于sql語句示例之case?when作為where條件的相關(guān)資料,在SQL語句中CASE WHEN子句是根據(jù)條件表達(dá)式的結(jié)果來執(zhí)行不同的邏輯操作,它使用在WHERE子句中,以根據(jù)特定的條件在查詢結(jié)果中過濾數(shù)據(jù),需要的朋友可以參考下
    2023-08-08
  • mysql 的root 用戶無法授權(quán)及解決navicat 遠(yuǎn)程授權(quán)提示1044問題

    mysql 的root 用戶無法授權(quán)及解決navicat 遠(yuǎn)程授權(quán)提示1044問題

    這篇文章主要介紹了mysql 的root 用戶無法授權(quán),解決navicat 遠(yuǎn)程授權(quán)提示1044問題，本文通過實例代碼給大家介紹的非常詳細(xì)，具有一定的參考借鑒價值,需要的朋友可以參考下
    2020-02-02
  • 使用MySQL中的AVG函數(shù)求平均值的教程

    使用MySQL中的AVG函數(shù)求平均值的教程

    這篇文章主要介紹了使用MySQL中的AVG函數(shù)求平均值的教程,是Python入門學(xué)習(xí)中的基礎(chǔ)知識,需要的朋友可以參考下
    2015-05-05
  • mysql服務(wù)啟動不了解決方案

    mysql服務(wù)啟動不了解決方案

    最近在Windows 2003上的MySQL出現(xiàn)過多次正常運行時無法連接數(shù)據(jù)庫故障，現(xiàn)象是無法連接數(shù)據(jù)庫，也無法停止MySQL或重啟MYSQL,由于每次都是草草嘗試各種方法搞定即可本文將詳細(xì)介紹解決方法
    2012-11-11
  • Mysql多表操作方法講解教程

    Mysql多表操作方法講解教程

    這篇文章主要介紹了MySQL數(shù)據(jù)庫的多表操作，文章圍繞主題展開詳細(xì)的內(nèi)容介紹，具有一定的參考價值，感興趣的小伙伴可以參考一下，希望對你的學(xué)習(xí)有所幫助
    2022-12-12
  • Mysql忘記root密碼怎么辦

    Mysql忘記root密碼怎么辦

    MySQL有時候忘記了root密碼是一件傷感的事，下面通過本文給大家介紹Mysql忘記root密碼怎么辦的相關(guān)知識，需要的朋友參考下
    2016-01-01
  • MySQL中數(shù)據(jù)導(dǎo)入恢復(fù)的簡單教程

    MySQL中數(shù)據(jù)導(dǎo)入恢復(fù)的簡單教程

    這篇文章主要介紹了MySQL中數(shù)據(jù)導(dǎo)入恢復(fù)的簡單教程,主要用到了LOAD DATA語句,需要的朋友可以參考下
    2015-05-05
  • MySQL多表數(shù)據(jù)記錄查詢詳解

    MySQL多表數(shù)據(jù)記錄查詢詳解

    這篇文章主要為大家詳細(xì)介紹了MySQL多表數(shù)據(jù)記錄查詢操作，具有一定的實用性，感興趣的小伙伴們可以參考一下
    2016-08-08
  • mysql 5.1版本修改密碼及遠(yuǎn)程登錄mysql數(shù)據(jù)庫的方法

    mysql 5.1版本修改密碼及遠(yuǎn)程登錄mysql數(shù)據(jù)庫的方法

    這篇文章主要介紹了mysql 5.1版本修改密碼及遠(yuǎn)程登錄mysql數(shù)據(jù)庫的方法,需要的朋友可以參考下
    2017-04-04
  • mysql數(shù)據(jù)庫刪除重復(fù)數(shù)據(jù)只保留一條方法實例

    mysql數(shù)據(jù)庫刪除重復(fù)數(shù)據(jù)只保留一條方法實例

    這篇文章主要給大家介紹了關(guān)于mysql數(shù)據(jù)庫刪除重復(fù)數(shù)據(jù)，只保留一條的相關(guān)資料，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧
    2021-03-03

最新評論