Diseño de índices

1. Diseño de índices

2. Introducción • Introducción a los índices • Arquitectura de los índices • Cómo SQL Server recupera los datos almacenados • Cómo SQL Server mantiene las estructuras de los índices y los montones • Decisión de las columnas que se van a indizar

3. Introducción a los índices • Cómo SQL Server almacena y tiene acceso a los datos • Ventajas e inconvenientes de crear índices

4. Akhtar ... Funk ... Smith ... Martin ... ... ... Cómo SQL Server almacena y tiene acceso a los datos • Cómo se almacenan los datos • Las filas se almacenan en páginas de datos • Los montones son una colección de páginas de datos para una tabla • Acceso a los datos • Recorre todas las páginas de datos en una tabla • Mediante un índice que apunte a los datos de una página Páginas de datos Página 8 Página 9 Página 4 Página 5 Página 6 Página 7 Con ... Rudd Smith Martin Ganio ... ... ... ... Funk ... White Ota Phua Jones ... ... ... ... White ... Barr Jones Jones Hall ... ... ... ... ... ... ... ... Smith ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...

5. 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

6. Arquitectura de los índices • Arquitectura de índices de SQL Server • Uso de montones • Uso de los índices agrupados • Uso de los índices no agrupados

7. Uso de montones SQL Server: • Utiliza las páginas de Mapa de asignación de índices que: • Contienen información acerca del lugar donde están almacenadas las extensiones de un montón • Se utilizan para recorrer el montón y encontrar espacio disponible para insertar nuevas filas • Conectan páginas de datos • Recupera espacio para las nuevas filas del montón cuando se elimina una fila

8. Uso de los índices agrupados • Cada tabla sólo puede tener un índice agrupado • El orden físico de las filas de la tabla y el orden de las filas en el índice son el mismo • La unicidad de los valores de clave se mantiene explícitamente o implícitamente

9. Uso de los índices no agrupados • Los índices no agrupados son los predeterminados de SQL Server • Los índices no agrupados existentes se vuelven a generar automáticamente • Se quita un índice agrupado existente • Se crea un índice agrupado • Se utiliza la opción DROP_EXISTING para cambiar las columnas que definen el índice agrupado

10. Cómo SQL Server recupera los datos almacenados • Cómo SQL Server utiliza la tabla sysindexes • Búsqueda de filas sin índices • Búsqueda de filas en un montón con un índice no agrupado • Búsqueda de filas en un índice agrupado • Búsqueda de filas en un índice agrupado con un índice no agrupado

11. Id. de índice Tipo de objeto 0 Montón 1 Índice agrupado 2 a 250 Índice no agrupado 255 text, ntext o image Cómo SQL Server utiliza la tabla sysindexes • Describe los índices • Ubicación de IAM, primero y raíz de índices • Número de páginas y filas • Distribución de datos

12. Búsqueda de filas sin índices sysindexes id indid = 0 Primera IAM IAM Extensión Mapa de bits … 127 1 128 1 129 0 130 1 … Montón Extensión 128 Extensión 129 Extensión 130 Extensión 127 01 Con … 01 Dunn … 01 Seattle … 01 Graff … 01 Rudd … 01 Rudd … 01 Rudd … 01 Rudd … 01 Akhtar … 01 Akhtar … 01 Akhtar … 01 Akhtar … 02 01 Funk Smith … … 02 01 Randall Smith … … 02 01 Paris Smith … … 02 01 Bacon Smith … … 02 01 White Con … … 02 01 White Con … … 02 01 White Con … … 02 01 White Con … … 02 01 Funk Rudd … … 02 01 Funk Rudd … … 02 01 Funk Rudd … … 02 01 Funk Rudd … … 03 02 White 01 Ota Akhtar … … … 03 02 Ota 01 Ota Akhtar … … … 03 02 Tokyo 01 Ota Akhtar … … … 03 02 Koch 01 Ota Akhtar … … … 03 02 Barr 01 Funk Smith … … … 03 02 Barr 01 Funk Smith … … … 03 02 Barr 01 Funk Smith … … … 03 02 Barr 01 Funk Smith … … … 03 02 Smith White … … 03 02 Smith White … … 03 02 Smith White … … 03 02 Smith White … … 04 03 Durkin 02 Jones Funk ... … … 04 03 Slichter 02 Jones Funk ... … … 04 03 Atlanta 02 Jones Funk ... … … … 03 ... 02 Jones Funk ... … … … 03 ... 02 White Ota ... … … … 03 ... 02 White Ota ... … … … 03 ... 02 White Ota ... … … … 03 ... 02 White Ota ... … … 04 03 Martin Barr ... … 04 03 Martin Barr ... … 04 03 Martin Barr ... … 04 03 Martin Barr ... … 05 … Lang 03 … Smith ... ... … 05 … LaBrie 03 … Smith ... ... … … … ... 03 … Smith ... ... … … … ... 03 … Smith ... ... … … … ... 03 ... Jones ... ... … … … ... 03 ... Jones ... ... … … … ... 03 ... Jones ... ... … … … ... 03 ... Jones ... ... … … … ... ... ... ... … … ... ... ... ... … … ... ... ... ... … … ... ... ... ... … 04 ... Martin ... ... … 04 ... Martin ... ... … 04 ... Martin ... ... … 04 ... Martin ... ... … … ... … ... ... … … ... … ... ... … … ... … ... ... … … ... … ... ... … ... ... … ... ... … ... ... … ... ... … ... ... … ... ... … ... ... … ... ... … ... ... … ... ... … ... ... … ... ...

13. id indid = 2 raíz Índice noagrupado Índicenoagrupado No situadasen el nivelde hoja No situadasen el nivelde hoja Akhtar Akhtar ... ... Martin Martin Martin Akhtar Akhtar Pág. 37 Pág. 37 Pág. 28 Pág. 28 Pág. 12 - Raíz Pág. 12 - Raíz Ganio Ganio Martin Martin Martin ... ... Smith Smith ... ... Nivel de hoja(Valor declave) Nivel de hoja(Valor declave) Pág. 41 Pág. 41 Pág. 51 Pág. 51 Pág. 61 Pág. 61 Pág. 71 Pág. 71 Akhtar Akhtar 4:706:01 4:706:01 Ganio Ganio Smith Smith 4:706:03 4:709:01 4:706:03 4:709:01 Barr Barr 4:705:03 4:705:03 Smith Hall Smith Hall 4:709:04 4:708:04 4:708:04 4:709:04 Martin Martin 4:708:01 4:708:01 Con Con 4:704:01 4:704:01 Smith Smith Jones Jones 4:709:02 4:707:01 4:707:01 4:709:02 Matey Matey Matey 4:706:04 4:706:04 4:706:04 Funk Funk 4:706:02 4:706:02 White White Jones Jones 4:708:03 4:708:03 4:704:03 4:704:03 Ota Ota 4:707:02 4:707:02 Funk Funk 4:704:02 4:704:02 White Jones Jones White 4:705:02 4:707:03 4:705:02 4:707:03 Phua Ota Phua Phua 4:708:02 4:707:02 4:708:02 4:708:02 Rudd Rudd Rudd 4:705:01 4:705:01 4:705:01 Montón Montón Pág. 704 Pág. 704 Pág. 705 Pág. 705 Pág. 706 Pág. 706 Pág. 707 Pág. 707 Pág. 808 Pág. 808 P 709 Pág. 709 01 01 ... ... Akhtar Akhtar 01 01 ... ... Conn Conn 01 01 01 ... ... ... Rudd Rudd Rudd 01 01 ... ... Smith Smith 01 01 ... ... Martin Martin 01 01 ... ... Ganio Ganio 02 02 ... ... Funk Funk 02 02 ... ... Funk Funk 02 02 ... ... White White 02 02 ... ... Ota Ota 02 02 02 02 ... ... ... ... Ota Phua Phua Phua 02 02 ... ... Jones Jones 03 03 ... ... Smith Smith 03 03 ... ... White White 03 03 ... ... Barr Barr 03 03 ... ... Jones Jones 03 03 ... ... Jones Jones 03 03 ... ... Hall Hall 04 04 ... ... Matey Matey ... ... ... ... ... ... ... ... ... ... ... ... 04 ... Matey ... ... ... ... ... ... 04 04 ... ... Smith Smith ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... File ID #4 File ID #4 Búsqueda de filas en un montón con un índice no agrupado sysindexes SELECT lastname, firstname FROM member WHERE lastname BETWEEN 'Masters' AND 'Rudd'

14. id indid = 1 raíz sysindexes Índice agrupado Índice agrupado Akhtar Akhtar … … Martin Martin Pág. 140 - Raíz Pág. 140 - Raíz Akhtar Akhtar Martin Martin Ganio Ganio Smith Smith … … … … Pág. 141 Pág. 141 Pág. 145 Pág. 145 Akhtar Akhtar 2334 2334 ... ... Ganio Ganio 7678 7678 ... ... Martin Martin 1234 1234 ... ... Smith Smith 1434 1434 ... ... Barr Barr 5678 5678 ... ... Hall Hall 8078 8078 ... ... Martin Martin 7778 7778 ... ... Smith Smith 5778 5778 ... ... Con Con 2534 2534 ... ... Jones Jones 2434 2434 ... ... Ota Ota 5878 5878 ... ... Smith Smith 7978 7978 ... ... Funk Funk 1334 1334 ... ... Jones Jones 5978 5978 ... ... Phua Phua 7878 7878 ... ... White White 2234 2234 ... ... ... ... ... ... ... ... ... ... Funk Funk 1534 1534 Jones Jones 2634 2634 Rudd Rudd 6078 6078 White White 1634 1634 ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... Pág. 100 Pág. 100 Pág. 110 Pág. 110 Pág. 120 Pág. 120 Pág. 130 Pág. 130 Ota 5878 ... Búsqueda de filas en un índice agrupado Martin SELECT lastname, firstname FROM member WHERE lastname = 'Ota' Martin

15. id indid = 2 raíz Índice noagrupadoparaFirst Name No situadasen el nivelde hoja Aaron Aaron ... ... Jose Jose Aaron Aaron Jose Jose Deanna Deanna Nina Nina … … … … Nivelde hoja(Valor de claveagrupado) Aaron Aaron Con Con Jose Jose Lugo Lugo Deanna Deanna Daum Daum Adam Adam Barr Barr Judy Judy Kaethler Kaethler Don Don Hall Hall Amie Amie Baldwin Baldwin Mike Mike Mike Nash Nash Nash Doug Doug Hampton Hampton … … … … … … … … … … … … Barr Barr Índice agrupadopara Last Name Kim Kim Nagata Nagata O’Melia O’Melia Barr Barr Adam Adam … … Kim Kim Shane Shane … … Nagata Nagata Susanne Susanne … … Cox Cox Arlette Arlette … … Kobara Kobara Linda Linda … … Nash Nash Nash Mike Mike Mike … … … Daum Daum Deanna Deanna … … LaBrie LaBrie Ryan Ryan … … Nixon Nixon Toby Toby … … … … … … … … … … … … … … … … … … … … Búsqueda de filas en un índice agrupado con un índice no agrupado sysindexes Índice noagrupadopara First Name No situadasen el nivelde hoja SELECT lastname, firstname, phone FROM member WHERE firstname = 'Mike' Nivelde hoja(Valor de claveagrupado) Índice agrupadopara Last Name Nagata

16. Cómo SQL Server mantiene las estructuras de los índices y los montones • Divisiones de páginas en un índice • Puntero de reenvío en un montón • Cómo SQL Server actualiza filas • Cómo SQL Server elimina filas

17. Ganio … Hall … … … … … Nivel de hoja(Valor de clave) Hart … Jones … Jones … Jackson … Jackson … Akhtar Akhtar … … Ganio Jones … … Lang Lang … … Smith Smith … … … … … … Barr Barr … … Hall Jones … … Martin Martin … … Smith Smith … … Barr Barr … … Hart … Martin Martin … … Smith Smith Akhtar Lang … … Borm Borm … … Martin Martin … … Smith Smith … Smith … … Buhl Buhl … … Moris Moris … … Smith Smith Martin … … … Divisiones de páginas en un índice INSERT member (last name) VALUES lastname = ‘Jackson' Páginas de índice No situadasen el nivelde hoja Akhtar Ganio … Jackson Nivel de hoja(Valor de clave)

18. id indid = 2 raíz Índice noagrupado Índice noagrupado No situadasen el nivelde hoja No situadasen el nivelde hoja Akhtar Akhtar ... ... Martin Martin Martin Akhtar Akhtar Pág. 37 Pág. 37 Pág. 28 Pág. 28 Pág. 12 - Raíz Pág. 12 - Raíz Ganio Ganio Martin Martin Martin ... ... Smith Smith ... ... Nivel de hoja(Valor declave) Nivel de hoja(Valor de clave) Pág. 41 Pág. 41 Pág. 51 Pág. 51 Pág. 61 Pág. 61 Pág. 71 Pág. 71 Akhtar Akhtar 4:706:01 4:706:01 Ganio Smith Smith Ganio 4:706:03 4:709:01 4:709:01 4:706:03 Barr Barr 4:705:03 4:705:03 Smith Hall Smith Hall 4:709:04 4:708:04 4:709:04 4:708:04 Martin Martin 4:708:01 4:708:01 Ota 4:707:02 Con Con 4:704:01 4:704:01 Smith Smith Jones Jones 4:707:01 4:709:02 4:709:02 4:707:01 Martin Martin 4:706:04 4:706:04 Funk Funk 4:706:02 4:706:02 Jones Jones White White 4:708:03 4:704:03 4:704:03 4:708:03 Ota Ota 4:707:02 4:707:02 Funk Funk 4:704:02 4:704:02 White Jones White Jones 4:705:02 4:707:03 4:707:03 4:705:02 Phua Phua 4:708:02 4:708:02 Rudd Rudd 4:705:01 4:705:01 Montón Montón Pág. 704 Pág. 704 Pág. 705 Pág. 705 Pág. 706 Pág. 706 Pág. 707 Pág. 707 Pág. 808 Pág. 808 Pág. 709 Pág. 709 01 01 ... ... Akhtar Akhtar 01 01 ... ... Conn Conn 01 01 ... ... Rudd Rudd 01 01 ... ... Smith Smith 04 01 01 02 02 02 ... ... ... ... ... Martin Martin Ota Ota Ota Ota 01 01 ... ... Ganio Ganio 02 02 ... ... Funk Funk 02 02 ... ... Funk Funk 02 02 ... ... White White 02 02 ... ... Ota Ota 02 02 ... ... Phua Phua 02 02 ... ... Jones Jones 03 03 ... ... Smith Smith 03 03 ... ... White White 03 03 ... ... Barr Barr 03 03 ... ... Jones Jones 03 03 ... ... Jones Jones 03 03 ... ... Hall Hall 04 04 ... ... Martin Martin ... ... ... ... ... ... ... ... ... ... ... ... 04 04 ... ... Corets Corets 04 04 ... ... Smith Smith ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... 05 05 ... ... Nash Nash ... ... ... ... ... ... ... ... ... ... ... ... File ID #4 File ID #4 Puntero de reenvío en un montón sysindexes UPDATE member SET Address = <something long> WHERE lastname = 'Ota'

19. Cómo SQL Server actualiza filas • Una actualización no suele hacer que una fila se mueva • Una actualización puede ser una eliminación seguida de una inserción • Las actualizaciones por lotes tocan cada índice una sola vez

20. Cómo SQL Server elimina filas • Cómo las eliminaciones producen registros fantasma • Cómo SQL Server reclama espacio • Cómo se pueden reducir los archivos Las páginas de índice agrupadas se desplazan como una unidad Los registros del montón se desplazan de forma individual

21. Decisión de las columnas que se van a indizar • Comprensión de los datos • Directrices de indización • Elección del índice agrupado adecuado • Creación de índices que admiten consultas • Determinación de la selectividad • Determinación de la densidad • Determinación de la distribución de datos

22. Comprensión de los datos • El diseño lógico y físico • Las características de los datos • Cómo se utilizan los datos • Los tipos de consultas realizadas • La frecuencia de las consultas más típicas

23. Directrices de indización • 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 agregación • 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

24. Elección del índice agrupado adecuado • Tablas continuamente actualizadas • Un índice agrupado con una columna de identidad mantiene las páginas actualizadas en memoria • Ordenación • Un índice agrupado mantiene los datos preordenados • Longitud de columna y tipo de datos • Limita el número de columnas • Reduce el número de caracteres • Utiliza los tipos de datos más pequeños posibles

25. Creación de índices que admiten consultas • Uso de argumentos de búsqueda • Escritura de buenos argumentos de búsqueda • Especificar una cláusula WHERE en la consulta • Comprobar que la cláusula WHERE limita el número de filas • Comprobar que existe una expresión para cada tabla a la que se hace referencia en la consulta • Evitar el uso de caracteres comodines iniciales

26. 900010000 Determinación de la selectividad Alta selectividad member_no last_name first_name Número de filas que cumplen el criterioNúmero total de filas en la tabla 100010000 1 Randall Joshua = 10% = 2 Flood Kathie . SELECT *FROM memberWHERE member_no > 8999 . . 10000 Anderson Bill Baja selectividad member_no last_name first_name Número de filas que cumplen el criterioNúmero total de filas en la tabla 1 Randall Joshua = 90% = 2 Flood Kathie . SELECT *FROM memberWHERE member_no < 9001 . . 10000 Anderson Bill

27. last_name first_name Randall Joshua . . . Randall Cynthia Randall Tristan . . . Ota Lani . . . Determinación de la densidad Alta densidad SELECT *FROM memberWHERE last_name = ‘Randall’ Baja densidad SELECT *FROM memberWHERE last_name = ‘Ota’

28. Distribución estándar de valores NúmerodeLast Names A - E F - J K - O P - U V - Z Last Name Determinación de la distribución de datos Distribución uniforme de valores NúmerodeLast Names A - B C - F G - K L - N O - Z Last Name