데이터 베이스 MySQL 튜토리얼 T-SQL查询进阶--理解SQL Server中索引的概念,原理以及其他

T-SQL查询进阶--理解SQL Server中索引的概念,原理以及其他

Jun 07, 2016 pm 03:13 PM
server sql 질문 이해하다 색인 고급의

T-SQL查询进阶--理解SQL Server中索引的概念,原理以及其他 简介 在SQL Server中,索引是一种增强式的存在,这意味着,即使没有索引,SQL Server仍然可以实现应有的功能。但索引可以在大多数情况下大大提升查询性能,在OLAP中尤其明显.要完全理解索引的概念

T-SQL查询进阶--理解SQL Server中索引的概念,原理以及其他

简介


     

    在SQL Server中,索引是一种增强式的存在,这意味着,即使没有索引,SQL Server仍然可以实现应有的功能。但索引可以在大多数情况下大大提升查询性能,在OLAP中尤其明显.要完全理解索引的概念,需要了解大量原理性的知识,包括B树,堆,数据库页,区,填充因子,碎片,文件组等等一系列相关知识,这些知识写一本小书也不为过。所以本文并不会深入讨论这些主题。

 

索引是什么


    索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可快速访问数据库表中的特定信息。

    精简来说,索引是一种结构.在SQL Server中,索引和表(这里指的是加了聚集索引的表)的存储结构是一样的,都是B树,B树是一种用于查找的平衡多叉树.理解B树的概念如下图:

    B-

    理解为什么使用B树作为索引和表(有聚集索引)的结构,首先需要理解SQL Server存储数据的原理.

    在SQL SERVER中,存储的单位最小是页(PAGE),页是不可再分的。就像细胞是生物学中不可再分的,或是原子是化学中不可再分的最小单位一样.这意味着,SQL SERVER对于页的读取,要么整个读取,要么完全不读取,没有折中.

    在数据库检索来说,对于磁盘IO扫描是最消耗时间的.因为磁盘扫描涉及很多物理特性,这些是相当消耗时间的。所以B树设计的初衷是为了减少对于磁盘的扫描次数。如果一个表或索引没有使用B树(对于没有聚集索引的表是使用堆heap存储),那么查找一个数据,需要在整个表包含的数据库页中全盘扫描。这无疑会大大加重IO负担.而在SQL SERVER中使用B树进行存储,则仅仅需要将B树的根节点存入内存,经过几次查找后就可以找到存放所需数据的被叶子节点包含的页!进而避免的全盘扫描从而提高了性能.

    下面,通过一个例子来证明:

     在SQL SERVER中,表上如果没有建立聚集索引,则是按照堆(HEAP)存放的,假设我有这样一张表:

     1

     现在这张表上没有任何索引,也就是以堆存放,我通过在其上加上聚集索引(以B树存放)来展现对IO的减少:

     2

 

理解聚集和聚集索引


    在SQL SERVER中,最主要的两类索引是聚集索引和非聚集索引。可以看到,这两个分类是围绕聚集这个关键字进行的.那么首先要理解什么是聚集.

    聚集在索引中的定义:

    为了提高某个属性(或属性组)的查询速度,把这个或这些属性(称为聚集码)上具有相同值的元组集中存放在连续的物理块称为聚集。

    简单来说,聚集索引就是:

    3

    在SQL SERVER中,聚集的作用就是将某一列(或是多列)的物理顺序改变为和逻辑顺序相一致,比如,我从adventureworks数据库的employee中抽取5条数据:

    4

    当我在ContactID上建立聚集索引时,再次查询:

    5

    在SQL SERVER中,聚集索引的存储是以B树存储,B树的叶子直接存储聚集索引的数据:

    grid.ai

    因为聚集索引改变的是其所在表的物理存储顺序,所以每个表只能有一个聚集索引.

 

非聚集索引

     因为每个表只能有一个聚集索引,如果我们对一个表的查询不仅仅限于在聚集索引上的字段。我们又对聚集索引列之外还有索引的要求,那么就需要非聚集索引了.

     非聚集索引,本质上来说也是聚集索引的一种.非聚集索引并不改变其所在表的物理结构,而是额外生成一个聚集索引的B树结构,但叶子节点是对于其所在表的引用,这个引用分为两种,如果其所在表上没有聚集索引,则引用行号。如果其所在表上已经有了聚集索引,则引用聚集索引的页.

     一个简单的非聚集索引概念如下:

     6

     可以看到,非聚集索引需要额外的空间进行存储,按照被索引列进行聚集索引,并在B树的叶子节点包含指向非聚集索引所在表的指针.

     MSDN中,对于非聚集索引描述图是:

     grid.ai

     可以看到,非聚集索引也是一个B树结构,与聚集索引不同的是,B树的叶子节点存的是指向堆或聚集索引的指针.

     通过非聚集索引的原理可以看出,如果其所在表的物理结构改变后,比如加上或是删除聚集索引,那么所有非聚集索引都需要被重建,这个对于性能的损耗是相当大的。所以最好要先建立聚集索引,再建立对应的非聚集索引.

 

聚集索引 VS 非聚集索引


      前面通过对于聚集索引和非聚集索引的原理解释.我们不难发现,大多数情况下,聚集索引的速度比非聚集索引要略快一些.因为聚集索引的B树叶子节点直接存储数据,而非聚集索引还需要额外通过叶子节点的指针找到数据.

      还有,对于大量连续数据查找,非聚集索引十分乏力,因为非聚集索引需要在非聚集索引的B树中找到每一行的指针,再去其所在表上找数据,性能因此会大打折扣.有时甚至不如不加非聚集索引.

      因此,大多数情况下聚集索引都要快于非聚集索引。但聚集索引只能有一个,因此选对聚集索引所施加的列对于查询性能提升至关紧要.

 

索引的使用


     索引的使用并不需要显式使用,建立索引后查询分析器会自动找出最短路径使用索引.

     但是有这种情况.当随着数据量的增长,产生了索引碎片后,很多存储的数据进行了不适当的跨页,会造成碎片(关于跨页和碎片以及填充因子的介绍,我会在后续文章中说到)我们需要重新建立索引以加快性能:

     比如前面的test_tb2上建立的一个聚集索引和非聚集索引,可以通过DMV语句查询其索引的情况:

SELECT index_type_desc,alloc_unit_type_desc,avg_fragmentation_in_percent,fragment_count,avg_fragment_size_in_pages,page_count,record_count,avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats(DB_ID('<span style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:0px; padding-right:0px; padding-bottom:0px; padding-left:0px; color:rgb(139,0,0)">AdventureWorks</span>'),OBJECT_ID('<span style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:0px; padding-right:0px; padding-bottom:0px; padding-left:0px; color:rgb(139,0,0)">test_tb2</span>'),NULL,NULL,'<span style="margin-top:0px; margin-right:0px; margin-bottom:0px; margin-left:0px; padding-top:0px; padding-right:0px; padding-bottom:0px; padding-left:0px; color:rgb(139,0,0)">Sampled</span>')
로그인 후 복사

 

     7

    我们可以通过重建索引来提高速度:

ALTER INDEX idx_text_tb2_EmployeeID ON test_tb2 REBUILD
로그인 후 복사

 

    还有一种情况是,当随着表数据量的增大,有时候需要更新表上的统计信息,让查询分析器根据这些信息选择路径,使用:

UPDATE STATISTICS 表名
로그인 후 복사

   那么什么时候知道需要更新这些统计信息呢,就是当执行计划中估计行数和实际表的行数有出入时:

   8

  

使用索引的代价


    我最喜欢的一句话是”everything has price”。我们通过索引获得的任何性能提升并不是不需要付出代价。这个代价来自几方面.

    1.通过聚集索引的原理我们知道,当表建立索引后,就以B树来存储数据.所以当对其进行更新插入删除时,就需要页在物理上的移动以调整B树.因此当更新插入删除数据时,会带来性能的下降。而对于聚集索引,当更新表后,非聚集索引也需要进行更新,相当于多更新了N(N=非聚集索引数量)个表。因此也下降了性能.

    2.通过上面对非聚集索引原理的介绍,可以看到,非聚集索引需要额外的磁盘空间。

    3.前文提过,不恰当的非聚集索引反而会降低性能.

    所以使用索引需要根据实际情况进行权衡.通常我都会将非聚集索引全部放到另外一个独立硬盘上,这样可以分散IO,从而使查询并行.

总结


     本文从索引的原理和概念对SQL SERVER中索引进行介绍,索引是一个很强大的工具,也是一把双刃剑.对于恰当使用索引需要对索引的原理以及数据库存储的相关原理进行系统的学习.

본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.

핫 AI 도구

Undresser.AI Undress

Undresser.AI Undress

사실적인 누드 사진을 만들기 위한 AI 기반 앱

AI Clothes Remover

AI Clothes Remover

사진에서 옷을 제거하는 온라인 AI 도구입니다.

Undress AI Tool

Undress AI Tool

무료로 이미지를 벗다

Clothoff.io

Clothoff.io

AI 옷 제거제

AI Hentai Generator

AI Hentai Generator

AI Hentai를 무료로 생성하십시오.

뜨거운 도구

메모장++7.3.1

메모장++7.3.1

사용하기 쉬운 무료 코드 편집기

SublimeText3 중국어 버전

SublimeText3 중국어 버전

중국어 버전, 사용하기 매우 쉽습니다.

스튜디오 13.0.1 보내기

스튜디오 13.0.1 보내기

강력한 PHP 통합 개발 환경

드림위버 CS6

드림위버 CS6

시각적 웹 개발 도구

SublimeText3 Mac 버전

SublimeText3 Mac 버전

신 수준의 코드 편집 소프트웨어(SublimeText3)

Hibernate 프레임워크에서 HQL과 SQL의 차이점은 무엇입니까? Hibernate 프레임워크에서 HQL과 SQL의 차이점은 무엇입니까? Apr 17, 2024 pm 02:57 PM

HQL과 SQL은 Hibernate 프레임워크에서 비교됩니다. HQL(1. 객체 지향 구문, 2. 데이터베이스 독립적 쿼리, 3. 유형 안전성), SQL은 데이터베이스를 직접 운영합니다(1. 데이터베이스 독립적 표준, 2. 복잡한 실행 파일) 쿼리 및 데이터 조작).

Oracle SQL의 나누기 연산 사용법 Oracle SQL의 나누기 연산 사용법 Mar 10, 2024 pm 03:06 PM

"OracleSQL의 나눗셈 연산 사용법" OracleSQL에서 나눗셈 연산은 일반적인 수학 연산 중 하나입니다. 데이터 쿼리 및 처리 중에 나누기 작업은 필드 간의 비율을 계산하거나 특정 값 간의 논리적 관계를 도출하는 데 도움이 될 수 있습니다. 이 문서에서는 OracleSQL의 나누기 작업 사용법을 소개하고 구체적인 코드 예제를 제공합니다. 1. OracleSQL의 두 가지 분할 연산 방식 OracleSQL에서는 두 가지 방식으로 분할 연산을 수행할 수 있습니다.

Oracle과 DB2의 SQL 구문 비교 및 ​​차이점 Oracle과 DB2의 SQL 구문 비교 및 ​​차이점 Mar 11, 2024 pm 12:09 PM

Oracle과 DB2는 일반적으로 사용되는 관계형 데이터베이스 관리 시스템으로, 각각 고유한 SQL 구문과 특성을 가지고 있습니다. 이 기사에서는 Oracle과 DB2의 SQL 구문을 비교 및 ​​차이점을 설명하고 구체적인 코드 예제를 제공합니다. 데이터베이스 연결 Oracle에서는 다음 문을 사용하여 데이터베이스에 연결합니다. CONNECTusername/password@database DB2에서 데이터베이스에 연결하는 문은 다음과 같습니다. CONNECTTOdataba

Xuexin.com에서 학업 자격을 확인하는 방법 Xuexin.com에서 학업 자격을 확인하는 방법 Mar 28, 2024 pm 04:31 PM

Xuexin.com에서 내 학업 자격을 어떻게 확인하나요? Xuexin.com에서 학업 자격을 확인할 수 있습니다. 많은 사용자가 Xuexin.com에서 학업 자격을 확인하는 방법을 모릅니다. 다음으로 편집자는 Xuexin.com에서 학업 자격을 확인하는 방법에 대한 그래픽 튜토리얼을 제공합니다. 유저들이 와서 구경해 보세요! Xuexin.com 사용 튜토리얼: Xuexin.com에서 학업 자격을 확인하는 방법 1. Xuexin.com 입구: https://www.chsi.com.cn/ 2. 웹사이트 쿼리: 1단계: Xuexin.com 주소를 클릭합니다. 위의 홈페이지에 들어가려면 [교육 쿼리]를 클릭합니다. 2단계: 최신 웹페이지에서 아래 그림의 화살표와 같이 [쿼리]를 클릭합니다. 3단계: 새 페이지에서 [학점 파일에 로그인]을 클릭합니다. 4단계: 로그인 페이지에서 정보를 입력하고 [로그인]을 클릭합니다.

12306 항공권 구매 내역 확인 방법 항공권 구매 내역 확인 방법 12306 항공권 구매 내역 확인 방법 항공권 구매 내역 확인 방법 Mar 28, 2024 pm 03:11 PM

12306 티켓 예매 앱의 최신 버전을 다운로드하세요. 모두가 매우 만족하는 여행 티켓 구매 소프트웨어입니다. 소프트웨어에서 제공되는 다양한 티켓 소스가 있어 매우 편리합니다. - 실명인증으로 온라인 구매가 가능합니다. 모든 사용자가 쉽게 여행티켓과 항공권을 구매하고 다양한 할인 혜택을 누릴 수 있습니다. 또한 사전에 예약하고 티켓을 얻을 수도 있습니다. 호텔을 예약하거나 차량으로 픽업 및 하차할 수도 있습니다. 한 번의 클릭으로 원하는 곳으로 이동하고 티켓을 구매할 수 있어 여행이 더욱 간편해지고 편리해집니다. 모든 사람의 여행 경험이 더욱 편안해졌습니다. 이제 편집자가 온라인으로 자세히 설명합니다. 12306명의 사용자에게 과거 티켓 구매 기록을 볼 수 있는 방법을 제공합니다. 1. 철도 12306을 열고 오른쪽 하단의 My를 클릭한 후 My Order를 클릭합니다. 2. 주문 페이지에서 Paid를 클릭합니다. 3. 유료페이지에서

데이터베이스 기술 경쟁: Oracle과 SQL의 차이점은 무엇입니까? 데이터베이스 기술 경쟁: Oracle과 SQL의 차이점은 무엇입니까? Mar 09, 2024 am 08:30 AM

데이터베이스 기술 경쟁: Oracle과 SQL의 차이점은 무엇입니까? 데이터베이스 분야에서 Oracle과 SQL Server는 매우 존경받는 관계형 데이터베이스 관리 시스템입니다. 둘 다 관계형 데이터베이스 범주에 속하지만 둘 사이에는 많은 차이점이 있습니다. 이 기사에서는 Oracle과 SQL Server의 차이점과 실제 애플리케이션에서의 기능 및 장점을 자세히 살펴보겠습니다. 우선, Oracle과 SQL Server 사이에는 구문에 차이가 있습니다.

Apple 휴대폰의 활성화 날짜를 확인하는 방법 Apple 휴대폰의 활성화 날짜를 확인하는 방법 Mar 08, 2024 pm 04:07 PM

애플 휴대폰을 이용하여 개통일을 확인하고 싶다면 휴대폰에 있는 일련번호를 통해 확인하는 것이 가장 좋은 방법이며, 애플 공식 홈페이지를 방문하여 컴퓨터에 연결한 후 세 번째 다운로드를 통해 확인할 수도 있습니다. - 그것을 확인하는 파티 소프트웨어. Apple 휴대폰의 활성화 날짜를 확인하는 방법은 무엇입니까? 답변: 일련번호 쿼리, Apple 공식 웹사이트 쿼리, 컴퓨터 쿼리, 타사 소프트웨어 쿼리 1. 사용자가 휴대폰의 일련번호를 아는 것이 가장 좋습니다. 설정, 일반, 이 기기 정보를 열어 일련번호를 확인할 수 있습니다. 2. 일련번호를 이용하면 휴대폰 개통일뿐만 아니라 휴대폰 버전, 휴대폰 원산지, 휴대폰 공장일 등을 확인할 수 있습니다. 3. 사용자는 Apple의 공식 웹 사이트를 방문하여 기술 지원을 찾고, 페이지 하단의 서비스 및 수리 열을 찾아 거기에서 iPhone 활성화 정보를 확인합니다. 4. 사용자

Oracle과 SQL의 차이점과 애플리케이션 시나리오 분석 Oracle과 SQL의 차이점과 애플리케이션 시나리오 분석 Mar 08, 2024 pm 09:39 PM

Oracle과 SQL의 차이점과 응용 시나리오 분석 데이터베이스 분야에서 Oracle과 SQL은 자주 언급되는 두 가지 용어입니다. Oracle은 관계형 데이터베이스 관리 시스템(RDBMS)이고, SQL(StructuredQueryLanguage)은 관계형 데이터베이스를 관리하기 위한 표준화된 언어입니다. 어느 정도 관련이 있지만 몇 가지 중요한 차이점도 있습니다. 우선, 정의에 따르면 Oracle은 다음으로 구성된 특정 데이터베이스 관리 시스템입니다.

See all articles