SQL Server 索引列的顺序——真的没关系吗

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介: 原文: SQL Server 索引列的顺序——真的没关系吗 翻译自:http://www.mssqltips.com/sqlservertip/2718/sql-server-index-column-order--does-it-m...
原文: SQL Server 索引列的顺序——真的没关系吗

翻译自:http://www.mssqltips.com/sqlservertip/2718/sql-server-index-column-order--does-it-matter/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=2012619

问题:

当设置表的索引时,在性能上有一个微妙的平衡:太多的索引将影响你的INSERT/UPDATE/DELETE操作。但是索引不足又将影响你的SELECT操作。本文将着眼于索引的列顺序和如何影响查询计划及性能。

解决方案:

示例SQLServer表和数据集:

-- Tablecreation logic

CREATE TABLE[dbo].[TABLE1]

([col1][int] NOT NULL,[col2] [int]NULL,[col3] [int] NULL,[col4][varchar](50)NULL)

GO

CREATE TABLE[dbo].[TABLE2]

([col1][int] NOT NULL,[col2] [int]NULL,[col3] [int] NULL,[col4][varchar](50)NULL)

GO

ALTER TABLEdbo.TABLE1ADD CONSTRAINT PK_TABLE1 PRIMARY KEY CLUSTERED (col1)

GO

ALTER TABLEdbo.TABLE2ADD CONSTRAINT PK_TABLE2 PRIMARY KEY CLUSTERED (col1)

GO

--Populate tables

DECLARE @val INT

SELECT @val=1

WHILE @val< 1000

BEGIN 

   INSERT INTO dbo.Table1(col1,col2, col3, col4)VALUES(@val,@val,@val,'TEST')

   INSERT INTO dbo.Table2(col1,col2, col3, col4)VALUES(@val,@val,@val,'TEST')

   SELECT @val=@val+1

END

GO

--Create multi-column index on table1

CREATE NONCLUSTEREDINDEX IX_TABLE1_col2col3ONdbo.TABLE1(col2,col3)

  WITH (STATISTICS_NORECOMPUTE=OFF, IGNORE_DUP_KEY = OFF,

        ALLOW_ROW_LOCKS=ON, ALLOW_PAGE_LOCKS = ON)

  ON [PRIMARY]

GO

在运行下面的代码前请先打开执行计划(Ctrl+M)和打开统计IO的语句:SET STATISTICS IO ON

单表查询例子:

在第一个例子里面,我们将使用在where子句中的一列来查询。第一个查询中where子句的索引使用第二列(col3),第二个查询使用第一列(col2)。注意这里使用了“DBCC DROPCLEANBUFFERS”,用于确保没有缓存带来的影响,代码如下:

DBCC DROPCLEANBUFFERS

GO

SELECT * FROM dbo.TABLE1 WHEREcol3=88

GO

DBCC DROPCLEANBUFFERS

GO

SELECT * FROM dbo.TABLE1 WHEREcol2=88

GO

执行后查看执行计划如下:

可以看到,第一个查询使用第二列(col3)的索引是在表上执行索引扫描,且没有用到刚才建立的索引。第二个查询使用了表查找,使得在表里只需要使用更少的资源。第一个查询读了6次,而第二个查询只读了4次。

执行查询后,你应该大概猜到,当表越来越大的时候,性能优势就显现出来了。

 

两表关联查询例子:

在下一个例子中,查询使用同样的where子句,但增加了一个inner join 关联另外一个表。第一个查询的where子句使用col3,并使用col2来关联表。

第二个查询的where子句使用col2,并使用col3来关联表。

同样,先执行DBCC DROPCLEANBUFFERS来确保缓存已经清空。代码如下:

DBCC DROPCLEANBUFFERS
GO
SELECT * 
  FROM dbo.TABLE1 INNER JOIN 
       dbo.TABLE2 ON dbo.TABLE1.col2 = dbo.TABLE2.col1
 WHERE dbo.TABLE1.col3=255       
GO
DBCC DROPCLEANBUFFERS
GO
SELECT * 
  FROM dbo.TABLE1 INNER JOIN 
       dbo.TABLE2 ON dbo.TABLE1.col3 = dbo.TABLE2.col1
 WHERE dbo.TABLE1.col2=255       
GO

执行计划如下:

从执行计划可以看到,当用于关联表的列也在索引中,但不是第一列时,会执行索引扫描。第二个查询中索引的第一列来关列,会使用索引查找。从IO来看,同样索引查找的读次数会更小。

总结:

从这些例子中,可以看到索引列的顺序对表的查询也有影响。当创建索引时,先确认你总是对尽可能小的集合进行操作,这意味着索引能从where子句中的列开始。另外,对order by子句中的列和SELECT中的列创建覆盖索引也有助于提高查询性能。这样可以不用在查询时执行书签查找。

在前面提到的,增加太多索引将引起insert/update/delete时对这些索引列的修改。所以,找到平衡点才是最重要的。

相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
2月前
|
SQL 存储 关系型数据库
如何巧用索引优化SQL语句性能?
本文从索引角度探讨了如何优化MySQL中的SQL语句性能。首先介绍了如何通过查看执行时间和执行计划定位慢SQL,并详细解析了EXPLAIN命令的各个字段含义。接着讲解了索引优化的关键点,包括聚簇索引、索引覆盖、联合索引及最左前缀原则等。最后,通过具体示例展示了索引如何提升查询速度,并提供了三层B+树的存储容量计算方法。通过这些技巧,可以帮助开发者有效提升数据库查询效率。
172 2
|
2月前
|
SQL Oracle 关系型数据库
SQL优化-使用联合索引和函数索引
在一次例行巡检中,发现一条使用 `to_char` 函数将日期转换为字符串的 SQL 语句 CPU 利用率很高。为了优化该语句,首先分析了 where 条件中各列的选择性,并创建了不同类型的索引,包括普通索引、函数索引和虚拟列索引。通过对比不同索引的执行计划,最终确定了使用复合索引(包含函数表达式)能够显著降低查询成本,提高执行效率。
|
3月前
|
存储 SQL 关系型数据库
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
MySQL调优主要分为三个步骤:监控报警、排查慢SQL、MySQL调优。 排查慢SQL:开启慢查询日志 、找出最慢的几条SQL、分析查询计划 。 MySQL调优: 基础优化:缓存优化、硬件优化、参数优化、定期清理垃圾、使用合适的存储引擎、读写分离、分库分表; 表设计优化:数据类型优化、冷热数据分表等。 索引优化:考虑索引失效的11个场景、遵循索引设计原则、连接查询优化、排序优化、深分页查询优化、覆盖索引、索引下推、用普通索引等。 SQL优化。
597 15
【MySQL调优】如何进行MySQL调优?从参数、数据建模、索引、SQL语句等方向,三万字详细解读MySQL的性能优化方案(2024版)
|
2月前
|
SQL 关系型数据库 MySQL
如何确认SQL用了索引:详细技巧与方法
在数据库管理中,索引是提高SQL查询性能的重要手段
|
3月前
|
存储 关系型数据库 MySQL
MySQL高级篇——覆盖索引、前缀索引、索引下推、SQL优化、主键设计
覆盖索引、前缀索引、索引下推、SQL优化、EXISTS 和 IN 的区分、建议COUNT(*)或COUNT(1)、建议SELECT(字段)而不是SELECT(*)、LIMIT 1 对优化的影响、多使用COMMIT、主键设计、自增主键的缺点、淘宝订单号的主键设计、MySQL 8.0改造UUID为有序
MySQL高级篇——覆盖索引、前缀索引、索引下推、SQL优化、主键设计
|
3月前
|
SQL 存储 索引
SQL Server的Descending Indexes降序索引
【9月更文挑战第21天】在SQL Server中,降序索引允许指定列的排序顺序为降序,可显著优化涉及降序排序的查询性能,特别是在复合索引中。通过创建降序索引,可以更高效地满足特定业务需求,如按交易时间降序获取最新记录。然而,使用时需考虑查询频率、数据分布及维护成本,以确保最佳性能。
|
2月前
|
SQL 存储 关系型数据库
SQL默认索引是什么:深入解析与技巧
在SQL数据库中,索引是一种用于提高查询性能的重要数据结构
|
2月前
|
SQL 存储 关系型数据库
SQL默认索引是什么
在SQL数据库中,索引是一种用于提高查询性能的数据结构
|
2月前
|
SQL 关系型数据库 MySQL
如何确认SQL用了索引
在数据库管理和优化过程中,确认SQL查询是否使用了索引是一个至关重要的步骤
|
2月前
|
SQL 关系型数据库 MySQL
如何确认SQL查询是否使用了索引:详细步骤与技巧
在数据库管理和优化中,确认SQL查询是否有效利用了索引是提升性能的关键步骤