Jquery中文网 www.jquerycn.cn
Jquery中文网 >  数据库  >  mysql  >  正文 MySQL innodb 聚集索引的概念与使用教程

MySQL innodb 聚集索引的概念与使用教程

发布时间:2017-12-12   编辑:www.jquerycn.cn
jquery中文网为您提供MySQL innodb 聚集索引的概念与使用教程等资源,欢迎您收藏本站,我们将为您提供最新的MySQL innodb 聚集索引的概念与使用教程资源
聚集索引是指数据库表行中数据的物理顺序与键值的逻辑(索引)顺序相同。一个表只能有一个聚集索引,因为一个表的物理顺序只有一种情况,所以,对应的聚集索引只能有一个。

在MySQL中,InnoDB引擎表是(聚集)索引组织表(clustered index organize table),而MyISAM引擎表则是堆组织表(heap organize table)。

也有人把聚集索引称为聚簇索引。

当然了,聚集索引的概念不是MySQL里特有的,其他数据库系统也同样有。

简言之,聚集索引是一种索引组织形式,索引的键值逻辑顺序决定了表数据行的物理存储顺序,而非聚集索引则就是普通索引了,仅仅只是对数据列创建相应的索引,不影响整个表的物理存储顺序。

我们先来看看两种存储形式的不同之处:

简单说,IOT表里数据物理存储顺序和主键索引的顺序一致,所以如果新增数据是离散的,会导致数据块趋于离散,而不是趋于顺序。而HOT表数据写入的顺序是按写入时间顺序存储的。

IOT表相比HOT表的优势是:

范围查询效率更高;

数据频繁更新(聚集索引本身不更新)时,更不容易产生碎片;

特别适合有一小部分热点数据频繁读写的场景;

通过主键访问数据时快速可达;

IOT表的不足则有:

数据变化如果是离散为主的话,那么效率会比HOT表差;

HOT表的不足有:

索引回表读开销很大;

大部分数据读取时随机的,无法保证被顺序读取,开销大;

每张InnoDB表只能创建一个聚集索引,聚集索引可以由一列或多列组成。

上面说过,InnoDB是聚集索引组织表,它的聚集索引选择规则是这样的:

首先选择显式定义的主键索引做为聚集索引;

如果没有,则选择第一个不允许NULL的唯一索引;

还是没有的话,就采用InnoDB引擎内置的ROWID作为聚集索引;

我们来看看InnoDB主键索引的示意图:

可以看到,在这个索引结构的叶子节点中,节点key值是主键的值,而节点的value则存储其余列数据,以及额外的ROWID、rollback pointer、trx id等信息。

结合这个图,以及上面所述,我们可以知道:在InnoDB表中,其聚集索引相当于整张表,而整张表也是聚集索引。主键必然是聚集索引,而聚集索引则未必是主键。

MyISAM是堆组织表,它没有聚集索引的概念。



innodb聚集索引clustered index


InnoDB和SolidDB支持clustered index.


clustered index不是一种单独的索引结构,而是一种数据存储方式,clustered index实际上是在B tree中同时保存了主键索引和数据。

clustered index存储结构的表中数据行是保存在索引的叶子页中的,clustered意思是数据行和索引主键都保存在一起。每个表只有一个clusterd index,就是那个primary key,换句话说Innodb按主键进行聚集。

如果没有定义primary key,Innodb会试着使用 Unique nonullable index列来代替,如果这都没有,会定义隐藏的主键然后再上面进行聚集。

 

clustered index的好处:

1)数据行是按主键顺序存储在一起的,读取少量的磁盘页面就可以把相邻主键的数据读出来。

2)索引和数据都保存在一棵B tree中,从索引中读取数据较快

3)使用覆盖索引的查询可以使用包含在叶子节点的主键值。

clustered index缺点

1)更新clustered index列代价是昂贵的,因为要强制把每个更新的数据行移到新位置

2)按主键顺序插入新行是一种好方法,否则更新主键或插入到随机插入性能开销比较大,数据行移动的时候还可能分页,其实道理和第一条一样。

3)辅助索引叶子节点包含的是主键列,而不是直接的数据行地址,所以辅助索引会相对较大

4)通过辅助索引的查找先找到的是主键列,然后再在主键索引中进行第二次查找,需要2次查找索引,但是Innodb的自哈希索引功能能减少这种损失。

 

Innodb和myisam的的索引及数据存储比较

都是使用的 B tree索引结构,但是他们的数据存储方式不一样,参考《Mysql High performance》

MyIsam是按数据行插入的时间顺序存储存储数据行,并且数据行和索引时分开存储的(table.MYD, table.MYI),应该不会强行建立隐式主键索引;Myisam的主键索引和其它索引没有结构上的区别。

而Innodb是按主键顺序聚集存储数据,数据行和索引都存储在B tree里面(聚集索引的主键最好的auto_increment的int型:插入的时候是按主键顺序插入的;而且会使辅助索引的大小更小)。

您可能感兴趣的文章:
Mysql启动中InnoDB: Error: log file ./ib_logfile0 is of different size 0错误的解决办法
MySQL数据库之锁的详解
DB2数据库设计和最高性能原则
PHP程序员最容易犯的11个MySQL错误
Amoeba:开源的分布式数据库Porxy解决方案
同一台服务器上安装两个mysql的教程
mysql启动故障一例
mysql类型转换
javascript错误的认识不用关心内存管理
mysql 主从同步一例

[关闭]