mysql innodb myisam 插入数据_innodb聚簇索引(关于innodb插入数据较myisam慢的原因)

聚簇索引、索引覆盖、innodb和myisam索引的区别

innodb(聚簇索引)的次索引指向对主键的引用，myisam的次索引和主索引都指向物理行。

聚簇索引的好处：根据主键查找效率高；缺点：其他字段的查找慢些

(而innodb的主键下就是数据，而其他字段指向主键的位置)

innodb的主索引文件上 直接存放该行数据,称为聚簇索引,次索引指向对主键的引用

myisam中,主索引和次索引,都指向物理行(磁盘位置).

注意: innodb来说,

1: 主键索引 既存储索引值,又在叶子中存储行的数据

2: 如果没有主键,则会Unique key做主键

3: 如果没有unique,则系统生成一个内部的rowid做主键.

4: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”

聚簇索引

优势: 根据主键查询条目比较少时,不用回行(数据就在主键节点下)

劣势: 如果碰到不规则数据插入时,造成频繁的页分裂.

(对于innodb(聚簇索引)，节点下存储了行数据，分裂的时候还要移动行数据)

C) 聚簇索引的页分裂过程

实验: 聚簇索引使用随机值导致页频繁分裂影响速度

过程:建立innodb表,利用php连接mysql,

分别规则插入10000条数据,不规则插入10000条数据

观察时间的差异,体会聚簇索引,页分裂的影响.

create table t5(

id int primary key,

c1 varchar(500),

c2 varchar(500),

c3 varchar(500),

c4 varchar(500),

c5 varchar(500),

c6 varchar(500)

) engine innodb charset utf8;

create table t6(

id int primary key,

c1 varchar(500),

c2 varchar(500),

c3 varchar(500),

c4 varchar(500),

c5 varchar(500),

c6 varchar(500)

) engine innodb charset utf8;

// testinnodb.php

$time_start = microtime_float();

$str = str_repeat('hello',100);

for($i=1;$i<=10000;$i++) {

$sql = "insert into t5 values ($i,'$str' , '$str' , '$str' , '$str' , '$str' , '$str'

)";

//echo $sql;

mysql_query($sql , $conn);

}

$time_end = microtime_float();

echo 'seq insert cost' , ($time_end - $time_start) , "seconds\n";

function microtime_float()

{

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

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

}

// rndinnodb.php

$base = range(1,10000);

shuffle($base);

$time_start = microtime_float();

$str = str_repeat('hello',100);

foreach($base as $i) {

$sql = "insert into t6 values ($i,'$str' , '$str' , '$str' , '$str' , '$str' , '$str'

)";

//echo $sql;

mysql_query($sql , $conn);

}

$time_end = microtime_float();

echo 'rand insert cost' , ($time_end - $time_start) , "seconds\n";

function microtime_float()

{

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

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

}

字段数混乱程度(步长)顺序1000条(秒数)乱序1000条(秒数)顺序写入page页数乱序写入page数

1154.36553.4386291

10153.41362.9402351301

1010064.181329

10100067.5121325

通过上面的规律可以看出-----

1: innodb的buffer_page很强大.

2: 聚簇索引的主键值,应尽量是连续增长的值,而不是要是随机值,

(不要用随机字符串或UUID)

否则会造成大量的页分裂与页移动.高性能索引策略

0:对于innodb而言,因为节点下有数据文件,因此节点的分裂将会比较慢.

对于innodb的主键,尽量用整型,而且是递增的整型.

如果是无规律的数据,将会产生的页的分裂,影响速度.