1.1.1 摘要
如果说要对数据库进行优化,我们主要可以通过以下五种方法,对数据库系统进行优化。
1. 计算机硬件调优
2. 应用程序调优
3. 数据库索引优化
4. SQL语句优化
5. 事务处理调优
在本篇博文中,我们将想大家讲述数据库中索引类型和使用场合,本文以SQL Server为例,对于其他技术平台的朋友也是有参考价值的,只要替换相对应的代码就行了!
索引使数据库引擎执行速度更快,有针对性的数据检索,而不是简单地整表扫描(Full table scan)。
为了使用有效的索引,我们必须对索引的构成有所了解,而且我们知道在数据表中添加索引必然需要创建和维护索引表,所以我们要全局地衡量添加索引是否能提高数据库系统的查询性能。
本文目录
1.1.2 正文
在物理层面上,数据库有数据文件组成,而这些数据文件可以组成文件组,然后存储在磁盘上。每个文件包含许多区,每个区的大小为64K由八个物理上连续的页组成(一个页8K),我们知道页是SQL Server数据库中的数据存储的基本单位。为数据库中的数据文件(.mdf 或 .ndf)分配的磁盘空间可以从逻辑上划分成页(从0到n连续编号)。
页中存储的类型有:数据,索引和溢出。
文件和文件组
在SQL Server中,通过文件组这个逻辑对象对存放数据的文件进行管理。
图1数据库文件组织
在顶层是我们的数据库,由于数据库是由一个或多个文件组组成,而文件组是由一个或多个文件组成的逻辑组,所以我们可以把文件组分散到不同的磁盘中,使用户数据尽可能跨越多个设备,多个I/O 运转,避免 I/O 竞争,从而均衡I/O负载,克服访问瓶颈。
区和页
如图2所示,文件是由区组成的,而区由八个物理上连续的页组成,由于区的大小为64K,所以每当增加一个区文件就增加64K。
图2文件组成
页中保存的数据类型有:表数据、索引数据、溢出数据、分配映射、页空闲空间、索引分配等,具体如下图所示:
页类型 |
内容 |
Data |
当 text in row 设置为 ON 时,包含除 text、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 数据之外的所有数据的数据行。 |
Index |
索引条目。 |
Text/Image |
大型对象数据类型:text 、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 数据。数据行超过 8 KB 时为可变长度数据类型列:varchar 、nvarchar、varbinary 和 sql_variant |
Global Allocation Map、Shared Global Allocation Map |
有关区是否分配的信息。 |
Page Free Space |
有关页分配和页的可用空间的信息。 |
Index Allocation Map |
有关每个分配单元中表或索引所使用的区的信息。 |
Bulk Changed Map |
有关每个分配单元中自最后一条 BACKUP LOG 语句之后的大容量操作所修改的区的信息。 |
Differential Changed Map |
有关每个分配单元中自最后一条 BACKUP DATABASE 语句之后更改的区的信息。 |
表1页中保存的数据类型
在数据页上,数据行紧接着页头(标头)按顺序放置;页头包含标识值,如页码或对象数据的对象ID;数据行持有实际的数据;最后,页的末尾是行偏移表,对于页中的每一行,每个行偏移表都包含一个条目,每个条目记录对应行的第一个字节与页头的距离,行偏移表中的条目的顺序与页中行的顺序相反。
图3数据页
索引的基本结构
“索引(Index)提供查询的速度”这是对索引的最基本的解释,接下来我们将通过介绍索引的组成,让大家对索引有更深入的理解。
索引是数据库中的一个独特的结构,由于它保存数据库信息,那么我们就需要给它分配磁盘空间和维护索引表。创建索引并不会改变表中的数据,它只是创建了一个新的数据结构指向数据表;打个比方,平时我们使用字典查字时,首先我们要知道查询单词起始字母,然后翻到目录页,接着查找单词具体在哪一页,这时我们目录就是索引表,而目录项就是索引了。
当然,索引比字典目录更为复杂,因为数据库必须处理插入,删除和更新等操作,这些操作将导致索引发生变化。
叶节点
假设我们磁盘上的数据是物理有序的,那么数据库在进行插入,删除和更新操作时,必然会导致数据发生变化,如果我们要保存数据的连续和有序,那么我们就需要移动数据的物理位置,这将增大磁盘的I/O,使得整个数据库运行非常缓慢;使用索引的主要目的是使数据逻辑有序,使数据独立于物理有序存储。
为了实现数据逻辑有序,索引使用双向链表的数据结构来保持数据逻辑顺序,如果要在两个节点中插入一个新的节点只需修改节点的前驱和后继,而且无需修改新节点的物理位置。
双向链表(Doubly linked list)也叫双链表,是链表的一种,它的每个数据结点中都有两个指针,分别指向直接后继和直接前驱。所以,从双向链表中的任意一个结点开始,都可以很方便地访问它的前驱结点和后继结点。
理论上说,从双向链表中删除一个元素操作的时间复杂度是O(1),如果希望删除一个具体有给定关键字的元素,那么最坏的情况下的时间复杂度为O(n)。
在删除的过程中,我们只需要将要删除的节点的前节点和后节点相连,然后将要删除的节点的前节点和后