做医疗信息网站的域名,系统开发的主要方法有生命周期法,北京网站主题制作,商城网站建设咨询1、概述
通常我们要对数据库进行优化#xff0c;主要可以通过以下五种方法。
计算机硬件调优应用程序调优数据库索引优化SQL语句优化事务处理调优 本篇文章将向大家介绍数据库中索引类型和使用场合#xff0c;本文以SQL Server为例#xff0c;对于其他技术平台的朋友也是有…1、概述
通常我们要对数据库进行优化主要可以通过以下五种方法。
计算机硬件调优应用程序调优数据库索引优化SQL语句优化事务处理调优 本篇文章将向大家介绍数据库中索引类型和使用场合本文以SQL Server为例对于其他技术平台的朋友也是有参考价值的原理差不多。
查询数据时索引使数据库引擎执行速度更快有针对性的数据检索而不是简单地整表扫描Full table scan。
为了有效的使用索引我们必须对索引的构成有所了解而且我们知道在数据表中添加索引必然需要创建和维护索引表所以我们要全局地衡量添加索引是否能提高数据库系统的查询性能。
2、数据库中的文件和文件组
在物理层面上数据库由数据文件组成而这些数据文件组成文件组然后存储在磁盘上。每个文件包含许多区每个区的大小为64K由八个物理上连续的页组成一个页8K我们知道页是SQL Server数据库中的数据存储的基本单位。为数据库中的数据文件.mdf 或 .ndf分配的磁盘空间可以从逻辑上划分成页从0到n连续编号。
页中存储的类型有数据索引和溢出。
在SQL Server中通过文件组这个逻辑对象对存放数据的文件进行管理。 在顶层是我们的数据库由于数据库是由一个或多个文件组组成而文件组是由一个或多个文件组成的逻辑组所以我们可以把文件组分散到不同的磁盘中使用户数据尽可能跨越多个设备多个I/O 运转避免 I/O 竞争从而均衡I/O负载克服访问瓶颈。
3、区和页
如下图所示文件是由区组成的而区由八个物理上连续的页组成由于区的大小为64K所以每当增加一个区文件就增加64K。 页中保存的数据类型有表数据、索引数据、溢出数据、分配映射、页空闲空间、索引分配等。
页类型内容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_variantGlobal Allocation Map、Shared Global Allocation Map有关区是否分配的信息。Page Free Space有关页分配和页的可用空间的信息。Index Allocation Map有关每个分配单元中表或索引所使用的区的信息。Bulk Changed Map有关每个分配单元中自最后一条 BACKUP LOG 语句之后的大容量操作所修改的区的信息。Differential Changed Map有关每个分配单元中自最后一条 BACKUP DATABASE 语句之后更改的区的信息。
在数据页上数据行紧接着页头标头按顺序放置页头包含标识值如页码或对象数据的对象ID数据行持有实际的数据最后页的末尾是行偏移表对于页中的每一行每个行偏移表都包含一个条目每个条目记录对应行的第一个字节与页头的距离行偏移表中的条目的顺序与页中行的顺序相反。 4、索引的基本结构
“索引Index提供查询的速度”这是对索引的最基本的解释接下来我们将通过介绍索引的组成让大家对索引有更深入的理解。
索引是数据库中的一个独特的结构由于它保存数据库信息那么我们就需要给它分配磁盘空间和维护索引表。创建索引并不会改变表中的数据它只是创建了一个新的数据结构指向数据表打个比方平时我们使用字典查字时首先我们要知道查询单词起始字母然后翻到目录页接着查找单词具体在哪一页这时我们目录就是索引表而目录项就是索引了。
当然索引比字典目录更为复杂因为数据库必须处理插入删除和更新等操作这些操作将导致索引发生变化。
叶节点
假设我们磁盘上的数据是物理有序的那么数据库在进行插入删除和更新操作时必然会导致数据发生变化如果我们要保存数据的连续和有序那么我们就需要移动数据的物理位置这将增大磁盘的I/O使得整个数据库运行非常缓慢使用索引的主要目的是使数据逻辑有序使数据独立于物理有序存储。
为了实现数据逻辑有序索引使用双向链表的数据结构来保持数据逻辑顺序如果要在两个节点中插入一个新的节点只需修改节点的前驱和后继而且无需修改新节点的物理位置。
双向链表Doubly linked list也叫双链表是链表的一种它的每个数据结点中都有两个指针分别指向直接后继和直接前驱。所以从双向链表中的任意一个结点开始都可以很方便地访问它的前驱结点和后继结点。
理论上说从双向链表中删除一个元素操作的时间复杂度是O(1)如果希望删除一个具体有给定关键字的元素那么最坏的情况下的时间复杂度为O(n)。
在删除的过程中我们只需要将要删除的节点的前节点和后节点相连然后将要删除的节点的前节点和后节点置为null即可。
//伪代码
node.prev.nextnode.next;
node.next.prevnode.prev;
node.prevnode.nextnull;如上图索引叶节点包含索引值和相应的RIDROWID而且叶节点通过双向链表有序地连接起来同时我们主要到数据表不同于索引叶节点表中的数据无序存储它们不全是存储在同一表块中而且块之间不存在连接。
总的来说索引保存着具体数据的物理地址值。
5、索引的类型
索引的类型主要有两种聚集索引和非聚集索引。
聚集索引物理存储按照索引排序。
指数据库表行中数据的物理顺序与键值的逻辑索引顺序相同。一个表只能有一个聚集索引因为一个表的物理顺序只有一种情况所以对应的聚集索引只能有一个。如果某索引不是聚集索引则表中的行物理顺序与索引顺序不匹配与非聚集索引相比聚集索引有着更快的检索速度。
非聚集索引物理存储不按照索引排序。
该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同一个表中可以拥有多个非聚集索引。除了聚集索引以外的索引都是非聚集索引只是人们想细分一下非聚集索引分成普通索引唯一索引全文索引。如果非要把非聚集索引类比成现实生活中的东西那么非聚集索引就像新华字典的偏旁字典他结构顺序与实际存放顺序不一定一致。
5.1、聚集索引
聚集索引的数据页是物理有序地存储数据页是聚集索引的叶节点数据页之间通过双向链表的形式连接起来而且实际的数据都存储在数据页中。当我们给表添加索引后表中的数据将根据索引进行排序。
假设我们有一个表T_Pet它包含四个字段分别是animalnamesex和age而且使用animal作为索引列具体SQL代码如下
-----------------------------------------------------------
---- Create T_Pet table in tempdb.
-----------------------------------------------------------
USE tempdbCREATE TABLE T_Pet
(animal VARCHAR(20),[name] VARCHAR(20),sex CHAR(1),age INT
)CREATE UNIQUE CLUSTERED INDEX T_PetonAnimal1_ClterIdx ON T_Pet (animal)-----------------------------------------------------------
---- Insert data into data table.
-----------------------------------------------------------DECLARE i intSET i0
WHILE(i1000000)
BEGININSERT INTO T_Pet (animal,[name],sex,age)SELECT [dbo].random_string(11) animal,[dbo].random_string(11) [name],F sex,cast(floor(rand()*5) as int) age SET ii1ENDINSERT INTO T_Pet VALUES(Aardark, Hello, F, 1)
INSERT INTO T_Pet VALUES(Cat, Kitty, F, 2)
INSERT INTO T_Pet VALUES(Horse, Ma, F, 1)
INSERT INTO T_Pet VALUES(Turtles, SiSi, F, 4)
INSERT INTO T_Pet VALUES(Dog, Tomma, F, 2)
INSERT INTO T_Pet VALUES(Donkey, YoYo, F, 3)如上图所示从左往右的第一和第二层是索引页第三层是数据页叶节点数据页之间通过双向链表连接起来而且数据页中的数据根据索引排序假设我们要查找名字name为Xnnbqba的动物Ifcey这里我们以animal作为表的索引所以数据库首先根据索引查找当找到索引值animal ‘Ifcey时接着查找该索引的数据页叶节点获取具体数据。具体的查询语句如下
SET STATISTICS PROFILE ON
SET STATISTICS TIME ONSELECT animal, [name], sex, age
FROM T_Pet
WHERE animal IfceySET STATISTICS PROFILE OFF
SET STATISTICS TIME OFF当我们执行完SQL查询计划时把鼠标指针放到“聚集索引查找”上这时会出现如下图信息我们可以查看到一个重要的信息Logical Operation——Clustered Index SeekSQL查询是直接根据聚集索引获取记录查询速度最快。 从下图查询结果我们发现查询步骤只有2步首先通过Clustered Index Seek快速地找到索引Ifcey接着查询索引的叶节点数据页获取数据。
查询执行时间CPU 时间 0 毫秒占用时间 1 毫秒。 现在我们把表中的索引删除重新执行查询计划这时我们可以发现Logical Operation已经变为Table Scan由于表中有100万行数据这时查询速度就相当缓慢。 从下图查询结果我们发现查询步骤变成3步了首先通过Table Scan查找animal ‘Ifcey’在执行查询的时候SQL Server会自动分析SQL语句而且它估计我们这次查询比较耗时所以数据库进行并发操作加快查询的速度。
查询执行时间CPU 时间 329 毫秒占用时间 182 毫秒。 通过上面的有聚集索引和没有的对比我们发现了查询性能的差异如果使用索引数据库首先查找索引而不是漫无目的的全表遍历。
5.2、非聚集索引
在没有聚集索引的情况下表中的数据页是通过堆(Heap)形式进行存储堆是不含聚集索引的表SQL Server中的堆存储是把新的数据行存储到最后一个页中。
非聚集索引是物理存储不按照索引排序非聚集索引的叶节点Index leaf pages包含着指向具体数据行的指针或聚集索引数据页之间没有连接是相对独立的页。
假设我们有一个表T_Pet它包含四个字段分别是animalnamesex和age而且使用animal作为非索引列具体SQL代码如下
-----------------------------------------------------------
---- Create T_Pet table in tempdb with NONCLUSTERED INDEX.
-----------------------------------------------------------
USE tempdbCREATE TABLE T_Pet
(animal VARCHAR(20),[name] VARCHAR(20),sex CHAR(1),age INT
)CREATE UNIQUE NONCLUSTERED INDEX T_PetonAnimal1_NonClterIdx ON T_Pet (animal)接着我们要查询表中animal ‘Cat’的宠物信息具体的SQL代码如下
SET STATISTICS PROFILE ON
SET STATISTICS TIME ONSELECT animal, [name], sex, age
FROM T_Pet
WHERE animal CatSET STATISTICS PROFILE OFF
SET STATISTICS TIME OFF如下图所示我们发现查询计划的最右边有两个步骤RID和索引查找。由于这两种查找方式相对于聚集索引查找要慢Clustered Index Seek。 首先SQL Server查找索引值然后根据RID查找数据行直到找到符合查询条件的结果。
查询执行时间CPU 时间 0 毫秒占用时间 1 毫秒 5.3、堆表非聚集索引
由于堆是不含聚集索引的表所以非聚集索引的叶节点将包含指向具体数据行的指针。
以前面的T_Pet表为例假设T_Pet使用animal列作为非聚集索引那么它的堆表非聚集索引结构如下图所示 通过上图我们发现非聚集索引通过双向链表连接而且叶节点包含指向具体数据行的指针。
如果我们要查找animal ‘Dog’的信息首先我们遍历第一层索引然后数据库判断Dog属于Cat范围的索引接着遍历第二层索引然后找到Dog索引获取其中的保存的指针信息根据指针信息获取相应数据页中的数据接下来我们将通过具体的例子说明。
现在我们创建表employees然后给该表添加堆表非聚集索引具体SQL代码如下
USE tempdb---- Creates a sample table.
CREATE TABLE employees (employee_id NUMERIC NOT NULL,first_name VARCHAR(1000) NOT NULL,last_name VARCHAR(900) NOT NULL,date_of_birth DATETIME ,phone_number VARCHAR(1000) NOT NULL,junk CHAR(1000) ,CONSTRAINT employees_pk PRIMARY KEY NONCLUSTERED (employee_id)
);
GO现在我们查找employee_id 29976的员工信息。
SELECT *
FROM employees
WHERE employee_id 29976查询计划如下图所示 首先查找索引值employee_id ‘29976’的索引然后根据RID查找符合条件的数据行所以说堆表索引的查询效率不如聚集表接下来我们将介绍聚集表的非聚集索引。
5.4、聚集表非聚集索引
当表上存在聚集索引时任何非聚集索引的叶节点不再是包含指针值而是包含聚集索引的索引值。
以前面的T_Pet表为例假设T_Pet使用animal列作为非聚集索引那么它的索引表非聚集索引结构如下图所示 通过上图我们发现非聚集索引通过双向链表连接而且叶节点包含索引表的索引值。
如果我们要查找animal ‘Dog’的信息首先我们遍历第一层索引然后数据库判断Dog属于Cat范围的索引接着遍历第二层索引然后找到Dog索引获取其中的保存的索引值然后根据索引值获取相应数据页中的数据。
接下来我们修改之前的employees表首先我们删除之前的堆表非聚集索引然后增加索引表的非聚集索引具体SQL代码如下
ALTER TABLE employeesDROP CONSTRAINT employees_pkALTER TABLE employees ADD CONSTRAINT employees_pk PRIMARY KEY CLUSTERED (employee_id)
GO SELECT * FROM employees
WHERE employee_id299766、索引的有效性
SQL Server每执行一个查询首先要检查该查询是否存在执行计划如果没有则要生成一个执行计划那么什么是执行计划呢简单来说它能帮助SQL Server制定一个最优的查询计划。
下面我们将通过具体的例子说明SQL Server中索引的使用首先我们定义一个表testIndex它包含三个字段testIndexbitValue和filler具体的SQL代码如下
-----------------------------------------------------------
---- Index Usefulness sample
-----------------------------------------------------------CREATE TABLE testIndex
(testIndex int identity(1,1) constraint PKtestIndex primary key,bitValue bit,filler char(2000) not null default (replicate(A,2000))
)CREATE INDEX XtestIndex_bitValue on testIndex(bitValue)
GOINSERT INTO testIndex(bitValue)VALUES (0)
GO 20000 --runs current batch 20000 times.INSERT INTO testIndex(bitValue)VALUES (1)
GO 10 --puts 10 rows into table with value 1接着我们查询表中bitValue 0的数据行而且表中bitValue 0的数据有2000行。
SELECT *
FROM testIndex
WHERE bitValue 0现在我们查询bitValue 1的数据行。
SELECT *
FROM testIndex
WHERE bitValue 1现在我们注意到对同一个表不同数据查询居然执行截然不同的查询计划这究竟是什么原因导致的呢
我们可以通过使用DBCC SHOW_STATISTICS查看到表中索引的详细使用情况具体SQL代码如下
UPDATE STATISTICS dbo.testIndex
DBCC SHOW_STATISTICS(dbo.testIndex, XtestIndex_bitValue)
WITH HISTOGRAM通过上面的直方图我们知道SQL Server估计bitValue 0数据行行有约19989行而bitValue 1估计约21SQL Server优化器根据数据量估算值采取不同的执行计划从而到达最优的查询性能由于bitValue 0数据量大SQL Server只能提供扫描聚集索引获取相应数据行而bitValue 1实际数据行只有10行SQL Server首先通过键查找bitValue 1的数据行然后嵌套循环联接到聚集索引获得余下数据行。
7、索引的优缺点
优点 第一通过创建唯一性索引可以保证数据库表中每一行数据的唯一性。 第二可以大大加快 数据的检索速度这也是创建索引的最主要的原因。 第三可以加速表和表之间的连接特别是在实现数据的参考完整性方面特别有意义。 第四在使用分组和排序 子句进行数据检索时同样可以显著减少查询中分组和排序的时间。 第五通过使用索引可以在查询的过程中使用优化隐藏器提高系统的性能。
缺点 第一创建索引和维护索引要耗费时间这种时间随着数据量的增加而增加。 第二索引需要占物理空间除了数据表占数据空间之外每一个索引还要占一定的物理空间如果要建立聚簇索引那么需要的空间就会更大。 第三当对表中的数据进行增加、删除和修改的时候索引也要动态的维护这样就降低了数据的维护速度。
8、索引创建原则
1.定义主键的数据列一定要建立索引。 2.定义有外键的数据列一定要建立索引。 3.对于经常查询的数据列最好建立索引。 4.对于需要在指定范围内的快速或频繁查询的数据列; 5.经常用在WHERE子句中的数据列。 6.经常出现在关键字order by、group by、distinct后面的字段。如果建立的是复合索引索引的字段顺序要和这些关键字后面的字段顺序一致否则索引不会被使用。 7.对于那些查询中很少涉及的列重复值比较多的列不要建立索引。 8.对于定义为text、image和bit的数据类型的列不要建立索引。 9.对于经常存取的列不要建立索引 10.限制表上的索引数目。对一个存在大量更新操作的表所建索引的数目一般不要超过3个最多不要超过5个。索引虽说提高了访问速度但太多索引会影响数据的更新操作。 11.对复合索引按照字段在查询条件中出现的频度建立索引。在复合索引中记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录系统再按照第二个字段的取值排序以此类推。因此只有复合索引的第一个字段出现在查询条件中该索引才可能被使用,因此将应用频度高的字段放置在复合索引的前面会使系统最大可能地使用此索引发挥索引的作用。
9、参考文章
索引 - SQL Server | Microsoft Learn
聚集与非聚集索引 - SQL Server | Microsoft Learn
《ORACLE PL/SQL编程详解》全原创共八篇–系列文章导航
8 种主流数据迁移工具技术选型
SQLServer中的CTE(Common Table Expression)通用表表达式使用详解
[推荐推荐]ORACLE SQL经典查询练手系列文章收尾(目录篇)
国思RDIF低代码快速开发平台支持vue2、vue3 一路走来数个年头感谢RDIF框架的支持者与使用者大家可以通过下面的地址了解详情。
官方网站http://www.guosisoft.com/ http://www.rdiframework.net/
特别说明框架相关的技术文章请以官方网站为准欢迎大家收藏
RDIF.vNext低代码快速开发框架由海南国思软件科技有限公司专业团队长期打造、一直在更新、一直在升级请放心使用
欢迎关注RDIF.vNext低代码快速开发框架官方公众微信微信号guosisoft及时了解最新动态。 文章转载自： http://www.morning.nrydm.cn.gov.cn.nrydm.cn http://www.morning.bnqcm.cn.gov.cn.bnqcm.cn http://www.morning.mcjrf.cn.gov.cn.mcjrf.cn http://www.morning.nhdw.cn.gov.cn.nhdw.cn http://www.morning.btqrz.cn.gov.cn.btqrz.cn http://www.morning.bsbcp.cn.gov.cn.bsbcp.cn http://www.morning.dlbpn.cn.gov.cn.dlbpn.cn http://www.morning.gjmbk.cn.gov.cn.gjmbk.cn http://www.morning.lsbjj.cn.gov.cn.lsbjj.cn http://www.morning.jmnfh.cn.gov.cn.jmnfh.cn http://www.morning.yrfxb.cn.gov.cn.yrfxb.cn http://www.morning.qhln.cn.gov.cn.qhln.cn http://www.morning.kkqgf.cn.gov.cn.kkqgf.cn http://www.morning.mdrnn.cn.gov.cn.mdrnn.cn http://www.morning.cprbp.cn.gov.cn.cprbp.cn http://www.morning.fthcq.cn.gov.cn.fthcq.cn http://www.morning.kcxtz.cn.gov.cn.kcxtz.cn http://www.morning.dkqyg.cn.gov.cn.dkqyg.cn http://www.morning.jrdbq.cn.gov.cn.jrdbq.cn http://www.morning.stbhn.cn.gov.cn.stbhn.cn http://www.morning.rpsjh.cn.gov.cn.rpsjh.cn http://www.morning.grxsc.cn.gov.cn.grxsc.cn http://www.morning.mkfr.cn.gov.cn.mkfr.cn http://www.morning.bswhr.cn.gov.cn.bswhr.cn http://www.morning.bxgpy.cn.gov.cn.bxgpy.cn http://www.morning.cplym.cn.gov.cn.cplym.cn http://www.morning.ctlbf.cn.gov.cn.ctlbf.cn http://www.morning.dbrnl.cn.gov.cn.dbrnl.cn http://www.morning.rxlck.cn.gov.cn.rxlck.cn http://www.morning.ykklw.cn.gov.cn.ykklw.cn http://www.morning.dyght.cn.gov.cn.dyght.cn http://www.morning.xmwdt.cn.gov.cn.xmwdt.cn http://www.morning.qtqjx.cn.gov.cn.qtqjx.cn http://www.morning.fqyqm.cn.gov.cn.fqyqm.cn http://www.morning.kqrql.cn.gov.cn.kqrql.cn http://www.morning.bklhx.cn.gov.cn.bklhx.cn http://www.morning.hlwzd.cn.gov.cn.hlwzd.cn http://www.morning.lxjxl.cn.gov.cn.lxjxl.cn http://www.morning.gmnmh.cn.gov.cn.gmnmh.cn http://www.morning.jmbgl.cn.gov.cn.jmbgl.cn http://www.morning.lmrjn.cn.gov.cn.lmrjn.cn http://www.morning.twhgn.cn.gov.cn.twhgn.cn http://www.morning.dwncg.cn.gov.cn.dwncg.cn http://www.morning.ksqyj.cn.gov.cn.ksqyj.cn http://www.morning.rpms.cn.gov.cn.rpms.cn http://www.morning.rcntx.cn.gov.cn.rcntx.cn http://www.morning.qhkdt.cn.gov.cn.qhkdt.cn http://www.morning.hmlpn.cn.gov.cn.hmlpn.cn http://www.morning.kwrzg.cn.gov.cn.kwrzg.cn http://www.morning.kaylyea.com.gov.cn.kaylyea.com http://www.morning.tsnq.cn.gov.cn.tsnq.cn http://www.morning.ljdd.cn.gov.cn.ljdd.cn http://www.morning.wdykx.cn.gov.cn.wdykx.cn http://www.morning.wktbz.cn.gov.cn.wktbz.cn http://www.morning.xhfky.cn.gov.cn.xhfky.cn http://www.morning.knqzd.cn.gov.cn.knqzd.cn http://www.morning.gkktj.cn.gov.cn.gkktj.cn http://www.morning.jopebe.cn.gov.cn.jopebe.cn http://www.morning.qkcyk.cn.gov.cn.qkcyk.cn http://www.morning.rrgm.cn.gov.cn.rrgm.cn http://www.morning.stbfy.cn.gov.cn.stbfy.cn http://www.morning.rkmsm.cn.gov.cn.rkmsm.cn http://www.morning.sgmgz.cn.gov.cn.sgmgz.cn http://www.morning.ybshj.cn.gov.cn.ybshj.cn http://www.morning.mrfjr.cn.gov.cn.mrfjr.cn http://www.morning.rqjfm.cn.gov.cn.rqjfm.cn http://www.morning.kongpie.com.gov.cn.kongpie.com http://www.morning.ghccq.cn.gov.cn.ghccq.cn http://www.morning.bjjrtcsl.com.gov.cn.bjjrtcsl.com http://www.morning.jpkhn.cn.gov.cn.jpkhn.cn http://www.morning.wsxxq.cn.gov.cn.wsxxq.cn http://www.morning.yxshp.cn.gov.cn.yxshp.cn http://www.morning.snzgg.cn.gov.cn.snzgg.cn http://www.morning.jypqx.cn.gov.cn.jypqx.cn http://www.morning.cykqg.cn.gov.cn.cykqg.cn http://www.morning.rwhlf.cn.gov.cn.rwhlf.cn http://www.morning.ykmtz.cn.gov.cn.ykmtz.cn http://www.morning.ruifund.com.gov.cn.ruifund.com http://www.morning.nrfrd.cn.gov.cn.nrfrd.cn http://www.morning.yrbp.cn.gov.cn.yrbp.cn
