创新的购物网站建设,网站表单模板,建立网站 费用,苏州高端网站制作官网文章目录 前言1.聚簇索引和非聚簇索引的概念2.两者详细介绍2.1 聚簇索引2.2 非聚簇索引 3. 两者的区别3.1 数据存储方式3.2 二级索引查询 前言
1.聚簇索引和非聚簇索引的概念
数据库表的索引从数据存储方式上可以分为聚簇索引和非聚簇索引两种。“聚簇”的意思是数据行被按照… 文章目录 前言1.聚簇索引和非聚簇索引的概念2.两者详细介绍2.1 聚簇索引2.2 非聚簇索引 3. 两者的区别3.1 数据存储方式3.2 二级索引查询 前言
1.聚簇索引和非聚簇索引的概念
数据库表的索引从数据存储方式上可以分为聚簇索引和非聚簇索引两种。“聚簇”的意思是数据行被按照一定顺序一个个紧密地排列在一起存储。我们熟悉的InnoDB和MyISAM两大引擎InnoDB的默认数据结构是聚簇索引而MyISAM是非聚簇索引。
聚簇索引Clustered Index并不是一种单独的索引类型而是一种数据存储方式。当表有了聚簇索引的时候表的数据行都存放在索引树的叶子页中。无法把数据行放到两个不同的地方所以一张表只允许有一个聚簇索引。InnoDB的聚簇索引实际上是将索引和数据保存中同一个B-Tree中。InnoDB通过主键聚集数据如果没有定义主键InnoDB会选择一个唯一的的非空索引代替。如果没有这样的索引InnoDB会隐式定义一个主键来作为聚簇索引。
非聚簇索引NoClustered Index又叫二级索引。二级索引的叶子节点中保存的不是指向行的物理指针而是行的主键值。当通过二级索引查找行存储引擎需要在二级索引中找到相应的叶子节点获得行的主键值然后使用主键去聚簇索引中查找数据行这需要两次B-Tree查找。
2.两者详细介绍
2.1 聚簇索引
因为聚簇和非聚簇索引本质上是数据存储方式需要依赖于载体即以InnoDB引起来讲解聚簇索引以MyISAM来讲解非聚簇索引。下述讲解的图都引用自《高性能MySQL》。
对于InnoDB引擎来说是按照聚簇索引的形式存储数据
它的每个聚簇索引的叶子节点都包含主键值、事务ID、回滚指针(用于事务和MVCC)以及余下的列。从物理文件也可以看出 InnoDB的数据文件只有数据结构文件.frm和数据文件.ibd 其中.ibd中存放的是数据和索引信息 是存放在一起的。
InnoDB的二级索引和主键索引也有很大的不同二级索引存放的是主键值而不是行指针减少了移动数据或者分裂时维护二级索引的开销因为不需要更新索引的行指针。 聚簇索引的特点 优点 可以把相关数据保存在一起。例如实现电子邮箱时可以根据用户ID来聚集数据这样只需要从磁盘读取少量的数据页就能获取某个用户全部邮件如果没有使用聚集索引则每封邮件都可能导致一次磁盘IO。数据访问更快聚集索引将索引和数据保存在同一个B-Tree中因此从聚集索引中获取数据通常比在非聚集索引中查找要快。使用覆盖索引扫描的查询可以直接使用页节点中的主键值。 缺点 聚簇数据最大限度地提高了IO密集型应用的性能但如果数据全部放在内存中则访问的顺序就没有那么重要了聚集索引也没有什么优势了。插入速度严重依赖于插入顺序按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式但如果不是按照主键顺序加载数据那么在加载完成后最好使用optimize table命令重新组织一下表。更新聚集索引列的代价很高因为会强制InnoDB将每个被更新的行移动到新的位置。基于聚集索引的表在插入新行或者主键被更新导致需要移动行的时候可能面临页分裂的问题当行的主键值要求必须将这一行插入到某个已满的页中时存储引擎会将该页分裂成两个页面来容纳该行这就是一次页分裂操作页分裂会导致表占用更多的磁盘空间。聚集索引可能导致全表扫描变慢尤其是行比较稀疏或者由于页分裂导致数据存储不连续的时候。二级索引可能比想象的更大因为在二级索引的叶子节点包含了引用行的主键列。二级索引访问需要两次索引查找而不是一次。
2.2 非聚簇索引
对于MyISAM引擎来说是按照非聚簇索引的形式存储数据
原始数据
存储方式 按照列值和行号来组织索引的叶子节点中保存的实际上是指向存放数据块的指针。从物理文件中也可以看出MyISAM的索引文件.MYI和数据文件.MYD是分开存储的 是相对独立的。
举例执行流程select * from user where id 1 1、查看该user表的myi索引文件中有没有以id为索引的索引树 2、在id索引树上通过id值找到相应节点从而得到节点的数据叶子节点存的是索引值和数据地址数据地址指向当前表myd数据文件具体的哪一行 3、根据数据地址去myd文件里找到对应的数据返回。 3. 两者的区别
3.1 数据存储方式
最直观的区别是反映在数据存储方式上在MySQL数据库中InnoDB聚簇和MyISAM非聚簇数据存储文件格式如下
存储引擎是InnoDB, 在data目录下会看到2类文件.frm、.ibd 1*.frm–表结构的文件。 2*.ibd–表数据文件
存储引擎是MyISAM, 在data目录下会看到3类文件.frm、.myi、.myd 1*.frm–表定义是描述表结构的文件。 2*.MYD–D数据信息文件是表的数据文件。 3*.MYI–I索引信息文件是表数据文件中任何索引的数据树
示意图test1的存储引擎为InnoDBtest2的存储引擎为MyISAM 聚簇索引和非聚簇索引的存储方式区别
在MyISAM引擎索引和数据是分开存储的而InnoDB是索引和数据是一起以idb文件的形式进行存储的。在访问速度上聚簇索引比非聚簇索引快。非聚簇索引需要先查询一遍索引文件得到索引跟据索引获取数据。而聚簇索引的索引树的叶子节点的直接指向要查找的数据行。
3.2 二级索引查询
对于采用聚簇索引的InnoDB引擎的主键索引BTree和MyISAM的主键索引树以及MyISAM的二级索引BTree都是采用这样的结构。 但是InnoDB的二级索引BTree却是这样的 可以得出 在使用二级索引进行查询的时候InnoDB首先通过二级索引BTree得到数据行的主键索引然后再通过主键索引树查询数据。所以在二级索引InnoDB的性能消耗比较大。 但是这种情况在InnoDB中有一定的优化不是认为控制的而是引擎实现的通过二级索引查询多了InnoDB会生成自适应的哈希索引。
引用高性能MySQL的图能更加清晰的看到其差异 从图中可以看出 InnoDB二级索引的叶子节点存放的是KEY字段主键值因此首先通过二级索引查找到的是主键值再根据主键值在主键索引中查找到相应的数据文件。而MyISAM的二级索引存放的还是列值和行号的组合 叶子节点中保存的是指向物理数据的指针因此它的主建索引和二级索引的结构并没有任何区别只是说主键索引的索引值是唯一且非空的而MyISAM引擎可以不设置主键。InnoDB引擎是必须设置主键的需要依赖主键生成聚簇索引。
