聚簇索引索引覆盖分析
聚簇索引
优势: 根据主键查询条目比较少时,不用回行(数据就在主键节点下)
劣势: 如果碰到不规则数据插入时,造成频繁的页分裂.
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数 |
|
1 |
1 |
54.365 |
53.438 |
62 |
91 |
|
10 |
1 |
53.413 |
62.940 |
235 |
1301 |
|
10 |
100 |
|
64.18 |
|
1329 |
|
10 |
1000 |
|
67.512 |
|
1325 |
通过上面的规律可以看出-----
1: innodb的buffer_page 很强大.
2: 聚簇索引的主键值,应尽量是连续增长的值,而不是要是随机值,
(不要用随机字符串或UUID)
否则会造成大量的页分裂与页移动.
高性能索引策略
0:对于innodb而言,因为节点下有数据文件,因此节点的分裂将会比较慢.
对于innodb的主键,尽量用整型,而且是递增的整型.
如果是无规律的数据,将会产生的页的分裂,影响速度.
索引覆盖:
索引覆盖是指 如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据.
这种查询速度非常快,称为”索引覆盖”
