辽宁网站seo,ai制作网页教程,公司网站建设 费用入账,什么是品牌vi设计从InnoDB索引的数据结构#xff0c;去理解索引 1、InnoDB 中的 BTree1.1、BTree 的组成1.2、BTree中的数据页 2、聚簇索引2.1、聚簇索引的特点2.2、聚簇索引的结构示例2.3、聚簇索引的优缺点 3、非聚簇索引3.1、非聚簇索引结构示例3.2、关于回表3.3、聚簇索引和非聚簇索引的区… 从InnoDB索引的数据结构去理解索引 1、InnoDB 中的 BTree1.1、BTree 的组成1.2、BTree中的数据页 2、聚簇索引2.1、聚簇索引的特点2.2、聚簇索引的结构示例2.3、聚簇索引的优缺点 3、非聚簇索引3.1、非聚簇索引结构示例3.2、关于回表3.3、聚簇索引和非聚簇索引的区别与代价 4、扩展与问答4.1、InnoDB 和 MyISAM 对比4.2、MySQL使用InnoDB存储引擎时为什么不建议使用过长的字段作为主键 4.3、MySQL使用InnoDB存储引擎时为何不建议使用非单调字段不是按照递增或递减的顺序进行排列的字段作为主键 4.4、一般情况下为何我们用到的BTree 都不会超过4层 该篇我们都是基于 InnoDB 存储引擎的大前提下讨论的如文中未明确指出存储引擎一律说的是 InnoDB.
要知道 InnoDB 的索引数据结构主要是 BTree. 按照物理实现方式可以将索引划分为聚簇索引和非聚簇索引也称为 二级索引、辅助索引。 1、InnoDB 中的 BTree 1.1、BTree 的组成 ① 根节点BTree的最顶层节点如果树的高度大于1则根节点可以有多个子节点。 ② 内部节点也称为内节点、非叶子节点除根节点和叶子节点之外的节点每个内部节点包含多个键值对和指向子节点的指针。内部节点用于索引不存储实际的数据。内节点用于存放目录项。InnoDB 的BTree中内节点中的目录项记录必须保证唯一性所以对于二级索引而言二级索引非聚簇索引中必须包含主键列。 ③ 叶子节点位于BTree最底层的节点存储实际的数据用户记录或索引键值。叶子节点之间通过指针相互连接形成一个链表结构便于进行范围查询和排序操作。 不论是存放 用户记录指这个记录中存储了所有列的值包括隐藏列的数据页还是存放目录项记录的数据页我们都把它们存放在 BTree 这个数据结构中故我们也称这些数据页为节点。每个节点包括根节点、内部节点和叶子节点都包含多个键值对和指向子节点的指针。BTree通过这种结构将数据存储在一个平衡的多路搜索树中从而提高了查询性能和数据访问的效率。 1.2、BTree中的数据页 在BTree中页是数据存储的基本单位也是磁盘I/O操作的最小单元。每个页通常具有固定的大小例如InnoDB存储引擎中页的大小默认为16KB。页中包含了节点的键值对数据和指向子节点的指针等信息。通俗来讲一个叶子节点中有一页数据一页数据中包含了多条用户记录、主键等信息。每页包含的数据量与每条用户记录的大小、索引大小等相关。一个非叶子节点中也有一页数据其包含主键、二级索引字段、下级页码等数据。在InnoDB的一个数据页中至少存放两条数据记录。 对于非叶子节点每个页存储了多个键值对和指向子节点的指针用于索引和导航到下一层的页。对于叶子节点每个页存储了实际的数据或索引键值以及指向相邻叶子节点的指针用于范围查询和排序操作。通过页的设计BTree可以高效地利用存储空间减少磁盘I/O操作次数提高查询性能和数据访问的效率。 2、聚簇索引 InnoDB存储引擎的聚簇索引是一种按照主键顺序存储数据的索引结构由于是主键顺序存储故主键尽量用有序的。聚簇索引的叶子节点就是数据节点且数据都是按照主键排序存储的。其特点是将索引和数据行保存在同一个BTree中因此查询通过聚簇索引可以直接获取数据。二级索引非聚簇索引中必须包含主键列所以如果主键列很大的话其他的索引也会占用很大的空间。
InnoDB 的一个表要求必须有聚簇索引且只能有一个聚簇索引。InnoDB如果没有显示指定MySQL系统会自动选择一个非空且唯一的列作为聚簇索引如果不存在这样的列MySQL 会自动为 InnoDB 表生成一个隐含字段作为主键这个字段长度为6个字节类型为长整型。一般主键即为聚簇索引建议主键自增。 2.1、聚簇索引的特点 使用记录主键值的大小进行记录和页的排序这包含三方面的含义 页内的记录是按照主键的大小顺序排成一个 单向链表。各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。存放目录项记录的页分为不同的层次在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。 BTree 的叶子节点存储的是完整的用户记录。所谓用户记录就是指这个记录中存储了所有列的值包括隐藏列。 2.2、聚簇索引的结构示例 以下示例使用 tb_table 表为例tb_table表使用 InnoDB 引擎其中包含 c1、c2、c3、c4、c5 五个字段其中 c1 为主键即聚簇索引。
表数据示例
c1主键8c2c3c4c514u44u39d99d44a44aa53y33yy87a71aa104o44oe127d77q202e12tt439x32xx595b53u766i41e878a88aa995m55d
聚簇索引结构示例 2.3、聚簇索引的优缺点 优点缺点数据访问更快因为聚簇索引将索引和数据保存在同一个BTree中节省大量的 io 操作。插入速度严重依赖于插入顺序按照主键的顺序插入是最快的方式否则将会出现页分裂严重影响性能。所以对于 InnoDB 引擎的表一般建议 自增的ID列作为主键。聚簇索引对主键的 排序查找 、范围查找 速度非常快。更新主键的代价很高更新主键会导致被更新的行移动页分裂调整。一般建议 主键为不可更新。_二级索引访问需要两次索引查找。使用二级索引查找时第一次找到主键值第二次根据主键值找到行数据这个过程也就是回表。 3、非聚簇索引 InnoDB的非聚簇索引也称为二级索引或辅助索引它是一种基于聚簇索引创建的索引结构。非聚簇索引的叶子节点并不直接存储实际的数据行而是存储了创建非聚簇索引的字段和聚簇索引的主键值。InnoDB的非聚簇索引也是按照BTree的数据结构来组织的这样可以保证查询的高效性。
3.1、非聚簇索引结构示例 非聚簇索引结构示例表结构数据同 聚簇索引上述示例其中以 c2 字段作为非聚簇索引字段 3.2、关于回表 说到非聚簇索引就不得不说【回表】的概念。
我们根据非聚簇索引二级索引列的大小排序的 BTree只能确定我们要查找记录的主键所以如果我们要根据非聚簇索引去做查询但查询的字段中除了该非聚簇索引字段还包括其他字段那就要根据非聚簇索引先查到相关用户记录的主键再根据主键去查该用户记录行的完整数据再筛选出需要查询的字段。
总结一下通过非聚簇索引查询时如果查询的字段不仅仅是该非聚簇索引字段时就需要使用到 2 棵 BTree这个过程就叫回表。 3.3、聚簇索引和非聚簇索引的区别与代价 聚簇索引和非聚簇索引的区别索引的代价① 聚簇索引的叶子节点存储的是数据记录非聚簇索引的叶子节点存储的是 数据位置。非聚簇索引不会影响数据表的物理存储顺序。① 空间上的代价每建立一个索引都要为它建立一棵 BTree 树每棵 BTree 树的每个节点都是一个数据页一个数据页默认会占用 16KB 的存储空间一棵 BTree 由许多数据页组成那就是一个比较大的存储空间。② 对于InnoDB存储引擎而言一个表只能有一个聚簇索引因为只能有一种排序存储的方式。可以有多个非聚簇索引也就是多个索引目录提供数据检索。② 时间上的代价每次对表中的数据进行 增、删、改 操作时都会去修改各个 BTree 索引。BTree 每层节点都是按照索引列的值 从小到大的顺序排序 而组成 双向链表。不论是叶子节点中的记录还是内节点中的记录都是按照索引列的值从小到大的顺序而形成的一个单向链表.增删改操作可能会对节点和记录的排序造成破坏存储引擎需要额外的时间进行记录移位、页面分裂、页面回收等操作来维护好节点和记录的排序。③ 使用聚簇索引的时候数据的 查询效率高但是如果对数据进行插入、删除、更新等操作效率会比非聚簇索引低。③ 一个表上的索引建的越多就会占用越多的存储空间在增删改记录的时候性能就越差。 4、扩展与问答 4.1、InnoDB 和 MyISAM 对比 MyISAM 的索引方式都是非聚簇的InnoDB有一个聚簇索引可以有多个非聚簇索引。在InnoDB 的数据文件本身就是索引文件MyISAM 索引文件和数据文件是分离的索引文件仅保存数据记录的地址。在 InnoDB 存储引擎中我们只需要根据主键值对 聚簇索引 进行一次查找就能找到对应的记录而在 MyISAM 中需要进行一次回表操作意味着 MyISAM 中建立的索引相当于全部都是二级索引。InnoDB 的非聚簇索引 data域存储相应记录的主键值而MyISAM索引记录的是地址。换句话说InnoDB的所有非聚簇索引都引用主键作为data域。MyISAM 的回表操作是十分快速的因为是拿着地址偏移量直接到文件中取数据的。InnoDB是通过获取主键之后再去聚簇索引里找记录虽然也不慢但还是比不上直接拿地址去获取。InnoDB要求必须有聚簇索引MyISAM可以没有。InnoDB如果没有显示指定MySQL系统会自动选择一个非空且唯一的列作为聚簇索引如果不存在这样的列MySQL 会自动为 InnoDB 表生成一个隐含字段作为主键这个字段长度为6个字节类型为长整型。
4.2、MySQL使用InnoDB存储引擎时为什么不建议使用过长的字段作为主键 答所有二级索引都引用主键索引聚簇索引过长的主键索引会让二级索引变得过大。 4.3、MySQL使用InnoDB存储引擎时为何不建议使用非单调字段不是按照递增或递减的顺序进行排列的字段作为主键 答InnoDB的数据文件本身就是一颗BTree非单调的主键会造成在插入新记录时数据文件为了维持BTree的特性而频繁的分裂调整十分低效。而使用自增字段作为主键则是一个很好的选择。 4.4、一般情况下为何我们用到的BTree 都不会超过4层 答可以初步估算一下单表的数据存储情况。假设所有存放用户记录的叶子节点代表的数据页可以存放 100 条用户记录所有存放目录项的记录的内节点代表的数据页可以存放1000条目录项记录那么
如果BTree只有 1 层也就是只有一个用于存放用户记录的节点最多存放 100条记录。如果BTree只有 2 层最多存放的 1000 * 100 100,000 条记录即十万条。如果BTree只有 3 层最多存放的 1000 * 1000 * 100 100,000,000 条记录即一亿条。如果BTree只有 4 层最多存放的 1000 * 1000 * 1000 * 100 100,000,000,000 条记录即一千亿条。 系列文章
一 《搞懂 MySql 的架构和执行流程》
二 《从InnoDB索引的数据结构去理解索引》
三 《从 Hash索引、二叉树、B-Tree 与 BTree 对比看索引结构选择》
四 《MySQL 的索引分类和设计原则》
五 《MySQL 优化思路篇》 .
