INNODB引擎下auto

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

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

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

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

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

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

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

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

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

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

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

两种情况做了一个测试。

表结构如下：

CREATE TABLE m_a (

message_id INTAUTO_INCREMENT NOT NULL PRIMARY KEY,

uid INT NOT NULL,

subject VARCHAR(256),

content VARCHAR(5000),

ctime TIMESTAMP NOTNULL,

KEY(uid)

) ENGINE=innodb

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

 

 

CREATE TABLE m_c (

uid INT NOT NULL,

message_id INT NOTNULL,

subject VARCHAR(256),

content VARCHAR(5000),

ctime TIMESTAMP NOTNULL,

PRIMARY KEY(uid,message_id)

) ENGINE=innodb

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

 

使用如下语句进行查询：

SELECTmessage_id,subj,content,ctime FROM m_a(or m_c) WHERE uid=? LIMIT100

测试的结果如下：

   这个测试结果仅供参考。

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