广东外贸网站开发,做网站的流量怎么算钱,一手渠道推广平台,wordpress首页乱码索引的数据结构 为什么使用索引什么是索引索引的优缺点优点缺点 常见索引概念聚簇索引二级索引(辅助索引, 非聚簇索引)InnoDB的B树索引的注意事项 MyISAM 与 InnoDB 对比索引的代价 为什么使用索引
索引是存储引擎用于快速找到数据记录的一种数据结构#xff0c;就好比一本教… 索引的数据结构 为什么使用索引什么是索引索引的优缺点优点缺点 常见索引概念聚簇索引二级索引(辅助索引, 非聚簇索引)InnoDB的B树索引的注意事项 MyISAM 与 InnoDB 对比索引的代价 为什么使用索引
索引是存储引擎用于快速找到数据记录的一种数据结构就好比一本教科书的目录部分通过目录中找到对应文章的页码便可快速定位到需要的文章. MySQL中也是一样的道理进行数据查找时首先查看查询条件是否命中某条索引符合则通过索引查找相关数据如果不符合则需要全表扫描即需要一条一条地查找记录直到找到与条件符合的记录.
什么是索引
MySQL官方对索引的定义为索引Index是帮助MySQL高效获取数据的数据结构。 索引的本质索引是数据结构. 可以简单理解为“排好序的快速查找数据结构”满足特定查找算法. 这些数据结构以某种方式指向数据 这样就可以在这些数据结构的基础上实现高级查找算法. 索引是在存储引擎中实现的, 因此每种存储引擎的索引不一定完全相同并且每种存储引擎不一定支持所有索引类型. 同时存储引擎可以定义每个表的最大索引数和最大索引长度. 所有存储引擎支持每个表至少16个索引总索引长度至少为256字节. 有些存储引擎支持更多的索引数和更大的索引长度.
索引的优缺点
优点
提高数据检索的效率降低 数据库的IO成本 这也是创建索引最主要的原因。通过创建唯一索引可以保证数据库表中每一行 数据的唯一性 。在实现数据的 参考完整性方面可以 加速表和表之间的连接 。换句话说对于有依赖关系的子表和父表联合查询时 可以提高查询速度。在使用分组和排序子句进行数据查询时可以显著 减少查询中分组和排序的时间 降低了CPU的消耗。
缺点
创建索引和维护索引要 耗费时间 并 且随着数据量的增加所耗费的时间也会增加。索引需要占 磁盘空间 除了数据表占数据空间之 外每一个索引还要占一定的物理空间 存储在磁盘上 如果有大量的索引索引文件就可能比数据文 件更快达到最大文件尺寸。虽然索引大大提高了查询速度同时却会 降低更新表的速度 。当对表 中的数据进行增加、删除和修改的时候索引也要动态地维护这样就降低了数据的维护速度。 因此选择使用索引时需要综合考虑索引的优点和缺点。
因此选择使用索引时需要综合考虑索引的优点和缺点 索引可以提高查询的速度但是会影响插入记录的速度。这种情况下最好的办法是先删除表中的索引然后插入数据插入完成后再创建索引。
常见索引概念
索引按照物理实现方式索引可以分为 2 种聚簇聚集和非聚簇非聚集索引。我们也把非聚集索引称为二级索引或者辅助索引。
聚簇索引
聚簇索引并不是一种单独的索引类型而是一种数据存储方式所有的用户记录都存储在了叶子结点也就是所谓的索引即数据, 数据即索引. 术语聚簇表示当前数据行和相邻的键值聚簇的存储在一起 特点:
使用记录主键值的大小进行记录和页的排序这包括三个方面的含义 页内的记录是按照主键的大小顺序排成一个单向链表。各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。存放目录项记录的页分为不同的层次在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。 B树的 叶子节点 存储的是完整的用户记录。 所谓完整的用户记录就是指这个记录中存储了所有列的值包括隐藏列。我们把具有这两种特性的B树称为聚簇索引所有完整的用户记录都存放在这聚簇索引的叶子节点处。这种聚簇索引并不需要我们在MySQL语句中显式的使用INDEX 语句去创建InnDB存储引擎会自动的为我们创建聚簇索引。 优点
数据访问更快因为聚簇索引将索引和数据保存在同一个B树中因此从聚簇索引中获取数据比非聚簇索引更快聚簇索引对于主键的排序查找和范围查找速度非常快按照聚簇索引排列顺序查询显示一定范围数据的时候由于数据都是紧密相连数据库不用从多 个数据块中提取数据所以节省了大量IO操作。
缺点
插入速度严重依赖于插入顺序按照主键的顺序插入是最快的方式否则将会出现页分裂严重影响性能。因此对于InnoDB表我们一般都会定义一个自增的ID列为主键更新主键的代价很高因为将会导致被更新的行移动。因此对于InnoDB表我们一般定义主键为不可更新二级索引访问需要两次索引查找第一次找到主键值第二次根据主键值找到行数据
二级索引(辅助索引, 非聚簇索引)
如果我们想以别的列作为搜索条件该怎么办肯定不能是从头到尾沿着链表依次遍历记录一遍。 答案我们可以多建几颗B树不同的B树中的数据采用不同的排列规则。比方说我们用c2列的大小作为数据页、页中记录的排序规则再建一课B树效果如下图所示 这个B树与上边介绍的聚簇索引有几处不同 使用记录c2列的大小进行记录和页的排序,这包括三个方面的含义 1. 页内的记录是按照c2列的大小顺序排成一个单向链表。
2. 各个存放用户记录的页也是根据页中记录的c2列大小顺序排成一个双向链表存放目录项记录的页分为不同的层次,在同一层次中的页也是根据页中目录项记录的c2列大小顺序排成一个双向链表。B树的叶子节点存储的并不是完整的用户记录,而只是c2列主键这两两个列的值。 目录项记录中不再是主键页号的搭配,而变成了c2列页号的搭配。
所以如果我们现在想通过c2列的值查找某些记录的话就可以使用我们网刚刚建好的这个B树了。以查找c2列的值为4的记录为例,查找过程如下:
确定 目录项记录页根据根页面,也就是页44,可以快速定位到目录项记录所在的页为页42(因为249)。通过目录项记录页确定用户记录真实所在的页。在页42中可以快速定位到实际存储用户记录的页,但是由于c2列并没有唯一性约束,所以c2列值为4的记录可能分布在多个数据页中,又因为244,所以确定实际存存储用户记录的页在页34和页35中。在真实存储用户记录的页中定位到具体的记录。到页34和页35中定位到具体的记录。但是这个B树的叶子节点中的记录只存储了c2和c1(也就就是主键)两个列,所以我们必须再根据主键值去聚簇索引中再查找一遍完整的用户记录。
聚簇索引与非聚簇索引的原理不同在使用上也有一些区别
聚簇索引的叶子结点存储的就是我们的数据记录, 非聚簇索引的叶子节点存储的是数据位置。非聚簇索引不会影响数据表的物理存储顺序。一个表只能有一个聚簇索引因为只能有一种排序存储的方式但可以有多个非聚簇索引也就是多个索引目录提供数据检索。使用聚簇索引的时候数据的查询效率高但如果对数据进行插入删除更新等操作效率会比非聚簇索引低。
InnoDB的B树索引的注意事项
根页面位置万年不动
实际上B树的形成过程是这样的
每当为某个表创建一个B树索引聚簇索引不是人为创建的默认就有的时候都会为这个索引创建一个根节点页面。最开始表中没有数据的时候每个B树索引对应的根节点中即没有用户记录也没有目录项记录。随后向表中插入用户记录时先把用户记录存储到这个根节点中。当根节点中的可用 空间用完时继续插入记录此时会将根节点中的所有记录复制到一个新分配的页比如 页a中然后对这个新页进行页分裂的操作得到另一个新页比如页b。这时新插入的记录根据键值也就是聚簇索引中的主键值二级索引中对应的索引列的值的大小就会被分配到页a或者页b中而根节点便升级为存储目录项记录的页。
这个过程特别注意的是一个B树索引的根节点自诞生之日起便不会再移动。这样只要我们对某个表建议一个索引那么它的根节点的页号便会被记录到某个地方。然后凡是 InnoDB存储引擎需要用到这个索引的时候都会从哪个固定的地方取出根节点的页号从而来访问这个索引。
一个页面最少储存两条数据
MyISAM 与 InnoDB 对比
MyISAM的索引方式都是“非聚簇”的与InnoDB包含1个聚簇索引是不同的。小结两种引擎中索引的区别 ① 在InnoDB存储引擎中我们只需要根据主键值对聚簇索引进行一次查找就能找到对应的记录而在 MyISAM 中却需要进行一次回表操作意味着MyISAM中建立的索引相当于全部都是 二级索引 。 ② InnoDB的数据文件本身就是索引文件而MyISAM索引文件和数据文件是分离的 索引文件仅保存数据记录的地址。 ③ InnoDB的非聚簇索引data域存储相应记录**主键的值 而MyISAM索引记录的是地址 **。换句话说 InnoDB的所有非聚簇索引都引用主键作为data域。 ④ MyISAM的回表操作是十分快速的因为是拿着地址偏移量直接到文件中取数据的反观InnoDB是通过获取主键之后再去聚簇索引里找记录虽然说也不慢但还是比不上直接用地址去访问。 ⑤ InnoDB要求表 必须有主键 MyISAM可以没有 。如果没有显式指定则MySQL系统会自动选择一个 可以非空且唯一标识数据记录的列作为主键。如果不存在这种列则MySQL自动为InnoDB表生成一个隐 含字段作为主键这个字段长度为6个字节类型为长整型。
索引的代价
空间上的代价 每建立一个索引都要为它建立一棵B树每一棵B树的每一个节点都是一个数据页一个页默认会 占用 16KB 的存储空间一棵很大的B树由许多数据页组成那就是很大的一片存储空间。 时间上的代价 每次对表中的数据进行 增、删、改 操作时都需要去修改各个B树索引。而且我们讲过B树每 层节点都是按照索引列的值 从小到大的顺序排序 而组成了 双向链表 。不论是叶子节点中的记录还 是内节点中的记录也就是不论是用户记录还是目录项记录都是按照索引列的值从小到大的顺序 而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏所以存储引擎需 要额外的时间进行一些 记录移位 页面分裂 、 页面回收 等操作来维护好节点和记录的排序。如果 我们建了许多索引每个索引对应的B树都要进行相关的维护操作会给性能拖后腿。
