1 / 64

UNIT four 索引

UNIT four 索引. 武汉大学计算机学院. 思考几个问题. 在创建数据库、创建表时，你指定了存储设备和所需的存储空间了吗？你的 DBMS 是如何进行磁盘资源分配的？为了提高系统性能，你能在物理结构设计方法做哪些努力？ 在创建索引时，你使用了 CREATE INDEX 的哪些子句？你了解各种索引的工作原理吗？下面是 ORACLE 和 DB2 UDB 的 CREATE INDEX 的完整语法：. 思考几个问题. ORACLE 的 Create Index 语句的语法：

paige
Download Presentation

UNIT four 索引

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. UNIT four索引 武汉大学计算机学院

  2. 思考几个问题 • 在创建数据库、创建表时，你指定了存储设备和所需的存储空间了吗？你的DBMS是如何进行磁盘资源分配的？为了提高系统性能，你能在物理结构设计方法做哪些努力？ • 在创建索引时，你使用了CREATE INDEX的哪些子句？你了解各种索引的工作原理吗？下面是ORACLE和DB2 UDB的CREATE INDEX的完整语法：

  3. 思考几个问题 • ORACLE 的Create Index语句的语法： CREATE [UNIQUE | BITMAP] INDEX [Schema.]indexname ON [Schema.] tablename (columnname [ASC|DESC]{, columnname [ASC|DESC]}) [TABLESPACE tblspacename] [STORAGE ([INITIAL n [K|M]] [NEXT n [K|M]] [MINEXTENTS n] [MAXEXTENTS n] [PCTINCREASE n])] [PCTFREE n] [other disk storage and transactional clauses not covered or defered] [NOSORT]

  4. 思考几个问题 • DB2 UDB 的Create Index语句的语法： CREATE [UNIQUE] INDEX indexname ON tablename (columnname [ASC|DESC]{, columnname [ASC|DESC]}) [INCLUDE(columnname[ASC|DESC]{,columnname[ASC|DESC]})] [CLUSTER] [PCTFREE n] [MINPCTUSED n] [ALLOW RESERSE SCANS];

  5. 本 讲 主 要 目 标 学完本讲后，你应该能够了解： 1. 数据库索引设计的最重要目标就是要减少读数据所需要的磁盘访问的次数； • 所有的磁盘访问都是“面向页面的”，DBA应该针对特定的数据库产品分配表的磁盘空间，并在合适的时候进行数据库重组织； • 减少磁盘访问次数的一种方法是在主存中保留尽可能多的页面——即缓冲区的概念； • B+-tree索引、聚簇索引和非聚簇索引、位图索引的特点； • 如何利用Create Index语句创建合适的索引来优化应用程序和交互操作。

  6. 一．磁盘存储 二．索引的概念 三．B+-tree索引结构 四．聚簇索引 五. 创建一个有效的索引 内容提纲

  7. 磁盘存储

  8. 磁 盘 存 储 • 磁盘子系统 • 磁盘访问速度是极慢的 • 缓冲区管理 • 廉价磁盘冗余阵列（RAID） • 磁盘资源分配 • 数据存储页面和行指针

  9. 磁 盘 存 储 读写头 • 磁盘子系统 盘片 磁盘臂 装置 磁道 柱面 可移动磁头 磁盘装置 扇区 磁盘臂

  10. 系统总线 磁盘控制器 磁盘 磁盘子系统 磁 盘 存 储 • 磁盘子系统 所有的磁盘访问都是“面向页面的”

  11. 磁 盘 存 储 • 磁盘访问速度是极慢的 磁盘页面访问时间 = 寻道时间 + 旋转延时 + 传输时间 磁盘臂移动到指定柱面的时间 磁盘旋转到指定扇区的时间

  12. 磁 盘 存 储 • 磁盘访问速度是极慢的 一次磁盘访问需要大致0.0125秒，时间分配为： 寻道时间： 0.008秒 旋转延时： 0.004秒 传输时间： 0.0005秒 DBA可以通过合理 分配磁盘空间， 使要访问的数据 尽量集中在更少 的页面，并且这 些页面在磁盘上 紧挨着 若两个要连续读取的页面在磁盘上是紧挨着的，则寻道时间是很短的；若在同一柱面上，则寻道时间为零

  13. 散列后备表 h(dp) 缓冲区 要读取的磁盘 页面地址dp 磁 盘 存 储 • 缓冲区管理 减少磁盘访问次数的一种方法是在主存中保留尽可能多的页面，目的是增大访问的页面在主存的机会，这样就不再需要访问磁盘。

  14. 1 2 3 4 5 6 7 8 9 n 1 5 9 2 6 3 7 4 8 磁 盘 存 储 • 廉价磁盘冗余阵列（RAID） RAID（Redundant Array of Inexpensive Disks）的主要特性是：多个物理磁盘驱动器可以组合成一个逻辑磁盘驱动器 作用：解决性能和可靠性问题

  15. 磁 盘 存 储 • 磁盘资源分配 • 表的磁盘分配是DBA的最重要任务之一； • 在创建数据库前，DBA首先需要在磁盘上分配一些操作系统文件； • 让磁盘空间连续分布可以使得寻道时间最小； • 表空间—— 是ORACLE数据库基本的分配介质，所有请求磁盘空间的表、索引和其他对象都在表空间得到分配给它们的空间； • 表空间对应于一个或多个操作系统文件，也可以跨越多个操作系统文件； • 所有的数据库产品都有类似于表空间的结构来隔离用户与操作系统。

  16. 数据库 表空间1 SYSTEM 文件1 文件2 文件3 表1 表2 表3 表4 索引 数据段 数据段 数据段 数据段 索引段 区 域 区 域 区 域 磁 盘 存 储 • 磁盘资源分配 ORACLE数据库的存储结构：

  17. 磁 盘 存 储 • 磁盘资源分配 ORACLE的CREATE TABLESPACE语句： CREATE TABLESPACE tblspname DATAFILE 'filename' [SIZE n [K|M]] [REUSE] [AUTOEXTEND OFF | AUTOEXTEND ON [NEXT n [K|M] [MAXSIZE {UNLIMITED | n [K|M]}] {, 'filename' . . .} [ONLINE | OFFLINE] [DEFAULT STORAGE ( [INITIAL n[K|M] ] [ NEXT n[K|M] ] [MINEXTENTS n] [MAXEXTENTS {n|UNLIMITED}] [PCTINCREASE n])] [MINIMUM EXTENT n [K|M] ]；

  18. 磁 盘 存 储 • 磁盘资源分配 AUTOEXTEND ON [NEXT n [K|M] [MAXSIZE {UNLIMITED | n [K|M]}子句： • AUTOEXTEND ON：自动扩展数据文件的大小； • NEXT n [K|M]：每次新申请的区域的大小； • MAXSIZE {UNLIMITED | n [K|M]} ：文件最大可扩展的大小，可以是有限的字节数，也可以是无限制的。

  19. 磁 盘 存 储 • 磁盘资源分配 DEFAULT STORAGE ([INITIAL n[K|M] ] [NEXT n[K|M] ] [MINEXTENTS n] [ MAXEXTENTS {n | UNLIMITED } ] [PCTINCREASE n] )子句： • INITIAL n[K|M]：初始区域的大小； • NEXT n[K|M]：下一个区域的大小； • MINEXTENTS n：可以分配的区域的最小个数； • MAXEXTENTS {n | UNLIMITED }：可以分配的区域的最大个数； • PCTINCREASE n：每一次后续的区域比前一个区域大的百分比；

  20. 磁 盘 存 储 • 磁盘资源分配 MINIMUM EXTENTn [K|M]子句： 每次分配的的区域大小的最小值是n[K|M]个字节。

  21. 磁 盘 存 储 • 磁盘资源分配 ORACLE的CREATE TABLE语句： create table [schema.]tablename ({colname datatype [default {constant|NULL}] [col_constr] {, col_constr…} | table_constr} {, colname datatype etc. . . .} [ORGANIZATION HEAP | ORGANIZATION INDEX] [tablespace tblspname] [storage ([initial n [K|M]] [next n [K|M]] [minextents n] [maxextents n] [pctincrease n] ) ] [PCTFREE n] [PCTUSED n] [as subquery]

  22. 磁 盘 存 储 • 磁盘资源分配 • ORGANIZATION HEAP | ORGANIZATION INDEX 堆组织的文件新的行存放在任何方便的位置 索引组织的文件新的行存放在索引里，按主键值排序 • PCTFREE n 决定每个磁盘页面上可以用于行的插入的空间的百分比 • PCTUSED n 指定一个条件，当已用空间降到某个百分比以下时，新的行可以继续插入

  23. 头 信 息 行N 行N-1 … 行1 1 2 … N 可用空间 行目录 数据行 磁盘页面中的行布局 磁 盘 存 储 • 数据存储页面和行指针 • 行是否可以跨越多个页面与系统有关。 • 如果可能的话，尽量避免分段的行的出现。（在ORACLE中，使用CREATE TABLE语句中的PCTFREE在页面上空出尽量多的空间来处理行的增大） • 减少片段是需要进行数据库重组织的原因之一。

  24. 索引的概念

  25. 索 引 的 概 念 顺序索引 基本表 索引表 索引按顺序存储搜索码的值， 并将搜索码与包含该搜索码的记录关联起来

  26. 索 引 的 概 念 多级索引 数据块0 索引块0 数据块1 。 。 。 外层索引 索引块1 内层索引 • 如果索引小到能够放到主存中，搜索一个索引项的时间会很少。 • 利用多级索引搜索比二分法搜索要快得多

  27. 索 引 的 概 念 • 数据库索引包含的数据量比一次能调入内存的数据量大； • 数据库索引设计的最重要目标就是要减少读数据所需的磁盘访问次数； • 数据库管理员负责创建和删除索引，而索引的使用由数据库管理系统决定； • Create Index没有标准。原因是：索引策略需要考虑数据库系统的内部体系结构。

  28. B+-tree索引结构

  29. B+-tree 索 引 结 构 • B+-tree的结构 • B+-tree的查询 • B+-tree的插入与删除 • B+-tree的属性 • 位图索引 • Create Index语句

  30. B+-tree 索 引 结 构 • B+-tree的结构 B+-tree索引是一个多级索引，但是其结构不同于多级索引顺序文件， B+-tree索引的每一个节点就是一个页面，节点分为目录层节点和叶子层节点两大类，结构为： 目录层 叶子层

  31. B+-tree 索 引 结 构 • B+-tree的结构 典型的B+-tree结构的叶节点为： 其中： • K1，K2， ···，Kn-1是键值； • P1，P2， ···，Pn-1是指向每一个具有键值K1，K2， ···，Kn-1的数据记录的指针 ； • Pn是指向下一个叶节点的指针。

  32. Martin Martin Ota Phua Rudd ... ... Martin 1234 ... Smith 1434 ... Martin 7778 ... Smith 5778 ... Smith 5878 ... Ota 7978 ... Phua 7878 ... White 2234 ... ... ... White 6078 Funk 1634 ... ... ... ... ... ... B+-tree 索 引 结 构 • B+-tree的结构 例如： 叶节点页 Akhtar Ganio Smith Barr Hall Smith Con Jones Smith Funk Jones White Funk Jones White ... ... ... ... ... ... 数据页 Ganio 2334 ... Akhtar 7678 ... Barr 5678 ... Hall 8078 ... Jones 2534 ... Con 2434 ... Funk 1334 ... Jones 5978 ... ... ... Jones 1534 ... ... ... ... ... ...

  33. B+-tree 索 引 结 构 • B+-tree的结构 典型的B+-tree结构的目录节点为： 其中： • K1，K2， ···，Kn-1是键值； • P1，P2， ···，Pn是指向每一个叶节点的指针（页面号）； • 目录节点形成叶节点上的一个多级索引

  34. Akhtar Akhtar … … Martin Martin Akhtar Akhtar Ganio Ganio … … Martin Martin Martin Martin Smith Smith … … Ganio Martin Smith 叶节点页 Akhtar Hall Martin Smith Barr Jones Ota Smith Con Jones Phua White Funk Jones Rudd White Funk ... ... ... ... ... ... ... ... B+-tree 索 引 结 构 • B+-tree的结构 例如：

  35. B+-tree 索 引 结 构 • B+-tree的结构 根层节点 第2层节点 叶子层节点

  36. B+-tree 索 引 结 构 • B+-tree的结构 • 所有的商业产品都采用B+-tree • 数据键值全部在叶子节点 • 索引页(非叶子节点) 的分支很大 • 多数都在内存缓冲区 • 存放的都是redundant data • 大多数表只需要几层

  37. Akhtar Akhtar … … Martin Martin Akhtar Akhtar Ganio Ganio … … Martin Martin Martin Martin Smith Smith … … Ganio Martin Smith Akhtar Hall Martin Smith Barr Jones Ota Smith Con Jones Phua White Funk Jones Rudd White Funk ... ... ... ... ... ... ... ... B+-tree 索 引 结 构 • B+-tree的查询 查询“Martin”的记录：

  38. B+-tree 索 引 结 构 • B+-tree的插入与删除 插入和删除要比查找复杂得多，因为节点可能因为插入变得过大而需要分裂或因为删除变得过小而需要合并。此外，当一个节点分裂或一对节点合并时，必须保证B+-tree能保持平衡。

  39. 根页面 根页面 np 62 np np 41 np 88 np 第二层 索引页面 叶子页面 np 41 np np 88 np 7 39 41 55 65 88 96 7 39 41 55 62 65 88 95 叶子页面 根页面 np 41 np 62 np 88 np 叶子页面 7 39 62 65 88 96 41 55 B+-tree 索 引 结 构 • B+-tree的插入与删除 假定叶子节点可以包含三个索引项，高层索引节点可以包含三个节点指针np。例：插入62 插入62的中间形式：

  40. B+-tree 索 引 结 构 • B+-tree的属性 B+-tree具有从根到叶多个扇出的特性。 它具有下列属性： • 每个节点都和磁盘页面大小一致，并存放在磁盘上； • 叶子层以上的节点包含目录项，有n-1个分隔符键值和n个指向下层节点的磁盘指针； • 叶子层上的节点包含形式为（keyval,ROWID）的项，指向被索引的行； • 根节点以下的所有节点都至少是半满的（进行多次删除之后，可能会不满足这一条件）； • 根节点至少有两个项（除非只有一行被索引，并且根节点是包含一个（keyval,ROWID）对的叶节点）。

  41. B+-tree 索 引 结 构 • 位图索引 位图索引的思想： 有N行的表中，每一行都有一个原始编号，依次是0，1，2，…,N-1。连续编号的行，无论在什么情况下，在磁盘上都是物理连续的，而且一定有一个函数能进行原始编号到ROWID的转换。而位图是一个有N位的序列（每一位的值是0或者1，8位是一个字节），位图能表示任何一个ROWID的列表L：如果原始编号为k的ROWID值在列表L中，那么N位位图的第k位被设置为1，否则就被设置为0。 每一个位图都代表了B树中一个键值所对应的排序了的ROWID列表。

  42. B+-tree 索 引 结 构 • 位图索引 例如： 基本表 item位图索引表 city位图索引表 键值H的位图

  43. B+-tree 索 引 结 构 • 位图索引 Select * From R Where item=“H” and city=“V”； 基本表R item位图索引表 city位图索引表

  44. B+-tree 索 引 结 构 • Create Index语句 • 先装载记录，然后在上面创建索引比先创建索引、然后再装载数据的效率要高； • 在大多数数据库系统中，我们可以控制B+-tree开始创建的时候节点满的程度。通过CREATE INDEX语句的PCTFREE n 子句说明，n的值可以从0到99，决定索引第一次创建时，节点页面不被填充的百分比； • 在创建索引时，UNIQUE或者BITMAP不能同时使用。

  45. 聚簇索引

  46. 聚 簇 索 引 • 聚簇索引的特点 • 聚簇索引的优点 • 聚簇索引的查找 • 聚簇索引的创建

  47. Akhtar … Martin Node Pages Akhtar Page 140 - Root Ganio Martin … Smith … Page 141 Page 145 Akhtar 2334 ... Ganio 7678 ... Martin 1234 ... Smith 1434 ... Barr 5678 ... Hall 8078 ... Martin 7778 ... Smith 5778 ... LeafPages Con 2534 ... Jones 2434 ... Ota 5878 ... Smith 7978 ... Funk 1334 ... Jones 5978 ... Phua 7878 ... White 2234 ... ... ... ... ... Funk 1534 Jones 2634 Rudd 6078 White 1634 ... ... ... ... ... ... ... ... ... ... ... ... Page 100 Page 110 Page 120 Page 130 聚 簇 索 引 一般索引结构

  48. 聚 簇 索 引 • 聚簇索引的特点 • 聚簇(clustering)——根据索引键值把记录行放在磁盘上被称为聚簇。 • 聚簇索引(clustered index)——所引用的行和键值顺序一样的索引称为聚簇索引。

  49. 叶节点页 Akhtar Ganio Martin Smith Barr Hall Martin Smith Con Jones Ota Smith Funk Jones Phua White Funk Jones Rudd White ... ... ... ... ... ... ... ... 数据页 Ganio 2334 ... Akhtar 7678 ... Martin 1234 ... Smith 1434 ... Barr 5678 ... Hall 8078 ... Martin 7778 ... Smith 5778 ... Jones 2534 ... Con 2434 ... Smith 5878 ... Ota 7978 ... Funk 1334 ... Jones 5978 ... Phua 7878 ... White 2234 ... ... ... ... ... Jones 1534 White 6078 Funk 1634 ... ... ... ... ... ... ... ... ... ... ... ... 聚 簇 索 引 • 聚簇索引的特点 非聚簇索引：

  50. 叶节点页 Akhtar Ganio Smith Martin Barr Hall Smith Martin Con Jones Smith Ota Funk Jones White Phua Funk Jones White Rudd ... ... ... ... ... ... ... ... Akhtar 2334 ... Ganio 7678 ... Martin 1234 ... Smith 1434 ... Barr 5678 ... Hall 8078 ... Martin 7778 ... Smith 5778 ... Con 2534 ... Jones 2434 ... Ota 5878 ... Smith 7978 ... 数据页 Funk 1334 ... Jones 5978 ... Phua 7878 ... White 2234 ... ... ... ... ... Funk 1534 Jones 2634 Rudd 6078 White 1634 ... ... ... ... ... ... ... ... ... ... ... ... 聚 簇 索 引 • 聚簇索引的特点 聚簇索引：

More Related