在一个大表(75M+行)上优化一个简单的mysql选择

Question

我有一个统计表，它以较大的速度增长（约2500万行/天），我想对其进行优化以进行选择，该表适合内存，服务器有足够的备用内存（32G，表为4G）。

我的简单汇总查询是：

EXPLAIN select FROM_UNIXTIME(FLOOR(endtime/3600)*3600) as ts,sum(numevent1) as success , sum(numevent2) as failure from stats where endtime > UNIX_TIMESTAMP()-3600*96 group by ts order by ts;
+----+-------------+--------------+------+---------------+------+---------+------+----------+----------------------------------------------+
| id | select_type | table        | type | possible_keys | key  | key_len | ref  | rows     | Extra                                        |
+----+-------------+--------------+------+---------------+------+---------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | stats        | ALL  | ts            | NULL | NULL    | NULL | 78238584 | Using where; Using temporary; Using filesort |
+----+-------------+--------------+------+---------------+------+---------+------+----------+----------------------------------------------+

Stats是一个innodb表，在endtime上有一个正常的索引。。我应该如何优化它？

注意：我确实计划添加汇总表，但目前我一直坚持这样做，我想知道是否可以在没有额外应用程序代码的情况下修复它。

Answer 1

我一直在做本地测试。尝试以下操作：

alter table stats add index (endtime, numevent1, numevent2);

并删除order by，因为它应该隐含在group by中（我猜解析器在这种情况下只是忽略order by，但只是以防万一：）

Answer 2

由于您正在使用InnoDB，您也可以尝试以下操作：

a） 将innodb_buffer_pool_size更改为24GB（需要重新启动服务器）-这将确保整个表都能加载到内存中，因此即使表的越来越大，也会加快排序速度

b） 添加innodb_file_per_table，使innodb将每个新表空间放置在自己的表中。需要您删除现有表并重新创建

c） 使用可容纳数据的最小可用列大小。如果没有看到实际的列定义和一些示例，我就无法提供任何具体的想法。您能提供一个示例模式和可能的5行数据