consultas optimizar sql

35
Distintas formas de optimizar las consultas realizadas en SQL. Por Claudio El lenguaje SQL es no procedimental, es decir, en las sentencias se indica que queremos conseguir y no como lo tiene que hacer el interprete para conseguirlo. Esto es pura teoría, pues en la práctica a todos los gestores de SQL hay que especificar sus propios truquitos para optimizar el rendimiento. Por tanto, muchas veces no basta con especificar una sentencia SQL correcta, sino que además, hay que indicarle como tiene que hacerlo si queremos que el tiempo de respuesta sea el mínimo. En este apartado veremos como mejorar el tiempo de respuesta de nuestro interprete ante unas determinadas situaciones: Diseño de las tablas Normaliza las tablas, al menos hasta la tercera forma normal, para asegurar que no hay duplicidad de datos y se aprovecha al máximo el almacenamiento en las tablas. Si hay que desnormalizar alguna tabla piensa en la ocupación y en el rendimiento antes de proceder. Los primeros campos de cada tabla deben ser aquellos campos requeridos y dentro de los requeridos primero se definen los de longitud fija y después los de longitud variable. Ajusta al máximo el tamaño de los campos para no desperdiciar espacio. Es muy habitual dejar un campo de texto para observaciones en las tablas. Si este campo se va a utilizar con poca frecuencia o si se ha definido con gran tamaño, por si acaso, es mejor crear una nueva

Upload: edomega

Post on 06-Aug-2015

104 views

Category:

Documents


7 download

DESCRIPTION

Informatica

TRANSCRIPT

Page 1: Consultas Optimizar SQL

Distintas formas de optimizar las consultas realizadas en SQL.

Por Claudio

El lenguaje SQL es no procedimental, es decir, en las sentencias se

indica que queremos conseguir y no como lo tiene que hacer el

interprete para conseguirlo. Esto es pura teoría, pues en la práctica a

todos los gestores de SQL hay que especificar sus propios truquitos

para optimizar el rendimiento.

Por tanto, muchas veces no basta con especificar una sentencia SQL

correcta, sino que además, hay que indicarle como tiene que hacerlo si

queremos que el tiempo de respuesta sea el mínimo. En este apartado

veremos como mejorar el tiempo de respuesta de nuestro interprete

ante unas determinadas situaciones:

Diseño de las tablas

Normaliza las tablas, al menos hasta la tercera forma normal,

para asegurar que no hay duplicidad de datos y se aprovecha al

máximo el almacenamiento en las tablas. Si hay que

desnormalizar alguna tabla piensa en la ocupación y en el

rendimiento antes de proceder.

Los primeros campos de cada tabla deben ser aquellos campos

requeridos y dentro de los requeridos primero se definen los de

longitud fija y después los de longitud variable.

Ajusta al máximo el tamaño de los campos para no desperdiciar

espacio.

Es muy habitual dejar un campo de texto para observaciones en

las tablas. Si este campo se va a utilizar con poca frecuencia o si

se ha definido con gran tamaño, por si acaso, es mejor crear una

nueva tabla que contenga la clave primaria de la primera y el

campo para observaciones.

Gestión y elección de los índices

Los índices son campos elegidos arbitrariamente por el constructor de

la base de datos que permiten la búsqueda a partir de dicho campo a

Page 2: Consultas Optimizar SQL

una velocidad notablemente superior. Sin embargo, esta ventaja se ve

contrarrestada por el hecho de ocupar mucha más memoria (el doble

más o menos) y de requerir para su inserción y actualización un

tiempo de proceso superior.

Evidentemente, no podemos indexar todos los campos de una tabla

extensa ya que doblamos el tamaño de la base de datos. Igualmente,

tampoco sirve de mucho el indexar todos los campos en una tabla

pequeña ya que las selecciones pueden efectuarse rápidamente de

todos modos.

Un caso en el que los índices pueden resultar muy útiles es cuando

realizamos peticiones simultáneas sobre varias tablas. En este caso, el

proceso de selección puede acelerarse sensiblemente si indexamos los

campos que sirven de nexo entre las dos tablas.

Los índices pueden resultar contraproducentes si los introducimos

sobre campos triviales a partir de los cuales no se realiza ningún tipo

de petición ya que, además del problema de memoria ya mencionado,

estamos ralentizando otras tareas de la base de datos como son la

edición, inserción y borrado. Es por ello que vale la pena pensárselo

dos veces antes de indexar un campo que no sirve de criterio para

búsquedas o que es usado con muy poca frecuencia por razones de

mantenimiento.

Campos a Seleccionar

En la medida de lo posible hay que evitar que las sentencias SQL

estén embebidas dentro del código de la aplicación. Es mucho

más eficaz usar vistas o procedimientos almacenados por que el

gestor los guarda compilados. Si se trata de una sentencia

embebida el gestor debe compilarla antes de ejecutarla.

Seleccionar exclusivamente aquellos que se necesiten

No utilizar nunca SELECT * por que el gestor debe leer primero la

estructura de la tabla antes de ejecutar la sentencia

Page 3: Consultas Optimizar SQL

Si utilizas varias tablas en la consulta especifica siempre a que

tabla pertenece cada campo, le ahorras al gestor el tiempo de

localizar a que tabla pertenece el campo. En lugar de SELECT

Nombre, Factura FROM Clientes, Facturacion WHERE IdCliente =

IdClienteFacturado, usa: SELECT Clientes.Nombre,

Facturacion.Factura WHERE Clientes.IdCliente =

Facturacion.IdClienteFacturado.

Campos de Filtro

Se procurará elegir en la cláusula WHERE aquellos campos que

formen parte de la clave del fichero por el cual interrogamos.

Además se especificarán en el mismo orden en el que estén

definidos en la clave.

Interrogar siempre por campos que sean clave.

Si deseamos interrogar por campos pertenecientes a índices

compuestos es mejor utilizar todos los campos de todos los

índices. Supongamos que tenemos un índice formado por el

campo NOMBRE y el campo APELLIDO y otro índice formado por

el campo EDAD. La sentencia WHERE NOMBRE='Juan' AND

APELLIDO Like '%' AND EDAD = 20 sería más optima que WHERE

NOMBRE = 'Juan' AND EDAD = 20 por que el gestor, en este

segundo caso, no puede usar el primer índice y ambas

sentencias son equivalentes por que la condición APELLIDO Like

'%' devolvería todos los registros.

Orden de las Tablas

Cuando se utilizan varias tablas dentro de la consulta hay que tener

cuidado con el orden empleado en la cláusula FROM. Si deseamos

saber cuantos alumnos se matricularon en el año 1996 y escribimos:

FROM Alumnos, Matriculas WHERE Alumno.IdAlumno =

Matriculas.IdAlumno AND Matriculas.Año = 1996 el gestor recorrerá

todos los alumnos para buscar sus matriculas y devolver las

correspondientes. Si escribimos FROM Matriculas, Alumnos WHERE

Matriculas.Año = 1996 AND Matriculas.IdAlumno =

Page 4: Consultas Optimizar SQL

Alumnos.IdAlumnos, el gestor filtra las matrículas y después selecciona

los alumnos, de esta forma tiene que recorrer menos registros.

Comentar algunos errores comunes observados en numerosas instalaciones:1) En uniones de consultas es mejor el uso de UNION ALL en vez de UNION si ambas subconsultas no pueden devolver datos repetidos.2) Las LEFT JOIN y RIGHT JOIN son en realidad OUTER JOINs a pesar de que no se explicite la palabra OUTER.3) En el uso de índices no es necesario utilizar todos los campos del mismo pero no es óptimo utilizar un campo si no se han informado los campos previos por igualdad .

esto solo funciona en ADO 2.8 para arriba o en SQL server 2000.

Para usar el LIKE en ADO 2.7 para abajo deben cambiarse los comodines '%' por los comodines '*' (asteriscos). De no hacerlo el LIKE funcionara buscando un STRING que contenga el %

Ejemplos:

BUSCA TODO LO Q TENGA H EN Mi_Campo en ADO <= 2.7SELECT * FROM Mi_Tabla WHERE Mi_Campo LIKE '*H*'

BUSCA TODO LO Q TENGA H EN Mi_Campo en ADO >= 2.8 y SQL SERVERSELECT * FROM Mi_Tabla WHERE Mi_Campo LIKE '%H%'

1.Es preferible realizar las relaciones entra tablas usando INNER JOIN, LEFT JOIN, RIGHT JOIN ... y no realizar la relación con la cláusula WHERE. 2. No se recomienda usar cláusula muy frecuentes como LIKE 3. Utilizar lo menos posible ANY, SOME, EXISTS, IN (SELECT Campo1 From Tabla2) 4. Utilizar FILEGROUPS, lo q permite asignar una base de datos, tabla, indices o determinados campos TEXT o IMAGES hacia diferentes archivos, lo cual permite una excelente aceleración. 5. Colocar las bases de datos en diferentes discos duros 6. Configurar los campos con la misma intercalación. 5. No utilizar cursores!!!!! 6. No utilizar tablas temporales con # o ## 7. Utilizar para poco volumen de datos en lugar de cursores las llamadas variables tipo tablas: DECLARE T1 TABLE (CAMPO1 VARCHAR(1))

Page 5: Consultas Optimizar SQL

8. Utilizar vistas en lugar de consultas gigantes en tus StoreProc. o mezclar el uso de estas, porque recordemos que las vistas son ya pre-compiladas.

Query Optimizer. Index Selection. Selección de índice adecuado. SQL Server. Tema: VII

En este tema voy a comentar como podemos verificar que se está usando el índice de una forma correcta en una consulta, ya que no solo por que aparezca el índice en el plan de ejecución de la consulta ha de ser correcto.  Por dar una primera pincelada, si nosotros encontramos en un plan de ejecución, donde un índice se esta usando como Index Scan, muy probablemente no se este usando de la forma adecuada, ya que Index Scan para obtener su resultado ha de pasar por todas los campos del índice. Mientras que si encontramos un Index Seek, forma normal de utilización de un índice, solo se accede para obtener el resultado a los campos selectivos por la consulta del índice.  Es aconsejable para el buen entendimiento de este “Tema:VII” que se lea o repase los enlaces: Query Optimizer. Parameter Sniffing. SQL Server. Tema: I y el enlace: Query Optimizer. Cardinality Estimation Error. SQL Server. Tema: II, y sus temas posteriors.   Normalmente, partiendo de escenarios con índices y estadísticas actualizadas, cuando usamos consultas donde filtramos por un valor específico como por ejemplo: Select ProductID, SalesOrderID, salesOrderDetailID form sales. salesOrderDetail where ProductID =771 Os voy a mostrar un ejemplo en un plan de ejecución donde se ve la forma correcta de cómo ha de usarse un índice,. Veremos que usa el operador: Index Seek, y verificamos que usa predicados de búsqueda: Seek Predicates:

Page 6: Consultas Optimizar SQL

Se suelen dar en casos donde utilizamos funciones o diferentes expresiones para filtrar en las consultas, como por ejemplo: Select ProductID, SalesOrderID, salesOrderDetailID form sales. salesOrderDetail where ABS(ProductID) =771 quizás SQL Server, no va a utiliza los índices deforma correcta. En el ejemplo que os muestro en el plan de ejecución de la consulta, vemos que usa Index Scan, donde hace un scan del indice que por lo menos es mejor hacer un scan de la tabla (Table Scan). Vemos que no tenemos un Seek Predicates pero si un Predicate -> esto no es bueno, ya que hace un sacan de todo el índice para buscar los valores específicos de ProductID.

Page 7: Consultas Optimizar SQL

Obviamente, podemos estar en escenarios con índices de más de una columna, y con varios predicados, como es el caso en del ejemplo: Select ProductID, SalesOrderID, salesOrderDetailID form sales. salesOrderDetail where ProductID =771 AND SalesOrderID=45233 para este caso, el resultado es muy bueno por que usa un Index Seek y nos hemos de fijar que en el Seek Predicates hace la búsqueda por las dos columnas ProducID y SalesOrderID:

Como ultimo caso, voy a mostrar una mezcla de los casos anteriores: Select ProductID, SalesOrderID, salesOrderDetailID form sales. salesOrderDetail where ProductID =771 AND

Page 8: Consultas Optimizar SQL

ABS(SalesOrderID)=45233 en estos casos puede ser mas difícil de encontrar un problema aunque veamos que usa un Index Seek y nos parezca que todo esta perfecto, realmente no lo esta, por que en el Seek Predicates solo busca por ProductID, y no por la función ABS(salesOrderID), donde vemos que hace su busqueda en el Predicate: (El índice hace dos operaciones, primero busca el ProductID, haciendo uso del Index Seek, y segundo busca por SalesOrderID haciendo un Scan de la columna en índice…)

 Actualización de estadísticas síncronas o asíncronas mejoran la respuesta del optimizador de consultas. SQL ServerLas estadísticas usadas para la optimización de consulta, son objetos que contienen información estadística acerca de la distribución de valores en una o más columnas de una tabla o vista indexada. El optimizador de consultas utiliza las estadísticas para estimar la cardinalidad o el número de filas del resultado de la consulta, lo que hace posible que el optimizador de consultas pueda crear un plan de consulta de alta calidad. Hay 3 opciones de estadísticas, configurables, a nivel de cada base de datos:AUTO_CREATE_STATISTICSAUTO_UPDATE_STATISTICSAUTO_UPDATE_STATISTICS_ASYNC

Page 9: Consultas Optimizar SQL

AUTO_CREATE_STATISTICS y AUTO_UPDATE_STATISTICS

La opción automática de creación de estadísticas, AUTO_CREATE_STATISTICS, y la de actualización de estadísticas, AUTO_UPDATE_STATISTICS, están activadas de forma predeterminada:

ALTER DATABASE TU_BaseDatos SET AUTO_CREATE_STATISTICS ON;ALTER DATABASE TU_BaseDatos SET AUTO_UPDATE_STATISTICS ON;GO

Estas actualizaciones de las estadísticas son sincronas, las consultas siempre se compilan y ejecutan con estadísticas actualizadas; cuando las estadísticas están obsoletas, el optimizador de consultas espera a que las estadísticas estén actualizadas antes de compilar y ejecutar la consulta.

Se aconseja su uso cuando se realiza operaciones que cambian la distribución de los datos, como truncar una tabla o realizar una actualización masiva de un gran porcentaje de las filas. Si no actualiza las estadísticas después de completar la operación, el uso de estadísticas sincrónicas garantizará que las estadísticas estén actualizadas antes de ejecutar las consultas en los datos cambiados.

AUTO_UPDATE_STATISTICS_ASYNC

La opción automática de creación de estadísticas asincronas, AUTO_UPDATE_STATISTICS_ASYNC, no están activadas de forma predeterminada:

ALTER DATABASE TU_BaseDatos SET AUTO_UPDATE_STATISTICS_ASYNC ON;GO

Estas actualizaciones de las estadísticas son asincrónicas, las consultas se compilan con las estadísticas existentes incluso aunque estén anticuadas; el optimizador de consultas podría elegir un plan de consulta poco óptimo si las estadísticas están obsoletas cuando se compila la consulta. Las consultas que se compilan cuando las actualizaciones asincrónicas se han completado se beneficiarán del uso de estadísticas actualizadas.

Se aconseja su uso:

Page 10: Consultas Optimizar SQL

Cuando su aplicación ejecuta frecuentemente la misma consulta, consultas similares o los planes de consulta almacenados en memoria caché similares. Sus tiempos de respuesta a la consulta podrían ser más predecibles con actualizaciones asincrónicas de las estadísticas que con actualizaciones sincrónicas, porque el optimizador de consultas puede ejecutar las consultas de entrada sin esperar a que las estadísticas se actualicen

Si su aplicación ha experimentado tiempos de espera de solicitud de cliente causados por una o varias consultas que aguardaban la actualización de estadísticas. En algunos casos, la espera por las estadísticas sincrónicas podría causar errores en aplicaciones con tiempos de espera agresivos.

 

 Usar estadísticas sincronas y asincronas, de forma conjunta. 

La opción AUTO_UPDATE_STATISTICS_ASYNC se establece en el nivel de la base de datos y determina el método de actualización para todas las estadísticas de la base de datos. Sólo es aplicable a la actualización de estadísticas y no se puede usar para crear estadísticas de forma asincrónica. El establecimiento de esta opción en ON no tiene ningún efecto a menos que AUTO_UPDATE_STATISTICS también se establezca en ON. De forma predeterminada, la opción AUTO_UPDATE_STATISTICS_ASYNC está en OFF.

 En aquellos escenarios donde no tenemos un control total sobre las operaciones que se realizan en la base de datos y no podemos hilar siempre muy fino es preferible tener actualizaciones síncronas, de esta forma no obtendremos la mejor de las latencias y posiblemente suframos en algunas consultas, pero minimizamos el riesgo de tener pocas consultas “optimizadas”, en base a unas estadísticas no adecuadas, que nos pueden dar un gran disgusto.

Estadísticas filtradas CREATE STATISTICS

Las estadísticas filtradas pueden mejorar el rendimiento de las consultas. Para la mayoría de las consultas, el optimizador de consultas genera ya las estadísticas necesarias para un plan de consulta de alta calidad; en algunos casos, para mejorar el rendimiento de la consulta necesita crear estadísticas adicionales con CREATE STATISTICS.

Page 11: Consultas Optimizar SQL

A modo de ejemplo, se crean las estadísticas NameStatistic para todas las filas de las columnas Colum1ID y Colum2 de la tabla TBA y se deshabilita la posibilidad de volver a calcular las estadísticas automáticamente

CREATE STATISTICS NameStatistic ON Tu_BaseDatos. TBA.TBA (Colum1ID, Colum2) WITH FULLSCAN, NORECOMPUTE;  Utilizar esta opción puede producir planes de consulta poco óptimos. Se recomienda usar esta opción con moderación y que lo haga únicamente un administrador de sistemas cualificado.Activar actualización automática de estadísticas en SQL 2000 Para activar la actualización automática de estadísticas en SQL 2000: 

--habilita: auto create statisticssp_dboption TubaseDatos, 'auto create statistics', 'ON'GO--habilita: auto update statisticssp_dboption TubaseDatos, 'auto update statistics', 'ON'GO--habilita: sp_autostatsEXEC sp_autostats 'ON'GO

  Cuando se actualizan las estadísticas automáticas    Para tablas con muchas filas, hemos de tener cuidado ya que las estadísticas se actualizan “automáticamente” cuando se han cambiado (insert/update/delete) mas del 20% de la cantidad de filas actuales de la tabla. Ese valor se guarda en la columna [rowmodctr] en la tabla de sistema [sysindexes].  Por poner un ejemplo, en una tabla con 80.000.000 registros, e imaginando que hay un promedio de 200.000 registros modificados por día. Y luego solo se hacen select el resto del día: 100.00 * (200,000 / 80,000,000) = 0.25  Como veis, la cantidad de filas que se insertan en un día equivalen al 0.25, de la cantidad de filas actuales y por tanto SQL Server no actualizara las estadísticas hasta que el valor en [rowmodctr] sea el 20% de las filas. Por eso hay que vigilar de cerca las tablas con

Page 12: Consultas Optimizar SQL

muchas filas y de ser posible buscar el tiempo adecuado para actualizar sus estadísticas. Ya que puede penalizar la creación de los planes de ejecución, en estas tablas.   Para forzar la actualización de estadísticas, en todos los objetos de las bases de datos, para evitar estar en el anterior escenario comentado: 

--Actualizo estadisticasEXEC sp_updatestats GO 

  Os paso el procedimiento que nos dice el tamaño de un tabla de nuestra base de datos: 

sp_spaceused 'Nombre_De_Tu_Tabla'  Os paso un procedimiento para ver los tamaños de todas las tablas de una base de datos, en SQL Server 2000: (En versiones posteriores, esta información se muestra desde el Management Studio) 

CREATE PROCEDURE dbo.TableSpaceUsedAS -- Create the temporary table...CREATE TABLE #tblResults(   [name]   nvarchar(20),   [rows]   int,   [reserved]   varchar(18),   [reserved_int]   int default(0),   [data]   varchar(18),   [data_int]   int default(0),   [index_size]   varchar(18),   [index_size_int]   int default(0),   [unused]   varchar(18),   [unused_int]   int default(0)) -- Populate the temp table...EXEC sp_MSforeachtable @command1=         "INSERT INTO #tblResults           ([name],[rows],[reserved],[data],[index_size],[unused])          EXEC sp_spaceused '?'"  

Page 13: Consultas Optimizar SQL

-- Strip out the " KB" portion from the fieldsUPDATE #tblResults SET   [reserved_int] = CAST(SUBSTRING([reserved], 1,                              CHARINDEX(' ', [reserved])) AS int),   [data_int] = CAST(SUBSTRING([data], 1,                              CHARINDEX(' ', [data])) AS int),   [index_size_int] = CAST(SUBSTRING([index_size], 1,                              CHARINDEX(' ', [index_size])) AS int),   [unused_int] = CAST(SUBSTRING([unused], 1,                              CHARINDEX(' ', [unused])) AS int)   -- Return the results...SELECT * FROM #tblResults

 

 Como calcular la longitud de los campos de todas las tablas de una bbdd

--Longitud de campo de todas las tablas en sql sever 2005

SET NOCOUNT ON DBCC UPDATEUSAGE(0) -- DB size.

EXEC sp_spaceused-- Table row counts and sizes.

CREATE TABLE #t ( [name] NVARCHAR(128), [rows] CHAR(11), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18))

INSERT #t EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' SELECT *FROM #t-- # of rows.

SELECT SUM(CAST([rows] AS int)) AS [rows]FROM #t DROP TABLE #t

--en lugar de select sum(cast(rows as int) que dice el número total de filas, puedes poner

SELECT SUM(CAST([rows] AS int)) AS [rows], sum(cast(reserved as flaot))*1024 as bytereservados FROM #t

 

Fuentes:Microsoft, MSDN

Page 14: Consultas Optimizar SQL

Chapter 11

Creación de índices en tablas

Es posible crear uno o más índices en una tabla a fin de acelerar el proceso de recuperación de datos. Los índices son transparentes para los usuarios que acceden a los datos de esa tabla; SQL Server decide automáticamente cuándo usar los índices creados para las tablas.

En este capítulo se trata lo siguiente:

Introducción general a los índices y algunas indicaciones sobre cuándo deben usarse

Creación de índices para una tabla Uso de índices agrupados y no agrupados Especificación de opciones de índices Omisión de índices Determinación de los índices que existen en una tabla

Definición de índice

Los índices ayudan a SQL Server a localizar datos. Aceleran el proceso de recuperación de información indicando a SQL Server la posición que ocupan los datos de una columna de tabla en el disco. Las tablas pueden tener más de un índice.

Los índices son transparentes para los usuarios. SQL no incluye ninguna sintaxis para hacer referencia a un índice en una consulta. Sólo es posible crear u omitir índices de una tabla; SQL Server decide si usarlos o no para cada una de las consultas ejecutadas para esa tabla. A medida que los datos de una tabla van cambiando con el tiempo, SQL Server puede cambiar los índices de la tabla de modo que reflejen esas modificaciones. También estos cambios son transparentes para los usuarios, SQL Server lleva a cabo esta tarea por su cuenta.

SQL Server admite los siguientes tipos de índices:

Indices compuestos : estos índices abarcan más de una columna. Este tipo de índice se usa cuando es más conveniente buscar dos o más columnas como unidad, debido a la relación lógica existente entre ellas.

Indices únicos : estos índices no permiten que dos filas de las columnas especificadas tengan el mismo valor. SQL Server verifica si existen valores duplicados cuando se crea el índice (si ya existen datos) y cada vez que se añaden datos.

Page 15: Consultas Optimizar SQL

Indices agrupados o no agrupados : los índices agrupados obligan a SQL Server a que ordene y vuelva a ordenar continuamente las filas de la tabla de modo que su orden físico sea siempre el mismo que el orden lógico (o indexado). Sólo se permite un índice agrupado por tabla. Los índices no agrupados no requieren que el orden físico de las filas sea el mismo que el orden indexado. Todos los índices no agrupados pueden proporcionar acceso a los datos con un criterio de ordenación diferente.

Estos tipos de índices se describen con mayor detalle más adelante en este capítulo.

Comparación de las dos formas de creación de índices

Es posible crear índices en las tablas usando la instrucción create index (descrita en este capítulo), o bien usando las restricciones de integridad unique o primary key del comando create table . Sin embargo, estas restricciones de integridad están limitadas de las siguientes formas:

No podrá crear índices no únicos. No podrá usar las opciones proporcionadas por el comando

create index para adaptar el funcionamiento de los índices. Sólo podrá omitir estos índices como una restricción usando la

instrucción alter table .

Si la aplicación que usa requiere estas funciones, deberá crear los índices mediante create index . De lo contrario, las restricciones de integridad unique o primary key ofrecen una forma más sencilla de definir un índice para una tabla. Para obtener información sobre las restricciones unique y primary key , consulte el Capítulo 7, "Creación de bases de datos y tablas".

Indicaciones para el uso de índices

Los índices aceleran la recuperación de datos. La inclusión de un índice en una columna supone con frecuencia la diferencia entre una respuesta inmediata a una consulta y una larga espera.

Si esto es así, sería lógico suponer que lo adecuado es incluir un índice en cada columna. La razón más importante por la que esto no es así, es que la construcción de un índice lleva tiempo y ocupa espacio de almacenamiento.

Page 16: Consultas Optimizar SQL

Por ejemplo, tenga en cuenta que los índices no agrupados se vuelven a crear de forma automática cuando un índice agrupado se reconstruye.

Otra razón es que la inserción, eliminación o actualización de datos de columnas indexadas lleva más tiempo que el precisado por las no indexadas. Sin embargo, este coste se ve compensado con creces gracias a la enorme mejora que supone el uso de índices para el rendimiento de los procesos de recuperación.

A continuación se indican algunas directrices sobre cuándo utilizar índices:

Si planea realizar inserciones manuales en la columna IDENTITY, cree un índice único a fin de garantizar que las inserciones no asignen un valor que ya se haya usado.

Una columna a la que se acceda con frecuencia según criterios de ordenación, es decir, una especificada en la cláusula order by , probablemente debería indexarse a fin de que SQL Server pudiera beneficiarse del orden indexado.

Las columnas que se usan de forma regular en combinaciones siempre deberían indexarse, dado que el sistema puede llevar a cabo la combinación con mayor rapidez si las columnas están ordenadas según criterios de ordenación.

La columna que almacena la clave primaria de la tabla tiene con frecuencia un índice agrupado, especialmente si se combina a menudo con columnas de otras tablas (no olvide que sólo hay un índice agrupado por tabla).

Una columna en la que se realizan búsquedas frecuentes de márgenes de valores puede ser una opción adecuada para la asignación de un índice agrupado. Una vez encontrada la fila con el primer valor del margen, se garantiza que las filas con los valores subsiguientes serán físicamente adyacentes. Un índice agrupado no ofrece ventajas tan importantes para las búsquedas sobre valores únicos.

Existen algunos casos en los que los índices no son útiles:

Las columnas a las que casi nunca o nunca se hace referencia en las consultas no obtienen ninguna ventaja de los índices, puesto que el sistema casi nunca o nunca tiene que buscar filas basándose en los valores de dichas columnas.

Las columnas que sólo tienen dos o tres valores, como varón y mujer, o sí y no, tampoco se benefician de los índices.

Page 17: Consultas Optimizar SQL

Si el sistema tiene que buscar en una columna no indexada, lo hace examinando las filas una a una. El tiempo que se tarda en llevar a cabo este tipo de barrido es directamente proporcional al número de filas de la tabla.

Creación de índices para acelerar la recuperación de datos

Los índices se crean en las columnas para acelerar la recuperación de datos. El formato más sencillo del comando create index es:

create index index_name on table_name ( column_name )

Para crear un índice en la columna au_id de la tabla authors , el comando es el siguiente:

create index au_id_ind on authors(au_id)

El nombre del índice debe cumplir con las reglas para identificadores. Los nombres de columna y tabla especifican la columna que se desea indexar y la tabla que la contiene.

No es posible crear índices en columnas que tienen los tipos de datos bit , text o image .

Es necesario ser el propietario de una tabla para poder ejecutar create o drop a fin de crear u omitir un índice. El propietario de una tabla puede crear u omitir un índice en cualquier momento, independientemente de que haya datos en la tabla. Es posible crear índices en tablas de otra base de datos calificando el nombre de la tabla.

Sintaxis de create index

La sintaxis completa del comando create index es:

create [unique] [clustered | nonclustered] index index_name on [[ database .] owner .] table_name ( column_name [, column_name ]...) [with {{fillfactor | max_rows_per_page}= x, ignore_dup_key, sorted_data, [ignore_dup_row | allow_dup_row]}] [on segment_name ]

Page 18: Consultas Optimizar SQL

En los siguientes apartados se explican las diversas opciones del comando create index .

Note: La extensión on segment_name de create index permite colocar el índice en un segmento que apunte a un dispositivo de bases de datos específico o a un conjunto de dispositivos de bases de datos. Antes de crear un índice en un segmento, consulte al administrador del sistema o al propietario de la base de datos a fin de obtener una lista de los segmentos que puede utilizar. Algunos segmentos pueden estar asignados a tablas o índices específicos por razones de rendimiento, o por otras consideraciones.

Indexación de más de una columna: índices compuestos

Es necesario especificar uno o más nombres de columna si se desea crear un índice compuesto sobre los valores combinados de todas las columnas especificadas.

Los índices compuestos se usan cuando es conveniente buscar dos o más columnas como una unidad. Por ejemplo, la tabla friends_etc tiene un índice compuesto en pname y sname . Ponga todas las columnas que deben incluirse en el índice compuesto según los criterios de ordenación dentro del paréntesis después del nombre de la tabla, como a continuación:

create index nmind on friends_etc(pname, sname)

Las columnas de un índice compuesto no tienen que estar en el mismo orden que las columnas de la instrucción create table . El orden de pname y sname se puede invertir en la instrucción de creación de índices anterior.

Es posible combinar hasta 16 columnas en un mismo índice compuesto. Todas las columnas de un índice compuesto deben estar en la misma tabla. El tamaño máximo permitido de los valores de índice combinados es de 256 bytes. Es decir, la suma de las longitudes de las columnas que componen el índice compuesto no puede exceder de 256.

Es posible especificar dos o más nombres de columna al crear un índice. Estas columnas, junto con la columna sensitivity , forman un índice compuesto de los valores combinados de las columnas. Los índices compuestos se emplean cuando es conveniente buscar dos o más columnas como una unidad. Por ejemplo, la tabla friends_etc tiene un índice compuesto en pname , sname y sensitivity (añadida de

Page 19: Consultas Optimizar SQL

forma automática por SQL Server). Cuando especifique las columnas en la instrucción create index , ponga todas las columnas que deben incluirse en el índice compuesto, salvo sensitivity , entre paréntesis según los criterios de ordenación después del nombre de la tabla, como a continuación:

create index nmind on friends_etc(pname, sname)

Las columnas de un índice compuesto no tienen que estar en el mismo orden que las columnas de la instrucción create table . El orden de pname y sname podría invertirse en la instrucción de creación de índices anterior. SQL Server siempre añade sensitivity como la última columna de cada índice.

Uso de la opción unique

Un índice único es aquél en el que no se permite que dos filas tengan el mismo valor de índice, incluido el valor NULL. El sistema verifica la existencia de valores duplicados cuando el índice se crea, si ya existen datos, y realiza esta verificación cada vez que se añaden o modifican datos con una instrucción insert o update .

La especificación de un índice único sólo es útil cuando la unicidad es una característica de los datos propiamente dichos. Por ejemplo, no es conveniente asignar un índice único a una columna last_name (de apellidos), puesto que es probable que haya más de un "Smith" o "Wong" en tablas incluso de algunos centenares de filas.

Sin embargo, sí es adecuado asignar un índice único a la columna que contiene los números de la seguridad social. En este caso, la unicidad es una característica propia de los datos, puesto que cada persona tiene un número de seguridad social diferente. Además, un índice único puede hacer las veces de una verificación de integridad. Por ejemplo, la existencia de un número de seguridad social duplicado refleja con toda probabilidad un error en la introducción de los datos o por parte de la administración pública.

Si intenta crear un índice único en datos existentes que incluyen valores duplicados, el comando se aborta y SQL Server muestra un mensaje de error que indica el primer duplicado. No es posible crear un índice único en una columna que contiene valores nulos en más de una fila; éstos se consideran valores duplicados para fines de indexación.

Si intenta modificar datos que tienen asignado un índice único, el resultado depende de si ha usado la opción ignore_dup_key .

Page 20: Consultas Optimizar SQL

Consulte la sección dedicada a las opciones de índices más adelante en este capítulo.

Es posible usar la palabra clave unique en índices compuestos. Esto no se ha llevado a cabo para el índice friends_etc creado anteriormente.

Inclusión de columnas IDENTITY en índices no únicos

La opción identity in nonunique index incluye de forma automática una columna IDENTITY en las claves de índice de una tabla para que todos los índices creados en la tabla sean únicos. Esta opción de base de datos hace que los índices lógicamente no únicos sean únicos internamente y permite usarlos para procesar cursores actualizables y lecturas de nivel de aislamiento 0.

La tabla ya debe contener una columna IDENTITY para que la opción de base de datos identity in nonunique index funcione, ya sea por una instrucción create table o al definir la opción de base de datos auto identity como true antes de crear la tabla.

Use identity in nonunique index si planea utilizar cursores y lecturas de nivel de aislamiento 0 en tablas con índices no únicos. El índice único hace que el cursor se coloque en la fila correcta la siguiente vez que se efectúa una operación fetch con dicho cursor.

Uso de las opciones fillfactor y max_rows_per_page

Casi nunca es necesario incluir las opciones fillfactor o max_rows_per_page en la instrucción create index . Estas opciones se proporcionan para mejorar el rendimiento y sólo son útiles cuando se crea un nuevo índice sobre datos existentes.

fillfactor

Con la opción fillfactor , el usuario puede especificar en qué medida debe llenar SQL Server cada página de índice. La cantidad de espacio libre en una página de índice se debe controlar porque cuando una página de índice se llena una vez que se han añadido suficientes filas, el sistema debe emplear algún tiempo en dividirla a fin de dejar espacio para nuevas filas.

El valor predeterminado es 0, que es el valor que se usa cuando no se especifica ningún factor de llenado. El administrador del sistema puede cambiar el valor predeterminado con el procedimiento del sistema

Page 21: Consultas Optimizar SQL

sp_configure . Consulte la Guía de Administración del Sistema para obtener más información sobre fillfactor .

Los valores válidos de fillfactor especificados por el usuario están entre 1 y 100.

A continuación se muestra una instrucción create index que usa la opción fillfactor :

create index postalcode_ind on friends_etc(postalcode) with fillfactor = 100

Un valor fillfactor de 100 llena completamente todas las páginas y sólo es útil cuando se sabe de antemano que nunca va a cambiar ninguno de los valores de índice de la tabla.

max_rows_per_page

La opción max_rows_per_page limita el número de filas que SQL Server puede incluir en cada página de índice. Un valor max_rows_per_page bajo reduce la contienda de bloqueo y sólo resulta útil para las tablas a las que se accede con frecuencia. Los valores bajos también hacen que el índice ocupe espacio.

El valor predeterminado es 0, que se emplea cuando no se especifica un valor máximo. El usuario puede cambiar el valor con el procedimiento del sistema sp_relimit .

Los valores max_rows_per_page especificados por el usuario están entre 1 y 256.

La siguientes instrucción create index utiliza la opción max_rows_per_page :

create index postalcode_ind on friends_etc(postalcode) with max_rows_per_page = 10

Uso de índices agrupados o no agrupados

Con un índice agrupado, SQL Server ordena las filas de forma continuada de modo que su orden físico sea el mismo que el orden lógico, es decir, el indexado. El nivel inferior o de hoja de un índice agrupado contiene las páginas de datos reales de la tabla. Los índices agrupados deben crearse antes que los no agrupados, ya que estos

Page 22: Consultas Optimizar SQL

últimos se reconstruyen automáticamente cuando se crea un índice agrupado.

Por definición, sólo puede haber un índice agrupado por tabla. Este se crea a menudo en la clave primaria , es decir, la columna o columnas que identifican la fila de forma única.

Lógicamente, una clave primaria viene determinada por el diseño de la base de datos. Sin embargo, es posible definir de forma explícita las claves primarias, las claves externas y las claves comunes (pares de claves que se combinan con frecuencia) con los procedimientos del sistema sp_primarykey , sp_foreignkey y sp_commonkey . Puede mostrar información sobre las claves y sobre las columnas que son probables candidatos de combinación mediante sp_helpkey y sp_helpjoins , respectivamente.

Como alternativa, es posible especificar restricciones primary key con las instrucciones create table o alter table a fin de crear un índice e imponer los atributos de clave primaria para las columnas de la tabla. Para mostrar información sobre las restricciones, utilice sp_helpconstraint .

Para obtener una definición de las claves primarias y externas, consulte el Capítulo 15, "Disparadores: imposición de la integridad de referencia". Para obtener información completa sobre los procedimientos del sistema, consulte el Manual de Referencia de SQL Server.

Con un índice no agrupado, el orden físico de las filas no es el mismo que el indexado. El nivel de hoja de un índice no agrupado contiene punteros hacia las filas de las páginas de datos. Más concretamente, cada página hoja contiene un valor indexado y un puntero hacia la fila que contiene dicho valor. En otras palabras, un índice no agrupado tiene un nivel adicional entre la estructura de índice y los datos propiamente dichos.

Cada uno de los hasta 249 índices no agrupados permitidos en una tabla puede proporcionar acceso a los datos según un criterio de ordenación distinto.

La búsqueda de datos mediante un índice agrupado es casi siempre más rápido que mediante un índice no agrupado. Además, los índices agrupados suponen una ventaja cuando se recuperan muchas filas con valores clave contiguos, es decir, en las columnas donde se efectúan búsquedas frecuentes de márgenes de valores. Una vez que se encuentra la fila con el primer valor clave , se garantiza que las filas

Page 23: Consultas Optimizar SQL

con valores indexados subsiguientes serán físicamente adyacentes, y no será necesaria ninguna búsqueda adicional.

Si no se usa la palabra clave clustered ni la palabra clave nonclustered , se crea un índice no agrupado.

A continuación se muestra la forma de crear el índice de la columna title_id de la tabla titles (si desea ejecutar este comando, primero debe omitir el índice con drop index ):

create clustered index titleidind on titles(title_id)

Puesto que piensa que será necesario ordenar con frecuencia las personas de la tabla friends_etc según su código postal, debería crear un índice no agrupado en la columna postalcode de la siguiente manera:

create nonclustered index postalcodeind on friends_etc(postalcode)

Un índice único no tendría ningún sentido en este caso, puesto que es probable que algunos de sus contactos tengan el mismo código postal. Un índice agrupado no sería adecuado tampoco, puesto que el código postal no es la clave primaria.

El índice agrupado de friends_etc debería ser un índice compuesto sobre las columnas de nombre y apellidos. Para crear este índice agrupado, primero omita el índice no agrupado nmind :

drop index friends_etc.nmind

Y luego cree el índice agrupado:

create clustered index nmind on friends_etc(pname, sname) Note: Dado que el nivel inferior (o de hoja) de un índice agrupado y sus páginas de datos son iguales por definición, la creación de un índice agrupado ( clustered ) y el uso de la extensión on segment_name traslada efectivamente la tabla desde el dispositivo donde se creó hasta el segmento indicado.

Consulte al administrador del sistema o al propietario de la base de datos antes de crear tablas o índices en los segmentos; algunos segmentos pueden estar reservados por razones de rendimiento.

Page 24: Consultas Optimizar SQL

Especificación de opciones de índices

Las opciones de índices ignore_dup_key, ignore_dup_row y allow_dup_row controlan lo que ocurre cuando se crea una clave o fila duplicada con insert o update . A continuación se muestra una tabla que indica cuándo se deben usar estas opciones de índices:

Tabla 11-1: Opciones de índices

Tipo de índice Opciones

Agrupado ignore_dup_row | allow_dup_row

Agrupado único ignore_dup_key

No agrupado Ninguna

No agrupado único

ignore_dup_key

No agrupado único

ignore_dup_row

Uso de la opción ignore_dup_key

Si intenta insertar un valor duplicado en una columna que tiene un índice único, el comando se cancela. Es posible evitar que se cancele una transacción grande incluyendo la opción ignore_dup_key con un índice unique .

El índice unique puede ser agrupado o no agrupado. Cuando se comienza la introducción de datos, cada intento de inserción de una clave duplicada se cancela, con un mensaje de error. Las claves no duplicadas se insertan de la forma habitual.

Note: Si intenta ejecutar una instrucción update que crea una clave duplicada, la actualización se cancela. Tras la cancelación, todas las transacciones que estaban activas en ese momento pueden continuar como si la actualización con update no hubiese tenido lugar.

No se puede crear un índice único en una columna que ya incluye valores duplicados, independientemente de que ignore_dup_key esté definida. Si lo intenta, SQL Server imprime un mensaje de error y una

Page 25: Consultas Optimizar SQL

lista de valores duplicados. Hay que eliminar los duplicados antes de crear un índice único en la columna.

A continuación se muestra un ejemplo del uso de la opción ignore_dup_key :

create unique clustered index phone_ind on friends_etc(phone) with ignore_dup_key

Uso de las opciones ignore_dup_row y allow_dup_row

ignore_dup_row y allow_dup_row son opciones para la creación de un índice agrupado y no único. Estas opciones no son relevantes al crear un índice no agrupado y no único. Puesto que un índice no agrupado de SQL Server anexa internamente un número de identificación de fila único, no es necesario preocuparse por las filas duplicadas, ni siquiera por los valores de datos idénticos.

ignore_dup_row y allow_dup_row se excluyen mutuamente.

Si se define allow_dup_row , es posible crear un índice nuevo no único y agrupado en una tabla que incluye filas duplicadas y, a continuación, crear filas duplicadas subsiguientes con insert o update .

Si alguno de los índices de la tabla es único, el requisito de unicidad, que es el requisito más restrictivo, prevalece sobre la opción allow_dup_row . Por tanto, allow_dup_row sólo se aplica a tablas con índices no únicos. No puede usar esta palabra clave si hay un índice agrupado único en alguna de las columnas de la tabla.

La opción ignore_dup_row se utiliza para eliminar los duplicados de un lote de datos. Cuando se introduce una fila duplicada, esta fila se ignora y el comando insert en cuestión se cancela, con un mensaje de error informativo. Las filas no duplicadas se insertan de la forma habitual.

La opción ignore_dup_row se aplica sólo a las tablas con índices no únicos: no puede usar esta palabra clave si hay un índice único en alguna de las columnas de la tabla.

Note: Si intenta ejecutar una instrucción update que cree una fila duplicada, la actualización se cancelará. Tras la cancelación, las transacciones que estaban activas en ese momento pueden continuar como si la actualización no hubiese tenido lugar.

Page 26: Consultas Optimizar SQL

Esta tabla ilustra la forma en que allow_dup_row y ignore_dup_row afectan a los intentos de creación de un índice agrupado no único en una tabla que incluye filas duplicadas y a los intentos de introducción de filas duplicadas en una tabla.

Tabla 11-2: Opciones de filas duplicadas en índices

Opción Duplicados existentes

Introducción de duplicados

Ninguna opción definida

El comando create index no se ejecuta de forma correcta.

El comando de introducción de filas duplicadas no se ejecuta de forma correcta.

allow_dup_row definida El comando se ejecuta de forma correcta.

El comando se ejecuta de forma correcta.

ignore_dup_row<Default Para Font> definida

Se crea el índice, pero las filas duplicadas se eliminan; mensaje de error.

Se aceptan todas las filas, excepto las duplicadas; mensaje de error. Consulte la advertencia anterior.

Uso de la opción sorted_data

La opción sorted_data acelera la creación de un índice cuando los datos de la tabla ya están clasificados según criterios de ordenación, por ejemplo, cuando se ha usado bcp para copiar datos que ya se han ordenado en una tabla vacía. La velocidad aumenta de forma considerable en las tablas de gran tamaño y es varias veces superior en las tablas de más de un gigabyte. Esta opción puede utilizarse con cualquier otra opción create index sin ningún efecto sobre su funcionamiento.

Si se especifica sorted_data , pero los datos no están ordenados, aparece un mensaje de error y el comando se aborta.

Esta opción agiliza la creación de índices sólo para índices agrupados o índices no agrupados únicos. Sin embargo, la creación de un índice no agrupado y no único tendrá éxito siempre que no haya filas con claves duplicadas. Si existen filas con claves duplicadas, aparece un mensaje de error y el comando se aborta.

Page 27: Consultas Optimizar SQL

Uso de la opción on segment_name

La cláusula on segment_name permite especificar el nombre del segmento de base de datos donde debe crearse el índice. Es posible crear un índice no agrupado en un segmento distinto del de las páginas de datos. Por ejemplo:

create index titleind on titles(title) on seg1

Omisión de índices

El comando drop index se usa para quitar un índice de la base de datos. Su sintaxis es:

drop index table_name . index_name [, table_name . index_name ]...

Cuando se ejecuta este comando, SQL Server quita los índices especificados de la base de datos, dejando libre el espacio de almacenamiento.

Sólo el propietario del índice puede omitirlo. El permiso drop index no puede transferirse a otros usuarios. El comando drop index no puede usarse en ninguna de las tablas del sistema de la base de datos master ni en la base de datos de usuario.

Es posible que quiera omitir un índice si no se usa en la mayoría de las consultas o en ninguna.

Para omitir el índice phone_ind de la tabla friends_etc , el comando es:

drop index friends_etc.phone_ind

Determinación de los índices que existen en una tabla

Para ver los índices que existen en una tabla, es posible usar el procedimiento del sistema sp_helpindex . A continuación se muestra un informe sobre la tabla friends_etc :

sp_helpindex friends_etc index_name index_description index_keys -------------- ---------------------------- ------------- nmind clustered located on default pname, sname

Page 28: Consultas Optimizar SQL

postalcode_ind nonclustered located on default postalcode postalcodeind nonclustered located on default postalcode (3 rows affected, return status = 0)

sp_help también informa sobre los índices de una tabla.

Actualización de estadísticas sobre los índices

El comando update statistics ayuda a SQL Server a tomar las mejores decisiones sobre qué índices debe usar cuando procesa una consulta, proporcionándole información actualizada sobre la distribución de los valores clave de los índices. Este comando debe utilizarse cuando se han añadido, modificado o eliminado grandes cantidades de datos de una columna indexada.

El permiso para ejecutar el comando update statistics está asignado de forma predeterminada al propietario de la tabla, y no es transferible. Su sintaxis es:

update statistics table_name [ index_name ]

Si no se especifica un nombre de índice, el comando actualiza las estadísticas de distribución de todos los índices de la tabla indicada. Si se especifica el nombre de un índice, sólo se actualizan las estadísticas de ese índice.

Se pueden buscar los nombres de los índices con el procedimiento del sistema sp_helpindex . Este procedimiento toma como parámetro un nombre de tabla.

A continuación se muestra cómo enumerar los índices de la tabla authors :

sp_helpindex authors index_name index_description index_keys ---------- ----------------- ------------------ auidind clustered, unique au_id aunmind nonclustered au_lname, au_fname (2 rows affected)

Para actualizar las estadísticas de todos los índices, escriba:

Page 29: Consultas Optimizar SQL

update statistics authors

Para actualizar las estadísticas sólo del índice de la columna au_id , escriba:

update statistics authors auidind

Dado que Transact-SQL no requiere que los nombres de índice sean únicos en una base de datos, se debe indicar el nombre de la tabla a la que está asociado el índice. SQL Server ejecuta update statistics de forma automática cuando se crea un índice en los datos existentes.