mysql自增主键与业务无关_Mysql InnoDB为什么要添加跟业务无关的自增主键

在Mysql表设计中，通常会使用一个与业务无关的自增列做为主键。

这是因为Mysql默认使用B-Tree索引，你可以简单理解为“排好序的快速查找结构”。

如下是一个B-Tree的结构图，2层B+树，每个页面的扇出为4；并有1到6五条记录；上层记录保存每个页面的最小值；每个页面通过双向链表链接起来的；

当你插入记录7时，就会发生页面分裂：

如上可见分裂产生了记录移动，但是优化后的分裂操作无需记录移动：

在InnoDB的实现中，为每个索引页面维护了一个上次插入的位置，以及上次的插入是递增/递减的标识。根据这些信息，InnoDB能够判断出新插入到页面中的记录，是否仍旧满足递增/递减的约束，若满足约束，则采用优化后的分裂策略；

所以建议使用一列顺序递增的 ID 来作为主键，但不必是数据库的autoincrement字段，只要满足顺序增加即可 。很多大型应用会有顺序递增的ID生成器。

测试如下：

CREATE TABLE `table1` (

`id` int(10) NOT NULL AUTO_INCREMENT,

`text` varchar(255) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=200001 DEFAULT CHARSET=utf8

CREATE TABLE `table2` (

`id` int(10) NOT NULL,

`text` varchar(255) NOT NULL,

KEY `id` (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

脚本如下：

$link = mysql_connect('127.0.0.1', 'root', 'mckee');

mysql_select_db('test', $link);

$count = 200000;

$table1_data = range(1, $count);

$text = 'just test!just test!just test!just test!just test!';

$time1 = get_time();

foreach ($table1_data as $row) {

$id = rand(1,100000000);

mysql_query("insert into table1(text) values ('{$text}')");

}

$time2 = get_time();

foreach ($table1_data as $row) {

$id = rand(1,100000000);

mysql_query("insert into table2(id, text) values ({$id}, '{$text}')");

}

$time3 = get_time();

echo 'tabe1 insert execute time:' . ($time2 - $time1) . PHP_EOL;

echo 'tabe2 insert execute time:' . ($time3 - $time2) . PHP_EOL;

function get_time()

{

list( $usec , $sec ) = explode ( " " , microtime ());

return ((float) $usec + (float) $sec );

}

/*

运行结果

tabe1 insert execute time:267.36530303955

tabe2 insert execute time:304.34842610359

*/

相关文章