soporta - ¿Cómo optimizar esta consulta de MySQL? Millones de filas

optimizar consultas mysql

Question

Tengo la siguiente consulta:

SELECT 
    analytics.source AS referrer, 
    COUNT(analytics.id) AS frequency, 
    SUM(IF(transactions.status = ''COMPLETED'', 1, 0)) AS sales
FROM analytics
LEFT JOIN transactions ON analytics.id = transactions.analytics
WHERE analytics.user_id = 52094 
GROUP BY analytics.source 
ORDER BY frequency DESC 
LIMIT 10 

La tabla de análisis tiene 60 millones de filas y la tabla de transacciones tiene 3 millones de filas.

Cuando ejecuto un EXPLAIN en esta consulta, obtengo:

+------+--------------+-----------------+--------+---------------------+-------------------+----------------------+---------------------------+----------+-----------+-------------------------------------------------+
| # id |  select_type |      table      |  type  |    possible_keys    |        key        |        key_len       |            ref            |   rows   |   Extra   |                                                 |
+------+--------------+-----------------+--------+---------------------+-------------------+----------------------+---------------------------+----------+-----------+-------------------------------------------------+
| ''1''  |  ''SIMPLE''    |  ''analytics''    |  ''ref'' |  ''analytics_user_id | analytics_source'' |  ''analytics_user_id'' |  ''5''                      |  ''const'' |  ''337662'' |  ''Using where; Using temporary; Using filesort'' |
| ''1''  |  ''SIMPLE''    |  ''transactions'' |  ''ref'' |  ''tran_analytics''   |  ''tran_analytics'' |  ''5''                 |  ''dijishop2.analytics.id'' |  ''1''     |  NULL     |                                                 |
+------+--------------+-----------------+--------+---------------------+-------------------+----------------------+---------------------------+----------+-----------+-------------------------------------------------+

No puedo averiguar cómo optimizar esta consulta ya que es muy básica. Se tarda unos 70 segundos para ejecutar esta consulta.

Aquí están los índices que existen:

+-------------+-------------+----------------------------+---------------+------------------+------------+--------------+-----------+---------+--------+-------------+----------+----------------+
|   # Table   |  Non_unique |          Key_name          |  Seq_in_index |    Column_name   |  Collation |  Cardinality |  Sub_part |  Packed |  Null  |  Index_type |  Comment |  Index_comment |
+-------------+-------------+----------------------------+---------------+------------------+------------+--------------+-----------+---------+--------+-------------+----------+----------------+
| ''analytics'' |  ''0''        |  ''PRIMARY''                 |  ''1''          |  ''id''            |  ''A''       |  ''56934235''  |  NULL     |  NULL   |  ''''    |  ''BTREE''    |  ''''      |  ''''            |
| ''analytics'' |  ''1''        |  ''analytics_user_id''       |  ''1''          |  ''user_id''       |  ''A''       |  ''130583''    |  NULL     |  NULL   |  ''YES'' |  ''BTREE''    |  ''''      |  ''''            |
| ''analytics'' |  ''1''        |  ''analytics_product_id''    |  ''1''          |  ''product_id''    |  ''A''       |  ''490812''    |  NULL     |  NULL   |  ''YES'' |  ''BTREE''    |  ''''      |  ''''            |
| ''analytics'' |  ''1''        |  ''analytics_affil_user_id'' |  ''1''          |  ''affil_user_id'' |  ''A''       |  ''55222''     |  NULL     |  NULL   |  ''YES'' |  ''BTREE''    |  ''''      |  ''''            |
| ''analytics'' |  ''1''        |  ''analytics_source''        |  ''1''          |  ''source''        |  ''A''       |  ''24604''     |  NULL     |  NULL   |  ''YES'' |  ''BTREE''    |  ''''      |  ''''            |
| ''analytics'' |  ''1''        |  ''analytics_country_name''  |  ''1''          |  ''country_name''  |  ''A''       |  ''39510''     |  NULL     |  NULL   |  ''YES'' |  ''BTREE''    |  ''''      |  ''''            |
| ''analytics'' |  ''1''        |  ''analytics_gordon''        |  ''1''          |  ''id''            |  ''A''       |  ''56934235''  |  NULL     |  NULL   |  ''''    |  ''BTREE''    |  ''''      |  ''''            |
| ''analytics'' |  ''1''        |  ''analytics_gordon''        |  ''2''          |  ''user_id''       |  ''A''       |  ''56934235''  |  NULL     |  NULL   |  ''YES'' |  ''BTREE''    |  ''''      |  ''''            |
| ''analytics'' |  ''1''        |  ''analytics_gordon''        |  ''3''          |  ''source''        |  ''A''       |  ''56934235''  |  NULL     |  NULL   |  ''YES'' |  ''BTREE''    |  ''''      |  ''''            |
+-------------+-------------+----------------------------+---------------+------------------+------------+--------------+-----------+---------+--------+-------------+----------+----------------+


+----------------+-------------+-------------------+---------------+-------------------+------------+--------------+-----------+---------+--------+-------------+----------+----------------+
|    # Table     |  Non_unique |      Key_name     |  Seq_in_index |    Column_name    |  Collation |  Cardinality |  Sub_part |  Packed |  Null  |  Index_type |  Comment |  Index_comment |
+----------------+-------------+-------------------+---------------+-------------------+------------+--------------+-----------+---------+--------+-------------+----------+----------------+
| ''transactions'' |  ''0''        |  ''PRIMARY''        |  ''1''          |  ''id''             |  ''A''       |  ''2436151''   |  NULL     |  NULL   |  ''''    |  ''BTREE''    |  ''''      |  ''''            |
| ''transactions'' |  ''1''        |  ''tran_user_id''   |  ''1''          |  ''user_id''        |  ''A''       |  ''56654''     |  NULL     |  NULL   |  ''''    |  ''BTREE''    |  ''''      |  ''''            |
| ''transactions'' |  ''1''        |  ''transaction_id'' |  ''1''          |  ''transaction_id'' |  ''A''       |  ''2436151''   |  ''191''    |  NULL   |  ''YES'' |  ''BTREE''    |  ''''      |  ''''            |
| ''transactions'' |  ''1''        |  ''tran_analytics'' |  ''1''          |  ''analytics''      |  ''A''       |  ''2436151''   |  NULL     |  NULL   |  ''YES'' |  ''BTREE''    |  ''''      |  ''''            |
| ''transactions'' |  ''1''        |  ''tran_status''    |  ''1''          |  ''status''         |  ''A''       |  ''22''        |  NULL     |  NULL   |  ''YES'' |  ''BTREE''    |  ''''      |  ''''            |
| ''transactions'' |  ''1''        |  ''gordon_trans''   |  ''1''          |  ''status''         |  ''A''       |  ''22''        |  NULL     |  NULL   |  ''YES'' |  ''BTREE''    |  ''''      |  ''''            |
| ''transactions'' |  ''1''        |  ''gordon_trans''   |  ''2''          |  ''analytics''      |  ''A''       |  ''2436151''   |  NULL     |  NULL   |  ''YES'' |  ''BTREE''    |  ''''      |  ''''            |
+----------------+-------------+-------------------+---------------+-------------------+------------+--------------+-----------+---------+--------+-------------+----------+----------------+

Esquema simplificado para las dos tablas antes de agregar cualquier índice adicional como se sugirió, ya que no mejoró la situación.

CREATE TABLE `analytics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `affil_user_id` int(11) DEFAULT NULL,
  `product_id` int(11) DEFAULT NULL,
  `medium` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `source` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `terms` varchar(1024) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `is_browser` tinyint(1) DEFAULT NULL,
  `is_mobile` tinyint(1) DEFAULT NULL,
  `is_robot` tinyint(1) DEFAULT NULL,
  `browser` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `mobile` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `robot` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `platform` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `referrer` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `domain` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `ip` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `continent_code` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `country_name` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `city` varchar(100) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `analytics_user_id` (`user_id`),
  KEY `analytics_product_id` (`product_id`),
  KEY `analytics_affil_user_id` (`affil_user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=64821325 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `transactions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `transaction_id` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `pay_key` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `sender_email` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `currency` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `analytics` int(11) DEFAULT NULL,
  `ip_address` varchar(46) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `session_id` varchar(60) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `eu_vat_applied` int(1) DEFAULT ''0'',
  PRIMARY KEY (`id`),
  KEY `tran_user_id` (`user_id`),
  KEY `transaction_id` (`transaction_id`(191)),
  KEY `tran_analytics` (`analytics`),
  KEY `tran_status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=10019356 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Si lo anterior no se puede optimizar más. Cualquier consejo de implementación en tablas de resumen será genial. Estamos utilizando una pila LAMP en AWS. La consulta anterior se está ejecutando en RDS (m1.large).

Answer 1

¿Podrías intentarlo a continuación?

SELECT 
    analytics.source AS referrer, 
    COUNT(analytics.id) AS frequency, 
    SUM(sales) AS sales
FROM analytics
LEFT JOIN(
 SELECT transactions.Analytics, (CASE WHEN transactions.status = ''COMPLETED'' THEN 1 ELSE 0 END) AS sales
 FROM analytics INNER JOIN transactions ON analytics.id = transactions.analytics
) Tra
ON analytics.id = Tra.analytics
WHERE analytics.user_id = 52094 
GROUP BY analytics.source 
ORDER BY frequency DESC 
LIMIT 10 

Answer 2

El único problema que encuentro en tu consulta es

GROUP BY analytics.source 
ORDER BY frequency DESC 

Debido a esta consulta está haciendo filesort usando tabla temporal.

Una forma de evitar esto es creando otra tabla como

CREATE TABLE `analytics_aggr` (
  `source` varchar(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `frequency` int(10) DEFAULT NULL,
  `sales` int(10) DEFAULT NULL,
  KEY `sales` (`sales`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;`

insertar datos en analytics_aggr usando la siguiente consulta

insert into analytics_aggr SELECT 
    analytics.source AS referrer, 
    COUNT(analytics.id) AS frequency, 
    SUM(IF(transactions.status = ''COMPLETED'', 1, 0)) AS sales
    FROM analytics
    LEFT JOIN transactions ON analytics.id = transactions.analytics
    WHERE analytics.user_id = 52094 
    GROUP BY analytics.source 
    ORDER BY null 

Ahora puedes obtener fácilmente tus datos usando

select * from analytics_aggr order by sales desc

Answer 3

Esta consulta potencialmente une millones de registros analytics con registros de transactions y calcula la suma (incluida la verificación de estado) en millones de registros. Si pudiéramos primero aplicar el LIMIT 10 y luego hacer la unión y calcular la suma, podríamos acelerar la consulta. Desafortunadamente, necesitamos el analytics.id para la unión, que se pierde después de aplicar GROUP BY . Pero quizás analytics.source sea ​​lo suficientemente selectivo como para impulsar la consulta de todos modos.

Por lo tanto, mi idea es calcular las frecuencias, limitarlas, devolver la analytics.source y la frequency analytics.source en una subconsulta y usar este resultado para filtrar las analytics en la consulta principal, que luego realiza el resto de las combinaciones y cálculos en una Esperemos que se reduzca mucho el número de registros.

Subconsulta mínima (nota: sin unión, sin suma, devuelve 10 registros):

SELECT
    source,
    COUNT(id) AS frequency
FROM analytics
WHERE user_id = 52094
GROUP BY source
ORDER BY frequency DESC 
LIMIT 10

La consulta completa utilizando la consulta anterior como subconsulta x :

SELECT
    x.source AS referrer,
    x.frequency,
    SUM(IF(t.status = ''COMPLETED'', 1, 0)) AS sales
FROM
    (<subquery here>) x
    INNER JOIN analytics a
       ON x.source = a.source  -- This reduces the number of records
    LEFT JOIN transactions t
       ON a.id = t.analytics
WHERE a.user_id = 52094      -- We could have several users per source
GROUP BY x.source, x.frequency
ORDER BY x.frequency DESC

Si esto no produce el aumento de rendimiento esperado, esto podría deberse a que MySQL aplique las uniones en un orden inesperado. Como se explica aquí "¿Hay alguna forma de forzar el orden de ejecución de MySQL?" , podría reemplazar la unión por STRAIGHT_JOIN en este caso.

Answer 4

Esta pregunta definitivamente ha recibido mucha atención, así que estoy seguro de que se han intentado todas las soluciones obvias. Sin embargo, no vi algo que aborde la LEFT JOIN en la consulta.

Me he dado cuenta de que las declaraciones LEFT JOIN generalmente obligan a los planificadores de consultas a unirse a hash, que son rápidos para un pequeño número de resultados, pero terriblemente lento para un gran número de resultados. Como se señaló en la respuesta de @Rick James, dado que la unión en la consulta original se encuentra en el campo de identidad analytics.id , esto generará un gran número de resultados. Una combinación de hash producirá resultados de rendimiento terrible. La siguiente sugerencia aborda esto a continuación sin ningún cambio de esquema o procesamiento.

Dado que la agregación es de analytics.source , probaría una consulta que crea agregaciones separadas para la frecuencia por fuente y las ventas por fuente y difiere la combinación izquierda hasta después de que se complete la agregación. Esto debería permitir que los índices se utilicen mejor (normalmente se trata de una combinación de combinación para grandes conjuntos de datos).

Aquí está mi sugerencia:

SELECT t1.source AS referrer, t1.frequency, t2.sales
FROM (
  -- Frequency by source
  SELECT a.source, COUNT(a.id) AS frequency
  FROM analytics a
  WHERE a.user_id=52094
  GROUP BY a.source
) t1
LEFT JOIN (
  -- Sales by source
  SELECT a.source,
    SUM(IF(t.status = ''COMPLETED'', 1, 0)) AS sales
  FROM analytics a
  JOIN transactions t
  WHERE a.id = t.analytics
    AND t.status = ''COMPLETED''
    AND a.user_id=52094
  GROUP by a.source
) t2
  ON t1.source = t2.source
ORDER BY frequency DESC 
LIMIT 10 

Espero que esto ayude.

Answer 5

Intentaría separar la consulta de las dos tablas. Ya que solo necesita las 10 source , primero las obtendría y luego consultaré en las transactions la columna de sales :

SELECT  source as referrer
        ,frequency
        ,(select count(*) 
          from   transactions t  
          where  t.analytics in (select distinct id 
                                 from   analytics 
                                 where  user_id = 52094
                                        and source = by_frequency.source) 
                 and status = ''completed''
         ) as sales
from    (SELECT analytics.source
                ,count(*) as frequency
        from    analytics 
        where   analytics.user_id = 52094
        group by analytics.source
        order by frequency desc
        limit 10
        ) by_frequency

También puede ser más rápido sin los distinct

Answer 6

Me gustaría crear los siguientes índices (índices b-tree):

analytics(user_id, source, id) 
transactions(analytics, status)

Esto es diferente de la sugerencia de Gordon.

El orden de las columnas en el índice es importante.

Usted filtra por analytics.user_id específico, por lo que este campo debe ser el primero en el índice. Entonces te agrupas por analytics.source . Para evitar la clasificación por source este debe ser el siguiente campo del índice. También hace referencia a analytics.id , por lo que es mejor tener este campo como parte del índice, ponerlo en último lugar. ¿MySQL es capaz de leer solo el índice y no tocar la tabla? No lo sé, pero es bastante fácil de probar.

El índice de transactions debe comenzar con el analytics , ya que se usaría en la JOIN . También necesitamos status .

SELECT 
    analytics.source AS referrer, 
    COUNT(analytics.id) AS frequency, 
    SUM(IF(transactions.status = ''COMPLETED'', 1, 0)) AS sales
FROM analytics
LEFT JOIN transactions ON analytics.id = transactions.analytics
WHERE analytics.user_id = 52094 
GROUP BY analytics.source 
ORDER BY frequency DESC 
LIMIT 10 

Answer 7

Para esta consulta:

SELECT a.source AS referrer, 
       COUNT(*) AS frequency, 
       SUM( t.status = ''COMPLETED'' ) AS sales
FROM analytics a LEFT JOIN
     transactions t
     ON a.id = t.analytics
WHERE a.user_id = 52094 
GROUP BY a.source 
ORDER BY frequency DESC 
LIMIT 10 ;

Desea un índice de analytics(user_id, id, source) y transactions(analytics, status) .

Answer 8

Primero un análisis ...

SELECT  a.source AS referrer,
        COUNT(*) AS frequency,  -- See question below
        SUM(t.status = ''COMPLETED'') AS sales
    FROM  analytics AS a
    LEFT JOIN  transactions AS t  ON a.id = t.analytics AS a
    WHERE  a.user_id = 52094
    GROUP BY  a.source
    ORDER BY  frequency DESC
    LIMIT  10 

Si la asignación de a a t es "uno a muchos", entonces debe considerar si COUNT y SUM tienen los valores correctos o inflados. Como la consulta se mantiene, están "inflados". El JOIN produce antes de la agregación, por lo que está contando el número de transacciones y cuántas se completaron. Asumiré que se desea.

Nota: El patrón habitual es COUNT(*) ; decir COUNT(x) implica verificar que x sea NULL . Sospecho que no se necesita cheque?

Este índice maneja el WHERE y está "cubriendo":

 analytics:  INDEX(user_id, source, id)   -- user_id first

 transactions:  INDEX(analytics, status)  -- in this order

El GROUP BY puede o no requerir una ''ordenación''. El ORDER BY , al ser diferente del GROUP BY , definitivamente necesitará una clasificación. Y todo el conjunto agrupado de filas tendrá que ser ordenado; no hay atajo para el LIMIT

Normalmente, las tablas de resumen están orientadas a la fecha. Es decir, la PRIMARY KEY incluye una ''fecha'' y algunas otras dimensiones. Quizás, ¿teclear por fecha y user_id tendría sentido? ¿Cuántas transacciones por día tiene el usuario promedio? Si al menos 10, entonces consideremos una tabla de resumen. Además, es importante no UPDATEing o DELETEing registros antiguos. More

Probablemente tendría

user_id ...,
source ...,
dy DATE ...,
status ...,
freq      MEDIUMINT UNSIGNED NOT NULL,
status_ct MEDIUMINT UNSIGNED NOT NULL,
PRIMARY KEY(user_id, status, source, dy)

Entonces la consulta se convierte en

SELECT  source AS referrer,
        SUM(freq) AS frequency,
        SUM(status_ct) AS completed_sales
    FROM  Summary
    WHERE  user_id = 52094
      AND  status = ''COMPLETED''
    GROUP BY source
    ORDER BY  frequency DESC
    LIMIT  10 

La velocidad viene de muchos factores.

• Tabla más pequeña (menos filas para mirar)
• No JOIN
• Índice más útil

(Todavía necesita el tipo extra).

Incluso sin la tabla de resumen, puede haber algunas aceleraciones ...

• ¿Qué tan grandes son las mesas? ¿Qué tan grande es `innodb_buffer_pool_size?
• Normalizing algunas de las cadenas que son voluminosas y repetitivas podría hacer que esa tabla no esté vinculada a E / S.
• Esto es horrible: KEY (transaction_id(191)) ; Vea here para 5 maneras de arreglarlo.
• Las direcciones IP no necesitan 255 bytes, ni utf8mb4_unicode_ci . (39) y ascii son suficientes.

Answer 9

Prueba a continuación y hazme saber si esto ayuda.

SELECT 
    analytics.source AS referrer, 
    COUNT(analytics.id) AS frequency, 
    SUM(IF(transactions.status = ''COMPLETED'', 1, 0)) AS sales
FROM (SELECT * FROM analytics where user_id = 52094) analytics
LEFT JOIN (SELECT analytics, status from transactions where analytics = 52094) transactions ON analytics.id = transactions.analytics
GROUP BY analytics.source 
ORDER BY frequency DESC 
LIMIT 10

Answer 10

Prueba esto

SELECT 
    a.source AS referrer, 
    COUNT(a.id) AS frequency, 
    SUM(t.sales) AS sales
FROM (Select id, source From analytics Where user_id = 52094) a
LEFT JOIN (Select analytics, case when status = ''COMPLETED'' Then 1 else 0 end as sales 
           From transactions) t ON a.id = t.analytics
GROUP BY a.source 
ORDER BY frequency DESC 
LIMIT 10 

Estoy proponiendo esto porque dijiste "son una tabla masiva" pero este sql usa solo muy pocas columnas. En este caso, si usamos la vista en línea con solo columnas requeridas, entonces será bueno

Nota: la memoria también jugará un papel importante aquí. Así que confirme la memoria antes de decidir la vista en línea

Answer 11

Supongo que el predicado, user_id = 52094, es para fines de ilustración y en la aplicación, el user_id seleccionado es una variable.

También asumo que la propiedad ACID no es muy importante aquí.

(1) Por lo tanto, mantendré dos tablas de réplicas con solo los campos necesarios (es similar a los índices que Vladimir había sugerido anteriormente) usando una tabla de utilidades.

CREATE TABLE mv_anal (
  `id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `source` varchar(45),
  PRIMARY KEY (`id`)
);

CREATE TABLE mv_trans (
  `id` int(11) NOT NULL,
  `status` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `analytics` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE util (
  last_updated_anal int (11) NOT NULL,
  last_updated_trans int (11) NOT NULL
);

INSERT INTO util (0, 0);

La ventaja aquí es que leeremos proyecciones relativamente más pequeñas de las tablas originales; es de esperar que los cachés a nivel de sistema operativo y nivel de base de datos funcionen y no se lean desde un almacenamiento secundario más lento sino desde una memoria RAM más rápida. Esto puede ser una gran ganancia.

Aquí es cómo actualicé las dos tablas (la siguiente es una transacción ejecutada por un cron):

-- TRANSACTION STARTS -- 

INSERT INTO mv_trans 
SELECT id, IF (status = ''COMPLETE'', 1, 0) AS status, analysis 
FROM transactions JOIN util
ON util.last_updated_trans <= transactions.id

UPDATE util
SET last_updated_trans = sub.m
FROM (SELECT MAX (id) AS m FROM mv_trans) sub;

-- TRANSACTION COMMITS -- 

-- similar transaction for mv_anal.

(2) Ahora, abordaré la selectividad para reducir el tiempo de exploración secuencial. Tendré que crear un índice b-tree en user_id, source e id (en esta secuencia) en mv_anal.

Nota: lo anterior se puede lograr simplemente creando un índice en la tabla de análisis, pero la creación de dicho índice requiere la lectura de una tabla grande con filas de 60M. Mi método requiere que la creación de índices lea solo una tabla muy delgada. Por lo tanto, podemos reconstruir el btree con mayor frecuencia (para contrarrestar el problema de sesgo, ya que la tabla es solo de anexo).

Así es como me aseguro de que se logre una alta selectividad al realizar consultas y para contrarrestar el problema del sesgo.

(3) En PostgreSQL, las subconsultas CON siempre se materializan. Espero lo mismo para MySQL. Por lo tanto, como la última milla de optimización:

WITH sub_anal AS (
  SELECT user_id, source AS referrer, COUNT (id) AS frequency
  FROM mv_anal
  WHERE user_id = 52094
  GROUP BY user_id, source
  ORDER BY COUNT (id) DESC
  LIMIT 10
)
SELECT sa.referrer, sa.frequency, SUM (status) AS sales
FROM sub_anal AS sa 
JOIN mv_anal anal 
ON sa.referrer = anal.source AND sa.user_id = anal.user_id
JOIN mv_trans AS trans
ON anal.id = trans.analytics

Answer 12

Tarde a la fiesta. Creo que necesitarás cargar un índice en el caché de MySQL. El NLJ probablemente está matando el rendimiento. Así es como lo veo:

El camino

Su consulta es simple. Tiene dos tablas y el "camino" es muy claro:

• El optimizador debería planear leer primero la tabla de analytics .
• El optimizador debería planear leer la tabla de transactions segundo lugar. Esto se debe a que está utilizando una LEFT OUTER JOIN . No hay mucha discusión sobre esto.
• Además, la tabla de analytics es de 60 millones de filas y la mejor ruta debería filtrar las filas tan pronto como sea posible en este caso.

El acceso

Una vez que la ruta está despejada, debe decidir si desea utilizar un acceso de índice o un acceso de tabla. Ambos tienen pros y contras. Sin embargo, desea mejorar el rendimiento de SELECT :

• Usted debe elegir el acceso al índice.
• Evita el acceso híbrido. Por lo tanto, debe evitar cualquier acceso a la tabla (búsquedas) a toda costa. Traducción: colocar todas las columnas participantes en índices.

El filtrado

Una vez más, desea alto rendimiento para el SELECT . Por lo tanto:

• Debe realizar el filtrado en el nivel de índice, no en el nivel de tabla.

Agregación de filas

Después de filtrar, el siguiente paso es agregar filas por GROUP BY analytics.source . Esto se puede mejorar colocando la columna de source como la primera columna en el índice.

Índices óptimos para ruta, acceso, filtrado y agregación

Teniendo en cuenta todo lo anterior, debe incluir todas las columnas mencionadas en los índices. Los siguientes índices deberían mejorar el tiempo de respuesta:

create index ix1_analytics on analytics (user_id, source, id);

create index ix2_transactions on transactions (analytics, status);

Estos índices cumplen con la "ruta", el "acceso" y las estrategias de "filtrado" descritas anteriormente.

El caché del índice

Finalmente, y esto es crítico, cargue el índice secundario en la memoria caché de MySQL. MySQL está realizando un NLJ (Nested Loop Join), una ''referencia'' en la jerga de MySQL, y necesita acceder al segundo casi 200k veces al azar.

Desafortunadamente, no estoy seguro de cómo cargar el índice en el caché de MySQL. El uso de FORCE puede funcionar, como en:

SELECT 
    analytics.source AS referrer, 
    COUNT(analytics.id) AS frequency, 
    SUM(IF(transactions.status = ''COMPLETED'', 1, 0)) AS sales
FROM analytics
LEFT JOIN transactions FORCE index (ix2_transactions)
  ON analytics.id = transactions.analytics
WHERE analytics.user_id = 52094 
GROUP BY analytics.source 
ORDER BY frequency DESC 
LIMIT 10

Asegúrese de tener suficiente espacio de caché. Aquí hay una breve pregunta / respuesta para averiguar: Cómo averiguar si el índice mysql encaja completamente en la memoria

¡Buena suerte! Ah, y publicar los resultados.

Answer 13

Yo trataría la subconsulta:

SELECT a.source AS referrer, 
       COUNT(*) AS frequency,
       SUM((SELECT COUNT(*) FROM transactions t 
        WHERE a.id = t.analytics AND t.status = ''COMPLETED'')) AS sales
FROM analytics a
WHERE a.user_id = 52094 
GROUP BY a.source
ORDER BY frequency DESC 
LIMIT 10; 

Más los índices exactamente como la respuesta de @ Gordon: análisis (user_id, id, fuente) y transacciones (análisis, estado).