Как оптимизировать этот запрос MySQL? Миллионы строк

Ответ 1

Я бы создал следующие индексы (индексы b-tree):

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

Это отличается от предложения Гордона.

Важен порядок столбцов в индексе.

Вы фильтруете специальную analytics.user_id, поэтому это поле должно быть первым в индексе. Затем вы группируете analytics.source. Чтобы избежать сортировки по source это должно быть следующее поле индекса. Вы также ссылаетесь на analytics.id, поэтому лучше иметь это поле как часть индекса, поставить его последним. Может ли MySQL читать только индекс и не касаться таблицы? Я не знаю, но это довольно легко проверить.

Индекс transactions должен начинаться с analytics, поскольку он будет использоваться в JOIN. Нам также нужен 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 

Ответ 2

Сначала некоторый анализ...

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 

Если сопоставление от a до t является "один ко многим", тогда вам нужно подумать о том, имеют ли COUNT и SUM правильные значения или завышенные значения. По мере того как запрос стоит, они "завышены". JOIN происходит до агрегации, поэтому вы рассчитываете количество транзакций и сколько их было завершено. Я предполагаю, что это желательно.

Примечание. Обычным шаблоном является COUNT(*); говоря, что COUNT(x) подразумевает проверку x наличие NULL. Я подозреваю, что проверка не нужна?

Этот индекс обрабатывает WHERE и "покрывает":

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

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

GROUP BY может или не может потребовать "сортировку". ORDER BY, отличающийся от GROUP BY, определенно будет нужен. И весь сгруппированный набор строк нужно будет отсортировать; для LIMIT нет ярлыка.

Как правило, сводные таблицы ориентированы на дату. То есть, PRIMARY KEY включает в себя "дату" и некоторые другие измерения. Может быть, манипулирование по дате и user_id будет иметь смысл? Сколько транзакций в день имеет средний пользователь? Если не менее 10, то рассмотрим таблицу Сводной таблицы. Кроме того, важно не UPDATEing или DELETEing старые записи. Больше

Я, вероятно,

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

Тогда запрос становится

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 

Скорость исходит из многих факторов

• Меньшая таблица (меньше строк для просмотра)
• Нет JOIN
• Более полезный индекс

(Ему все еще нужен дополнительный вид.)

Даже без сводной таблицы могут быть некоторые ускорения...

• Насколько велики таблицы? Насколько велика 'innodb_buffer_pool_size?
• Normalizing некоторых строк, которые являются объемными и повторяющимися, может сделать эту таблицу не связанной с I/O.
• Это ужасно: KEY (transaction_id(191)); См. Здесь 5 способов исправить это.
• IP-адресам не требуется 255 байт, а не utf8mb4_unicode_ci. (39) и ascii.

Ответ 3

Для этого запроса:

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 ;

Вам нужен индекс analytics(user_id, id, source) и transactions(analytics, status).

Ответ 4

Попробуйте ниже и дайте мне знать, если это поможет.

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

Ответ 5

Не могли бы вы попробовать ниже Подход:

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 

Ответ 6

Этот запрос потенциально объединяет миллионы analytics записей с записями transactions и вычисляет сумму (включая проверку состояния) на миллионах записей. Если мы сможем сначала применить LIMIT 10 а затем выполнить объединение и рассчитать сумму, мы можем ускорить запрос. К сожалению, нам нужна команда analytics.id для соединения, которая теряется после применения GROUP BY. Но, может быть, analytics.source является достаточно избирательным, чтобы в любом случае увеличить запрос.

Поэтому моя идея состоит в том, чтобы вычислить частоты, ограниченные ими, для возврата analytics.source и frequency в подзапрос и использовать этот результат для фильтрации analytics в основном запросе, который затем выполняет остальные соединения и вычисления на надеюсь, значительно сократит количество записей.

Минимальный подзапрос (примечание: нет соединения, нет суммы, возвращается 10 записей):

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

Полный запрос с использованием вышеуказанного запроса в качестве подзапроса 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

Если это не даст ожидаемого повышения производительности, это может быть связано с тем, что MySQL применяет соединения в непредвиденном порядке. Как объясняется здесь: "Есть ли способ заставить порядок выполнения MySQL?" , в этом случае вы можете заменить соединение STRAIGHT_JOIN.

Ответ 7

Я бы попробовал подзапрос:

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; 

Плюс индексы точно так же, как @Gordon answer: analytics (user_id, id, source) и транзакции (аналитика, статус).

Ответ 8

Только проблема, которую я нахожу в вашем запросе, - это

GROUP BY analytics.source 
ORDER BY frequency DESC 

из-за этого запроса выполняется fileort с использованием временной таблицы.

Один из способов избежать этого - создать другую таблицу, такую как

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;'

вставлять данные в analytics_aggr, используя следующий запрос

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 

Теперь вы можете легко получить данные с помощью

select * from analytics_aggr order by sales desc

Ответ 9

Попробуй это

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 

Я предлагаю это, потому что вы сказали "они массивная таблица", но этот sql использует только несколько столбцов. В этом случае, если мы используем встроенный просмотр с требуемыми столбцами, тогда это будет хорошо

Примечание: память также будет играть важную роль здесь. Поэтому подтвердите память перед тем, как выбрать встроенный вид

Ответ 10

Я бы попытался отделить запрос от двух таблиц. Поскольку вам нужны только первые 10 source, я бы сначала их получил, а затем запросил transactions в столбце 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

Это может быть также быстрее без distinct

Ответ 11

Я предполагаю, что предикат user_id = 52094 предназначен для иллюстрации, и в приложении выбранный user_id является переменной.

Я также предполагаю, что свойство ACID здесь не очень важно.

(1) Поэтому я буду поддерживать две таблицы реплик только с необходимыми полями (это похоже на индексы, предложенные Владимиром выше) с использованием таблицы утилиты.

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);

Усиление здесь заключается в том, что мы будем читать относительно небольшие прогнозы исходных таблиц - надеюсь, работают кеши уровня ОС и уровня БД, и они не считываются из более медленного вторичного хранилища, а из более быстрой ОЗУ. Это может быть очень большим выигрышем.

Вот как я обновляю две таблицы (ниже транзакция выполняется 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) Теперь я рассмотрю избирательность, чтобы уменьшить время последовательного сканирования. Мне нужно будет построить индекс b-дерева для user_id, source и id (в этой последовательности) на mv_anal.

Примечание: вышесказанное может быть достигнуто только путем создания индекса в таблице аналитики, но для построения такого индекса требуется чтение большой таблицы с 60M строк. Мой метод требует, чтобы индексное здание считывало только очень тонкую таблицу. Таким образом, мы можем чаще настраивать btree (чтобы противостоять проблеме перекоса, поскольку таблица добавляется только).

Именно поэтому я убеждаюсь в том, что высокая избирательность достигается при запросе и противодействии проблеме перекоса btree.

(3) В PostgreSQL с подзапросами всегда материализованы. Я также надеюсь на MySQL. Поэтому в качестве последней мили оптимизации:

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

Ответ 12

Поздно к вечеринке. Я думаю, вам нужно будет загрузить один индекс в кеш MySQL. NLJ, вероятно, убивает производительность. Вот как я это вижу:

Путь

Ваш запрос прост. Он имеет две таблицы и "путь" очень ясен:

• Оптимизатор должен сначала планировать чтение analytics таблицы.
• Оптимизатор должен планировать чтение второй таблицы transactions. Это связано с тем, что вы используете LEFT OUTER JOIN. Об этом мало обсуждается.
• Кроме того, таблица analytics составляет 60 миллионов строк, и лучший путь должен фильтровать строки как можно скорее на этом.

Доступ

Когда путь будет понятным, вам нужно решить, хотите ли вы использовать индексный доступ или доступ к таблице. У обоих есть плюсы и минусы. Однако вы хотите улучшить производительность SELECT:

• Вы должны выбрать Index Access.
• Избегайте гибридного доступа. Поэтому вам следует избегать любого доступа к таблице (выборки) любой ценой. Перевод: поместите все участвующие столбцы в индексы.

Фильтрация

Опять же, вы хотите высокую производительность для SELECT. Следовательно:

• Вы должны выполнить фильтрацию на уровне индекса, а не на уровне таблицы.

Агрегация строк

После фильтрации следующим шагом будет объединение строк по GROUP BY analytics.source. Это можно улучшить, поставив source столбец в качестве первого столбца в индексе.

Оптимальные индексы для пути, доступа, фильтрации и агрегирования

Учитывая все вышесказанное, вы должны включить все упомянутые столбцы в индексы. Следующие индексы должны улучшить время отклика:

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

create index ix2_transactions on transactions (analytics, status);

Эти индексы соответствуют стратегиям "путь", "доступ" и "фильтрация", описанным выше.

Кэш индекса

Наконец - и это очень важно - загрузите вторичный индекс в кеш памяти MySQL. MySQL выполняет NLJ (вложенный Loop Join) - "ref" в MySQL lingo - и ему нужно получить доступ к второму случайному почти в 200k раз.

К сожалению, я точно не знаю, как загрузить индекс в кеш MySQL. Использование FORCE может работать, как в:

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

Убедитесь, что у вас достаточно свободного места в кеше. Вот короткий вопрос/ответ, чтобы выяснить: как выяснить, полностью ли индексируется индекс mysql в памяти

Удачи! О, и опубликуйте результаты.

Ответ 13

Этот вопрос определенно получил много внимания, поэтому я уверен, что все очевидные решения были опробованы. Однако я не видел что-то, что обращается к LEFT JOIN в запросе.

Я заметил, что операторы LEFT JOIN обычно заставляют планировщиков запросов в хеш-соединение, которые быстро выполняются для небольшого количества результатов, но ужасно медленны для большого количества результатов. Как отмечено в ответе @Rick James, поскольку объединение в исходном запросе находится в поле identity analytics.id, это приведет к большому числу результатов. Присоединение хэша принесет ужасные результаты. Приведенное ниже предложение относится к этому ниже без каких-либо изменений схемы или обработки.

Поскольку агрегирование осуществляется с помощью analytics.source, я бы попробовал запрос, который создает отдельные агрегаты для частоты по источникам и продажам по источникам и откладывает левое соединение до тех пор, пока агрегация не будет завершена. Это должно позволить лучше использовать индексы (обычно это объединение слияния для больших наборов данных).

Вот мое предложение:

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 

Надеюсь это поможет.