resueltos - Índice de SQL Server-¿Alguna mejora para las consultas LIKE?

plan de ejecución y optimización de consultas sql server 2008 r2

Question

Tenemos una consulta que se ejecuta en una tabla bastante grande que, lamentablemente, necesita usar LIKE ''% ABC%'' en un par de campos varchar para que el usuario pueda buscar nombres parciales, etc. SQL Server 2005

¿La adición de un índice en estos campos varchar ayudaría a cualquiera en términos de seleccionar el rendimiento de la consulta cuando se usa LIKE o básicamente ignora los índices y realiza un análisis completo en esos casos?

¿Alguna otra forma posible de mejorar el rendimiento al usar LIKE?

Answer 1

Al igual que ''% ABC%'' siempre se realizará un escaneo completo de la tabla. No hay manera de evitar eso.

Tienes un par de enfoques alternativos. En primer lugar, la búsqueda de texto completo, está realmente diseñada para este tipo de problema, así que primero lo vería.

Alternativamente, en algunas circunstancias, podría ser apropiado desnormalizar los datos y preprocesar los campos de destino en tokens apropiados, y luego agregar estos posibles términos de búsqueda en una tabla de búsqueda separada de muchas. Por ejemplo, si mis datos siempre constaban de un campo que contenía el patrón ''AAA / BBB / CCC'' y mis usuarios buscaban en BBB, lo señalaría en la inserción / actualización (y eliminaré en la eliminación). Este también sería uno de esos casos en los que se preferiría usar desencadenantes, en lugar del código de aplicación.

Debo enfatizar que esta no es realmente una técnica óptima y solo se debe utilizar si los datos son adecuados para el enfoque y, por alguna razón, no desea utilizar la búsqueda de texto completo (y el rendimiento de la base de datos en el análisis similar realmente es inaceptable). También es probable que produzca dolores de cabeza de mantenimiento en el futuro.

Answer 2

La única otra manera (que no sea el uso de la indexación de texto completo) en la que podría mejorar el rendimiento es usar "LIKE ABC%"; no agregue el comodín en ambos extremos de su término de búsqueda; en ese caso, un índice podría funcionar.

Si sus requisitos son tales que tiene que tener comodines en ambos extremos de su término de búsqueda, no tiene suerte ...

Bagazo

Answer 3

Puede ver mejoras en el rendimiento agregando índice (es), depende mucho de las características específicas :)

¿Cuánto del tamaño total de la fila son sus columnas predicadas? ¿Cuántas filas espera que coincidan? ¿Necesita devolver todas las filas que coincidan con el predicado, o solo las filas top 1 o top n?

Si está buscando valores con alta selectividad / unicidad (por lo tanto, pocas filas para regresar), y las columnas predicadas son una porción pequeña de todo el tamaño de la fila, un índice podría ser bastante útil. Seguirá siendo un escaneo, pero su índice se ajustará a más filas por página que la tabla de origen.

Aquí hay un ejemplo donde el tamaño total de la fila es mucho mayor que el tamaño de la columna para buscar:

create table t1 (v1 varchar(100), b1 varbinary(8000))
go
--add 10k rows of filler
insert t1 values (''abc123def'', cast(replicate(''a'', 8000) as varbinary(8000)))
go 10000
--add 1 row to find
insert t1 values (''abc456def'', cast(replicate(''a'', 8000) as varbinary(8000)))
go
set statistics io on
go
select * from t1 where v1 like ''%456%''
--shows 10001 logical reads
--create index that only contains the column(s) to search across
create index t1i1 on t1(v1)
go
select * from t1 where v1 like ''%456%''
--or can force to
--shows 37 logical reads

Si observa el plan de ejecución real, puede ver que el motor analizó el índice y realizó una búsqueda de marcadores en la fila correspondiente. O puede decirle al optimizador directamente que use el índice, si no hubiera decidido usar este plan por sí solo: seleccione * desde t1 con (índice (t1i1)) donde v1 tiene gusto de ''% 456%''

Si tiene un montón de columnas para buscar en solo unas pocas que son altamente selectivas, puede crear múltiples índices y utilizar un enfoque de reducción. Por ejemplo, primero determine un conjunto de ID (o lo que sea su PK) a partir de su índice altamente selectivo, luego busque sus columnas menos selectivas con un filtro contra ese pequeño conjunto de PK.

Si siempre necesita devolver un gran conjunto de filas, es casi seguro que sería mejor con un escaneo de tablas.

Por lo tanto, las posibles optimizaciones dependen mucho de las características específicas de la definición de su tabla y de la selectividad de sus datos.

HTH! -Adrian

Answer 4

Solo si agrega la búsqueda de texto completo a esas columnas y usa las capacidades de consulta de texto completo de SQL Server.

De lo contrario, no, un índice no ayudará.

Answer 5

crear estadísticas en esa columna. sql srever 2005 ha optimizado la búsqueda de cadenas para que pueda beneficiarse de eso.