精华内容
下载资源
问答
  • 自增主键
    千次阅读
    2020-05-29 09:22:36

    使用自增主键的好处

    每次插入新的记录,记录就会顺序的添加到当前索引节点的后续位置,当一页写满,就会自动开辟一共新的页。

    使用非自增主键坏处

    由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页得中间某个位置,此时MySQL不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

    总结

    如果InnoDB表的数据写入顺序能和B+树索引的叶子节点顺序一致的话,这时候存取效率是最高的。

    • 使用自增列(INT/BIGINT类型)做主键,这时候写入顺序是自增的,和B+数叶子节点分裂顺序一致;
    • 该表不指定自增列做主键,同时也没有可以被选为主键的唯一索引(上面的条件),这时候InnoDB会选择内置的ROWID作为主键,写入顺序和ROWID增长顺序一致;
    • 如果一个InnoDB表又没有显示主键,又有可以被选择为主键的唯一索引,但该唯一索引可能不是递增关系时(例如字符串、UUID、多字段联合唯一索引的情况),该表的存取效率就会比较差。
    更多相关内容
  • 面试官:”用过mysql吧,你们是用自增主键还是UUID?” 你:”用的是自增主键” 面试官:”为什么是自增主键?” 你:”因为采用自增主键,数据在物理结构上是顺序存储,性能最好,blabla…” 面试官:”那自增主键...
  • Mybatis插入数据返回自增主键 Mybatis插入数据以后只会返回影响的数据库行数,如果是插入一条数据成功则返回1,失败返回零,插入多条返回插入成功的数量。 通过注解或者xml配置无法直接返回自增的主键。Mybatis插入...
  • 主要介绍了利用Java的MyBatis框架获取MySQL中插入记录的自增长字段值,其中大家可以看到MyBatis支持普通SQL语句所带来的遍历,需要的朋友可以参考下
  • 在设计数据库表的时候发现Oracle没有自增主键的设置,Google了解到Oracle本身并不支持自增主键,需要通过序列(Sequence)和触发器(Trigger)实现。 创建表Student Create Table Student( id number(12) primary ...
  • mysql修改自增主键初始值,简单易操作,数据库维护小技巧。
  • 主要介绍了SQLite3中自增主键相关知识总结,清零的方法、INTEGER PRIMARY KEY AUTOINCREMENT和rowid的使用等,需要的朋友可以参考下
  • mybatis获取自增主键的值 ,mybatis获取自增主键的值!
  • 自增 主键

    千次阅读 2021-01-22 17:41:49
    我们前面提到过自增主键,由于自增主键可以让主键索引尽量地保持递增顺序插入,避免了页分裂,因此索引更紧凑。 自增值的实现机制 1.存储 表定义里面出现了一个 AUTO_INCREMENT=2,表示下一次插入数据时,如果...

    我们前面提到过自增主键,由于自增主键可以让主键索引尽量地保持递增顺序插入,避免了页分裂,因此索引更紧凑。

    自增值 的实现机制

    1. 存储

        

    表定义里面出现了一个 AUTO_INCREMENT=2,表示下一次插入数据时,如果需要自动生成自增值,会生成 id=2。

    这个输出结果容易引起这样的误解:自增值是保存在表结构定义里的。实际上,表的结构定义存放在后缀名为.frm 的文件中,但是并不会保存自增值

    不同的引擎对于自增值的保存策略不同:

     a. MyISAM 引擎的自增值保存在数据文件中。

     b. InnoDB 引擎的自增值,其实是保存在了内存里,并且到了 MySQL 8.0 版本后,才有了“自增值持久化”的能力,也就是才实现了“如果发生重启,表的自增值可以恢复为 MySQL 重启前的值”,具体情况是:

    • 在 MySQL 5.7 及之前的版本,自增值保存在内存里,并没有持久化。每次重启后,第一次打开表的时候,都会去找自增值的最大值 max(id),然后将 max(id)+1 作为这个表当前的自增值。 也就是说,MySQL 重启可能会修改一个表的 AUTO_INCREMENT 的值。
    • 在 MySQL 8.0 版本,将自增值的变更记录在了 redo log 中,重启的时候依靠 redo log 恢复重启之前的值。

    2. 修改机制

    在 MySQL 里面,如果字段 id 被定义为 AUTO_INCREMENT,在插入一行数据的时候,自增值的行为如下:

    • 如果插入数据时 id 字段指定为 0、null 或未指定值,那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段;
    • 如果插入数据时 id 字段指定了具体的值,就直接使用语句里指定的值。而且,自增值大则不变,小则更新。

    自增值更新:从 auto_increment_offset 开始,以 auto_increment_increment(默认值1) 为步长,持续叠加,直到找到第一个大于 插入 的值,作为新的自增值。

    3. 修改时机

    假设,表 t 里面已经有了 (1,1,1) 这条记录,插入一条数据:insert into t values(null, 1, 1);这个语句的执行流程就是:

    1. 执行器调用 InnoDB 引擎接口写入一行,传入的这一行的值是 (0,1,1);
    2. InnoDB 发现用户没有指定自增 id 的值,获取表 t 当前的自增值 2;
    3. 将传入的行的值改成 (2,1,1);
    4. 将表的自增值改成 3;
    5. 继续执行插入数据操作,由于发生 某个唯一索引键冲突 ,所以报 Duplicate key error,语句返回。

    可以看到,这个表的自增值改成 3,是在真正执行插入数据的操作之前。这个语句真正执行的时候,因为碰到唯一键 冲突,所以 id=2 这一行并没有插入成功,但也没有将自增值再改回去。所以,在这之后,再插入新的数据行时,拿到的自增 id 就是 3。这里,出现了自增主键不连续的情况一:唯一键冲突。

    自增键不连续的情况二:事务回滚。

    假设有两个并行执行的事务,在申请自增值的时候,为了避免两个事务申请到相同的自增 id,肯定要加锁,然后顺序申请

    • 假设事务 A 申请到了 id=2, 事务 B 申请到 id=3,那么这时候表的自增值是 4,之后继续执行。
    • 事务 B 正确提交了,但事务 A 出现了唯一键冲突。
    • 如果允许事务 A 把自增 id 回退,也就是把表 的当前自增值改回 2,那么就会出现这样的情况:表里面已经有 id=3 的行,而当前的自增 id 值是 2。
    • 接下来,继续执行的其他事务就会申请到 id=2,然后再申请到 id=3。这时,就会出现插入语句报错“主键冲突”。

    而为了解决这个主键冲突,有两种方法:

    • 每次申请 id 之前,先判断表里面是否已经存在这个 id。如果存在,就跳过这个 id。但是本来申请 id 是一个很快的操作,现在还要再去主键索引树上判断 id 是否存在。
    • 把自增 id 的锁范围扩大,必须等到一个事务执行完成并提交,下一个事务才能再申请自增 id。这个方法的问题,会导致系统并发能力大大下降。

    这两个方法都会导致性能问题,都是由于我们假设的这个“允许自增 id 回退”的前提导致的。

    自增锁

    可以看到,自增 id 锁并不是一个事务锁,而是每次申请完就马上释放,以便允许别的事务再申请。其实,在 MySQL 5.1 版本之前,并不是这样的。

    MySQL 5.0 版本中,自增锁的范围是语句级别,如果一个语句申请了一个表自增锁,这个锁会等语句执行结束以后才释放。这样就影响了并发度。

    MySQL 5.1.22 版本引入了一个新策略,新增参数 innodb_autoinc_lock_mode,默认值是 1。

    参数的值被设置为 0 时,表示采用之前 MySQL 5.0 版本的策略。

    参数的值被设置为 1 时:

    • 普通 insert 语句,自增锁在申请之后就马上释放;
    • 类似 insert … select 这样的批量插入数据的语句,自增锁还是要等语句结束后才被释放;

    参数的值被设置为 2 时,所有的申请自增主键的动作都是申请后就释放锁。

    再详说一下,默认设置下,insert … select 会使用语句级的锁的原因:

    看一下这个场景:

        

    如果 session B 是申请了自增值以后马上就释放自增锁,那么就可能出现这样的情况:

    • session B 先插入了两个记录,(1,1,1)、(2,2,2);
    • 然后,session A 来申请自增 id 得到 id=3,插入了(3,5,5);
    • 之后,session B 继续执行,插入两条记录 (4,3,3)、 (5,4,4)。

    从数据逻辑上看也没多大问题。但是,如果我们现在的 binlog_format=statement,由于两个 session 是同时执行插入数据命令的,所以 binlog 里面对表 t2 的更新日志只有两种情况:要么先记 session A 的,要么先记 session B 的。但不论是哪一种,这个 binlog 拿去从库执行,或者用来恢复临时实例,备库和临时实例里面,session B 这个语句执行出来,生成的结果里面,id 都是连续的。这时,这个库就发生了数据不一致。

    这是因为原库 session B 的 insert 语句,生成的 id 不连续。这个不连续的 id,用 statement 格式的 binlog 来串行执行,是执行不出来的。

    而要解决这个问题,有两种思路:

    • 一种思路是,让原库的批量插入数据语句,固定生成连续的 id 值。自增锁直到语句执行结束才释放,就是为了达到这个目的。
    • 另一种思路是,在 binlog 里面把插入数据的操作都如实记录进来,到备库执行的时候,不再依赖于自增主键去生成。这种情况,其实就是 innodb_autoinc_lock_mode 设置为 2,同时 binlog_format 设置为 row。

    因此,在生产上,尤其是有 insert … select 这种批量插入数据的场景时,从并发插入数据性能的角度考虑,我建议你这样设置:innodb_autoinc_lock_mode=2 ,并且 binlog_format=row. 批量插入数据,包含的语句类型是 insert … select、replace … select 和 load data 语句。

    然而,在普通的 insert 语句里面包含多个 value 值的情况下,即使 innodb_autoinc_lock_mode 设置为 1,也不会等语句执行完成才释放锁。因为这类语句在申请自增 id 的时候,是可以精确计算出需要多少个 id 的,然后一次性申请,再去释放。

    批量插入数据的语句,预先不知道要申请多少个自增 id,那么一种直接的想法就是需要一个时申请一个。但如果一个 select … insert 语句要插入 10 万行数据,就要申请 10 万次。显然,这种申请自增 id 的策略,在大批量插入数据的情况下,不但速度慢,还会影响并发插入的性能。因此,对于批量插入数据的语句,MySQL 有一个批量申请自增 id 的策略:

    • 语句执行过程中,第一次申请自增 id,会分配 1 个;
    • 1 个用完以后,这个语句第二次申请自增 id,会分配 2 个;
    • 2 个用完以后,还是这个语句,第三次申请自增 id,会分配 4 个;
    • 依此类推,同一个语句去申请自增 id,每次申请到的自增 id 个数都是上一次的两倍。

    这是主键 id 出现自增 不连续的第三种原因。

    自增id到达上限

    1. 表定义自增值 id

    表的自增 id 达到上限后,再申请时它的值就不会改变,进而导致继续插入数据时报主键冲突的错误。因此在建表的时候你需要考察你的表是否有可能达到这个上限,如果有可能,就应该创建成 8 个字节的 bigint unsigned

    2. InnoDB系统自增 id

    如果你创建的 InnoDB 表没有指定主键,那么 InnoDB 会给你创建一个不可见的,长度为 6 个字节的 row_id。InnoDB 维护了一个全局的 dict_sys.row_id 值,所有无主键的 InnoDB 表,每插入一行数据,都将当前的 dict_sys.row_id 值作为要插入数据的 row_id,然后把 dict_sys.row_id 的值加 1。

    实际上,在代码实现时 row_id 是一个长度为 8 字节的无符号长整型 (bigint unsigned)。但是,InnoDB 在设计时,给 row_id 留的只是 6 个字节的长度,这样写到数据表中时只放了最后 6 个字节,所以 row_id 能写到数据表中的值,就有两个特征:

    • row_id 写入表中的值范围,是从 0 到 2^48-1;
    • 当 dict_sys.row_id=2^48时,如果再有插入数据的行为要来申请 row_id,拿到以后再取最后 6 个字节的话就是 0。

    在 InnoDB 逻辑里,申请到 row_id=N 后,就将这行数据写入表中;如果表中已经存在 row_id=N 的行,新写入的行就会覆盖原有的行

    从这个角度看,我们还是应该在 InnoDB 表中主动创建自增主键。因为,表自增 id 到达上限后,再插入数据时报主键冲突错误,是更能被接受的。毕竟覆盖数据,就意味着数据丢失,影响的是数据可靠性;报主键冲突,是插入失败,影响的是可用性。而一般情况下,可靠性优先于可用性。

    内容来源:林晓斌《MySQL实战45讲》

    展开全文
  • MyBatis 3.2.6插入时候获取自增主键方法有两种,下面以以MySQL5.5为例通过两种方法给大家介绍mybatis获取自增主键的方法,一起看看吧
  • MySQL8.0 GA版本发布了,展现了众多新特性,下面这篇文章主要给大家介绍了关于MySQL8新特性:自增主键的持久化的相关资料,文中通过示例代码介绍的非常详细,需要的朋友可以参考借鉴,下面随着小编来一起学习学习吧
  • MySQL自增主键auto_increment原理; MySQL的innodb_autoinc_lock_mode参数说明; MySQL的AUTO-INC锁原理; 自增主键出现间隙不连续现象的定位;

    一、背景:

    1.1、业务描述与SQL:

    为了保存机器上报信息(业务需求是每个机器只需保存最新的一条记录),原 SQL 语句如下(其中,machineId 的为唯一索引,t_report_pad 的 id 为 bigint 类型的自增主键):

    insert into t_report_pad
        (machine_id,pad_device_model,app_version,app_version_code,pad_version,pad_version_code,upgrade_status,reason,create_time,update_time) 
    values
        (#{machineId},#{padDeviceModel},#{appVersion},#{appVersionCode},#{padVersion},#{padVersionCode},#{upgradeStatus},#{reason},now(),now())

    这种情况下,当同一个 machine_id 多次发送 SQL 的时候,会造成唯一主键冲突问题,从而导致后面的机器信息添加不了,为了解决这种情况,业务中使用了 insert … on duplocate key update 语句优化原有语句,修改后的 SQL 语句如下,自此业务功能正常使用。

    insert into t_report_pad 
        (machine_id,pad_device_model,app_version,app_version_code,pad_version,pad_version_code,upgrade_status,reason,create_time,update_time)
    values
        (#{machineId},#{padDeviceModel},#{appVersion},#{appVersionCode},#{padVersion},#{padVersionCode},#{upgradeStatus},#{reason},now(),now())
    ON DUPLICATE KEY UPDATE
        pad_device_model=#{padDeviceModel},
        app_version=#{appVersion},
        pad_version=#{padVersion},
        pad_version_code=#{padVersionCode},
        app_version_code=#{appVersionCode},
        upgrade_status=#{upgradeStatus},
        reason=#{reason},
        update_time=now()

    1.2、问题 - 数据类型转换异常:

    运行一段时间之后,发现 t_report_pad 表中的自增主键 id 的值已经超过 int 类型的最大值,并且由于业务代码中使用 int 类型进行接收,从而导致业务功能出现异常(这里代码和 MySQL 中 ID 的类型不一致是因为, DBA 事前已经先修改了 ID 的数据类型,但是开发人员忘记同步修改代码中 Java 对象的类型),于是紧急修复了一个版本解决线上问题。

    1.3、深入分析:

    (1)现象:

    深入分析之后,发现 DB 表中的数据量只有 600W 条,但是自增主键 ID 却高达 21 亿,并且很多自增主键都不连续,那为什么会出现这种现象呢?是不是代码中存在bug?亦或是MySQL 中的自增主键步长设置有问题?亦或是其他?本着打破砂锅问到底的心态,查询多方资料之后,最终终于找到这种现象的原因:对于这种自增主键出现间隙的情况,既不是代码的bug,也不是DB参数设置的问题,而是 mysql 中 为了优化高并发下 auto_increment 自增主键获取的性能,而默认配置的一种算法模式(该算法模式通过 innodb_autoinc_lock_mode 参数配置)导致的,特别是在使用 insert ... on duplicate key update 语法的时候,很容易出现主键间隙,是一种正常的使用现象。

    (2)分析过程:

    ① 分析过程1 - 查看代码中业务逻辑是否正常:查看 SQL 语句中是否手动设置了 ID 列的值,从上面的 SQL 语句可以很容易看出,并没有手动设置 id 的值,默认都是使用自增主键生成的数值。接着查看代码中是否存在删除表中对应的行记录,从而导出出现主键间隙的情况,经查看也不存在。

    ② 分析过程2 - 查看自增主键的步长设置:

    输入:

    show session variables like 'auto_increment%'

    结果如下:

    结果显示 DB 的自增主键的步长设置正常,默认都是自增 1,所以主键间隙问题也不是由这种情况导致的。

    ③ 分析过程3 - 自增主键的算法模式设置(最终定位到的问题所在):

    该参数只要由 innodb_autoinc_lock_mode 参数影响,具体看文章的第二、三部分

     

    二、MySQL 的 auto_increment 详解:

    1、auto_increment 的基本特性:

    MySQL的中 auto_increment 类型的属性主要用于为一个表中记录自动生成 ID。

    (1)当插入记录时,如果为 auto_increment 数据列明确指定了一个数值,则会出现两种情况:

    • 情况一:如果插入的值与已有编号重复,则会出现报错异常,因为 auto_increment 数据列的值必须是唯一的;
    • 情况二:如果插入的值大于已有编号,则会把该插入到数据列中,并使在下一个编号将从这个新值开始递增。也就是说,会跳过一些编号。

    (2)如果自增序列的最大值被删除了,则在插入新记录时,该值被重用。

    (3)如果使用 update 命令更新自增列,列值与已有的值重复,则会出错。如果大于已有值,则下一个编号从该值开始递增。

    2、关于 MySQL 的 auto_increment  所带来的锁表操作:

    (1)在 MySQL 5.1.22 前,MySQL 的 "insert-like" 会在执行语句的过程中使用一个表级的自增锁(AUTO-INC Lock)将整个表锁住,直到整个语句结束(而不是事务结束)。在此期间会阻塞其他的 insert-like、update 等语句,所以推荐使用程序将这些语句分成多条语句,一一插入,减少单一时间的锁表时间。

    insert-like语句:insert,insert … select,replace,replace … select,load data,insert ... values(),values()

    (2)在 MySQL 5.1.22 之后,MySQL 进行了改进,引入了参数 innodb_autoinc_lock_mode,通过这个参数控制 MySQL 的锁表逻辑。

    3、MySQL 的 innodb_autoinc_lock_mode 参数说明:

    innodb_autoinc_lock_mode 参数可用于配置自动增量锁定算法的模式,从而进行在可预测的自动递增值序列和插入操作的最大并发性之间进行权衡。

    3.1、insert 语句的 分类:

    前面提到了 insert-like 语句,就是指所有可以向表中增加行的语句,再进行细分的话,还可以分成三种类型:

    • (1)simple inserts:通过分析 insert 语句可以预先确定要插入的行数,包括 insert、insert ... values()、values() 语句
    • (2)bulk inserts:通过分析 insert 语句不能确定插入数量,包括 insert … select、replace … select、load data 语句。
    • (3)mixed-mode inserts:不确定是否需要分配 auto_increment id,如 insert into t (id,name) values (1,'a'),(null,'b'),(5,'c') 以及 insert… on duplicate key update。对于后者,它的最坏情况实际上是在 insert 语句后面又跟了一个 update,其中 auto_increment 列的分配值不一定会在 update 阶段使用

    3.2、innodb_autoinc_lock_mode 模式说明:

    • 0:这个表示 tradition,即传统模式(每次都会产生表锁)
    • 1:这个表示 consecutive,即连续模式(会产生一个轻量锁,simple insert 会获得批量的锁,保证连续插入)
    • 2:这个表示 interleaved,即交错模式(不会锁表,来一个处理一个,并发最高)

    (1)tradition 模式(innodb_autoinc_lock_mode=0):

    这种方式和 MySQL 5.1.22 之前一样,主要是提供一个向后兼容的能力。在该模式下,所有 insert 语句都要在开始执行的时候获得一个表级的 auto_inc 锁,直到语句结束(不是事务结束)时才释放这把锁,保证自增长值的分配是可预见、连续性、可重复性的,并确保给任何给定语句分配的自动递增值是连续的。但是由于  auto_inc 锁 需要一直保持到语句的结束,造成并发性较差。

    在主从复制的安全性方面,在 statement-based replication 的情况下,这意味着当在从服务器上复制SQL语句时,自动增量列使用与主服务器上相同的值。因为在该模式下,多个INSERT语句的执行结果是确定性的,SLAVE 可以再现与 MASTER 相同的数据,保证基于语句复制的安全性。

    (2)consecutive 模式(innodb_autoinc_lock_mode=1):

    MySQL 5.1.22 之后版本的默认模式,并发度相对加高,这个模式的好处是 auto_inc 锁在特定情况下不需要一直保持到语句的结束,只要语句得到了相应的值后就可以提前释放锁。这种模式下:

    • simple inserts:由于可以预先获得要插入的数量,通过在 mutex 互斥量的控制下获得所需数量的自动递增值来避免表级 auto-inc 锁,然后一次性分配足够的 auto_increment id,只锁住分配id的过程,而不是整个语句的操作过程。
    • bulk inserts:因为不能确定插入的数量,因此使用和以前的模式相同的表级锁定。innoDB 在处理每个SQL时一次性为 auto_increment 列分配新值
    • mixed-mode inserts:通过分析语句获得最坏情况下需要插入的数量,然后一次性分配足够的 auto_increment id,同样只会锁住分配id的过程。但这种方式下,会分配过多的id,而导致“浪费”。
    • 比如 insert into t1 (id,c2) values  (1,’a'), (null,’b'), (5,’c'), (null,’d');会一次性的分配4个id,而不管用户是否指定了部分id;
    • insert … on duplocate key update 一次性分配,而不管将来插入过程中是否会因为 duplicate key 而仅仅执行 update 操作。

    在主从复制的安全性方面,无论是 statement-based 还是 row-based 方式,该模式也都可以保证基于语句的主从复制的安全。

    (3)interleaved 模式(innodb_autoinc_lock_mode=2):

    该模式下,所有的 insert-like 语句都不会使用表级 auto-inc 锁,这种模式是来一个分配一个,只锁住 id 分配的过程,并且可以同时执行多个语句,是性能最好和最可扩展的锁定模式。它和 innodb_autoinc_lock_mode = 1 的区别在于,不会预分配多个。

    由于可以同时为多个语句生成自增长值(即跨语句交叉编号),可能会造成同一个语句插入的行生成的 auto_incremant 值不连续,也就是存在间隙。比如执行 “bulk inserts” 时,则在给任何给定语句分配的自动递增值中可能存在间隙。但是如果执行的是语句是 “simple inserts”,其中要插入的行数可提前知道,那么不会有间隙。

    最后在主从复制replication的安全性方面,当 binlog_format 为 statement-based 时(简称 SBR,statement-based replication),则会存在问题,因为是来一个分配一个,当并发执行时,“bulk inserts” 在分配时会同时向其他的 insert 分配,从而出现主从不一致(从库执行结果和主库执行结果不一样),因为 binlog 只会记录开始的insert id。但是如果 binlog_format 为 row-based 则不会出现问题。

    3.3、innodb_autoinc_lock_mode 的主从安全性问题总结:

    (1)主从的安全性:如果 binlog_format 使用基于行的或混合模式的复制,则所有自动增量锁定模式都是安全的,因为基于行的复制对SQL语句的执行顺序不敏感(混合模式会在遇到不安全的语句是使用基于行的复制模式),所以可以设置 innodb_autoinc_lock_mode = 2 可以获得更好的并发度。但是当 binlog_format 是基于语句复制 statement-base 的情况下,可设置 innodb_autoinc_lock_mode = 1,保证复制安全的同时,获得简单insert语句的最大并发度

    (2)innodb_autoinc_lock_mode 参数的设置是针对 innoDB 存储引擎,在 myisam 引擎情况下,无论什么样自增id锁都是表级锁。

     

    三、导致自增长列存在间隙的情况:

    通过上面文章的描述,最终可以确定导致自增主键出现间隙的原因在于,mixed-mode inserts 中的 insert… on duplicate key update,它的最坏情况实际上是在 insert 语句后面又跟了一个 update,其中 auto_increment 列的分配值不一定会在 update 阶段使用,从而导致自增主键间隙出现。

    1、为了防止出现同样的线上问题,有什么解决方案呢:

    (1)对于旧表的主键类型,在修改 DB 表中的字段类型的时候,必须同步修改代码中的对象属性类型

    (2)后续对于新表的主键类型,直接设置为 bigint,原因在于:

    • 当 mysql 表中的数据量较少时,int 类型的主键 与 bigint 类型的主键,对 DB 的性能与存储压力可以忽略不计。
    • 假如表中的数据比较多但还未达到 int 的最大值 21 亿时,但是出现了 DB 性能或者磁盘存储压力,靠使用 int 代替 bigint 类型带来的提升其实是微乎其微的。
    • 当 mysql 表中的数据量超过21亿时,int 类型的主键升级为 bigint 类型是必须的。

    (3)不使用uuid 的原因:

    一般情况下,MySQL数据库对于主键,推荐使用自增ID,因为在MySQL的 InnoDB 存储引擎中,主键索引是聚簇索引,主键索引的B+树的叶子节点按照顺序存储了主键值及数据,如果主键索引是自增ID,只需要按顺序往后排列即可,如果是UUID,ID是随机生成的,在数据插入时会造成大量的数据移动,产生大量的内存碎片,造成插入性能的下降。除此之外,UUID占用空间较大,建立的索引越多,造成的影响越大。

    2、其他导致自增列存在间隙的情况:

    (1)如果生成自动递增值的事务回滚,那些自动递增值将丢失。 一旦为自动增量列生成了值,无论是否完成 “insert-like” 语句以及包含事务是否回滚,这些值都不能回滚,也就是这种丢失的值不被重用。 因此,存储在表的 auto_increment 列中的值可能存在间隙。

    (2)如果用户在 insert 中为 auto_increment  指定 null 或者 0,InnoDB会将该行视为未指定值,并为其生成新值。

    (3)如果手动为 auto_increment 列分配了一个负值,则不会触发自动增量机制的行为。

    (4)DB 自增主键的步长设置

     

     

    参考文章:

    https://blog.csdn.net/fwkjdaghappy1/article/details/7663331

    https://blog.csdn.net/ashic/article/details/53810319

    展开全文
  • 以前建立自增主键都是用mysql建的,只要写个 auto_increment就行了,最近因为工作,需要用到PostgreSQL的自增主键,就去了解了下。发现网上的这点东西写的真乱…就没有个说是整整齐齐从头到尾说清楚的,没有就只能我...

          
          以前建立自增主键都是用mysql建的,只要写个 auto_increment就行了,最近因为工作,需要用到PostgreSQL的自增主键,就去了解了下。发现网上的这点东西写的真乱…就没有个说是整整齐齐从头到尾说清楚的,没有就只能我自己写了…
          首先,pgsql没有auto_increment这么一说,所以想要建立自增主键就得想想其他办法了

    一、 准备工作

          首先创建一张表:

    create table test(
    	id int primary key,
    	age int
    )
    

          

    二、 第一种方法----创建序列达到自增的效果

    1. 创建序列

          pgsql里,有种东西叫自增,很像mysql里的约束。建立一个自增的序列,每次需要获取自增主键时,调用一下这个序列就可以了。
          建立自增主键的序列的语法:

    CREATE SEQUENCE 
    test_id_seq
    INCREMENT 1
    MINVALUE 1
    MAXVALUE 9223372036854775807
    START WITH 1
    CACHE 1; 
    

          
          INCREMENT BY : 每次序列增加(或减少)的步长
          MINVALUE : 序列最小值,NO MINVALUE表示没有最小值
          MAXVALUE : 序列最大值,NO MAXVALUE表示没有最大值
          START WITH :以什么序列值开始
          CYCLE : 序列是否循环使用
          OWNED BY : 可以直接指定一个表的字段,也可以不指定。
          
          需要自增主键的时候,调用nextval(序列名)就可以了:

    test=#  select nextval('test_id_seq');
     nextval
    ---------
           1
    

    2. 序列的相关方法

          相关的方法如下(regclass 表示序列的名称):

    函 数返 回 类 型描 述
    currval( regclass )bigint获取指定序列最近一次使用netxval后的数值,如果没有使用nextval而直接使用currval会出错。
    lastval()bigint返回最近一次用 nextval 获取的任意序列的数值
    nextval( regclass )bigint递增序列并返回新值
    setval( regclass,bigint )bigint设置序列的当前数值
    setval( regclass,bigint ,boolean )bigint设置序列的当前数值以及 is_called 标志,如果为true则立即生效,如果为false,则调用一次nextval后才会生效

          需要什么值就去找具体的函数就行了。

    3. 插入数据

          假设现在要向test表中插入数据,id自增,则:

    insert into test values(
    	nextval('test_id_seq') , 1 
    )
    

          但是这种操作,每次都得写一遍序列名,很烦。不过有办法解决。看下面:

    4. 设置默认值

          可以设置id字段的默认值为nextval('test_id_seq'),这样插入数据就不用每次都写一遍了。

    alter table 
    	test -- 表名
    alter column 
    	id -- 列名 
    set default nextval(
    	'test_id_seq'  -- 序列名
    );
    

          再向test表中插入数据,这样写就行了:

    insert into test(age) values(12)
    

          或者直接:

    insert into test values(12)
    

          

    三、第二种方法----使用SERIAL

    1. 建表语句

          使用serial时,建表语句需要改一下:

    create table test(
    	id serial primary key,
    	age int
    )
    

          这时候,会自动创建名为表名_字段名_seq的序列,且MAXVALUE=9223372036854775807,其余值为1。
          例如,建立上表以后,则自动创建出名为test_id_seq的序列。
          

    2. 插入数据

          插入数据,这样写就行了:

    insert into test(age) values(12)
    

          或者直接:

    insert into test values(12)
    

          

    3. 如何获取序列的当前值

          使用如下sql可以获取序列当前值:

    select currval('test_id_seq')
    

          一些其他情况可以看这里: 如何获取序列当前值?以及如何解决相关报错

    展开全文
  • SQLServer 中含自增主键的表,通常不能直接指定ID值插入,可以采用以下方法插入。1. SQLServer 自增主键创建语法:identity(seed, increment)其中seed 起始值increment 增量示例:create table student(id int ...
  • Mybatis 获取自增主键

    千次阅读 2022-02-20 16:29:16
    一、MySQL 获取自增主键 若数据库支持自动生成主键(比如 MySQL 和 SQL Server),则可以设置 useGeneratedKeys=“true”,表明使用自增主键获取主键值策略,然后再利用 keyProperty 属性指定对应的主键属性,也就是...
  • MySQL自增主键详解

    千次阅读 2021-01-18 19:35:11
    出现了自增主键不连续的情况 唯一键冲突和事务回滚都会导致自增主键id不连续的情况 四、自增锁的优化 自增id锁并不是一个事务锁,而是每次申请完就马上释放,以便允许别的事务再申请 但在MySQL5.0版本的时候,自增锁...
  • mysql 插入数据并返回自增主键和非自增主键的方式 MyBatis的insert操作,如何在插入数据的同时返回主键呢?分为两种情况,一种情况是该主键是自增主键,另一种情况是该主键非自增,接下来对两种情况详细解释。 mysql...
  • MySQL 自增主键

    千次阅读 2021-01-18 22:23:22
    自增主键有两个性质需要考虑:单调性每次插入一条数据,其 ID 都是比上一条插入的数据的 ID 大,就算上一条数据被删除。连续性插入成功时,其数据的 ID 和前一次插入成功时数据的 ID 相邻。自增主键的单调性为何会有...
  • mysql自增主键详解

    千次阅读 2021-04-08 23:22:30
    文章目录什么是自增主键自增主键的优点好处?自增值保存在哪儿?自增值修改机制自增主键 id不能保证连续的原因?原因一:唯一键冲突原因二:事务回滚原因三:自增锁的优化思考题 什么是自增主键? 当设置了主键...
  • 如果数据大于11000,则设置无效 INNODB自增主键的一些问题 vs mysql获得自增字段下一个值 今天发现 批量插入下,自增主键不连续了....... InnoDB AUTO_INCREMENT Lock Modes This section describes the behavior of...
  • Oracle 创建自增主键

    千次阅读 2022-03-19 15:55:59
    Oracle没有这个auto_increment属性,所以它无法像MySQL般在表内定义自增主键。但是,Oracle里的序列,可间接实现自增主键的作用。 序列: 序列(Sequence),又叫序列生成器,用于提供一系列的数字,开发人员使用...
  • MySQL的自增主键

    2021-08-12 11:27:34
    MySQL8.0之后会将自增主键进行持久化(写入redo log),所以数据库重启后,可以接着表中当前数据行后继续插入自增主键自增主键的计算方法 当用户没有指定插入数据行的主键id时,系统默认用AUTO_INCREMENT的值。 ...
  • 获取自增主键

    2022-02-11 18:50:05
    获取自增主键
  • 我发现我用的 一直是自增主键 而老师他怎么偷偷改了 他把主键自增改成UUID主键了!!!!!!!可恶 自增主键与UUID主键了解(不出BUG谁跑来了解啊!) 当我看了一些关于它俩的博客感悟。。。。。 自增
  • 自增主键自增值的存储MyISAM引擎将当前自增值存储在表数据文件中。InnoDB引擎在5.7及之前将当前自增值存储在内存中,MySQL重启时从表中查询自增列最大值+步长作为当前自增值。InnoDB引擎在8.0及之后版本中将自增值...
  •  设置一张MySQL表,表里有一个自增主键ID,往表里插入数据,假如插入数据之后表后一行的ID是100,我先删除这条ID为100的记录,然后重新启动服务器,按理说如果再往这个表里插入新的记录,新纪录的ID将为101,对吧...
  • mysql修改自增主键类型

    千次阅读 2021-11-19 15:38:27
    今天修改表自增主键数据类型,由int改为bigint,修改完新增数据时报错:"id没有默认值"。原来是因为修改的时候自增给去掉了,要在自增主键的时候加上AUTO_INCREMENT sql alter table 表名 modify column 字段名 ...
  • DB2 的自增主键方式

    2022-06-10 15:49:21
    DB2 的自增主键方式: 1、not null generated by default as identity 不会自增长 一定要指定主键值 2、not null GENERATED ALWAYS AS IDENTITY 自增,不支持指定id值插入 最近在搞DB2数据库的项目: 还是在AS400 ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 124,210
精华内容 49,684
关键字:

自增主键