MySQL SUM Query работает очень медленно

Есть таблица под названием transactions с ~6 миллионами строк. Ниже запрос подсчитывает текущий баланс пользователя. Вот журнал после того как я включил slow_query_log = 'ON':

# Time: 170406  9:51:48
# User@Host: root[root] @  [xx.xx.xx.xx]
# Thread_id: 13  Schema: main_db  QC_hit: No
# Query_time: 38.924823  Lock_time: 0.000034  Rows_sent: 1  Rows_examined: 773550
# Rows_affected: 0
SET timestamp=1491456108;
SELECT SUM(`Transaction`.`amount`) as total
    FROM `main_db`.`transactions` AS `Transaction`
    WHERE `Transaction`.`user_id` = 1008
      AND `Transaction`.`confirmed` = 1
    LIMIT 1;

Как видите, это заняло ~38 seconds!

Вот transactions таблица ПОЯСНИТЕ:

введите описание изображения здесь

Этот запрос иногда выполняется быстро (около ~1 секунды), а иногда очень медленно!

Любая помощь будет принята с благодарностью.

PS:

Это InnoDB и transactions Таблица имеет частые операции INSERT и SELECT.

Я попытался запустить запрос с SQL_NO_CACHE, но это все еще иногда быстро, иногда медленно.

transactions Схема таблицы:

CREATE TABLE `transactions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `ref_id` varchar(40) COLLATE utf8_persian_ci NOT NULL,
  `payment_id` tinyint(3) unsigned NOT NULL,
  `amount` decimal(10,1) NOT NULL,
  `created` datetime NOT NULL,
  `private_note` varchar(6000) COLLATE utf8_persian_ci NOT NULL,
  `public_note` varchar(200) COLLATE utf8_persian_ci NOT NULL,
  `confirmed` tinyint(3) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=13133663 DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci

MySQL работает на VPS с 12 ГБ оперативной памяти и 9 логическими ядрами процессора.

Вот часть my.cnf:

# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
default_storage_engine  = InnoDB
# you can't just change log file size, requires special procedure
innodb_buffer_pool_size = 9G
innodb_log_buffer_size  = 8M
innodb_file_per_table   = 1
innodb_open_files       = 400
innodb_io_capacity      = 400
innodb_flush_method     = O_DIRECT
innodb_thread_concurrency = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64


# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address           = 127.0.0.1
#
# * Fine Tuning
#
max_connections         = 500
connect_timeout         = 5
wait_timeout            = 600
max_allowed_packet      = 16M
thread_cache_size       = 128
sort_buffer_size        = 4M
bulk_insert_buffer_size = 16M
tmp_table_size          = 32M
max_heap_table_size     = 32M
Источник

4 ответа

Решение

(Да, я добавляю другой ответ. Обоснование: он решает основную проблему другим способом.)

Основная проблема, как представляется, заключается в том, что существует постоянно растущая таблица "транзакций", из которой получают различные статистические данные, такие как SUM(amount), Производительность этого будет становиться все хуже и хуже по мере роста таблицы.

Основанием для этого Ответа будет рассмотрение данных двумя способами: "История" и "Текущий". Transactions это история. Новая таблица будет Current итоги по каждому пользователю. Но я вижу несколько способов сделать это. Каждый включает некоторую форму промежуточных итогов, чтобы избежать добавления 773 тыс. Строк для получения ответа.

  • Традиционный банковский способ... Каждую ночь подсчитывать день Transactions и добавить их в Current,
  • Способ материализованного просмотра... Каждый раз, когда в Transactions, приращение Current,
  • Гибрид: храните ежедневные промежуточные итоги в "Сводной таблице". Суммируйте эти промежуточные итоги, чтобы получить SUM до прошлой ночи.

Больше обсуждения в моем блоге на Сводных таблицах.

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

  1. Получить сумму прошлой ночью
  2. Добавьте любые транзакции, которые произошли в течение дня.

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

Источник

(Извините, что наступил на все хорошие комментарии. Я надеюсь, что добавил достаточно, чтобы оправдать требование "Ответ".)

Есть ли 6M строк в таблице? Но 773K строк с этим user_id?

9GB buffer_pool? Таблица составляет около 4 ГБ данных? Таким образом, он помещается в buffer_pool, если нет ничего другого, чтобы его увеличить. (SHOW TABLE STATUS и проверьте "Длина_данных".)

Существующий INDEX(user_id) может быть 20 МБ, легко кэшируется.

Если user_ids достаточно разбросаны по всей таблице, запрос, вероятно, должен извлечь практически каждый 16-килобайтный блок данных. Итак, исходный запрос с оригинальным индексом будет выглядеть примерно так:

  1. сканировать индекс на предмет заданного user_id, Это будет незначительной частью общих усилий.
  2. Для каждой записи в индексе ищите (случайным образом) запись. Это происходит в 1,5 млн раз. С "холодным" кешем это легко займет 38 секунд и более. Где "медленные" времена вскоре после перезагрузки? Или что-то еще, что выдувает кеш? С "теплым" кешем это все ЦП (без ввода / вывода), поэтому разумно использовать 1 секунду.

Если вы измените на оптимальное "покрытие", INDEX(user_id, confirmed, amount), некоторые вещи меняются...

  • "Покрытие" означает, что весь запрос будет выполнен в индексе. (Этот составной индекс может быть больше похож на 40 МБ, но он все еще очень мал по сравнению с данными.)
  • В "холодном" кеше только 40 МБ потребуется для извлечения - ожидайте намного лучше, чем 38-е.
  • В "теплом" кэше (на этот раз только 40 МБ) он может работать за полсекунды.

Если бы был также диапазон дат в WHERE пункт, я бы настаивал на создании и ведении "Сводная таблица". Это может ускорить подобные запросы в 10 раз.

Если вы добавляете составной индекс, начинающийся с user_id, ты должен (не должен) DROP индекс только user_id как избыточный. (Если вы не уроните его, он будет в основном тратить место на диске.)

Что касается того, чтобы делать это в производстве...

  • Если у вас достаточно новая версия MySQL, ALTER TABLE ... ALGORITHM=INPLACE ..., что возможно для добавления / удаления индексов с минимальным воздействием.
  • Для более старых версий см. pt-online-schema-change, Это требует, чтобы не было никаких других триггеров, и действительно занимает очень короткое время простоя. Триггер заботится о 200 записях в минуту "прозрачно".

ALGORITHM=INPLACE был добавлен в MySQL 5.6 и MariaDB 10.0.

Источник

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

ALTER TABLE `transactions` ADD INDEX `user_confirmed` (`user_id`, `confirmed`);

Кроме того, как @wajeeh указал в комментарии, LIMIT предложение здесь не нужно, так как вы уже вызываете агрегатную функцию.

Было бы полезно, если бы вы также могли опубликовать схему таблицы в своем вопросе.

Источник

Взгляните на этот ответ. Любой способ выбора без блокировки в MySQL?

И эта статья: последовательная разблокировка читает

В вашем случае, как я думаю и как упомянуто @billynoah, таблица должна выполнять много операций записи, как Log стол, так что это может вам помочь.

Источник
Другие вопросы по тегам