01 diseño de indices

Diseo de ndices

Adiel Omar FLORES RAMOS

Msc. Adiel Omar FLORES RAMOS

Introduccin

Introduccin a los ndices

Arquitectura de los ndices

Cmo SQL Server recupera los datos almacenados

Cmo SQL Server mantiene las estructuras de losndices y los montones

Decisin de las columnas que se van a indizar


Introduccin a los ndices

Cmo SQL Server almacena y tiene acceso a los datos

Ventajas e inconvenientes de crear ndices


Cmo SQL Server almacena y tiene acceso a

los datos

Cmo se almacenan los datos Las filas se almacenan en pginas de datos Los montones son una coleccin de pginas de datos para

una tabla Acceso a los datos Recorre todas las pginas de datos en una tabla Mediante un ndice que apunte a los datos de una pgina

Pginas de datos

Pgina 7 Pgina 8 Pgina 9Pgina 4 Pgina 5 Pgina 6

...

...

...

...

...

Con

Funk

White

...

...

Rudd

White

Barr

...

...

Smith

Ota

Jones

...

...

Martin

Phua

Jones

Smith

...

Ganio

Jones

Hall

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

...

Akhtar

Funk

Smith

Martin

...

...

...

...

...

...


Ventajas e inconvenientes de crear ndices

Razones para crear un ndice

Acelerar el acceso a datos

Fuerzan la unicidad de las filas

Razones para no crear un ndice

Consumen espacio en disco

Generan costos de procesamiento


Arquitectura de los ndices

Arquitectura de ndices de SQL Server

Uso de montones

Uso de los ndices agrupados

Uso de los ndices no agrupados


Uso de montones

SQL Server:

Utiliza las pginas de Mapa de asignacin de ndicesque: Contienen informacin acerca del lugar donde estn

almacenadas las extensiones de un montn

Se utilizan para recorrer el montn y encontrar espaciodisponible para insertar nuevas filas

Conectan pginas de datos

Recupera espacio para las nuevas filas del montncuando se elimina una fila


Uso de los ndices agrupados

Cada tabla slo puede tener un ndice agrupado

El orden fsico de las filas de la tabla y el orden de lasfilas en el ndice son el mismo

La unicidad de los valores de clave se mantieneexplcitamente o implcitamente


Uso de los ndices no agrupados

Los ndices no agrupados son los predeterminados deSQL Server

Los ndices no agrupados existentes se vuelven agenerar automticamente

Se quita un ndice agrupado existente

Se crea un ndice agrupado

Se utiliza la opcin DROP_EXISTING para cambiar lascolumnas que definen el ndice agrupado


Cmo SQL Server recupera los datosalmacenados

Cmo SQL Server utiliza la tabla sysindexes

Bsqueda de filas sin ndices

Bsqueda de filas en un montn con un ndice noagrupado

Bsqueda de filas en un ndice agrupado

Bsqueda de filas en un ndice agrupado con un ndiceno agrupado


Cmo SQL Server utiliza la tabla sysindexes

Describe los ndices

Ubicacin de IAM, primero y raz de ndices

Nmero de pginas y filas

Distribucin de datos

Id. de ndice Tipo de objeto

0 Montn

1 ndice agrupado

2 a 250 ndice no agrupado

255 text, ntext o image


Bsqueda de filas sin ndices

MontnExtensin 127

id indid = 0 Primera IAM

sysindexes

IAM

Extensin 128 Extensin 129

010203

SmithOtaJones...

......

01020304

AkhtarFunkSmithMartin...

......

010203

RuddWhiteBarr......

......

010203

ConFunkWhite......

......

010203

SmithOtaJones...

......

01020304


......

010203

RuddWhiteBarr......

......

0102030405

ConFunkWhiteDurkinLang

......

010203

SmithOtaJones...

......

01020304


......

010203

RuddWhiteBarr......

......

010203

ConFunkWhite......

......

010203

SmithOtaJones...

......

01020304


......

010203

RuddWhiteBarr......

......

0102030405

DunnRandallOtaSlichterLaBrie

......

010203

SmithOtaJones...

......

01020304


......

010203

RuddWhiteBarr......

......

010203

ConFunkWhite......

......

010203

SmithOtaJones...

......

01020304


......

010203

RuddWhiteBarr......

......

010203

SmithOtaJones...

......

01020304


......

010203

RuddWhiteBarr......

......

010203

ConFunkWhite......

......

010203

SmithOtaJones...

......

01020304


......

010203

RuddWhiteBarr......

......

010203

GraffBaconKoch......

......

Extensin 130

01020304

SeattleParisTokyoAtlanta...

......

127 1128 1129 0130 1

Extensin Mapa de bits


No situadas

en el nivel

de hojaPg. 12 - Raz

Pg. 37 Pg. 28

Nivel de hoja(Valor de

clave)

Pg. 41 Pg. 51 Pg. 61 Pg. 71

Akhtar...Martin

AkhtarBarrConFunkFunk

4:706:014:705:034:704:014:706:024:704:02

MartinSmith...

SmithSmithSmithWhiteWhite

4:706:034:708:044:707:014:704:034:705:02

AkhtarGanio...

GanioHall

JonesJonesJones

4:709:014:709:044:709:024:708:034:707:03

MontnPg. 707 Pg. 808 Pg. 709

01

02

03

04

...

...

...

...

...

...

Akhtar

Funk

Smith

Matey

...

Pg. 704 Pg. 705 Pg. 706

01

02

03

...

...

...

...

...

...

...

Conn

Funk

White

...

...

01

02

03

...

...

...

...

...

...

...

Rudd

White

Barr

...

...

01

02

03

...

...

...

...

...

...

...

Smith

Ota

Jones

...

...

01

02

03

04

...

...

...

...

...

...

Martin

Phua

Jones

Smith

...

01

02

03

...

...

...

...

...

...

...

Ganio

Jones

Hall

...

...

MartinMateyOta

PhuaRudd

4:708:014:706:044:707:024:708:024:705:01

ndice no

agrupado

File ID #4

SELECT lastname, firstnameFROM memberWHERE lastnameBETWEEN 'Masters' AND 'Rudd'

No situadas

en el nivel

de hojaPg. 12 - Raz

Pg. 37 Pg. 28


clave)

Pg. 41 Pg. 51 Pg. 61 Pg. 71

Akhtar...Martin


4:706:014:705:034:704:014:706:024:704:02

MartinSmith...


4:706:034:708:044:707:014:704:034:705:02

AkhtarGanio...

GanioHall

JonesJonesJones

4:709:014:709:044:709:024:708:034:707:03

MontnPg. 707 Pg. 808 P 709

01

02

03

04

...

...

...

...

...

...

Akhtar

Funk

Smith

Matey

...

Pg. 704 Pg. 705 Pg. 706

01

02

03

...

...

...

...

...

...

...

Conn

Funk

White

...

...

01

02

03

...

...

...

...

...

...

...

Rudd

White

Barr

...

...

01

02

03

...

...

...

...

...

...

...

Smith

Ota

Jones

...

...

01

02

03

04

...

...

...

...

...

...

Martin

Phua

Jones

Smith

...

01

02

03

...

...

...

...

...

...

...

Ganio

Jones

Hall

...

...

MartinMateyOta

PhuaRudd

4:708:014:706:044:707:024:708:024:705:01

ndice

no

agrupado

File ID #4

Bsqueda de filas en un montn con un ndice no

agrupadoid indid = 2 razsysindexes

Martin

Martin

04 ... Matey

Matey 4:706:04

02 ... Phua

Phua 4:708:02

01 ... Rudd

Rudd 4:705:01

02 ... Ota

Ota 4:707:02


ndice agrupado

Pg. 140 - Raz

Pg. 100 Pg. 120 Pg. 130

Pg. 141 Pg. 145

Akhtar

Barr

Con

Funk

Funk

...

2334

5678

2534

1334

1534

...

...

...

...

...

...

...

Martin

Martin

Ota

Phua

Rudd

...

1234

7778

5878

7878

6078

...

...

...

...

...

...

...

Smith

Smith

Smith

White

White

...

1434

5778

7978

2234

1634

...

...

...

...

...

...

...

Akhtar

Ganio

Akhtar

Martin

Martin

Smith

Pg. 110

Ganio

Hall

Jones

Jones

Jones

...

7678

8078

2434

5978

2634

...

...

...

...

...

...

...

SELECT lastname, firstnameFROM memberWHERE lastname = 'Ota'

ndice agrupado

Pg. 140 - Raz

Pg. 100 Pg. 120 Pg. 130

Pg. 141 Pg. 145

Akhtar

Barr

Con

Funk

Funk

...

2334

5678

2534

1334

1534

...

...

...

...

...

...

...

Martin

Martin

Ota

Phua

Rudd

...

1234

7778

5878

7878

6078

...

...

...

...

...

...

...

Smith

Smith

Smith

White

White

...

1434

5778

7978

2234

1634

...

...

...

...

...

...

...

Akhtar

Ganio

Akhtar

Martin

Martin

Smith

Pg. 110

Ganio

Hall

Jones

Jones

Jones

...

7678

8078

2434

5978

2634

...

...

...

...

...

...

...

Bsqueda de filas en un ndice agrupado

Martin

Ota 5878 ...

Martin

id indid = 1 razsysindexes


ndice agrupado

para Last Name

ndice no

agrupado

para

First Name

No situadas

en el nivel

de hoja

Nivel

de hoja(Valor de clave

agrupado)

Aaron

Deanna

Aaron

...

Jose

Jose

Nina

DeannaDonDoug

DaumHallHampton

AaronAdamAmie

ConBarrBaldwin

JoseJudyMike

LugoKaethlerNash

Barr AdamCoxDaum

ArletteDeanna

KimKobaraLaBrie

ShaneLindaRyan

NagataNashNixon

SusanneMikeToby

BarrKimNagataOMelia

SELECT lastname, firstname, phoneFROM memberWHERE firstname = 'Mike'

ndice agrupado

para Last Name

ndice no

agrupado

para

First Name

No situadas

en el nivel

de hoja

Nivel

de hoja(Valor de clave

agrupado)

Aaron

Deanna

Aaron

...

Jose

Jose

Nina

DeannaDonDoug

DaumHallHampton

AaronAdamAmie

ConBarrBaldwin

JoseJudyMike

LugoKaethlerNash

Barr AdamCoxDaum

ArletteDeanna

KimKobaraLaBrie

ShaneLindaRyan

NagataNashNixon

SusanneMikeToby

BarrKimNagataOMelia

Bsqueda de filas en un ndice agrupado con un

ndice no agrupadoid indid = 2 razsysindexes

Mike Nash

Nagata

Nash Mike


Cmo SQL Server mantiene las estructuras de los ndices y los montones

Divisiones de pginas en un ndice

Puntero de reenvo en un montn

Cmo SQL Server actualiza filas

Cmo SQL Server elimina filas


Divisiones de pginas en un ndice

Pginas de ndice

No situadas

en el nivel

de hoja

INSERT member (last name)VALUES lastname = Jackson'

Lang

Smith

Akhtar

Ganio

Akhtar

Martin

Jackson

Nivel de hoja(Valor de clave)

Akhtar

Barr

Barr

Borm

Buhl

Lang

Martin

Martin

Martin

Moris

Smith

Smith

Smith

Smith

Smith

Ganio

Hall

Hart

Jones

Jones


Akhtar

Barr

Barr

Borm

Buhl

Jackson

Jones

Jones

Lang

Martin

Martin

Martin

Moris

Smith

Smith

Smith

Smith

Smith

Ganio

Hall

Hart

Jackson


Puntero de reenvo en un montn

No situadas

en el nivel

de hojaPg. 12 - Raz

Pg. 37 Pg. 28


Pg. 41 Pg. 51 Pg. 61 Pg. 71

Akhtar...Martin


4:706:014:705:034:704:014:706:024:704:02

MartinSmith...


4:706:034:708:044:707:014:704:034:705:02

AkhtarGanio...

GanioHall

JonesJonesJones

4:709:014:709:044:709:024:708:034:707:03

MontnPg. 707 Pg. 808 Pg. 709

01

02

03

04

...

...

...

...

...

...

Akhtar

Funk

Smith

Martin

...

Pg. 704 Pg. 705 Pg. 706

01

02

03

...

...

...

...

...

...

...

Conn

Funk

White

...

...

01

02

03

...

...

...

...

...

...

...

Rudd

White

Barr

...

...

01

02

03

04

05

...

...

...

...

...

Smith

Ota

Jones

Corets

Nash

01

02

03

04

...

...

...

...

...

...

Martin

Phua

Jones

Smith

...

01

02

03

...

...

...

...

...

...

...

Ganio

Jones

Hall

...

...

MartinMartin

OtaPhuaRudd

4:708:014:706:044:707:024:708:024:705:01

ndice no

agrupado

File ID #4

id indid = 2 razsysindexes

UPDATE memberSET Address = WHERE lastname = 'Ota'

No situadas

en el nivel

de hojaPg. 12 - Raz

Pg. 37 Pg. 28


clave)

Pg. 41 Pg. 51 Pg. 61 Pg. 71

Akhtar...Martin


4:706:014:705:034:704:014:706:024:704:02

MartinSmith...


4:706:034:708:044:707:014:704:034:705:02

AkhtarGanio...

GanioHall

JonesJonesJones

4:709:014:709:044:709:024:708:034:707:03

MontnPg. 707 Pg. 808 Pg. 709

01

02

03

04

...

...

...

...

...

...

Akhtar

Funk

Smith

Martin

...

Pg. 704 Pg. 705 Pg. 706

01

02

03

...

...

...

...

...

...

...

Conn

Funk

White

...

...

01

02

03

...

...

...

...

...

...

...

Rudd

White

Barr

...

...

01

02

03

04

05

...

...

...

...

...

Smith

Ota

Jones

Corets

Nash

01

02

03

04

...

...

...

...

...

...

Martin

Phua

Jones

Smith

...

01

02

03

...

...

...

...

...

...

...

Ganio

Jones

Hall

...

...

MartinMartin

OtaPhuaRudd

4:708:014:706:044:707:024:708:024:705:01

ndice no

agrupado

File ID #4

02 ... Ota02 ... Ota

Martin

Martin

Ota 4:707:02

02 Ota

04 ... Ota


Cmo SQL Server actualiza filas

Una actualizacin no suele hacer que una fila se mueva

Una actualizacin puede ser una eliminacin seguida deuna insercin

Las actualizaciones por lotes tocan cada ndice una solavez


Cmo SQL Server elimina filas

Los registros del montn se desplazan de forma individual

Las pginas de ndice agrupadas se desplazan como una unidad

Cmo las eliminaciones producen registros fantasma

Cmo SQL Server reclama espacio

Cmo se pueden reducir los archivos


Decisin de las columnas que se van a indizar

Comprensin de los datos

Directrices de indizacin

Eleccin del ndice agrupado adecuado

Creacin de ndices que admiten consultas

Determinacin de la selectividad

Determinacin de la densidad

Determinacin de la distribucin de datos


Comprensin de los datos

El diseo lgico y fsico

Las caractersticas de los datos

Cmo se utilizan los datos

Los tipos de consultas realizadas

La frecuencia de las consultas ms tpicas


Directrices de indizacin

Columnas adecuadas para indizar Claves principal y externa En las que se buscan frecuentemente intervalos A las que se tiene acceso de forma ordenada Agrupadas juntas durante la agregacin

Columnas no adecuadas para indizar Se incluyen con poca frecuencia en consultas Contienen pocos valores nicos Se definen con los tipos de datos text, ntext o image


Eleccin del ndice agrupado adecuado

Tablas continuamente actualizadas Un ndice agrupado con una columna de identidad

mantiene las pginas actualizadas en memoria

Ordenacin Un ndice agrupado mantiene los datos preordenados

Longitud de columna y tipo de datos Limita el nmero de columnas Reduce el nmero de caracteres Utiliza los tipos de datos ms pequeos posibles


Creacin de ndices que admiten consultas

Uso de argumentos de bsqueda

Escritura de buenos argumentos de bsqueda

Especificar una clusula WHERE en la consulta

Comprobar que la clusula WHERE limita el nmerode filas

Comprobar que existe una expresin para cada tabla a la que se hace referencia en la consulta

Evitar el uso de caracteres comodines iniciales


Determinacin de la selectividad

Alta selectividadmember_no

1

2

.

.

last_name first_name

Randall

Flood

Joshua

Kathie

.

10000 Anderson Bill

SELECT *FROM memberWHERE member_no > 8999

1000

10000 = 10%Nmero de filas que cumplen el criterio

Nmero total de filas en la tabla =

Baja selectividadmember_no

1

2

.

.


Randall

Flood

Joshua

Kathie

.

10000 Anderson Bill

SELECT *FROM memberWHERE member_no < 9001

9000

10000 = 90%Nmero de filas que cumplen el criterio

Nmero total de filas en la tabla =


Determinacin de la densidad


Randall

.

.

.

Joshua

Randall Cynthia

Randall Tristan

.

.

.

Ota Lani

.

.

.

SELECT *FROM memberWHERE last_name = Ota

Baja densidad

SELECT *FROM memberWHERE last_name = Randall

Alta densidad


Determinacin de la distribucin de datos

Distribucin uniforme de valores

Last Name

Distribucin estndar de valores

F - JA - E K - O P - U V - Z

Last Name

Nmero

de

Last Names

Nmero

de

Last Names

C - FA - B G - K L - N O - Z

01 diseño de indices

Documents