Mysql InnoDB为什么要添加跟业务无关的自增主键
Mysql InnoDB为什么要添加跟业务无关的自增主键
开元中国2015 发表于3年前
Mysql InnoDB为什么要添加跟业务无关的自增主键
  • 发表于 3年前
  • 阅读 114
  • 收藏 1
  • 点赞 0
  • 评论 0

腾讯云 技术升级10大核心产品年终让利>>>   

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
 */



参考来源: 
Mysql InnoDB为什么要添加跟业务无关的自增主键
http://www.lai18.com/content/422501.html

标签: MySQL innoDB
共有 人打赏支持
粉丝 32
博文 104
码字总数 112959
×
开元中国2015
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: