MySQL大型表性能优化

Question

我试着用这个表来解决性能问题

+--------------+------------------+------+-----+---------+----------------+
| Field        | Type             | Null | Key | Default | Extra          |
+--------------+------------------+------+-----+---------+----------------+
| id           | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| direction_id | int(10) unsigned | NO   | MUL | NULL    |                |
| created_at   | datetime         | NO   |     | NULL    |                |
| rate         | decimal(16,6)    | NO   |     | NULL    |                |
+--------------+------------------+------+-----+---------+----------------+

，它包含大约100米行。

只有一个查询从这个表中选择数据：

SELECT AVG(rate) AS rate, created_at 
FROM statistics 
WHERE direction_id = ? 
AND created_at BETWEEN ? AND ? 
GROUP BY created_at

direction_id是一个外键，但是它的选择性很差：

+----+-------------+------------+------------+------+---------------------------------+---------------------------------+---------+-------+-------+----------+---------------------------------------------------------------------+
| id | select_type | table      | partitions | type | possible_keys                   | key                             | key_len | ref   | rows  | filtered | Extra                                                               |
+----+-------------+------------+------------+------+---------------------------------+---------------------------------+---------+-------+-------+----------+---------------------------------------------------------------------+
|  1 | SIMPLE      | statistics | NULL       | ref  | statistics_direction_id_foreign | statistics_direction_id_foreign | 4       | const | 26254 |    11.11 | Using index condition; Using where; Using temporary; Using filesort |
+----+-------------+------------+------------+------+---------------------------------+---------------------------------+---------+-------+-------+----------+---------------------------------------------------------------------+

所以我正在寻找解决这个问题的方法，并且需要一些建议。散列分区(Direction_id)对我有帮助吗？如果有帮助的话，最好的方法是什么？

或者还有别的办法来解决这个问题。

Answer 1

按平均每日费率计算，你是说这个？

SELECT AVG(rate) AS rate, 
       DATE(created_at) 
    FROM statistics 
    WHERE direction_id = ? 
      AND created_at BETWEEN ? AND ? 
    GROUP BY DATE(created_at)

还有INDEX(direction_id, created, rate) --它既是“覆盖”，也是“复合”。解释将使用“使用索引”来表示“覆盖”，这意味着整个查询只能在索引的BTree上执行。因此，“覆盖”提供了额外的性能提升。

更改为包含DATE(created_at)的高级索引可能无助于此查询。

未指示PARTITIONing。

可注明“简表”。http://mysql.rjweb.org/doc.php/summarytables

Answer 2

首先，让我们修复您的查询，以便它是一个有效的聚合查询。想必，您希望获得rate的日平均值，因此：

SELECT AVG(rate) AS rate, DATE(created_at) as created_day
FROM statistics 
WHERE direction_id = ? AND created_at BETWEEN ? AND ? 
GROUP BY DATE(created_at)

然后，我建议创建以下索引：

create index idx_statistics on statistics (direction_id, created_at, rate);

在MySQL的最新版本中，我们还可以考虑在date(create_at)上使用索引。如果您可以使用以下where子句：

WHERE direction_id = ? AND DATE(created_at) BETWEEN ? AND ? 

那么下面的索引就会派上用场了：

create index idx_statistics on statistics (direction_id, (date(created_at)), rate);