soporta - optimizar consultas mysql explain

MySQL no usa el índice con JOIN, WHERE y ORDER

Question

Tenemos dos tablas que se asemejan a una estructura de registro de etiqueta simple de la siguiente manera (en realidad es mucho más compleja, pero esta es la esencia del problema):

tag (A.a) | recordId (A.b)
1         | 1
2         | 1
2         | 2
3         | 2
....

y

recordId (B.b) | recordData (B.c)
1              | 123
2              | 666
3              | 1246

El problema es obtener registros ordenados con una etiqueta específica. La forma obvia de hacerlo es con una simple combinación e índices en (PK) (Aa, Ab), (Ab), (PK) (Bb), (Bb, Bc) como tales:

select A.a, A.b, B.c from A join B on A.b = B.b where a = 44 order by c;

Sin embargo, esto da el resultado desagradable de una clasificación de archivos:

+----+-------------+-------+------+---------------+---------+---------+-----------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key     | key_len | ref       | rows | Extra                                        |
+----+-------------+-------+------+---------------+---------+---------+-----------+------+----------------------------------------------+
|  1 | SIMPLE      | A     | ref  | PRIMARY,b     | PRIMARY | 4       | const     |   94 | Using index; Using temporary; Using filesort | 
|  1 | SIMPLE      | B     | ref  | PRIMARY,b     | b       | 4       | booli.A.b |    1 | Using index                                  | 
+----+-------------+-------+------+---------------+---------+---------+-----------+------+----------------------------------------------+

Usando una "visión materializada" enorme y extremadamente redundante podemos obtener un rendimiento bastante decente, pero esto a expensas de complicar la lógica comercial, algo que nos gustaría evitar, especialmente dado que las tablas A y B ya son MV: s (y están necesario para otras consultas, y de hecho las mismas consultas usando un UNION).

create temporary table C engine=innodb as (select A.a, A.b, B.c from A join B on A.b = B.b);
explain select a, b, c from C where a = 44 order by c;

Complicando aún más la situación es el hecho de que tenemos condicionales en la tabla B, como filtros de rango.

select A.a, A.b, B.c from A join B on A.b = B.b where a = 44 AND B.c > 678 order by c;

Pero estamos seguros de que podemos manejar esto si el problema de archivos se va.

¿Alguien sabe por qué la unión simple en el bloque de código 3 anterior no usará el índice para ordenar y si podemos solucionar el problema de alguna manera sin crear un nuevo MV?

A continuación se muestra la lista completa de SQL que estamos utilizando para las pruebas.

DROP TABLE IF EXISTS A;
DROP TABLE IF EXISTS B;
DROP TABLE IF EXISTS C;
CREATE TEMPORARY TABLE A (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY(a, b), INDEX idx_A_b (b)) ENGINE=INNODB;
CREATE TEMPORARY TABLE B (b INT NOT NULL, c INT NOT NULL, d VARCHAR(5000) NOT NULL DEFAULT '''', PRIMARY KEY(b), INDEX idx_B_c (c), INDEX idx_B_b (b, c)) ENGINE=INNODB;

DELIMITER $$
CREATE PROCEDURE prc_filler(cnt INT)
BEGIN
        DECLARE _cnt INT;
        SET _cnt = 1;
        WHILE _cnt <= cnt DO
                INSERT IGNORE INTO A SELECT RAND()*100, RAND()*10000;
                INSERT IGNORE INTO B SELECT RAND()*10000, RAND()*1000, '''';
                SET _cnt = _cnt + 1;
        END WHILE;
END
$$
DELIMITER ;

START TRANSACTION;
CALL prc_filler(100000);
COMMIT;
DROP PROCEDURE prc_filler;

CREATE TEMPORARY TABLE C ENGINE=INNODB AS (SELECT A.a, A.b, B.c FROM A JOIN B ON A.b = B.b);
ALTER TABLE C ADD (PRIMARY KEY(a, b), INDEX idx_C_a_c (a, c));

EXPLAIN EXTENDED SELECT A.a, A.b, B.c FROM A JOIN B ON A.b = B.b WHERE A.a = 44;
EXPLAIN EXTENDED SELECT A.a, A.b, B.c FROM A JOIN B ON A.b = B.b WHERE 1 ORDER BY B.c;
EXPLAIN EXTENDED SELECT A.a, A.b, B.c FROM A JOIN B ON A.b = B.b where A.a = 44 ORDER BY B.c;
EXPLAIN EXTENDED SELECT a, b, c FROM C WHERE a = 44 ORDER BY c;
-- Added after Quassnois comments
EXPLAIN EXTENDED SELECT A.a, A.b, B.c FROM  B FORCE INDEX (idx_B_c) JOIN A ON A.b = B.b WHERE A.a = 44 ORDER BY B.c;
EXPLAIN EXTENDED SELECT A.a, A.b, B.c FROM A JOIN B ON A.b = B.b WHERE A.a = 44 ORDER BY B.c LIMIT 10;
EXPLAIN EXTENDED SELECT A.a, A.b, B.c FROM  B FORCE INDEX (idx_B_c) JOIN A ON A.b = B.b WHERE A.a = 44 ORDER BY B.c LIMIT 10;

Answer 1

Cuando trato de reproducir esta consulta usando sus scripts:

SELECT  A.a, A.b, B.c
FROM    A
JOIN    B
ON      A.b = B.b
WHERE   a = 44
ORDER BY
        c

, completa en 0.0043 seconds (al instante), devuelve 930 filas y cede este plan:

1, ''SIMPLE'', ''A'', ''ref'', ''PRIMARY'', ''PRIMARY'', ''4'', ''const'', 1610, ''Using index; Using temporary; Using filesort''
1, ''SIMPLE'', ''B'', ''eq_ref'', ''PRIMARY'', ''PRIMARY'', ''4'', ''test.A.b'', 1, ''''

Es bastante eficiente para esa consulta.

Para dicha consulta, no puede usar un solo índice para filtrar y clasificar.

Vea este artículo en mi blog para explicaciones más detalladas:

• Elegir índice

Si espera que su consulta devuelva pocos registros, debe usar el índice en A para filtrar y luego ordenar usando filesort (como hace la consulta anterior).

Si espera que devuelva muchos registros (y los LIMIT ), debe usar el índice para clasificar y luego filtrar:

CREATE INDEX ix_a_b ON a (b);
CREATE INDEX ix_b_c ON b (c)

SELECT  *
FROM    B FORCE INDEX (ix_b_c)
JOIN    A
ON      A.b = B.b
ORDER BY
        b.c
LIMIT 10;

1, ''SIMPLE'', ''B'', ''index'', '''', ''ix_b_c'', ''4'', '''', 2, ''Using index''
1, ''SIMPLE'', ''A'', ''ref'', ''ix_a_b'', ''ix_a_b'', ''4'', ''test.B.b'', 4, ''Using index''

Answer 2

select Aa, Ab, Bc from A join B on Ab = Bb where a = 44 order by c;

Si alias las columnas, ¿eso ayuda? Ejemplo:

 SELECT 
 T1.a AS colA, 
 T2.b AS colB, 
 T2.c AS colC 
 FROM A AS T1 
 JOIN B AS T2 
 ON (T1.b = T2.b) 
 WHERE 
 T1.a = 44 
 ORDER BY colC;

Los únicos cambios que hice fueron:

• Puse las condiciones de unión entre paréntesis
• Las condiciones de unión y donde las condiciones se basan en columnas de tabla
• La condición ORDER BY se basa en la columna de tabla resultante
• Aliasé las columnas de la tabla de resultados y las tablas consultadas para (con suerte) dejarlo más claro cuando estaba usando uno u otro (y más claro para el servidor. Olvidas referirte a tus columnas en dos lugares en tu consulta original).

Sé que sus datos reales son más complejos, pero supongo que proporcionó una versión simple de la consulta porque el problema está en ese nivel simple.