INNODB引擎下auto_increment的应用

最新推荐文章于 2022-07-10 20:55:58 发布

CareChere

最新推荐文章于 2022-07-10 20:55:58 发布

阅读量914

点赞数

分类专栏： MySQL

MySQL 专栏收录该内容

44 篇文章 0 订阅

订阅专栏

在Innodb engine的primary key是 clustered index,这种索引是不同于其他索引，它的查询效率非常高，它指向的就是对应的row data, 其他索引即secondary index里面也存有primary key(clustered index)的列数据，在查询时，是先利用sencondary index找到primary key,然后在利用primary key找到row data,这个也解释了为什么在innodb engine里primary key效率非常高的原因，同时也说明了为什么innodb engine表的数据文件比myisam engine表大的原因之一。

在innodb engine中很多人都喜欢使用auot_increment做primary key。我个人持否定态度，甚至有点讨厌使用自增。因为当一个innodb engine的表里有一个auto_increment字段的时候，innodb engine会在内存里保存一个计数器用来记录auto_increment的值，当插入一个新行数据时，就会用一个table lock来锁住这个计数器，直到插入操作完成。如果是一行一行的插入数据基本上没有什么问题，但是如果大量的并发插入就会因为产生的表锁导致SQL语句堵塞，不仅使效率很低，而且可能会瞬间达到max_connections而导致数据crash。

这里总结一下我个人讨厌使用自增作为主键的主要原因：

1.自增在很多情况下没有意义，业务的查询语句不用使用这个自增来查询，浪费了作为clustered index这个索引的好处。

2.自增作为主键容易产生auto_inc lock，虽然innodb engine是采用的基于mvcc的row lock,但在高并发时这个auto_inc lock反而会影响并发，auto_inc lock是table lock，这个table lock是在一个SQL语句结束才释放，而不是在一个事务结束了释放。有些资料显示在线程个数大约10时这个锁将成为这个表的瓶颈。在mysql5.1.22之后的版本可以通过innodb_autoinc_lock_mode这个参数来调节锁策略。

但不可否认在某些情况使用自增效果很好，毕竟合适的才是最好的。

下面两种情况可以考虑使用自增作为主键：

1. 如果表中没有一个字段值有唯一需求，那么这时候可以考虑增加一个自增列来做主键。

2. 表中有某些字段有唯一性要求，但这些字段的数据类型定义为字符串类型（包括：char, varchar, text, blob）,并且这个字符串定义的长度较长（大于255个字符，例如：char(256)）。

需要说明的是：对于第一种情况，也可以不定义主键，innodb engine会自动使用隐式的row id来作为主键，这个row id类似一个自增。对于第二种情况，必须定义主键，因为不定义的话，innodb engine会按照次序把第一个unique key作为主键，即为clustered index, 这样就又会出现上面说的问题。反之，不建议使用主键。

使用自增的最大好处就是能减少表文件的大小，如果使用了一个很大的字符串做primary key,再加上有很多的secondary key,你会发现次表数据文件大小和myisam engine相比有指数级的增加。

两种情况做了一个测试。

表结构如下：

CREATE TABLE m_a (

message_id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,

uid INT NOT NULL,

subject VARCHAR(256),

content VARCHAR(5000),

ctime TIMESTAMP NOT NULL,

KEY(uid)

) ENGINE=innodb

利用自增作为primary key，建立一个secondary key: uid进行测试。

CREATE TABLE m_c (

uid INT NOT NULL,

message_id INT NOT NULL,

subject VARCHAR(256),

content VARCHAR(5000),

ctime TIMESTAMP NOT NULL,

PRIMARY KEY(uid, message_id)

) ENGINE=innodb

把uid和message_id作为primary key.进行测试。

使用如下语句进行查询：

SELECT message_id,subj,content,ctime FROM m_a(or m_c) WHERE uid=? LIMIT 100

测试的结果如下：

这个测试结果仅供参考。

从上面的测试结果可以看出，利用primary key(uid，message_id)的查询速度比使用primary key(message_id)自增快了很多倍。但插入速度慢了很多，主要原因是uid和message_id作为primary key时，在插入一条数据时都要对B-TREE进行更新，消耗的资源比较多，同时clustered index是按照这两列的order存储的，会较上一种产生更多的随机IO，虽然logfile能通过合并数据缓解一些。总之一句话，根据自己的实际情况来选择是否用自增来作为primary key。重复之前那句话，适合的就是最好的。

InnoDB的auto_increment指定值被重置的解决方法

有时候新建的表需要对自增列指定初始值，但是有时候会出现明明指定过的初始值却被重置的现象。下面以一个小实验来说明这个问题：

MySQL version:5.1.42 OS:redhat5.3

无废话，建张表先：

代码
CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=20000000 DEFAULT CHARSET=latin1;

诸位看官可以看见我指定了auto_increment=20000000。

表建好后，如果紧接着插数据，那么此现象是不会出现的:

代码
mysql> insert into sbtest1(k,pad) values(123,'what are you doing');
Query OK, 1 row affected (0.00 sec)

mysql> select * from sbtest1;
+----------+-----+---+--------------------+
| id | k | c | pad |
+----------+-----+---+--------------------+
| 20000000 | 123 | | what are you doing |
+----------+-----+---+--------------------+
1 row in set (0.00 sec)

那什么时候？嗯，确切地说是做什么样的操作，重置现象才会出现呢？看实验：

mysql> quit
Bye
[root@test_2 ~]# service mysqld restart
mysql> show create table sbtest1 \G
*************************** 1. row ***************************
Table: sbtest1
Create Table: CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

这时，auto_increment的值被重置了！

通过以上的小实验，结合手册就能理解为什么会发生这种现象了：

如果对某张innodb表指定了auto_increment,那么innodb就会在data dictionary为它维护一个auto_increment计数器，注意：这个计数器只存储在内存中，不会写在disk上。
那么 innodb在DB重启后，如何对这个计数器进行初始化呢（结合实验中的这张表来说明）？
InnoDB在数据库重新启动后，它会对指定过 auto_increment的表(sbtest1)做这样一个操作：

SELECT MAX(id) FROM sbtest1 FOR UPDATE;
然后将这条语句取得的值+1赋给被指定auto_increment的字段和在内存中的这张表的计数器。如果表是空的，那么这个值将会是1.

解决方法：
1.重建完表后，插入数据之前不要重启（说白了，就是要保证内存的数据不会释放）
2.插入一条脏数据