MySQL Query / Table нуждается в оптимизации

0

У меня есть вопрос, который занимает очень много времени. ~ 7 минут неловко. Я был бы очень признателен за помощь. Отсутствуют индексы? Перепишите запрос? Все вышеперечисленное?

Большое спасибо

mysql Ver 14.14 Distrib 5.7.25, для Linux (x86_64)

Запрос выглядит так:

SELECT COUNT(*) AS count_all, name 
FROM api_events ae
INNER JOIN products p on p.token=ae.product_token 
WHERE (ae.created_at > '2019-01-21 12:16:53.853732') 
GROUP BY name

Вот два определения таблицы

api_events имеет ~ 31 миллион записей

CREATE TABLE 'api_events' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'api_name' varchar(200) NOT NULL,
  'hostname' varchar(200) NOT NULL,
  'controller_action' varchar(2000) NOT NULL,
  'duration' decimal(12,5) NOT NULL DEFAULT '0.00000',
  'view' decimal(12,5) NOT NULL DEFAULT '0.00000',
  'db' decimal(12,5) NOT NULL DEFAULT '0.00000',
  'created_at' datetime NOT NULL,
  'updated_at' datetime NOT NULL,
  'product_token' varchar(255) DEFAULT NULL,
  PRIMARY KEY ('id'),
  KEY 'product_token' ('product_token')
) ENGINE=InnoDB AUTO_INCREMENT=64851218 DEFAULT CHARSET=latin1;

и продуктов имеет только 12 записей

CREATE TABLE 'products' (
  'id' int(11) NOT NULL AUTO_INCREMENT,
  'code' varchar(30) NOT NULL,
  'name' varchar(100) NOT NULL,
  'description' varchar(2000) NOT NULL,
  'token' varchar(50) NOT NULL,
  'created_at' datetime NOT NULL,
  'updated_at' datetime NOT NULL,
  PRIMARY KEY ('id')
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1;
  • 0
    Почему токен varchar (50) и product_token varchar (255)?
  • 0
    Я предложил бы использовать диапазон секционирование created_at для таблицы api_events. Для получения более подробной информации смотрите - https://dev.mysql.com/doc/refman/8.0/en/partitioning-range.html
Теги:
database
indexing
query-optimization

4 ответа

1

Для запроса, как указано, вам нужно

api_events:  INDEX(created_at, product_token)
products:    INDEX(token, name)

Поскольку WHERE упоминает api_events, Оптимизатор может начинаться с этой таблицы. created_at находится в WHERE, поэтому индекс начинается с этого, даже если начинать с "range" обычно неправильно. В этом случае пара является "прикрытием".

Тогда INDEX(token, name) также "покрывает".

"Покрывающие" индексы дают небольшое, но широко варьируемое улучшение производительности.

Поделиться
1

Вы можете улучшить индекс добавления производительности соединения

create index  idx1 on api_events(product_token, created_at);

create index  idx2 on products(token);

Вы также можете попробовать инвертировать столбцы из api_events

 create index  idx1 on api_events(created_at, product_token);

и пытается добавить избыточность в индекс продукта

create index  idx2 on products(token, name);
Поделиться
  • 0
    Есть ли причина, по которой вы не предложили внешний ключ вместо двух индексов? Редактировать: Неважно, я только что увидел, что MySql, кажется, не добавляет индексы неявно при добавлении внешних ключей.
  • 0
    @FlorianLim добавление идентификатора ограничения при выборе дизайна ... добавление индекса при выборе производительности .. поэтому в этом случае я предложил индекс .. часть
Показать ещё 2 комментария
0

Что произойдет, если вы сгруппируете по токену вместо name?

SELECT ae.product_token, COUNT(*) AS count_all 
FROM api_events ae
WHERE ae.created_at > '2019-01-21 12:16:53.853732')
GROUP BY ae.product_token;

Для этого запроса, вероятно, поможет индекс api_events(created_at, product_token).

Если это быстрее, вы можете ввести имя, используя подзапрос.

Поделиться
0

Кажется, что критерии для created_at очень избирательны (глядя только на последние 7 дней?). Это кричит, чтобы исследовать индекс с created_at в качестве ведущего столбца.

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

  api_events_IX ON api_events ( created_at, product_token )

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

При использовании индекса для запроса GROUP BY по-прежнему потребуется операция "Использование сортировки файлов".

(Я предполагаю, что соединение с 12 строками в продукте не исключает большого количества строк... что в подавляющем большинстве строк в api_event product_token ссылается на строку, существующую в product.

Используйте MySQL EXPLAIN чтобы увидеть план выполнения запроса.

Еще одним возможным уточнением (для проверки производительности) было бы выполнение некоторой агрегации во встроенном представлении:

 SELECT SUM(s.count_all) AS count_all
      , p.name
   FROM ( SELECT COUNT(*) AS count_all
               , ae.product_token
            FROM api_events ae
           WHERE ae.created_at > '2019-01-21 12:16:53.853732'
           GROUP
              BY ae.product_token
        ) s
   JOIN products p 
     ON p.token = s.product_token 
  GROUP
     BY p.name

Если предположение о product_token неверно проинформировано, если в api_event есть много строк api_event значениями product_token которые не ссылаются на строку в product... мы могли бы пойти другим путем...

Поделиться

Ещё вопросы

Сообщество Overcoder
Наверх
Меню