Consulta optimizada de MySQL e índice para exclusión
Consulta optimizada de MySQL con índice de exclusión
En el caso de una selección en una tabla de alto volumen con un criterio de selección que excluye resultados, ¿cuáles son las posibles alternativas?
Por ejemplo, con la siguiente tabla:
+—-+—+—+—-+—-+
| id | A | B | C | D |
+—-+—+—+—-+—-+
| 1 | a | b | c | d |
| 2 | a | b | c | d |
| 3 | a | b | c1 | d1 |
| 4 | a | b | c2 | d |
| 5 | a | b | c | d2 |
| 6 | a | b | c | d2 |
+—-+—+—+—-+—-+
Me gustaría seleccionar todas las tuplas (C, D) donde A=a y B=b y (C!=c o D!=d)
SELECT C,D FROM my_table WHERE A=a AND B=b AND (C!=c OR D!=d) GROUP BY C,D;
Resultado esperado:
(c1,d1)
(c2,d)
(c,d2)
Intenté agregar un índice de la siguiente manera: CREATE INDEX idx_my_index ON my_table(A, B, C, D); pero los tiempos de respuesta siguen siendo muy largos
PD: Estoy usando MariadDB 10.3
La explicación:
+—-+————-+———–+——-+—————-+—————+———+————-+———–+————————–+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———–+——-+—————-+—————+———+————-+———–+————————–+
| 1 | SIMPLE | my_table | ref | idx_my_index | idx_my_index | 6 | const,const | 12055772 | Using where; Using index |
+—-+————-+———–+——-+—————-+—————+———+————-+———–+————————–+
¿Hay alguna mejora que pueda agregar a mi índice, en la configuración de Mariadb o alguna otra selección para hacer esto?
Solución específica: Si usamos esta consulta como una subconsulta, podemos usar la estrategia FirstMatch para evitar el escaneo completo de la tabla. Esto se describe en https://mariadb.com/kb/en/firstmatch-strategy/
SELECT * FROM my__second_table tbis
WHERE (tbis.C, tbis.D)
IN (SELECT C,D FROM my_table WHERE A=a AND B=b AND (C!=c OR D!=d));
davy.ai
Como experto en IT especializado en optimización e indexación de MySQL, existen algunas alternativas posibles para optimizar una consulta SELECT en una tabla de alto volumen con criterios de exclusión.
En primer lugar, agregar un índice como este
CREATE INDEX idx_my_index ON my_table(A, B, C, D);es un buen comienzo. Sin embargo, como ha notado, los tiempos de respuesta siguen siendo muy largos. Un posible enfoque para optimizar aún más esta consulta es aprovechar la estrategia FirstMatch. Esto implica utilizar la consulta como una subconsulta en otra consulta, aplicando los criterios de exclusión a la consulta externa. Por ejemplo:Este enfoque permite que los criterios de exclusión se desplacen hacia la subconsulta, que luego puede utilizar la estrategia FirstMatch para reducir la cantidad de datos que deben escanearse.
Otro enfoque posible es dividir la tabla en particiones más pequeñas y manejables. Esto se puede hacer utilizando la partición, que permite almacenar los datos en particiones físicas separadas basadas en una clave de partición elegida. Esto puede mejorar el rendimiento de las consultas SELECT, especialmente si los criterios de consulta están alineados con la clave de particionamiento.
Finalmente, es posible que desee considerar ajustar la configuración de MariaDB para optimizar su carga de trabajo. Esto podría implicar ajustar el tamaño del búfer de almacenamiento, cambiar el tamaño de la memoria caché de consultas o habilitar la memoria caché de consultas. Estos cambios pueden ayudar a mejorar el rendimiento, especialmente si tiene un alto volumen de conexiones concurrentes o si su carga de trabajo implica muchas consultas complejas.
En general, existen varios enfoques posibles para optimizar una consulta SELECT en una tabla de alto volumen con criterios de exclusión. Al experimentar con diferentes enfoques y configuraciones, debería poder encontrar una solución que satisfaga sus necesidades de rendimiento.