mysql的有序插入研究

最新推荐文章于 2021-08-07 22:56:02 发布

原创最新推荐文章于 2021-08-07 22:56:02 发布 · 1.8k 阅读

0 ·

CC 4.0 BY-SA版权

本文主要探讨了在InnoDB存储引擎下，进行有序插入的原理和性能表现。通过对比实验，发现尽管有序插入减少了数据移动，但由于InnoDB主键的聚簇索引特性，行数据与叶子节点的物理存储可能存在不一致，这需要进一步研究源代码以了解详情。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

要测试mysql的有序插入，是基于innodb存储引擎的，先设计以下两表进行比较，测试代码是一致的。下表的插入是属于有序插入，即不会移动数据项，因为innodb的主键是属于聚簇索引。

create table test_insert_tbl(uid int primary key, age int);
create table test_insert_tbl(id int auto_increment primary key, uid int, age int, unique key(uid));

首先先建立测试原始数据，共200万条，代码如下，上表的插入时间显著快于下表的时间。

<?php
$dsn = "mysql:host=localhost;dbname=test";
$db = new PDO($dsn, 'root', '');
$db->exec('set autocommit = 0');
$db->exec('set innodb_additional_mem_pool_size = 512M');
$sql = "insert into test_insert_tbl(uid, age) values ";
for ($i = 0; $i < 1000; ++$i) {
        $content = '';
        for ($j = 0; $j < 1000; ++$j) {
                $id = $i*1000 + $j;
                if (0 == $j)
                        $content = "($id,18)";
                else
                        $content .= ",($id,18)";
        }
        $db->exec($sql.$content);
}
for ($i = 0; $i < 1000; ++$i) {
        $content = '';
        for ($j = 0; $j < 1000; ++$j) {
                $id = $i*1000 + $j + 5000000;
                if (0 == $j)
                        $content = "($id,18)";
                else
                        $content .= ",($id,18)";
        }
        $db->exec($sql.$content);
}
$db = null;

然后是测试代码，如下

<?php
$i = 0;
$arr = array();
while ($i < 1000) {
        $id = rand(3000000, 4000000);
        if (!isset($arr[$id])) {
                $arr[$id] = 1;
                ++$i;
        }
}
//echo count($arr);
$t1 = microtime(true);
$dsn = "mysql:host=localhost;dbname=test";
$db = new PDO($dsn, 'root', '');
foreach ($arr as $key => $value) {
        $db->exec("insert into test_insert_tbl(uid, age) values($key, $value)");
}
$db = null;
$t2 = microtime(true);
echo '耗时'.round($t2-$t1,3).'秒';

执行时间结果

	上表	下表
时间	31.741秒	30.824秒

相差无几，可能是索引的移动也是需要操作，与移动数据项的时间差不多，所以想添加表的列数。新表加了新的数据项。

create table test_insert_tbl(uid int primary key, age int, name char(20) not null default 'abb', sex char(6) not null default 'male', memo char(255) not null default 'a', memo2 char(255) not null default 'a');
create table test_insert_tbl(id int auto_increment primary key, uid int, age int, name char(20) not null default 'abb', sex char(6) not null default 'male', memo char(255) not null default 'a', memo2 char(255) not null default 'a', unique key(uid));

现在的执行时间