Блог

Я ищу способ, как сделать мой запрос ВЫБОРА еще быстрее, чем сейчас, потому что у меня есть ощущение, что это должно быть возможно сделать быстрее.

Вот запрос

 SELECT r.id_customer, ROUND(AVG(tp.percentile_weighted), 2) AS percentile
FROM tag_rating AS r USE INDEX (value_date_add)
JOIN tag_product AS tp ON (tp.id_pair = r.id_pair)
WHERE 
r.value = 1 AND
r.date_add > '2020-08-08 11:56:00'
GROUP BY r.id_customer
  

Здесь ОБЪЯСНЯЕТСЯ ВЫБОР

  ---- ------------- ------- -------- ---------------- ---------------- --------- --------------- -------- --------------------------------------------------------------------- 
| id | select_type | table | type   | possible_keys  | key            | key_len | ref           | rows   | Extra                                                               |
 ---- ------------- ------- -------- ---------------- ---------------- --------- --------------- -------- --------------------------------------------------------------------- 
| 1  | SIMPLE      | r     | ref    | value_date_add | value_date_add | 1       | const         | 449502 | Using index condition; Using where; Using temporary; Using filesort |
 ---- ------------- ------- -------- ---------------- ---------------- --------- --------------- -------- --------------------------------------------------------------------- 
| 1  | SIMPLE      | tp    | eq_ref | PRIMARY        | PRIMARY        | 4       | dev.r.id_pair | 1      |                                                                     |
 ---- ------------- ------- -------- ---------------- ---------------- --------- --------------- -------- --------------------------------------------------------------------- 
  

Теперь таблицы

 CREATE TABLE `tag_product` (
  `id_pair` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_product` int(10) unsigned NOT NULL,
  `id_user_tag` int(10) unsigned NOT NULL,
  `status` tinyint(3) NOT NULL,
  `percentile` decimal(8,4) unsigned NOT NULL,
  `percentile_weighted` decimal(8,4) unsigned NOT NULL,
  `elo` int(10) unsigned NOT NULL,
  `date_add` datetime NOT NULL,
  `date_upd` datetime NOT NULL,
  PRIMARY KEY (`id_pair`),
  UNIQUE KEY `id_product_id_user_tag` (`id_product`,`id_user_tag`),
  KEY `status` (`status`),
  KEY `id_user_tag` (`id_user_tag`),
  CONSTRAINT `tag_product_ibfk_5` FOREIGN KEY (`id_user_tag`) REFERENCES `user_tag` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `tag_rating` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_customer` int(10) unsigned NOT NULL,
  `id_pair` int(10) unsigned NOT NULL,
  `id_duel` int(10) unsigned NOT NULL,
  `value` tinyint(4) NOT NULL,
  `date_add` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_duel_id_pair` (`id_duel`,`id_pair`),
  KEY `id_pair_id_customer` (`id_pair`,`id_customer`),
  KEY `value` (`value`),
  KEY `value_date_add` (`value`,`date_add`),
  KEY `id_customer_value_date_add` (`id_customer`,`value`,`date_add`),
  CONSTRAINT `tag_rating_ibfk_3` FOREIGN KEY (`id_pair`) REFERENCES `tag_product` (`id_pair`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tag_rating_ibfk_6` FOREIGN KEY (`id_duel`) REFERENCES `tag_rating_duel` (`id_duel`) ON DELETE CASCADE ON UPDATE CASCADE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  

Таблица tag_product содержит около 250 тыс. строк, а tag_rating — около 1 млн строк.

Моя проблема в том, что SQL-запрос занимает в среднем около 0,8 с на моей машине. Я хотел бы сделать это в идеале менее 0,5 с, предполагая, что таблицы могут стать примерно в 10 раз больше. Количество строк, принимаемых в игру, должно быть примерно одинаковым, потому что у меня есть условие даты (мне нужны только строки возрастом менее месяца).

Возможно ли ускорить это с помощью какого-либо трюка (иначе говоря, не реструктурировать мои таблицы)? Когда я немного изменяю (не присоединяюсь к таблице меньшего размера) оператор как

 SELECT r.id_customer, COUNT(*)
FROM tag_rating AS r USE INDEX (value_date_add)
WHERE 
r.value = 1 AND
r.date_add > '2020-08-08 11:56:00'
GROUP BY r.id_customer;
  

здесь ОБЪЯСНЯЕТСЯ ВЫБОР

  ---- ------------- ------- ------ ---------------- ---------------- --------- ------- -------- --------------------------------------------------------------------- 
| id | select_type | table | type | possible_keys  | key            | key_len | ref   | rows   | Extra                                                               |
 ---- ------------- ------- ------ ---------------- ---------------- --------- ------- -------- --------------------------------------------------------------------- 
| 1  | SIMPLE      | r     | ref  | value_date_add | value_date_add | 1       | const | 449502 | Using index condition; Using where; Using temporary; Using filesort |
 ---- ------------- ------- ------ ---------------- ---------------- --------- ------- -------- --------------------------------------------------------------------- 
  

это занимает около 0,25 с, что здорово. Таким образом, объединение делает его в 3 раза медленнее. Это неизбежно? Я чувствую, что, поскольку я присоединяюсь через первичный ключ, это не должно замедлять выполнение запроса в 3 раза.

—ОБНОВЛЕНИЕ—

На самом деле это мой запрос. Количество различных значений id_customer составляет около 1 тысячи и, как ожидается, будет увеличиваться, количество строк со значением=1 составляет ровно половину. Пока что производительность запроса, похоже, линейно замедляется в зависимости от количества строк в рейтинговой таблице

Использование добавления id_pair в конце индекса id_customer_value_date_add или value_id_customer_date_add не помогает.

 SELECT r.id_customer, ROUND(AVG(tp.percentile_weighted), 2) AS percentile
FROM tag_rating AS r USE INDEX (id_customer_value_date_add)
JOIN tag_product AS tp ON (tp.id_pair = r.id_pair)
WHERE 
r.value = 1 AND
r.id_customer IN (2593179,1461878,2318871,2654090,2840415,2852531,2987432,3473275,3960453,3961798,4129734,4191571,4202912,4204817,4211263,4248789,765650,1341317,1430380,2116196,3367674,3701901,3995273,4118307,4136114,4236589,783262,913493,1034296,2626574,3574634,3785772,2825128,4157953,3331279,4180367,4208685,4287879,1038898,1445750,1975108,3658055,4185296,4276189,428693,4248631,1892448,3773855,2901524,3830868,3934786) AND
r.date_add > '2020-08-08 11:56:00'
GROUP BY r.id_customer
  

Это ОБЪЯСНЕНИЕ ВЫБОРА

  ---- ------------- ------- -------- ---------------------------- ---------------------------- --------- ---------------------------------- -------- -------------------------- 
| id | select_type | table | type   | possible_keys              | key                        | key_len | ref                              | rows   | Extra                    |
 ---- ------------- ------- -------- ---------------------------- ---------------------------- --------- ---------------------------------- -------- -------------------------- 
| 1  | SIMPLE      | r     | range  | id_customer_value_date_add | id_customer_value_date_add | 10      |                                  | 558906 | Using where; Using index |
 ---- ------------- ------- -------- ---------------------------- ---------------------------- --------- ---------------------------------- -------- -------------------------- 
| 1  | SIMPLE      | tp    | eq_ref | PRIMARY,status             | PRIMARY                    | 4       | dev.r.id_pair | 1      | Using where              |
 ---- ------------- ------- -------- ---------------------------- ---------------------------- --------- ---------------------------------- -------- -------------------------- 
  

Приветствуются любые советы. Спасибо

 INDEX(value, date_add, id_customer, id_pair)
  

Было бы «покрыто», что дало бы дополнительный прирост производительности для обоих запросов. А также для формулировки Гордона.

В то же время избавьтесь от этих:

 KEY `value` (`value`),
KEY `value_date_add` (`value`,`date_add`),
  

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

Если вы не используете что-либо другое tag_rating.id , избавьтесь от него и продвигайте UNIQUE to PRIMARY KEY .

Попробуйте написать запрос, используя коррелированный подзапрос:

 SELECT r.id_customer,
       (SELECT ROUND(AVG(tp.percentile_weighted), 2)
        FROM tag_product tp 
        WHERE tp.id_pair = r.id_pair
       ) AS percentile
FROM tag_rating AS r 
WHERE r.value = 1 AND
      r.date_add > '2020-08-08 11:56:00';
  

Это устраняет внешнюю агрегацию, которая должна быть быстрее.