背景:
自增长是一个很常见的数据属性,在MySQL中大家都很愿意让自增长属性的字段当一个主键。特别是InnoDB,因为InnoDB的聚集索引的特性,使用自增长属性的字段当主键性能更好,这里要说明下自增主键需要注意的几个事项。
问题一:表锁
在MySQL5.1.22之前,InnoDB自增值是通过其本身的自增长计数器来获取值,该实现方式是通过表锁机制来完成的(AUTO-INC LOCKING)。锁不是在每次事务完成后释放,而是在完成对自增长值插入的SQL语句后释放,要等待其释放才能进行后续操作。比如说当表里有一个auto_increment字段的时候,innoDB会在内存里保存一个计数器用来记录auto_increment的值,当插入一个新行数据时,就会用一个表锁来锁住这个计数器,直到插入结束。如果大量的并发插入,表锁会引起SQL堵塞。
在5.1.22之后,InnoDB为了解决自增主键锁表的问题,引入了参数innodb_autoinc_lock_mode,该实现方式是通过轻量级互斥量的增长机制完成的。它是专门用来在使用auto_increment的情况下调整锁策略的,目前有三种选择:
插入类型说明:
INSERT-LIKE:指所有的插入语句,比如 INSERT、REPLACE、INSERT…SELECT、REPLACE…SELECT,LOAD DATA等 Simple inserts:指在插入前就能确定插入行数的语句,包括INSERT、REPLACE,不包含INSERT…ON DUPLICATE KEY UPDATE这类语句。 Bulk inserts:指在插入前不能确定得到插入行的语句。如INSERT…SELECT,REPLACE…SELECT,LOAD DATA. Mixed-mode inserts:指其中一部分是自增长的,有一部分是确定的。
0:通过表锁的方式进行,也就是所有类型的insert都用AUTO-inc locking。
1:默认值,对于simple insert 自增长值的产生使用互斥量对内存中的计数器进行累加操作,对于bulk insert 则还是使用表锁的方式进行。
2:对所有的insert-like 自增长值的产生使用互斥量机制完成,性能最高,并发插入可能导致自增值不连续,可能会导致Statement 的 Replication 出现不一致,使用该模式,需要用 Row Replication的模式。
在mysql5.1.22之前,mysql的INSERT-LIKE语句会在执行整个语句的过程中使用一个AUTO-INC锁将表锁住,直到整个语句结束(而不是事务结束)。因此在使用INSERT…SELECT、INSERT…values(…),values(…)时,LOAD DATA等耗费时间较长的操作时,会将整个表锁住,而阻塞其他的insert-like,update等语句。推荐使用程序将这些语句分成多条语句,一一插入,减少单一时间的锁表时间。
解决:
通过参数innodb_autoinc_lock_mode =1/2解决,并用simple inserts 模式插入。
问题二:自增主键不连续
5.1.22后 默认:innodb_autoinc_lock_mode = 1
直接通过分析语句,获得要插入的数量,然后一次性分配足够的auto_increment id,只会将整个分配的过程锁住。
插入10条记录,但表的AUTO_INCREMENT=16,再插入一条的时候,表的自增id已经是不连续了。
原因:
参数innodb_autoinc_lock_mode = 1时,每次会“预申请”多余的id(handler.cc:compute_next_insert_id),而insert执行完成后,会特别将这些预留的id空出,就是特意将预申请后的当前最大id回写到表中(dict0dict.c:dict_table_autoinc_update_if_greater)。
这个预留的策略是“不够时多申请几个”, 实际执行中是分步申请。至于申请几个,是由当时“已经插入了几条数据N”决定的。当auto_increment_offset=1时,预申请的个数是 N-1。
所以会发现:插入只有1行时,你看不到这个现象,并不预申请。而当有N>1行时,则需要。多申请的数目为N-1,因此执行后的自增值为:1+N+(N-1)。测试中为10行,则:1+10+9 =20,和 16不一致?原因是:当插入8行的时候,表的AUTO_INCREMENT已经是16了,所以插入10行时,id已经在第8行时预留了,所以直接使用,自增值仍为16。所以当插入8行的时候,多申请了7个id,即:9,10,11,12,13,14,15。按照例子中的方法插入8~15行,表的AUTO_INCREMENT始终是16
验证:
插入16行:猜测 预申请的id:1+16+(16-1)= 32,即:AUTO_INCREMENT=32
和猜测的一样,自增id到了32。所以当插入16行的时候,多申请了17,18,19...,31 。
所以导致ID不连续的原因是因为innodb_autoinc_lock_mode = 1时,会多申请id。好处是:一次性分配足够的auto_increment id,只会将整个分配的过程锁住。
5.1.22前 默认:innodb_autoinc_lock_mode = 0
插入10条记录,但表的AUTO_INCREMENT=11,再插入一条的时候,表的自增id还是连续的。
innodb_autoinc_lock_mode = 2 和 innodb_autoinc_lock_mode = 1 的测试情况一样。但该模式下是来一个分配一个,而不会锁表,只会锁住分配id的过程,和1的区别在于,不会预分配多个,这种方式并发性最高。但是在replication中当binlog_format为statement-based时存在问题
解决:
尽量让主键ID没有业务意义,或则使用simple inserts模式插入。
结论:
当innodb_autoinc_lock_mode为0时候, 自增id都会连续,但是会出现表锁的情况,解决该问题可以把innodb_autoinc_lock_mode 设置为1,甚至是2。会提高性能,但是会在一定的条件下导致自增id不连续。
总结:
通过上面2个问题的说明,自增主键会产生表锁,从而引发问题;自增主键有业务意义,不连续的主键导致主从主键不一致到出现问题。对于simple inserts 的插入类型,上面的问题都不会出现。对于Bulk inserts的插入类型,会出现上述的问题。
更多信息:
http://dinglin.iteye.com/blog/1279536
http://dev.mysql.com/doc/refman/5.5/en/innodb-auto-increment-handling.html
相关推荐
1.InnoDB存储引擎的数据结构必须需要一个主键才可以组织起来,如果用户使用InnoDB存储引擎建立表的时候,没有指定...使用自增主键作为InnoDB表的主键会存在一个问题? Mysql中innodb_page_size = 16kb,选择BIGINT作为主
设置一张MySQL表,表里有一个自增主键ID,往表里插入数据,假如插入数据之后表后一行的ID是100,我先删除这条ID为100的记录,然后重新启动服务器,按理说如果再往这个表里插入新的记录,新纪录的ID将为101,对吧...
1、一张表,里面有 ID 自增主键,当 insert 了 17 条记录之后,删除了第 15,16,17 条记录, 再把 Mysql 重启,再 insert 一条记录,这条记录的 ID 是 18 还是 15 ? (1)如果表的类型是 MyISAM,那么是 18 因为 ...
1、一张表,里面有 ID 自增主键,当 insert 了 17 条记录之后,删除了第 15,16,17 条记录,再把 Mysql 重启,再 insert 一条记录,这条记录的 ID 是 18 还是 15 ? (1) 如果表的类型是 MyISAM,那么是 18 因为 ...
因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件里,重启 MySQL 自增主键的最大ID 也不会丢失 (2)如果表的类型是 InnoDB,那么是 15 InnoDB 表只是把自增主键的最大 ID 记录到内存中,所以重启数据库或者是...
1、一张表,里面有 ID 自增主键,当 insert 了 17 条记录之后,删除了第 15,16,17 条记录,再把 Mysql 重启,再 insert 一条记录,这条记录的 ID 是 18 还是 15 ? (1)如果表的类型是 MyISAM,那么是 18 因为 MyISAM...
最近生产环境一套数据库因为疯狂写日志数据,造成主键值溢出的情况出现,因此有必要将这个指标监控起来。 mysqld_exporter自带的这个功能,下面是我使用的启动参数: nohup ./mysqld_exporter –config.my-cnf=”./...
因为 MyISAM 表会把自增主键的最大 ID 记录到数据文件里,重启 MySQL 自增主键的最大ID 也不会丢失 (2)如果表的类型是 InnoDB,那么是 15 InnoDB 表只是把自增主键的最大 ID 记录到内存中,所以重启数据库或者是...
在MySQL中,InnoDB数据表的主键设计我们通常遵循几个原则: 1、采用一个没有业务用途的自增属性列作为主键; 2、主键字段值总是不更新,只有新增或者删除两种操作; 3、不选择会动态更新的类型,比如当前时间戳等。 ...
在使用MySQL建表时,我们通常会创建一个自增字段(AUTO_INCREMENT),并以此字段作为主键。本篇文章将以问答的形式讲述关于自增id的一切。 注: 本文所讲的都是基于Innodb存储引擎。 下面话不多说了,来一起随着小编...
1、一张表,里面有 ID 自增主键,当 insert 了 17 条记录之后,删除了第 15,16,17 条记录, 再把 Mysql 重启,再 insert 一条记录,这条记录的 ID 是 18 还是 15 ? 2、Mysql 的技术特点是什么? 3、Heap 表是什么?...
一、数据库自增主键可能遇到什么问题 二、MySQL中InnoDB引擎的行锁是怎么实现的? 三、MySQL有关权限的表都有哪几个?
1、一张表,里面有 ID 自增主键,当 insert 了 17 条记录之后,删除了第 15,16,17 条记录,再把 Mysql 重启,再 insert 一条记录,这条记录的 ID 是 18 还是 15 2、Mysql 的技术特点是什么 3、Heap 表是什么 4、...
6.如果把一个 InnoDB 表的主键删掉,是不是就没有主键,就没办法进行回表查询了? 7.一张自增表中有三条数据,删除两条数据之后重启数据库,再新增一条数据,此时这条数据的 ID 是几? 8.什么是独立表空间和共享表...
SQL Server 平台修改自增列值 由于之前处理过sql server数据库的迁移工作,尝试过其自增列...如果在生成环境修改自增列的话,建议在空闲时间(零点以后,平台或网站使用的用户很少的时间段)来处理这类问题。数据量大
导读:在使用MySQL建表时,我们通常会创建一个自增字段(AUTO_INCREMENT),并以此字段作为主键。本篇文章将以问答的形式讲述关于自增id的一切。 注: 本文所讲的都是基于Innodb存储引擎。 1.MySQL为什么建议将自增列...
主键索引 主键索引,简称主键,原文是PRIMARY KEY,由一个或多个列组成,用于唯一性标识数据表中的某一条记录。...可以参考以往的分享“[MySQL FAQ]系列 — 为什么InnoDB表要建议用自增列做主键”; 业务数
1、一张表,里面有ID自增主键,当insert了17条记录之后,删除了第15,16,17条记录,再把Mysql重启,再insert一条记录,这条记录的ID是18还是15 ? 2、Mysql的技术特点是什么? Mysql数据库软件是一个客户端或服务器...
3、InnoDB 为何推荐使用自增主键? 自增 ID 可以保证每次插入时 B+ 树索引是从右边扩展的,因此相比自定义 ID (如 UUID)可以避免 B+ 树的频繁合并和分裂。如果使用字符串主键和随机主键,会使得数据随机插入,效率...
1、为什么要尽量设定一个主键?...总之,在数据量大一些的情况下,用自增主键性能会好一些。 3、字段为什么要求定义为not null? null值会占用更多的字节,且会在程序中造成很多与预期不符的情况。