当前位置:网站首页>SQL Server查询优化 (转载非原创)
SQL Server查询优化 (转载非原创)
2022-08-09 12:21:00 【wxilejun】
从上至下优化
看过一篇文章,印象深刻,里面将数据库查询优化分为四个大的方向
- 使用钞能力——给DB服务器加物理配置,内存啊,CPU啊,硬盘啊,全上顶配
- 替换存储系统——根据实际的业务情况选择不同的存储数据库,比如用ES做全文检索
- 优化存储结构——比如采用分库分表,CQRS(命令查询职责分离),分布式缓存,历史数据归档,数据序列化等
- 查询语句的优化——增加数据库索引命中率,定期清理数据库索引碎片等 从上到下成本依次递减,性价比依次升高,今天咱们聊聊Sql Server中基于索引的“查询语句的优化”
索引数据结构
谈到索引,咱们避免不了会想到索引的存储数据结构,目前大多数RDBS(关系型数据库系统)采用B+树来存储索引数据,如果还不是特别清楚啥是B+树的话,这里有传送门点击这里。 这里简单概括一下B+树的几个特点:
- 每个节点可以存储多个元素
- 所有的非叶子节点只存储关键字信息
- 所有具体数据都存在叶子结点中
- 所有的叶子结点中包含了全部元素的信息
- 所有叶子节点之间都有一个链指针
索引分类
聚集索引
- 聚集索引根据数据行的键值在表或视图中排序和存储这些数据行。 索引定义中包含聚集索引列。 每个表只能有一个聚集索引,因为数据行本身只能按一个顺序存储。
- 只有当表包含聚集索引时,表中的数据行才按排序顺序存储。 如果表具有聚集索引,则该表称为聚集表。 如果表没有聚集索引,则其数据行存储在一个称为堆的无序结构中。
可以简单理解为数据表中的数据按照既定的顺序进行存储,而这个用来排序的字段就是聚集索引。也可以理解为一个个由Key-Value组成的元素分布在一棵B+树上,Key对应的就是索引,Value对应的就是具体的数据行。
非聚集索引
- 非聚集索引具有独立于数据行的结构。 非聚集索引包含非聚集索引键值,并且每个键值项都有指向包含该键值的数据行的指针
- 从非聚集索引中的索引行指向数据行的指针称为行定位器。 行定位器的结构取决于数据页是存储在堆中还是聚集表中。 对于堆,行定位器是指向行的指针。 对于聚集表,行定位器是聚集索引键。
大白话就是非聚集索引中存储的Key-Value,其中Key跟聚集索引一样是索引列,Value根据表是否存在聚集索引来进行区分,如果存在则Value为指向聚集索引键(也就是聚集索引的Key)的指针,不存在,则Value为指向表中数据行的指针。
查询优化
索引命中规则之最左匹配原则
众所周知,我们通常会在高频的where条件所用的字段上建立相关索引,那么我们建立索引以后我们的where查询条件是否命中索引呢?
CREATE NONCLUSTERED INDEX IDEMO ON DEMOTABLE (A ASC,B ASC,C ASC,D ASC);
如上,在表DEMOTABLE中用A,B,C,D四个字段创建了非聚集索引,首先列A必须出现在查询条件中即(A组合),剩下的依次可以为,A,B组合,A,B,C组合,A,B,C,D组合,类似下面这样:
SELECT E,F,G FROM DEMOTABLE WHERE A=1
SELECT E,F,G FROM DEMOTABLE WHERE A=1 AND B=2
SELECT E,F,G FROM DEMOTABLE WHERE A=1 AND B=2 AND C=3
SELECT E,F,G FROM DEMOTABLE WHERE A=1 AND B=2 AND C=3 AND D=4
//不会命中索引
SELECT E,F,G FROM DEMOTABLE WHERE B=2 AND C=3 AND D=4
//部分命中索引,只有条件A=1会命中
SELECT E,F,G FROM DEMOTABLE WHERE A=1 AND C=3 AND D=4
索引之覆盖索引
何为覆盖索引?
CREATE NONCLUSTERED INDEX IDEMO ON DEMOTABLE (A ASC,B ASC,C ASC,D ASC) INCLUDE(E,F,G);
上面所建的非聚集索引以上一个创建语句后面多了一个INCLUDE语句,这样做可以减少索引命中以后查询相关列时的回表操作,何谓回表?之前我们讲过在非聚集索引的叶子节点上存放了对应聚集索引的指针,查询在命中非聚集索引的以后要查询非索引列时会根据这个指针去聚集索引上查找相关列,这个动作就是回表;如果我们的非聚集索引上INCLUDE了要查询的列,就可以减少相关查询的回表操作,从而提高查询性能。像下面这条语句就可以完美的规避回表查询。
SELECT E,F,G FROM DEMOTABLE WHERE A=1 AND B=2 AND C=3 AND D=4
索引碎片
索引在建立过程中随着数据量的增加,索引碎片也会越来越多,从而导致即使在索引命中的情况下查询性能可能也不是特别理想,那这些碎片是怎么产生的呢?
- 外部碎片
新的索引在插入的时候与旧的索引在物理存储位置上不连续,这就产生了外部碎片。
- 内部碎片
新的索引在插入的时候导致因为索引所占空间大小的变化导致同一页上本可以存储3个索引,现在只能存下2个索引,存储2个索引以后剩下的空间就是内部碎片。
如何处理索引碎片呢?
- 索引碎片已经很多的情况下 这种情况我们可以采用索引重新生成或索引重新组织,当然一般来说线上环境都有专门的DBA负责这些事宜,我们只需要知道有这些处理方式就好。
- 在创建索引的时候 创建索引时我们可以根据实际的业务场景和索引字段所存信息的大小来适当的添加填充因子(0-100),也可以一定程度上减少索引碎片的产生。如果你还不清楚填充因子的话,可以看看这个。
转载来源: https://www.cnblogs.com/clue-yang/p/16526295.html
边栏推荐
- 鹅厂机器狗花式穿越10m梅花桩:前空翻、单桩跳、起身作揖...全程不打一个趔趄...
- ansible-cmdb friendly display ansible collects host information
- MongoDB-查询中$all的用法介绍
- 十分钟教会你如何使用VitePress搭建及部署个人博客站点
- 关于Retrofit网络请求URL中含有可变参数的处理
- 放下手机吧:实验表明花20分钟思考和上网冲浪同样快乐
- FFmpeg在win10上编译安装(配置libx264)
- AI basketball referee, walking is special, ask harden care don't care
- Flutter入门进阶之旅(五)Image Widget
- Use RecyclerView to implement three-level collapsed list
猜你喜欢
Scala Advanced (7): Collection Content Summary (Part 1)
Flutter入门进阶之旅(八)Button Widget
生成上传密钥和密钥库
Win10 compiles the x264 library (there are also generated lib files)
腾讯欲成育碧最大股东/ 米哈游招NLP内容生成研究员/ AI发现四千余物种濒临灭绝...今日更多新鲜事在此...
张朝阳对话俞敏洪:一边是手推物理公式,一边是古诗信手拈来
西湖大学教授怎么看AI制药革命?|量子位智库圆桌实录
数据挖掘-05
The new features of ABP 6.0.0 - rc. 1
脱光衣服待着就能减肥,当真有这好事?
随机推荐
Flutter入门进阶之旅(四)文本输入Widget TextField
JD.com architects tidy up: what are the core technical knowledge points of jvm and performance tuning
基于CAP组件实现补偿事务与幂等性保障
Flutter Getting Started and Advanced Tour (1) - Getting to Know Flutter
Nature:猪死亡1小时后,器官再次运转
脱光衣服待着就能减肥,当真有这好事?
工作任务统计
你没见过的《老友记》镜头,AI给补出来了|ECCV 2022
苹果Meta都在冲的Pancake技术,中国VR团队YVR竟抢先交出产品答卷
鹅厂机器狗花式穿越10m梅花桩:前空翻、单桩跳、起身作揖...全程不打一个趔趄...
go基础之web获取参数
FFmpeg compiles and installs on win10 (configure libx264)
Flutter入门进阶之旅(二)Hello Flutter
h264协议
h264 protocol
箭头函数和普通函数的常见区别
【HCIP持续更新】IS-IS协议原理与配置
用场景定义硬件,英码科技破解“边缘计算”密码
LeetCode #101. Symmetric Binary Tree
注:检测到当前使用的ADB不是HBuilder内置或自定义ADB:PID为:9544进程名称为:adb.exe 路径为:c:\users\administrator\appdata\local\and