SELECT语句优化MySQL

Question

我正在寻找一种方法，如何使我的SELECT查询比现在更快，因为我有一种感觉，它应该可以让它更快。

下面是查询

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

下面是EXPLAIN SELECT

+----+-------------+-------+--------+----------------+----------------+---------+---------------+--------+---------------------------------------------------------------------+
| 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大约有250k行，tag_rating大约有1M行。

我的问题是，在我的机器上，SQL查询平均需要0.8秒。我希望它理想地在0.5s以下，同时也假设表可以变得像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;

下面是EXPLAIN SELECT

+----+-------------+-------+------+----------------+----------------+---------+-------+--------+---------------------------------------------------------------------+
| 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秒，这是很棒的。所以JOIN使其速度慢了3倍。这是不可避免的吗？我觉得因为我是通过主键连接的，所以它不应该使查询速度慢3倍。

-更新

这实际上是我的问题。不同id_customer值的数量约为1000个，并且预计会上升，具有value=1的行数恰好是一半。到目前为止，根据rating表中的行数，查询性能似乎呈线性下降

在id_customer_value_date_add或value_id_customer_date_add索引的末尾添加id_pair不会有任何帮助。

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

这是EXPLAIN SELECT

+----+-------------+-------+--------+----------------------------+----------------------------+---------+----------------------------------+--------+--------------------------+
| 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              |
+----+-------------+-------+--------+----------------------------+----------------------------+---------+----------------------------------+--------+--------------------------+

任何建议都是值得感谢的。谢谢

Answer 1

INDEX(value, date_add, id_customer, id_pair)

将是“覆盖”，为两个查询提供额外的性能提升。也适用于戈登的公式。

同时，去掉这些：

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

因为它们可能会妨碍优化器选择新的索引。使用这些索引的任何其他查询都将很容易地使用新索引。

如果您没有以其他方式使用tag_rating.id，请去掉它，并将UNIQUE提升为PRIMARY KEY。

Answer 2

尝试使用相关子查询编写查询：

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

这消除了应该更快的外部聚集。