MySQL表格有3500万行，查询速度非常缓慢。

Question

我正在尝试弄清楚为什么我的MySQL数据库查询如此缓慢。我已经阅读了关于MySQL性能的各种内容，以及各种SO问题，但这对我来说仍然是一个谜。

1. 我使用的是MySQL 5.6.23-log - MySQL Community Server（GPL）。 2. 我有一张大约有3500万行的表。 3. 这个表每秒插入大约5次。 4. 这个表看起来像这样： 5. 我在所有列上都有索引，除了answer_text。 我运行的查询是：

SELECT answer_id, COUNT(1) 
FROM answers_onsite a 
WHERE a.screen_id=384 
 AND a.timestamp BETWEEN 1462670000000 AND 1463374800000 
GROUP BY a.answer_id

这个查询大约需要20-30秒的时间，然后会返回一个结果集：

有什么见解吗？ 编辑 如所请求，我的show create table：

CREATE TABLE 'answers_onsite' (
  'id' bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  'device_id' bigint(20) unsigned NOT NULL,
  'survey_id' bigint(20) unsigned NOT NULL,
  'answer_set_group' varchar(255) NOT NULL,
  'timestamp' bigint(20) unsigned NOT NULL,
  'screen_id' bigint(20) unsigned NOT NULL,
  'answer_id' bigint(20) unsigned NOT NULL DEFAULT '0',
  'answer_text' text,
  PRIMARY KEY ('id'),
  KEY 'device_id' ('device_id'),
  KEY 'survey_id' ('survey_id'),
  KEY 'answer_set_group' ('answer_set_group'),
  KEY 'timestamp' ('timestamp'),
  KEY 'screen_id' ('screen_id'),
  KEY 'answer_id' ('answer_id')
) ENGINE=InnoDB AUTO_INCREMENT=35716605 DEFAULT CHARSET=utf8

- ThaiKov

---

请添加查询的解释输出并列出您在表上拥有的所有索引。您可能没有一个可以加速查询的多列索引。 - Shadow

截图通常是展示这些信息的一种糟糕方式。你能否用该表的SHOW CREATE TABLE命令的文本内容来替代截图呢？ - tadman

更新了我的问题，粘贴了创建表语句。此外，索引也有很好的解释。 - ThaiKov

你尝试过用 > 和 < 替换 between 吗？另外，运行 analyze table 看看是否有帮助。 - Felippe Duarte

分析表会破坏我的生产环境，因为我写的这个表有3500万行，并且每秒钟都会被多次插入。 - ThaiKov

Answer 1

ALTER TABLE answers_onsite ADD key complex_index (screen_id,`timestamp`,answer_id);

- Alex

Answer 2

看一下你的WHERE子句：

WHERE a.screen_id=384 AND a.timestamp BETWEEN 1462670000000 AND 1463374800000 GROUP BY a.answer_id

我会创建一个复合索引（screen_id，answer_id，timestamp）并运行一些测试。 您还可以尝试（screen_id，timestamp，answer_id），以查看其是否更有效。

然而，BETWEEN子句是已知的较慢操作，就像任何范围查询一样。在数百万行上进行COUNT也是如此。我会每天计算一次，并将结果保存到“统计”表中，您可以在需要时查询该表...显然，如果您不需要实时数据。

- Paranoid Android

Answer 3

您可以像这样使用MySQL 分区：

alter table answers_onsite drop primary key;
alter table answers_onsite add primary key (id, timestamp) partition by HASH(id) partitions 500;

运行以上代码可能需要一些时间，具体取决于您的表格大小。

- Gogo