分享更有价值
被信任是一种快乐

Mysql中INNODB自增主键的问题有哪些

文章页正文上

小编给大家分享一下Mysql中INNODB自增主键的问题有哪些,相信大部分人都还不怎么了解,因此分享这篇文章给大家参考一下,希望大家阅读完这篇文章后大有收获,下面让我们一起去了解一下吧!背景:
自增长是一个很常见的数据属性,在MySQL中大家都很愿意让自增长属性的字段当一个主键。特别是InnoDB,因为InnoDB的聚集索引的特性,使用自增长属性的字段当主键性能更好,这里要说明下自增主键需要注意的几个事项。 问题一:表锁

在MySQL5.1.22之免费主机域名前,InnoDB自增值是通过其本身的自增长计数器来获取值,该实现方式是通过表锁机制来完成的(AUTO-INC LOCKING)。锁不是在每次事务完成后释放,而是在完成对自增长值插入的SQL语句后释放,要等待其释放才能进行后续操作。比如说当表里有一个auto_increment字段的时候,innoDB会在内存里保存一个计数器用来记录auto_increment的值,当插入一个新行数据时,就会用一个表锁来锁住这个计数器,直到插入结束。如果大量的并发插入,表锁会引起SQL堵塞。insert into…..select大量插入数据的性能也比较差 在5.1.22之后,InnoDB为了解决自增主键锁表的问题,引入了参数innodb_autoinc_lock_mode,该实现方式是通过轻量级互斥量的增长机制完成的。它是专门用来在使用auto_increment的情况下调整锁策略的,目前有三种选择:
插入类型说明: 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
为了发现规律,这儿我做了实验,不是很准确,插入行数与对应的autocommit分别是 2》4 3》4 4-7》8 8-15》16 16-31》32),只能说AUTO_INCREMENT有可能是2n(具体什么时候是2n还没发现规律),范围应该是[n,2n]之间
验证:
插入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不连续。 以上是“Mysql中INNODB自增主键的问题有哪些”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注云技术行业资讯频道!

相关推荐: mysql如何实现查表空间大小的SQL语句

这篇文章将为大家详细讲解有关mysql如何实现查表空间大小的SQL语句,小编觉得挺实用的,因此分享给大家做个参考,希望大家阅读完这篇文章后可以有所收获。 SQL语句如下:select concat(truncate(sum(data_length)/1024/…

文章页内容下
赞(0) 打赏
版权声明:本站采用知识共享、学习交流,不允许用于商业用途;文章由发布者自行承担一切责任,与本站无关。
文章页正文下
文章页评论上

云服务器、web空间可免费试用

宝塔面板主机、支持php,mysql等,SSL部署;安全高速企业专供99.999%稳定,另有高防主机、不限制内容等类型,具体可咨询QQ:360163164,Tel同微信:18905205712

主机选购导航云服务器试用

登录

找回密码

注册