如何优化AVG查询表？ [英] How to optimise a table for AVG query?

问题描述

我的表格如下：

| calls | CREATE TABLE `calls` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `request_id` int(10) unsigned NOT NULL,
  `ct` int(10) unsigned DEFAULT NULL,
  `wt` int(10) unsigned DEFAULT NULL,
  `cpu` int(10) unsigned DEFAULT NULL,
  `mu` int(10) unsigned DEFAULT NULL,
  `pmu` int(10) unsigned DEFAULT NULL,
  `caller_id` int(10) unsigned DEFAULT NULL,
  `callee_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `caller_id` (`caller_id`,`request_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3164057 DEFAULT CHARSET=utf8 |

以及简单的查询：

mysql> EXPLAIN SELECT
    -> AVG(`c1`.`wt`) `wt`,
    -> AVG(`c1`.`cpu`) `cpu`,
    -> AVG(`c1`.`mu`) `mu`,
    -> AVG(`c1`.`pmu`) `pmu`
    -> FROM
    -> `calls` `c1`;
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
|  1 | SIMPLE      | c1    | ALL  | NULL          | NULL | NULL    | NULL | 3161147 |       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------+
1 row in set (0.00 sec)

mysql> SELECT
    -> AVG(`c1`.`wt`) `wt`,
    -> AVG(`c1`.`cpu`) `cpu`,
    -> AVG(`c1`.`mu`) `mu`,
    -> AVG(`c1`.`pmu`) `pmu`
    -> FROM
    -> `calls` `c1`;
+-----------+----------+------------+------------+
| wt        | cpu      | mu         | pmu        |
+-----------+----------+------------+------------+
| 2285.2079 | 428.2061 | 30567.4517 | 24925.7182 |
+-----------+----------+------------+------------+
1 row in set (1.61 sec)

服务器非常快（24 GB的RAM）。最相关的 my.cnf （完整my.cnf ）是：

The server is pretty fast (24 GB of RAM). The most relevant of the my.cnf (full my.cnf) is:

query_cache_type=0
query_cache_size=0
key_buffer_size=50M
sort_buffer_size=10M
innodb_buffer_pool_size=1G
read_rnd_buffer_size=1M
join_buffer_size=4M
max_connections=400
table_cache=2000
table_definition_cache=2000

我可以做些什么来优化查询？只有5,278,808条记录，我似乎不太可能达到硬件限制。

Is there anything I can do to optimise the query? With only 5,278,808 records, it seems unlikely that I've reached the hardware limits.

我也尝试将整个表移动到一个相同的 ENGINE = MEMORY 表。时间提高了大约30％。但是，这仍然很慢。

I've also tried moving the entire table to a otherwise the same ENGINE=MEMORY table. The time improved by roughly 30%. However, that's still slow.

推荐答案

您可以尝试避免一遍又一遍地查看旧数据......

You could try and avoid looking at old data over and over again...

也许将记录计数和求和值与上次更新的日期时间一起存储在另一个表中，并将datetime列添加到您的调用表中（确保将其编入索引）。

Maybe store the record counts and summed values in another table along with the datetime last updated and add a datetime column to your calls table (make sure it is indexed).

当您需要计算平均值时，只需查看上次检查后创建的数据，将其与新表中的数据相结合，然后更新新表。

When you need to calculate the averages, just look at data created after the last time you checked, combine this with the data in the new table, and update the new table.

如果你的旧数据可以更新会变得更复杂 - 那么你可能需要有触发器。

It would get more complicated if your old data can be updated - you would probably need to have triggers then.

这篇关于如何优化AVG查询表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！