文盲的数据库指令优化心得:第一部分,关于索引

2024-02-25 20:48

本文主要是介绍文盲的数据库指令优化心得:第一部分,关于索引,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

先说点废话,用了10年sqlserver了,楞是不知道指令怎么优化,怎么提高效率太失败了,经过2、3个月的重新学习,才明白之前工作中没有出过问题实在太幸运了


本文中出现的大部分心得内容,在网上也能搜索到,对部分内容有误的,也欢迎大家指正


1、先说说索引,反正文盲本身是野路子出身,没系统学过,说的错了大家来指正

首先,索引是用来优化查询效率的,针对于不同的条件,所需要的索引也千奇百怪,如果索引过多,又没有能够有效的覆盖所有需要的字段,还是会降低效率,那么索引到底应该怎么建立呢?

1.1、所有必定出现在条件内的字段建立一个统一索引,然后包含所有输出的字段,这样可以保证执行计划是索引查找(index seek)而不是索引扫描(index scan),同时因为包含了条件和输出内容,所以也不会出现表扫描(table scan)

1.2、所有可能出现的条件单独建立索引并包含主键,然后单独使用该条件进行主键查询,以确保执行计划是索引查找(index seek)

1.3、索引重复字段尽量减少,因为重复字段约多,更新时越慢,更新所表情况越严重

那么有了以上几点,咱们做个实例进行测试

if object_id('test_index') is not null drop table test_index
go
create table test_index(id bigint identity primary key,n1 int,n2 int,n3 int,n4 int,n5 int,n6 int,n7 int,n8 int,n9 int,d1 date,d2 date,d3 date,d4 date,d5 datetime,d6 datetime,b1 bit,b2 bit,b3 bit,b4 bit,s1 varchar(max),s2 varchar(max),s3 nvarchar(max),s4 nvarchar(max))
go
insert into test_index(n1,n2,n3,n4,n5,n6,n7,n8,n9,d1,d2,d3,d4,d5,d6,b1,b2,b3,b4,s1,s2,s3,s4)
values(RAND()*500,RAND()*500,RAND()*500,RAND()*500,RAND()*500,RAND()*500,RAND()*500,RAND()*500,RAND()*500,dateadd(d,-RAND()*500,getdate()),dateadd(d,-RAND()*500,getdate()),dateadd(d,-RAND()*500,getdate()),dateadd(d,-RAND()*500,getdate()),dateadd(d,-RAND()*500,getdate()),dateadd(d,-RAND()*500,getdate()),(case when RAND()*2 > 1 then 1 else 0 end),(case when RAND()*2 > 1 then 1 else 0 end),(case when RAND()*2 > 1 then 1 else 0 end),(case when RAND()*2 > 1 then 1 else 0 end),'','','',''
)
go 2000000

创建一个测试表,扔200万数据进去,来测试一下索引应该怎么玩

第一个指令

select id from test_index where n1>400
来看看执行计划


我们发现,基本上所有的查询都会提示缺少索引,那么我们就来分辨一下索引的条件

假设:n1、n2、d1、b1必定出现在查询条件中,而我们只输出主键,那么我们的索引应该这么建立

CREATE NONCLUSTERED INDEX [n1_n2_d1_b1_inc_id] ON [dbo].[test_index]
([n1] ASC,[n2] ASC,[d1] ASC,[b1] ASC
)
INCLUDE ( 	[id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

建立好索引后,我们再次运行第一条指令,发现提示的还是缺少索引,那么我们不应该再去建立索引未了适应第一个指令,而是应该将指令变形,以适应我们已有的索引

那么来写第二条指令吧

select id from test_index where n1>40 and n2=n2 and b1=b1 and d1=d1

再看看执行计划如何?


很好,经过我们在指令中加了两句废话,n2=n2,b1=b1,d1=d1这样的条件后,他的执行计划改变了,索引查找(index seek),好吧,sqlserver就是这么个性

那么结论1就是,我们未必需要很多索引,因为我们可以把指令变形,让数据库使用我们所期望的索引,即便实际应用中可能有些条件不完整,我们也要通过废话告诉数据库,来使用已有的索引,也就是1.1中的观点

好了,我们再看看,可能出现的条件,先写个第三条指令

select id from test_index where n1>40 and n2=n2 and b1=b1 and d1=d1 and n5<100


数据库傻掉了,没有使用我们建立的索引,因为n5不在我们的索引范围内,那么我们应该怎么去调整呢,文盲是通过一套组合拳来玩的,首先,建立一个n5为主的,包含主键的索引

CREATE NONCLUSTERED INDEX [n5_inc_id] ON [dbo].[test_index]
([n5] ASC
)
INCLUDE ( 	[id]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

然后把第三条语句变形一下,称为第四条语句

select main.id from test_index main
inner join (select id from test_index where n5<100) app_n5 on main.id=app_n5.id
where n1>40 and n2=n2 and b1=b1 and d1=d1


呵呵,我们的索引又回来了,那么这个语句的变形依据就是1.2的论点了,这样,我们就可以对所有不确定是否参与运算的字段进行单独索引,并且连排序都可以使用这些索引了

例如

select main.id from test_index main
inner join (select id from test_index where n5<100) app_n5 on main.id=app_n5.id
where n1>40 and n2=n2 and b1=b1 and d1=d1
order by n5


select main.id from test_index main
inner join (select id,n5 from test_index where n5<100) app_n5 on main.id=app_n5.id
where n1>40 and n2=n2 and b1=b1 and d1=d1
order by app_n5.n5


对比这两个指令,唯一的区别就是把索引对应的内容单独拿出来排序,而不是在无该字段索引的查询中排序,呵呵,sqlserver还真是个性啊

那么明白了这个内容之后,我们就可以根据执行计划和现有的索引来调整执行格式,而不是无限制的去加索引了

那么,1.3的观点大部分人都已经说明过了,我就简单说一下,如果一个字段出现在多个索引中,那么在更新这个字段的时候,所有相关的索引都会同时更新,有人说过,一个表上的相关索引最好不要超过5个,那么文盲自己的理解则是,同一个字段(除主键外)相关的索引最好控制在三个以下,索引总数到是无所谓



----------------------------------------------------------------------------------------

文盲的数据库指令优化心得:第一部分,关于索引

文盲的数据库指令优化心得:第二部分,指令变形和执行计划


SQL SERVER全面优化-------Expert for SQL Server 诊断系列

l

这篇关于文盲的数据库指令优化心得:第一部分,关于索引的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/746658

相关文章

Java实现数据库图片上传功能详解

《Java实现数据库图片上传功能详解》这篇文章主要为大家详细介绍了如何使用Java实现数据库图片上传功能,包含从数据库拿图片传递前端渲染,感兴趣的小伙伴可以跟随小编一起学习一下... 目录1、前言2、数据库搭建&nbsChina编程p; 3、后端实现将图片存储进数据库4、后端实现从数据库取出图片给前端5、前端拿到

IDEA连接达梦数据库的详细配置指南

《IDEA连接达梦数据库的详细配置指南》达梦数据库(DMDatabase)作为国产关系型数据库的代表,广泛应用于企业级系统开发,本文将详细介绍如何在IntelliJIDEA中配置并连接达梦数据库,助力... 目录准备工作1. 下载达梦JDBC驱动配置步骤1. 将驱动添加到IDEA2. 创建数据库连接连接参数

Jmeter如何向数据库批量插入数据

《Jmeter如何向数据库批量插入数据》:本文主要介绍Jmeter如何向数据库批量插入数据方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录Jmeter向数据库批量插入数据Jmeter向mysql数据库中插入数据的入门操作接下来做一下各个元件的配置总结Jmete

MySql中的数据库连接池详解

《MySql中的数据库连接池详解》:本文主要介绍MySql中的数据库连接池方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录mysql数据库连接池1、概念2、为什么会出现数据库连接池3、原理4、数据库连接池的提供商5、DataSource数据源6、DBCP7、C

StarRocks数据库详解(什么是StarRocks)

《StarRocks数据库详解(什么是StarRocks)》StarRocks是一个高性能的全场景MPP数据库,支持多种数据导入导出方式,包括Spark、Flink、Hadoop等,它采用分布式架构,... 目录StarRocks介绍什么是StarRocks?StarRocks适合什么场景?StarRock

Windows环境下安装达梦数据库的完整步骤

《Windows环境下安装达梦数据库的完整步骤》达梦数据库的安装大致分为Windows和Linux版本,本文将以dm8企业版Windows_64位环境为例,为大家介绍一下达梦数据库的具体安装步骤吧... 目录环境介绍1 下载解压安装包2 根据安装手册安装2.1 选择语言 时区2.2 安装向导2.3 接受协议

Mysql中InnoDB与MyISAM索引差异详解(最新整理)

《Mysql中InnoDB与MyISAM索引差异详解(最新整理)》InnoDB和MyISAM在索引实现和特性上有差异,包括聚集索引、非聚集索引、事务支持、并发控制、覆盖索引、主键约束、外键支持和物理存... 目录1. 索引类型与数据存储方式InnoDBMyISAM2. 事务与并发控制InnoDBMyISAM

StarRocks索引详解(最新整理)

《StarRocks索引详解(最新整理)》StarRocks支持多种索引类型,包括主键索引、前缀索引、Bitmap索引和Bloomfilter索引,这些索引类型适用于不同场景,如唯一性约束、减少索引空... 目录1. 主键索引(Primary Key Index)2. 前缀索引(Prefix Index /

Java嵌套for循环优化方案分享

《Java嵌套for循环优化方案分享》介绍了Java中嵌套for循环的优化方法,包括减少循环次数、合并循环、使用更高效的数据结构、并行处理、预处理和缓存、算法优化、尽量减少对象创建以及本地变量优化,通... 目录Java 嵌套 for 循环优化方案1. 减少循环次数2. 合并循环3. 使用更高效的数据结构4

SpringBoot使用Jasypt对YML文件配置内容加密的方法(数据库密码加密)

《SpringBoot使用Jasypt对YML文件配置内容加密的方法(数据库密码加密)》本文介绍了如何在SpringBoot项目中使用Jasypt对application.yml文件中的敏感信息(如数... 目录SpringBoot使用Jasypt对YML文件配置内容进行加密(例:数据库密码加密)前言一、J