本文主要说明MySQL Innodb存储引擎要使用与业务无关的自增ID做主键的原因，测试方法和结果。

欢迎转载，请注明作者、出处。

作者：张正

blog：http://space.itpub.net/26355921

QQ：176036317

如有疑问，欢迎联系。

各位dba童鞋有没有碰到一件事情，就是开发人员经常会拿一些与业务有关的字段作为innodb表的主键，很多时候，这个业务其实完全可以改为 使用与业务无关的自增ID作为主键，你这样推荐之后，开发人员可能会问你：为什么？你如果自己也不是太清楚，或者说服力不够，开发人员很可能就懒得改了。
看看本文对这个事情，做一个简单的说明吧。看完之后你就清楚地知道为什么了。

一、首先解释如下：（以下中文解释参考wjlcn：）
InnoDB引擎使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。
如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如下图所示：

这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置：

  此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构。

二、接下来看看官方文档的解释：
All InnoDB indexes are B-trees where the index records are stored in the leaf pages of the tree. The default size of an index page is 16KB. When new records are inserted, InnoDB tries to leave 1/16 of the page free for future insertions and updates of the index records.
  If index records are inserted in a sequential order (ascending or descending), the resulting index pages are about 15/16 full. If records are inserted in a random order, the pages are from 1/2 to 15/16 full. If the fill factor of an index page drops below 1/2, InnoDB tries to contract the index tree to free the page.

三、测试脚本
既然看到以上的结论，相信大家对原理已经明白了，但是具体相差多少呢。我这里使用2个python脚本做了点简单的测试，大体上能看出来区别。

  测试的表结构如下：
CREATE TABLE `sbtest` (
   `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=1 DEFAULT CHARSET=utf8;

a、主键顺序插入：

from tmpdb import query_mgr #连接数据的
import random

a=[]
for i in range(1,100001):
a+=[i]

for i in range(0,100000):
id=random.randrange(0,len(a))
sql="insert into sbtest values(%d,0,'aaa','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt')" %a[0]
a.remove(a[0])
query_mgr(sql)

b、主键随机插入：
如果主键是与业务有关的字段，那么插入的值如果随机性越大，那么insert的效果越差，因此我这里就是用random随机值来插入，这样更能凸显出效果来。

from tmpdb import query_mgr #连接数据的
import random

a=[]
for i in range(1,100001):
a+=[i]

for i in range(0,100000):
id=random.randrange(0,len(a))
sql="insert into sbtest2 values(%d,0,'aaa','qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt')" %a[id]
a.remove(a[id])
query_mgr(sql)

两个脚本是为了尽量保持一致，让python本身消耗的时间尽量一致。这样才能看出来在mysql层处理的差异。（两个脚本之间可能还是存在一定效率差异，但是本文只是做一个简单的测试，看看效果，足矣）

四、测试结果
插入的10W数据，虽然不多，但是效果很明显了，经过在相同的环境中多次测试，得出如下结果：

	消耗耗时
主键顺序插入	3min17s
主键随机插入	5min30s

MySQL Innodb使用与业务无关的自增id做主键，原因及测试方法、测试结果