MYSQL

发表于 更新于
本文字数： 2.1k 阅读时长 ≈ 2 分钟

自增主键用完了怎么办？

解决：

主键用完了会插入失败。
在 mysql 中，int 的存储范围：

最小值最大值存储大小
int（有符号）-214748364821474836484bytes
int（无符号）042949672954bytes

以无符号整型为主键，可存储 43 亿数据。
一般不会用到最大值，如果达到最大值，继续插入会报异常：

//Duplicate entry ‘4294967295’ for key ‘PRIMARY’

我们可以将主键类型修改为 bigint 继续使用。
bigint 的存储范围：

最小值最大值存储大小
bigint（有符号）-922337203685477580892233720368547758088bytes
bigint（无符号）0184467440737095516158bytes

问题：

对已经设置主键为 int 的表，且自增主键已经用完，如何改表？

1. 使用 mysql 自己的原生语句。

这种操作不支持并发的 DML 操作，会导致表无法进行 UPDATA，DELETE 操作，因此这种方法不可行。

2. 借助第三方工具。

使用业内通用的第三方工具，如，pt-osc、gh-ost。

3. 改从库表结构，然后主从切换。

方法较为复杂。因为 mysql 一般使用主从架构，从库用于读，在修改从库的表结构是，不会阻塞读操作，改完之后进行主从切换即可。主从切换时有可能造成数据丢失。

4. 分库分表

单表数据量如此巨大，使用分库分表。此时使用分布式唯一主键。

锁

锁总结 - 九分石人的个人空间 - OSCHINA - 中文开源技术交流社区
mysql 锁分为共享锁和排他锁，也叫做读锁和写锁。
读锁是共享的，可以通过 lock in share mode 实现，这时候只能读不能写。
写锁是排他的，会阻塞其他的读锁和写锁。
从颗粒度来区分，可以分为表锁和行锁两种。
表锁会锁定整张表并且阻塞所有用户对该表的所有读写操作。
行锁又可以分为乐观锁和悲观锁，悲观锁可以通过 for update 实现，乐观锁则通过版本号实现。

不推荐使用 UUID 或者雪花 ID 作为主键

为什么 MySQL 不推荐使用 uuid 或者雪花 id 作为主键？

使用自增主键的好处

自增的主键的值是顺序的,所以 Innodb 把每一条记录都存储在一条记录的后面。当达到页面的最大填充因子时候(innodb 默认的最大填充因子是页大小的 15/16,会留出 1/16 的空间留作以后的修改)：

  1. 下一条记录就会写入新的页中，一旦数据按照这种顺序的方式加载，主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，不会有页的浪费。
  2. 新插入的行一定会在原有的最大数据行的下一行，mysql 定位和寻址很快，不会为计算新行的位置而产生实际的消耗。
  3. 减少了页分裂和碎片的产生。

使用 uuid 的索引内部结构

因为 uuid 相对顺序的自增 id 来说是毫无规律可言的,新行的值不一定要比之前的主键的值要大,所以 innodb 无法做到总是把新行插入到索引的最后,而是需要为新行寻找新的合适的位置从而来分配新的空间。
这个过程需要做很多额外的操作，数据的毫无顺序会导致数据分布散乱，将会导致以下的问题：

  1. 写入的目标页很可能已经刷新到磁盘上，并且从缓存上移除，或者还没有被加载到缓存中，innodb 在插入之前不得不先找到并从磁盘读取内容到内存中，这将导致大量的随机 IO。
  2. 因为写入是乱序的，innodb 不得不频繁的做页分裂操作，以便为新的行分配空间，页分裂导致移动大量的数据，一次插入最少需要修改 3 个页以上。
  3. 由于频繁的页分裂，页会变得稀疏并被不规则的填充，最终会导致数据会有碎片。

在把随机值（uuid 和雪花 id）载入到聚簇索引(innodb 默认的索引类型)以后,有时候会需要做一次 OPTIMEIZE TABLE 来重建表并优化页的填充，这将又需要一定的时间消耗。

自增主键的缺点

  1. 别人一旦爬取你的数据库,就可以根据数据库的自增 id 获取到你的业务增长信息，很容易分析出你的经营情况。
  2. 对于高并发的负载，innodb 在按主键进行插入的时候会造成明显的锁争用，主键的上界会成为争抢的热点，因为所有的插入都发生在这里，并发插入会导致间隙锁竞争。
  3. Auto_Increment 锁机制会造成自增锁的抢夺,有一定的性能损失。

主从同步怎么做？

mysql 主从同步的原理：

  1. master 提交完事务后，写入 binlog。
  2. slave 连接到 master，获取 binlog。
  3. master 创建 dump 线程，推送 binlog 到 slave。
  4. slave 启动一个 IO 线程，读取同步过来的 master 的 binlog，记录到 relay log 中继日志中。
  5. slave 再开启一个线程读取 relay log 事件并在 slave 执行，完成同步。
  6. slave 记录自己的 binlog。

由于 mysql 默认的赋值方式是异步的，主库把日志发送给从库后不关系从库是否已经处理，这样会产生一个问题就是假设主库挂了，从库处理失败了，这时候从库升为主库后，日志就丢失了。由此产生：

全同步复制

主库写入 binlog 后强制同步日志到从库，所有的从库都执行完成后才返回给客户端，但是很显然这个方式的话性能会受到严重影响。

半同步复制

和全同步不同的是，半同步复制的逻辑是这样，从库写入日志成功后返回 ACK 确认给主库，主库收到至少一个从库的确认就认为写操作完成。