图集1/5

正文 1987字数 94,957阅读



这个问题其实可以分为有主键 & 无主键两种情况回答。



有主键

如果你的表有主键，并且把主键设置为自增。

在 MySQL 中，一般会把主键设置成 int 型。而 MySQL 中 int 型占用 4 个字节，作为有符号位的话范围就是 [-2^31,2^31-1]，也就是[-2147483648,2147483647]；无符号位的话最大值就是 2^32-1，也就是 4294967295。

下面以有符号位创建一张表：

CREATE TABLE IF NOT EXISTS `t`( `id` INT(11) NOT NULL AUTO_INCREMENT, `url` VARCHAR(64) NOT NULL, PRIMARY KEY ( `id` ) )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Run code
Cut to clipboard


    插入一个 id 为最大值 2147483647 的值，

    如果此时继续下面的插入语句：

    INSERT INTO t (url) VALUES ('TEST')

    结果就会造成主键冲突：



    解决方案

    虽说 int 4 个字节，最大数据量能存储 21 亿。你可能会觉得这么大的容量，应该不至于用完。但是互联网时代，每天都产生大量的数据，这是很有可能达到的。

    所以，我们的解决方案是：把主键类型改为 bigint，也就是 8 个字节。这样能存储的最大数据量就是 2^64-1，我也数不清有多少了。反正在你有生之年应该是够用的。

    PS：单表 21 亿的数据量显然不现实，一般来说数据量达到 500 万就该分表了。

    没主键

    另一种情况就是建表时没设置主键。这种情况，InnoDB 会自动帮你创建一个不可见的、长度为 6 字节的 row_id，默认是无符号的，所以最大长度是 2^48-1。

    实际上 InnoDB 维护了一个全局的 dictsys.row_id，所以未定义主键的表都共享该 row_id，并不是单表独享。每次插入一条数据，都把全局 row_id 当成主键 id，然后全局 row_id 加 1。

    这种情况的数据库自增 ID 用完会发生什么呢？

    1、创建一张无显示设置主键的表 t：

    CREATE TABLE IF NOT EXISTS `t`( `age` int(4) NOT NULL )ENGINE=InnoDB DEFAULT CHARSET=utf8;
    Run code
    Cut to clipboard


      2、通过 ps -ef|grep mysql 命令获取 mysql 的进程 ID，然后执行命令，通过 gdb 先把 row_id 修改为 1。

      sudo gdb -p 16111 -ex 'p dict_sys->row_id=1' -batch
      Run code
      Cut to clipboard


        出现下图就是没错的：



        3、插入三条数据：

        insert into t(age) values(1); insert into t(age) values(2); insert into t(age) values(3);
        Run code
        Cut to clipboard


          此时的数据库数据：



          4、gdb 把 row_id 修改为最大值：281474976710656

          sudo gdb -p 16111 -ex 'p dict_sys->row_id=281474976710656' -batch
          Run code
          Cut to clipboard


            5、再插入三条数据：
            urlinsert into t(age) values(4); insert into t(age) values(5); insert into t(age) values(6);
            Run code
            Cut to clipboard


              此事的数据库数据：



              分析：

              刚开始设置 row_id 为 1，插入三条数据 1、2、3 的 row_id 也理应是 1、2、3；这是没问题的。

              接着设置 row_id 为最大值，紧跟着插入三条数据。这时的数据库结果是：4、5、6、3；你会发现 1、2 被覆盖了。

              row_id 达到后最大值后插入的值 4、5、6 的 row_id 分别是 0、1、2；由于 row_id 为 1、2 的值已存在，所以后者的值 5、6 会覆盖掉 row_id 为 1、2 的值。

              结论：row_id 达到最大值后会从 0 重新开始算；前面插入的数据就会被后插入的数据覆盖，且不会报错。

              总结

              数据库自增主键用完后分两种情况：

              有主键，报主键冲突

              无主键，InnDB 会自动生成一个全局的row_id。它到达最大值后会从 0 开始算，遇到 row_id 一样时，新数据覆盖旧数据。所以，我们还是尽量给表设置主键。

              原文链接