de Martin LE TARNEC.

Indexar los filtros LIKE


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
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' (no UPPER). Parece que DB2 LUW 10.5 no puede usar un predicado de acceso con un LIKE sobre 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 START y STOP que 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

Para la base de datos PostgreSQL, se necesitaría especificar un operador de clase (p.ej., varchar_pattern_ops) con el fin de usar las expresiones como predicados de acceso. Referirse a Clases y famiilias de operadores€ dentro de la documentación para más detalle.

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 7.3 Columnas combinadas se explica eso con detalle.

Etiquetar las expresiones de texto completo con LIKE

Si usas el operador LIKE para una búsqueda de texto completo, se debe separar el comodín del criterio de búsqueda:

WHERE text_column LIKE '%' || ? || '%'
El comodín está escrito directamente dentro de la sentencia SQL, pero se usa una variable Bind como criterio de búsqueda. La expresión final LIKE está construida por la misma base de datos usando el operador de concatenación de caracteres || (Oracle, PostgreSQL). Aunque se usa una variable Bind, la expresión final LIKE empezará siempre con un comodín. Desafortunadamente, las bases de datos no lo identifican.

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 Index Filter Predicates Used Intentionally). Si no existe otro camino de acceso, se podría utilizar una de las soluciones propietarias de los índices de texto completo.

DB2

DB2 soporta la palabra clave contains. Ver "Tutorial DB2 de búsqueda de texto en IBM developerWorks.

MySQL

MySQL ofrece las palabras claves match y against para 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.

Acerca del autor

Foto de Markus Winand

Markus Winand enseña eficientemente SQL, en casa y online. Minimiza el tiempo de desarrollo utilizando moderno SQL y optimiza el tiempo de ejecución con indexación inteligente. Para ello también ha publicado el libro SQL Performance Explained.

“Use The Index, Luke” de Markus Winand se halla bajo licencia Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Aspectos legales | Contacto | SIN GARANTÍA | Marcas | Privacy | CC-BY-NC-ND 3.0 licencia