top - SQL ''like'' vs ''='' performance

top sql performance

Question

Esta pregunta bordea lo que me pregunto, pero las respuestas no lo abordan exactamente.

Parecería que en general ''='' es más rápido que ''como'' cuando se usan comodines. Esta parece ser la sabiduría convencional. Sin embargo, supongamos que tengo una columna que contiene un número limitado de diferentes identificadores varchar fijos y codificados, y quiero seleccionar todas las filas que coincidan con uno de ellos:

select * from table where value like ''abc%''

y

select * from table where value = ''abcdefghijklmn''

''Me gusta'' solo debería probar los primeros tres caracteres para encontrar una coincidencia, mientras que ''='' debe comparar toda la cadena. En este caso, me parecería que ''me gusta'' tendría una ventaja, en igualdad de condiciones.

Esto está pensado como una pregunta académica general, por lo que no debería importar qué DB, pero surgió usando SQL Server 2005.

Answer 1

Lo primero es lo primero ,

ellos no son siempre iguales

    select ''Hello'' from dual where ''Hello  '' like ''Hello'';

    select ''Hello'' from dual where ''Hello  '' =  ''Hello'';

Cuando las cosas no siempre son iguales, hablar sobre su desempeño no es tan relevante.

Si está trabajando en cadenas y solo en variables char, entonces puede hablar sobre el rendimiento. Pero no use like y "=" como generalmente intercambiable.

Como habrías visto en muchos mensajes (arriba y otras preguntas), en los casos en que son iguales, el rendimiento de like es más lento debido a la coincidencia de patrones (colación)

Answer 2

Si el value no está indexado, ambos resultan en un escaneo de tabla. La diferencia de rendimiento en este escenario será insignificante.

Si el value está indexado, como Daniel señala en su comentario, el = dará como resultado una búsqueda de índice que es el rendimiento O (log N). El LIKE (muy probablemente, dependiendo de qué tan selectivo sea) dará como resultado un escaneo parcial del índice >= ''abc'' y < ''abd'' que requerirá más esfuerzo que el = .

Tenga en cuenta que estoy hablando de SQL Server aquí - no todos los DBMS serán agradables con LIKE.

Answer 3

Tal vez está buscando en Búsqueda de texto completo .

A diferencia de la búsqueda de texto completo, el predicado LIKE Transact-SQL solo funciona en patrones de caracteres. Además, no puede usar el predicado LIKE para consultar datos binarios formateados. Además, una consulta LIKE frente a una gran cantidad de datos de texto no estructurados es mucho más lenta que una consulta de texto completo equivalente con los mismos datos . Una consulta LIKE contra millones de filas de datos de texto puede demorar unos minutos en regresar; mientras que una consulta de texto completo puede tomar segundos o menos contra los mismos datos, dependiendo del número de filas que se devuelven.

Answer 4

También debe tener en cuenta que al usar like , algunos sabores sql ignorarán los índices, y eso matará el rendimiento. Esto es especialmente cierto si no usa el patrón "comienza con" como en su ejemplo.

Deberías mirar el plan de ejecución de la consulta y ver qué está haciendo, adivina lo menos posible.

Dicho esto, el patrón "comienza con" puede y está optimizado en el servidor sql. Utilizará el índice de la tabla. EF 4.0 cambió a like para StartsWith por esta misma razón.

Answer 5

Un ejemplo personal usando mysql 5.5: tuve una unión interna entre 2 tablas, una de 3 millones de filas y una de 10 mil filas.

Al usar un me gusta en un índice como el que se muestra a continuación (sin comodines), tardó unos 30 segundos:

where login like ''12345678''

usando ''explicar'' obtengo:

Cuando se usa un ''='' en la misma consulta, tomó aproximadamente 0.1 segundos:

where login =''600009''

Usando ''explicar'' obtengo:

Como puede ver, las cosas anularon por completo la búsqueda de índice, por lo que la consulta tomó 300 veces más tiempo.

Answer 6

Usted está haciendo la pregunta incorrecta. En las bases de datos, no es el rendimiento del operador lo que importa, siempre es la SARGability de SARGability de la expresión y la coverability de coverability de la consulta general. El rendimiento del operador en sí es en gran medida irrelevante.

Entonces, ¿cómo LIKE y = compara en términos de SARGability? LIKE , cuando se usa con una expresión que no comienza con una constante (por ejemplo, cuando se usa LIKE ''%something'' ) es, por definición, no SARGabale. ¿Pero eso hace = o LIKE ''something%'' gusta LIKE ''something%'' SARGable? No. Al igual que con cualquier pregunta sobre el rendimiento de SQL, la respuesta no está en la consulta del texto, sino en el esquema desplegado. Estas expresiones pueden ser SARGables si existe un índice para satisfacerlas.

Entonces, a decir verdad, hay pequeñas diferencias entre = y LIKE . Pero preguntar si un operador u otro operador es ''más rápido'' en SQL es como preguntar ''¿Qué pasa más rápido, un auto rojo o un auto azul?''. Deberías hacer preguntas sobre el tamaño del motor y el peso del vehículo, no sobre el color ... Para abordar las preguntas sobre la optimización de tablas relacionales, el lugar para mirar son tus índices y tus expresiones en la cláusula WHERE (y otras cláusulas, pero por lo general comienza con DONDE).

Answer 7

Ver http://myitforum.com/cs2/blogs/jnelson/archive/2007/11/16/108354.aspx

Cita desde allí:

las reglas para el uso del índice con LIKE son muy similares a esto:

• Si su criterio de filtro usa equals = y el campo está indexado, lo más probable es que use un INDEX / CLUSTERED INDEX SEEK

• Si su criterio de filtro utiliza LIKE, sin comodines (como si tuviera un parámetro en un informe web que PODRÍA tener un% pero en su lugar utiliza la cadena completa), es casi tan probable como # 1 usar el índice. El costo incrementado es casi nada.

• Si su criterio de filtro utiliza LIKE, pero con un comodín al principio (como en Name0 LIKE ''% UTER'') es mucho menos probable que use el índice, pero al menos puede realizar un INDEX SCAN en un rango completo o parcial de El índice.

• SIN EMBARGO, si su criterio de filtro utiliza LIKE, pero comienza con STRING FIRST y tiene comodines en algún lugar DESPUÉS de eso (como en Name0 LIKE ''COMP% ER''), SQL puede usar un INDEX SEEK para buscar rápidamente las filas que tienen el mismo primero comenzando caracteres, y luego mira a través de esas filas para una coincidencia exacta.

(También tenga en cuenta que el motor de SQL aún puede no usar un índice de la manera que espera, dependiendo de qué más esté pasando en su consulta y a qué tablas se está sumando. El motor de SQL se reserva el derecho de reescribir su consultar un poco para obtener los datos de una manera que considere más eficiente y que puedan incluir una EXPLORACIÓN DE ÍNDICES en lugar de una BÚSQUEDA DE ÍNDICES)

Answer 8

Es una diferencia medible.

Ejecute lo siguiente:

Create Table #TempTester (id int, col1 varchar(20), value varchar(20))
go

INSERT INTO #TempTester (id, col1, value)
VALUES
(1, ''this is #1'', ''abcdefghij'')
GO

INSERT INTO #TempTester (id, col1, value)
VALUES
(2, ''this is #2'', ''foob''),
(3, ''this is #3'', ''abdefghic''),
(4, ''this is #4'', ''other''),
(5, ''this is #5'', ''zyx''),
(6, ''this is #6'', ''zyx''),
(7, ''this is #7'', ''zyx''),
(8, ''this is #8'', ''klm''),
(9, ''this is #9'', ''klm''),
(10, ''this is #10'', ''zyx'')
GO 10000

CREATE CLUSTERED INDEX ixId ON #TempTester(id)CREATE CLUSTERED INDEX ixId ON #TempTester(id)

CREATE NONCLUSTERED INDEX ixTesting ON #TempTester(value)

Entonces:

SET SHOWPLAN_XML ON

Entonces:

SELECT * FROM #TempTester WHERE value LIKE ''abc%''

SELECT * FROM #TempTester WHERE value = ''abcdefghij''

El plan de ejecución resultante le muestra que el costo de la primera operación, la comparación LIKE , es aproximadamente 10 veces más costosa que la comparación = .

Si puede usar una = comparación, hágalo.