数据库索引、优化

参考地址:

如何看MS SQLSERVER数据库的执行计划https://blog.csdn.net/luoyanqing119/article/details/17022649

SQLserver索引的原理和应用https://www.cnblogs.com/knowledgesea/p/3672099.html 
聚集索引和非聚集索引https://www.cnblogs.com/aspnethot/articles/1504082.html

数据库SQL优化大总结之 百万级数据库优化方案https://www.cnblogs.com/yunfeifei/p/3850440.html

数据库优化之程序操作优化https://www.cnblogs.com/AK2012/archive/2012/12/28/2012-122803.html

为什么不建议用select *?https://blog.csdn.net/u013240038/article/details/90731874

数据库查询执行流程

 

 

 

1
2
3
备注:客户端软件管理硬盘上的mdf文件,对数据库执行增删改查,发送一条SQL语句给数据库服务(运行→services.msc可查看)经过查询分析器检查语法,将检查通过的
语句发给查询优化器,查询优化器再匹配已经写好的优化规则,生成优化树,查询之前缓存管理器先到数据库缓存中查找是会否有需要查询的结果,如果有则直接返回数据(这就是读
取数据库第二次比第一次快很多的原因),如果没有就读取硬盘中mdf文件,然后将读取到的数据保存到数据库缓存中和发给数据库服务,数据库服务再将结果发给DBMS呈献给用户!

 

 

 

1
2
3
4
5
6
7
a  当我们接收到一个查询语句的时候,第一件事就是解析这个查询语句,主要是看看他要查的是哪一个表啊,做得是一个什么join啊之类的。这一步也至少保证了我们的接收的查询是能够被解析的,否则我们也不能知道究竟要做些什么,<br>这里的检查更多的是语法方面的检查。
 
b  在解析完成之后,就是进行绑定,这个步骤更多的是一个名字的对应处理,就是把上一步解析出来的内容和我们系统中真实存在一些object进行对应,顺便也检查这些object是否真实存在,从而进一步对查询的有效性进行验证,<br>当然这里的检查就更多地是逻辑方面的检查了。
 
c  在绑定之后,我们就需要找到一个最优的执行计划,这里会有列出一些可能的执行计划,然后从中找到最优的执行计划。然而现实并不总是如我们所料,比如说一个查询语句,可能有几万,几十万设置几百万种执行计划,我们不可能<br>遍历检查所有的执行计划,然后找出最优的执行计划。因为这个遍历也是要花时间的,所以这里会有一个trade off。因为我们只能说这里会找到一个局部最优解(想到了AI,哈哈)来设置执行计划。
 
有了查询优化后的结果,在后面两步中我们就不需要动太多的脑筋,只要执行这个查询,并返回结果就可以了。

 了解了这样的整个过程之后,我们知道其实所有的关键就是查询优化这一块,为了让这一步能够选择出最优的执行计划,我们首先要来看一下它究竟会产生哪些执行计划,然后再来分析这些执行计划在我们的场景中是好还是不好,或者在我们的场景中哪些步骤占据了大的时间块,从而才可以给我们优化提供具体的建议。因此本文主要就是介绍SQL Server中的执行计划。 

  

数据访问的操作

首先我们来看一下数据访问的操作,所谓数据访问就是直接访问数据,可以是访问一个表也可以是访问一个索引。通常有两种方法:一种是扫描(scan)一种是查找(seek)。扫描就是读取整个结构,可以访问一个heap或者一个clustered索引或者一个non-clustered索引。而查找则不会读取整个结构,他则是更高效地通过索引访问一行,所以从这个角度来看,查找就只能应用在索引上面了。简单总结如下表所示:

 

下面我们来看一下几个扫描的例子,在开始具体的例子之前,先说明一下如何得到执行计划,其实得到执行计划有很多种方法,一种最简单的方法就是SSMS中打开‘include actual exectution plan’选项,如下图所示:

全表扫描

这样我们就可以看到下面的执行计划:

 

我们可以清楚的看到这里就是一个全表的扫描。

Clustered index扫描

这样我们可以看到下面的执行计划:

 

这里有一点需要强调的是,尽管我们的clustered index在保存的时候是有序的,但并不保证我们通过他进行scan出来的结果也是有序排列的,因此如果你想要按照clustered index进行排序的结果,那么请显式加上order by的语句,如下图所示:

此时把鼠标放到clustered index scan的图标上可以看到如下结果:

 

这里我们可以看到在弹出的property窗口中的ordered属性被置为了true,这就表明我们的结果是有序的,而你要是返回去看看之前的这个属性,毫无疑问,它是false的。

Non-clustered index扫描

我们可以看到下面的结果

 

这个例子中,我们查询了addressId, city以及stateProvinceId三列,而我们查询的是IX_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode,这个index是基于AddressLine1,AddressLine2,City,StateProvinceID和PostalCode这几列产生的,很显然city和stateProvinceId是处于这个index中的,但是addressID却不在其中,那我们究竟怎么得到这一列的呢?其实原因很简单,addressId是一个clustered index,所有的non clustered index都默认包含clustered index这一列,所以我们在设置non clustered index的时候没有必要显式包含clustered index的列。

上面所说的三种扫描都会扫描所有的结构,下面我们来看看查询的一些例子:

Clustered index查询

结果如下图所示:

 

Non-Clustered index查询

结果如下图所示:

 

对clustered index查询和non-clustered index查询来说,他们两者是差不多的,唯一的差别在于前者前者可以cover所有的column,而后者则只能特定的column,原因也很简单就是clustered index他在逻辑上就是根据clustering的key进行排序的。可惜的是一个表只能有一个clustered index。

书签查询(Bookmark lookup)

我们在上节最后说non-clustered index查询并不能cover所有的column,那如果我们查询的column有些在这个non-clustered index中,有些则不在,那这种时候该怎么办呢?有两种可能的选择,一种是先扫描non-clustered index,然后再去查询表格得到别的column,另外一种就是直接去扫描表格。这两种都是有可能的,具体情况可能需要具体分析。

使用方法一的例子:

结果如下图所示:

 

这里因为City和ModifiedDate不在non-clustered index中,我们选择的是先用这个index进行seek,然后再key lookup整个表。这里上面的index seek只会执行一遍,然后会执行n遍的下面的key lookup,n就是我们在index seek中找到的行数。把鼠标放到对应的执行计划中,我们可以看到Number of Executions分别是1(non-clustered index seek)和25 (key lookup).

 

使用方法二的例子:

同样的查询语句,不同的查询值也是可能有不同的执行计划的,我们这里把stateProviceID=1改成stateProviceID=20

执行结果如下图:

我们可以看到这里选择了一个基础表的扫描,而没有选择先去扫描nonclustered index的扫描,其实就这个简单例子来说,他主要是和输出的行数有关系的,我们不难想象,假如输出的函数够多,那我们就不如直接去扫描全表来得快了,大家可以自己去实验看这个输出行数的临界值是多少会影响到最招的执行计划。

上面的例子上,我们有一个clustered的index,有时我们还会进行heap的lookup,看下面这个例子:

结果如下:

 

至此,我们关于执行计划的第一部分的内容:数据访问操作以及书签查询就介绍完毕了,我们会在后续文章继续介绍别的执行计划的内容,敬请期待。

  • 原文链接:https://kuaibao.qq.com/s/20180624G0NV4B00?refer=cp_1026

索引

数据是什么?

  数据库就是把东西有序放好,还能随时找到的一个工具。应用程序,有序的数据管理,数据在硬盘上(持久化,唯一的,多线程操作需要加锁,速度慢,可以SSD加快速度)。char nvarchar字段最长是8kb。

什么是索引

在数据库中,索引的含义与日常意义上的“索引”一词并无多大区别(想想小时候查字典),它是用于提高数据库表数据访问速度的数据库对象。 
A)索引可以避免全表扫描。多数查询可以仅扫描少量索引页及数据页,而不是遍历所有数据页。 
B)对于非聚集索引,有些查询甚至可以不访问数据页。 
C)聚集索引可以避免数据插入操作集中于表的最后一个数据页。 
D)一些情况下,索引还可用于避免排序操作。

当然,众所周知,虽然索引可以提高查询速度,但是它们也会导致数据库系统更新数据的性能下降,因为大部分数据更新需要同时更新索引。

例如这样一个查询:select * from table1 where id=44。如果没有索引,必须遍历整个表,直到ID等于44的这一行被找到为止;有了索引之后(必须是在ID这一列上建立的索引),直接在索引里面找44(也就是在ID这一列找),就可以得知这一行的位置,也就是找到了这一行。可见,索引是用来定位的。

索引的存储

一条索引记录中包含的基本信息包括:键值(即你定义索引时指定的所有字段的值)+逻辑指针(指向数据页或者另一索引页)。

当你为一张空表创建索引时,数据库系统将为你分配一个索引页,该索引页在你插入数据前一直是空的。此页此时既是根结点,也是叶结点。每当你往表中插入一行数据,数据库系统即向此根结点中插入一行索引记录。当根结点满时,数据库系统大抵按以下步骤进行分裂: 
A)创建两个儿子结点 
B)将原根结点中的数据近似地拆成两半,分别写入新的两个儿子结点 
C)根结点中加上指向两个儿子结点的指针

通常状况下,由于索引记录仅包含索引字段值(以及4-9字节的指针),索引实体比真实的数据行要小许多,索引页相较数据页来说要密集许多。一个索引页可以存储数量更多的索引记录,这意味着在索引中查找时在I/O上占很大的优势,理解这一点有助于从本质上了解使用索引的优势。

索引的类型

A) 唯一索引:唯一索引不允许两行具有相同索引值

B) 主键索引:定义一个主键将自动创建主键索引,主键索引是唯一索引的特殊类型.要求每个值唯一的,并且不能为空。

C)聚集索引,表数据按照索引的顺序来存储的。对于聚集索引,叶子结点即存储了真实的数据行,不再有另外单独的数据页,每个表只能一个

D)非聚集索引,表数据存储顺序与索引顺序无关。对于非聚集索引,数据存储在一个位置,索引存储在另一个位置,索引中包含指向数据存储位置的指针。

索引优缺点

优点: 创建索引可以大大提高系统的性能。 
    1):通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。 
    2):可以大大加快数据的检索速度,这也是创建索引的最主要的原因。 
    3):可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。 
    4):在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。 
    5):通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。

缺点: 1):创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加。 
    2):索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚簇索引,那么需要的空间就会更大。 
    3):当对表中的数据进行增加、删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

在哪建索引   

索引是建立在数据库表中的某些列的上面。在创建索引的时候,应该考虑在哪些列上可以创建索引,在哪些列上不能创建索引。

  一般来说,应该在这些列上创建索引: 
  1):在经常需要搜索的列上,可以加快搜索的速度; 
  2):在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构; 
  3):在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度;在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,其指定的范围是连续的; 
  4):在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间; 
  5):在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。

哪不该建索引   

         同样,对于有些列不应该创建索引。一般来说,不应该创建索引的的这些列具有下列特点:   

       1):对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为,既然这些列很少使用到,因此有索引或者无索引,并不能提高查询速度。相反,由于增加了索引,反而降低了系统的维护速度和增大了空间需求。 

  2):对于那些只有很少数据值的列也不应该增加索引。这是因为,由于这些列的取值很少,例如人事表的性别列,在查询的结果中,结果集的数据行占了表中数据行的很大比例,

   即需要在表中搜索的数据行的比例很大。增加索引,并不能明显加快检索速度。 
  3):对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为,这些列的数据量要么相当大,要么取值很少,不利于使用索引。 
  4):当修改性能远远大于检索性能时,不应该创建索引。这是因为,修改性能和检索性能是互相矛盾的。当增加索引时,会提高检索性能,但是会降低修改性能。

    当减少索引时,会提高修改性能,降低检索性能。因此,当修改操作远远多于检索操作时,不应该创建索引。

(以上内容转载于:https://www.cnblogs.com/ToNi/p/4233116.html

执行计划

    提交SQL语句,数据库查询优化器,经过分析生成,指定多个查询方式,从中算则使用资源最少的,数控制定执行计划是按照使用资源最少,而不是时间最短。

    1、Table Scan 全表扫描性能最差 
    2、Cluster Index Scan (聚集索引的扫描) 性能最差,同上,虽然有聚集索引,其实也是全表扫描 
    3、Index Seek(NonClustered)(索引查找) 性能非常高 
    4、Index Scan 先Index 再扫描 
    5、Cluster Index Seek 性能最高

常规的SQL优化建议

  1、对列的计算,任何形式都要避免

  2、in 查询 or 查询索引会失效,可能是拆分

  3、in 换 exists,not in 不要用,完全不走索引

  4、is null 和 is not null都不走索引,索引里面不保存null的

  5、<>这种也不走索引,可以拆分成< 和 >

  6、join时,连接越少,性能越高。左连接,以左边的表结果为主;右连接,反过来;连接字段要求带索引

当数据库量达到上亿之后,索引用处就不大了,SQLserver数据库,两千万就可以了。当数据量过大,就应该从数据库设计层次找问题了。

以上内容转载于:https://www.cnblogs.com/taotaozhuanyong/p/11563906.html

 

 

 

 

 

posted @   明志德道  阅读(139)  评论(0编辑  收藏  举报
编辑推荐:
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
阅读排行:
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
· 永远不要相信用户的输入:从 SQL 注入攻防看输入验证的重要性
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
点击右上角即可分享
微信分享提示