聚簇索引

最新推荐文章于 2022-10-07 20:58:47 发布

freedomai

最新推荐文章于 2022-10-07 20:58:47 发布

阅读量636

点赞数

分类专栏： mysql优化

本文链接：https://blog.csdn.net/freedomai/article/details/45125965

版权

mysql优化专栏收录该内容

19 篇文章 0 订阅

订阅专栏

聚簇索引与非聚簇索引

Myisam与innodb引擎,索引文件的异同

innodb的主索引文件上直接存放该行数据,称为聚簇索引,次索引指向对主键的引用
myisam中, 主索引和次索引,都指向物理行(磁盘位置).
PS：比如一个表id为主键，name为普通索引，where id = x and name='x'

innodb会先查找主键（id）的位置，然后再查找普通索引（name）的位置，最后根据普通索引直接定位到主键上

myisam则先查找主键的磁盘位置，然后再查找普通索引的磁盘位置，需要定位两次磁盘位置，而innodb只需要定位一次磁盘位置（即主键位置）

注意: innodb来说,
1: 主键索引既存储索引值,又在叶子中存储行的数据
2: 如果没有主键, 则会Unique key做主键
3: 如果没有unique,则系统生成一个内部的rowid做主键.
4: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”

聚簇索引
优势: 根据主键查询条目比较少时,不用回行(数据就在主键节点下)
劣势: 如果碰到不规则数据插入时,造成频繁的页分裂.

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

实验: 聚簇索引使用随机值导致页频繁分裂影响速度
过程:建立innodb表, 利用php连接mysql,
分别规则插入10000条数据,不规则插入10000条数据
观察时间的差异,体会聚簇索引,页分裂的影响.

<span style="font-size:18px;">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;</span>

<span style="font-size:18px;">// 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);
}</span>