tuning - optimize mysql centos

MySQL no usa índices("Usando el archivo") cuando usa ORDER BY

Question

Estoy teniendo problemas de rendimiento bastante importantes debido al uso de las declaraciones "ORDER BY" en mi código SQL.

Todo está bien siempre y cuando no esté usando las instrucciones ORDER BY en el SQL. Sin embargo, una vez que introduzco ORDER BY: s en el código SQL, todo se ralentiza drásticamente debido a la falta de indexación correcta. Se podría suponer que arreglar esto sería algo trivial, pero a juzgar por las discusiones del foro, etc., este parece ser un problema bastante común que aún no he visto una respuesta definitiva y concisa a esta pregunta.

Pregunta: Dada la siguiente tabla ...

CREATE TABLE values_table ( id int(11) NOT NULL auto_increment, ... value1 int(10) unsigned NOT NULL default ''0'', value2 int(11) NOT NULL default ''0'', PRIMARY KEY (id), KEY value1 (value1), KEY value2 (value2), ) ENGINE=MyISAM AUTO_INCREMENT=2364641 DEFAULT CHARSET=utf8;

... ¿cómo creo los índices que se usarán al consultar la tabla para un rango de value1 mientras se ordena el valor de value2 ?

Actualmente, la recuperación está bien cuando NO se usa la cláusula ORDER BY.

Vea el siguiente resultado de EXPLAIN QUERY:

OK, when NOT using ORDER BY: EXPLAIN select ... from values_table this_ where this_.value1 between 12345678 and 12349999 limit 10; +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+ | 1 | SIMPLE | this_ | range | value1 | value1 | 4 | NULL | 3303 | Using where | +----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+

However, when using ORDER BY I get "Using filesort": EXPLAIN select ... from values_table this_ where this_.value1 between 12345678 and 12349999 order by this_.value2 asc limit 10; +----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------------+ | 1 | SIMPLE | this_ | range | value1 | value1 | 4 | NULL | 3303 | Using where; Using filesort | +----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------------+

Alguna información adicional sobre el contenido de la tabla:

SELECT MIN(value1), MAX(value1) FROM values_table; +---------------+---------------+ | MIN(value1) | MAX(value2) | +---------------+---------------+ | 0 | 4294967295 | +---------------+---------------+ ... SELECT MIN(value2), MAX(value2) FROM values_table; +---------------+---------------+ | MIN(value2) | MAX(value2) | +---------------+---------------+ | 1 | 953359 | +---------------+---------------+

Por favor, avíseme si se necesita más información para responder la pregunta.

¡Muchas gracias por adelantado!

Actualización # 1: Agregar un nuevo índice compuesto ( ALTER TABLE values_table ADD INDEX (value1, value2); ) no resuelve el problema. Seguirá obteniendo "Utilizando filesort" después de agregar dicho índice.

Actualización # 2: una restricción que no mencioné en mi pregunta es que prefiero cambiar la estructura de la tabla (por ejemplo, agregar índices, etc.) que cambiar las consultas SQL utilizadas. Las consultas SQL se generan automáticamente utilizando Hibernate, por lo que considere las más o menos fijas.

Answer 1

Me parece que tiene dos claves totalmente independientes , una para value1 y otra para value2.

Por lo tanto, cuando utiliza la clave value1 para recuperar, los registros no se devuelven necesariamente en el orden de value2, por lo que deben ordenarse. Esto es aún mejor que un análisis completo de la tabla, ya que solo está ordenando los registros que satisfacen su cláusula "where value1".

Creo que (si esto es posible en MySQL), una clave compuesta en (valor1, valor2) resolvería esto.

Tratar:

CREATE TABLE values_table (
    id int(11) NOT NULL auto_increment,
    ...
    value1 int(10) unsigned NOT NULL default ''0'',
    value2 int(11) NOT NULL default ''0'',
    PRIMARY KEY  (id),
    KEY value1 (value1),
    KEY value1and2 (value1,value2),
) ENGINE=MyISAM AUTO_INCREMENT=2364641 DEFAULT CHARSET=utf8;

(o la ALTER TABLE equivalente), asumiendo que esa es la sintaxis correcta en MySQL para una clave compuesta.

En todas las bases de datos que conozco (y tengo que admitir que MySQL no es una de ellas), eso haría que el motor de base de datos seleccionara la clave value1and2 para recuperar las filas y que ya estuvieran ordenadas por orden value2-within-value1, No necesitaría un tipo de archivo.

Todavía puede mantener la clave value2 si lo necesita.

Answer 2

No puede usar un índice en este caso, ya que usa una condición de filtrado de RANGE .

Si usaras algo como:

SELECT  *
FROM    values_table this_
WHERE   this_.value1 = @value
ORDER BY
        value2
LIMIT 10

, luego, la creación de un índice compuesto en (VALUE1, VALUE2) se usaría tanto para el filtrado como para el pedido.

Pero utiliza una condición de rango, por eso tendrá que realizar el pedido de todos modos.

Su índice compuesto se verá así:

value1 value2 ----- ------ 1 10 1 20 1 30 1 40 1 50 1 60 2 10 2 20 2 30 3 10 3 20 3 30 3 40

, y si selecciona 1 y 2 en value1 , aún no obtiene un conjunto ordenado de value2 .

Si su índice en value2 no es muy selectivo (es decir, no hay muchos DISTINCT value2 en la tabla), puede intentar:

CREATE INDEX ix_table_value2_value1 ON mytable (value2, value1)

/* Note the order, it''s important */    

SELECT  *
FROM    (
        SELECT  DISTINCT value2
        FROM    mytable
        ORDER BY
                value2
        ) q,
        mytable m
WHERE   m.value2 >= q.value2
        AND m.value2 <= q.value2
        AND m.value1 BETWEEN 13123123 AND 123123123

Esto se denomina método de acceso SKIP SCAN . MySQL no lo admite directamente, pero se puede emular de esta manera.

En este caso, se utilizará el acceso RANGE , pero probablemente no obtendrá ningún beneficio de rendimiento a menos que DISTINCT value2 represente menos de aproximadamente el 1% de las filas.

Note el uso de:

m.value2 >= q.value2
AND m.value2 <= q.value2

en lugar de

m.value2 = q.value2

Esto hace que MySQL realice una comprobación de RANGE en cada bucle.