El operador SQL LIKE puede dar lugar a un
comportamiento inesperado en el rendimiento porque algunos criterios de
búsqueda previenen de manera eficiente el uso del índice. Eso significa
que existen criterios de búsqueda que pueden ser muy bien indexados,
pero otros no lo permiten. Es la posición del comodín el cual provoca
la diferencia.
El siguiente ejemplo usa el comodín % en medio del
criterio de búsqueda:
SELECT first_name, last_name, date_of_birth
FROM employees
WHERE UPPER(last_name) LIKE 'WIN%D'- Db2 (LUW)
Explain Plan ---------------------------------------------------- ID | Operation | Rows | Cost 1 | RETURN | | 13 2 | FETCH EMPLOYEES | 1 of 1 (100.00%) | 13 3 | IXSCAN EMP_NAME | 1 of 10000 ( .01%) | 6 Predicate Information 3 - START ('WIN.................................... STOP (Q1.LAST_NAME <= 'WIN.................... SARG (Q1.LAST_NAME LIKE 'WIN%D')Para este ejemplo, la sentencia se cambió con el fin de leer
WHERE last_name LIKE 'WIN%D'(noUPPER). Parece que Db2 (LUW) 10.5 no puede usar un predicado de acceso con unLIKEsobre un índice basado en función (hacer un escaneo completo del índice seria lo mejor).De otra manera, Db2 sobresale en que muestra claramente las condiciones
STARTySTOPque representan la parte antes del primer comodín, pero también evidencia que el molde completo está aplicado como filtro de predicado.- MySQL
+----+-----------+-------+----------+---------+------+-------------+ | id | table | type | key | key_len | rows | Extra | +----+-----------+-------+----------+---------+------+-------------+ | 1 | employees | range | emp_name | 767 | 2 | Using where | +----+-----------+-------+----------+---------+------+-------------+- Oracle
--------------------------------------------------------------- |Id | Operation | Name | Rows | Cost | --------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 4 | | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 1 | 4 | |*2 | INDEX RANGE SCAN | EMP_UP_NAME | 1 | 2 | --------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access(UPPER("LAST_NAME") LIKE 'WIN%D') filter(UPPER("LAST_NAME") LIKE 'WIN%D')- PostgreSQL
QUERY PLAN ---------------------------------------------------------- Index Scan using emp_up_name on employees (cost=0.01..8.29 rows=1 width=17) Index Cond: (upper((last_name)::text) ~>=~ 'WIN'::text) AND (upper((last_name)::text) ~<~ 'WIO'::text) Filter: (upper((last_name)::text) ~~ 'WIN%D'::text)
Los filtros LIKE solamente pueden usar los caracteres
antes del primer comodín durante su recorrido a
través del árbol. Los caracteres restantes son solamente filtros de
predicados lo que no reducen el rango escaneado dentro del índice. Una
única expresión LIKE puede contener dos tipos de
predicados: (1) la parte antes del primer comodín como acceso de
predicado; (2) los otros caracteres como filtros de predicados.
Atención
El filtro LIKE lee uno por uno los caracteres mientras la intercalación puede atender múltiples caracteres a la vez como una sola operación de ordenación. Algunas intercalaciones impiden usar los índices con el filtro LIKE. Para obtener más detalles, pueden leer “Indexing LIKE in PostgreSQL and Oracle” escrito por Laurenz Albe.
El prefijo más selectivo antes del primer comodín es, el que se
convertirá en el rango escaneado más pequeño dentro del índice. Eso, uno
a uno, hace la búsqueda (“LOOKUP”) del índice más rápido. La Figura 2.4 ilustra la relación
usando tres expresiones LIKE diferentes. Las tres
seleccionan el mismo registro, pero el rango escaneado dentro del
índice, y su rendimiento, es muy diferente.
Figura 2.4 Varias búsquedas LIKE
La primera expresión tiene dos caracteres antes del comodín.
Limitan el rango escaneado dentro del índice a 18 registros. Solamente
uno de ellos coincide completamente con la expresión LIKE,
los otros 17 serán devueltos pero descartados. La segunda expresión
tiene un prefijo largo que reduce el rango escaneado dentro del índice a
2 registros. Con esta expresión, la base de datos lee solamente un
registro adicional, lo que no es relevante para el resultado. La última
expresión no tiene predicados de filtro completo: la base de datos
lee el registro que coincide completamente con la expresión
LIKE.
Importante
Solamente la parte anterior al comodín sirve como predicado de acceso.
Los caracteres restantes no reducen el rango escaneado dentro del índice. Los que no coinciden sólo quedan descartados del resultado final
El caso contrario es también posible: una expresión
LIKE que empieza por un comodín. Como la expresión
LIKE no sirve como un acceso de predicado, la base de datos
tiene que escanear la tabla entera si no existen otras condiciones para
proveer un predicado de acceso.
Sugerencia
Evitar las expresiones LIKE
con el comodín como primer renglón (p.ej.,
'%TERM').
La posición del comodín afecta al uso de los índices (al menos en teoría). En realidad, el optimizador crea un plan de ejecución cuando el criterio de búsqueda se proporciona a través de variables Bind. En este caso, el optimizador tiene que adivinar, sin importar, si la mayoría de las ejecuciones tendrán como primer renglón el comodín o no.
La mayoría de las bases de datos asumen que no existe
un comodín como primer renglón cuando se optimiza la condición
LIKE con una variable Bind, pero esta suposición es
equivocada si la expresión LIKE es usada para una búsqueda
de texto completo. Desafortunadamente, no es la manera directa
de indicar que una condición LIKE es
búsqueda de texto completo. El apartado titulado “Etiquetar las expresiones de texto completo con
LIKE”
muestra qué hacer cuando no funciona. Especificar el criterio de
búsqueda sin variable Bind es la solución más evidente, pero incrementa
la sobre carga de optimización y abre la puerta para una inyección de
código SQL. Una solución efectiva, pero también segura y portátil, es
impedir deliberadamente la condición LIKE. En la sección
“Columnas combinadas” se explica eso con
detalle.
Para la base de datos PostgreSQL, el problema es diferente porque
PostgreSQL asume que existe un comodín como primer
renglón cuando se usa una variable Bind con una expresión
LIKE. En este caso, PostgreSQL no podrá usar índices. La
única manera de obtener un acceso por el índice para una expresión
LIKE es hacer visible el criterio de búsqueda para el
optimizador. Si no se pueden usar las variables Bind pero se pone
directamente el criterio de búsqueda dentro de la sentencia SQL, ¡se
tendrá que tomar otras precauciones ante los ataques de inyección
SQL!
Aunque la base de datos optimice el plan de ejecución para
un comodín como primer reglón, se puede todavía obtener un rendimiento
insuficiente. Por otra parte, es posible usar un filtro where para tener acceso a los datos de manera
eficiente (en este caso ver también “Filtros de predicados usados intencionalmente sobre índices”). Si no existe otro camino de
acceso, se podría utilizar una de las soluciones propietarias de los
índices de texto completo.
- Db2 (LUW)
Db2 soporta la palabra clave
contains. Ver "Search functions for Db2 Text Search.- MySQL
MySQL ofrece las palabras claves
matchyagainstpara las búsquedas de texto completo. Desde la aparición de MySQL 5.6, se pueden crear índices sobre texto completo para tablas InnoDB; anteriormente, estaba disponible solamente para tablas MyISAM. Ver “Funciones para la búsqueda de texto completo” dentro de la documentación MySQL.- Oracle
La base de datos Oracle ofrece la palabra clave
contains. Ver la “Guía para los desarrolladores de aplicaciones texto.”- PostgreSQL
PostgreSQL ofrece el operador
@@para implementar las búsquedas de texto completo. Ver “Búsqueda de texto completo” en la documentación PostgreSQL .Otra alternativa es usar la extensión WildSpeed para optimizar directamente las expresiones
LIKE. La extensión almacena el texto con todas sus rotaciones, así que cada carácter está una vez al principio. Eso significa que el texto indexado no se almacena solamente una vez, sino, varias veces tantas como existen caracteres dentro de la cadena de caracteres lo que requiere mucho espacio.- SQL Server
SQL Server ofrece la palabra clave
contains. Ver “búsqueda de texto completo” dentro de la documentación de SQL Server.
Piénsalo
¿Cómo se puede indexar una búsqueda LIKE si se
tiene solamente un comodín al inicio del criterio de la búsqueda
('%TERM')?
Si te gusta mi manera de explicar, te encantará mi libro.
