优秀设计赏析网站,洛阳网站推广怎么做,免费域名注册二级域名,怎么进去wordpress的仪表盘索引
索引是对数据库表中一列或者多列数据检索时#xff0c;为了加速查询而创建的一种结构。可以在建表的时候创建#xff0c;也可以在后期添加。 USER表中有100万条数据#xff0c;现在要执行一个查询SELECT * FROM USER where ID999999#xff0c;如果没有索…索引
索引是对数据库表中一列或者多列数据检索时为了加速查询而创建的一种结构。可以在建表的时候创建也可以在后期添加。 USER表中有100万条数据现在要执行一个查询SELECT * FROM USER where ID999999如果没有索引查询的时候MySQL会从第一行开始遍历直到ID等于999999的这一行被找到为止。如果在ID列上创建索引MySQL不需要任何扫描直接在索引里面找999999就可以得知这一行的位置。
结构索引可以采用不同的数据结构最常见的是B树索引。B树索引是一种树状结构其中根节点连接到多个中间节点最终指向叶子节点叶子节点包含实际的数据行或指向数据行的引用。唯一性索引可以是唯一的也可以包含重复值。唯一索引确保索引列中的值是唯一的而非唯一索引可以包含重复值。创建索引是通过数据库管理系统的SQL语句创建的。通常你可以使用CREATE INDEX语句来创建索引。在创建索引时需要指定要在哪些列上创建索引以及使用哪种索引类型。维护随着数据表的更新插入、更新、删除操作索引也需要不断维护以保持其准确性。维护包括插入新的索引项、更新现有的索引项和删除不再符合条件的索引项。覆盖索引覆盖索引是一种包含了查询所需的所有列的索引。它可以避免回表操作因为索引本身包含了所需的数据。聚簇索引聚簇索引是一种特殊类型的索引它决定了数据行的物理存储顺序。一个表只能有一个聚簇索引通常是主键索引。非聚簇索引非聚簇索引是其他非主键索引它不决定数据行的物理存储顺序通常需要回表操作来获取完整的数据。
B 树
B 树又叫平衡多路查找树每个节点可以包含多个子节点因此 B 树的高度比较低从而查找过程中 I/O 次数比较少。当插入或删除操作破坏了B树的平衡时节点可能会分裂成两个节点或合并成一个节点以维持平衡。 根节点最顶端的节点就是要最开始遍历的节点一般存在缓存中。 内部节点内部节点是除叶子节点和根节点之外的所有节点 叶节点没有子节点也没有指向子节点的指针所有叶节点在同一层。 每个节点包含了 键 key、指针 p 指向子节点、以及数据 data 阶是表示所有节点中子节点个数的最大值一般用 m 表示。 一棵m阶的B 树的特性如下
每一个节点最多有 m 个子节点每一个非叶子节点除根节点最少有 ceil(m/2)向上取整 个子节点如果根节点不是叶子节点那么它至少有两个子节点有 k 个子节点的非叶子节点拥有 k − 1 个键所有的叶子节点都在同一层
B树
B树是一个平衡的多叉树和 B 树一样有三中节点根节点内部节点叶节点。B树的非叶节点只存储键值数据都存储在叶节点中所有叶节点都在同一层形成一个单向链表对于主键的排序查找和范围查找速度非常快。 内部节点p 指向子节点k 关键字 叶子节点k 是关键字d 指向包含关键字 k 的磁盘的文件块p 指向下一个相邻的叶节点也就是叶节点通过 p 来连接成一个链表。
B树作为索引的原因
B树每个节点可以拥有多个子节点相比于二叉树B树的分支更多减少了树的高度提高了操作效率。红黑树也是二叉的平衡树高度比B树高树的查找性能取决于树的高度高度越高对IO操作次数越多在内存中红黑树比B树性能好在文件系统种B树比红黑树好。
B树和B树的区别
1、B 树非叶子节点和叶子节点都存储数据因此查询数据时时间复杂度最好为O(1)最坏为 O(logn)。B树只在叶子节点存储数据非叶子节点存储关键字且不同非叶子节点的关键字可能重复因此查询数据时时间复杂度固定为O(logn)。 2、 如果完成一次遍历操作B树通过中序遍历。 B树叶子节点之间用链表相互连接因而只需要扫描叶子节点的链表就可以完成一次遍历操作。 3、B树的优势是当要查找的值恰好处于一个非叶子节点时查找该节点就会成功并结束查询不需要到达叶节点。B树由于非叶子节点只是索引部分无论查找成功与否都要到达叶节点。 4、 在 B 树中被删除的项可能出现在非叶结点中。我们必须从包含被删除项的子树中选择正确的值来作为替代。在 B树中被删除的项总是出现在叶结点中。 5、B树非叶子节点上是不存储数据的仅存储键值那么就会存储更多的键值相应的树的阶数节点的子节点树就会更大树就会更矮更胖。
B树和 B 树 范围查询
在B树中所有数据都存储在叶子节点上而且这些数据按键值的顺序有序排列。这意味着相关数据通常存储在相邻的叶子节点上从而形成了连续的数据范围。在范围查询时可以轻松地扫描连续的数据块而不必跳跃到不相邻的数据块从而减少了磁盘访问的延迟。 B树的内部节点通常只包含键值和指向子节点的指针而不包含数据本身。这减少了访问内部节点的开销因为在B树中内部节点可能包含部分数据。在范围查询中B树内部节点的减少访问可以显著减少不必要的磁盘访问。B 树 总是要从根节点向下遍历子树查找到包含左边界数据的节点再进行中序遍历找到包含右边界数据的节点。每遍历一次节点就要进行 I/O 。
聚簇索引
聚簇索引是数据库表中的一种特殊索引它决定了表中数据行的物理存储顺序。在一个表上只能有一个聚簇索引因为表中的数据默认只能有一种存储顺序这个索引定义了数据行在磁盘上的排列顺序。 如果聚簇索引的键值按顺序排列那么数据行也会按顺序存储在磁盘上而不是随机存储的。这种情况下范围查询和顺序扫描性能通常会非常好因为数据行在磁盘上是紧密排列的减少了磁盘寻道时间。 比如在订单表中如果在下单时间上创建了聚簇索引那么存储在订单表中的数据默认是按下单时间存储的。 聚簇索引通常适用于需要支持范围查询和顺序扫描操作的数据以及具有紧凑、顺序存储的数据比如时间、日期、ID、序列号、金融数据等。
非聚簇索引
与聚簇索引相对应的是非聚簇索引。非聚簇索引只包含索引键值和指向实际数据行的指针而不决定数据行在磁盘上的存储顺序。当使用非聚簇索引执行范围查询时可能需要进行更多的磁盘读取因为数据行的物理存储顺序不一定与索引顺序一致。 如果需要快速查找单个行或具有高度变化的索引键值非聚簇索引可能更合适。
聚簇索引磁盘读取过程
聚簇索引的键值决定了数据行的物理存储顺序。这意味着具有相似或连续键值的数据行在磁盘上也是相邻存储的而不同键值的数据行可能在磁盘上分散存储。在机械硬盘上读取数据需要进行磁盘寻道操作当数据行在磁盘上按顺序排列时寻道时间较低因为数据行紧密排列在相邻的磁道上。 3.具有聚簇索引的表通常在范围查询方面表现出色。由于相关数据行在磁盘上是连续存储的范围查询可以有效地从头到尾扫描数据行而无需频繁进行磁盘寻道。 4.如果一个表上有非聚簇索引这些索引通常会指向具体的数据行。非聚簇索引的使用可能需要额外的磁盘读取因为它们可能引导查询从索引到数据行的跳转导致更多的磁盘寻道操作。 5.在数据库设计中需要考虑查询需求和性能优化来决定是否使用聚簇索引。如果数据库表的主要操作是范围查询或顺序扫描那么聚簇索引通常是更好的选择。但如果需要快速查找单个数据行或具有高度变化的索引键值非聚簇索引可能更适合。
索引查询过程
考虑一个简单的查询过程假设我们有一个名为employees的员工表其中EmployeeID是聚簇索引列。
查询条件用户执行查询例如查找EmployeeID等于1001的员工记录。聚簇索引查找数据库管理系统使用聚簇索引B树来查找满足查询条件的记录。在B树索引中我们从根节点开始导航逐级向下直到找到叶子节点其中包含了EmployeeID为1001的键值。数据行定位一旦索引找到了匹配的键值1001数据库管理系统使用聚簇索引的物理存储顺序直接定位到实际数据行的位置。由于聚簇索引的键值与数据行的键值相匹配所以可以精确地找到数据行的位置。数据行访问数据库管理系统直接从数据表中的物理存储位置访问或检索数据行而无需额外的磁盘访问。这是因为聚簇索引的物理存储顺序与索引键值的顺序一致从而提供了快速的数据访问。数据行返回最后查询返回EmployeeID等于1001的员工记录。
回表
是数据库查询优化中的一个术语指的是在使用非聚簇索引进行查询时数据库需要额外访问数据表来获取完整的数据行。回表中的非聚簇索引通常会返回主键索引的值然后根据主键索引再次查询数据。这是因为非聚簇索引的叶子节点通常包含指向数据行的主键值而不是实际的数据。 查询过程
索引键值查询首先数据库管理系统使用非聚簇索引来查找匹配查询条件的索引键值。获取主键值在索引叶子节点中找到匹配的索引键值后数据库会获取主键索引的值。这通常是数据行的主键列的值。再次查询数据一旦获取了主键值数据库管理系统将使用这个主键值来执行另一次查询但这次是在主键索引上执行。这个查询将定位到实际数据行从而获取完整的数据。
索引数据存储位置
在数据库中索引通常存储在磁盘上并且与数据表的物理存储分开
系统表空间某些数据库管理系统如Oracle将索引存储在系统表空间中。系统表空间是一个专门用于存储数据库系统元数据包括索引定义的区域。用户表空间其他数据库管理系统如MySQL和PostgreSQL将索引存储在用户表空间中。每个用户可以有一个或多个表空间用于存储表、索引和其他对象。独立文件在某些情况下数据库管理系统允许将索引存储在独立的文件中。这通常是为了管理索引的物理存储位置以优化性能或数据管理。内存中一些数据库管理系统会将索引的一部分或整个索引缓存在内存中以提高查询性能。这些内存索引通常是对常用的索引进行缓存以加速查询。
索引建立过程
选择索引列首先选择一个或多个表中的列作为索引列。通常你会选择那些经常用于查询条件的列以提高查询性能。索引类型确定要创建的索引类型例如B树索引、哈希索引、全文索引等。大多数数据库系统使用B树索引因为它们对各种查询类型都有效。创建索引执行数据库管理系统提供的CREATE INDEX语句来创建索引。这将触发数据库系统执行以下操作 数据扫描遍历数据表中的所有数据行。键值提取从每个数据行中提取索引列的键值。索引插入将键值插入到索引结构中按照选择的索引类型进行排序。 维护索引随着数据表的变化插入、更新、删除操作索引也需要不断维护以保持其准确性。这包括在数据插入时插入新的索引项数据更新时更新索引项以及在数据删除时删除相应的索引项。
示例 假设有一个名为employees的员工表其中包含以下列EmployeeID员工ID、FirstName名字、LastName姓氏、Department部门等。现在我们希望在LastName列上创建一个B树索引。
sqlCopy code
-- 创建B树索引
CREATE INDEX idx_lastname ON employees (LastName);这个示例中我们使用SQL的CREATE INDEX语句在employees表的LastName列上创建了一个B树索引名为idx_lastname。数据库管理系统将执行数据扫描提取LastName列的键值并按照B树结构进行排序和插入。随着数据表的更改索引将进行维护以保持其准确性。 一旦索引创建完成查询操作在LastName列上将更快速因为数据库管理系统可以使用这个索引来快速定位并检索数据行而不必扫描整个表。这提高了查询性能特别是在大型数据表上。
物理存储顺序
指的是数据在计算机磁盘或内存等物理存储介质上的排列顺序。它表示数据元素在物理存储介质上的实际位置这一位置通常与数据元素在逻辑上的排列顺序例如按照键值或索引的顺序不一定完全一致。 数据通常以二进制形式存储在磁盘、内存或其他存储介质上。物理存储顺序对于数据的读取和访问性能至关重要因为它影响了数据访问时的磁盘寻道时间、内存访问时间等因素。
顺序存储如果数据在物理存储介质上按照逻辑顺序存储即相邻数据元素依次排列这通常会提高范围查询和顺序扫描的性能。随机存储如果数据在物理存储介质上以随机顺序存储数据元素之间可能会散布在不同的位置导致更多的磁盘寻道和读取操作降低性能。磁盘寻道在机械硬盘上数据的物理存储顺序影响了磁盘寻道时间。数据元素越接近磁头的移动距离就越小寻道时间越短。内存存储在内存中物理存储顺序对于缓存性能也很重要。如果数据在内存中紧密存储缓存命中的机会更大从而提高读取性能。
数据库和磁盘 机械硬盘结构
1、盘片磁盘通常由多个金属或玻璃盘片组成每个盘片有两个磁性表面。这些盘片被堆叠在一起共享一个中心轴。 2、磁道每个盘片的表面被划分成多个同心圆状的磁道这些磁道沿盘片的半径方向分布。 3、扇区每个磁道又被分成若干扇区通常是物理存储的最小单元。数据存储在这些扇区中每个扇区有一个唯一的地址。硬盘的第一个扇区叫做引导扇区。 4、簇操作系统对硬盘进行读写时需要用到文件系统把硬盘的扇区组合成簇并建立文件和树形目录制度使操作系统对其访问和查找变得容易这是因为操作系统直接对数目众多的扇区进行寻址会十分麻烦。 5、柱面柱面是垂直于盘片的直线连接相同位置上不同盘片上的磁道。多个盘片上的磁道构成一个柱面它们具有相同的半径位置。 6、主轴磁盘的主轴是一个旋转轴用于旋转盘片。盘片通常以高速旋转例如每分钟数千转。 7、磁头每个磁性表面上悬浮着一个磁头用于读取和写入数据。这些磁头通常非常接近盘片表面以便进行高精度的数据访问。 8、 电机驱动器电机驱动器用于旋转盘片使其保持高速旋转。它通常位于磁盘的内部结构。 9、 控制器磁盘控制器是磁盘的智能部分负责控制磁头的移动、管理数据访问、寻道和定位以及与计算机系统通信。 10、 缓存一些磁盘具有内置缓存用于临时存储数据以提高读写性能。这是一个较小但更快速的存储区域用于加速数据传输。
寻道和旋转时间
1、寻道 读取或写入数据时磁盘通常需要将磁头移动到正确的磁道上这个过程称为寻道。这个过程需要一定的时间通常以毫秒为单位。 2、旋转 在寻道完成后磁盘需要等待所需的扇区旋转到磁头的位置以便进行读取或写入操作。盘片旋转到特定扇区的位置也是需要时间的。
数据块
磁盘的数据通常被划分为块或者页有助于减少这些时间。块是存储和检索数据的最小单位。每个块通常包含一个固定数量的字节通常是4KB或更大每个块通常包含一个文件的一部分或多个文件的整个内容。在InnoDB存储引擎中数据页的大小通常为16KB这个数值可以根据系统需求进行配置。
访问模式
1、 顺序访问数据存在连续的磁盘块上。当 进行 I/O时第一块可能需要寻道后续相邻的磁道会被连续访问这种情况下操作系统会预先的读取连续的块减少 I/O 次数。 2、 随机访问数据存在随机的磁盘块上每一次 I/O 都要进行一次寻道
磁盘运作原理
当磁盘被使用时驱动马达使磁盘以很高的恒定速度旋转(通常为每秒60、90或120转也可达到每秒250转)。磁头恰好位于盘片表面的上方。对于现在的磁盘扇区大小一般为512字节每一个盘片有约50000100000条磁道每条磁盘有15个盘片。内侧的磁道(离转轴近的地方)长度较短内侧每磁道大约包含5001000个扇区而外侧每磁道大约包含10002000个扇区。 一张磁盘通常包括很多个盘片每个盘片的每一面都有一个磁头磁头通过在盘片上移动来访问不同的磁道。所有磁道的磁头安装在一个称为磁盘臂的单独装置上并且一起移动。安装在转轴上的所有磁盘盘片和安装在磁盘臂上的所有磁头统称为磁头-磁盘装置。因为所有盘片上的磁头一起移动所以当某一个盘片的磁头在第i条磁道上时所有其他盘片的磁头也都在各自盘片的第i条磁道上。因此所有盘片的第i条磁道合在一起称为第 i 个柱面。磁盘工作时通过反转磁性物质磁化的方向磁头将信息磁化存储到扇区中。 数据库管理系统或文件系统维护一个数据页到物理块磁盘块的映射表。这个映射表记录了每个数据页对应的物理块的位置通常包括磁盘块的地址或偏移量。当需要访问或修改特定数据页时B树结构或数据库管理系统会查找映射表找到相应的物理块。然后它执行磁盘读写操作读取或写入该磁盘块上的数据。
