MySQL
La optimización del rendimiento

Sintaxis

• No utilice DISTINCT y GROUP BY en el mismo SELECT.

• No pagines a través de OFFSET, "recuerda dónde lo dejaste".

• DONDE (a, b) = (22,33) no se optimiza en absoluto.

• Diga explícitamente ALL o DISTINCT después de UNION: le recuerda que debe elegir entre el ALL más rápido o el DISTINCT más lento.

• No utilice SELECT *, especialmente si tiene columnas TEXT o BLOB que no necesita. Hay sobrecarga en tablas tmp y transmisión.

• Es más rápido cuando GROUP BY y ORDER BY pueden tener exactamente la misma lista.

• No uses el ÍNDICE DE FUERZA; Puede ayudar hoy, pero probablemente dolerá mañana.

Observaciones

Agregue el índice correcto

Este es un tema enorme, pero también es el problema más importante del "rendimiento".

La lección principal para un principiante es aprender de los índices "compuestos". Aquí hay un ejemplo rápido:

INDEX(last_name, first_name)

Es excelente para estos:

WHERE last_name = '...'
WHERE first_name = '...' AND last_name = '...'   -- (order in WHERE does not matter)

pero no para

WHERE first_name = '...'   -- order in INDEX _does_ matter
WHERE last_name = '...' OR first_name = '...'   -- "OR" is a killer

Establecer el caché correctamente

innodb_buffer_pool_size debería ser aproximadamente el 70% de la RAM disponible.

Evitar construcciones ineficientes.

x IN ( SELECT ... )

convertirse en un JOIN

Cuando sea posible, evite OR .

No "esconda" una columna indexada en una función, como la WHERE DATE(x) = ... ; reformular como WHERE x = ...

Por lo general, puede evitar WHERE LCASE(name1) = LCASE(name2) teniendo una intercalación adecuada.

No use OFFSET para "paginación", en lugar de eso, 'recuerde donde lo dejó'.

Evite SELECT * ... (a menos que esté depurando).

Nota para Maria Deleva, Barranka, Batsu: Este es un marcador de posición; Por favor, elimine estos elementos a medida que construye ejemplos a gran escala. Después de que hayas hecho lo que puedas, me moveré para elaborar el resto y / o lanzarlos.

Negativos

Aquí hay algunas cosas que probablemente no ayuden al rendimiento. Se derivan de información desactualizada y / o de ingenuidad.

• InnoDB ha mejorado hasta el punto de que es poco probable que MyISAM sea mejor.
• PARTITIONing rara vez proporciona beneficios de rendimiento; Incluso puede dañar el rendimiento.
• La configuración de query_cache_size mayor que 100M generalmente afectará el rendimiento.
• El aumento de muchos valores en my.cnf puede llevar a un "intercambio", que es un grave problema de rendimiento.
• Los "índices de prefijo" (como INDEX(foo(20)) ) son generalmente inútiles.
• OPTIMIZE TABLE es casi siempre inútil. (Y se trata de bloquear la mesa.)

Tener un índice

Lo más importante para acelerar una consulta en cualquier tabla no pequeña es tener un índice adecuado.

WHERE a = 12  --> INDEX(a)
WHERE a > 12  --> INDEX(a)

WHERE a = 12 AND b > 78  --> INDEX(a,b) is more useful than INDEX(b,a)
WHERE a > 12 AND b > 78  --> INDEX(a) or INDEX(b); no way to handle both ranges

ORDER BY x  --> INDEX(x)
ORDER BY x, y  --> INDEX(x,y) in that order
ORDER BY x DESC, y ASC  --> No index helps - because of mixing ASC and DESC

No te escondas en función

Un error común es ocultar una columna indexada dentro de una llamada de función. Por ejemplo, esto no puede ser ayudado por un índice:

WHERE DATE(dt) = '2000-01-01'

En cambio, dado el INDEX(dt) , estos pueden usar el índice:

WHERE dt = '2000-01-01'  -- if `dt` is datatype `DATE`

Esto funciona para DATE , DATETIME , TIMESTAMP e incluso DATETIME(6) (microsegundos):

WHERE dt >= '2000-01-01'
  AND dt  < '2000-01-01' + INTERVAL 1 DAY

O

En general OR mata la optimización.

WHERE a = 12 OR b = 78

no puede usar INDEX(a,b) , y puede o no puede usar INDEX(a), INDEX(b) través de "fusión de índice". La fusión de índices es mejor que nada, pero solo a medias.

WHERE x = 3 OR x = 5

se convierte en

WHERE x IN (3, 5)

que puede utilizar un índice con x en ella.

Subconsultas

Las subconsultas vienen en varios sabores y tienen un potencial de optimización diferente. Primero, tenga en cuenta que las subconsultas pueden ser "correlacionadas" o "no correlacionadas". Correlacionado significa que dependen de algún valor externo a la subconsulta. Esto generalmente implica que la subconsulta debe ser reevaluada para cada valor externo.

Esta subconsulta correlacionada es a menudo bastante buena. Nota: Debe devolver como máximo 1 valor. A menudo es útil como una alternativa, aunque no necesariamente más rápida que una LEFT JOIN .

SELECT a, b, ( SELECT ... FROM t WHERE t.x = u.x ) AS c
    FROM u ...
SELECT a, b, ( SELECT MAX(x) ... ) AS c
    FROM u ...
SELECT a, b, ( SELECT x FROM t ORDER BY ... LIMIT 1 ) AS c
    FROM u ...

Esto generalmente no está correlacionado

SELECT ...
    FROM ( SELECT ... ) AS a
    JOIN b ON ...

Notas sobre el FROM-SELECT :

• Si vuelve 1 fila, genial.
• Un buen paradigma (nuevamente "1 fila") es que la subconsulta sea ( SELECT @n := 0 ) , inicializando así una `@variable para uso en el resto o la consulta.
• Si devuelve muchas filas y la JOIN también es ( SELECT ... ) con muchas filas, la eficiencia puede ser terrible. Pre-5.6, no había índice, por lo que se convirtió en una CROSS JOIN ; 5.6+ implica deducir el mejor índice en las tablas temporales y luego generarlo, solo para desecharlo cuando termine con SELECT .

ÚNETE + GRUPO POR

Un problema común que conduce a una consulta ineficiente es algo como esto:

SELECT ...
    FROM a
    JOIN b  ON ...
    WHERE ...
    GROUP BY a.id

Primero, el JOIN expande el número de filas; luego el GROUP BY lo reduce de nuevo el número de filas en a .

Puede que no haya ninguna buena elección para resolver este problema de explosión e implosión. Una opción posible es convertir el JOIN en una subconsulta correlacionada en SELECT . Esto también elimina el GROUP BY .